JPA日常问题整理

直接执行pgsql函数

String obtainSelectSQL = reportConfigurationRepository.getObtainSelectSQL(schema, configuration.getTableName(), info.getReportDate());

public interface ReportConfigurationRepository extends JpaRepository<ReportConfiguration, Long> {
    @Query(value = "SELECT fn_get_table_sql_use_comment(:schema,:tableName,:reportDate) as text",nativeQuery = true)
    String getObtainSelectSQL(String schema, String tableName, String reportDate);
}

直接执行sql语句

	//	obtainSelectSQL = select id as "INTERNAL_X_ID",the_date_insert as "THE_DATE_INSERT",campaign_code as "Campaign Code",date_str as "Date",visit_counts as "No. of visitor",sales_counts as "No. of Click ( Submit Button)",to_char(the_date_update,'yyyy/mm/dd hh24:mi:ss') as "THE_DATE_UPDATE" from report.t_dgp_traffic_kfc where the_date_insert='20201209'
	Query q = entityManager.createNativeQuery(obtainSelectSQL);
	List<Object[]> list = q.getResultList();
	//	从数据库查询的数据用List存一行一行
	//	Object[]存每一行的数据，遍历数组取其中每一列数据
    list.forEach( objects -> {
        for(int i = 0; i < objects.length; i ++){
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(objects[i].toString());
            cell.setCellValue(text);
        }
    });