java连接mysql数据库实现单条和批量插入的具体代码

java连接mysql数据库实现单条和批量插入的具体代码

1、连接数据库

package com.njupt.ymh;

  

import java.sql.DriverManager;

import java.sql.SQLException;

import com.mysql.jdbc.Connection;

  

public class Connect_MySQL {

  

 private static final String URL="jdbc:mysql://127.0.0.1:3306/news"; // 一般默认3306，这里设置成6666 （33060） MYSQL8 WMPNetworkSvc

 private static final String USER="root";

 private static final String PASSWORD="12345";

 private static Connection connection=null;

  

 static{

 //1、加载驱动程序（反射的方法）

 try {

  Class.forName("com.mysql.jdbc.Driver");

 } catch (ClassNotFoundException e) {

  e.printStackTrace();

 }

 //2、连接数据库

 try {

 connection=(Connection) DriverManager.

   getConnection(URL, USER,PASSWORD);//地址，用户名，密码

 } catch (SQLException e) {

  e.printStackTrace();

 }

 }

 public static Connection getConnection(){

 return connection;

 }
}

 2、单条插入

package com.njupt.ymh;

/**

 * 单条插入数据

 */

  

import java.sql.SQLException;

import java.util.List;

  

import com.mysql.jdbc.Connection;

  

public class OperationPaper {

  

 private static Connection connection=Connect_MySQL.getConnection();

  

 public void addNewsPaper(NewsPaper newsPaper){//增

 // connection = Connect_MySQL.getConnection();

 String sql="insert into papertest (id, date, title, lead_pargraph, full_text) values(?, ?, ?, ?, ?)";

  

  java.sql.PreparedStatement ptmt = null;

  try {

  ptmt = connection.prepareStatement(sql);

  } catch (SQLException e1) {

  e1.printStackTrace();

  }

   

  try {

  ptmt.setLong(1, newsPaper.getID());

  ptmt.setString(2, newsPaper.getDate());

  ptmt.setString(3, newsPaper.getTitle());

  ptmt.setString(4, newsPaper.getLead());

  ptmt.setString(5, newsPaper.getfull());

  ptmt.execute();//执行给定的SQL语句，该语句可能返回多个结果

   

  } catch (SQLException e) {

  e.printStackTrace();

  }

 }

 public static void main(String[] args) {

 OperationPaper operationPaper = new OperationPaper();

 List<String> listFile = SearchFile.getAllFile("E:\\huadai\\1996\\07\\21", false); // 文件列表

  

 for (String string : listFile) { 

  NewsPaper newsPaper = new NewsPaper(string);

  if (newsPaper.isUseful())

  operationPaper.addNewsPaper(newsPaper); // 插入数据库

 }

 }
}

3、批量插入

package com.njupt.ymh;

  

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

  

import com.mysql.jdbc.Connection;

  

public class OperaOnNewsPaper implements Cloneable{

  

private static Connection connection=Connect_MySQL.getConnection();

  

  

 /**

 * 支持批量插入数据

 * @param newsPaper

 */

  

 public void addNewsPaper(ArrayList<NewsPaper> listNewsPaper){//增

 String sql="insert into papertest (id, date, title, lead_pargraph, full_text) values(?, ?, ?, ?, ?)";

 java.sql.PreparedStatement ptmt = null;

  

 try {

  connection.setAutoCommit(false);// 关闭事务

  ptmt = connection.prepareStatement(sql);

  

 } catch (SQLException e2) {

  e2.printStackTrace();

 }

  

 for (NewsPaper paperaper : listNewsPaper) {

   

  try {

  ptmt.setLong(1, paperaper.getID());

  ptmt.setString(2, paperaper.getDate());

  ptmt.setString(3, paperaper.getTitle());

  ptmt.setString(4, paperaper.getLead());

  ptmt.setString(5, paperaper.getfull());

  ptmt.addBatch();

  }

  catch (SQLException e) {

  e.printStackTrace();

  }

 }

 try {

  ptmt.executeBatch();//执行给定的SQL语句，该语句可能返回多个结果

  connection.commit();

 } catch (SQLException e) {

  e.printStackTrace();

 }

 }

  

  

 public static void main(String[] args) {

 OperaOnNewsPaper operation = new OperaOnNewsPaper();

 List<String> listFile = SearchFile.getAllFile("E:\\huadai\\2007", false); // 文件列表

 ArrayList<NewsPaper> listPaper = new ArrayList<>();

 int count = 0;

 int sizenum = 1000;

 for (String string : listFile) {

  NewsPaper newsPaper = new NewsPaper(string);

   

  if (newsPaper.isUseful()) {

  count++;

  listPaper.add(newsPaper);  // 新闻列表

  if (count % sizenum == 0) {

   //System.out.println("ok");

   System.out.println("  " + count);

   operation.addNewsPaper(listPaper); //插入数据库

    

   System.out.println(count);

   listPaper.clear();

  }

  }

 }

 if (count %sizenum != 0) {

  operation.addNewsPaper(listPaper);

  System.out.println("zui hou ");

 }

 }

}

通过实际测试，大概十万级数据批量插入要不单条插入节省10分钟左右时间。因为每次单条插入就要和数据库建立一次连接，进行一次日志更新。但是，如果批量插入过程中，批量的数据值有一条不符合格式就将导致本次批量插入整体失败，因此需要对失败情况进行处理，或者对批量插入的数据进行预处理，保证批量插入能够成功。