SpringBoot--JPA以及@Query注解使用

使用SpringBoot JPA来访问mysql数据库，实现基本的增删改查操作。

使用maven来管理项目，导入具体的依赖

<dependencies>	
		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
        </dependency> 
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<!--定时任务和@Slf4j注解日志的依赖-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- JPA Data (We are going to use Repositories, Entities, Hibernate, etc...) -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <!-- Use MySQL Connector-J -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>    
	</dependencies>

数据库表准备：

CREATE TABLE `1_device_info` (
  `physical_device_id` varchar(64) NOT NULL DEFAULT '' COMMENT '设备物理ID',
  `sub_domain_id` varchar(32) NOT NULL DEFAULT '' COMMENT '子域ID',
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '设备名称',
  `aes_key` varchar(255) NOT NULL DEFAULT '' COMMENT '密钥',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT ' 0(INVALID), 1(ACTIVE), 2(FROZEN), 3(DELETED), 4(PRE_BIND)',
  `reserve` varchar(64) NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`physical_device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

代码应该分层Model,Repository,Service以及Controller

Model层定义实体类与数据库表进行对应映射：

使用@Entity以及@Id注解来标识model类以及主键字段

@Entity(name = "1_device_info") 
public class DeviceInfo {
	
	//设备物理ID
	@Id
	private String physicalDeviceId;
	//子域ID
	private String subDomainId;
	//设备名称
	private String name;
	//密钥
	private String aesKey;
	// 0(INVALID), 1(ACTIVE), 2(FROZEN), 3(DELETED), 4(PRE_BIND)
	private Integer status;
	
	private String reserve;
	//创建时间
	private Date createTime;
	//最后修改时间
	private Date modifyTime;
	
	public String getPhysicalDeviceId() {
		return physicalDeviceId;
	}
	public void setPhysicalDeviceId(String physicalDeviceId) {
		this.physicalDeviceId = physicalDeviceId;
	}
	public String getSubDomainId() {
		return subDomainId;
	}
	public void setSubDomainId(String subDomainId) {
		this.subDomainId = subDomainId;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAesKey() {
		return aesKey;
	}
	public void setAesKey(String aesKey) {
		this.aesKey = aesKey;
	}
	public Integer getStatus() {
		return status;
	}
	public void setStatus(Integer status) {
		this.status = status;
	}
	public String getReserve() {
		return reserve;
	}
	public void setReserve(String reserve) {
		this.reserve = reserve;
	}
	public Date getCreateTime() {
		return createTime;
	}
	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}
	public Date getModifyTime() {
		return modifyTime;
	}
	public void setModifyTime(Date modifyTime) {
		this.modifyTime = modifyTime;
	}
	
	@Override
	public String toString() {
		return "DeviceInfo [physicalDeviceId=" + physicalDeviceId + ", subDomainId=" + subDomainId + ", name=" + name
				+ ", aesKey=" + aesKey + ", status=" + status + ", reserve=" + reserve + ", createTime=" + createTime
				+ ", modifyTime=" + modifyTime + "]";
	}
}

Repository层代码如下：

注解@Repository，注解@Transactional应该加在Service层，此处为了简单便于演示，加在repository层，声明事务处理。

@Repository
//此处事务注解应该加在Service层
@Transactional
public interface DeviceInfoRepository extends CrudRepository<DeviceInfo, String>{
	
	@Query(value = "select count(*) from 1_device_info di where di.status = ?1", nativeQuery = true)
	public long countDeviceInfoByCondition(String status);
	
	@Query(value = "select * from 1_device_info", nativeQuery = true)
	public List<DeviceInfo> getAllDeviceInfoList();
	
	@Query(value = "select * from 1_device_info", nativeQuery = true)
	public Page<DeviceInfo> getDeviceInfoListPager(Pageable pageable); 
	
	//用来表示修改操作
	@Modifying
	@Query(value = "insert into 1_device_info(physical_device_id) values(?1)", nativeQuery = true)
	public Integer addDeviceInfo(String physicalDeviceId);
	
	@Modifying
	@Query(value = "update 1_device_info set aes_key = ?1 where physical_device_id = ?2", nativeQuery = true)
    public void updateDeviceInfo(String aesKey, String physicalDeviceId);
	
	@Modifying
	@Query(value = "delete from 1_device_info where physical_device_id = ?1", nativeQuery = true)
    public void deleteDeviceInfoById(String physicalDeviceId);
}

Controller层来进行路由寻址以及跳转：

@Controller    
@RequestMapping(path="/deviceInfoController") 
@Slf4j
public class DeviceInfoController {
	
	@Autowired 
	private DeviceInfoRepository deviceInfoRepository;
	
	// /deviceInfoController/getAllDeviceInfo
	@GetMapping(path="/getAllDeviceInfo")
	@ResponseBody
	public List<DeviceInfo> getAllDeviceInfo() {
		// This returns a JSON or XML with the users
		System.out.println(deviceInfoRepository.countDeviceInfoByCondition(DeviceInfoStatus.INVALID));
		
		//deviceInfoRepository.addDeviceInfo("123456");
		
		deviceInfoRepository.updateDeviceInfo("11111", "123456");
		
		deviceInfoRepository.deleteDeviceInfoById("123456");
		
		Pageable pager = new PageRequest(1, 10);
		return deviceInfoRepository.getDeviceInfoListPager(pager).getContent();	
		//return deviceInfoRepository.getAllDeviceInfoList();
	}
}

main方法

@SpringBootApplication
public class DemoApplication {
	
	public static void main(String[] args) {
		SpringApplication.run(DemoApplication.class, args);
	}

}

@Query注解使用

CrudRepository接口已经实现部分常见的操作，对于一些需要特殊定制化的操作，需要使用@Query注解来实现sql语句与代码的对应关系

基本查询操作

public interface UserRepository extends JpaRepository<User, Long> {
  @Query("select u from User u where u.emailAddress = ?1")
  User findByEmailAddress(String emailAddress);
}

Like模糊查询

public interface UserRepository extends JpaRepository<User, Long> {

  @Query("select u from User u where u.firstname like %?1")
  List<User> findByFirstnameEndsWith(String firstname);
}

原生sql

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)
  User findByEmailAddress(String emailAddress);
}

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

排序操作sort

public interface UserRepository extends JpaRepository<User, Long> {

  @Query("select u from User u where u.lastname like ?1%")
  List<User> findByAndSort(String lastname, Sort sort);

  @Query("select u.id, LENGTH(u.firstname) as fn_len from User u where u.lastname like ?1%")
  List<Object[]> findByAsArrayAndSort(String lastname, Sort sort);
}

repo.findByAndSort("lannister", new Sort("firstname"));               
repo.findByAndSort("stark", new Sort("LENGTH(firstname)"));           
repo.findByAndSort("targaryen", JpaSort.unsafe("LENGTH(firstname)")); 
repo.findByAsArrayAndSort("bolton", new Sort("fn_len"));   

@Param注解参数映射

public interface UserRepository extends JpaRepository<User, Long> {

  @Query("select u from User u where u.firstname = :firstname or u.lastname = :lastname")
  User findByLastnameOrFirstname(@Param("lastname") String lastname,
                                 @Param("firstname") String firstname);
}

修改操作@Modifying

@Modifying
@Query("update User u set u.firstname = ?1 where u.lastname = ?2")
int setFixedFirstnameFor(String firstname, String lastname);

interface UserRepository extends Repository<User, Long> {

  void deleteByRoleId(long roleId);

  @Modifying
  @Query("delete from User u where user.role.id = ?1")
  void deleteInBulkByRoleId(long roleId);
}

 

参考链接：

https://spring.io/guides/gs/accessing-data-mysql/

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/