spring mysql jpa_使用Spring Data JPA操作Mysql

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 {

List 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 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 中的 "

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 userList) ，方法的参数可以是 Collection 类型，也可以是数组或者不定长参数；

NotIn --- 等价于 SQL 中的 "not in"，比如 findByUsernameNotIn(Collection userList) ，方法的参数可以是 Collection 类型，也可以是数组或者不定长参数；

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 TestMysql2() {

List list = context.findAll();

System.out.println(list);

return list;

}

@ApiOperation(value="测试mysql3_根据自定义方法名查询",httpMethod="POST")

@RequestMapping("/api/testmysql3")

public List TestMysql3() {

List 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 TestMysql5() {

Pageable pageable = new PageRequest(0,3, Sort.Direction.DESC,"id");

Page list = context.findByUserIdGreaterThan(1,pageable);

System.out.println(list);

return list.getContent();

}

}