Java - Export SQL Backup Commands for Delete, Insert

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

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

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

首先，需提供table名稱及查詢 where條件後的指令片段
  1. String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  2. String url = "jdbc:sqlserver://localhost:1433;DatabaseName=Homework";
  3. String user = "sa";
  4. String password = "1234";
  5. Connection m_conn = null;
  7. public static void main(String[] args) {
  8.     // TODO Auto-generated method stub
  9.     new ExportSQLData();
  10. }
  12. public ExportSQLData(){
  13.     try{
  14.        Class.forName(driver);
  15.        m_conn = DriverManager.getConnection(url, user, password);
  16.        String exportCon = "where year = ? and month = ? and source = ?";
  17.        String exportTa  = "IncomeRecord"; //table name
  18.        ...
  19.     }catch(Exception e){
  20.        e.printStackTrace();
  21.     } finally{
  22.        try {
  23.           m_conn.close();
  24.        } catch (SQLException e) {
  25.          // TODO Auto-generated catch block
  26.          e.printStackTrace();
  27.        }
  28.     }
  29. }

再來是組出查詢的指令
  1. public ExportSQLData(){
  2.     ...
  3.     String execSql = composeSQL(exportTa, exportCon);
  4.     ...
  5. }
  7. public String composeSQL(String table, String condition){
  8.     String sqlTemplate = "select * from %s %s";
  9.     return String.format(sqlTemplate, table, condition);
  10. }

緊接著，產生ResultSet，並透過beanutils包裝成RowSetDynaClass
  1. public ExportSQLData(){
  2.     ...
  3.     RowSetDynaClass rsdc = getRowSet(execSql, "103", "09", "A");
  4.     ...
  5. }
  6. public RowSetDynaClass getRowSet(String sql, Object ...params) throws SQLException{
  7.      PreparedStatement getRowsList = m_conn.prepareStatement(sql);
  8.      for(int i = 0 ; i < params.length ; i++){
  9.         if(params[i] instanceof java.lang.String)
  10.            getRowsList.setString(i+1, (String)params[i]);
  11.         else if(params[i] instanceof java.lang.Integer)
  12.            getRowsList.setInt(i+1, (Integer)params[i]);
  13.         else if(params[i] instanceof java.math.BigInteger)
  14.            getRowsList.setBigDecimal(i+1, (BigDecimal)params[i]);
  15.    
  16.      } 
  17.      ResultSet rs = getRowsList.executeQuery();
  18.   
  19.      return new RowSetDynaClass(rs);
  20. }

此時當我們得到查詢資料的RowSetDynaClass後，再來就是進行重頭戲的組delete, insert指令
  1. public ExportSQLData(){
  2.     ...
  3.     exportSQL(rsdc, exportTa);
  4.     ...
  5. }
  7. public void exportSQL(RowSetDynaClass rsdc, String table){
  8.     List<DynaBean> rows = rsdc.getRows();
  9.     List<String> propertyList = new ArrayList<String>();
  10.     String delTemplate = "delete from %s where %s;";
  11.     String insTemplate = "insert into %s (%s) values (%s);";
  12.     try {
  13.         Class<?> clazz = null;
  14.         try {
  15.            clazz = Class.forName("com.bin.beans."+table);
  16.         } catch (ClassNotFoundException e) {
  17.            // TODO Auto-generated catch block
  18.            e.printStackTrace();
  19.         }
  20.         //1. 取得bean field map getMethod
  21.         Map<String, String> col2Method = getGetterMethod(clazz);
  22.         Method m = null;
  23.         PrimaryBean bean = (PrimaryBean) clazz.newInstance();
  24.         String pkeys[] = bean.getPkeys();
  25.         StringBuilder condition = new StringBuilder();
  26.         StringBuilder columns = new StringBuilder();
  27.         StringBuilder values = new StringBuilder();
  28.         StringBuilder result = new StringBuilder();
  29.    
  30.         //2. 組出delete指令的條件
  31.         for(int i = 0 ; i < pkeys.length ; i++){
  32.            condition.append(pkeys[i]+" = :"+pkeys[i]+" and ");
  33.         } 
  34.         condition.delete(condition.length()-5, condition.length());
  35.    
  36.         //3. 組出insert指令下的欄位名稱
  37.         for(DynaProperty d : rsdc.getDynaProperties()){
  38.            propertyList.add(d.getName());
  39.            columns.append(d.getName()+",");
  40.         }
  41.         columns.deleteCharAt(columns.length()-1);
  42.    
  43.         //   System.out.println(condition.toString());
  44.         //   System.out.println(propertyList);
  45.         //   System.out.println(col2Method);
  46.         //4. 資料走訪
  47.         for(int j = 0 ; j < rows.size() ; j++){
  48.            bean = (PrimaryBean) clazz.newInstance();
  49.            PropertyUtils.copyProperties(bean, rows.get(j));
  50.            String con = condition.toString();
  51.     
  52.            //5. 走訪定義好的primary keys array,並且將delete條件下的指令改成值
  53.            for(int i = 0 ; i < pkeys.length ; i++){
  54.               m = clazz.getDeclaredMethod(col2Method.get(pkeys[i]));
  55.               if(m.invoke(bean) instanceof java.lang.String)
  56.                  con = con.replace(":"+pkeys[i], "'"+m.invoke(bean).toString()+"'");
  57.               else if(m.invoke(bean) instanceof java.lang.Integer 
  58.                        || m.invoke(bean) instanceof java.math.BigInteger)
  59.                  con = con.replace(":"+pkeys[i], m.invoke(bean).toString());
  60.            }
  61.            result.append(String.format(delTemplate, table, con).toUpperCase()+"\n");
  62.     
  63.            //6. 組出insert指令的values的部分
  64.            for(int k = 0 ; k < propertyList.size() ; k++){
  65.               m = clazz.getDeclaredMethod(col2Method.get(propertyList.get(k)));
  66.               if(m.invoke(bean) instanceof java.lang.String){
  67.                  values.append("'"+m.invoke(bean).toString()+"',");
  68.               }else if(m.invoke(bean) instanceof java.math.BigDecimal){
  69.                  values.append((BigDecimal)m.invoke(bean)+",");
  70.               }else if(m.invoke(bean) instanceof java.lang.Integer){
  71.                  values.append((Integer)m.invoke(bean)+",");
  72.            }
  73.         }
  74.         values.deleteCharAt(values.length()-1);
  75.     
  76.         result.append(String.format(insTemplate, table, columns.toString(), 
  77.         values.toString()).toUpperCase()+"\n");
  78.         values.delete(0, values.length());
  79.      }
  80.    
  81.      System.out.println(result);
  82.      } catch (Exception e){
  83.        e.printStackTrace();
  84.     }
  85.  }
  88.  public Map<String, String> getGetterMethod(Class<?> c){
  89.     Map<String, String> col2method = new HashMap<String, String>();
  90.     Method[] methods = c.getDeclaredMethods();
  91.     List<String> fields = new ArrayList<String>();
  92.     List<String> fieldsLower = new ArrayList<String>();
  93.     for(Field n : c.getDeclaredFields()){
  94.        fields.add(n.getName());
  95.        fieldsLower.add(n.getName().toLowerCase());
  96.     }
  97.     String colname = ""; 
  98.     boolean flag = false;
  99.     for (Method method : methods){
  100.         if (Modifier.isPublic(method.getModifiers()) && 
  101.                method.getParameterTypes().length == 0) {
  102.             if (method.getName().matches("^get[A-Z].*") && 
  103.               !method.getReturnType().equals(void.class)){
  104.                flag = true;
  105.             }
  106.             if (method.getName().matches("^is[A-Z].*") && 
  107.                method.getReturnType().equals(boolean.class)){
  108.                flag = true;
  109.             }
  110.             if(flag){
  111.                colname = method.getName().toLowerCase()
  112.                           .substring(3, method.getName().length());
  113.                for(int i = 0 ; i < fieldsLower.size() ; i++){
  114.                   if(fieldsLower.get(i).equals(colname)){
  115.                    col2method.put(fields.get(i), method.getName());
  116.                }
  117.             }
  118.         }
  119.         flag = false;
  120.      }
  121.     } 
  122.     return col2method;
  123.  }

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，如此一來才知道要刪除該筆資料時的條件欄位!!

  1. public class IncomeRecord extends PrimaryBean{
  2.    private String year;
  3.    private String month;
  4.    private String id;
  5.    private String source;
  6.    private BigDecimal income;
  7.  
  8.    public String getYear() {
  9.      return year;
  10.    }
  11.    public void setYear(String year) {
  12.      this.year = year;
  13.    }
  14.    .....
  16.    public String[] getPkeys() {
  17.       String[] fields = { "year", "month", "id", "source"};
  18.       return fields;
  19.    }
  20. }

  1. public abstract class PrimaryBean {
  2.    public abstract String[] getPkeys();
  3. }

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, ...);

最後得到的結果如下：

輸出指令

查詢資料

留言

張貼留言