JDBC 获取表中列名以及列的类型-CSDN博客

有时候我们在做数据迁移的时候会遇到这种需求,将一张含有二进制字段的表中的数据完整的迁移到另一张表中 (注意: 将之前的BOLB类型改为COLB类型 )

考虑这种情况 :

1.先创建一张新的表 2.将类型改掉 3.复制表中的数据到新表中

可是如果老的表中含有日期字段在使用程序添加值的时候会出现 *列在此处不允许的错误*

那么这种情况需要转换表中日期类型在insert语句中使用to_date(date,'yyyy-mm-dd') 函数

可是怎么才能确定这个字段就是日期字段??

使用ResultSetMetaData rd = rs.getMetaData();对象来获取对应的表的信息

例如获取日期类型:rd.getColumnTypeName(i).equals("DATE")

下边还是直接看看源码:

  1 import java.io.File;
  2 import java.io.FileInputStream;
  3 import java.io.FileOutputStream;
  4 import java.io.FileWriter;
  5 import java.io.InputStream;
  6 import java.io.PrintWriter;
  7 import java.sql.Connection;
  8 import java.sql.DriverManager;
  9 import java.sql.PreparedStatement;
 10 import java.sql.ResultSet;
 11 import java.sql.ResultSetMetaData;
 12 
 13 import oracle.sql.BLOB;
 14 
 15 import org.junit.Test;
 16 import org.junit.experimental.results.ResultMatchers;
 17 
 18 /**
 19  * 复制表  T_ZCGL_JLZCKP_t -> T_ZCGL_JLZCKP_NEW  BLOB->CLOB  null
 20  * @author Administrator
 21  *
 22  */
 23 public class CopyTable2 {
 24 
 25     private static Connection getCon()throws Exception{
 26         Class.forName("oracle.jdbc.OracleDriver");
 27         return DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.2:1521:orcl", "sg_uap", "sys");
 28     }
 29     private static void close(Connection con){
 30         try {
 31             if(con!=null)
 32                 con.close();
 33         } catch (Exception e) {
 34             e.printStackTrace();
 35         }
 36     }
 37     public static void main(String[] args) {
 38         System.out.println(new CopyTable2().a(5));
 39     }
 40     private String a(int num){
 41         StringBuffer buf = new StringBuffer();
 42         for(int i=0;i<num;i++){
 43             buf.append("?,");
 44         }
 45         return buf.deleteCharAt(buf.length()-1).toString();
 46     }
 47     
 48     @Test
 49     public void copy(){
 50         Connection con = null;
 51         PreparedStatement inPs = null;
 52         PreparedStatement outPs = null;
 53         String QUERY_SQL = "select * from T_ZCGL_JLZCKP_T t";
 54         String INSERTSQL = "insert into T_ZCGL_JLZCKP_NEW values("+this.a(46)+",'<CLOB>', '<CLOB>', '<CLOB>')";
 55         int num = 0;
 56         try {
 57             con = this.getCon();
 58             //con.setAutoCommit(false);
 59             inPs = con.prepareStatement(QUERY_SQL);
 60             outPs = con.prepareStatement(INSERTSQL);
 61             ResultSet rs = inPs.executeQuery();
 62             while(rs.next()){
 63                 num++;
 64                 ResultSetMetaData rd = rs.getMetaData();
 65                 StringBuffer buf = new StringBuffer();
 66                 for(int i=1;i<=46;i++){
 67                     outPs.setString(i, rs.getString(i));
 68                     if(null==rs.getString(i)){
 69                         buf.append(null+",");
 70                     }else if(rd.getColumnTypeName(i).equals("DATE")){
 71                         // 转换日期格式
 72                         buf.append("to_date('"+rs.getDate(i)+"','yyyy-MM-dd'),");
 73                     }else{
 74                         buf.append("'"+rs.getString(i)+"',");
 75                     }
 76                     
 77                     
 78                 }
 79                 //outPs.executeUpdate();
 80                 String ss = "insert into T_ZCGL_JLZCKP_NEW values("+buf.deleteCharAt(buf.length()-1)+",null, null, null);";
 81                 FileWriter out2 = new FileWriter("c:/T_ZCGL_JLZCKP_NEW.sql",true); 
 82                 out2.write(ss+"\n");
 83                 out2.flush();
 84                 out2.close();
 85 //                con.commit();
 86 //                con.setAutoCommit(true);
 87                 System.out.println("==========="+(num));
 88             }
 89         } catch (Exception e) {
 90             e.printStackTrace();
 91         }//
 92     }
 93     
 94     
 95     public void copy2(){
 96         Connection con = null;
 97         PreparedStatement inPs = null;
 98         PreparedStatement outPs = null;
 99         String QUERY_SQL = "select t2.cpbh,t2.fj1,t2.fj2,t2.fj3 from t_mx_attachment t,t_zcgl_jlzckp t2 where t.pk_val=t2.obj_id";
100         String INSERTSQL = "update t_zcgl_jlzckp_t set fj1=?,fj2=?,fj3=? where cpbh=?";
101         int num = 0;
102         try {
103             con = this.getCon();
104             con.setAutoCommit(false);
105             inPs = con.prepareStatement(QUERY_SQL);
106             outPs = con.prepareStatement(INSERTSQL);
107             ResultSet rs = inPs.executeQuery();
108             while(rs.next()){
109                 num++;
110                 // 字段顺序完全一致 不做列的判断
111                 outPs.setString(1, rs.getString("ATTACHMENT_ID"));
112                 outPs.setString(2, rs.getString("TABLE_NAME"));
113                 outPs.setString(3, rs.getString("PK_VAL"));
114                 outPs.setString(4, rs.getString("ATT_NAME"));
115                 outPs.setString(5, rs.getString("ATT_PATH"));
116                 outPs.setString(6, rs.getString("COL_NAME"));
117                 outPs.setString(7, rs.getString("ATT_SIZE"));
118                 
119                 byte[] bytes = new byte[1024];
120                 FileOutputStream out = new FileOutputStream("E:/mxuploadpath/"+rs.getString(4));
121                 int bytesIn = 0;
122                 BLOB blob = (BLOB) rs.getBlob("ATT_FILE");
123                 InputStream is = blob.getBinaryStream();
124                 while ((bytesIn = is.read(bytes, 0, 1024)) != -1) {
125                     out.write(bytes, 0, bytesIn);
126                 }
127                 is.close();
128                 out.close();
129                 
130                 // 读取本地图片
131                 File file = new File("E:/mxuploadpath/"+rs.getString(4));
132                 outPs.setBinaryStream(8, new FileInputStream(file), (int) file.length());
133                 outPs.execute();
134                 con.commit();
135                 con.setAutoCommit(true);
136                 System.out.println("==========="+(num));
137             }
138         } catch (Exception e) {
139             e.printStackTrace();
140         }//
141     }
142 }