JPA的使用记录

集成JPA

由于用的是fegin，工具用的是idea，所以创建项目的时候勾选了JPA。

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

打印sql，在application.yml中配置

spring:
    jpa:
    properties:
      hibernate:
        format_sql: true
        show_sql: true

开始使用

 

创建实体类

实体类我使用的

这个自动生成的。自定义的.groovy,可以生成注释和注解

但是不能生成

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)

需要自己加上去。

package com.epyc.ycdbbase.entity;

import javax.persistence.*;
import java.util.Date;

/**
 * @Description  
 * @Author  wqy
 * @Date 2019-09-17 
 */

@Entity
@Table ( name ="yc_device_info" )
public class YcDeviceInfoEntity  {


	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "id" )
	private Long id;

	/**
	 * 设备号
	 */
   	@Column(name = "device_num" )
	private String deviceNum;

	/**
	 * 设备名称
	 */
   	@Column(name = "device_name" )
	private String deviceName;

	/**
	 * 录入时间
	 */
   	@Column(name = "input_time" )
	private Date inputTime;

	/**
	 * 地址
	 */
   	@Column(name = "address" )
	private String address;

	/**
	 * 经度
	 */
   	@Column(name = "longitude" )
	private String longitude;

	/**
	 * 纬度
	 */
   	@Column(name = "latitude" )
	private String latitude;

	/**
	 * 备注
	 */
   	@Column(name = "remarks" )
	private String remarks;

	/**
	 * 组织机构编号
	 */
   	@Column(name = "organization_num" )
	private String organizationNum;

	/**
	 * 组织机构名称
	 */
   	@Column(name = "organization_name" )
	private String organizationName;

	/**
	 * 视频编码
	 */
   	@Column(name = "video_code" )
	private String videoCode;

	/**
	 * 对接平台
	 */
   	@Column(name = "to_platform" )
	private String toPlatform;

	/*省略get，set*/

}

创建完实体类就该创建这个实体类的仓库了。

第一种：普通查询

package com.epyc.ycdbbase.db_w.dao;

import com.epyc.ycdbbase.entity.YcDeviceInfoEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author WQY
 * @date 2019/9/17 10:10
 */
public interface Yc_Device_Jpa extends JpaRepository<YcDeviceInfoEntity,Integer> {

    /**
     * 根据组织机构删除设备
     * @param organization_num
     * @return
     */
    @Transactional
    @Modifying(clearAutomatically = true)
    @Query(nativeQuery = true,value = "DELETE FROM yc_device_info WHERE organization_num LIKE ?1")
    int deleteLikeOrganizationName(String organization_num);

    /**
     * 根据设备编号查询
     * @param deviceNum
     * @return
     */
    YcDeviceInfoEntity findByDeviceNum(String deviceNum);

    /**
     * 根据设备号删除设备
     * @param deviceNum
     * @return
     */
    @Transactional
    @Modifying(clearAutomatically = true)
    int deleteByDeviceNum(String deviceNum);

    /**
     * 根据组织机构模糊查询
     * @param orgNum
     * @return
     */
    @Query(nativeQuery = true,value = "SELECT * FROM `yc_device_info` WHERE organization_num LIKE ?1")
    List<YcDeviceInfoEntity> findByLikeOrOrganizationNum(String orgNum);



}

删除和修改的时候一定要加上事务，不然会出现异常。

deleteByDeviceNum（String DeviceNum）；这个方法是JPA的关键字查询

JPA也自带了一些简单操作，类似save，findAll都直接可以在service中调用。

只要返回值字段

JpaRepository<YcDeviceInfoEntity,Integer>

是YcDeviceInfoEntity这个实体类中的就可以写复杂的sql。

第二种：接受复杂查询返回值

package com.epyc.ycdbbase.db_w.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * @author WQY
 * @date 2019/9/19 10:31
 */
@Entity
public class IconEntity {

    @Id
    @Column(name = "hours" )
    private String hours;
    @Column(name = "count" )
    private String count;
    @Column(name = "pm25" )
    private String pm25;
    @Column(name = "pm10" )
    private String pm10;

    /*省略get，set*/
}

类似这个实体类。

因为他是为了接受一些统计值，素以并没有对应数据库表，直接把返回字段对应上即可

这个是他的仓库

package com.epyc.ycdbbase.db_w.dao;

import com.epyc.ycdbbase.db_w.entity.IconEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author WQY
 * @date 2019/9/19 10:34
 */
public interface Icon_Jpa extends JpaRepository<IconEntity,Integer> {

    @Query(nativeQuery = true,value = "select DATE_FORMAT(updatetime,'%Y%m%d%H') hours,count(id) count,AVG(pm25) pm25,AVG(pm10) pm10 from yc_his20190919 group by hours;")
    List<IconEntity> findByDayMsg();

}

视图也是常用的查询方法

创建视图实体类

因为视图中没有主键

所以在创建视图的时候最好选择一个ID或者主键列，不选也没事。

但是一定要在实体类中加上一个@ID

第三种：视图查询

package com.epyc.ycdbbase.entity;

import javax.persistence.*;
import java.util.Date;

/**
 * @Description  
 * @Author  wqy
 * @Date 2019-09-17 
 */

@Entity
@Table ( name ="yc_device_realtime" )
public class YcDeviceInfoRealtimeEntity {


	@Id
	@Column(name = "id" )
	private Long id;

	/**
	 * 设备号
	 */
   	@Column(name = "device_num" )
	private String deviceNum;

	/**
	 * 设备名称
	 */
   	@Column(name = "device_name" )
	private String deviceName;


	/**
	 * 地址
	 */
   	@Column(name = "address" )
	private String address;

	/**
	 * 经度
	 */
   	@Column(name = "longitude" )
	private String longitude;

	/**
	 * 纬度
	 */
   	@Column(name = "latitude" )
	private String latitude;


	/**
	 * 组织机构编号
	 */
   	@Column(name = "organization_num" )
	private String organizationNum;

	/**
	 * 组织机构名称
	 */
   	@Column(name = "organization_name" )
	private String organizationName;


	/**
	 * pm2.5
	 */
	@Column(name = "pm25" )
	private String pm25;

	/**
	 * pm10
	 */
	@Column(name = "pm10" )
	private String pm10;

	/**
	 * 噪音
	 */
	@Column(name = "noise" )
	private String noise;

	/**
	 * 空气温度
	 */
	@Column(name = "air_temperature" )
	private String airTemperature;

	/**
	 * 空气湿度
	 */
	@Column(name = "air_humidity" )
	private String airHumidity;

	/**
	 * 风速
	 */
	@Column(name = "wind_speed" )
	private Double windSpeed;

	/**
	 * 风向
	 */
	@Column(name = "wind_direction" )
	private String windDirection;

	/**
	 * tsp
	 */
	@Column(name = "tsp" )
	private String tsp;

	/**
	 * 富氧因子
	 */
	@Column(name = "oxygen_factor" )
	private String oxygenFactor;

	/**
	 * 大气压力
	 */
	@Column(name = "atmospheric_pressure" )
	private String atmosphericPressure;

	/**
	 * 更新时间
	 */
	@Column(name = "updatetime" )
	private Date updatetime;

    /*省略get，set*/
}

视图仓库

package com.epyc.ycdbbase.db_w.dao;

import com.epyc.ycdbbase.entity.YcDeviceInfoEntity;
import com.epyc.ycdbbase.entity.YcDeviceInfoRealtimeEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author WQY
 * @date 2019/9/18 19:04
 */
public interface Yc_Device_Realtime_Jpa extends JpaRepository<YcDeviceInfoRealtimeEntity,Integer> {

    /**
     * 根据传入组织机构id查该组织机构id下的所有设备
     * @param orgNum
     * @return
     */
    @Query(nativeQuery = true,value = "SELECT * FROM `yc_devcie_realtime` where organization_num IN(SELECT organization_serial_num FROM `yc_organization` WHERE organization_serial_num LIKE ?1)")
    List<YcDeviceInfoRealtimeEntity> findByOrganizationNumList(String orgNum);

}

第四种：使用Query查询（非注解）

因为JPA注解并不能动态传入表明，这时候就要用EntityManager 来了

拼接sql。

比如历史表，数据多的话可能一天一个表或者一周一个月，就需要用到动态拼接了。

package com.epyc.ycdbbase.db_w.dao;

import com.epyc.ycdbbase.db_w.entity.IconEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

/**
 * @author WQY
 * @date 2019/9/19 10:34
 */
@Component
public class Icon_Jpa{

    //    @Query(nativeQuery = true,value = "select DATE_FORMAT(updatetime,'%Y%m%d%H') hours,count(id) count,AVG(pm25) pm25,AVG(pm10) pm10 from ?1 group by hours;")
//    List<IconEntity> findByDayMsg(String tableName);
    @PersistenceContext
    EntityManager entityManager;

    public List<IconEntity> findByDayMsg(String tableName){

        String sql = "select DATE_FORMAT(updatetime,'%Y%m%d%H') hours,count(id) count,AVG(pm25) pm25,AVG(pm10) pm10 from "+tableName+" group by hours";

        List<Object[]> rs = entityManager.createNativeQuery(sql).getResultList();

        List<IconEntity> iconEntities = new ArrayList<IconEntity>();
        for (int i = 0,num = rs.size();i<num;i++){
            IconEntity iconEntity = new IconEntity();
            iconEntity.setHours((String) rs.get(i)[0]);
            iconEntity.setCount(((BigInteger) rs.get(i)[1]));
            iconEntity.setPm25((double) rs.get(i)[2]);
            iconEntity.setPm10((double) rs.get(i)[3]);
            iconEntities.add(iconEntity);
        }
        return iconEntities;
    }

}