SQL批量处理+JDBC操作大数据及工具类的封装
一、批处理 — 批量处理sql语句
在jdbc的url中添加rewriteBatchedStatements=true参数，可以提高批处理执行效率。
在我们进行大批量数据操作的时候，需要采用批处理的方式来提高程序的运行性能，目的是减少跟数据库交互的次数
1.批量执行多条sql语句，sql语句不相同（statement.addBatch(inserSql)）
import com.qf.utils.DBUtil;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class Test01 {
public static void main(String[] args) throws SQLException {
Connection connection = DBUtil.getConnection();
Statement statement = connection.createStatement();
String sql1 = "INSERT INTO student(name,sex,age,salary,course,password) VALUES('小橘子','男',25,15000,'Java','12345678');";
String sql2 = "UPDATE student set password = '66666666'";
//将SQL命令添加到Batch包
statement.addBatch(sql1);
statement.addBatch(sql2);
//将Batch包发送给MySQL
statement.executeBatch();
DBUtil.close(connection,statement,null);
}
}2.批量插入100条记录，sql语句相同，只是参数值不同(statement.addBatch()😉
import com.qf.utils.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Test02 {
public static void main(String[] args) throws SQLException {
Connection connection = DBUtil.getConnection();
String sql = "INSERT INTO student (name,sex,age,salary,course,password) VALUES(?,?,?,?,?,?); ";
PreparedStatement statement = connection.prepareStatement(sql);
for (int i = 1;i <= 1000;i++){
statement.setString(1,"盘古" + i);
statement.setString(2,"男");
statement.setInt(3,200);
statement.setFloat(4,15000);
statement.setString(5,"Java");
statement.setString(6,"123456789");
//将当前SQL命令添加到Batch包中
statement.addBatch();
}
statement.executeBatch();
DBUtil.close(connection,statement,null);
}
}3.加入事务，批量插入10004条记录，sql语句相同，只是参数值不同，并且分批次发送
import com.qf.utils.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Test03 {
public static void main(String[] args) throws SQLException {
DBUtil.startTransaction();
Connection connection = DBUtil.getConnection();
String sql = "INSERT INTO student (name,sex,age,salary,course,password) VALUES(?,?,?,?,?,?); ";
PreparedStatement statement = connection.prepareStatement(sql);
for (int i = 1;i <= 10005;i++){
statement.setString(1,"女娲" + i);
statement.setString(2,"女");
statement.setInt(3,100);
statement.setFloat(4,10000);
statement.setString(5,"Java");
statement.setString(6,"123456789");
//将当前SQL命令添加到Batch包中
statement.addBatch();
if(i %1000 == 0){
statement.executeBatch();
//清空Batch包中的SQL命令
statement.clearBatch();
}
}
statement.executeBatch();
DBUtil.commit();
DBUtil.close(connection,statement,null);
}
}二、JDBC操作大数据
1.CLOB：将长文本数据存储到数据库中
类型：TEXT、LONGTEXT
import com.qf.utils.DBUtil;
import org.junit.Test;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test01 {
@Test
public void test01() throws SQLException, FileNotFoundException {
//将长文本文本数据写入到数据库中
Connection connection = DBUtil.getConnection();
String sql = "insert into cblob(C_CLOB) values(?)";
PreparedStatement statement = connection.prepareStatement(sql);
//设置参数
File file = new File("小说.txt");
FileReader fr = new FileReader(file);
statement.setCharacterStream(1,fr,(int)file.length());
statement.executeUpdate();
DBUtil.close(connection,statement,null);
}
@Test
public void test02() throws SQLException, IOException {
//获取数据库中的长文本文本数据
Connection connection = DBUtil.getConnection();
String sql = " select * from cblob where id=1";
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
if(resultSet.next()){
Reader r = resultSet.getCharacterStream("C_CLOB");
FileWriter fw = new FileWriter("copy.txt");
char[] cs = new char[1024];
int len;
while((len=r.read(cs)) != -1){
fw.write(cs,0,len);
}
r.close();
fw.close();
}
DBUtil.close(connection,statement,resultSet);
}
}2.BLOB：将二进制文件存储到数据库中
类型：BLOB、LONGBLOB
import com.qf.utils.DBUtil;
import org.junit.Test;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test02 {
@Test
public void test01() throws SQLException, FileNotFoundException {
//将二进制数据写入到数据库中
Connection connection = DBUtil.getConnection();
String sql = "insert into cblob(B_BLOB) values(?)";
PreparedStatement statement = connection.prepareStatement(sql);
//设置参数
File file = new File("海底世界.jpg");
FileInputStream fis = new FileInputStream(file);
statement.setBinaryStream(1, fis,(int)file.length());
statement.executeUpdate();
DBUtil.close(connection,statement,null);
}
@Test
public void test02() throws SQLException, IOException {
//获取数据库中的二进制数据
Connection connection = DBUtil.getConnection();
String sql = " select * from cblob where id=1";
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
if(resultSet.next()){
InputStream in = resultSet.getBinaryStream("B_BLOB");
FileOutputStream out = new FileOutputStream("copy.jpg");
byte[] bs = new byte[1024];
int len;
while((len = in.read(bs)) != -1){
out.write(bs,0,len);
}
in.close();
out.close();
}
DBUtil.close(connection,statement,resultSet);
}
}三、数据库工具类的封装
JDBC所有内容应结合DBUtil工具包的使用
因为部分结构方法被封装，一劳永逸
封装一次永久使用，复制到项目即可使用
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
//数据库工具类
public class DBUtil {
private static String url;
private static String username;
private static String password;
static {
//获取配置文件对象
Properties properties = new Properties();
try {
//加载配置文件
properties.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties"));
} catch (IOException e) {
e.printStackTrace();
}
String driverName = properties.getProperty("driverName");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
try {
//导入驱动包
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private static ThreadLocal<Connection>local = new ThreadLocal<>();
//开启事务
public static void startTransaction() throws SQLException {
Connection connection = getConnection();
connection.setAutoCommit(false);
}
//提交事务
public static void commit() throws SQLException {
Connection connection = local.get();
if(connection != null){
connection.commit();
connection.close();
local.set(null);
}
}
//回滚事务
public static void rollback() throws SQLException {
Connection connection = local.get();
if (connection != null) {
connection.rollback();
connection.close();
local.set(null);
}
}
//获取连接对象
public static Connection getConnection() throws SQLException {
Connection connection = local.get();
if (connection == null) {
connection = DriverManager.getConnection(url, username, password);
local.set(connection);
}
return connection;
}
//关闭资源
public static void close(Connection connection, Statement statement, ResultSet resultSet){
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try {
if (connection.getAutoCommit()){//没有开启事务，MySQL自动管理提交
connection.close();
local.set(null);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//sql -- insert into xxx(username,name,password,salary,age) values(?,?,?,?,?)
//参数
//设置sql参数的方法
private static void setParameter(PreparedStatement statement,Object... params) throws SQLException {
for (int i = 0; i < params.length; i++) {
statement.setObject(i+1,params[i]);
}
}
//更新数据 -- （添加、删除、修改）
public static int commonUpdate(String sql,Object... params){
Connection connection = null;
PreparedStatement statement = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
setParameter(statement,params);
int num = statement.executeUpdate();
return num;
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(connection,statement,null);
}
return -1;
}
//主键回填
public static int commonInsert(String sql,Object... params){
Connection connection = null;
PreparedStatement statement = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
setParameter(statement,params);
statement.executeUpdate();
//获取主键
ResultSet resultSet = statement.getGeneratedKeys();
if(resultSet.next()){
int primaryKey = resultSet.getInt(1);
return primaryKey;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(connection,statement,null);
}
return -1;
}
//select * from user where id < 6
//查询数据
public static <T> List<T> commonQuery(Class<T> clazz,String sql,Object... params){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
setParameter(statement,params);
//获取表信息
ResultSetMetaData metaData = statement.getMetaData();
//获取表中字段的个数
int columnCount = metaData.getColumnCount();
//创建集合
List<T> list = new ArrayList<>();
resultSet = statement.executeQuery();
while(resultSet.next()){//1 女娲 女 22 12000 java 00000000
//利用反射创建实体类对象
T obj = clazz.newInstance();
//循环获取字段名，并利用反射机制添加到实体类对象中
for (int i = 1; i <= columnCount; i++) {
//字段名
String columnName = metaData.getColumnName(i);
//获取字段名对应的值
Object columnValue = resultSet.getObject(columnName);
//获取属性对象
Field field = getField(clazz, columnName);
if(field != null){
field.setAccessible(true);
field.set(obj,columnValue);
}
}
list.add(obj);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} finally {
close(connection,statement,resultSet);
}
return null;
}
//利用反射获取实体类中的属性对象
private static Field getField(Class<?> clazz,String fieldName){
for(Class<?> c = clazz;c != null;c=c.getSuperclass()){
try {
Field field = c.getDeclaredField(fieldName);
return field;
} catch (NoSuchFieldException e) {
}
}
return null;
}
}四、使用数据库工具类 操作数据库
public class Student {
private int id;
private String name;
private String sex;
private int age;
private float salary;
private String course;
private String password;
public Student() {
}
public Student(int id, String name, String sex, int age, float salary, String course, String password) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
this.salary = salary;
this.course = course;
this.password = password;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public float getSalary() {
return salary;
}
public void setSalary(float salary) {
this.salary = salary;
}
public String getCourse() {
return course;
}
public void setCourse(String course) {
this.course = course;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", age=" + age +
", salary=" + salary +
", course='" + course + '\'' +
", password='" + password + '\'' +
'}';
}
}public class User {
private int id;
private String name;
private float money;
public User() {
}
public User(int id, String name, float money) {
this.id = id;
this.name = name;
this.money = money;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getMoney() {
return money;
}
public void setMoney(float money) {
this.money = money;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", money=" + money +
'}';
}
}import com.qf.pojo.Student;
import com.qf.pojo.User;
import com.qf.utils.DBUtil;
import org.junit.Test;
import java.sql.*;
import java.util.List;
public class Test01 {
@Test
public void test01() {
//添加数据
String sql = "INSERT INTO student(name,sex,age,salary,course,password) VALUES(?,?,?,?,?,?);";
DBUtil.commonUpdate(sql,"abc","男",22,12000,"Java","12344321");
}
@Test
public void test02() {
//删除数据
String sql = "delete from student where id>?";
DBUtil.commonUpdate(sql,10);
}
@Test
public void test03() {
//修改数据
String sql = "update student set salary=? where id=?";
DBUtil.commonUpdate(sql,30000,3);
}
@Test
public void test04() {
//主键回填
String sql = "INSERT INTO student(name,sex,age,salary,course,password) VALUES(?,?,?,?,?,?);";
int primaryKey = DBUtil.commonInsert(sql, "孙策", "男", 22, 12000, "Java", "12344321");
System.out.println(primaryKey);
}
@Test
public void test05() {
//查询数据 -- 查询学生表
String sql = "select * from student where id < ?";
List<Student> stuList = DBUtil.commonQuery(Student.class, sql, 5);
for (Student stu:stuList) {
System.out.println(stu);
}
}
@Test
public void test06() {
//查询数据 -- 查询用户表
String sql = "select * from user";
List<User> userList = DBUtil.commonQuery(User.class, sql);
for (User user:userList) {
System.out.println(user);
}
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
