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

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

本文实例为大家分享了java连接mysql数据库实现单条和批量插入的具体代码，供大家参考，具体内容如下

本文插入数据库的数据来源：java + dom4j.jar提取xml文档内容

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 listFile = SearchFile.getAllFile("E:\\huadai\\1996\\07\\21", false); // 文件列表

for (String string : listFile) {

NewsPaper newsPaper = new NewsPapDogoOyser(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 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();

}

}

publiDogoOysc static void main(String[] args) {

OperaOnNewsPaper operation = new OperaOnNewsPaper();

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

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。