Spring JDBC多批次操作

来源：互联网发布：db2和sqlserver 编辑：程序博客网时间：2024/05/21 21:46

http://www.yiibai.com/springjdbc/multiple_batches.html

参考地址:

http://www.yiibai.com/springjdbc/jdbctemplate.html#article-start

http://www.yiibai.com/springjdbc/object_batch.html#article-start

http://www.yiibai.com/springjdbc/batch_operation.html

以下示例将演示如何使用spring jdbc在单个调用中进行多批次更新。我们将在批量大小为1的多批次操作中更新student表中的记录。

student表的结果如下 -

CREATE TABLE student(   id   INT NOT NULL AUTO_INCREMENT,   name VARCHAR(20) NOT NULL,   age  INT NOT NULL,   PRIMARY KEY (id));SQL

语法：

String SQL = "update Student set age = ? where id = ?";int[][] updateCounts = jdbcTemplateObject.batchUpdate(SQL,students,1,   new ParameterizedPreparedStatementSetter<Student>() {      public void setValues(PreparedStatement ps, Student student)         throws SQLException {         ps.setInt(1, student.getAge());         ps.setInt(2, student.getId());          }   });Java

在上面语法中 -

SQL - 更新语句以更新学生的年龄。
ParameterizedPreparedStatementSetter - 批量执行者，在PerparedStatement中设置值，每个项目由Student对象列表识别。
jdbcTemplateObject - NamedParameterJdbcTemplate对象来更新数据库中的学生对象。
updateCounts - int[][]数组包含每个更新查询更新的行计数(数量)。

创建项目

要了解上面提到的Spring JDBC相关的概念，这里创建一个项目用来演示如何执行多次更新批处理操作。打开Eclipse IDE，并按照以下步骤创建一个名称为：MultipleBatchOperation 的Spring应用程序项目。

步骤说明

参考第一个Spring JDBC项目来创建一个Maven项目 - http://www.yiibai.com/springjdbc/first_application.html
更新bean配置并运行应用程序。

完整的项目结构如下所示 -

文件 : pom.xml 的内容 -

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">    <modelVersion>4.0.0</modelVersion>    <groupId>com.yiibai</groupId>    <artifactId>MultipleBatchOperation</artifactId>    <version>0.0.1-SNAPSHOT</version>    <packaging>jar</packaging>    <name>MultipleBatchOperation</name>    <url>http://maven.apache.org</url>    <dependencies>        <dependency>            <groupId>junit</groupId>            <artifactId>junit</artifactId>            <version>3.8.1</version>            <scope>test</scope>        </dependency>        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <version>5.1.40</version>        </dependency>        <dependency>            <groupId>org.springframework</groupId>            <artifactId>spring-jdbc</artifactId>            <version>4.1.0.RELEASE</version>        </dependency>        <dependency>            <groupId>org.springframework</groupId>            <artifactId>spring-context</artifactId>            <version>4.1.4.RELEASE</version>        </dependency>    </dependencies></project>

以下是数据访问对象接口文件：StudentDAO.java的代码内容：

package com.yiibai;import java.util.List;import javax.sql.DataSource;public interface StudentDAO {    /**     * This is the method to be used to initialize database resources ie.     * connection.     */    public void setDataSource(DataSource ds);    /**     * This is the method to be used to list down all the records from the     * Student table.     */    public List<Student> listStudents();    public void create(String name, Integer age);    public void batchUpdate(final List<Student> students);}Java

以下是文件：Student.java的代码内容：

package com.yiibai;public class Student {    private Integer age;    private String name;    private Integer id;    public void setAge(Integer age) {        this.age = age;    }    public Integer getAge() {        return age;    }    public void setName(String name) {        this.name = name;    }    public String getName() {        return name;    }    public void setId(Integer id) {        this.id = id;    }    public Integer getId() {        return id;    }}Java

以下是文件：StudentMapper.java的代码内容：

package com.yiibai;import java.sql.ResultSet;import java.sql.SQLException;import org.springframework.jdbc.core.RowMapper;public class StudentMapper implements RowMapper<Student> {   public Student mapRow(ResultSet rs, int rowNum) throws SQLException {      Student student = new Student();      student.setId(rs.getInt("id"));      student.setName(rs.getString("name"));      student.setAge(rs.getInt("age"));      return student;   }}Java

以下是文件：StudentJDBCTemplate.java的代码内容：

package com.yiibai;import java.sql.PreparedStatement;import java.util.List;import javax.sql.DataSource;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;import java.sql.SQLException;public class StudentJDBCTemplate implements StudentDAO {    private DataSource dataSource;    private JdbcTemplate jdbcTemplateObject;    public void setDataSource(DataSource dataSource) {        this.dataSource = dataSource;        this.jdbcTemplateObject = new JdbcTemplate(dataSource);    }    public List<Student> listStudents() {        String SQL = "select * from Student";        List<Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());        return students;    }    public void batchUpdate(final List<Student> students) {        String SQL = "update Student set age = ? where id = ?";        int[][] updateCounts = jdbcTemplateObject.batchUpdate(SQL, students, 1,                new ParameterizedPreparedStatementSetter<Student>() {                    public void setValues(PreparedStatement ps, Student student) throws SQLException {                        ps.setInt(1, student.getAge());                        ps.setInt(2, student.getId());                    }                });        System.out.println("Records updated!");    }    public void create(String name, Integer age) {        // TODO Auto-generated method stub        String insertQuery = "insert into student (name, age) values (?, ?)";        jdbcTemplateObject.update(insertQuery, name, age);        System.out.println("Created Record Name = " + name + " Age = " + age);        return;    }}Java

以下是程序执行入口文件：MainApp.java的代码内容：

package com.yiibai;import java.util.ArrayList;import java.util.List;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import com.yiibai.StudentJDBCTemplate;public class MainApp {    public static void main(String[] args) {        ApplicationContext context = new ClassPathXmlApplicationContext("application-beans.xml");        StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate) context.getBean("studentJDBCTemplate");        System.out.println("------Records Creation--------");        studentJDBCTemplate.create("Maxsu", 21);        studentJDBCTemplate.create("Curry", 22);        studentJDBCTemplate.create("Xiaowei", 25);        List<Student> initialStudents = studentJDBCTemplate.listStudents();        System.out.println("Initial Students");        for (Student student2 : initialStudents) {            System.out.print("ID : " + student2.getId());            System.out.println(", Age : " + student2.getAge());        }        Student student = new Student();        student.setId(1);        student.setAge(17);        Student student1 = new Student();        student1.setId(3);        student1.setAge(18);        List<Student> students = new ArrayList<Student>();        students.add(student);        students.add(student1);        studentJDBCTemplate.batchUpdate(students);        List<Student> updatedStudents = studentJDBCTemplate.listStudents();        System.out.println("Updated Students");        for (Student student3 : updatedStudents) {            System.out.print("ID : " + student3.getId());            System.out.println(", Age : " + student3.getAge());        }    }}Java

以下是Bean和数据库配置文件：application-beans.xml的代码内容：

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xsi:schemaLocation="http://www.springframework.org/schema/beans    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">    <!-- Initialization for data source -->    <bean id="dataSource"        class="org.springframework.jdbc.datasource.DriverManagerDataSource">        <property name="driverClassName" value="com.mysql.jdbc.Driver" />        <property name="url" value="jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true" />        <property name="username" value="root" />        <property name="password" value="123456" />    </bean>    <!-- Definition for studentJDBCTemplate bean -->    <bean id="studentJDBCTemplate" class="com.yiibai.StudentJDBCTemplate">        <property name="dataSource" ref="dataSource" />    </bean></beans>XML

注意： application-beans.xml 文件的位置是：{workspace}/fistapp/src/main/java 目录，如果放置错了，程序可能会因为找不到此配置文件而出错。

完成创建源代码和bean和数据库连接信息的文件配置后，运行应用程序。这里先简单说明一下运行的步骤，在项目名称(MultipleBatchOperation)上点击右键，在弹出的菜单中选择：【Run As】-> 【Maven test】

在运行测试成功后，应该会输出类似以下内容(含有 BUILD SUCCESS 的信息) 。
接下来，点击类文件：MainApp.java 选择【Run As】->【Java Application】，如果应用程序一切正常，这将打印以下消息：

------Records Creation--------Created Record Name = Maxsu Age = 21Created Record Name = Curry Age = 22Created Record Name = Xiaowei Age = 25Initial StudentsID : 1, Age : 21ID : 2, Age : 22ID : 3, Age : 25Records updated!Updated StudentsID : 1, Age : 17ID : 2, Age : 22ID : 3, Age : 18Shell

数据库中的student表中的数据被修改为：

mysql> select * from student;+----+--------+-----+| id | name   | age |+----+--------+-----+|  1 | Maxsu  |  17 ||  2 | Curry  |  22 ||  3 | Xiaowei |  18 |+----+--------+-----+3 rows in setmysql>SQL

易百教程移动端：请扫描本页面底部(右侧)二维码并关注微信公众号，回复："教程" 选择相关教程阅读或直接访问：http://m.yiibai.com 。

上一篇：Spring JDBC对象批量操作下一篇：Spring JDBC JdbcTemplate类示例

加QQ群啦，易百教程官方技术学习群

阅读全文

0 0