本文實例為大家分享了java連接MySQL數(shù)據(jù)庫實現(xiàn)單條和批量插入的具體代碼，供大家參考，具體內(nèi)容如下
成都創(chuàng)新互聯(lián)公司總部坐落于成都市區(qū)，致力網(wǎng)站建設服務有成都網(wǎng)站設計、網(wǎng)站制作、網(wǎng)絡營銷策劃、網(wǎng)頁設計、網(wǎng)站維護、公眾號搭建、小程序開發(fā)、軟件開發(fā)等為企業(yè)提供一整套的信息化建設解決方案。創(chuàng)造真正意義上的網(wǎng)站建設，為互聯(lián)網(wǎng)品牌在互動行銷領域創(chuàng)造價值而不懈努力！
本文插入數(shù)據(jù)庫的數(shù)據(jù)來源：java + dom4j.jar提取xml文檔內(nèi)容
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，這里設置成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、連接數(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語句，該語句可能返回多個結果
} 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 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 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();//執(zhí)行給定的SQL語句，該語句可能返回多個結果
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
public 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); //插入數(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)容，希望對大家的學習有所幫助，也希望大家多多支持創(chuàng)新互聯(lián)。
