Spring5——JdbcTemplate笔记

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="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.xsd
                           http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
                           http://www.springframework.org/schema/aop http://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);
      }
  
  }
  
  

  ​