欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Spring入门学习笔记(4)——JDBC的使用

程序员文章站 2022-07-02 15:46:26
[TOC] Spring JDBC框架概览 使用传统的JDBC连接数据库,需要编写不必要的代码来处理异常、打开和关闭数据库连接等变得非常麻烦。然而,Spring JDBC Framework从打开连接、准备和执行SQL语句、处理异常、处理事务以及最后关闭连接开始,负责所有低级别的细节。 因此,您需要 ......

目录

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