hibernate 自定义表名与列名 - 增删改查分页 - 兼容Mysql和Oracle
1. 新增service，先组装SQL
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
@Slf4j
public class DataService {
@Autowired
private CustomRepository customRepository;
@Autowired
private EventService EventService;
@Value("${spring.jpa.database}")
private String database;
public ApiResult addCustomData(CustomDataDTO customDataDTO) {
String cusTableName = customDataDTO.getTargetTable();
List<CustomColumnData> dataList = customDataDTO.getDatas();
StringBuilder sql = new StringBuilder();
StringBuilder strColumns = new StringBuilder();
StringBuilder strValues = new StringBuilder();
for (int i =0; i < dataList.size(); i++) {
CustomColumnData data = dataList.get(i);
strColumns.append("`").append(data.getColumnName()).append("`")
.append((i<dataList.size() - 1) ? "," : "");
strValues.append("'").append(data.getColumnValue()).append("'")
.append((i<dataList.size() - 1) ? "," : "");
}
sql.append(" insert into ").append(cusTableName).append(" (").append(strColumns).append(") values ").append("(").append(strValues).append(")");
int res = customRepository.insertData(sql.toString());
return ApiResult.ok(res);
}
public ApiResult addCustomDataAutoID(CustomDataDTO customDataDTO) {
String cusTableName = customDataDTO.getTargetTable();
List<CustomColumnData> dataList = customDataDTO.getDatas();
StringBuilder strColumns = new StringBuilder();
StringBuilder strValues = new StringBuilder();
for (int i =0; i < dataList.size(); i++) {
CustomColumnData data = dataList.get(i);
strColumns.append(data.getColumnName())
.append((i<dataList.size() - 1) ? "," : "");
strValues.append("'").append(data.getColumnValue()).append("'")
.append((i<dataList.size() - 1) ? "," : "");
}
int res = customRepository.insertDataAutoID(cusTableName, strColumns.toString(), strValues.toString());
return ApiResult.ok(res);
}
public ApiResult updateCustomData(CustomDataDTO customDataDTO) {
String cusTableName = customDataDTO.getTargetTable();
List<CustomColumnData> dataList = customDataDTO.getDatas();
StringBuilder setValues = new StringBuilder();
for (int i =0; i < dataList.size(); i++) {
CustomColumnData data = dataList.get(i);
if (data.getColumnName() != null && !data.getColumnName().isEmpty()) {
setValues.append(data.getColumnName())
.append("=").append("'").append(data.getColumnValue()).append("'")
.append((i<dataList.size() - 1) ? "," : "");
}
}
int res = customRepository.updateDataSetByID(cusTableName , String.valueOf(setValues), customDataDTO.getDataId());
return ApiResult.ok(res);
}
public ApiResult deleteCustomDataByID(CustomDataCondition customDataCondition) {
String cusTableName = customDataDTO.getTargetTable();
StringBuilder setValues = new StringBuilder();
List<Long> longList = customDataCondition.getIdList();
for (int i =0; i < longList.size(); i++) {
long id = longList.get(i);
if (id > 0) {
setValues.append(id)
.append((i<longList.size() - 1) ? "," : "");
}
}
int res = customRepository.deleteDataSetByID(cusTableName, setValues.toString());
return ApiResult.ok(res);
}
public ApiResult getAll(CustomDataQueryDTO customDataDTO) {
String cusTableName = customDataDTO.getTargetTable();
List<CustomColumnQueryData> dataList = customDataDTO.getDatas();
StringBuilder setValues = new StringBuilder();
for (int i =0; i < dataList.size(); i++) {
CustomColumnQueryData data = dataList.get(i);
if (data.getColumnName() != null && !data.getColumnName().isEmpty()) {
setValues.append(data.getColumnName());
if (data.isLike()) {
setValues.append(" like '%").append(data.getColumnValue()).append("%'");
} else {
setValues.append("=").append("'").append(data.getColumnValue()).append("'");
}
setValues.append((i<dataList.size() - 1) ? "," : "");
}
}
List res = customRepository.queryAllDataByTableName(cusTableName , String.valueOf(setValues));
return ApiResult.ok(res);
}
public ApiResult getPage(CustomDataQueryDTO customDataQueryDTO) {
String cusTableName = customDataDTO.getTargetTable();
List<CustomColumnQueryData> dataList = customDataQueryDTO.getDatas();
StringBuilder setValues = new StringBuilder();
for (int i =0; i < dataList.size(); i++) {
CustomColumnQueryData data = dataList.get(i);
if (data.getColumnName() != null && !data.getColumnName().isEmpty()) {
setValues.append(data.getColumnName());
if (data.isLike()) {
setValues.append(" like '%").append(data.getColumnValue()).append("%'");
} else {
setValues.append("=").append("'").append(data.getColumnValue()).append("'");
}
setValues.append((i < dataList.size() - 1) ? "," : "");
}
}
Page res = customRepository.queryPageDataByTableName(cusTableName , String.valueOf(setValues), customDataQueryDTO.getSize(), customDataQueryDTO.getPage());
return ApiResult.ok(res);
}
public ApiResult queryColumnNamesByTableName(CustomDataQueryDTO customDataQueryDTO) {
String cusTableName = customDataDTO.getTargetTable();
List<String> names;
if (database.equals("MYSQL")) {
names = customRepository.queryColumnNamesByTableNameMySQL(cusTableName);
} else {
names = customRepository.queryColumnNamesByTableNameOracle(cusTableName);
}
return ApiResult.ok(names);
}
}
2. 新增Repository
import lombok.extern.slf4j.Slf4j;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.stereotype.Repository;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
@Slf4j
@Repository
public class CustomRepository {
@PersistenceContext
private EntityManager entityManager;
@Transactional
@Modifying
@Query
public int insertData(String sql) {
log.info(" 请求的SQL: \n" + sql);
return entityManager.createNativeQuery(sql).executeUpdate();
}
@Transactional
@Modifying
@Query
public int insertDataAutoID(String cusTableName, String strColumns, String strValues) {
StringBuilder sql = new StringBuilder();
sql.append(" insert into ").append(cusTableName).append(" ( ").append(strColumns).append(") values ").append("( ").append(strValues).append(")");
log.info(" 请求的SQL: " + sql);
return entityManager.createNativeQuery(sql.toString()).executeUpdate();
}
@Transactional
@Modifying
@Query
public int updateDataSetByID(String cusTableName, String setValues, long id) {
StringBuilder sql = new StringBuilder();
sql.append(" update ").append(cusTableName).append(" set ").append(setValues).append(" where ").append("( id = ").append(id).append(")");
log.info(" 执行的SQL: " + sql);
return entityManager.createNativeQuery(sql.toString()).executeUpdate();
}
@Transactional
@Modifying
@Query
public int deleteDataSetByID(String cusTableName, String idSqlStr) {
StringBuilder sql = new StringBuilder();
sql.append(" delete from ").append(cusTableName).append(" where id in ( ").append(idSqlStr).append(")");
log.info(" 执行的SQL: " + sql);
return entityManager.createNativeQuery(sql.toString()).executeUpdate();
}
@Modifying
@Query
public List queryAllDataByTableName(String cusTableName, String whereCondition) {
StringBuilder sql = new StringBuilder();
sql.append(" select * from ").append(cusTableName);
if (!whereCondition.isEmpty()) {
sql.append(" where ").append(whereCondition);
}
javax.persistence.Query query = entityManager.createNativeQuery(sql.toString());
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
log.info(" 执行的SQL: " + sql);
return query.getResultList();
}
@Modifying
@Query
public Page queryPageDataByTableName(String cusTableName, String whereCondition, int size, int page) {
StringBuilder countSql = new StringBuilder();
countSql.append("select count(*) from ").append(cusTableName).append(" e ");
if (!whereCondition.isEmpty()) {
countSql.append(" where ").append(whereCondition);
}
StringBuilder sql = new StringBuilder();
sql.append("select * from ").append(cusTableName);
if (!whereCondition.isEmpty()) {
sql.append(" where ").append(whereCondition);
}
log.info(" 执行的SQL: " + sql);
Pageable pageable = PageRequest.of(page, size);
return findWithPagination(pageable, sql.toString(), countSql.toString());
}
public Page findWithPagination(Pageable pageable, String sql, String countSql) {
String countQuery = countSql;
javax.persistence.Query cntQuery = entityManager.createNativeQuery(countQuery);
BigInteger cnt = BigInteger.valueOf(0);
Object countObj = cntQuery.getSingleResult();
if (countObj instanceof BigDecimal) {
cnt = BigInteger.valueOf(Integer.parseInt(String.valueOf(countObj)));
} else {
cnt = (BigInteger) cntQuery.getSingleResult();
}
javax.persistence.Query query = entityManager.createNativeQuery(sql);
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
query.setFirstResult((int) pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
List list = query.getResultList();
Page pageResult = new PageImpl<>(list, pageable, cnt.longValue());
// 创建并返回一个Page对象
return pageResult;
}
/**
* 获取指定表的所有字段名称 MysqlDB
* @param cusTableName 表名
* @return 字段列名称
*/
@Modifying
@Query
public List<String> queryColumnNamesByTableNameMySQL(String cusTableName) {
StringBuilder sql = new StringBuilder();
// oracle写法
sql.append(" SHOW COLUMNS FROM ").append(cusTableName).append("");
javax.persistence.Query query = entityManager.createNativeQuery(sql.toString());
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
log.info(" 执行的SQL: " + sql);
List<String> columns = new ArrayList<>();
List<HashMap> resultList = query.getResultList();
for (HashMap map : resultList) {
String column = map.get("Field").toString();
columns.add(column);
}
log.info("所有的列名: " + columns);
return columns;
}
/**
* 获取指定表的所有字段名称 OracleDB
* @param cusTableName 表名
* @return 字段列名称
*/
@Modifying
@Query
public List<String> queryColumnNamesByTableNameOracle(String cusTableName) {
StringBuilder sql = new StringBuilder();
// oracle写法
sql.append(" SELECT COLUMN_NAME FROM user_tab_columns where TABLE_NAME = '").append(cusTableName).append("'");
javax.persistence.Query query = entityManager.createNativeQuery(sql.toString());
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
log.info(" 执行的SQL: " + sql);
List<String> columns = new ArrayList<>();
List<HashMap> resultList = query.getResultList();
for (HashMap map : resultList) {
String column = map.get("COLUMN_NAME").toString();
columns.add(column);
}
log.info("所有的列名: " + columns);
return columns;
}
}
3. 使用的Bean
@Data
@ApiModel("动态查询")
public class CustomDataQueryDTO {
@ApiModelProperty(value = "目的表")
private String targetTable;
@ApiModelProperty(value = "每页显示多少数据")
private int size;
@ApiModelProperty(value = "查询第几页")
private int page;
@ApiModelProperty(value = "列名与Value值的集合")
private List<CustomColumnQueryData> datas;
}
@Data
@ApiModel("列名与Value值的集合")
public class CustomColumnQueryData {
@ApiModelProperty(value = "所需操作的字段")
private String columnName;
@ApiModelProperty(value = "字段对应的Value")
private String columnValue;
@ApiModelProperty(value = "是否是模糊查询，true为模糊查询，false为相等查询")
private boolean like;
}
@Data
@ApiModel("数据新增/更新")
public class CustomDataCondition {
@ApiModelProperty(value = "目的表")
private String targetTable;
@ApiModelProperty(value = "删除/编辑的目的表里的数据ID集合")
private List<Long> idList;
@ApiModelProperty(value = "新增/编辑时列名与Value值的集合")
private List<CustomColumnData> datas;
}
@Data
@ApiModel("列名与Value值的集合")
public class CustomColumnData {
@ApiModelProperty(value = "目标字段")
private String columnName;
@ApiModelProperty(value = "目标值")
private String columnValue;
}
