dmgldictdao.java

以前做的一个j2ee的项目

        Connection conn=this.getCurrConnection();
        Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

        String sql1=" ROWID";
        int n = fdList.size();

        //构建字段列表
        for (int i = 0; i < n; i++) {
            dfvo = (DMGLDictFieldVO) fdList.get(i);
            sql1 += (","+dfvo.getColumn_name());
        }

        String sql="select "+sql1+" from "+tableName+" order by ROWID desc ";

        ResultSet rs = stmt.executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();

        ArrayList retList=new ArrayList();

        //字段列数
        int colcount=rsmd.getColumnCount();
        //各列数据类型
        String[] lstColType=new String[colcount];
        //字段名称保存至第一行
        String[] retStr=new String[colcount];

        for(int i=0;i<colcount;i++){
          lstColType[i]=rsmd.getColumnTypeName(i+1);
          retStr[i]=rsmd.getColumnLabel(i+1);
        }
        retList.add(retStr);

        String s1=null;

        //保存数据至数组
        while(rs.next()){
          retStr=new String[colcount];
          for(int i=0;i<colcount;i++){
            if(lstColType[i].equals("TIMESTAMP")||lstColType[i].equals("TIMESTAMP(6)")){
              java.util.Calendar dc=DateUtils.convSqlTimestampToUtilCalendar(rs.getTimestamp(i+1));
              s1=DateUtils.toDateTimeStr(dc);
            }else if(lstColType[i].equals("DATE")){
              java.util.Calendar dc=DateUtils.convSqlTimestampToUtilCalendar(rs.getTimestamp(i+1));
              s1=DateUtils.toDateTimeStr(dc);
            }else{
              s1=rs.getString(i+1);
            }
            retStr[i]=((s1==null)?"":s1);
          }
          retList.add(retStr);
        }

        rs.close();
        stmt.close();
        conn.close();
      return retList;
    }

    /**
     * 功能:向table插入一条数据
     * 参数:fdList 字段列表
     * 参数:vlList 数据列表
     */
    public int addTableData(List fdList,String[] vlList) throws Exception{

        if(fdList==null)return 0;

        DMGLDictFieldVO dfvo=(DMGLDictFieldVO) fdList.get(0);
        String tableName=dfvo.getTable_name();

        Connection conn=this.getCurrConnection();
        Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

        String sql="insert into "+tableName+"(";
        String sql1="";

        int n=fdList.size();
        String[] ftList=new String[n]; //保存字段类型到数组,以便下面使用

        //构建字段列表
        for(int i=0;i<n;i++){
          if(i>0){sql1+=",";}
          dfvo=(DMGLDictFieldVO) fdList.get(i);
          sql1+=dfvo.getColumn_name();
          ftList[i]=dfvo.getData_type();
        }

        sql+=sql1+") values(";

        sql1="";
        String s="";
        //构建取值列表
        for(int i=0;i<vlList.length;i++){
          if(i>0){sql1+=",";}
          //处理不同字段类型的数据
          if(ftList[i].equals("TIMESTAMP(6)")){
            s="TO_DATE("+vlList[i]+", 'YYYY/MM/DD HH24:MI:SS')";
          }else if(ftList[i].equals("DATE")){
            s="TO_DATE("+vlList[i]+", 'MM/DD/YYYY HH24:MI:SS')";
          }else{
            s=vlList[i];
          }
          sql1+=s;
        }

        sql+=sql1+")";

        System.out.println(sql);

        int rows=-1;
        //try {
        rows=stmt.executeUpdate(sql);
        //} catch (SQLException ex) {
        //  ex.printStackTrace();
        //}

        stmt.close();
        conn.close();
        return rows;
    }

    /**
     * 功能:修改table的一条数据
     * 参数:fdList 字段列表
     * 参数:vlList 数据列表
     */

    public int updTableData(String keyField,String keyValue,List fdList,String[] vlList) throws Exception{

        if(fdList==null)return 0;

        DMGLDictFieldVO dfvo=(DMGLDictFieldVO) fdList.get(0);
        String tableName=dfvo.getTable_name();

        Connection conn=this.getCurrConnection();
        Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

        String sql="update "+tableName+" set ";
        String sql1="";

        int n=fdList.size();
        String s,dataType;

        //构建字段列表
        for(int i=0;i<n;i++){
          if(i>0){sql1+=",";}
          dfvo=(DMGLDictFieldVO) fdList.get(i);
          dataType=dfvo.getData_type();
          if (dataType.equals("TIMESTAMP(6)")) {
              s = "TO_DATE(" + vlList[i] + ", 'YYYY/MM/DD HH24:MI:SS')";
          }else if(dataType.equals("DATE")){
              s = "TO_DATE(" + vlList[i] + ", 'MM/DD/YYYY HH24:MI:SS')";
          } else {
              s = vlList[i];
          }
          sql1+=dfvo.getColumn_name()+"="+s;

        }


        sql+=sql1+" where "+keyField+"="+keyValue;

        System.out.println(sql);

        int rows=-1;
        //try {
          rows=stmt.executeUpdate(sql);
        //} catch (SQLException ex) {
        //  ex.printStackTrace();
        //}

        stmt.close();
        conn.close();

        return rows;
    }

    /**
     * 功能:删除table的一条数据
     * 参数:fdList 字段列表
     * 参数:vlList 数据列表
     */

    public int delTableData(String table_name,String keyField,String keyValue) throws Exception{
        Connection conn=this.getCurrConnection();
        Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        keyValue=(keyValue.equals(""))?"''":keyValue;

        String sql="delete from "+table_name+" where "+keyField+" in ("+keyValue+")";

        int rows=-1;
        //try {
        rows=stmt.executeUpdate(sql);
        //} catch (SQLException ex) {
        //  ex.printStackTrace();
        //}

        stmt.close();
        conn.close();
        return rows;
    }


    /**
     * 功能:取回所有的代码表分类列表
     */
    /*
    public List getDMBFLList() throws Exception {
      return this.selectAll("T_DM_DICT.selectDMBFLList",null);
    }
    */

    public List getTest() throws Exception{
      return this.selectAll("T_DM_DICT.selectTest","SELECT DISTINCT dmb_mc FROM t_dm_dict");
    }

    private void jbInit() throws Exception {
    }

    public void settable_name(String table_name) {
        this.table_name = table_name;
    }

    public void setZdbXh(int zdbXh) {

        this.zdbXh = zdbXh;
    }

    public void setZdValue(String zdValue) {
        this.zdValue = zdValue;
    }

    public void setZdDescript(String zdDescript) {
        this.zdDescript = zdDescript;
    }

    public void setQzbXh(int qzbXh) {
        this.qzbXh = qzbXh;
    }

    public String gettable_name() {
        return table_name;
    }

    public int getZdbXh() {

        return zdbXh;
    }

    public String getZdValue() {
        return zdValue;
    }

    public String getZdDescript() {
        return zdDescript;
    }

    public int getQzbXh() {
        return qzbXh;
    }
}