因为一个项目学习了另外的方式去操作数据库在这里比较一下。
方式框架
第一种
需要添加注解@Repository
并且需要在查询的时候
@Query(value = "SELECT * FROM 表名 WHERE id = :id", nativeQuery = true)
表中字段的实体 findByID(@Param("id") String id);
Param
注解的就是:id
要求能够对应上,Query
注解中value 后面直接加对应的查询sql,参数使用上一个方式进行注入
启动类扫描时候需要添加EnableJpaRepositories
第二种
@PersistenceContext
private EntityManager entityManager;
String sql = "select count(*) from user_tables where table_name =upper('"+tablenameNew+"')";
javax.persistence.Query query = entityManager.createNativeQuery(sql);
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List<Map> rows = query.getResultList();
但是使用时候一定要添加注解@Transactional @Modifying
增加
第一种
@Transactional
@Modifying
@Query(value = "insert into GSGUOXIN_PUSHED(id,DJBH,DJWCSj,filename,fileid) values(:id,:DJBH,to_timestamp(:DJWCSj, 'YYYY-MM-DD hh24:mi:ss'),:filename,:fileid)", nativeQuery = true)
void addGuoXinPush(@Param("id") String id, @Param("DJBH") String DJBH, @Param("DJWCSj") String DJWCSj, @Param("filename") String filename, @Param("fileid") String fileid );
第二种
//新表的插入操作
@Transactional
@Modifying
public JSONObject insertIntotablenameNew(List<Map> rows,String tablenameNew,String datagovernancetype) {
try{
StringBuilder rowsName = new StringBuilder();
StringBuilder rowsValue = new StringBuilder();
for (int i = 0; i < rows.size(); i++) {
Map map = rows.get(i);
for (Object o : map.keySet()) {
rowsName.append( ","+o.toString());
Object o1 = map.get(o);
if(StringUtils.isEmpty(o1)){
rowsValue.append(", ''");
}else{
rowsValue.append( ",'"+o1.toString()+"'");
}
}
}
if("1".equals(datagovernancetype)){
rowsName.append(",altertable_type");
rowsValue.append( ",'delete'");
}else{
rowsName.append(",altertable_type");
rowsValue.append( ",'update'");
}
rowsName.append(",altertable_time");
Date date =new Date();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String format = sdf.format(date);
rowsValue.append(",'"+format);
rowsValue.append("'");
String substringRowsName = rowsName.substring(1);
String substringRowsValue = rowsValue.substring(1);
//INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
String sql = "insert into "+" "+tablenameNew+" "+"("+" "+substringRowsName+" "+") values ("+" "+substringRowsValue+" "+")";
int i = entityManager.createNativeQuery(sql).executeUpdate();
JSONObject jsonObject =new JSONObject();
jsonObject.put("status","true");
return jsonObject;
}
catch (Exception e){
JSONObject jsonObject =new JSONObject();
jsonObject.put("status","false");
return jsonObject;
}
}
删除
第一种
@Transactional
@Modifying
@Query(value = "delete GSGUOXIN_PUSHED where id = :id", nativeQuery = true)
void deleteGuoXinPush(@Param("id") String id);
第二种
/**
*
* @param tablenameOld 表名
* @param datagovernancetype 更新类型
* @param condition 条件
* @param split 更细字段分离出来的内容
* @param jsonArray 前段返回值
* @return
*/
@Transactional
@Modifying
public int updatetablenameOld(String tablenameOld,String datagovernancetype,String condition,String[] split,JSONArray jsonArray) {
int res =0;
String sql = "";
//1表示delete,0表示update
if("1".equals(datagovernancetype)){
//DELETE FROM Person WHERE LastName = 'Wilson'
sql = "delete from "+" "+tablenameOld+" "+"where"+" "+condition;
res = entityManager.createNativeQuery(sql).executeUpdate();
return res;
}else{
//UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
sql = "update "+" "+tablenameOld+" "+"set";
for (int i = 0; i < split.length; i++) {
String splits = split[i];
for (int j = 0; j < jsonArray.size(); j++) {
JSONObject jsonObject = jsonArray.getJSONObject(j);
String key = jsonObject.getString("key");
if(key.equals(splits)){
sql = sql +" "+jsonObject.getString("key")+" "+"="+" '"+jsonObject.getString("value")+"' "+",";
}
}
}
sql = sql.substring(0,sql.length()-1);
sql = sql+"where"+" "+condition;
res = entityManager.createNativeQuery(sql).executeUpdate();
return res;
}
}
修改
第一种
@Transactional
@Modifying
@Query(value = "update GSGUOXIN_PUSHED o set o.DJBH = :DJBH where o.id = :id", nativeQuery = true)
void updateGuoXinPush(@Param("id") String id,@Param("DJBH") String DJBH);
第二种
/**
*
* @param tablenameOld 表名
* @param datagovernancetype 更新类型
* @param condition 条件
* @param split 更细字段分离出来的内容
* @param jsonArray 前段返回值
* @return
*/
@Transactional
@Modifying
public int updatetablenameOld(String tablenameOld,String datagovernancetype,String condition,String[] split,JSONArray jsonArray) {
int res =0;
String sql = "";
//1表示delete,0表示update
if("1".equals(datagovernancetype)){
//DELETE FROM Person WHERE LastName = 'Wilson'
sql = "delete from "+" "+tablenameOld+" "+"where"+" "+condition;
res = entityManager.createNativeQuery(sql).executeUpdate();
return res;
}else{
//UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
sql = "update "+" "+tablenameOld+" "+"set";
for (int i = 0; i < split.length; i++) {
String splits = split[i];
for (int j = 0; j < jsonArray.size(); j++) {
JSONObject jsonObject = jsonArray.getJSONObject(j);
String key = jsonObject.getString("key");
if(key.equals(splits)){
sql = sql +" "+jsonObject.getString("key")+" "+"="+" '"+jsonObject.getString("value")+"' "+",";
}
}
}
sql = sql.substring(0,sql.length()-1);
sql = sql+"where"+" "+condition;
res = entityManager.createNativeQuery(sql).executeUpdate();
return res;
}
}
查询
第一种
@Query(value = "select * from GSGUOXIN_PUSHED o where o.DJWCSj >= to_timestamp(:DJWCSj, 'YYYY-MM-DD')", nativeQuery = true)
List<GuoXinPush> findYearOfAllGuoxin( @Param("DJWCSj") String DJWCSj);
第二种
//查询是否有对应的表
@Transactional
@Modifying
public JSONObject selectTablename(String tablenameOld , String tablenameNew) {
JSONObject result = new JSONObject();
try {
String sql = "select count(*) from user_tables where table_name =upper('"+tablenameNew+"')";
javax.persistence.Query query = entityManager.createNativeQuery(sql);
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List<Map> rows = query.getResultList();
String count = rows.get(0).get("COUNT(*)").toString();
if("0".equals(count)){
result.put("status","false");
result.put("message","不存在对应表");
return result;
}else {
JSONObject jsonObject = compareTableStructure(tablenameOld, tablenameNew);
// if("true".equals(jsonObject.getString("status"))){
//
// }
// else {
// return jsonObject;
// }
// result.put("status","true");
// result.put("message","null");
return jsonObject;
}
}catch (Exception sqlex) {
result.put("status","false");
result.put("message","不存在对应表");
return result;
}
}
差别和感受
其实我感觉后面一种的操作性更加的高,可以看看我的上一篇博客,可以去直接新建表,不是说第一种不行,只是我不知道方法,感觉越学越知道内容多感觉自己无知,那是真的无知,多学学吧。