Spring深入浅出（十四），JDBC，Oracle数据库基本CRUD操作，以及获得插入记录的主键值

写一个简单示例，阐述Spring环境、Oracle数据库之下，实体表的CRUD 操作；包括获得插入记录的主键值。

首先，需要在项目中引用相关包，包括Spring JDBC包、Spring事务驱动包、Oracle数据库驱动包：

• spring-jdbc-5.2.3.RELEASE.jar
• spring-tx-5.2.3.RELEASE.jar
• ojdbc6.jar

一、创建表，创建序列（Oracle数据库，序列用于自增长主键）

CREATE TABLE STUDENT
  (
    ID   NUMBER NOT NULL ,
    NAME VARCHAR2(20 BYTE) NOT NULL ,
    AGE  NUMBER NOT NULL ,
    CONSTRAINT STUDENT_PK PRIMARY KEY (ID) 
  );

create sequence SEQ_STUDENT;

二、创建实体类

package com.clzhang.spring.demo.jdbc;

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;
    }
}

三、创建数据访问对象接口文件（DAO）

package com.clzhang.spring.demo.jdbc;

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);

    // 插入一条记录
    public void create(String name, Integer age);
    
    // 插入一条记录，并返回主键值
    public int insert(String name, Integer age);

    /**
     * This is the method to be used to list down a record from the Student table
     * corresponding to a passed student id.
     */
    public Student getStudent(Integer id);

    /**
     * This is the method to be used to list down all the records from the Student
     * table.
     */
    public List<Student> listStudents();

    /**
     * This is the method to be used to delete a record from the Student table
     * corresponding to a passed student id.
     */
    public void delete(Integer id);

    /**
     * This is the method to be used to update a record into the Student table.
     */
    public void update(Integer id, Integer age);
}

四、创建Mapper，Sping中的RowMapper可以将数据中的每一行数据封装成用户定义的类。

package com.clzhang.spring.demo.jdbc;

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;
   }
}

五、定义的 DAO 接口的实现类

package com.clzhang.spring.demo.jdbc;

import java.util.List;
import javax.sql.*;
import java.sql.*;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

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 void create(String name, Integer age) {
          String SQL = "insert into Student (id, name, age) values (SEQ_STUDENT.nextval, ?, ?)";     
          jdbcTemplateObject.update( SQL, name, age);
          System.out.println("Created Record Name = " + name + " Age = " + age);
        return;
    }
    
    // 需要返回主键值的方法
    public int insert(final String name, final Integer age) {
        final String SQL = "insert into Student (id, name, age) values (SEQ_STUDENT.nextval, ?, ?)";
        
        KeyHolder keyHolder = new GeneratedKeyHolder(); 
        jdbcTemplateObject.update(new PreparedStatementCreator() { 
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { 
                PreparedStatement ps = conn.prepareStatement(SQL,new String[] {"id"}); 
                   ps.setString(1, name);  
                   ps.setInt(2, age);  
               
                   return ps;  
                  } 
             },keyHolder); 
        return keyHolder.getKey().intValue();    
    }
    
    public Student getStudent(Integer id) {
        String SQL = "select * from Student where id = ?";
        Student student = jdbcTemplateObject.queryForObject(SQL, new Object[] { id }, new StudentMapper());
        return student;
    }

    public List<Student> listStudents() {
        String SQL = "select * from Student";
        List<Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
        return students;
    }

    public void delete(Integer id) {
        String SQL = "delete from Student where id = ?";
        jdbcTemplateObject.update(SQL, id);
        System.out.println("Deleted Record with ID = " + id);
        return;
    }

    public void update(Integer id, Integer age) {
        String SQL = "update Student set age = ? where id = ?";
        jdbcTemplateObject.update(SQL, age, id);
        System.out.println("Updated Record with ID = " + id);
        return;
    }
}

六、创建主程序

package com.clzhang.spring.demo.jdbc;

import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class MainApp {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
        StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate) context.getBean("studentJDBCTemplate");
        System.out.println("------Insert three records--------");
        studentJDBCTemplate.create("张三", 11);
        studentJDBCTemplate.create("李四", 12);
        int id = studentJDBCTemplate.insert("赵五", 13);
        System.out.println("New Student id: " + id);
        System.out.println("------Query--------");
        List<Student> students = studentJDBCTemplate.listStudents();
        for (Student record : students) {
            System.out.print("ID : " + record.getId());
            System.out.print(", Name : " + record.getName());
            System.out.println(", Age : " + record.getAge());
        }
        System.out.println("----Update one record-----");
        studentJDBCTemplate.update(id, 20);
        System.out.println("----Query again-----");
        Student student = studentJDBCTemplate.getStudent(id);
        System.out.print("ID : " + student.getId());
        System.out.print(", Name : " + student.getName());
        System.out.println(", Age : " + student.getAge());

        System.out.println("----Delete-----");
        studentJDBCTemplate.delete(id);
        // 此句将引起异常
//        student = studentJDBCTemplate.getStudent(id); 
}
}

七、创建配置文件 

<?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="oracle.jdbc.driver.OracleDriver"/>
      <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"/>
      <property name="username" value="mytest"/>
      <property name="password" value="test001"/>
   </bean>

   <!-- Definition for studentJDBCTemplate bean -->
   <bean id="studentJDBCTemplate" 
      class="com.clzhang.spring.demo.jdbc.StudentJDBCTemplate">
      <property name="dataSource"  ref="dataSource" />    
   </bean>

</beans>

 八、运行

------Insert three records--------
Created Record Name = 张三 Age = 11
Created Record Name = 李四 Age = 12
New Student id: 4
------Query--------
ID : 2, Name : 张三, Age : 11
ID : 3, Name : 李四, Age : 12
ID : 4, Name : 赵五, Age : 13
----Update one record-----
Updated Record with ID = 4
----Query again-----
ID : 4, Name : 赵五, Age : 20
----Delete-----
Deleted Record with ID = 4

本文参考：https://www.w3cschool.cn/wkspring/iuck1mma.html