一、簡介

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;}@Overridepublic 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