Spring5 JdbcTemplate如何高效实现数据库操作？

本文共计1432个文字，预计阅读时间需要6分钟。

Spring5——JdbcTemplate笔记概念，翻译过来是模板的意思，顾名思义，JdbcTemplate就是一个JDBC的模板，它对JDBC进行了封装，可以很方便地实现数据库的CRUD（增、删、改、查）操作。

Spring5——JdbcTemplate笔记 概念

template，翻译过来是模板的意思，顾名思义，JdbcTemplate就是一个JDBC的模板，它对JDBC进行了封装，可以很方便地实现对数据库的CRUD（增、删、改、查）的操作。

JdbcTemplate准备工作 引入相关的依赖

• druid-1.1.9.jar
• mysql-connector-java-5.1.7-bin.jar
• spring-tx-5.2.6.RELEASE.jar
• spring-orm-5.2.6.RELEASE.jar
• 在这里要注意相关jar包的版本，比如说Java连接MySQL的驱动包的版本应该与目的数据库的版本一致

配置数据库连接池、在JdbcTemplate对象中注入DataSource（数据源）

这里用xml配置文件来配置相关的信息

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="www.springframework.org/schema/beans" xmlns:xsi="www.w3.org/2001/XMLSchema-instance" xmlns:context="www.springframework.org/schema/context" xmlns:aop="www.springframework.org/schema/aop" xsi:schemaLocation="www.springframework.org/schema/beans www.springframework.org/schema/beans/spring-beans.xsd www.springframework.org/schema/context www.springframework.org/schema/context/spring-context.xsd www.springframework.org/schema/aop www.springframework.org/schema/aop/spring-aop.xsd"> <!-- 开启注解扫描--> <context:component-scan base-package="com.ws.spring"></context:component-scan> <!-- 数据库连接池 --> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> <property name="url" value="jdbc:mysql:///study" /> <property name="username" value="root" /> <property name="password" value="123" /> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> </bean> <!-- JdbcTemplate --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <!-- 注入DataSource --> <property name="dataSource" ref="dataSource"></property> </bean> </beans> JdbcTemplate的使用 创建service类、创建dao类

package com.spring.service; // service类 @Service public class UserService { }

package com.spring.dao; // dao类 public interface UserDao { }

package com.spring.dao; // dao的实现类 @Repository public class UserDaoImpl implements UserDao { } 在service类中注入dao类的对象属性

package com.spring.service; @Service public class UserService { //注入dao对象 @Autowired private UserDao userDao; } 在dao类中注入JdbcTemplate对象

package com.spring.dao; @Repository public class UserDaoImpl implements UserDao { //注入jdbcTemplate对象 @Autowired private JdbcTemplate jdbcTemplate; } 增删改查

• 实体类

  package com.spring.entiey; public class User { private Integer id; private String name; private Integer age; private String email; public User() { } public User(Integer id, String name, Integer age, String email) { this.id = id; this.name = name; this.age = age; this.email = email; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + ", email='" + email + '\'' + '}'; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } }

• UserDao

  package com.spring.dao; public interface UserDao { //添加的方法 void add(User user); //修改的方法 void updateUser(User user); //删除的方法 void deleteUser(int id); //查询表记录数 int selectCount(); //查询返回对象 User findUserInfo(int id); //查询返回集合 List<User> findAllUser(); //批量添加数据 void batchAddUser(List<Object[]> batchArgs); }

• UserDaoImpl

  package com.spring.dao; @Repository public class UserDaoImpl implements UserDao { //注入jdbcTemplate对象 @Autowired private JdbcTemplate jdbcTemplate; @Override public void add(User user) { //用jdbcTemplate来操作数据库 String sql = "insert into userinfo values (?,?,?,?)"; Object[] args = {user.getId(),user.getName(),user.getAge(),user.getEmail()}; int update = jdbcTemplate.update(sql,args); System.out.println(update); } @Override public void updateUser(User user) { String sql = "update userinfo set name=? where id=?"; Object[] args = {user.getName(),user.getId()}; int update = jdbcTemplate.update(sql, args); System.out.println("修改了："+update); } @Override public void deleteUser(int id) { String sql = "delete from userinfo where id=?"; int update = jdbcTemplate.update(sql, id); System.out.println("删除了："+update); } //查询表中的记录数 @Override public int selectCount() { String sql = "select count(*) from userinfo"; Integer count = jdbcTemplate.queryForObject(sql, Integer.class); return count; } //查询返回对象 @Override public User findUserInfo(int id) { String sql = "select * from userinfo where id=?"; User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id); return user; } //查询返回集合 @Override public List<User> findAllUser() { String sql = "select * from userinfo"; List<User> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class)); return users; } //批量添加 @Override public void batchAddUser(List<Object[]> batchArgs) { String sql = "insert into userinfo values (?,?,?,?)"; int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs); System.out.println(Arrays.toString(ints)); } }

• UserService

  package com.spring.service; @Service public class UserService { //注入dao对象 @Autowired private UserDao userDao; //添加数据的方法 public void addUser(User user){ userDao.add(user); } //修改数据的方法 public void updateUser(User user){ userDao.updateUser(user); } //删除数据的方法 public void deleteUser(int id){ userDao.deleteUser(id); } //查询表中记录数 public int findCount(){ int count = userDao.selectCount(); return count; } //查询返回对象 public User findOne(int id){ User userInfo = userDao.findUserInfo(id); return userInfo; } //查询返回集合 public List<User> findAll(){ return userDao.findAllUser(); } //批量添加数据 public void batchAdd(List<Object[]> batchArgs){ userDao.batchAddUser(batchArgs); } }

• 测试类

  package com.spring.testdemo; @SuppressWarnings({"all"}) public class TestSpringDemo { //添加单条数据 @Test public void testAdd(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); UserService userService = context.getBean("userService", UserService.class); User user = new User(); user.setName("111"); user.setId(10); user.setAge(18); user.setEmail("shuaiwang2019@126.com"); System.out.println("user= "+user); userService.addUser(user); } //修改单条数据 @Test public void testUpdate(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); UserService userService = context.getBean("userService", UserService.class); User user = new User(); user.setName("test"); user.setId(1); System.out.println("user= "+user); userService.updateUser(user); } //删除单条数据 @Test public void testDelete(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); UserService userService = context.getBean("userService", UserService.class); userService.deleteUser(1); } }

  package com.spring.testdemo; public class TestJdbcTemplateSelect { //查询记录数 @Test public void testSelectCount(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); UserService userService = context.getBean("userService", UserService.class); int count = userService.findCount(); System.out.println(count); } //查询返回对象 @Test public void testSelectUserInfo(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); UserService userService = context.getBean("userService", UserService.class); User userInfo = userService.findOne(10); System.out.println(userInfo); } //查询返回集合 @Test public void testSelectAll(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); UserService userService = context.getBean("userService", UserService.class); List<User> users = userService.findAll(); System.out.println(users); } //批量添加 @Test public void testBatchAdd(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml"); UserService userService = context.getBean("userService", UserService.class); //要添加的集合 List<Object[]> batchArgs = new ArrayList<>(); Object[] o1 = {11,"java",24,"java@126.com"}; Object[] o2 = {12,"python",30,"python@126.com"}; Object[] o3 = {13,"c++",40,"c++@126.com"}; batchArgs.add(o1); batchArgs.add(o2); batchArgs.add(o3); userService.batchAdd(batchArgs); } }

  ​