1 概念和准备
什么是JdbcTemplate
?
Spring框架对JDBC进行封装,使用 JdbcTemplate
方便实现对数据库的操作。
准备工作
引入相关jar包
<!-- 整合其他框架 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
</dependency>
<!-- 针对事务 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
</dependency>
<!-- 数据库相关 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
</dependency>
配置数据库的连接池
#数据库外部配置文件 jdbc.properties
jdbc.url=jdbc:mysql://localhost:3306/user_db?characterEncoding=utf-8
jdbc.username=root
jdbc.password=Ylj123..
jdbc.driver=com.mysql.cj.jdbc.Driver
<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="classpath:jdbc.properties" file-encoding="utf-8"/>
<!-- 配置数据源 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<property name="driverClassName" value="${jdbc.driver}"/>
</bean>
</beans>
配置
JdbcTemplate
对象,注入DataSource
<!-- 配置jdbcTemplate对象,注入DataSource -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
创建 service 类,
dao
类,在dao
注入jdbcTemplate
对象
<!-- 开启注解扫描 -->
<context:component-scan base-package="com.wit"/>
@Service
public class UserService {
@Autowired
UserDao userDao;
}
public interface UserDao {
}
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
JdbcTemplate jdbcTemplate;
}
创建数据库表
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`user_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`ustatus` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
创建实体类
public class User {
private String userId;
private String username;
private String ustatus;
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getUstatus() {
return ustatus;
}
public void setUstatus(String ustatus) {
this.ustatus = ustatus;
}
@Override
public String toString() {
return "User{" +
"userId='" + userId + '\'' +
", username='" + username + '\'' +
", ustatus='" + ustatus + '\'' +
'}';
}
}
2 增删改代码编写
增加方法编写
编写
service
和dao
中的方法
@Service
public class UserService {
@Autowired
UserDao userDao;
public void addUser(){
User user = new User();
user.setUserId("111");
user.setUsername("张三");
user.setUstatus("在线");
userDao.addUser(user);
}
}
public interface UserDao {
void addUser(User user);
}
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
JdbcTemplate jdbcTemplate;
public void addUser(User user) {
// 两个参数:
// sql:sql语句
// 可变参数,对应sql语句中的"?"
// 返回结果是更新的行数
String sql = "insert into t_user(user_id,username,ustatus) values(?,?,?)";
int count = jdbcTemplate.update(sql,user.getUserId(),user.getUsername(),user.getUstatus());
System.out.println(count);
}
}
测试
@Test
public void testInsert(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
UserService service = context.getBean("userService", UserService.class);
service.addUser();
}
![image-20210311215242760](https://i-blog.csdnimg.cn/blog_migrate/75d90ed118cce127c3c008e590b1f31a.png)
删改方法编写
编写
service
和dao
中的方法
public interface UserDao {
void updateUser(User user);
void deleteUser(String id);
}
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
JdbcTemplate jdbcTemplate;
public void updateUser(User user){
String sql = "update t_user set username=?,ustatus=? where user_id=?";
Object[] args = {user.getUsername(),user.getUstatus(),user.getUserId()};
int count = jdbcTemplate.update(sql,args);
}
public void deleteUser(String id){
String sql = "delete from t_user where user_id=?";
int count = jdbcTemplate.update(sql,id);
}
}
@Service
public class UserService {
@Autowired
UserDao userDao;
public void updateUser(){
User user = new User();
user.setUserId("111");
user.setUsername("李四");
user.setUstatus("离线");
userDao.updateUser(user);
}
public void deleteUser(){
userDao.deleteUser("111");
}
}
测试
@Test
public void testUpdate(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
UserService service = context.getBean("userService", UserService.class);
service.updateUser();
}
@Test
public void testDelete(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
UserService service = context.getBean("userService", UserService.class);
service.deleteUser();
}
更新结果
删除结果
3 查询代码编写
查询返回某个值:举例查询 t_user
中一共有多少条记录。
方法解析:两个参数,第一个参数是执行语句,第二个参数是返回类型的Class。
// UserService类中加入
public void findCount(){
System.out.println(userDao.findCount());
}
// UserDao接口中加入
int findCount();
// UserDaoImpl类中加入
public int findCount(){
String sql = "select count(*) from t_user";
return jdbcTemplate.queryForObject(sql,Integer.class);
}
测试
@Test
public void testFindCount(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
UserService service = context.getBean("userService", UserService.class);
service.findCount();
}
查询返回对象
方法解析:第一个参数是执行语句,第二个使用传入
RowMapper
的实现类BeanPropertyRowMapper<类>(类.class)
,第三个传入sql中"?"对应的参数集合。
// UserService类中加入
public void findUserInfo(){
User user = userDao.findUserInfo("1");
System.out.println(user.toString());
}
// UserDao接口中加入
User findUserInfo(String id);
// UserDaoImpl类中加入
public User findUserInfo(String id){
String sql = "select * from t_user where user_id = ?";
return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),id);
}
测试
@Test
public void testFindUserInfo(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
UserService service = context.getBean("userService", UserService.class);
service.findUserInfo();
}
查询返回集合操作:举例查询用户列表
方法名不同,参数同查询对象中的参数含义。
// UserService类中加入
public void findUserList(){
List<User>userList = userDao.findUserList();
System.out.println(userList);
}
// UserDao接口中加入
List<User> findUserList();
// UserDaoImpl类中加入
public List<User> findUserList(){
String sql = "select * from t_user";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
}
测试
@Test
public void testFindUserList(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
UserService service = context.getBean("userService", UserService.class);
service.findUserList();
}
4 批量操作
方法解析:
batchUpdate
用来实现批量添加,修改,删除操作。第一个参数是sql语句,第二个参数对应sql语句的参数列表。即每一条sql语句"?"对应的参数集合构成的列表。
批量添加
// UserService类中加入
public void batchInsert(){
List<Object[]> list = new ArrayList<Object[]>();
Object [] arg1 = {"1","张三","在线"};
Object [] arg2 = {"2","张四","在线"};
Object [] arg3 = {"3","张五","在线"};
list.add(arg1);
list.add(arg2);
list.add(arg3);
int []counts = userDao.batchInsert(list);
System.out.println(Arrays.toString(counts));
}
// UserDao接口中加入
int[] batchInsert(List<Object[]> args);
// UserDaoImpl类中加入
public int[] batchInsert(List<Object[]> args){
String sql = "insert into t_user values(?,?,?)";
return jdbcTemplate.batchUpdate(sql,args);
}
测试
@Test
public void testBatchInsert(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
UserService service = context.getBean("userService", UserService.class);
service.batchInsert();
}
批量修改
// UserService类中加入
public void batchUpdate(){
List<Object[]> list = new ArrayList<Object[]>();
Object [] arg1 = {"离线",1};
Object [] arg2 = {"离线",2};
Object [] arg3 = {"离线",3};
list.add(arg1);
list.add(arg2);
list.add(arg3);
userDao.batchUpdate(list);
}
// UserDao接口中加入
int[] batchUpdate(List<Object[]> args);
// UserDaoImpl类中加入
public int[] batchUpdate(List<Object[]> args){
String sql = "update t_user set ustatus=? where user_id=?";
return jdbcTemplate.batchUpdate(sql,args);
}
测试
@Test
public void testBatchUpdate(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
UserService service = context.getBean("userService", UserService.class);
service.batchUpdate();
}
批量删除
// UserService类中加入
public void batchDelete(){
List<Object[]> list = new ArrayList<Object[]>();
Object [] arg1 = {1};
Object [] arg2 = {2};
Object [] arg3 = {3};
list.add(arg1);
list.add(arg2);
list.add(arg3);
userDao.batchDelete(list);
}
// UserDao接口中加入
int[] batchDelete(List<Object[]> args);
// UserDaoImpl类中加入
public int[] batchDelete(List<Object[]> args){
String sql = "delete from t_user where user_id=?";
return jdbcTemplate.batchUpdate(sql,args);
}
测试
@Test
public void testBatchDelete(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
UserService service = context.getBean("userService", UserService.class);
service.batchDelete();
}