2021-09-23 Spring5_JdbcTemplate的基本使用

JdbcTemplate的基本使用

1.1 JdbcTemplate概述

它是spring框架中提供的一个对象，是对原始繁琐的Jdbc API对象的简单封装。 spring框架为我们提供了很多的操作模板类。例如：操作关系型数据的JdbcTemplate和HibernateTemplate，操作nosql数据库的RedisTemplate，操作消息队列的JmsTemplate等等。

1.2 JdbcTemplate开发步骤

① 导入spring-jdbc和spring-tx坐标

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>5.0.5.RELEASE</version>
</dependency>
<dependency>
    <groupId>jstl</groupId>
    <artifactId>jstl</artifactId>
    <version>1.2</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.10</version>
</dependency> 
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.32</version>
</dependency>

② 创建数据库表和实体

package com.raphuscucullatus.domain;
/**
 * Account实体类
 *
 * @author raphus cucullatus
 * @version 2021/9/23 17:25
 * @since JDK8
 */
public class Account {
    private String name;
    private Double money;

    public String getName() {}
    public void setName(String name) { this.name = name;}
    public Double getMoney() {return money;}
    public void setMoney(Double money) {this.money = money;}
    @Override
    public String toString() {
        return "Account{" +
                "name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

③ 创建JdbcTemplate对象,执行数据库操作

@Test
public void jdbcTemplateQuickStart(){
    DruidDataSource druidDateSource = new DruidDataSource();
    druidDateSource.setUrl("jdbc:mysql://localhost:3306/test");
    druidDateSource.setUsername("root");
    druidDateSource.setPassword("088.5741");

    //创建jdbcTemplate对象
    JdbcTemplate jdbcTemplate = new JdbcTemplate();
    //设置数据源(可得到connection 以及sql执行器)
    jdbcTemplate.setDataSource(druidDateSource);
    //执行sql
    int row = jdbcTemplate.update("insert into account values (?,?)", "tom", 2000);
}

1.3 Spring产生JdbcTemplate对象

jdbc.properties

url=jdbc:mysql://localhost:3306/test
name=root
password=088.5741

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"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
                http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"
>
    <context:property-placeholder location="jdbc.properties"/>

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="username" value="${name}"/>
        <property name="password" value="${password}"/>
        <property name="url" value="${url}"/>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

</beans>

测试用例

/**
 * 使用spring容器产生jdbcTemplate对象
 */
@Test
public void springCreateJdbcTemplate(){
    ClassPathXmlApplicationContext classPathXmlApplicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
    //通过配置文件中的配置来创建jdbcTemplate对象并注入Druid数据源
    JdbcTemplate jdbcTemplate = classPathXmlApplicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
    //执行sql
    int row = jdbcTemplate.update("insert into account values (?,?)", "jerry", 20300);
}

1.4 集成JUnit测试jdbcTemplate

spring核心配置文件

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

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="username" value="${name}"/>
        <property name="password" value="${password}"/>
        <property name="url" value="${url}"/>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

</beans>

测试类

package com.raphuscucullatus.domain;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;

/**
 * spring集成Junit测试
 *
 * @author raphus cucullatus
 * @version 2021/9/23 18:26
 * @since JDK8
 */
//集成JUnit
@RunWith(SpringJUnit4ClassRunner.class)
//注解加载spring核心配置文件
@ContextConfiguration("classpath:applicationContext.xml")
public class jdbcTemplateJunitTest {
	//注入jdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
	//改
    @Test
    public void testSelect(){
        int row = jdbcTemplate.update("update account set money=? where name=?", 20000.00, "tom");
    }
	//删
    @Test
    public void testDelete(){
        int row = jdbcTemplate.update("delete from account where name=?",  "tom");
    }
    //查所有
    @Test
    public void testSelectAll(){
        List<Account> accountList = jdbcTemplate.query("select * from account", new BeanPropertyRowMapper<Account>(Account.class));
        for (Account account : accountList) {
            System.out.println(account);
        }
    }
    //查一个
    @Test
    public void testSelectOne(){
        Account jerry = jdbcTemplate.queryForObject("select * from account where name=?", new BeanPropertyRowMapper<Account>(Account.class), "jerry");
        System.out.println(jerry);
    }
    //查总数
    @Test
    public void testSelectCount(){
        Long count = jdbcTemplate.queryForObject("select count(*) from account", Long.class);
        System.out.println("总数为:"+count);
    }
}