在使用SpringData JPA组件时,我们一般都是定义实体,定义仓库接口,然后就可以增删改查了。
但是在实际业务中,很多数据表的名称并不是固定的,比如我们的轨迹表,就是每天一张,类似:track_20210601。
这时如果在实体的@Table注解中再写明数据表名称就不现实,需要一个途径去处理此类问题,因为我们的业务涉及这种情况的表就两个,所以这里记录一种实现这个业务的简单方法。
具体如以下代码所示:
package com.jns.jpanamingstrategy;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;
import java.util.List;
@SpringBootTest
class JpaNamingStrategyApplicationTests {
@PersistenceContext
private EntityManager entityManager;
// SQL模板
String insertRecordTmp = "insert into %s(`id`,`name`)values(%d, '%s')";
String selectRecordTmp = "select `id`,`name` from %s";
String updateRecordTmp = "update %s set `name`='%s' where `id`=%d";
String deleteRecordTmp = "delete from %s where `id`=%d";
String findTableTmp = "select count(TABLE_NAME) from information_schema.`TABLES` where TABLE_SCHEMA='%s' AND TABLE_NAME='%s'";
@Test
// 启用事务 这个注解必须加
@Transactional
void contextLoads() {
// 数据表名称
String tableName = "student_01";
// 判断数据表是否存在
if (!findSameNameTable("test", tableName)) {
create(tableName);
insert(tableName, 1, "kaven");
insert(tableName, 2, "john");
insert(tableName, 3, "lily");
} else {
System.out.println(tableName + " 已经存在!");
}
System.out.println("验证插入");
select(tableName);
update(tableName, 1, "han mei mei");
System.out.println("验证更新");
select(tableName);
delete(tableName, 3);
System.out.println("验证删除");
select(tableName);
}
public void create(String tableName) {
String createTable = "create table " + tableName + "(`id` int primary key , `name` varchar(50))";
Query query = entityManager.createNativeQuery(createTable);
query.executeUpdate();
}
public void insert(String tableName, int id, String name) {
String sql = String.format(insertRecordTmp, tableName, id, name);
Query query = entityManager.createNativeQuery(sql);
query.executeUpdate();
}
public void select(String tableName) {
String sql = String.format(selectRecordTmp, tableName);
Query query = entityManager.createNativeQuery(sql);
// Object[] 为每一条数据每列的值,顺序为数据表列顺序,索引0开始
List<Object[]> l = query.getResultList();
for (Object[] o : l) {
System.out.println(o[0] + " " + o[1]);
}
}
public void update(String tableName, int id, String name) {
String sql = String.format(updateRecordTmp, tableName, name, id);
Query query = entityManager.createNativeQuery(sql);
query.executeUpdate();
}
public void delete(String tableName, int id) {
String sql = String.format(deleteRecordTmp, tableName, id);
Query query = entityManager.createNativeQuery(sql);
query.executeUpdate();
}
public boolean findSameNameTable(String TABLE_SCHEMA, String TABLE_NAME) {
boolean same = false;
String sql = String.format(findTableTmp, TABLE_SCHEMA, TABLE_NAME);
Query query = entityManager.createNativeQuery(sql);
List<Object> l = query.getResultList();
if (Integer.parseInt(String.valueOf(l.get(0))) > 0) {
same = true;
}
return same;
}
}
有一个地方需要注意,就是查询的时候,关于字段的返回,具体说明如下:
1625807923(1).png
就是当查询语句只有一个字段时,就直接返回该字段的值了,不会再是Object[]数组了。
网友评论