1.Batching with Statement Object
1) Batching with Create
package edu.xmu.jdbc.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.List; import edu.xmu.jdbc.bean.Student; public class StudentDao { private String url = "jdbc:mysql://localhost:3306/jdbctest"; private String username = "****"; private String password = "****"; static { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public void batchCreateStudent(List<Student> studentList) { Connection conn = null; Statement statement = null; try { conn = DriverManager.getConnection(url, username, password); statement = conn.createStatement(); conn.setAutoCommit(false); for (Student student : studentList) { String sql = "insert into student(name, gender, age) values ('" + student.getName() + "', '" + student.getGender() + "', " + student.getAge() + ")"; statement.addBatch(sql); } int[] count = statement.executeBatch(); conn.commit(); for (int i = 0; i < count.length; i++) { System.out.println(count[i]); } } catch (SQLException e) { e.printStackTrace(); } finally { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
2) Batching with Retrieve
package edu.xmu.jdbc.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import edu.xmu.jdbc.bean.Student; public class StudentDao { private String url = "jdbc:mysql://localhost:3306/jdbctest"; private String username = "****"; private String password = "****"; static { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } // There is no a mechanism for select batches. public List<Student> batchRetrieveStudent(List<String> studentNameList) { Connection conn = null; Statement statement = null; List<Student> studentList = new ArrayList<Student>(); try { conn = DriverManager.getConnection(url, username, password); statement = conn.createStatement(); conn.setAutoCommit(false); String sql = "select name, gender, age from student where name in ("; for (String studentName : studentNameList) { sql += "'" + studentName + "', "; } sql = sql.substring(0, sql.length() - 2); sql += ")"; System.out.println("Executing sql [" + sql + "]"); ResultSet resultSet = statement.executeQuery(sql.toString()); while (resultSet.next()) { String name = resultSet.getString("name"); String gender = resultSet.getString("gender"); int age = resultSet.getInt("age"); Student student = new Student(name, gender, age); studentList.add(student); } } catch (SQLException e) { e.printStackTrace(); } finally { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } return studentList; } }
Comments: As the statements in the link below, there in no mechanism for batch query. Probably because there is no apparant need for that.
As others have recommanded, you can simply retrieve all the rows you want at once by constructing the sql listed above.
3) Batching with Update
package edu.xmu.jdbc.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.List; import edu.xmu.jdbc.bean.Student; public class StudentDao { private String url = "jdbc:mysql://localhost:3306/jdbctest"; private String username = "****"; private String password = "****"; static { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public void batchUpdateStudent(List<Student> studentList) { Connection conn = null; Statement statement = null; try { conn = DriverManager.getConnection(url, username, password); statement = conn.createStatement(); conn.setAutoCommit(false); for (Student student : studentList) { String sql = "update student set gender='" + student.getGender() + "', age=" + student.getAge() + " where name='" + student.getName() + "'"; statement.addBatch(sql); } int[] count = statement.executeBatch(); conn.commit(); for (int i = 0; i < count.length; i++) { System.out.println(count[i] + " rows affected."); } } catch (SQLException e) { e.printStackTrace(); } finally { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
4) Batching with Delete
package edu.xmu.jdbc.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.List; public class StudentDao { private String url = "jdbc:mysql://localhost:3306/jdbctest"; private String username = "****"; private String password = "****"; static { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public void batchDeleteStudent(List<String> studentNameList) { Connection conn = null; Statement statement = null; try { conn = DriverManager.getConnection(url, username, password); statement = conn.createStatement(); conn.setAutoCommit(false); for (String studentName : studentNameList) { String sql = "delete from student where name='" + studentName + "'"; statement.addBatch(sql); } int[] count = statement.executeBatch(); conn.commit(); for (int i = 0; i < count.length; i++) { System.out.println(count[i] + " rows affected."); } } catch (SQLException e) { e.printStackTrace(); } finally { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
2. Batching with PreparedStatement Object
1) Batching with Create
public void batchCreateStudent(List<Student> studentList) { Connection conn = null; PreparedStatement statement = null; String sql = "insert into student(name, gender, age) values(?, ?, ?)"; try { conn = DriverManager.getConnection(url, username, password); statement = conn.prepareStatement(sql); conn.setAutoCommit(false); for (Student student : studentList) { statement.setString(1, student.getName()); statement.setString(2, student.getGender()); statement.setInt(3, student.getAge()); statement.addBatch(); } int[] count = statement.executeBatch(); conn.commit(); for (int i = 0; i < count.length; i++) { System.out.println(count[i] + " rows affected."); } } catch (SQLException e) { e.printStackTrace(); } finally { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
Comments: Attention that we should use "statement.addBatch();" instead of "statement.addBatch(sql);"
2) Batching with Retrieve
// There is no a mechanism for select batches. public List<Student> batchRetrieveStudent(List<String> studentNameList) { Connection conn = null; PreparedStatement statement = null; List<Student> studentList = new ArrayList<Student>(); try { conn = DriverManager.getConnection(url, username, password); String sql = "select name, gender, age from student where name in ("; for (int i = 0; i < studentNameList.size(); i++) { sql += "?, "; } sql = sql.substring(0, sql.length() - 2); sql += ")"; statement = conn.prepareStatement(sql); conn.setAutoCommit(false); for (int i = 0; i < studentNameList.size(); i++) { String studentName = studentNameList.get(i); statement.setString(i + 1, studentName); } ResultSet resultSet = statement.executeQuery(); while (resultSet.next()) { String name = resultSet.getString("name"); String gender = resultSet.getString("gender"); int age = resultSet.getInt("age"); Student student = new Student(name, gender, age); studentList.add(student); } } catch (SQLException e) { e.printStackTrace(); } finally { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } return studentList; }
Comments: Also, attention that we don't have and even don't need the batch select mechanism.
3) Batching with Update
public List<Student> batchUpdateStudent(List<Student> studentList) { Connection conn = null; PreparedStatement statement = null; try { conn = DriverManager.getConnection(url, username, password); String sql = "update student set gender=?, age=? where name=?"; statement = conn.prepareStatement(sql); conn.setAutoCommit(false); for (Student student : studentList) { statement.setString(1, student.getGender()); statement.setInt(2, student.getAge()); statement.setString(3, student.getName()); statement.addBatch(); } int[] count = statement.executeBatch(); conn.commit(); for (int i = 0; i < count.length; i++) { System.out.println(count[i] + " rows affected."); } } catch (SQLException e) { e.printStackTrace(); } finally { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } return studentList; }
4) Batching with Delete
public void batchDeleteStudent(List<String> studentNameList) { Connection conn = null; PreparedStatement statement = null; try { conn = DriverManager.getConnection(url, username, password); String sql = "delete from student where name=?"; statement = conn.prepareStatement(sql); conn.setAutoCommit(false); for (String studentName : studentNameList) { statement.setString(1, studentName); statement.addBatch(); } int[] count = statement.executeBatch(); conn.commit(); for (int i = 0; i < count.length; i++) { System.out.println(count[i] + " rows affected."); } } catch (SQLException e) { e.printStackTrace(); } finally { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
Comments:
1) As we can see, there is a lot of redundancy code of creating statements and handling exceptions.
Also, we may forget to close resultset, statements and connection.
Therefore, we need a more efficient framework to avoid these defacts. Here come Sping-JDBC-Framework.
2) Also, we've seen that every time we execute a sql, we just created a new connection which is time consuming.
Connection-Pool mechanism offers a better way of managing the connection resources.
Reference Links:
1. http://stackoverflow.com/questions/9853197/jdbc-batch-query-for-high-performance offers justification why we do not need batch select
2. http://www.tutorialspoint.com/jdbc/statement-batching-example.htm offers detailed tutorials & example for batch CRD
3. http://www.javaranch.com/journal/200510/Journal200510.jsp#a2 offers several alternative options for batch select.
相关推荐
|:-------:|:-------:|:-------:|:-------:|:-------:|:-------:|:-------:|:-------:|:-------:|:-------:| | 25 | 28.57 | 28.83 | 28.68 | 28.96 | 28.74 | 28.92 | **29.23** | **29.16** | **29.17** | - Set...
MySQL JDBC驱动包8.0, mysql-connector-java-8.0.29。 供Java连接数据库使用。
完整英文版 IEC 61512-2:2001 Batch control - Part 2:Data structures and guidelines for languages(批量控制--第二部分:数据结构和语言的准则)。本标准关于批处理控制的这一部分定义了描述应用于过程工业的...
Call the cross-build.cmd batch file suppling the version of Visual Studio as the first parameter. See the contents of the cross-build.cmd file for all possibilities. This will build the Visual ...
1: Introduction to Spring Integration 2: Enterprise Integration Fundamentals Part 2 - Messaging 3: Messages and Channels 4: Message Endpoints 5: Getting Down to Business 6: Go beyond ...
资源分类:Python库 所属语言:Python 资源全名:nf-batch-runner-0.0.11.tar.gz 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
E:\H\bat\200509162031592621.rar E:\H\bat\63个超级实用的BAT批处理程序-提高办公效率几百倍.txt E:\H\bat\bat2exe E:\H\bat\python-001-py2exe直接双击运行将1py转换成1exe.bat E:\H\bat\仅文件夹目录-20200307....
资源分类:Python库 所属语言:Python 资源全名:tencentcloud-sdk-python-batch-3.0.381.tar.gz 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
资源分类:Python库 所属语言:Python 资源全名:tencentcloud-sdk-python-batch-3.0.564.tar.gz 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
python库。 资源全名:mypy-boto3-batch-1.15.16.0.tar.gz
11)..Fixed: Possible failure to handle/process stack overflow exceptions 12)..Changed: VCL/CLX/FMX now will assign Application.OnException handler when low-level hooks are disabled EurekaLog 7.2 ...
--batch-size 8 --device 0 --hyp data/hyp.scratch.yaml 蒸馏训练: python train.py --weights weights/yolov5s.pt \ --cfg models/yolov5s.yaml --data data/voc.yaml --epochs 50 \ --batch-size 8 --device...
python库。 资源全名:mypy-boto3-batch-1.17.90.tar.gz
资源来自pypi官网。 资源全名:mypy-boto3-batch-1.17.103.tar.gz
资源来自pypi官网。 资源全名:mypy-boto3-batch-1.14.52.0.tar.gz
资源来自pypi官网。 资源全名:tencentcloud-sdk-python-batch-3.0.517.tar.gz
资源来自pypi官网。 资源全名:tencentcloud-sdk-python-batch-3.0.464.tar.gz
关于xeus-sql 主页: : 软件包许可证:BSD-3-Clause 原料许可证: 简介:基于xeus和SOCISQL的Jupyter内核文档: : 当前构建状态蔚蓝 变体状态linux_64 osx_64 win_64当前发行信息姓名资料下载版本平台类安装xeus-sql...
资源来自pypi官网。 资源全名:mypy-boto3-batch-1.18.51.tar.gz
资源来自pypi官网。 资源全名:mypy-boto3-batch-1.14.49.0.tar.gz