一、介绍
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; } }
创建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); }
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; } }
创建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); } }
使用Postman工具测试(有两种:浏览器插件版,安装版,我用的是安装版),这里简单列举几个测试结果:
三、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; } }
创建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); }
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); } }
创建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); } }
同样适用Postman测试,结果如下:
完整示例:https://gitee.com/lfalex/spring-boot-example
参考官方文档:https://docs.spring.io/spring-boot/docs/current-SNAPSHOT/reference/htmlsingle/#boot-documentation