Spring JdbcTemplate

一、简介

Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作。它是 spring 框架中提供的一个对象，是对原始 Jdbc API 对象的简单封装。spring 框架为我们提供了很多的操作模板类。

• 针对操作关系型数据：

  • jdbcTemplate
  • HibernateTemplate

• 针对操作非关系型数据库：

  • RedisTemplate

• 针对操作消息队列：

  • JmsTemplate

二、应用

导入相关依赖

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.1.14.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>5.1.3.RELEASE</version>
</dependency>

创建数据库表

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `money` float NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

1. JdbcTemplate 的简单使用

public static void main(String[] args) {
    //准备数据源
    DriverManagerDataSource ds = new DriverManagerDataSource();
    ds.setDriverClassName("com.mysql.jdbc.Driver");
    ds.setUrl("jdbc:mysql://localhost:3306/test");
    ds.setUsername("root");
    ds.setPassword("123456");


    //1. 创建jdbcTemplate对象
    JdbcTemplate jdbcTemplate = new JdbcTemplate();

    jdbcTemplate.setDataSource(ds);

    //2. 执行操作
    jdbcTemplate.execute("insert into account(name, money) values('ccc',1000)");
}

2. JdbcTemplate 操作数据库

1）准备工作

导入C3P0依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>5.3.29</version>
</dependency>
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.1</version>
</dependency>

创建mysql.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="accountDao" class="com.shiftycat.mysql.dao.impl.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

    <!--配置jdbcTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test"></property>
        <property name="user" value="root"></property>
        <property name="password" value="shiftlesscat"></property>
    </bean>

</beans>

创建PoJo类

package com.shiftycat.mysql.pojo;

public class Account {

    private int id;
    private String name;
    private float money;

    public Account() {
    }

    public Account(int id, String name, float money) {
        this.id = id;
        this.name = name;
        this.money = money;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public float getMoney() {
        return money;
    }

    public void setMoney(float money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

创建持久层

/**
 * 账户持久层接口
 */
public interface IAccountDao {

    /**
     * 根据id查询账户
     */
    Account findAccountById(Integer accountId);

    /**
     * 根据名称查询
     */
    Account findAccountByName(String accountName);

    /**
     * 更新账户
     */
    void updateAccount(Account account);
}

/**
 * 账户的持久层实现类
 */
public class AccountDaoImpl implements IAccountDao {

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
}

2） 添加

public void addAccount(Account account) {
    //1 创建sql语句
    String sql = "insert into account(id, name, money) values(?,?,?)";
    //2 调用方法实现
    Object[] args = {account.getId(), account.getName(), account.getMoney()};
    int update = jdbcTemplate.update(sql, args);
    System.out.println(update);
}

3）修改

public void updateAccount(Account account) {
    //1 创建sql语句
    String sql = "update account set username=?, ustatus=? where user_id=?";
    //2 调用方法实现
    Object[] args = {account.getId(), account.getName(), account.getMoney()};
    int update = jdbcTemplate.update(sql, args);
    System.out.println(update);
}

4）删除

public void deleteAccount(String id) {
    //1 创建sql语句
    String sql = "delete from account where id=?";
    //2 调用方法实现
    int update = jdbcTemplate.update(sql, id);
    System.out.println(update);
}

5) 查询返回某个值

public int selectCount() {
    //1 创建sql语句
    String sql = "select count(*) from account;";
    //2 调用方法实现
    Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
    return count;
}

6）查询返回对象

public Account findAccountInfo(String id) {
    //1 创建sql语句
    String sql = "select * from account where id=?;";
    //2 调用方法实现
    Account account = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Account>(Account.class), id);
    return account;
}

7）查询返回集合

public List<Account> findAllAccount() {
    //1 创建sql语句
    String sql = "select * from account";
    //2 调用方法实现
    List<Account> accountList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Account>(Account.class));
    return accountList;
}

8）批量操作-添加

public void batchAddAccount(List<Object[]> batchArgs) {
    //1 创建sql语句
    String sql = "insert into account(id, name, money) values(?,?,?)";
    //2 调用方法实现
    int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    System.out.println(Arrays.toString(ints));
}

9）批量操作-修改

public void batchUpdateAccount(List<Object[]> batchArgs) {
    //1 创建sql语句
    String sql = "update account set name=?, account=? where id=?";
    //2 调用方法实现
    int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    System.out.println(ints);
}

10）批量操作-删除

public void batchDeleteAccount(List<Object[]> batchArgs) {
    //1 创建sql语句
    String sql = "delete from account where id=?";
    //2 调用方法实现
    int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    System.out.println(ints);
}

前丨尘忆·梦-Spring——JdbcTemplate：https://blog.csdn.net/qq_36879493/article/details/121915176

Xiu Yan-Spring 从入门到精通系列 11 —— Spring 中的 JdbcTemplate: https://blog.csdn.net/qq_36879493/article/details/121915176