Spring JdbcTemplate 学习笔记

一、基本概念

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

二、准备工作

• 导入依赖

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13</version>
        <scope>test</scope>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.0.6.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.1.2.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.14</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.12</version>
        <scope>provided</scope>
    </dependency>
</dependencies>

• 名称空间。与平时的没什么差别，加个组件扫描罢了。

<?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"
       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
">

• 配置数据库连接池

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url">
        <value> <![CDATA[jdbc:mysql://localhost:3306/spring?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&rewriteBatchedStatements=true]]></value>
    </property>
    <property name="username" value="root"/>
    <property name="password" value="root"/>
</bean>

• 配置JdbcTemplate对象，注入DataSource对象

<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>

• 开启组件扫描

<context:component-scan base-package="com.du.spring"/>

• 文件结构
  
• pojo/bean

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
  private int id;
  private String username;
  private String phone;
}

• 编写Dao和Service

public interface UserDao {
  int addUser(User user);
}

// 一般用于持久层（DAO）
@Repository
public class UserDaoImpl implements UserDao {
  @Autowired
  JdbcTemplate jdbcTemplate;   // 自动装配

  @Override
  public int addUser(User user) {
    String sql = "insert into user_info(username, phone) values(?,?);";
    return jdbcTemplate.update(sql, user.getUsername(), user.getPhone());
  }
}

public interface UserService {
  int addUser(User user);
}

// 一般用Service创建对象
@Service
public class UserServiceImpl implements UserService {
  @Autowired
  private UserDao userDaoImpl;

  @Override
  public int addUser(User user) {
    return userDaoImpl.addUser(user);
  }
}

• 测试类

public class Demo {
  private static final Logger logger = Logger.getLogger(Demo.class);
  @Test
  public void test() {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    UserService userServiceImpl = context.getBean("userServiceImpl", UserServiceImpl.class);
    int res = userServiceImpl.addUser(new User("jhon", "1434314"));
    if (res > 0)
      logger.info("添加成功");
    else
      logger.info("添加失败");
  }
}

三、CRUD

• 查询返回某个值

@Override
  public int countUser() {
    String sql = "select count(*) from user_info";
    Integer res = jdbcTemplate.queryForObject(sql, Integer.class);
    if (res != null) return res;
    throw new RuntimeException("查不到");
  }

• 查询某个对象

  @Override
  public User selectUserById(int id) {
    String sql = "select * from user_info where id = ?";
    User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
    if (user != null) return user;
    throw new RuntimeException("查不到");
  }

RowMapper 是接口，针对返回不同类型数据，使用这个接口里面实现类完成数据封装

• 查询一个集合包含多个对象

@Override
  public List<User> selectUsers() {
    String sql = "select id, username, phone from user_info";
    // RowMapper 是接口，针对返回不同类型数据，使用这个接口里面实现类完成数据封装
    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
  }

RowMapper 是接口，针对返回不同类型数据，使用这个接口里面实现类完成数据封装

• 删除

  @Override
  public int deleteUserById(int id) {
    String sql = "delete from user_info where id = ?";
    return jdbcTemplate.update(sql, id);
  }

• 更新

  @Override
  public int updateUser(User user) {
    String sql = "update user_info set username = ?, phone = ? where id = ?;";
    return jdbcTemplate.update(sql, user.getUsername(), user.getPhone(), user.getId());
  }

四、批量操作

• 批量操作：操作表里面多条记录
• 批量增加

@Override
  public void batchAddUsers(List<Object[]> batchArgs) {
    String sql = "insert into user_info(username, phone) values(?,?);";
    // 批量执行sql,并传入多个数组组成的参数
    int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    System.out.println(Arrays.toString(ints));
  }

  @Test
  public void batchAddUsersTest() {
    List<Object[]> batchArgs = new ArrayList<>();
    Object[] o1 = {"nihao", "458785"};
    Object[] o2 = {"shijie", "458785"};
    batchArgs.add(o1);
    batchArgs.add(o2);
    userServiceImpl.batchAddUsers(batchArgs);
  }

• 批量更新

  @Override
  public void batchUpdateUsers(List<Object[]> batchArgs) {
    String sql = "update user_info set username = ?, phone = ? where id = ?;";
    // 批量执行sql,并传入多个数组组成的参数
    int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    System.out.println(Arrays.toString(ints));
  }

  @Test
  public void batchUpdateUsersTest() {
    List<Object[]> batchArgs = new ArrayList<>();
    Object[] o1 = {"nihao", "458785", 5};
    Object[] o2 = {"shijie", "458785", 6};
    batchArgs.add(o1);
    batchArgs.add(o2);
    userServiceImpl.batchUpdateUsers(batchArgs);
  }

• 批量删除

  @Override
  public void batchDeleteUsers(List<Object[]> batchArgs) {
    String sql = "delete from user_info where id = ?";
    // 批量执行sql,并传入多个数组组成的参数
    int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    System.out.println(Arrays.toString(ints));
  }

  @Test
  public void batchDeleteUsersTest() {
    List<Object[]> batchArgs = new ArrayList<>();
    Object[] o1 = {5};
    Object[] o2 = {6};
    batchArgs.add(o1);
    batchArgs.add(o2);
    userServiceImpl.batchDeleteUsers(batchArgs);
  }