java连接mysql数据库实现单条插入和批量插入

本文插入数据库的数据来源：/qq_29672495/article/details/82860226

1、连接数据库

package ;

import ;
import ;
import ;
 
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 {
			("");
		} catch (ClassNotFoundException e) {
			();
		}
		//2、连接数据库
		try {
		connection=(Connection) DriverManager.
					getConnection(URL, USER,PASSWORD);//地址，用户名，密码
		} catch (SQLException e) {
			();
		}
	}
	public static Connection getConnection(){
		return connection;
	}
	
}

 2、单条插入

package ;
/**
 * 单条插入数据
 */

import ;
import ;

import ;

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(?, ?, ?, ?, ?)";
		
			 ptmt = null;
			try {
				ptmt = (sql);
			} catch (SQLException e1) {
				();
			}
			
			try {
				(1, ());
				(2, ());
				(3, ());
				(4, ());
				(5, ());
				();//执行给定的SQL语句，该语句可能返回多个结果
			
			} catch (SQLException e) {
				();
			}
	}
	public static void main(String[] args) {
		OperationPaper operationPaper = new OperationPaper();
		List<String> listFile = ("E:\\huadai\\1996\\07\\21", false); // 文件列表
		
		for (String string : listFile) {   
			NewsPaper newsPaper = new NewsPaper(string);
			if (()) 
				(newsPaper);  //  插入数据库
		}
	}

}

3、批量插入

package ;

import ;
import ;
import ;

import ;

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(?, ?, ?, ?, ?)";
		 ptmt = null;
		
		try {
			(false);// 关闭事务
			ptmt = (sql);
		
		} catch (SQLException e2) {
			();
		} 

		for (NewsPaper paperaper : listNewsPaper) {
			
			try {
				(1, ());
				(2, ());
				(3, ());
				(4, ());
				(5, ());
				();	
				}	
			catch (SQLException e) {
				();
			}
		}
		try {
			();//执行给定的SQL语句，该语句可能返回多个结果
			();
		} catch (SQLException e) {
			();
		}
	}
	
	
	public static void main(String[] args) {
		OperaOnNewsPaper operation = new OperaOnNewsPaper();
		List<String> listFile = ("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 (()) {
				count++;
				(newsPaper);    // 新闻列表
				if (count % sizenum == 0) {
					//("ok");
					("    " + count);
					(listPaper); //插入数据库
					
					(count);
					();
				}
			}	
		}
		if (count %sizenum != 0) {
			(listPaper);
			("zui hou ");
		}		
	}
}

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