Spring JDBC Template简单使用

最新推荐文章于 2020-11-02 10:49:05 发布

ZX9727

最新推荐文章于 2020-11-02 10:49:05 发布

阅读量226

点赞数

分类专栏： SSM 文章标签： spring jdbc Template mysql

本文链接：https://blog.youkuaiyun.com/ZX9727/article/details/87903203

版权

SSM 专栏收录该内容

16 篇文章

订阅专栏

本文详细介绍SpringJDBCTemplate的使用方法，包括执行DDL语句、数据的增删改及批量操作，以及如何查询简单数据项、复杂对象并利用RowMapper接口封装实体对象。通过具体案例展示了数据库创建、更新、查询等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Spring JDBC Template简单使用

常用方法

execute()：执行DDL语句

void execute(String sql)

update()：对数据进行增删改

int update(String sql, Object[] args)
int update(String sql, Object... args)

bathUpdate()：对数据进行批量增删改操作

int[] batchUpdate(String[] sql)
int[] batchUpdate(String sql, List<Object[]> args)

查询简单数据项

//获取一个
T queryForObject(String sql, Class<T> type)
T queryForObject(String sql, Object[] args, Class<T> type)
T queryForObject(String sql, Class<T> type, Object... args)
//获取多个
List<T> queryForList(String sql, Class<T> type)
List<T> queryForList(String sql, Object[] args, Class<T> type)
List<T> queryForList(String sql, Class<T> type, Object... arg)

查询复杂对象（封装为Map）

//获取一个
Map queryForMap(String sql)
Map queryForMap(String sql, Object[] args)
Map queryForMap(String sql, Object... arg)
//获取多个
List<Map<String,Object>> queryForList(String sql)
List<Map<String,Object>> queryForList(String sql, Object[] args)
List<Map<String,Object>> queryForList(String sql, Object... arg)

查询复杂对象----RowMapper接口（封装为实体对象）

//获取一个
T queryForObject(String sql, RowMapper<T> mapper)
T queryForObject(String sql, Object[] args, RowMapper<T> mapper)
T queryForObject(String sql, RowMapper<T> mapper, Object... arg)
//获取多个
List<T> query(String sql, RowMapper<T> mapper)
List<T> query(String sql, Object[] args, RowMapper<T> mapper)
List<T> query(String sql, RowMapper<T> mapper, Object... arg)

简单案例

数据库代码

drop database if exists selection_course;

create database selection_course;

use selection_course;

create table course(
   id                   int not null auto_increment,
   name                 char(20),
   score                int,
   primary key (id)
);
create table selection(
   student              int not null,
   course               int not null,
   selection_time       datetime,
   score                int,
   primary key (student, course)
);

create table student(
   id                   int not null auto_increment,
   name                 varchar(20),
   sex                  char(2),
   born                 date,
   primary key (id)
);

alter table selection add constraint FK_Reference_1 foreign key (course)
      references course (id) on delete restrict on update restrict;

alter table selection add constraint FK_Reference_2 foreign key (student)
      references student (id) on delete restrict on update restrict;

insert into course(id,name,score) values(1001,'英语',5);
insert into course(id,name,score) values(1002,'操作系统',5);
insert into course(id,name,score) values(1003,'数据结构',3);

commit;

目录结构

aspectJDemo
- src
- - main
- - - java
- - - - com.zhangxin9727.entity
- - - - - Studnet.java
- - - resources
- - - - applicationContext.xml
- - test
- - - java
- - - - MyTest.java
- pom.xml

主要文件

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>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>

    <groupId>com.zhangxin9727</groupId>
    <artifactId>sc</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.1.5.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.1.5.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.1.5.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>5.4.0</version>
        </dependency>
    </dependencies>
</project>

Studnet.java

package com.zhangxin9727.entity;

import java.util.Date;

public class Student {
    private int id;
    private String name;
    private String sex;
    private Date born;

    public void setId(int id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }
    
    public void setBorn(Date born) {
        this.born = born;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", born=" + born +
                '}';
    }
}

applicationContext.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.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url"
                  value="jdbc:mysql://localhost:3306/selection_course?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF8&amp;serverTimezone=GMT%2B8"/>
        <property name="username" value="root"/>
        <property name="password" value="mypassword"/>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

MyTest.java

import com.zhangxin9727.entity.Student;
import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class MyTest {
    private JdbcTemplate jdbcTemplate;

    {
        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");
    }

    @Test
    public void testExecute() {
        String sql = "CREATE TABLE IF NOT EXISTS user(id INT, name VARCHAR(20))";
        jdbcTemplate.execute(sql);
    }

    @Test
    public void testUpdate1() {
        String sql = "INSERT INTO selection_course.student(name,sex) VALUES(?,?)";
        jdbcTemplate.update(sql, new Object[]{"Alice", "男"});
    }

    @Test
    public void testUpdate2() {
        String sql = "UPDATE selection_course.student SET sex=? WHERE name=?";
        jdbcTemplate.update(sql, "女", "Alice");
    }

    @Test
    public void testBatchUpdate1() {
        String[] sqls = {
                "INSERT INTO selection_course.student(name,sex) VALUES('Bob','女')",
                "UPDATE selection_course.student SET sex='男' WHERE name='Bob'"
        };
        jdbcTemplate.batchUpdate(sqls);
    }

    @Test
    public void teatBatchUpdate2() {
        String sql = "INSERT INTO selection_course.selection(student, course) VALUES(?,?)";
        List<Object[]> list = new ArrayList<>();
        list.add(new Object[]{2, 1001});
        list.add(new Object[]{2, 1002});
        jdbcTemplate.batchUpdate(sql, list);
    }

    @Test
    public void testQueryForObject1() {
        String sql = "SELECT name FROM selection_course.student WHERE id=? AND sex=?";
        String name = jdbcTemplate.queryForObject(sql, String.class, 2, "女");
        System.out.println(name);
    }

    @Test
    public void testQueryForList1() {
        String sql = "SELECT name from selection_course.student WHERE sex=?";
        List<String> names = jdbcTemplate.queryForList(sql, String.class, "女");
        System.out.println(names);
    }

    @Test
    public void testQueryForMap() {
        String sql = "SELECT * FROM selection_course.student WHERE id=?";
        Map<String, Object> student = jdbcTemplate.queryForMap(sql, 2);
        System.out.println(student);
    }

    @Test
    public void testQueryForList2() {
        String sql = "SELECT * FROM selection_course.student";
        List<Map<String, Object>> student = jdbcTemplate.queryForList(sql);
        System.out.println(student);
    }

    private class StudentRowMap implements RowMapper<Student> {
        @Override
        public Student mapRow(ResultSet resultSet, int i) throws SQLException {
            Student student = new Student();
            student.setId(resultSet.getInt("id"));
            student.setName(resultSet.getString("name"));
            student.setSex(resultSet.getString("sex"));
            student.setBorn(resultSet.getDate("born"));
            return student;
        }
    }

    @Test
    public void testQueryForObject2() {
        String sql = "SELECT * FROM selection_course.student WHERE id=?";
        Student student = jdbcTemplate.queryForObject(sql, new StudentRowMap(), 2);
        System.out.println(student);
    }

    @Test
    public void testQuery() {
        String sql = "SELECT * FROM selection_course.student";
        List<Student> students = jdbcTemplate.query(sql, new StudentRowMap());
        System.out.println(students);
    }
}