java两种方式操作数据库

因为一个项目学习了另外的方式去操作数据库在这里比较一下。

方式框架

第一种

需要添加注解@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;
        }
    }

差别和感受

其实我感觉后面一种的操作性更加的高，可以看看我的上一篇博客，可以去直接新建表，不是说第一种不行，只是我不知道方法，感觉越学越知道内容多感觉自己无知，那是真的无知，多学学吧。