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,在基本的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工具测试(有两种:浏览器插件版,安装版,我用的是安装版),这里简单列举几个测试结果:
Java Persistence API是一种标准技术,可以将对象“映射”到关系数据库。spring-boot-starter-data-jpa POM提供了一种快速入门的方法。它提供了以下关键依赖项:
使用方法:创建持久层实现接口,并用接口实现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