SpringBoot（四） SpringBoot整合JdbcTemplate

一、数据准备
CREATE TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`username` varchar(50) NOT NULL COMMENT '用户名',
`age` int(11) NOT NULL COMMENT '年龄',
`ctm` datetime NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES('张三', '18', NOW()) ;
INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES('李四', '20', NOW()) ;
INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES('王五', '19', NOW()) ;

二、引入依赖：
————————————————

<parent>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-parent</artifactId>

       <version>2.0.4.RELEASE</version>

    </parent>

    <properties>

        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>

        <java.version>1.8</java.version>

    </properties>

    <dependencies>

    <!-- jdbcTemplate -->

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-jdbc</artifactId>

        </dependency>

        <!-- MySQL连接 -->

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

            <scope>runtime</scope>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-configuration-processor</artifactId>

            <optional>true</optional>

        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-api -->

        <dependency>

            <groupId>org.apache.logging.log4j</groupId>

            <artifactId>log4j-api</artifactId>

            <version>2.11.2</version>

        </dependency>

        <!-- Spring-Mybatis -->

        <dependency>

            <groupId>org.mybatis.spring.boot</groupId>

            <artifactId>mybatis-spring-boot-starter</artifactId>

            <version>1.3.0</version>

            </dependency>

        <!-- MySQL -->

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

         </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-web</artifactId>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-test</artifactId>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-configuration-processor</artifactId>

            <optional>true</optional>

         </dependency>

         <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-devtools</artifactId>

            <optional>true</optional>

         </dependency>

    </dependencies>

    <build>

        <plugins>

            <plugin>

                <groupId>org.springframework.boot</groupId>

                <artifactId>spring-boot-maven-plugin</artifactId>

                <configuration>

                <fork>true</fork>

                </configuration>

            </plugin>

        </plugins>

  </build>

</project>

三、数据库配置文件
一如既往，我们采用yaml文件配置，当然properties文件也是一样。

注意点，SpringBoot默认采用tomcat-jdbc连接池，如果需要C3P0，DBCP，Druid等作为连接池，需要加入相关依赖以及配置，这里不作说明，采用默认配置即可。

spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/db_user
username: root
password: root
————————————————

四、代码

项目结构如下：

SpringBoot（四） SpringBoot整合JdbcTemplate-LMLPHP

实体类User.class：

package com.demo.bean;

import java.util.Date;

public class User {

    public  int id;

    public  String username;

    public  int age;

    public  Date ctm;

    public User() {

    }

    public User(String username, int age) {

        this.username = username;

        this.age = age;

        this.ctm = new Date();

    }

    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 int getAge() {

        return age;

    }

    public void setAge(int age) {

        this.age = age;

    }

    public Date getCtm() {

        return ctm;

    }

    public void setCtm(Date ctm) {

        this.ctm = ctm;

    }

}

UserDao：

package com.demo.dao;

import java.util.List;

import com.demo.bean.User;

public interface UserDao {

    public User getUserById(Integer id);

    public List<User> getUserList();

}

UserDaoImpl：

package com.demo.dao.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Repository;

import com.demo.bean.User;

import com.demo.dao.UserDao;

@Repository

public class UserDaoImpl implements UserDao{

    @Autowired

    private JdbcTemplate jdbcTemplate;

    @Override

    public User getUserById(Integer id) {

        List<User> list = jdbcTemplate.query("select * from tb_user where id = ?", new Object[]{id}, new BeanPropertyRowMapper(User.class));

        if(list!=null && list.size()>0){

            return list.get(0);

        }else{

            return null;

        }

    }

    @Override

    public List<User> getUserList() {

        List<User> list = jdbcTemplate.query("select * from tb_user", new Object[]{}, new BeanPropertyRowMapper(User.class));

        if(list!=null && list.size()>0){

            return list;

        }else{

            return null;

        }

    }

}

UserService：

package com.demo.service;

import java.util.List;

import com.demo.bean.User;

public interface UserService {

    User getUserById(Integer id);

    public List<User> getUserList();

}

UserServiceImpl：

package com.demo.service.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import com.demo.bean.User;

import com.demo.dao.UserDao;

import com.demo.service.UserService;

@Service

public class UserServiceImpl implements UserService{

    @Autowired

    private UserDao userDao;

    @Override

    public User getUserById(Integer id) {

        return userDao.getUserById(id);

    }

    @Override

    public List<User> getUserList() {

        return userDao.getUserList();

    }

}

UserController：

package com.demo.web;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.http.ResponseEntity;

import org.springframework.web.bind.annotation.PathVariable;

import org.springframework.web.bind.annotation.RequestBody;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RestController;

import com.demo.bean.JsonResult;

import com.demo.bean.User;

import com.demo.service.UserService;

@RestController

public class UserController {

    @Autowired

    private UserService userService;

    @RequestMapping(value = "user/{id}", method = RequestMethod.GET)

    public ResponseEntity<JsonResult> getUserById (@PathVariable(value = "id") Integer id){

        JsonResult r = new JsonResult();

        try {

            User user = userService.getUserById(id);

            r.setResult(user);

            r.setStatus("ok");

        } catch (Exception e) {

            r.setResult(e.getClass().getName() + ":" + e.getMessage());

            r.setStatus("error");

            e.printStackTrace();

        }

        return ResponseEntity.ok(r);

    }

    /**

     * 查询用户列表

     * @return

     */

    @RequestMapping(value = "users", method = RequestMethod.GET)

    public ResponseEntity<JsonResult> getUserList (){

        JsonResult r = new JsonResult();

        try {

            List<User> users = userService.getUserList();

            r.setResult(users);

            r.setStatus("ok");

        } catch (Exception e) {

            r.setResult(e.getClass().getName() + ":" + e.getMessage());

            r.setStatus("error");

            e.printStackTrace();

        }

        return ResponseEntity.ok(r);

    }

}

JsonResult:

package com.demo.bean;

public class JsonResult {

    private String status = null;

    private Object result = null;

    public JsonResult status(String status) {

        this.status = status;

        return this;

    }

    public String getStatus() {

        return status;

    }

    public void setStatus(String status) {

        this.status = status;

    }

    public Object getResult() {

        return result;

    }

    public void setResult(Object result) {

        this.result = result;

    }

}

测试：

访问：

http://localhost:8080/user/1

http://localhost:8080/users