Java generates SQL statements

The code is as follows:

 /** * 动态生成SQ及SQL参数L
         * @param ve 接收到的消息的CHGLIST
         * @param paramList MQ消息中的SQL参数
         * @param t 泛型对象
         * @param table 数据表
         * @param list 可执行SQL语句集合
         * @return */public <T> String updateSqlAndParamList(Vector<String> ve,List<String> paramList,T t,String table,List<String> list){
        String strSql="";//MQ消息SQlString upSql="";//可执行SQLtry {//组装SQL语句strSql = "update "+table+" set ";
            upSql="update "+table+" set ";for(int i = 0; i < ve.size(); i++){
                String str = "";
                String upStr="";
                String key = ve.get(i);
                String fileName="get"+key.toUpperCase();
                String value=(String)t.getClass().getMethod(fileName).invoke(t);
                paramList.add(i,value);if(i == ve.size()-1){
                    str = key+" = ?";
                    upStr=key+"='"+value+"'";
                }else{
                    str = key+" = ? ,";
                    upStr=key+"='"+value+"',";
                }
                strSql+=str;
                upSql += upStr;
            }
            strSql +=" where Id = ? ";
            upSql+=" where id='"+(String) t.getClass().getMethod("getID").invoke(t)+"'";
            list.add(upSql);
            paramList.add(ve.size(),(String) t.getClass().getMethod("getID").invoke(t));
        } catch (Exception e) {
            logger.info("组装UPDATE SQL失败！失败详情---"+e);
        }return strSql;
    }

1. Use dynamic statements

Many databases provide syntax for processing dynamic SQL, such as Oracle's EXECUTE IMMEDIATE statement, MSSQL's EXEC and SP_EXECUTESQL, Mysql's prepared statements, etc. These functions allow us to handle dynamic queries on the database side and provide great traversal, but this method is only suitable for relatively simple dynamic queries. In complex situations, the following method is often used.

2. Use stored procedures

For complex situations, dynamic SQL is generally spliced ​​into stored procedures. Using stored procedures is relatively flexible, but the coding complexity is too high and sometimes the running efficiency is low.

3. Use other (such as JAVA) programs

Using other external high-level languages ​​(such as JAVA) to splice and then hand them over to the database for execution is also an option. It has high flexibility, but Due to JAVA's lack of support for set calculations, completing these preparations is not easy.

If the main control program that needs to execute dynamic SQL is JAVA, you can use esProc to assist in completing dynamic SQL calculations. esProc is a script that dynamically interprets and executes. Conveniently spell out dynamic SQL execution. esProc provides a JDBC interface that can be placed between Java applications and databases, allowing applications to continue executing esProc scripts as if they were accessing the database, with almost no changes to the application structure.

The following uses examples to illustrate how to use esProc to complete dynamic SQL calculations and integrate them into JAVA programs.

The above is the detailed content of Java generates SQL statements. For more information, please follow other related articles on the PHP Chinese website!