使用Spring JdbcTemplate写一个增删改成Demo

JdbcTemplate概述

• 对原始jdbcAPI的简单封装.

1. 使用JdbcTemplate改进

上一篇博客Spring 零配置注解开发增删改查Demo使用了原生的jdbc开发了一个CRUD的demo, 这次我们使用Spring JdbcTemplate改进.

2. 编码改进

2.1 引入JdbcTemplate依赖

<!-- Spring Jdbc -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.2.4.RELEASE</version>
</dependency>

<!-- Spring JUnit -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>5.2.10.RELEASE</version>
</dependency>

2.2 修改数据源配置类

@Configuration
@PropertySource("classpath:jdbc.properties")
public class DataSourceConfiguration
{
    @Value("${jdbc.driver}")
    private String driver;

    @Value("${jdbc.url}")
    private String url;

    @Value("${jdbc.username}")
    private String username;

    @Value("${jdbc.password}")
    private String password;

    /**
     * 获取数据源, 并存储到Spring容器中
     * @return
     */
    @Bean("dataSource")
    public DataSource getDataSource()
    {
        DruidDataSource druidDataSource = new DruidDataSource();

        druidDataSource.setDriverClassName(driver);
        druidDataSource.setUrl(url);
        druidDataSource.setUsername(username);
        druidDataSource.setPassword(password);

        return druidDataSource;
    }

    /**
     * 获取jdbcTemplate, 并存储到Spring容器中
     * @return
     */
    @Bean("jdbcTemplate")
    public JdbcTemplate getJdbcTemplate()
    {
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(getDataSource());
        return jdbcTemplate;
    }

}

2.3 修改dao层接口

• 这里添加了查询所有用户的接口.

public interface UserDao
{
    /**
     * 查询一个用户
     * @param id 用户id
     * @return 用户
     */
    public User selectOneUser(Integer id);

    /**
     * 查询全部用户
     * @return 用户
     */
    public List<User> selectAllUser();

    /**
     * 添加用户
     * @param user
     * @return 添加结果
     */
    public int insert(User user);

    /**
     * 删除用户
     * @param id 用户id
     * @return 删除结果
     */
    public int delete(Integer id);

    /**
     * 更新用户
     * @param user
     * @return 更新结果
     */
    public int update(User user);
}

2.4 修改dao层实现类

@Repository("userDao")
public class UserDaoImpl implements UserDao
{
    @Autowired
    @Qualifier("jdbcTemplate")
    private JdbcTemplate jdbcTemplate;


    public User selectOneUser(Integer id)
    {
        String sql = "select id, username, age from user where id=?";
        User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
        return user;
    }

    public List<User> selectAllUser()
    {
        String sql = "select id, username, age from user";

        List<User> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));

        return users;
    }

    public int insert(User user)
    {
        String sql = "insert into user(id, username, age) values (?,?,?)";
        int row = jdbcTemplate.update(sql, user.getId(), user.getUsername(), user.getAge());
        return row;
    }

    public int delete(Integer id)
    {
        String sql = "delete from user where id=?";
        int row = jdbcTemplate.update(sql, id);
        return row;
    }

    public int update(User user)
    {
        String sql = "update user set username=?, age=? where id=?";
        int row = jdbcTemplate.update(sql, user.getUsername(), user.getAge(), user.getId());
        return row;
    }

}

2.5 修改Service层接口

public interface UserService
{
    /**
     * 查询一个用户
     * @param id 用户id
     * @return 用户
     */
    public User selectOneUser(Integer id);

    /**
     * 查询全部用户
     * @return 用户
     */
    public List<User> selectAllUser();

    /**
     * 添加用户
     * @param user
     * @return 添加结果
     */
    public boolean insert(User user);

    /**
     * 删除用户
     * @param id 用户id
     * @return 删除结果
     */
    public boolean delete(Integer id);

    /**
     * 更新用户
     * @param user
     * @return 更新结果
     */
    public boolean update(User user);
}

2.6 修改Service层实现类

@Service("userService")
public class UserServiceImpl implements UserService
{
    @Resource(name = "userDao")
    private UserDao userDao;

    public User selectOneUser(Integer id)
    {
        return userDao.selectOneUser(id);
    }

    public List<User> selectAllUser()
    {
        return userDao.selectAllUser();
    }

    public boolean insert(User user)
    {
        return userDao.insert(user)  > 1 ? true : false;
    }

    public boolean delete(Integer id)
    {
        return userDao.delete(id)  > 1 ? true : false;
    }

    public boolean update(User user)
    {
        return userDao.update(user) > 1 ? true : false;
    }
}

2.7 新建测试代码

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {SpringConfiguration.class})
public class JdbcTemplateCRUDTest
{
    @Autowired
    @Qualifier("userService")
    private UserService userService;

    @Test
    public void testSelectAllUser()
    {
        List<User> users = userService.selectAllUser();
        System.out.println(users);
    }
    @Test
    public void testSelectOneUser()
    {
        User user = userService.selectOneUser(1);
        System.out.println(user);
    }
    @Test
    public void testUpdate()
    {
        userService.update(new User(1, "孤云", 22));
    }
    @Test
    public void testInsert()
    {
        userService.insert(new User(2, "Tom", 30));
    }
    @Test
    public void testDelete()
    {
        userService.delete(2);
    }
}

3. 结语

• 可以看到dao层代码明显减少了, 简化了我们开发.