jdbc之批处理了解一下

最新推荐文章于 2021-02-16 05:08:41 发布

原创最新推荐文章于 2021-02-16 05:08:41 发布 · 164 阅读

0 ·

CC 4.0 BY-SA版权

JAVA Web基础同时被 2 个专栏收录

60 篇文章

订阅专栏

SQL

26 篇文章

订阅专栏

本文介绍了一种使用Java JDBC进行SQL批处理的方法，通过批量插入学生数据来提高数据库操作效率。文章详细展示了实体类、DAO接口及其实现类的编写过程，并提供了一个完整的批处理示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

什么时候需要批处理？

需要批量执行sql语句！

批处理相关方法

        void addBatch(String sql)     添加批处理
        void clearBatch()            清空批处理
        int[] executeBatch()         执行批处理

实现：

实体类：
entity.Student

package entity;

public class Student {
    private int id;
    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;
    }
    private String name;

}

Dao接口：
StudentDao.StudentDao：

package StudentDao;

import java.util.List;

import entity.Student;

public interface StudentDao {
    void save(List<Student> list);
}

Dao实现类：
StudentDaoimpl.StudentDaoimpl:

package StudentDaoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.List;

import StudentDao.StudentDao;
import entity.Student;
import jdbcUtil.jdbcUtil;

public class StudentDaoimpl implements StudentDao {
    private Connection connection;
    private PreparedStatement preparedStatement;

    @Override
    public void save(List<Student> list) {
        String sql = "INSERT INTO teacher value(?,?)";

        try {
            connection = jdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < list.size(); i++) {
                Student student = list.get(i);
                preparedStatement.setInt(1, student.getId());
                preparedStatement.setString(2, student.getName());
                preparedStatement.addBatch();
                if (i % 5 == 0) {
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                }
            }
            int[] number = preparedStatement.executeBatch();
            for (int i = 0; i < number.length; i++) {
                System.out.println(number[i]);
            }
            preparedStatement.clearBatch();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            jdbcUtil.close(connection, preparedStatement);
        }

    }
}

JDBC工具类：

package jdbcUtil;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class jdbcUtil {
    private static String url = null;
    private static String user = null;
    private static String password = null;
    private static String driverClass = null;
    static {
        try {
            Properties properties = new Properties();
            InputStream in = jdbcUtil.class.getResourceAsStream("/db.properties");
            properties.load(in);
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            driverClass = properties.getProperty("driverClass");
            Class.forName(driverClass);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("驱程程序注册出错");
        }
    }

    public static Connection getConnection() {
        try {
            Connection cnn = DriverManager.getConnection(url, user, password);
            return cnn;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    public static void close(Connection cnn, Statement st) {
        if (st != null) {
            try {
                st.close();
                st = null;
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        if (cnn != null) {
            try {
                cnn.close();
                cnn = null;
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
    }

    public static void close(Connection cnn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
                rs = null;
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        if (st != null) {
            try {
                st.close();
                rs = null;
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        if (cnn != null) {
            try {
                cnn.close();
                cnn = null;
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
    }
}

批处理：

    public static void main(String[] args) {
        List<Student> list=new ArrayList<Student>();
        for(int i=0; i<14; i++) {
            Student student = new Student();
            student.setId(i);
            student.setName("codingCoge:"+i);
            list.add(student);
        }
        StudentDao studentDao=new StudentDaoimpl();
        studentDao.save(list);
    }