Java - Export SQL Backup Commands for Delete, Insert

這篇主要是說明如何針對查詢出來的資料來產生相關delete, insert指令，會使用到apache

beanutils及java reflect等函式庫，如此一來可以達到資料備份的目的。

由於查詢、刪除、新增指令需動態的產生，因此我們會自訂一些前置步驟規則。

首先，需提供table名稱及查詢 where條件後的指令片段

String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=Homework";
String user = "sa";
String password = "1234";
Connection m_conn = null;

public static void main(String[] args) {
    // TODO Auto-generated method stub
    new ExportSQLData();
}

public ExportSQLData(){
    try{
       Class.forName(driver);
       m_conn = DriverManager.getConnection(url, user, password);
       String exportCon = "where year = ? and month = ? and source = ?";
       String exportTa  = "IncomeRecord"; //table name
       ...
    }catch(Exception e){
       e.printStackTrace();
    } finally{
       try {
          m_conn.close();
       } catch (SQLException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
       }
    }
}

再來是組出查詢的指令

public ExportSQLData(){
    ...
    String execSql = composeSQL(exportTa, exportCon);
    ...
}

public String composeSQL(String table, String condition){
    String sqlTemplate = "select * from %s %s";
    return String.format(sqlTemplate, table, condition);
}

緊接著，產生ResultSet，並透過beanutils包裝成RowSetDynaClass

public ExportSQLData(){
    ...
    RowSetDynaClass rsdc = getRowSet(execSql, "103", "09", "A");
    ...
}
public RowSetDynaClass getRowSet(String sql, Object ...params) throws SQLException{
     PreparedStatement getRowsList = m_conn.prepareStatement(sql);
     for(int i = 0 ; i < params.length ; i++){
        if(params[i] instanceof java.lang.String)
           getRowsList.setString(i+1, (String)params[i]);
        else if(params[i] instanceof java.lang.Integer)
           getRowsList.setInt(i+1, (Integer)params[i]);
        else if(params[i] instanceof java.math.BigInteger)
           getRowsList.setBigDecimal(i+1, (BigDecimal)params[i]);
   
     } 
     ResultSet rs = getRowsList.executeQuery();
  
     return new RowSetDynaClass(rs);
}

此時當我們得到查詢資料的RowSetDynaClass後，再來就是進行重頭戲的組delete, insert指令

public ExportSQLData(){
    ...
    exportSQL(rsdc, exportTa);
    ...
}

public void exportSQL(RowSetDynaClass rsdc, String table){
    List<DynaBean> rows = rsdc.getRows();
    List<String> propertyList = new ArrayList<String>();
    String delTemplate = "delete from %s where %s;";
    String insTemplate = "insert into %s (%s) values (%s);";
    try {
        Class<?> clazz = null;
        try {
           clazz = Class.forName("com.bin.beans."+table);
        } catch (ClassNotFoundException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
        }
        //1. 取得bean field map getMethod
        Map<String, String> col2Method = getGetterMethod(clazz);
        Method m = null;
        PrimaryBean bean = (PrimaryBean) clazz.newInstance();
        String pkeys[] = bean.getPkeys();
        StringBuilder condition = new StringBuilder();
        StringBuilder columns = new StringBuilder();
        StringBuilder values = new StringBuilder();
        StringBuilder result = new StringBuilder();
   
        //2. 組出delete指令的條件
        for(int i = 0 ; i < pkeys.length ; i++){
           condition.append(pkeys[i]+" = :"+pkeys[i]+" and ");
        } 
        condition.delete(condition.length()-5, condition.length());
   
        //3. 組出insert指令下的欄位名稱
        for(DynaProperty d : rsdc.getDynaProperties()){
           propertyList.add(d.getName());
           columns.append(d.getName()+",");
        }
        columns.deleteCharAt(columns.length()-1);
   
        //   System.out.println(condition.toString());
        //   System.out.println(propertyList);
        //   System.out.println(col2Method);
        //4. 資料走訪
        for(int j = 0 ; j < rows.size() ; j++){
           bean = (PrimaryBean) clazz.newInstance();
           PropertyUtils.copyProperties(bean, rows.get(j));
           String con = condition.toString();
    
           //5. 走訪定義好的primary keys array,並且將delete條件下的指令改成值
           for(int i = 0 ; i < pkeys.length ; i++){
              m = clazz.getDeclaredMethod(col2Method.get(pkeys[i]));
              if(m.invoke(bean) instanceof java.lang.String)
                 con = con.replace(":"+pkeys[i], "'"+m.invoke(bean).toString()+"'");
              else if(m.invoke(bean) instanceof java.lang.Integer 
                       || m.invoke(bean) instanceof java.math.BigInteger)
                 con = con.replace(":"+pkeys[i], m.invoke(bean).toString());
           }
           result.append(String.format(delTemplate, table, con).toUpperCase()+"\n");
    
           //6. 組出insert指令的values的部分
           for(int k = 0 ; k < propertyList.size() ; k++){
              m = clazz.getDeclaredMethod(col2Method.get(propertyList.get(k)));
              if(m.invoke(bean) instanceof java.lang.String){
                 values.append("'"+m.invoke(bean).toString()+"',");
              }else if(m.invoke(bean) instanceof java.math.BigDecimal){
                 values.append((BigDecimal)m.invoke(bean)+",");
              }else if(m.invoke(bean) instanceof java.lang.Integer){
                 values.append((Integer)m.invoke(bean)+",");
           }
        }
        values.deleteCharAt(values.length()-1);
    
        result.append(String.format(insTemplate, table, columns.toString(), 
        values.toString()).toUpperCase()+"\n");
        values.delete(0, values.length());
     }
   
     System.out.println(result);
     } catch (Exception e){
       e.printStackTrace();
    }
 }


 public Map<String, String> getGetterMethod(Class<?> c){
    Map<String, String> col2method = new HashMap<String, String>();
    Method[] methods = c.getDeclaredMethods();
    List<String> fields = new ArrayList<String>();
    List<String> fieldsLower = new ArrayList<String>();
    for(Field n : c.getDeclaredFields()){
       fields.add(n.getName());
       fieldsLower.add(n.getName().toLowerCase());
    }
    String colname = ""; 
    boolean flag = false;
    for (Method method : methods){
        if (Modifier.isPublic(method.getModifiers()) && 
               method.getParameterTypes().length == 0) {
            if (method.getName().matches("^get[A-Z].*") && 
              !method.getReturnType().equals(void.class)){
               flag = true;
            }
            if (method.getName().matches("^is[A-Z].*") && 
               method.getReturnType().equals(boolean.class)){
               flag = true;
            }
            if(flag){
               colname = method.getName().toLowerCase()
                          .substring(3, method.getName().length());
               for(int i = 0 ; i < fieldsLower.size() ; i++){
                  if(fieldsLower.get(i).equals(colname)){
                   col2method.put(fields.get(i), method.getName());
               }
            }
        }
        flag = false;
     }
    } 
    return col2method;
 }

exportSQL method程式碼有點長，在程式碼內有寫入六個註解說明，大致講解其下的片段

code主要是做什麼事!

1. 取得bean field map getMethod

透過getGetterMethod會組出JavaBean的成員變數對應它的getter method，目的可用在

如delete from table where column1 = value1 and column2 = value2;

可以透過欄位名稱查出它的getter method，再利用java reflection得到value1, value2的值

2. 組出delete指令的條件

目的先組出delete from table where column1 = :column1 and column2 = :column2;

重點在於:column1，如此一來才知道該字串與哪個欄位有關

在每一個JavaBean內由於繼承PrimaryBean，因此需定義自行的getPkeys method，來確認

哪些欄位為primary key，如此一來才知道要刪除該筆資料時的條件欄位!!

public class IncomeRecord extends PrimaryBean{
   private String year;
   private String month;
   private String id;
   private String source;
   private BigDecimal income;
 
   public String getYear() {
     return year;
   }
   public void setYear(String year) {
     this.year = year;
   }
   .....

   public String[] getPkeys() {
      String[] fields = { "year", "month", "id", "source"};
      return fields;
   }
}

public abstract class PrimaryBean {
   public abstract String[] getPkeys();
}

3. 組出insert指令下的欄位名稱

目的先組出insert into table (column1, column2, ...)

4. 資料走訪

bean = (PrimaryBean) clazz.newInstance();
PropertyUtils.copyProperties(bean, rows.get(j));

重點會將該筆record資料對應欄位拷貝至bean

PS. PrimaryBean為所有JavaBean的父類別

5. 走訪定義好的primary keys array,並且將delete條件下的指令改成值

將剛剛的delete from table where column1 = :column1 and column2 = :column2;下的

:column1 replace to 實際值

2016/04/09補充：利用replace的方式有遇到一種情況，那就是當欄位名稱各自為:seq, :seq1

時，這樣的欄位名稱會互相影響，因此在replace時可多加一" "空白字串，而前面在append指

令時也須多一空白字串在and前面。

6. 組出insert指令的values的部分

目的組出insert into table (column1, column2, ...) values (value1, value2, ...);

最後得到的結果如下：

輸出指令

查詢資料

Zhi-Bin's 談天說地

搜尋此網誌

Java - Export SQL Backup Commands for Delete, Insert

留言

張貼留言