Spring Boot JdbcTemplate 配置

配置

1.pom.xml 依赖

在 Spring Boot 项目中加入 spring-boot-starter-jdbc、mysql-connector-java 的依赖

<!-- 数据库 --> 
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId> 
</dependency>
<dependency>
    <groupId>mysql</groupId> 
    <artifactId>mysql-connector-java</artifactId> 
    <scope>runtime</scope>
</dependency>

2.配置数据源

# mysql
spring.datasource.url=jdbc:mysql://localhost/spring_boot_demo?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

注意：

• 可以不指定 driver-class-name，spring boot 会自动识别 url
• 数据连接池默认使用 tomcat-jdbc

使用 JdbcTemplate 模板

1.测试脚本

CREATE TABLE `user` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `name` varchar(255) DEFAULT NULL,
          `create_time` datetime DEFAULT NULL,
          PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';

2.实体类

/**
 * 实体类
 */
public class User {
    private int id;
    private String name;
    private Date createTime;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Date getCreateTime() {
        return createTime;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
    @Override
    public String toString() {
        return "RoncooUser [id=" + id + ", name=" + name + ", createTime=" + createTime + "]";
    }
}

3.dao 接口

/**
 * 用户 dao
 */
public interface UserDao {
    int insert(User user);
    int deleteById(int id);
    int updateById(User user);
    User selectById(int id);
}

4.dao 实现类

public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public int insert(User user) {
        String sql = "insert into user (name, create_time) values (?, ?)";
        return jdbcTemplate.update(sql, user.getName(), user.getCreateTime());
    }
    @Override
    public int deleteById(int id) {
        String sql = "delete from user where id=?";
        return jdbcTemplate.update(sql, id);
    }
    @Override
    public int updateById(User user) {
        String sql = "update user set name=?, create_time=? where id=?";
        return jdbcTemplate.update(sql, user.getName(), user.getCreateTime(), user.getId());
    }
    @Override
    public User selectById(int id) {
        String sql = "select * from user where id=?";
        return jdbcTemplate.queryForObject(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setCreateTime(rs.getDate("create_time"));
                return user;
            }
        }, id);
    }
}

5.测试类

@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringBootDemo131ApplicationTests {
    @Autowired
    private RoncooUserDao roncooUserDao;
    @Test
    public void insert() {
        RoncooUser roncooUser = new RoncooUser();
        roncooUser.setName("测试");
        roncooUser.setCreateTime(new Date());
        int result = roncooUserDao.insert(roncooUser);
        System.out.println(result);
    }
    @Test
    public void delete() {
        int result = roncooUserDao.deleteById(1);
        System.out.println(result);
    }
    @Test
    public void update() {
        RoncooUser roncooUser = new RoncooUser();
        roncooUser.setId(2);
        roncooUser.setName("测试2");
        roncooUser.setCreateTime(new Date());
        int result = roncooUserDao.updateById(roncooUser);
        System.out.println(result);
    }
    @Test
    public void select() {
        RoncooUser result = roncooUserDao.selectById(2);
        System.out.println(result);
    }
    @Test
    public void select2() {
        RoncooUser result = roncooUserDao.selectById(7);
        System.out.println(result);
    }
    // 分页测试
    @Test
    public void queryForPage(){
        Page<RoncooUser> result = roncooUserDao.queryForPage(1, 20, "测试");
        System.out.println(result.getList());
    }
}

6.打印 sql 语句，添加如下

<logger name="org.springframework.jdbc.core.JdbcTemplate" level="debug"/>

封装 Spring JDBC，带分页

提供三个类：JdbcDaoImpl.java、Page.java、Sql.java，具体查看附件。

JdbcDaoImpl.javaPage.javaSql.java