jdbc大量插入数据(prepareStatement)

时间:2020-12-24 21:41:05

PrepareStatement适用于较小规模的数据插入处理,大规模的使用Statement比较好(防止sql注入问题:可以使用字符串过滤)

DBUTtil.java:数据库连接类


/**
* @version
* @description
*/

package com.xasmall.txt;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
* 数据库连接类
* @author 26248
*
*/

public class DBUTtil {
public static Connection jdbaload() {
Properties prop=new Properties();
Connection conn=null;
try {
prop.load(DBUTtil.class.getClassLoader().getResourceAsStream("config.properties"));
} catch (IOException e1) {
System.out.println("文件为读取成功!");
}
String url=prop.getProperty("url");
String driver=prop.getProperty("driver");
String user=prop.getProperty("user");
String password=prop.getProperty("password");
try {
Class.forName(driver);
conn=DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
System.out.println("未加载mysql驱动!");
} catch (SQLException e) {
System.out.println("未连接mysql!");
}
if(conn!=null) {
return conn;
}
else {
throw new Error("数据库连接错误!");
}
}
}


User.java:用户数据类


/**
* @version
* @description
*/

package com.xasmall.txt;

/**
* @author 26248
*
*/

public class User {
private int id;
private String name;
private String academy;
/**
* @return the id
*/

public int getId() {
return id;
}
/**
* @param id the id to set
*/

public void setId(int id) {
this.id = id;
}
/**
* @return the name
*/

public String getName() {
return name;
}
/**
* @param name the name to set
*/

public void setName(String name) {
this.name = name;
}
/**
* @return the academy
*/

public String getAcademy() {
return academy;
}
/**
* @param academy the academy to set
*/

public void setAcademy(String academy) {
this.academy = academy;
}
}

DealTxt.java:处理文本数据类:
/**
* @version
* @description
*/

package com.xasmall.txt;

import java.io.BufferedReader;
import java.io.FileReader;
import java.util.ArrayList;

/**
* 将读取到的每一行处理,得到用户数据
* @author 26248
*
*/

public class DealTxt {
public static ArrayList<String> readerLine(String fileName) throws Exception {
FileReader filed=new FileReader(fileName);
ArrayList<String> list=new ArrayList<String>();
BufferedReader bf=new BufferedReader(filed);
String str=null;
while((str=bf.readLine())!=null) {
list.add(str);
}
bf.close();
return list;
}
public static User dealTxt(String line) {
User user=new User();
String[] str=line.split(",");
user.setId(Integer.parseInt(str[0]));
user.setName(str[1]);
user.setAcademy(str[2]);
return user;
}
}

UserDAO.java:数据库插入实现类:
/**
* @version
* @description
*/

package com.xasmall.txt;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
* @author 26248
*
*/

public class UserDAO {
public static void insert(Connection conn,ArrayList<User> list) {
try {
conn=DBUTtil.jdbaload();
conn.setAutoCommit(false);
String sql="insert into studentinfo(id,username,academy) value(?,?,?)";
PreparedStatement psmt=conn.prepareStatement(sql);
for(User user:list) {
psmt.setObject(1, user.getId());
psmt.setObject(2, user.getName());
psmt.setObject(3, user.getAcademy());
psmt.addBatch();
}
psmt.executeBatch();
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("SQLException error!");
}
}
}

Test.java:测试类:
/**
* @version
* @description
*/

package com.xasmall.txt;

import java.sql.Connection;
import java.util.ArrayList;

/**
* @author 26248
*
*/

public class Test {
public static void main(String[] args) throws Exception {
ArrayList<User> user=new ArrayList<User>();
ArrayList<String> list=DealTxt.readerLine("src/data.txt");
for(String line:list) {
user.add(DealTxt.dealTxt(line));
}
for(User us:user) {
System.out.println("user_id:-->"+us.getId()+"user_name:--->"+us.getName()+"user_academy:--->"+us.getAcademy());
}
Connection conn = null;
UserDAO.insert(conn, user);
}
}

文本数据:
config.properties:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/?
user=root
password=?
data.txt:
1008611,张三,计算机学院
1008511,李四,计算机学院
1003233,元丰,计算机学院
784327,哈防护,计算机学院