spring —— 使用 JDBCTemplate 对数据库操作

在传统方法中，我们一般建立 Connection 链接，然后通过 Statement stm = conn.createStatement() 来获取 Statement 对象，最后用 Statement 对象操作数据库。

在 spring 中，则是通过 com.alibaba 的 druid 配置数据源工具，来配置要连接的数据库，然后将配置好的数据源作为属性传入 JDBCTemplate 对象当中，最后用 JDBCTemplate 对象操作数据库。

一、pom 文件所需依赖

<dependency>
    <!--jar包依赖-->
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>6.0.2</version>
</dependency>
<dependency>
    <!--MySQL驱动-->
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.30</version>
</dependency>
<dependency>
    <!--数据源工具-->
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.0.31</version>
</dependency>

二、spring-config.xml 文件配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd
       http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd">

    <!--扫描组件-->
    <context:component-scan base-package="com.mysql.conn"></context:component-scan>
    <!--引入外部文件-->
    <context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>
    <!--使用druid工具配置数据源-->
    <bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="url" value="${jdbc.url}"></property>
        <property name="driverClassName" value="${jdbc.driver}"></property>
        <property name="username" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>
    <!--将数据源作为属性传入JDBCTemplate对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="druidDataSource"></property>
    </bean>
</beans>

三、java 代码

package com.mysql.conn;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;

//使用spring整合junit5进行测试
@SpringJUnitConfig(locations = "classpath:bean-jdbc.xml")
public class TestUpdate {
    @Autowired
    public JdbcTemplate jdbcTemplate;
    @Test
    public void testUpdate() {        
        String sql ="insert into user values(?,?,?)";
        int rows = jdbcTemplate.update(sql,11,"lucy","30");
        System.out.println(rows);
    }
}

四、常用 sql 语句

（一）增加

String sql ="insert into user values(?,?,?)";
Object[] x={11,"lucy","30"};
int rows = jdbcTemplate.update(sql,x);
System.out.println(rows);

（二）删除

String sql ="delete from user where id = ? ";
int rows = jdbcTemplate.update(sql,10);
System.out.println(rows);

（三）更改

String sql ="update user set account=?,password=? where id=?";
int rows = jdbcTemplate.update(sql,"James",123,8);
System.out.println(rows);

（四）查询

1、查询单个对象

① 创建对象类

package com.mysql.conn;

public class User {
    private int id;
    private String account;
    private String password;

    //生成set方法，这一步很关键
    public void setId(int id) {
        this.id = id;
    }
    public void setAccount(String account) {
        this.account = account;
    }
    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", account='" + account + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

② 测试类

package com.mysql.conn;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;

@SpringJUnitConfig(locations = "classpath:bean-jdbc.xml")
public class TestUpdate {
    @Autowired
    public JdbcTemplate jdbcTemplate;
    @Test
    public void testUpdate() {
        String sql ="select * from user where id = ?";
        //第一种写法
        User user1 = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(User.class),1);
        System.out.println(user1);
        //第二种写法
        User user2 = jdbcTemplate.queryForObject(sql,
               (rs, rowNum) -> {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setAccount(rs.getString("account"));
                    user.setPassword(rs.getString("password"));
                    return user;
                }, 2);
        System.out.println(user2);
    }
}

 2、查询 list 集合

① 目标类不变

② 测试类

package com.mysql.conn;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;

import java.util.List;

@SpringJUnitConfig(locations = "classpath:bean-jdbc.xml")
public class TestUpdate {
    @Autowired
    public JdbcTemplate jdbcTemplate;
    @Test
    public void testUpdate() {
        String sql ="select * from user";
        List<User> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
        System.out.println(list);       
    }
}

3、查询单个值

测试类（查询多少行）

package com.mysql.conn;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;

@SpringJUnitConfig(locations = "classpath:bean-jdbc.xml")
public class TestUpdate {
    @Autowired
    public JdbcTemplate jdbcTemplate;
    @Test
    public void testUpdate() {
        String sql ="select count(account) from user";
        Integer i = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(i);       
    }
}