一、介绍

　　SpringBoot框架为使用SQL数据库提供了广泛的支持，从使用JdbcTemplate的直接JDBC访问到完整的“对象关系映射”技术(如Hibernate)。Spring-data-jpa提供了额外的功能级别:直接从接口创建存储库实现，并使用约定方法名生成查询。

　　建表：

CREATE TABLE `user` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `address` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)

)；

INSERT INTO `user` VALUES ('3', 'andy', '6', 'month');

INSERT INTO `user` VALUES ('4', 'andy', '7', 'month');

INSERT INTO `user` VALUES ('5', 'andy', '8', 'month');

INSERT INTO `user` VALUES ('6', 'jack', '3', 'aaa');

CREATE TABLE `student` (

  `id` int(11) NOT NULL,

  `age` int(11) NOT NULL,

  `grade` int(11) NOT NULL,

  `name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)

)；

INSERT INTO `student` VALUES ('1', '2', '3', 'jack');

INSERT INTO `student` VALUES ('2', '4', '2', 'andy');

二、JdbcTemplate

　　在需要使用持久层的类中直接注入JdbcTemplate，在基本的SpringBoot配置（SpringBoot-HelloWorld）下增加配置数据库连接驱动器：

<dependency>

     <groupId>mysql</groupId>

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

     <version>5.1.21</version>

</dependency>

　　配置jdbc的依赖库：

<!-- jdbcTemplate -->

<dependency>

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

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

</dependency>

　　在application.properties默认属性文件中增加数据库连接信息：

spring.datasource.url=jdbc:mysql://192.168.1.121:3306/test

spring.datasource.username=root

spring.datasource.password=admincss

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

　　创建实体类user:

package com.cn.entity;

import java.io.Serializable;

/**

 * @program: spring-boot-example

 * @description: 用户类

 * @author:

 * @create: 2018-05-02 09:59

 **/

public class User implements Serializable{

    private int id;

    private String name;

    private int age;

    private String address;

    @Override

    public String toString() {

        return "User{" +

            "id=" + id +

            ", name='" + name + '\'' +

            ", age=" + age +

            ", address='" + address + '\'' +

            '}';

    }

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

        return age;

    }

    public void setAge(int age) {

        this.age = age;

    }

    public String getAddress() {

        return address;

    }

    public void setAddress(String address) {

        this.address = address;

    }

}

User.java

　　创建UserService接口，UserServiceImpl(内有User映射内部类)服务实现类：

package com.cn.service;

import com.cn.entity.User;

import java.util.List;

/**

 * @program: spring-boot-example

 * @description:

 * @author:

 * @create: 2018-05-02 10:02

 **/

public interface UserService {

    User getUserById(int id);

    List<User> getUsers();

    int deleteUserById(int id);

    int updateUserById(User user);

    int insertUser(User user);

}

UserService.java

package com.cn.service;

import com.cn.entity.User;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

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

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.PreparedStatementCreator;

import org.springframework.jdbc.core.PreparedStatementSetter;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.support.GeneratedKeyHolder;

import org.springframework.jdbc.support.KeyHolder;

import org.springframework.stereotype.Service;

/**

 * @program: spring-boot-example

 * @description:

 * @author:

 * @create: 2018-05-02 10:07

 **/

@Service

public class UserServiceImpl implements UserService{

    @Autowired

    private JdbcTemplate jdbcTemplate;

    @Override

    public User getUserById(int id) {

        User user = jdbcTemplate.queryForObject("select * from user where id=?", new Object[]{id},new UserRowMapper());

        return user;

    }

    @Override

    public List<User> getUsers() {

        return jdbcTemplate.query("select * from user",new UserRowMapper());

    }

    @Override

    public int deleteUserById(int id) {

        return jdbcTemplate.update("delete from user where id=?", new PreparedStatementSetter() {

            @Override

            public void setValues(PreparedStatement preparedStatement) throws SQLException {

                preparedStatement.setInt(1,id);

            }

        });

    }

    @Override

    public int updateUserById(User user) {

        return jdbcTemplate.update("update user SET name=?,age=?,address=? where id=?", new PreparedStatementSetter() {

            @Override

            public void setValues(PreparedStatement preparedStatement) throws SQLException {

                preparedStatement.setString(1,user.getName());

                preparedStatement.setInt(2,user.getAge());

                preparedStatement.setString(3,user.getAddress());

                preparedStatement.setInt(4,user.getId());

            }

        });

    }

    @Override

    public int insertUser(User user) {

        String sql = "insert into user(name,age,address) VALUES (?,?,?)";

        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override

            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {

                PreparedStatement preparedStatement = connection.prepareStatement(sql,new String[]{"id"});

                preparedStatement.setString(1,user.getName());

                preparedStatement.setInt(2,user.getAge());

                preparedStatement.setString(3,user.getAddress());

                return preparedStatement;

            }

        },keyHolder);

        return Integer.parseInt(keyHolder.getKey().toString());

    }

}

class UserRowMapper implements RowMapper<User> {

    @Override

    public User mapRow(ResultSet resultSet, int i) throws SQLException {

        User user=new User();

        user.setId(resultSet.getInt("id"));

        user.setName(resultSet.getString("name"));

        user.setAge(resultSet.getInt("age"));

        user.setAddress(resultSet.getString("address"));

        return user;

    }

}

UserServiceImpl.java

　　创建UserController：

package com.cn.controller;

import com.cn.entity.User;

import com.cn.service.UserService;

import java.util.List;

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

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;

/**

 * @program: spring-boot-example

 * @description:

 * @author:

 * @create: 2018-05-02 09:58

 **/

@RestController

public class UserController {

    @Autowired

    private UserService userService;

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

    public User getUserById(@PathVariable int id) {

        return userService.getUserById(id);

    }

    @RequestMapping("getUsers")

    public List<User> getUsers() {

        return userService.getUsers();

    }

    @RequestMapping(value = "updateUserById",method = RequestMethod.POST)

    public int updateUserByUd(User user) {

        return userService.updateUserById(user);

    }

    @RequestMapping(value = "insertUser",method = RequestMethod.POST)

    public int insertUser(User user) {

        return userService.insertUser(user);

    }

    @RequestMapping(value = "deleteUserById/{id}",method = RequestMethod.DELETE)

    public int deleteUserById(@PathVariable int id) {

        return userService.deleteUserById(id);

    }

}

UserController.java

　　使用Postman工具测试（有两种：浏览器插件版，安装版，我用的是安装版），这里简单列举几个测试结果：

　　（三）SpringBoot基础篇- 持久层，jdbcTemplate和JpaRespository-LMLPHP

三、JpaRepository

　　Java Persistence API是一种标准技术，可以将对象“映射”到关系数据库。spring-boot-starter-data-jpa POM提供了一种快速入门的方法。它提供了以下关键依赖项:

Hibernate: One of the most popular JPA implementations.
Spring Data JPA: Makes it easy to implement JPA-based repositories.
Spring ORMs: Core ORM support from the Spring Framework.

　　使用方法：创建持久层实现接口，并用接口实现JpaRepository<%Bean%,%PrimaryKey%>（Bean为实体类，PrimaryKey为实体类的主键，在JpaRepository中已经有部分接口方法，视情况自加）；

　　增加pom库的依赖：

<!-- spring-data-jpa -->

<dependency>

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

    <artifactId>spring-boot-starter-data-jpa</artifactId>

</dependency>

　　创建实体类Student（注意要声明实体类的注解，@Entity、@Id）：

package com.cn.entity;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

/**

 * @program: spring-boot-example

 * @description: 学生实体类

 * @author:

 * @create: 2018-05-02 10:47

 **/

@Entity

public class Student {

    @Id

    @GeneratedValue

    private int id;

    private String name;

    private int age;

    private int grade;

    @Override

    public String toString() {

        return "Student{" +

            "id=" + id +

            ", name='" + name + '\'' +

            ", age=" + age +

            ", grade=" + grade +

            '}';

    }

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

        return age;

    }

    public void setAge(int age) {

        this.age = age;

    }

    public int getGrade() {

        return grade;

    }

    public void setGrade(int grade) {

        this.grade = grade;

    }

}

Student.java

　　创建StudentService接口，StudentServiceImpl实现类：

package com.cn.service;

import com.cn.entity.Student;

/**

 * @program: spring-boot-example

 * @description:

 * @author:

 * @create: 2018-05-02 11:12

 **/

public interface StudentService {

    Student findByName(String name);

    Student findByNameAndAge(String name, Integer age);

    Student findUser(String name);

}

StudentService.java

package com.cn.service;

import com.cn.dao.StudentDao;

import com.cn.entity.Student;

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

import org.springframework.stereotype.Service;

/**

 * @program: spring-boot-example

 * @description:

 * @author:

 * @create: 2018-05-02 11:13

 **/

@Service

public class StudentServiceImpl implements StudentService{

    @Autowired

    private StudentDao studentDao;

    @Override

    public Student findByName(String name) {

        return studentDao.findByName(name);

    }

    @Override

    public Student findByNameAndAge(String name, Integer age) {

        return studentDao.findByNameAndAge(name,age);

    }

    @Override

    public Student findUser(String name) {

        return studentDao.findUser(name);

    }

}

StudentServiceImpl.java

　　创建StudentController：

package com.cn.controller;

import com.cn.entity.Student;

import com.cn.service.StudentService;

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

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

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

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

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

/**

 * @program: spring-boot-example

 * @description:

 * @author:

 * @create: 2018-05-02 11:15

 **/

@RestController

public class StudentController {

    @Autowired

    private StudentService studentService;

    @RequestMapping("findByName/{name}")

    public Student findByName(@PathVariable String name) {

        return studentService.findByName(name);

    }

    @RequestMapping("findByNameAndAge")

    public Student findByNameAndAge(@RequestParam("name") String name,@RequestParam("age") Integer age) {

        return studentService.findByNameAndAge(name,age);

    }

    @RequestMapping("findUser/{name}")

    public Student findUser(@PathVariable String name) {

        return studentService.findUser(name);

    }

}

StudentController.java

　　同样适用Postman测试，结果如下：

　　（三）SpringBoot基础篇- 持久层，jdbcTemplate和JpaRespository-LMLPHP

完整示例：https://gitee.com/lfalex/spring-boot-example

参考官方文档：https://docs.spring.io/spring-boot/docs/current-SNAPSHOT/reference/htmlsingle/#boot-documentation