使用Spring Data JPA操作Mysql
【摘要】
参考：Open-source development – IBM Developer – IBM Developer
1.添加依赖：
dependencies { compile('io.springfox:springfox-swagger2:2.2.2') compile('io.springfox:springfo...
参考：Open-source development – IBM Developer – IBM Developer
1.添加依赖：
-
dependencies {
-
compile('io.springfox:springfox-swagger2:2.2.2')
-
compile('io.springfox:springfox-swagger-ui:2.2.2')
-
-
compile('org.springframework.boot:spring-boot-starter-data-jpa')
-
compile('org.springframework.boot:spring-boot-starter-jdbc')
-
compile('org.springframework.boot:spring-boot-starter-web')
-
runtime('mysql:mysql-connector-java')
-
providedRuntime('org.springframework.boot:spring-boot-starter-tomcat')
-
testCompile('org.springframework.boot:spring-boot-starter-test')
-
}
2. application.properties文件添加链接串
-
spring.datasource.url=jdbc:mysql://localhost/yfeid
-
spring.datasource.username=root
-
spring.datasource.password=root
-
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
-
-
#update database by model
-
spring.jpa.show-sql= true
-
spring.jpa.properties.hibernate.hbm2ddl.auto=update
-
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
-
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
3.添加实体类：User
-
package com.yunfeng.TestMySQL.Model;
-
-
import java.io.Serializable;
-
import java.util.Date;
-
import javax.persistence.Entity;
-
import javax.persistence.GeneratedValue;
-
import javax.persistence.GenerationType;
-
import javax.persistence.Id;
-
import javax.persistence.Table;
-
import com.fasterxml.jackson.annotation.JsonFormat;
-
import javax.persistence.*;
-
-
@Entity
-
@Table(name = "user")
-
public class User implements Serializable {
-
/**
-
* @Fields serialVersionUID : TODO
-
*/
-
private static final long serialVersionUID = -6550777752269466791L;
-
-
@Id
-
@GeneratedValue(strategy = GenerationType.IDENTITY)
-
private int id;
-
-
private String name;
-
-
private String password;
-
-
private String address;
-
-
@Column(name="loginName")
-
private String loginName;
-
-
@Temporal(TemporalType.TIMESTAMP)
-
private Date createTime;
-
-
public String getName() {
-
return name;
-
}
-
-
public void setName(String name) {
-
this.name = name;
-
}
-
-
-
public String getPassword() {
-
return password;
-
}
-
-
public void setPassword(String password) {
-
this.password = password;
-
}
-
-
public String getAddress() {
-
return address;
-
}
-
-
public void setAddress(String address) {
-
this.address = address;
-
}
-
-
-
-
public String getLoginName() {
-
return loginName;
-
}
-
-
public void setLoginName(String loginName) {
-
this.loginName = loginName;
-
}
-
-
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
-
public Date getCreateTime() {
-
return createTime;
-
}
-
-
public void setCreateTime(Date createTime) {
-
this.createTime = createTime;
-
}
-
-
-
-
-
-
}
4.建立仓库类：
-
package com.yunfeng.TestMySQL.Repository;
-
-
import java.util.List;
-
import org.springframework.data.domain.Page;
-
import org.springframework.data.domain.Pageable;
-
import org.springframework.data.jpa.repository.JpaRepository;
-
import org.springframework.data.jpa.repository.Query;
-
import org.springframework.data.repository.query.Param;
-
import com.yunfeng.TestMySQL.Model.User;
-
-
public interface UserRepository extends JpaRepository<User, Integer> {
-
-
List<User> findByNameOrAddress(String name, String address);
-
-
@Query("select a from User a where a.address = ?1")
-
public User findAddress(String address);
-
-
@Query("from User a where a.id = :id")
-
public User findByUserId(@Param("id")int userId);
-
-
@Query("from User a where a.id > :userId")
-
public Page<User> findByUserIdGreaterThan(@Param("userId")int userId,Pageable pageable);
-
}
在查询时，通常需要同时根据多个属性进行查询，且查询的条件也格式各样（大于某个值、在某个范围等等），Spring Data JPA 为此提供了一些表达条件查询的关键字，大致如下：
- And --- 等价于 SQL 中的 and 关键字，比如 findByUsernameAndPassword(String user, Striang pwd)；
- Or --- 等价于 SQL 中的 or 关键字，比如 findByUsernameOrAddress(String user, String addr)；
- Between --- 等价于 SQL 中的 between 关键字，比如 findBySalaryBetween(int max, int min)；
- LessThan --- 等价于 SQL 中的 "<"，比如 findBySalaryLessThan(int max)；
- GreaterThan --- 等价于 SQL 中的">"，比如 findBySalaryGreaterThan(int min)；
- IsNull --- 等价于 SQL 中的 "is null"，比如 findByUsernameIsNull()；
- IsNotNull --- 等价于 SQL 中的 "is not null"，比如 findByUsernameIsNotNull()；
- NotNull --- 与 IsNotNull 等价；
- Like --- 等价于 SQL 中的 "like"，比如 findByUsernameLike(String user)；
- NotLike --- 等价于 SQL 中的 "not like"，比如 findByUsernameNotLike(String user)；
- OrderBy --- 等价于 SQL 中的 "order by"，比如 findByUsernameOrderBySalaryAsc(String user)；
- Not --- 等价于 SQL 中的 "！ ="，比如 findByUsernameNot(String user)；
- In --- 等价于 SQL 中的 "in"，比如 findByUsernameIn(Collection<String> userList) ，方法的参数可以是 Collection 类型，也可以是数组或者不定长参数；
- NotIn --- 等价于 SQL 中的 "not in"，比如 findByUsernameNotIn(Collection<String> userList) ，方法的参数可以是 Collection 类型，也可以是数组或者不定长参数；
关于Spring Data JPA的动态关联查询，参看：【一目了然】Spring Data JPA使用Specification动态构建多表查询、复杂查询及排序示例 - 简书
5.使用：
-
package com.yunfeng.TestMySQL;
-
-
import Request.*;
-
import Response.*;
-
-
import java.util.List;
-
-
import org.springframework.beans.factory.annotation.Autowired;
-
import org.springframework.data.domain.Page;
-
import org.springframework.data.domain.PageRequest;
-
import org.springframework.data.domain.Pageable;
-
import org.springframework.data.domain.Sort;
-
import org.springframework.web.bind.annotation.RequestBody;
-
import org.springframework.web.bind.annotation.RequestMapping;
-
import org.springframework.web.bind.annotation.RestController;
-
-
import io.swagger.annotations.ApiOperation;
-
-
@RestController
-
public class TestController {
-
-
@ApiOperation(value = "查找用户", httpMethod = "POST")
-
@RequestMapping("/api/Test1")
-
public TestResponse showPerson(@RequestBody TestRequest input) {
-
TestResponse res = new TestResponse();
-
res.setUserName("zzzili");
-
res.setUserPass("pass");
-
return res;
-
}
-
-
@Autowired//(required=false)
-
private UserRepository context;
-
-
@ApiOperation(value="测试mysql2_通过JPA自带方法查询",httpMethod="POST")
-
@RequestMapping("/api/testmysql2")
-
public List<User> TestMysql2() {
-
List<User> list = context.findAll();
-
System.out.println(list);
-
return list;
-
}
-
-
@ApiOperation(value="测试mysql3_根据自定义方法名查询",httpMethod="POST")
-
@RequestMapping("/api/testmysql3")
-
public List<User> TestMysql3() {
-
List<User> list = context.findByNameOrAddress("zz","ss");
-
System.out.println(list);
-
return list;
-
}
-
-
@ApiOperation(value="测试mysql4_自定义query查询",httpMethod="POST")
-
@RequestMapping("/api/testmysql4")
-
public User TestMysql4() {
-
//User user = context.findEmail("zz");
-
User user2 = context.findByUserId(1);
-
System.out.println(user2);
-
return user2;
-
}
-
-
@ApiOperation(value="测试mysql5_分页查询",httpMethod="POST")
-
@RequestMapping("/api/testmysql5")
-
public List<User> TestMysql5() {
-
Pageable pageable = PageRequest.of(0,3, Sort.Direction.DESC,"id");
-
Page<User> list = context.findByUserIdGreaterThan(1,pageable);
-
System.out.println(list);
-
return list.getContent();
-
}
-
}
文章来源: zzzili.blog.csdn.net，作者：清雨小竹，版权归原作者所有，如需转载，请联系作者。
原文链接：zzzili.blog.csdn.net/article/details/79347753
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）