一、简介
Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作。它是 spring 框架中提供的一个对象，是对原始 Jdbc API 对象的简单封装。spring 框架为我们提供了很多的操作模板类。
-
针对操作关系型数据：
- jdbcTemplate
- HibernateTemplate
-
针对操作非关系型数据库：
- RedisTemplate
-
针对操作消息队列：
- JmsTemplate
二、应用
导入相关依赖
dependency>
groupId>org.springframeworkgroupId>
artifactId>spring-jdbcartifactId>
version>5.1.14.RELEASEversion>
dependency>
dependency>
groupId>org.springframeworkgroupId>
artifactId>spring-txartifactId>
version>5.1.3.RELEASEversion>
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 JdbcTemplat服务器托管网e();
jdbcTemplate.setDataSource(ds);
//2. 执行操作
jdbcTemplate.execute("insert into account(name, money) values('ccc',1000)");
}
2. JdbcTemplate 操作数据库
1）准备工作
导入C3P0依赖
dependency>
groupId>mysqlgroupId>
artifactId>mysql-connector-javaartifactId>
version>5.1.49version>
dependency>
dependency>
groupId>org.springframeworkgroupId>
artifactId>spring-txartifactId>
version>5.3.29version>
dependency>
dependency>
groupId>com.mchangegroupId>
artifactId>c3p0artifactId>
version>0.9.5.1version>
dependency>
创建mysql.xml
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>
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 BeanPropertyRowMapperAccount>(Account.class), id);
return account;
}
7）查询返回集合
public ListAccount> findAllAccount() {
//1 创建sql语句
String sql = "select * from account";
//2 调用方法实现
ListAccount> accountList = jdbcTemplate.query(sql, new BeanPropertyRowMapperAccount>(Account.class));
return accountList;
}
8）批量操作-添加
public void batchAddAccount(ListObject[]> 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(ListObject[]> 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(ListObject[]> 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
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
