java連接mysql數(shù)據(jù)庫如何實現(xiàn)單條插入和批量插入

java連接mysql數(shù)據(jù)庫如何實現(xiàn)單條插入和批量插入，相信很多沒有經(jīng)驗的人對此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個問題。

1、連接數(shù)據(jù)庫

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，這里設(shè)置成6666 （33060） MYSQL8 WMPNetworkSvc private static final String USER="root"; private static final String PASSWORD="12345"; private static Connection connection=null;  static{ //1、加載驅(qū)動程序（反射的方法） try {  Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) {  e.printStackTrace(); } //2、連接數(shù)據(jù)庫 try { connection=(Connection) DriverManager.   getConnection(URL, USER,PASSWORD);//地址，用戶名，密碼 } catch (SQLException e) {  e.printStackTrace(); } } public static Connection getConnection(){ return connection; } }

2、單條插入

package com.njupt.ymh;/** * 單條插入數(shù)據(jù) */ 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();//執(zhí)行給定的SQL語句，該語句可能返回多個結(jié)果    } catch (SQLException e) {  e.printStackTrace();  } } public static void main(String[] args) { OperationPaper operationPaper = new OperationPaper(); List<String> listFile = SearchFile.getAllFile("E:\\huadai\\1996\7\\21", false); // 文件列表  for (String string : listFile) {    NewsPaper newsPaper = new NewsPaper(string);  if (newsPaper.isUseful())   operationPaper.addNewsPaper(newsPaper); // 插入數(shù)據(jù)庫 } } }

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();   /** * 支持批量插入數(shù)據(jù) * @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);// 關(guān)閉事務  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();//執(zhí)行給定的SQL語句，該語句可能返回多個結(jié)果  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); //插入數(shù)據(jù)庫      System.out.println(count);   listPaper.clear();  }  }  } if (count %sizenum != 0) {  operation.addNewsPaper(listPaper);  System.out.println("zui hou "); }  }}

通過實際測試，大概十萬級數(shù)據(jù)批量插入要不單條插入節(jié)省10分鐘左右時間。因為每次單條插入就要和數(shù)據(jù)庫建立一次連接，進行一次日志更新。但是，如果批量插入過程中，批量的數(shù)據(jù)值有一條不符合格式就將導致本次批量插入整體失敗，因此需要對失敗情況進行處理，或者對批量插入的數(shù)據(jù)進行預處理，保證批量插入能夠成功。

看完上述內(nèi)容，你們掌握java連接mysql數(shù)據(jù)庫如何實現(xiàn)單條插入和批量插入的方法了嗎？如果還想學到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！