JDBC+文本文档向mysql批量插入数据

JDBC+文本文档向mysql批量插入数据

温馨提示：建好自己的数据库和表，表中字段明确。

JDBC连接数据库并向数据库插入一条数据：（包自己导）

public static void  main(String[] srgs) throws NumberFormatException, IOException {
		@SuppressWarnings("resource")
		BufferedReader bfr = new BufferedReader(new InputStreamReader(new FileInputStream("D:/AddBatchTest.txt"), "utf-8"));
		Connection connection = null;
		PreparedStatement preStmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");//注册驱动程序
			String url = "jdbc:mysql://localhost:3306/grade?user=root&password=123456"
					+"&useUnioncode=true&characterEncoding=utf-8";
			connection =DriverManager.getConnection(url);//连接数据库
			if(connection != null)
				System.out.println("Connection success");
		          int count = 0;
			String sql = "insert into stock(orderId, sName,consignmentData, baleName, count, money)"
					+" values(?,?,?,?,?,?)";//构造半成品语句
			preStmt = connection.prepareStatement(sql);//生成对象
			preStmt.setInt(1, 1222);
			preStmt.setString(2,"辣条");
			preStmt.setString(3,"2020-3-22");
			preStmt.setString(4, "小秦");
			preStmt.setInt(5,12);
			preStmt.setDouble(6,120);
			preStmt.executeUpdate();	

System.out.println("成功插入" + preStmt.getUpdateCount() + "条数据");		
		}catch(ClassNotFoundException e){
			System.out.println("JDBC driver error!");
			e.printStackTrace();
		}catch(SQLException e){
			System.out.println("JDBC connection error!");
			e.printStackTrace();
		}finally {//关闭链接
			try {
				if(preStmt != null)
					preStmt.close();
				if(connection != null)
					connection.close();
			}catch(SQLException e){
				System.out.println("close error");
				e.printStackTrace();
			}
		}
	}

掌握批量数据的导入，既能节省时间，又符合我们的实操。
掌握一条数据的插入后，就该思考更深层的大批数据插入。
在此，我选择从文件下手
加入文件操作（先建好文件）

BufferedReaderbfr=newBufferedReader(newInputStreamReader(new FileInputStream(“D:/AddBatchTest.txt”), “utf-8”));

文件有77条数据（随便写的）。
每一行数据和表的字段对应，也就是以逗号分隔的每一列，对应同一个字段。
在这里插入图片描述

按行从文件读取（以逗号分隔）内容，再装入字符数组中
bfr.readLine()读一行，插入一行，直到为空

完整代码

public static void  main(String[] srgs) throws NumberFormatException, IOException {
		@SuppressWarnings("resource")
		BufferedReader bfr = new BufferedReader(new InputStreamReader(new FileInputStream("D:/AddBatchTest.txt"), "utf-8"));
		Connection connection = null;
		PreparedStatement preStmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");//注册驱动程序
			String url = "jdbc:mysql://localhost:3306/grade?user=root&password=123456"
					+"&useUnioncode=true&characterEncoding=utf-8";
			connection =DriverManager.getConnection(url);
			if(connection != null)
				System.out.println("Connection success");
			connection.setAutoCommit(false); // 设置手动提交
		    int count = 0;
			String sql = "insert into stock(orderId, sName,consignmentData, baleName, count, money)"
					+" values(?,?,?,?,?,?)";//构造半成品语句
			preStmt = connection.prepareStatement(sql);//生成对象
			
			String line = null;
			long beginTime = System.currentTimeMillis(); //获取开始时间
			while (null != (line = bfr.readLine())) {//按行读取文件呢
		            String[] infos = line.split(",");//每行以“,”号分隔读入数组
		            if (infos.length < 6)   continue;
		            preStmt.setLong(1, Long.valueOf(infos[0]));
		            preStmt.setString(2, infos[1]);
		            preStmt.setString(3, infos[2]);
		            preStmt.setString(4, infos[3]);
		            preStmt.setLong(5, Long.valueOf(infos[4]));
		            preStmt.setLong(6, Long.valueOf(infos[5]));
		            preStmt.addBatch();          // 加入批量处理
		            count++;
		     }
			 long endTime = System.currentTimeMillis(); //获取结束时间
			 
		     preStmt.executeBatch(); // 执行批量处理
		     connection.commit();  // 提交
		     
		     System.out.println("成功插入" + count + "条数据");
		     System.out.println("用时：" + (endTime-beginTime) + "秒");
		     connection.close();
		}catch(ClassNotFoundException e){
			System.out.println("JDBC driver error!");
			e.printStackTrace();
		}catch(SQLException e){
			System.out.println("JDBC connection error!");
			e.printStackTrace();
		}finally {//关闭链接
			try {
				if(preStmt != null)
					preStmt.close();
				if(connection != null)
					connection.close();
			}catch(SQLException e){
				System.out.println("close error");
				e.printStackTrace();
			}
		}
	}