Spring入门学习笔记（4）——JDBC的使用

目录

• spring jdbc框架概览

spring jdbc框架概览

使用传统的jdbc连接数据库，需要编写不必要的代码来处理异常、打开和关闭数据库连接等变得非常麻烦。然而，spring jdbc framework从打开连接、准备和执行sql语句、处理异常、处理事务以及最后关闭连接开始，负责所有低级别的细节。

因此，您需要做的就是定义连接参数并指定要执行的sql语句，并在从数据库获取数据的同时为每个迭代执行所需的工作。

spring jdbc提供了几种方法和相应不同的类来与数据库进行接口。我将采用经典且最流行的方法来使用框架的jdbctemplate类。这是管理所有数据库通信和异常处理的中心框架类。

jdbctemplate类

jdbc模板类执行sql查询、更新语句、存储过程调用、对结果集执行迭代，并提取返回的参数值。它还捕获jdbc异常，并将其转换为org.springframework.dao中定义的通用的、信息更丰富的包。

一旦配置好，jdbctemplate类的实例就是线程安全的。因此，您可以配置jdbctemplate的一个实例，然后将这个共享引用安全地注入多个daos。

在使用jdbc模板类时，一个常见的做法是在spring配置文件中配置一个数据源，然后将这个共享数据源bean注入到dao类中，然后在数据源的setter中创建jdbctemplate。

配置数据源

让我们在数据库测试中创建一个数据库表student。我们假设您正在使用mysql数据库，如果您使用任何其他数据库，那么您可以相应地更改ddl和sql查询。

create table student(
   id   int not null auto_increment,
   name varchar(20) not null,
   age  int not null,
   primary key (id)
);

现在需要为jdbc模板提供一个datasource,以便它可以进行配置获取数据库权限：

<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"/>
   <property name = "username" value = "root"/>
   <property name = "password" value = "password"/>
</bean>

数据访问对象（data access object,dao)

dao表示数据访问对象，通常用于数据库交互。daos的存在是为了提供一种向数据库读写数据的方法，它们应该通过应用程序的其他部分访问它们的接口来公开此功能。

spring中的dao支持使得以一致的方式使用jdbc、hibernate、jpa或jdo等数据访问技术变得很容易。

执行sql命令

让我们看看如何使用sql和jdbctemplate对象对数据库表执行crud(创建、读取、更新和删除)操作。

org.springframework.jdbc.core.jdbctemplate是jdbc核心包中的中心类。它简化了jdbc的使用，有助于避免常见错误。它执行核心jdbc工作流，让应用程序代码提供sql并提取结果。这个类执行sql查询或更新，在resultset上发起迭代，捕获jdbc异常，并将它们转换为org.springframework.dao中定义的更通用的、更有用的异常。

注：jdbctemplate是线程安全的，关于线程安全，将会在后续的文章中加以介绍。

下面介绍使用到的方法，完整信息见spring jdbctemplate api reference

example

以下项目我使用maven进行构建，创建maven项目，更新pom.xml文件为以下内容

<?xml version="1.0" encoding="utf-8"?>
<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>top.ninwoo.spring</groupid>
    <artifactid>build-demo</artifactid>
    <version>1.0-snapshot</version>

    <dependencies>
        <!-- spring依赖 -->
        <!-- 1.spring核心依赖 -->
        <dependency>
            <groupid>org.springframework</groupid>
            <artifactid>spring-core</artifactid>
            <version>4.1.7.release</version>
        </dependency>
        <dependency>
            <groupid>org.springframework</groupid>
            <artifactid>spring-beans</artifactid>
            <version>4.1.7.release</version>
        </dependency>
        <dependency>
            <groupid>org.springframework</groupid>
            <artifactid>spring-context</artifactid>
            <version>4.1.7.release</version>
        </dependency>
        <!-- 2.spring dao依赖 -->
        <!-- spring-jdbc包括了一些如jdbctemplate的工具类 -->
        <dependency>
            <groupid>org.springframework</groupid>
            <artifactid>spring-jdbc</artifactid>
            <version>4.1.7.release</version>
        </dependency>
        <dependency>
            <groupid>org.springframework</groupid>
            <artifactid>spring-tx</artifactid>
            <version>4.1.7.release</version>
        </dependency>
        <!-- 3.spring web依赖 -->
        <dependency>
            <groupid>org.springframework</groupid>
            <artifactid>spring-web</artifactid>
            <version>4.1.7.release</version>
        </dependency>
        <dependency>
            <groupid>org.springframework</groupid>
            <artifactid>spring-webmvc</artifactid>
            <version>4.1.7.release</version>
        </dependency>
        <!-- 4.spring test依赖：方便做单元测试和集成测试 -->
        <dependency>
            <groupid>org.springframework</groupid>
            <artifactid>spring-test</artifactid>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupid>mysql</groupid>
            <artifactid>mysql-connector-java</artifactid>
            <version>6.0.6</version>
        </dependency>
    </dependencies>
</project>

• studentdao.java : 定义student数据接口

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 create
      * a record in the student table.
   */
   public void create(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);
}

• student.java ： student类

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

• studentmapper.java ： 将数据库条目映射到student对象,关于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;
   }
}

这是一个函数接口，因此可以用作lambda表达式或方法引用的赋值目标。

rowmapper必须实现maprow方法来映射resultset中的每一行数据。这个方法不应该调用resultset上的next();它只应该映射当前行的值。

@nullable
t maprow(java.sql.resultset rs,
                   int rownum)
            throws java.sql.sqlexception

parameters:
rs - the resultset to map (pre-initialized for the current row)
rownum - the number of the current row
returns:
the result object for the current row (may be null)
throws:
java.sql.sqlexception - if a sqlexception is encountered getting column values (that is, there's no need to catch sqlexception)

• studentjdbctemplate.java ： student数据接口的具体实现

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 (name, age) values (?, ?)";
      jdbctemplateobject.update( sql, name, age);
      system.out.println("created record name = " + name + " age = " + age);
      return;
   }
   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;
   }
}

构造函数：

• jdbctemplate()
• jdbctemplate(javax.sql.datasource datasource)

update:

public int update(java.lang.string sql,
                  @nullable
                  java.lang.object... args)
           throws dataaccessexception

queryforobject

<t> t queryforobject(java.lang.string sql,
                               java.lang.object[] args,
                               rowmapper<t> rowmapper)
                        throws dataaccessexception

• mainapp.java ： 主函数

import java.util.list;

import org.springframework.context.applicationcontext;
import org.springframework.context.support.classpathxmlapplicationcontext;
import com.tutorialspoint.studentjdbctemplate;

public class mainapp {
   public static void main(string[] args) {
      applicationcontext context = new classpathxmlapplicationcontext("beans.xml");

      studentjdbctemplate studentjdbctemplate = 
         (studentjdbctemplate)context.getbean("studentjdbctemplate");
      
      system.out.println("------records creation--------" );
      studentjdbctemplate.create("zara", 11);
      studentjdbctemplate.create("nuha", 2);
      studentjdbctemplate.create("ayan", 15);

      system.out.println("------listing multiple records--------" );
      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("----updating record with id = 2 -----" );
      studentjdbctemplate.update(2, 20);

      system.out.println("----listing record with id = 2 -----" );
      student student = studentjdbctemplate.getstudent(2);
      system.out.print("id : " + student.getid() );
      system.out.print(", name : " + student.getname() );
      system.out.println(", age : " + student.getage());
   }
}

• 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"/>
      <property name = "username" value = "root"/>
      <property name = "password" value = "password"/>
   </bean>

   <!-- definition for studentjdbctemplate bean -->
   <bean id = "studentjdbctemplate" 
      class = "com.tutorialspoint.studentjdbctemplate">
      <property name = "datasource" ref = "datasource" />    
   </bean>
      
</beans>

• 输出

------records creation--------
created record name = zara age = 11
created record name = nuha age = 2
created record name = ayan age = 15
------listing multiple records--------
id : 1, name : zara, age : 11
id : 2, name : nuha, age : 2
id : 3, name : ayan, age : 15
----updating record with id = 2 -----
updated record with id = 2
----listing record with id = 2 -----
id : 2, name : nuha, age : 20