db2中对数据字典的应用

package  tool;

import  java.io.BufferedReader;
import  java.io.File;
import  java.io.FileReader;
import  java.io.FileWriter;
import  java.io.OutputStreamWriter;
import  java.sql.Connection;
import  java.sql.DriverManager;
import  java.sql.SQLException;
import  java.util.List;
import  java.util.Map;

import  jp.co.mftbc.cbp.com.QueryBuffer;

import  org.apache.commons.dbutils.DbUtils;
import  org.apache.commons.dbutils.QueryRunner;
import  org.apache.commons.dbutils.handlers.MapListHandler;

/** */ /**
 * DBアクセス部品を生成するクラス
 */
public   class  creator  ... {
    
    private String         root_path     = "tool";
    private String         src_path     = "webapps/WEB-INF/src/java/jp/co/mftbc/cbp/dao";
    private Connection     conn         = null; 
    private String         tableName     = null;
    private String         tableType     = null;
    private String         tableComm     = null;
    private List             columns        = null;
    private List             keys        = null;
    private String         colName        = null;
    private String         colComm        = null;
    private String         colType     = null;
    private String         colKey         = null;
    private String         colDef         = null;
    
    public void execute(String str) ...{
        
        try ...{
            
            Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
            conn = DriverManager.getConnection("jdbc:db2://192.168.35.219:50000/mftbcDB", "db2admin", "db2admin");
            
            // テーブル情報
            if(!getTableInfo(str)) return;
            
        } catch(Exception e)...{
            e.printStackTrace();
        }finally...{
            try...{
                DbUtils.close(conn);
            }catch(Exception e)...{}
        }
        
    }
    
    /**//*
     * テーブル情報取得
     * @return
     * @throws Exception  
     */
    private boolean getTableInfo(String str) throws Exception ...{
        
        QueryBuffer buf = new QueryBuffer();
        buf.append("SELECT TABNAME,                ");
        buf.append("       TYPE,                 ");
        buf.append("       REMARKS                 ");
        buf.append("  FROM SYSCAT.TABLES         ");
        buf.append(" WHERE TABSCHEMA = 'DB2ADMIN' ");
        buf.append("  AND  TABNAME like ?         ", str);
        List rec = (List) new QueryRunner().query(conn, buf.getQuery(), buf.getObject(), new MapListHandler());
        if(rec.isEmpty())...{
            System.out.println("テーブルなし");
            return false;
        }
        
        for(int i = 0; i < rec.size(); i++)...{
            Map map = (Map)rec.get(i);
            tableName = String.valueOf(map.get("TABNAME"));
            tableType = String.valueOf(map.get("TYPE"));
            tableComm = String.valueOf(map.get("REMARKS"));
            
            System.out.println(tableName);
            
            //カラム情報
            getColumnInfo();
            //キー情報
            getKeyInfo();
            
            //構造体を出力
            createStruct();
            //アクセス部品を出力
            createAccess();
            
        }
        
        return true;
    }
    
    /** *//**
     * カラム情報取得
     * @throws SQLException
     */
    private void getColumnInfo() throws SQLException...{
        
        QueryBuffer buf = new QueryBuffer();
        buf.append("SELECT COLNAME,             ");
        buf.append("       KEYSEQ,                 ");
        buf.append("       TYPENAME,             ");
        buf.append("       REMARKS,              ");
        buf.append("       DEFAULT              ");
        buf.append("  FROM SYSCAT.COLUMNS         ");
        buf.append(" WHERE TABNAME = ?             ", tableName);
        buf.append(" ORDER BY colno     ");
        
        columns = (List) new QueryRunner().query(conn, buf.getQuery(), buf.getObject(), new MapListHandler());
    }
    
    /** *//**
     * KEY情報取得
     * @throws SQLException
     */
    private void getKeyInfo() throws SQLException...{
        
        QueryBuffer buf = new QueryBuffer();
        buf.append("SELECT COLNAME,             ");
        buf.append("       KEYSEQ,                 ");
        buf.append("       TYPENAME,             ");
        buf.append("       REMARKS              ");
        buf.append("  FROM SYSCAT.COLUMNS         ");
        buf.append(" WHERE TABNAME = ?             ", tableName);
        buf.append("  AND  KEYSEQ is not null     ");
        buf.append(" ORDER BY KEYSEQ, colno     ");
        
        keys = (List) new QueryRunner().query(conn, buf.getQuery(), buf.getObject(), new MapListHandler());
    }
    
    /** *//**
     * 構造体を出力する。
     * @throws Exception 
     */
    private void createStruct() throws Exception...{
        
        String strPath = src_path + "/" + tableName + "Struct.java";
        if(new File(strPath).exists())...{
            new File(strPath).delete();
        }
        
        FileReader inFile = new FileReader(root_path + "/template/templateStruct.java");
        BufferedReader inBuffer = new BufferedReader(inFile);

        String line, temp, str;
        while ((line = inBuffer.readLine()) != null) ...{
            
            temp = "";
            if("//@LOOP".equals(line))...{
                while ((line = inBuffer.readLine()) != null) ...{
                    if("//@END LOOP".equals(line))...{
                        break;
                    }
                    if(temp.equals(""))...{
                        temp = line;
                    }else...{
                        temp = temp + " " + line;
                    }
                }
                
                for(int i = 0; i < columns.size(); i++)...{
                    setColumn((Map)columns.get(i));
                    
                    str = temp;
                    str = str.replaceAll("/[TABLE_NAME/]"        , tableName);
                    str = str.replaceAll("/[TABLE_COMMENT/]"    , tableComm);
                    str = str.replaceAll("/[OBJECT_NAME/]"    , colName.toLowerCase());
                    str = str.replaceAll("/[OBJECT_CLASS/]"    , getClassName(colType));
                    str = str.replaceAll("/[DEFAULT/]"        , colDef.replaceAll("'", """));
                    str = str.replaceAll("/[METHOD_NAME/]"    , getMethodName(colName));
                    str = str.replaceAll("/[METHOD_COMMENT/]"    , colComm);
                    output(strPath, str);
                }
                
            }else...{
                
                str = line;
                str = str.replaceAll("/[TABLE_NAME/]"        , tableName);
                str = str.replaceAll("/[TABLE_COMMENT/]"    , tableComm);
                output(strPath, str);
                
            }
            
        }
      
        inBuffer.close();
        
    }
    
    /** *//**
     * アクセス部品を出力する。
     * @throws Exception 
     */
    private void createAccess() throws Exception...{
        
        String strPath = src_path + "/" + tableName + ".java";
        if(new File(strPath).exists())...{
            new File(strPath).delete();
        }
        
        FileReader inFile = new FileReader(root_path + "/template/template.java");
        BufferedReader inBuffer = new BufferedReader(inFile);
        
        String line, temp, str;
        while ((line = inBuffer.readLine()) != null) ...{
            
            temp = "";
            if("//@COLUMN_LOOP".equals(line))...{
                while ((line = inBuffer.readLine()) != null) ...{
                    if("//@COLUMN_END LOOP".equals(line))...{
                        break;
                    }
                    if(temp.equals(""))...{
                        temp = line;
                    }else...{
                        temp = temp + " " + line;
                    }
                }
                
                for(int i = 0; i < columns.size(); i++)...{
                    setColumn((Map)columns.get(i));
                    
                    str = temp;
                    str = str.replaceAll("/[TABLE_NAME/]"        , tableName);
                    str = str.replaceAll("/[TABLE_COMMENT/]"    , tableComm);
                    str = str.replaceAll("/[COL_NAME/]"        , colName);
                    str = str.replaceAll("/[COL_COMMENT/]"    , colComm);
                    str = str.replaceAll("/[METHOD_NAME/]"    , getMethodName(colName));
                    str = str.replaceAll("/[OBJECT_CLASS/]"    , getClassName(colType));
                    
                    output(strPath, str);
                }
                
                
            //*********************************************************************
            //* SELECTメソッド(KEY)
            //*********************************************************************
            }else if("//@SELECT_METHOD".equals(line))...{
                if(keys.size() == 0) continue;
                
                output(strPath, " /**");
                output(strPath, "  * " + tableComm + "の検索を行うメソッド");
                output(strPath, "  * @param conn 接続情報");
                
                str = "";
                for(int i = 0; i < keys.size(); i++)...{
                    setColumn((Map)keys.get(i));
                    output(strPath, "  * @param " + colName.toLowerCase() + " " + colComm);
                }
                
                output(strPath, "  * @return 検索結果");
                output(strPath, "  * @throws SQLException");
                output(strPath, "  */");
                output(strPath, " public " + tableName + "Struct select(");
                output(strPath, " Connection conn, ");
                
                for(int i = 0; i < keys.size(); i++)...{
                    setColumn((Map)keys.get(i));
                    output(strPath, " " + getClassName(colType) + " " + colName.toLowerCase() + ((i == keys.size() - 1)?" ":", "));
                }
                
                output(strPath, " ) throws SQLException {");
                output(strPath, " " + "");
                output(strPath, " " + "QueryBuffer buf = new QueryBuffer();");
                
                for(int i = 0; i < keys.size(); i++)...{
                    setColumn((Map)keys.get(i));
                    output(strPath, " " + "buf.append("" + ((i == 0)? " WHERE ":"  AND  ") + colName + " = ? ", " + colName.toLowerCase() + ");");
                }
                
                output(strPath, " " + "");
                output(strPath, " " + "List list = select(conn, buf.getQuery(), buf.getObject());");
                output(strPath, " " + "if(list.isEmpty()) return null;");
                output(strPath, " " + "return (" + tableName + "Struct)list.get(0);");
                output(strPath, " }");
                output(strPath, " ");
                
            //*********************************************************************
            //* LOCKメソッド
            //*********************************************************************
            }else if("//@LOCK_METHOD".equals(line))...{
                if(keys.size() == 0) continue;
                
                output(strPath, " /**");
                output(strPath, "  * " + tableComm + "のロックを行うメソッド");
                output(strPath, "  * @param conn 接続情報");
                
                str = "";
                for(int i = 0; i < keys.size(); i++)...{
                    setColumn((Map)keys.get(i));
                    output(strPath, "  * @param " + colName.toLowerCase() + " " + colComm);
                }
                
                output(strPath, "  * @return 検索結果");
                output(strPath, "  * @throws SQLException");
                output(strPath, "  */");
                output(strPath, " public " + tableName + "Struct lock(");
                output(strPath, " Connection conn, ");
                
                for(int i = 0; i < keys.size(); i++)...{
                    setColumn((Map)keys.get(i));
                    output(strPath, " " + getClassName(colType) + " " + colName.toLowerCase() + ", ");
                }
                output(strPath, " java.sql.Timestamp modified_dt ");
                
                output(strPath, " ) throws SQLException {");
                output(strPath, " " + "");
                output(strPath, " " + "QueryBuffer buf = new QueryBuffer();");
                
                for(int i = 0; i < keys.size(); i++)...{
                    setColumn((Map)keys.get(i));
                    output(strPath, " " + "buf.append("" + ((i == 0)? " WHERE ":"  AND  ") + colName + " = ? ", " + colName.toLowerCase() + ");");
                }
                
                output(strPath, " " + "buf.append("  AND  MODIFIED_DT = ? ", modified_dt);");
//                output(strPath, " " + "buf.append("  AND (MODIFIED_ID = ? ", user_id);");
//                output(strPath, " " + "buf.append("  OR   MODIFIED_DT = ? )", modified_dt);");
                output(strPath, " " + "buf.append(" FOR UPDATE WITH RS ");");
                output(strPath, " " + "");
                output(strPath, " " + "List list = select(conn, buf.getQuery(), buf.getObject());");
                output(strPath, " " + "if(list.isEmpty()) return null;");
                output(strPath, " " + "return (" + tableName + "Struct)list.get(0);");
                output(strPath, " }");
                output(strPath, " ");

            //*********************************************************************
            //* INSERTメソッド
            //*********************************************************************
            }else if("//@INSERT_METHOD".equals(line))...{
                if("V".equals(tableType)) continue;

                output(strPath, " /**");
                output(strPath, "  * " + tableComm + "の登録を行うメソッド");
                output(strPath, "  * @param conn 接続情報");
                output(strPath, "  * @param data 登録データ");
                output(strPath, "  * @return 処理結果");
                output(strPath, "  * @throws SQLException");
                output(strPath, "  */");
                output(strPath, " public int insert(");
                output(strPath, " Connection conn, ");
                output(strPath, " " + tableName + "Struct data");
                output(strPath, " ) throws SQLException {");
                output(strPath, " " + "");
                output(strPath, " " + "QueryBuffer buf = new QueryBuffer();");
                output(strPath, " " + "buf.append("INSERT INTO " + tableName + "( ");");
                
                for(int i = 0 ; i < columns.size(); i++)...{
                    setColumn((Map)columns.get(i));
                    output(strPath, " " + "buf.append("  " + colName + ((i == columns.size() - 1)?"":",") + " ");");
                }
                
                output(strPath, " " + "buf.append(")VALUES( ");");
                
                for(int i = 0 ; i < columns.size(); i++)...{
                    setColumn((Map)columns.get(i));
                    
                    //更新者、登録者
                    if(colName.equals("REGISTED_ID")
                    || colName.equals("MODIFIED_ID"))...{
                        output(strPath, " " + "buf.append("  ?" + ((i == columns.size() - 1)?" ":",") + " ", user_id);");
                        continue;
                    }
                    
                    //更新日、登録日
                    if(colName.equals("REGISTED_DT")
                    || colName.equals("MODIFIED_DT"))...{
                        output(strPath, " " + "buf.append("  current timestamp" + ((i == columns.size() - 1)?" ":",") + " ");");
                        continue;
                    }
                    
                    if(colType.equals("DATE"))...{
                        output(strPath, " " + "buf.append("  ?" + ((i == columns.size() - 1)?" ":",") + " ", Util.dateToStr(data.get" + getMethodName(colName) + "(), Util.DB2_DATE));");
                    }else if(colType.equals("TIMESTAMP"))...{
                        output(strPath, " " + "buf.append("  ?" + ((i == columns.size() - 1)?" ":",") + " ", Util.dateToStr(data.get" + getMethodName(colName) + "(), Util.DB2_TIMESTAMP));");
                    }else...{
                        output(strPath, " " + "buf.append("  ?" + ((i == columns.size() - 1)?" ":",") + " ", data.get" + getMethodName(colName) + "());");
                    }
                }
                
                output(strPath, " " + "buf.append(") ");");
                output(strPath, " " + "");
                output(strPath, " " + "return new QueryRunner().update(conn, buf.getQuery(), buf.getObject());");
                output(strPath, " }");
                output(strPath, " ");
                
            //*********************************************************************
            //* UPDATEメソッド
            //*********************************************************************
            }else if("//@UPDATE_METHOD".equals(line))...{
                if(keys.size() == 0) continue;
                
                output(strPath, " /**");
                output(strPath, "  * " + tableComm + "の更新を行うメソッド");
                output(strPath, "  * @param conn 接続情報");
                output(strPath, "  * @param data 登録データ");
                output(strPath, "  * @return 処理結果");
                output(strPath, "  * @throws SQLException");
                output(strPath, "  */");
                output(strPath, " public int update(");
                output(strPath, " Connection conn, ");
                output(strPath, " " + tableName + "Struct data");
                output(strPath, " ) throws SQLException {");
                output(strPath, " " + "");
                output(strPath, " " + "QueryBuffer buf = new QueryBuffer();");
                output(strPath, " " + "buf.append("UPDATE " + tableName + " ");");
                
                boolean initflag = true;
                for(int i = 0 ; i < columns.size(); i++)...{
                    setColumn((Map)columns.get(i));
                    
                    //キーは更新しない
                    if(!colKey.equals("null")) continue;
                    
                    //登録者、登録日は更新しない
                    if(colName.equals("REGISTED_ID")
                    || colName.equals("REGISTED_DT"))...{
                        continue;
                    }
                    
                    //更新者
                    if(colName.equals("MODIFIED_ID"))...{
                        output(strPath, " " + "buf.append("" + ((initflag)? " SET   ":"       ") + colName + " = ?" + ((i == columns.size() - 1)?"":",") + " ", user_id);");

                    //更新日
                    }else                    
                    if(colName.equals("MODIFIED_DT"))...{
                        output(strPath, " " + "buf.append("" + ((initflag)? " SET   ":"       ") + colName + " = current timestamp" + ((i == columns.size() - 1)?"":",") + " ");");
                        
                    //その他の項目
                    }else...{
                        if(colType.equals("DATE"))...{
                            output(strPath, " " + "buf.append("" + ((initflag)? " SET   ":"       ") + colName + " = ?" + ((i == columns.size() - 1)?"":",") + " ", Util.dateToStr(data.get" + getMethodName(colName) + "(), Util.DB2_DATE));");
                        }else if(colType.equals("TIMESTAMP"))...{
                            output(strPath, " " + "buf.append("" + ((initflag)? " SET   ":"       ") + colName + " = ?" + ((i == columns.size() - 1)?"":",") + " ", Util.dateToStr(data.get" + getMethodName(colName) + "(), Util.DB2_TIMESTAMP));");
                        }else...{
                            output(strPath, " " + "buf.append("" + ((initflag)? " SET   ":"       ") + colName + " = ?" + ((i == columns.size() - 1)?"":",") + " ", data.get" + getMethodName(colName) + "());");
                        }
                    }

                    initflag = false;
                }
                
                for(int i = 0 ; i < keys.size(); i++)...{
                    setColumn((Map)keys.get(i));
                    output(strPath, " " + "buf.append("" + ((i == 0)? " WHERE ":"  AND  ") + colName + " = ? ", data.get" + getMethodName(colName) + "());");
                }
                
                output(strPath, " " + "");
                output(strPath, " " + "return new QueryRunner().update(conn, buf.getQuery(), buf.getObject());");
                output(strPath, " }");
                output(strPath, " ");
                    
            //*********************************************************************
            //* DELETEメソッド(任意)
            //*********************************************************************
            }else if("//@DELETE_METHOD".equals(line))...{
                if("V".equals(tableType)) continue;
            
                output(strPath, " /**");
                output(strPath, "  * " + tableComm + "の削除を行うメソッド");
                output(strPath, "  * @param conn 接続情報");
                output(strPath, "  * @param where 条件");
                output(strPath, "  * @param obj バインド変数");
                output(strPath, "  * @return 処理結果");
                output(strPath, "  * @throws SQLException");
                output(strPath, "  */");
                output(strPath, " public int delete(");
                output(strPath, " Connection conn, ");
                output(strPath, " String where, ");
                output(strPath, " Object[] obj ");
                output(strPath, " ) throws SQLException {");
                output(strPath, " " + "");
                output(strPath, " " + "QueryBuffer buf = new QueryBuffer();");
                output(strPath, " " + "buf.append("DELETE FROM " + tableName + " ");");
                output(strPath, " " + "");
                output(strPath, " " + "return new QueryRunner().update(conn, buf.getQuery() + where, obj);");
                output(strPath, " }");
                output(strPath, " ");
                
            //*********************************************************************
            //* DELETEメソッド（キー）
            //*********************************************************************
            }else if("//@DELETE_METHOD-KEY".equals(line))...{
                if(keys.size() == 0) continue;
            
                output(strPath, " /**");
                output(strPath, "  * " + tableComm + "の削除を行うメソッド");
                output(strPath, "  * @param conn 接続情報");
                
                str = "";
                for(int i = 0; i < keys.size(); i++)...{
                    setColumn((Map)keys.get(i));
                    output(strPath, "  * @param " + colName.toLowerCase() + " " + colComm);
                }
                
                output(strPath, "  * @return 処理結果");
                output(strPath, "  * @throws SQLException");
                output(strPath, "  */");
                output(strPath, " public int delete(");
                output(strPath, " Connection conn, ");
                
                for(int i = 0; i < keys.size(); i++)...{
                    setColumn((Map)keys.get(i));
                    output(strPath, " " + getClassName(colType) + " " + colName.toLowerCase() + ((i == keys.size() - 1)?" ":", "));
                }
                
                output(strPath, " ) throws SQLException {");
                output(strPath, " " + "");
                output(strPath, " " + "QueryBuffer buf = new QueryBuffer();");
                
                for(int i = 0; i < keys.size(); i++)...{
                    setColumn((Map)keys.get(i));
                    output(strPath, " " + "buf.append("" + ((i == 0)? " WHERE ":"  AND  ") + colName + " = ? ", " + colName.toLowerCase() + ");");
                }
                
                output(strPath, " " + "");
                output(strPath, " " + "return delete(conn, buf.getQuery(), buf.getObject());");
                output(strPath, " }");
                output(strPath, " ");
                    
            }else...{
                
                str = line;
                str = str.replaceAll("/[TABLE_NAME/]"        , tableName);
                str = str.replaceAll("/[TABLE_COMMENT/]"    , tableComm);
                output(strPath, str);
                
            }

            
        }
      
        inBuffer.close();
        
    }
    
    private void setColumn(Map map)...{
        colName = String.valueOf(map.get("COLNAME"));
        colComm = String.valueOf(map.get("REMARKS"));
        colType = String.valueOf(map.get("TYPENAME"));
        colKey  = String.valueOf(map.get("KEYSEQ"));
        colDef  = String.valueOf(map.get("DEFAULT"));
    }
    
    /** *//**
     * 機能名
     * @param str
     * @return
     */
    private String getMethodName(String str)...{
        return str.substring(0, 1) + str.substring(1).toLowerCase();
    }
    
    /** *//**
     * DB2の型をJavaクラスに変換する。
     * @param str
     * @return
     */
    private String getClassName(String str)...{
        if(str.equals("VARCHAR"))...{
            return "String";
        }else if(str.equals("CHARACTER"))...{
            return "String";
        }else if(str.equals("SMALLINT"))...{
            return "Integer";
        }else if(str.equals("INTEGER"))...{
            return "Integer";
        }else if(str.equals("DOUBLE"))...{
            return "Double";
        }else if(str.equals("DATE"))...{
            return "java.util.Date";
        }else if(str.equals("TIMESTAMP"))...{
            return "java.sql.Timestamp";
        }else...{
            return "Object";
        }
    }
    
    /** *//**
     * ファイル出力
     * @param path パス
     * @param str 文字列
     * @throws Exception
     */
    private void output(String path, String str) throws Exception...{
      OutputStreamWriter out = new FileWriter(new File(path), true);
      out.write(str + " ");
      out.close();
    }

}