Shulou(Shulou.com)06/03 Report--

This issue of the content of the editor will bring you about Java jdbc batch multithreading how to read CVS files into the library, the article is rich in content and professional analysis and description for you, after reading this article, I hope you can get something.

The requirements are as follows: now you need to test the incoming performance of an in-memory database, requiring you to test the performance of a single table with a total of 100w records per thread and one file per thread.

Knowledge points: jdbc + multithreading + batch processing + file reading

Let's take a look at my code structure first.

Description:

Files: stores the files you are about to read. Lib: stores third-party jar files, such as database driver packages. MemSqlTestMain: this is the entrance to the project, which is the main program. DBUtil: this class is a database help class, which mainly reads database configuration information, obtains connections, closes connections, and so on. InsertUtil: a class that reads data files to generate sql and stores them in batches. TableDataInfo: a class that focuses on the objects of the data table to be inserted. XMLUtil: read the XML configuration file config.xml: configure the table information to be inserted and the path of the file dbconfig.properties: mainly store the connection information of the database, including URL, user name password and so on.

Don't say much and go straight to the code:

Import java.util.ArrayList;/** * @ param * @ author wu.lin * @ description program entry to enable threads to read files and store them * @ create, 01 / 09, 2016 15:12 * @ throws * / public class MemSqlTestMain {public static void main (String [] args) {/ / read the table name String tableName = XMLUtil.getTableName (); System.out.println (tableName) by reading the configuration file / / read the path of the file where the data is stored through the configuration file ArrayList fileNameList = XMLUtil.getFileNameList (); int len = fileNameList.size (); / / start a process for each file to perform the read and storage operation for (int I = 0; I)

< len; i++) { String fileName = fileNameList.get(i); System.out.println(fileName); new Thread(new InsertUtil(fileName, tableName)).start(); } }} import java.io.BufferedReader;import java.io.File;import java.io.FileInputStream;import java.io.InputStreamReader;import java.sql.*;/** * @param * @author wu.lin * @description InsertUtil是一个线程类，主要读取数据文件组装Sql并执行入库操作 * @create 2016年09月01日 14:10 * @throws */public class InsertUtil implements Runnable { //文件路径 private String filePath; //表名 private String tableName; //.cvs文件数据以","分隔 private static String DELIMITERS = ","; //获取数据库帮助类 DBUtil dbutil = DBUtil.getInstance(); public InsertUtil() {} public InsertUtil(String filePath, String tableName) { this.filePath = filePath; this.tableName = tableName; } public static String getDELIMITERS() { return DELIMITERS; } public static void setDELIMITERS(String delimiters) { DELIMITERS = delimiters; } public String getFilePath() { return filePath; } public void setFilePath(String filePath) { this.filePath = filePath; } //读取文件并且批处理入库的方法 public boolean insertDB(String tablename, long rc, String filePath) { if(filePath == null || "".equals(filePath)) { System.out.println("文件路径为空"); return false; } if (rc < 1) { rc = 100; } Connection conn = null; boolean flag = false; Statement pre = null; String sql = ""; TableDataInfo tableInfo = new TableDataInfo(); try { if(conn == null) { conn = dbutil.getConnection(); } pre = conn.createStatement(); conn.setAutoCommit(false); int colCount = tableInfo.getTableColNums(tablename, conn); int rowCount = 0; File file = new File(filePath); BufferedReader buf = null; buf = new BufferedReader(new InputStreamReader(new FileInputStream(file))); String line_record = buf.readLine(); long startTime = System.currentTimeMillis(); //开始计时 while (line_record != null) { // 解析每一条记录 sql = "INSERT INTO " + tablename + " VALUES('"; String[] fields = line_record.split(DELIMITERS); //对Insert语句的合法性进行判断 if(fields.length != colCount){ System.out.println("要插入的数据列数和表的数据列不相匹配，停止执行"); break; } for (int i = 0; i < fields.length; i++) { sql += fields[i]; if (i < fields.length - 1) { sql += "','"; } } sql += "');"; // 在控制台输出SQL语句 // System.out.println(sql); //执行SQL语句 pre.addBatch(sql); rowCount++; line_record = buf.readLine(); if (rowCount >

= rc) {break;}} pre.executeBatch (); conn.setAutoCommit (true); pre.close (); System.out.println ("total number of rows written:" + rowCount); long endTime = System.currentTimeMillis (); / / stop timing System.out.println ("execution time is: + (endTime-startTime) +" ms ");} catch (Exception e) {flag = false; try {/ / rollback if (conn! = null) {conn.rollback () } catch (SQLException E1) {e1.printStackTrace ();} e.printStackTrace ();} finally {dbutil.close (null, pre, conn);} return flag;} public void run () {this.insertDB (tableName, 500000, filePath);}}

Import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.ResultSet;import java.sql.SQLException;/** * @ param * @ author wu.lin * @ description Database Table entity * @ create 14:19 * @ throws * / public class TableDataInfo {DBUtil dbutil = DBUtil.getInstance () / * * @ param m_TableName * @ param m_Connection * @ return the number of columns in this table * / public int getTableColNums (String m_TableName, Connection m_Connection) {int colCount = 0; try {if (m_Connection = = null) {m_Connection = dbutil.getConnection ();} DatabaseMetaData m_DBMetaData = m_Connection.getMetaData (); ResultSet tableRet = m_DBMetaData.getTables (null, "%", m_TableName, new String [] {"TABLE"}) While (tableRet.next ()) {System.out.println ("Table name is:" + tableRet.getString ("TABLE_NAME"));} String columnName; String columnType; ResultSet colRet = m_DBMetaData.getColumns (null, "%", m_TableName, "%"); while (colRet.next ()) {columnName = colRet.getString ("COLUMN_NAME"); columnType = colRet.getString ("TYPE_NAME"); int dataSize = colRet.getInt ("COLUMN_SIZE"); int digits = colRet.getInt ("DECIMAL_DIGITS") Int nullable = colRet.getInt ("NULLABLE"); String nullFlag; if (nullable = = 1) {nullFlag = "Null";} else {nullFlag = "Not Null";} System.out.println (columnName + "" + columnType + "(" + dataSize + "," + digits + ")" + nullFlag); colCount++;}} catch (SQLException e) {e.printStackTrace ();} System.out.println ("The number of column is:" + colCount); return colCount;}

Then all that is left is the code to read the configuration file. Let's first take a look at the contents of the configuration file (here you have configured the database configuration file path table name and the relative path where the file is stored):

Src/dbconfig.properties memtest files/memtest.csv files/memtest_1.csv files/memtest_2.csv files/memtest_3.csv files/memtest_4.csv files/memtest_5.csv files/memtest_6.csv files/memtest_7.csv files/memtest_8.csv files/memtest_9.csv files/memtest_10.csv

The next step is to read the contents of this configuration file, which is relatively simple, so paste only part of the code:

Import javax.xml.parsers.*;import org.w3c.dom.*;import java.io.*;import java.util.ArrayList;/** * @ param * @ author wu.lin * @ description read configuration Information * @ create 01 / 09 / 2016 15:45 * @ throws * / public class XMLUtil {/ / this method is used to extract the table name to be inserted from the XML configuration file and return the table name public static String getTableName () {return getXmlProperties ("tableName") } public static String getDatabaseUrl () {return getXmlProperties ("dataBaseUrl");} public static String getDbFilePath () {return getXmlProperties ("db_file");} private static String getXmlProperties (String proName) {try {Document doc = getDoc (); / / get the text node NodeList nl = doc.getElementsByTagName (proName); Node classNode=nl.item (0). GetFirstChild (); String tableName=classNode.getNodeValue (). Trim (); return tableName;} catch (Exception e) {e.printStackTrace (); return null }} private static Document getDoc () throws Exception {/ / create document object DocumentBuilderFactory dFactory = DocumentBuilderFactory.newInstance (); DocumentBuilder builder = dFactory.newDocumentBuilder (); Document doc; doc = builder.parse (new File ("src/config.xml")); return doc;}}

Database configuration information document:

Db.used=mysql# driver classoracle.jdbc.driver_class=oracle.jdbc.driver.OracleDriver# URLoracle.jdbc.url=jdbc:oracle:thin:@localhost:1521:ORCL# usernameoracle.jdbc.username=scott# pwdoracle.jdbc.pwd=tiger#mysql connect configmysql.jdbc.driver_class=com.mysql.jdbc.Drivermysql.jdbc.url=jdbc:mysql://localhost:3306/mysqldbmysql.jdbc.username=rootmysql.jdbc.pwd=

Finally, there are database help classes, which are more common:

Import java.io.FileInputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;/** * @ param * @ author wu.lin * @ description Database help Class * @ create 18:56 * @ throws * / public class DBUtil {private static Properties env = new Properties (); private static DBUtil dbutil; private static String dbname; private static String driverClass_key; private static String url_key Private static String username_key; private static String pwd_key; private DBUtil () {} / / Singleton pattern public static synchronized DBUtil getInstance () {if (null = = dbutil) {dbutil = new DBUtil ();} return dbutil;} / * * get the database connection * @ return * / public Connection getConnection () {Connection conn = null; try {env.load (new FileInputStream (XMLUtil.getDbFilePath ()); dbname = env.getProperty ("db.used"). ToLowerCase () DriverClass_key = dbname + ".jdbc.driver _ class"; url_key = dbname + ".jdbc.url"; username_key = dbname + ".jdbc.username"; pwd_key = dbname + ".jdbc.pwd"; / / load the driver class file Class.forName (env.getProperty (driverClass_key)); conn = createConnection ();} catch (Exception e) {e.printStackTrace ();} return conn;} private Connection createConnection () throws SQLException {Connection conn = null If ("oracle" .equals (dbname)) {conn = DriverManager.getConnection (env.getProperty (url_key), env.getProperty (username_key), env.getProperty (pwd_key));} if ("sqlserver" .equals (dbname)) {conn = DriverManager.getConnection (env.getProperty (url_key), env.getProperty (username_key), env.getProperty (pwd_key)) } if ("mysql" .equals (dbname)) {/ / connection syntax for other databases String url = env.getProperty (url_key); String username = env.getProperty (username_key); String pwd = env.getProperty (pwd_key); if (username! = null & &! ".equals (username)) {url + = ("? user= "+ username); if (pwd! = null & &!" .equals (pwd)) {url + = ("& password=" + pwd) }} conn = DriverManager.getConnection (url);} return conn;} / provide the method for jdbc to close the connection public void close (ResultSet rs,Statement st,Connection conn) {try {if (rswatches null) rs.close (); if (stalled null) st.close (); if (connexual null) conn.close ();} catch (SQLException e) {e.printStackTrace ();}

The final job is to store the corresponding data files in the file directory, and then configure the file name, table name and basic information of the database connection through the configuration file, and then run the program entry, you can run the program. However, I also encountered some minor problems in this process. For example, I only have a .csv file with 100w pieces of data, but I am required to read ten files. At this time, I used a gadget:

As we all know, files in .csv format can also be opened with Excel software, so here we can convert and use Excel splitter to divide the files into ten parts, which will solve the problem perfectly.

The above is the Java jdbc batch multithreading shared by Xiaobian how to read CVS files into the library. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.