JdbcTemplate(概念和准备)

什么是JdbcTemplate

Spring框架对JDBC进行封装,使用JdbcTemplate方便实现对数据库操作

准备工作

引入相关的Jar包

因为druid的包之前引入过,所以引入下面的四个就可以了

新建数据库

CREATE DATABASE `user_db` DEFAULT CHARACTER SET utf8mb4

新建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\"xmlns:aop=\"http://www.springframework.org/schema/aop\"xsi:schemaLocation=\"http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsdhttp://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd\"><!--  开启注解扫描  --><context:component-scan base-package=\"com.dance.spring.learn.jdbc\"/><bean id=\"dataSource\" class=\"com.alibaba.druid.pool.DruidDataSource\" destroy-method=\"close\"><property name=\"driverClassName\" value=\"com.mysql.jdbc.Driver\" /><property name=\"url\" value=\"jdbc:mysql://localhost:3306/user_db\" /><property name=\"username\" value=\"root\" /><property name=\"password\" value=\"123456\" /></bean><bean id=\"jdbcTemplate\" class=\"org.springframework.jdbc.core.JdbcTemplate\"><property name=\"dataSource\" ref=\"dataSource\" /></bean></beans>

新建BookDao接口

package com.dance.spring.learn.jdbc.dao;public interface BookDao {}

新建BookDao接口实现类

package com.dance.spring.learn.jdbc.dao.impl;import com.dance.spring.learn.jdbc.dao.BookDao;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.stereotype.Repository;@Repositorypublic class BookDaoImpl implements BookDao {@Autowiredprivate JdbcTemplate jdbcTemplate;}

新建Service类

package com.dance.spring.learn.jdbc.service;import com.dance.spring.learn.jdbc.dao.BookDao;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;@Servicepublic class BookService {@Autowiredprivate BookDao bookDao;}

JdbcTemplate操作数据库(添加)

新建表

create table user (user_id varchar(20) PRIMARY key,username varchar(100) not null,ustatus varchar(50) not null)

新建实体类

package com.dance.spring.learn.jdbc.entity;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;}}

编写BookDao

接口新增方法

void add(User user);

实现类新增实现

@Overridepublic void add(User user) {int update = jdbcTemplate.update(\"insert into user values(?,?,?)\", user.getUserId(), user.getUserName(), user.getUstatus());if(update > 0){System.out.println(\"新增成功\");}else{System.out.println(\"新增失败\");}}

编写BookService

public void add(User user){bookDao.add(user);}

编写测试类

@Testpublic void testAdd(){ClassPathXmlApplicationContext classPathXmlApplicationContext = new ClassPathXmlApplicationContext(\"Spring-jdbc.xml\");BookService bookService = classPathXmlApplicationContext.getBean(\"bookService\", BookService.class);User user = new User();user.setUserId(\"1\");user.setUserName(\"flower\");user.setUstatus(\"1\");bookService.add(user);}

执行结果

十二月 11, 2021 4:47:14 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info信息: {dataSource-1} inited新增成功

查看数据库

JdbcTemplate操作数据库(修改和删除)

编写BookDao

增加接口

void update(User user);void delete(User user);

实现接口

@Overridepublic void update(User user) {int update = jdbcTemplate.update(\"update user set username = ?,ustatus = ? where user_id = ?\", user.getUserName(), user.getUstatus(), user.getUserId());if(update > 0){System.out.println(\"修改成功\");}else{System.out.println(\"修改失败\");}}@Overridepublic void delete(User user) {int update = jdbcTemplate.update(\"delete from user where user_id = ?\", user.getUserId());if(update > 0){System.out.println(\"删除成功\");}else{System.out.println(\"删除失败\");}}

编写BookService

public void update(User user){bookDao.update(user);}public void delete(User user){bookDao.delete(user);}

编写测试类

@Testpublic void testUpdate(){BookService bookService = classPathXmlApplicationContext.getBean(\"bookService\", BookService.class);User user = new User();user.setUserId(\"1\");user.setUserName(\"dance\");user.setUstatus(\"2\");bookService.update(user);}@Testpublic void testDelete(){BookService bookService = classPathXmlApplicationContext.getBean(\"bookService\", BookService.class);User user = new User();user.setUserId(\"1\");bookService.delete(user);}

执行结果

修改

十二月 11, 2021 6:13:12 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info信息: {dataSource-1} inited修改成功

删除

十二月 11, 2021 6:13:40 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info信息: {dataSource-1} inited删除成功

JdbcTemplate操作数据库(查询)

查询返回某个值

编写BookDao

新增接口

int selectCount();

实现接口

@Overridepublic int selectCount() {return jdbcTemplate.queryForObject(\"select count(1) from user\",Integer.class);}

编写BookService

public int selectCount(){return bookDao.selectCount();}

编写测试类

@Testpublic void testSelectCount(){BookService bookService = classPathXmlApplicationContext.getBean(\"bookService\", BookService.class);int i = bookService.selectCount();System.out.println(\"总用户数为:\"+i);}

执行结果

执行之前先执行一下add否则没有数据就是0

十二月 11, 2021 6:30:05 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info信息: {dataSource-1} inited总用户数为:1

查询返回对象

编写BookDao

新增接口

User selectUserById(int id);

实现接口

@Overridepublic User selectUserById(int id) {return jdbcTemplate.queryForObject(\"select * from user where user_id = ?\", new BeanPropertyRowMapper<>(User.class),id);}

编写BookService

public User selectUserById(int id) {return bookDao.selectUserById(id);}

编写测试类

@Testpublic void testSelectUserById(){BookService bookService = classPathXmlApplicationContext.getBean(\"bookService\", BookService.class);User user = bookService.selectUserById(1);System.out.println(user);}

user类新增toString方法

@Overridepublic String toString() {return \"User{\" +\"userId=\'\" + userId + \'\\\'\' +\", userName=\'\" + userName + \'\\\'\' +\", ustatus=\'\" + ustatus + \'\\\'\' +\'}\';}

执行结果

十二月 11, 2021 6:37:39 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info信息: {dataSource-1} initedUser{userId=\'1\', userName=\'flower\', ustatus=\'1\'}

查询返回集合

编写BookDao

新增接口

List selectUserList();

实现接口

@Overridepublic List selectUserList() {return jdbcTemplate.query(\"select * from user\", new BeanPropertyRowMapper<>(User.class));}

编写BookService

public List selectUserList() {return bookDao.selectUserList();}

编写测试类

@Testpublic void testSelectUserList(){BookService bookService = classPathXmlApplicationContext.getBean(\"bookService\", BookService.class);List users = bookService.selectUserList();System.out.println(users);}

执行结果

十二月 11, 2021 6:54:41 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info信息: {dataSource-1} inited[User{userId=\'1\', userName=\'flower\', ustatus=\'1\'}, User{userId=\'2\', userName=\'dance\', ustatus=\'1\'}]

JdbcTemplate操作数据库(批量操作)

批量新增

编写BookDao

新增接口

void batchAdd(List userList);

实现接口

@Overridepublic void batchAdd(List userList) {List<Object[]> collect = userList.stream().map(x -> new Object[]{x.getUserId(), x.getUserName(), x.getUstatus()}).collect(Collectors.toList());int[] ints = jdbcTemplate.batchUpdate(\"insert into user values(?,?,?)\", collect);System.out.println(Arrays.toString(ints));}

编写BookService

public void batchAdd(List userList) {bookDao.batchAdd(userList);}

编写测试类

user类增加全参数构造和无参数构造

public User(String userId, String userName, String ustatus) {this.userId = userId;this.userName = userName;this.ustatus = ustatus;}public User() {}

测试类

@Testpublic void testBatchAdd(){BookService bookService = classPathXmlApplicationContext.getBean(\"bookService\", BookService.class);List userList = Arrays.asList(new User(\"3\",\"张三\",\"1\"),new User(\"4\",\"李四\",\"2\"),new User(\"5\",\"王五\",\"3\"));bookService.batchAdd(userList);}

执行结果

[1, 1, 1]

但是这里出现了一个小问题,中文乱码了

去数据库删除数据

修改Spring配置文件

jdbc:mysql://localhost:3306/user_db?useSSL=false&characterEncoding=utf-8&autoReconnect=true

URL后面增加字符编码设置

再次测试

OK了

批量编辑

编写BookDao

新增接口

void batchUpdate(List userList);

实现接口

@Overridepublic void batchUpdate(List userList) {List<Object[]> collect = userList.stream().map(x -> new Object[]{x.getUserName(), x.getUstatus(), x.getUserId()}).collect(Collectors.toList());int[] ints = jdbcTemplate.batchUpdate(\"update user set username=?,ustatus=? where user_id=?\", collect);System.out.println(Arrays.toString(ints));}

编写BookService

public void batchUpdate(List userList) {bookDao.batchUpdate(userList);}

编写测试类

@Testpublic void testBatchUpdate(){BookService bookService = classPathXmlApplicationContext.getBean(\"bookService\", BookService.class);List userList = Arrays.asList(new User(\"3\",\"张三1\",\"11\"),new User(\"4\",\"李四2\",\"22\"),new User(\"5\",\"王五3\",\"33\"));bookService.batchUpdate(userList);}

执行结果

[1, 1, 1]

批量删除

编写BookDao

新增接口

void batchDelete(List userList);

实现接口

@Overridepublic void batchDelete(List userList) {List<Object[]> collect = userList.stream().map(x -> new Object[]{x.getUserId()}).collect(Collectors.toList());int[] ints = jdbcTemplate.batchUpdate(\"delete from user where user_id=?\", collect);System.out.println(Arrays.toString(ints));}

编写BookService

public void batchDelete(List userList) {bookDao.batchDelete(userList);}

编写测试类

@Testpublic void testBatchDelete(){BookService bookService = classPathXmlApplicationContext.getBean(\"bookService\", BookService.class);List userList = Arrays.asList(new User(\"3\",\"张三1\",\"11\"),new User(\"4\",\"李四2\",\"22\"),new User(\"5\",\"王五3\",\"33\"));bookService.batchDelete(userList);}

执行结果

[1, 1, 1]

完结 撒花花

若有收获，就点个赞吧

作者：彼岸舞

时间：2021\\12\\13

内容关于：Java

本文属于作者原创,未经允许,禁止转发