Spring-JdbcTemplate

JdbcTemplate概述

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

引入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.16</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.23</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.3.2</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>5.3.2</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>5.3.2</version>
</dependency>

配置数据库连接池

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
    <property name="driverClassName" value="${jdbc.driverClass}"></property>
    <property name="url" value="${jdbc.url}"></property>
    <property name="username" value="${jdbc.username}"></property>
    <property name="password" value="${jdbc.password}"></property>
</bean>

配置JdbcTemplate对象,注入DataSource

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

实现添加功能

创建数据库对应实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private String userId;
    private String userName;
    private String uStatus;
}

编写service和dao,在dao实现类实现添加功能

public void add(User user) {
    // 创建sql语句
    String sql = "INSERT INTO `userdb`.`t_user` (`user_id`, `username`, `ustatus`) VALUES (?, ?, ?);";
    // 调用方法实现
    int update = jdbcTemplate.update(sql, user.getUserId(), user.getUserName(), user.getUStatus());
    System.out.println(update);
}

测试添加

@Test
public void add() {
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean6.xml");
    UserService userService = context.getBean("userService", UserService.class);
    User user = new User("2", "xyx", "1");
    userService.addUser(user);
}

实现批量添加功能

dao编写批量添加方法

使用batchUpdate实现批量添加

public void batchAdd(List<Object[]> batchArgs) {
    // 创建sql语句
    String sql = "INSERT INTO `userdb`.`t_user` (`user_id`, `username`, `ustatus`) VALUES (?, ?, ?);";
    jdbcTemplate.batchUpdate(sql, batchArgs);
}

测试批量添加

@Test
public void batchAdd() {
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean6.xml");
    UserService userService = context.getBean("userService", UserService.class);
    List<Object[]> list = new ArrayList<>();
    Object[] user1 = {"3", "czs", "1"};
    Object[] user2 = {"4", "czs", "2"};
    Object[] user3 = {"5", "czs", "3"};
    list.add(user1);
    list.add(user2);
    list.add(user3);
    userService.batchAdd(list);
}

实现修改功能

dao编写修改方法

public void updateUser(User user) {
    // 创建sql语句
    String sql = "UPDATE `userdb`.`t_user` SET `username` = ?, `ustatus` = ? WHERE `user_id` = ?;";
    // 调用方法实现
    jdbcTemplate.update(sql, user.getUserName(), user.getUStatus(), user.getUserId());
}

测试修改

@Test
public void update() {
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean6.xml");
    UserService userService = context.getBean("userService", UserService.class);
    User user = new User("2", "xyx111", "1");
    userService.updateUser(user);
}

实现批量修改

dao编写批量修改方法

public void batchUpdate(List<Object[]> batchArgs) {
    // 创建sql语句
    String sql = "UPDATE `userdb`.`t_user` SET `username` = ?, `ustatus` = ? WHERE `user_id` = ?;";
    // 调用方法实现
    jdbcTemplate.batchUpdate(sql, batchArgs);
}

测试批量修改

public void batchUpdate() {
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean6.xml");
    UserService userService = context.getBean("userService", UserService.class);
    List<Object[]> list = new ArrayList<>();
    // 根据sql传参顺序构建参数
    Object[] user1 = {"czs", "1", "3"};
    Object[] user2 = {"czs", "2", "4"};
    Object[] user3 = {"czs", "3", "5"};
    list.add(user1);
    list.add(user2);
    list.add(user3);
    userService.batchUpdate(list);
}

实现删除功能

dao编写删除方法

public void deleteUser(String id) {
    // 创建sql语句
    String sql = "DELETE FROM `userdb`.`t_user` WHERE `user_id` = ?;";
    jdbcTemplate.update(sql, id);
}

测试删除

@Test
public void delete() {
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean6.xml");
    UserService userService = context.getBean("userService", UserService.class);
    userService.deleteUser("2");
}

实现批量删除功能

dao编写批量删除方法

public void batchDelete(List<Object[]> batchArgs) {
    // 创建sql语句
    String sql = "DELETE FROM `userdb`.`t_user` WHERE `user_id` = ?;";
    jdbcTemplate.update(sql, batchArgs);
}

测试批量删除

public void batchDelete() {
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean6.xml");
    UserService userService = context.getBean("userService", UserService.class);
    List<Object[]> list = new ArrayList<>();
    // 根据sql传参顺序构建参数
    Object[] user1 = {"3"};
    Object[] user2 = {"4"};
    list.add(user1);
    list.add(user2);
    userService.batchDelete(list);
}

实现查询功能

查询返回基本数据类型

dao编写查询方法

使用queryForObject进行查询,requiredType指定返回的类型

@Override
public int findCount() {
    // 创建sql语句
    String sql = "SELECT COUNT(*) FROM `t_user`";
    Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
    return count;
}

查询返回单个对象

dao编写查询方法

使用queryForObject进行查询,BeanPropertyRowMapper指定封装成什么对象返回

public User findUserInfo(String id) {
    String sql = "SELECT * FROM t_user WHERE user_id=?";
    User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
    return user;
}

测试查询

@Test
public void findUserInfo() {
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean6.xml");
    UserService userService = context.getBean("userService", UserService.class);
    User userInfo = userService.findUserInfo("1");
    System.out.println(userInfo);
}

查询返回集合

dao编写查询方法

public List<User> findAllUser() {
    String sql = "SELECT * FROM t_user";
    List<User> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
    return list;
}

测试查询

@Test
public void findAllUser() {
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean6.xml");
    UserService userService = context.getBean("userService", UserService.class);
    List<User> list = userService.findAllUser();
    System.out.println(list);
}