使用Spring Data JPA操作Mysql

小鱼儿 2023-10-17 18:20 75阅读 0赞

参考：[Open-source development – IBM Developer – IBM Developer][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动态构建多表查询、复杂查询及排序示例 - 简书][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();
    	}
    }

[Open-source development _ IBM Developer _ IBM Developer]: https://www.ibm.com/developerworks/cn/opensource/os-cn-spring-jpa/
[Spring Data JPA_Specification_ -]: https://www.jianshu.com/p/659e9715d01d