SpringBoot 整合jdbctemplate

前言

JdbcTemplate简介

• JdbcTemplate是Spring框架自带的对JDBC操作的封装，目的是提供统一的模板方法使对数据库的操作更加方便、友好，效率也不错。但是功能还是不够强大（比如不支持级联属性），在实际应用中还需要和hibernate、mybaties等框架混合使用。
• 优点：运行期：高效、内嵌Spring框架中、支持基于AOP的声明式事务。
• 缺点：必须于Spring框架结合在一起使用、不支持数据库跨平台、默认没有缓存。

JdbcTemplate主要提供以下五类方法：

• execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
• update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；
• query方法及queryForXXX方法：用于执行查询相关语句；
• call方法：用于执行存储过程、函数相关语句。

一、pom.xml文件

添加JdbcTemplate依赖。

    <dependencies>
        <!--JdbcTemplate-->
        <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>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

二、配置文件

spring:
  # datasource config
  datasource:
    url: jdbc:mysql://localhost:3306/test?serverTimezone=GMT
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver

三、实体类

User类实现了RowMapper类，重写了mapRow方法。

public class User implements RowMapper<User> {

    private int id;
    private String username;
    private String password;

    public User() {
    }

    public User(int id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    public User(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public User mapRow(ResultSet resultSet, int i) throws SQLException {
        User user = new User();
        user.setId(resultSet.getInt(("id")));
        user.setUsername(resultSet.getString("username"));
        user.setPassword(resultSet.getString("password"));
        return user;
    }
}

四、Controller类

@RestController
public class UserController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    //http://localhost:8080/createTable
    @GetMapping("createTable")
    public String createTable() {
        String sql = "CREATE TABLE `user` (\n" +
                "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
                "  `username` varchar(255) DEFAULT NULL,\n" +
                "  `password` varchar(255) DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`)\n" +
                ") ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;\n" +
                "\n";
        jdbcTemplate.execute(sql);
        return "创建User表成功";
    }

    //http://localhost:8080/saveUserSql
    @GetMapping("saveUserSql")
    public String saveUserSql() {
        String sql = "INSERT INTO USER (username,password) VALUES ('admin','123456')";
        int rows = jdbcTemplate.update(sql);
        return "执行成功，影响" + rows + "行";
    }

    //http://localhost:8080/saveUser?userName=lisi&passWord=111
    @GetMapping("saveUser")
    public String saveUser(String userName, String passWord) {
        int rows = jdbcTemplate.update("INSERT INTO USER (username,password) VALUES (?,?)", userName, passWord);
        return "执行成功，影响" + rows + "行";
    }

    //http://localhost:8080/updateUserPassword?id=1&passWord=111
    @GetMapping("updateUserPassword")
    public String updateUserPassword(int id, String passWord) {
        int rows = jdbcTemplate.update("UPDATE USER SET password = ? WHERE ID = ?", passWord, id);
        return "执行成功，影响" + rows + "行";
    }

    //http://localhost:8080/deleteUserById?id=1
    @GetMapping("deleteUserById")
    public String deleteUserById(int id) {
        int rows = jdbcTemplate.update("DELETE FROM  USER  WHERE ID = ?", id);
        return "执行成功，影响" + rows + "行";
    }

    //http://localhost:8080/batchSaveUserSql
    @GetMapping("batchSaveUserSql")
    public String batchSaveUserSql() {
        String sql =
                "INSERT INTO USER (username,password) VALUES (?,?)";
        List<Object[]> paramList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            String[] arr = new String[2];
            arr[0] = "zhangsan" + i;
            arr[1] = "password" + i;
            paramList.add(arr);
        }
        jdbcTemplate.batchUpdate(sql, paramList);
        return "执行成功";
    }

    //http://localhost:8080/getUserByUserName?userName=zhangsan0
    @GetMapping("getUserByUserName")
    public List getUserByUserName(String userName) {
        String sql = "SELECT * FROM USER WHERE username = ?";
        //写法很多种
        //下面列举两种写法，都可以实现
        //List<User> list= jdbcTemplate.query(sql,new Object[]{userName}, new BeanPropertyRowMapper(User.class));
        List<User> list = jdbcTemplate.query(sql, new User(), new Object[]{userName});
        return list;
    }

    //http://localhost:8080/getMapById?id=1
    @GetMapping("getMapById")
    public Map getMapById(Integer id) {
        String sql = "SELECT * FROM USER WHERE ID = ?";
        Map map = jdbcTemplate.queryForMap(sql, id);
        return map;
    }

    //http://localhost:8080/getUserById?id=1
    @GetMapping("getUserById")
    public User getUserById(Integer id) {
        String sql = "SELECT * FROM USER WHERE ID = ?";
        User user = jdbcTemplate.queryForObject(sql, new User(), new Object[]{id});
        return user;
    }
}