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分钟左右时间。因为每次单条插入就要和数据库建立一次连接，进行一次日志更新。但是，如果批量插入过程中，批量的数据值有一条不符合格式就将导致本次批量插入整体失败，因此需要对失败情况进行处理，或者对批量插入的数据进行预处理，保证批量插入能够成功。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。