尚硅谷-Spring5.2.6-源码讲解课程-JdbcTemplate篇

原创已于 2024-10-19 15:18:44 修改 · 440 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#java

于 2024-10-19 14:21:21 首次发布

Spring5 专栏收录该内容

4 篇文章

订阅专栏

JdbcTemplate简介

理解：Spring对JDBC模块进行封装，定义了JdbcTemplate类，便于开发人员更方便地实现对数据库的操作，实际上就可以直接通过JdbcTemplate对象调用Spring预先定义好的方法就可以，简化了原始的JDBC操作代码。

导入依赖：mysql-connector-数据库驱动、sring-jdbc、spring-tx、

若是与其他ORM框架整合，如mybatis等，则还需要spring-orm

JdbcTemplate功能演示

准备工作

1.配置数据库连接池、创建JdbcTemplate的Bean并注入属性

可以看到JdbcTemplate的有参构造方法中，是通过set方法注入的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"
       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">

    <!--开启组件扫描-->
    <context:component-scan base-package="com.coffeeship"/>
    
    <!--配置数据库连接池-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:13306/dbtest"/>
        <property name="username" value="root"/>
        <property name="password" value="qts0922"/>
    </bean>

    <!--JdbcTemplate对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

2.准备业务类UserService和持久化数据的接口UserDao及其实现类UserDaoImpl

service层和dao层分别添加数据增加方法，dao层调用JdbcTemplate类中的方法

@Service
public class UserService {
    @Autowired
    private UserDao userDao;

    public void addUser(User user) {
        userDao.addUser(user);
    }
}

public interface UserDao {

    void addUser(User user);
}

@Repository
public class UserDaoImpl implements UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void addUser(User user) {
        String sql = "insert into user values (?, ?)";
        Object[] args = {user.getUserId(), user.getUserName()};
        jdbcTemplate.update(sql, args);
    }
}

3.创建数据库中数据表对应的实体User类

public class User {
    private String userId;
    private String userName;

    public User() {
    }

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

    public String getUserId() {
        return userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }
}

测试

@Test
public void test01() {
    ApplicationContext applicationContext = new ClassPathXmlApplicationContext("jdbcbean.xml");
    UserService userSerice = applicationContext.getBean("userService", UserService.class);
    User coffeeship = new User("1006", "coffeeship");
    userSerice.addUser(coffeeship);
}

增删改查

补全CRUD操作，修改删除同增加，

@Override
public void addUser(User user) {
    String sql = "insert into user values (?, ?)";
    Object[] args = {user.getUserId(), user.getUserName()};
    jdbcTemplate.update(sql, args);
}

@Override
public void updateUser(User user) {
    String sql = "update user set name = ? where id = ?";
    Object[] args = {user.getUserName(), user.getUserId()};
    jdbcTemplate.update(sql, args);
}

@Override
public void deleteUser(String id) {
    String sql = "delete from user where id = ?";
    jdbcTemplate.update(sql, id);
}

查询分查询单个值、对象和集合。

查询单个值

<T> T queryForObject(String sql, Class<T> requiredType)

@Override
public int queryCount() {
    String sql = "select count(*) from user";
    Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
    return count;
}

查询返回对象

sql，rowmapper接口实现类对象，参数

<T> T queryForObject(String var1, RowMapper<T> var2, @Nullable Object... var3)

@Override
public User queryUser(String id) {
    String sql = "select * from user where id = ?";
    User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
    return user;
}

这里有一个问题，返回的User对象属性都为空，这是因为数据库中的字段名和类的属性不一致造成的，User实体类中是setUserId和setUserName，jdbcTemplate就会找字段名为user_id和user_name的属性值，将其注入，但没有找到，因为数据库中的字段名是id和name。

更改了User类的属性后，就成功注入了。

或者更改数据库表的字段名也可以：

查询返回集合

<T> List<T> query(String var1, RowMapper<T> var2)

@Override
public List<User> queryUsers() {
    String sql = "select * from user";
    List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
    return userList;
}

批量添加、修改和删除

@Override
public int addUsers(List<Object[]> users) {
    String sql = "insert into user values(?, ?)";
    int[] ints = jdbcTemplate.batchUpdate(sql, users);
    return ints.length;
}

@Override
public int updateUsers(List<Object[]> users) {
    String sql = "update user set userName = ? where userId = ?";
    int[] ints = jdbcTemplate.batchUpdate(sql, users);
    return ints.length;
}

@Override
public int deleteUsers(List<Object[]> ids) {
    String sql = "delete from user where userId = ?";
    int[] ints = jdbcTemplate.batchUpdate(sql, ids);
    return ints.length;
}

测试类

public void test07() {
    ApplicationContext applicationContext = new ClassPathXmlApplicationContext("jdbcbean.xml");
    UserService userSerice = applicationContext.getBean("userService", UserService.class);
    List<Object[]> batchArgs = new ArrayList<>(3);
    batchArgs.add(new Object[]{"1002", "java"});
    batchArgs.add(new Object[]{"1003", "spring"});
    batchArgs.add(new Object[]{"1004", "mybatis"});
    int adds = userSerice.addUsers(batchArgs);
    System.out.println(adds
    );
}

@Test
public void test08() {
    ApplicationContext applicationContext = new ClassPathXmlApplicationContext("jdbcbean.xml");
    UserService userSerice = applicationContext.getBean("userService", UserService.class);
    List<Object[]> batchArgs = new ArrayList<>(3);
    batchArgs.add(new Object[]{"javas", "1002"});
    batchArgs.add(new Object[]{"springboot", "1003"});
    batchArgs.add(new Object[]{"hibernate", "1004"});
    int updates = userSerice.updateUsers(batchArgs);
    System.out.println(updates);
}

@Test
public void test09() {
    ApplicationContext applicationContext = new ClassPathXmlApplicationContext("jdbcbean.xml");
    UserService userSerice = applicationContext.getBean("userService", UserService.class);
    List<Object[]> batchArgs = new ArrayList<>(2);
    batchArgs.add(new Object[]{"1002"});
    batchArgs.add(new Object[]{"1004"});
    int deletes = userSerice.deleteUsers(batchArgs);
    System.out.println(deletes);
}