SpringBoot 使用 JPA 连接MySQL 完整 Demo 样例
完整项目已经提交到GitHub上：SpringBoot-JPA-MySQL Demo样例
我用的是自己搭建的Linux环境中的MySQL，不管它只要有MySQL就行了吧，不管你是Windows上面，还是自己搭的Linux都行...
项目工程结构：
先看下项目中使用到的RESTful风格的API
如果你并不是很明白什么是RESTful，推荐你看下阮一峰的这篇博文：《理解RESTful 架构》
好了，废话不多说，直接怼代码：
先看下application.properties配置文件：
server.port=9001
server.servlet.context-path=/sp-demo
logging.config=classpath:conf/logback-config.xml
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.137.150:3306/test_database_01
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.database=mysql上面的参数都很好理解吧..
端口、上下文路径、logback配置文件路径、数据库设置、jpa设置
logback.xml
<?xml version="1.0" encoding="UTF-8" ?>
<configuration scan="true" scanPeriod="30 seconds" debug="false">
<include resource="org/springframework/boot/logging/logback/defaults.xml"/>
<!--定义日志文件的存储地址-->
<property name="LOG_HOME" value="./log"/>
<!-- 彩色日志 -->
<!-- 彩色日志依赖的渲染类 -->
<conversionRule conversionWord="clr" converterClass="org.springframework.boot.logging.logback.ColorConverter"/>
<conversionRule conversionWord="wex" converterClass="org.springframework.boot.logging.logback.WhitespaceThrowableProxyConverter"/>
<conversionRule conversionWord="wEx" converterClass="org.springframework.boot.logging.logback.ExtendedWhitespaceThrowableProxyConverter"/>
<!-- 彩色日志格式 -->
<property name="CONSOLE_LOG_PATTERN"
value="${CONSOLE_LOG_PATTERN:-%clr(%d{yyyy-MM-dd HH:mm:ss.SSS}){faint} %clr(${LOG_LEVEL_PATTERN:-%5p}) %clr(${PID:- }){magenta} %clr(---){faint} %clr([%15.15t]){faint} %clr(%-40.40logger{39}){cyan} %clr(:){faint} %m%n${LOG_EXCEPTION_CONVERSION_WORD:-%wEx}}"/>
<!-- Console 输出设置 -->
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<encoder charset="UTF-8">
<!-- 显示毫秒数
<pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger - %msg%n</pattern>-->
<pattern>${CONSOLE_LOG_PATTERN}</pattern>
</encoder>
</appender>
<appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<!--日志文件输出的文件名-->
<FileNamePattern>${LOG_HOME}/02-springboot-demo.%d{yyyy-MM-dd}.log</FileNamePattern>
<!--日志文件保留天数-->
<MaxHistory>30</MaxHistory>
</rollingPolicy>
<!--日志文件最大的大小-->
<triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">
<MaxFileSize>10MB</MaxFileSize>
</triggeringPolicy>
<encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
<!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符-->
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
</encoder>
</appender>
<!-- * 通配符 设置log打印级别 对所有类有效TRACE, DEBUG, INFO, WARN, ERROR, ALL 和 OFF-->
<!--将上面的appender添加到root-->
<root level="INFO">
<appender-ref ref="STDOUT"/>
<appender-ref ref="FILE"/>
</root>
</configuration>项目中创建的数据库、表：
CREATE DATABASE test_database_01;
CREATE TABLE `girl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT NULL,
`cup_size` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;Application主类没什么修改的，这里就不贴出来了。
Girl bean对象
package com.server.bean;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
/**
* @author CYX
* @create 2018-07-10-18:55
*/
@Entity
@Table(name = "girl")
public class Girl extends Pagination {
@Id
@GeneratedValue
@Column(name = "id")
private Integer id;
@Column(name = "cup_size")
private String cupSize;
@Column(name = "age")
private Integer age;
public Girl() {
}
@Override
public String toString() {
return "Girl{" +
"id=" + id +
", cupSize='" + cupSize + '\'' +
", age=" + age +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCupSize() {
return cupSize;
}
public void setCupSize(String cupSize) {
this.cupSize = cupSize;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
@Entity注解：指明这是一个和数据库表映射的实体类.
@Table注解：指明这个bean对象指向的数据库表
@Id注解：指明这个属性映射为数据库的主键.
@GeneratedValue：默认使用主键生成方式为自增.
@Column注解：指明这个Bean字段对应数据库中的哪个字段
分页对象
package com.server.bean;
/**
* 分页参数对象
*
* @author CYX
* @create 2018-07-14-19:36
*/
public class Pagination {
/**
* 分页页码,默认1
*/
private int page = 1;
/**
* 分页每页数量,默认20
*/
private int pageSize = 20;
public int getPage() {
return page;
}
public void setPage(int page) {
this.page = page;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
@Override
public String toString() {
return "Page{" +
"page=" + page +
", pageSize=" + pageSize +
'}';
}
}上面两个bean类，是测试载体，其中Girl 继承了Pagination分页Bean，用于后面测试分页。
JPA 实现类
package com.server.repository;
import com.server.bean.Girl;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;
/**
* JPA实现类
*
* @author CYX
* @create 2018-07-10-18:44
*/
public interface GirlRepository extends JpaRepository<Girl, Integer> {
/**
* 通过年龄查询女生信息
*
* @param age 年龄
* @return
*/
List<Girl> findGirlsByAge(Integer age);
/**
* 根据年龄、cupsize查询女生信息
*
* @param age
* @param cupSize
* @return
*/
List<Girl> findGirlsByAgeAndCupSize(Integer age, String cupSize);
/**
* 根据cupSize查找女生
* <br>
* 使用自定义SQL语句
* <br>
* '@Query':实用类配置自定义SQL的注解,后面参数nativeQuery = true,才是表明使用原生SQL.
* 如果不配置,默认是false,则使用HQL查询方式.
*
* @param cupSize
* @return
*/
@Query(value = "SELECT g.id , g.age , g.cup_size FROM girl AS g where g.cup_size = ?1", nativeQuery = true)
List<Girl> findGirlsByCupSize(String cupSize);
}下面看下Controller类
package com.server.controller;
import com.server.bean.Girl;
import com.server.repository.GirlRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
/**
* 女生-Controller
*
* @author CYX
* @create 2018-07-10-18:59
*/
@RestController
public class GirlController {
private static final Logger LOGGER = LoggerFactory.getLogger(GirlController.class);
@Autowired
private GirlRepository girlRepository;
/**
* 添加一个女生信息
*
* @param cupSize
* @param age
* @return
*/
@PostMapping(value = "/girls")
public String addGirlInfo(@RequestParam("cupSize") String cupSize, @RequestParam("age") Integer age) {
final String method = "addGirlInfo";
LOGGER.info("method : {} , cupSize : {} , age : {} - start", new Object[]{method, cupSize, age});
Girl girl = new Girl();
girl.setCupSize(cupSize);
girl.setAge(age);
Girl resultGirl = girlRepository.save(girl);
LOGGER.info("method : {} , result : {} - end", new Object[]{method, resultGirl});
return resultGirl.toString();
}
/**
* 通过id查询一个女生信息
*
* @param id
* @return
*/
@GetMapping(value = "/girls/{id}")
public Girl getGirlInfoByID(@PathVariable("id") Integer id) {
final String method = "getGirlInfoByID";
LOGGER.info("method : {} , id : {} - start", new Object[]{method, id});
Girl girl = girlRepository.findById(id).get();
LOGGER.info("method : {} , result : {} - end", new Object[]{method, girl.toString()});
return girl;
}
/**
* 通过id更新女生信息
*
* @param id
* @param cupSize
* @param age
* @return
*/
@PutMapping(value = "/girls/{id}")
public String updataGirlByID(
@PathVariable("id") Integer id,
@RequestParam("cupSize") String cupSize,
@RequestParam("age") Integer age) {
final String method = "updataGirlByID";
LOGGER.info("method : {} , id : {} , cupSize : {} , age : {} - start", new Object[]{method, id, cupSize, age});
Girl girl = new Girl();
girl.setId(id);
girl.setAge(age);
girl.setCupSize(cupSize);
Girl result = girlRepository.save(girl);
LOGGER.info("method : {} , result : {} - end", new Object[]{method, result.toString()});
return result.toString();
}
/**
* 获取所有女生信息
*
* @return
*/
@GetMapping(value = "/girls")
public List<Girl> getAllGirlsInfos() {
final String method = "getAllGirlsInfos";
LOGGER.info("method : {} - start", new Object[]{method});
List<Girl> girls = girlRepository.findAll();
LOGGER.info("method : {} , result : {} - end", new Object[]{method, girls.toString()});
return girls;
}
/**
* 通过id删除一个女生信息
*
* @param id
* @return
*/
@DeleteMapping(value = "/girls/{id}")
public Boolean deleteGirlInfoByID(@PathVariable("id") Integer id) {
final String method = "deleteGirlInfoByID";
LOGGER.info("method : {} , id : {} - start", new Object[]{method, id});
boolean result = true;
try {
girlRepository.deleteById(id);
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
result = false;
}
LOGGER.info("method : {} , result : {} - end", new Object[]{method, result});
return result;
}
/**
* 通过age查询女生信息
*
* @param age
*/
@GetMapping(value = "/girls/byAge/{age}")
public List<Girl> findGirlsByAge(@PathVariable("age") Integer age) {
final String method = "findGirlsByAge";
LOGGER.info("method : {} , age : {} - start", new Object[]{method, age});
List<Girl> result = null;
try {
result = girlRepository.findGirlsByAge(age);
} catch (Exception e) {
LOGGER.error(e.getMessage());
}
LOGGER.info("method : {} , result : {} - end", new Object[]{method, result});
return result;
}
/**
* 通过年龄和cupSize查找女生信息
*
* @param age
* @param cupSize
* @return
*/
@GetMapping(value = "/girls/byAgeAndCupSize")
public List<Girl> findGirlsByAgeAndCupSize(Integer age, String cupSize) {
final String method = "findGirlsByAgeAndCupSize";
LOGGER.info("method : {} , age : {} , cupSize : {} - start", new Object[]{method, age, cupSize});
List<Girl> result = null;
try {
result = girlRepository.findGirlsByAgeAndCupSize(age, cupSize);
} catch (Exception e) {
LOGGER.error(e.getMessage());
}
LOGGER.info("method : {} , result : {} - end ", new Object[]{method, result});
return result;
}
/**
* 获取女生信息列表-根据年龄排序
*
* @return
*/
@GetMapping(value = "/girls/sortByAge")
public List<Girl> findAllGirlsWithSort() {
final String method = "findAllGirlsWithSort";
LOGGER.info("method : {} - start", new Object[]{method});
List<Girl> result = null;
try {
result = girlRepository.findAll(new Sort(Sort.Direction.ASC, "age"));
} catch (Exception e) {
LOGGER.error(e.getMessage());
}
LOGGER.info("method : {} , result : {} - end", new Object[]{method, result});
return result;
}
/**
* 查询女生信息-分页
*
* @param page
* @return
*/
@GetMapping(value = "/girls/byPage")
public List<Girl> findGirlsByPage(@RequestParam("page") int page) {
final String method = "findGirlsByPage";
LOGGER.info("method : {} , page : {} - start", new Object[]{method, page});
Girl girl = new Girl();
girl.setPageSize(5);
girl.setPage(page);
PageRequest pageRequest = PageRequest.of(girl.getPage() - 1, girl.getPageSize());
Page<Girl> girlPages = girlRepository.findAll(pageRequest);
List<Girl> result = girlPages.getContent();
LOGGER.info("method : {} , result : {} - end", new Object[]{method, result});
return result;
}
/**
* 通过cupSize查询女生信息-使用自定义SQL
* <p>
* '@Modifying':该注解配合'@Query'注解使用<br>
*
* @param cupSize
* @return
*/
@GetMapping(value = "/girls/byCupSize")
@Modifying
public List<Girl> findGirlsByCupSize(@RequestParam("cupSize") String cupSize) {
final String method = "findGirlsByCupSize";
LOGGER.info("method : {} , cupSize : {} - start", new Object[]{method, cupSize});
List<Girl> result = null;
try {
result = girlRepository.findGirlsByCupSize(cupSize);
} catch (Exception e) {
LOGGER.error(e.getMessage());
}
LOGGER.info("method : {} , result : {} - end", new Object[]{method, result});
return result;
}
}本来是想把一步步怎么测试也贴上来的，想想太麻烦了...
贴个总的吧...测试使用Postman...
SpringBoot 使用JPA 操作 MySQL 增删查改 基本操作就到这里。
后面会把事务补上来，完整的项目代码 上传到了GitHub上，自取：SpringBoot-JPA-MySQL Demo样例
1629
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
