我会走向何方

  1 package  com.fangq.excel2sql;
  2
  3 import  java.io.BufferedReader;
  4 import  java.io.BufferedWriter;
  5 import  java.io.FileNotFoundException;
  6 import  java.io.FileReader;
  7 import  java.io.FileWriter;
  8 import  java.io.IOException;
  9 import  java.io.LineNumberReader;
 10 import  java.sql.Connection;
 11 import  java.sql.DriverManager;
 12 import  java.sql.PreparedStatement;
 13 import  java.sql.ResultSet;
 14 import  java.sql.ResultSetMetaData;
 15 import  java.sql.SQLException;
 16 import  java.sql.Statement;
 17 import  java.util.ArrayList;
 18 import  java.util.List;
 19
 20 import  org.safehaus.uuid.UUID;
 21 import  org.safehaus.uuid.UUIDGenerator;
 22 /** */ /**
 23  * 
 24  *  @author  方强
 25  *
 26   */
 27 public   class  Test  {
 28      public   static   final  String MAJORSET = " gy_major_set_temp " ;
 29      public   static   final  String MAJOR = " gy_major " ;
 30      public   static   void  main(String[] args)  {
 31          //  TODO Auto-generated method stub
 32         majorSet();
 33         
 34
 35     }
 36      /** */ /**
 37      * 生成专业开设表的相关脚本和文档
 38      *
 39       */
 40      public   static   void  majorSet() {
 41         Connection conn  =   null ;
 42          try   {
 43             conn  =  connect();
 44              // excel转换后的txt文件(另存为:文本文件制表分隔符)
 45             FileReader fileR  =   new  FileReader( " E:\\code\\txt\\majorSet.txt " );
 46             String fileWS  =   " E:\\code\\sql\\majorSet.sql " ;
 47              // 生成的insert脚本文件
 48             FileWriter fileW  =   new  FileWriter(fileWS);
 49              // 删除此次实施数据的delete脚本
 50             FileWriter fileWD  =   new  FileWriter( " E:\\code\\sql\\majorSet_remove.sql " );
 51              // 违反非空约束的数据
 52             FileWriter fileWNull  =   new  FileWriter( " E:\\code\\sql\\majorSet_null.sql " );
 53              // 违反外键关联的数据
 54             FileWriter fileWFK  =   new  FileWriter( " E:\\code\\sql\\majorSet_FK.sql " );
 55             BufferedReader bufferedR  =   new  BufferedReader(fileR);
 56             BufferedWriter bufferedW  =   new  BufferedWriter(fileW);
 57             BufferedWriter bufferedWD  =   new  BufferedWriter(fileWD);
 58             BufferedWriter bufferedWNull  =   new  BufferedWriter(fileWNull);
 59             BufferedWriter bufferedWFK  =   new  BufferedWriter(fileWFK);
 60             String line = null ;
 61             String[] rec  = null ;
 62             List recs  =   new  ArrayList();
 63              while ((line = bufferedR.readLine()) != null ) {
 64                 line += "   " ;
 65                 rec  =  line.split( " \\t " );
 66                 recs.add(rec);
 67             }
 68              for ( int  i = 0 ;i < recs.size();i ++ ) {
 69                 String[] s  =  (String[])recs.get(i);
 70                  if (s[ 0 ] == null || s[ 0 ].trim().equals( "" )) {
 71                     String message  =   " 第 " + (i + 1 ) + " 行开设专业号为空\n " ;
 72                     bufferedWNull.write(message);
 73                 } else   if ( ! FK(MAJOR, " ZYH " ,s[ 0 ],conn)) {
 74                     String message  = " 专业开设号为: " + s[ 0 ] + " 在专业表中不存在相应的记录\n " ;
 75                     bufferedWFK.write(message);
 76                 } else {
 77                     
 78                     UUIDGenerator generator  =  UUIDGenerator.getInstance();
 79                     UUID uuid  =  generator.generateRandomBasedUUID(); 
 80                     String id  =  uuid.toString().replaceAll( " - " , "" );
 81                     String sql  =   " insert into  " + MAJORSET + "  (MAJOR_SET_ID, ZYH, ZYMC, KSNF, KSXQ, DEPARTMENT_ID, BMMC, YXBJ, XZ, XKML, JKZYBJ, XYGZYH, JWZYH, JWZYMC, YWMC) "   +
 82                              " values ( "   +
 83                              " ' " + id + " ', ' " + s[ 0 ].trim() + " ', ' " + s[ 1 ].trim() + " ', ' " + s[ 2 ].trim() + " ', ' " + s[ 3 ].trim() + " ', ' " + s[ 4 ].trim() + " ', ' " + s[ 5 ].trim() + " ', ' " + s[ 6 ].trim() + " ', ' " + s[ 7 ].trim() + " ', ' " + s[ 8 ].trim() + " ', ' " + s[ 9 ].trim() + " ', ' " + s[ 10 ].trim() + " ', ' " + s[ 11 ].trim() + " ', ' " + s[ 12 ].trim() + " ', ' " + s[ 13 ].trim().trim() + " ' " +
 84                              " );\n " ;
 85                     String sqlD  =   " delete from  " + MAJORSET + "  where MAJOR_SET_ID=' " + id + " ';\n " ;
 86                     bufferedW.write(sql);
 87                     bufferedWD.write(sqlD);
 88                 }
 89             }
 90             bufferedR.close();
 91             bufferedW.close();
 92             bufferedWD.close();
 93             bufferedWNull.close();
 94             bufferedWFK.close();
 95             runScript(fileWS,conn);
 96         }   catch  (Exception e)  {
 97              //  TODO Auto-generated catch block            
 98             e.printStackTrace();
 99         }   finally {
100              try {
101                  if (conn != null ) {
102                   conn.rollback();
103                   conn.close();
104                 }
105                 } catch (Exception e) {
106                     e.printStackTrace();
107                 }
108         }
109     }
110      /** */ /**
111      * 检查相应的外键是否在父表中是否存在
112      * 存在返回true
113      * 不存在返回false
114      *  @param  table
115      *  @param  col
116      *  @param  value
117      *  @param  conn
118      *  @return
119       */
120      public   static   boolean  FK(String table,String col,String value,Connection conn) {
121         PreparedStatement statement  =   null ;
122         ResultSet rs  =   null ;
123         StringBuffer sb  =   new  StringBuffer( "" );
124         sb.append( " select count(*) from  " );
125         sb.append(table);
126         sb.append( "  where  " );
127         sb.append(col);
128         sb.append( " =' " );
129         sb.append(value);
130         sb.append( " ' " );
131          try   {
132             statement  =  conn.prepareStatement(sb.toString());
133             rs  =  statement.executeQuery();
134              while (rs.next()) {
135              if (rs.getInt( 1 ) == 0 ) {
136                  return   false ;
137             }
138             }
139         }   catch  (SQLException e)  {
140              //  TODO Auto-generated catch block
141             e.printStackTrace();
142         } finally {
143              if (statement != null )
144                  try   {
145                     statement.close();
146                 }   catch  (SQLException e)  {
147                      //  TODO Auto-generated catch block
148                     e.printStackTrace();
149                 }
150              if (rs != null )
151                  try   {
152                     rs.close();
153                 }   catch  (SQLException e)  {
154                      //  TODO Auto-generated catch block
155                     e.printStackTrace();
156                 }
157         }
158          return   true ;
159     }
160      /** */ /**
161      * 获得数据库链接
162      *  @return
163       */
164      public   static  Connection connect() {
165         Connection c  =   null ;
166         String driver  =   " oracle.jdbc.driver.OracleDriver " ;
167         String url  =   " jdbc:oracle:thin:@192.168.1.111:1521:hitjw " ;
168         String userName  =   " hitjw " ;
169         String password  =   " hitjw " ;
170          try   {
171             Class.forName(driver).newInstance();
172         }   catch  (InstantiationException e)  {
173              //  TODO Auto-generated catch block
174             e.printStackTrace();
175         }   catch  (IllegalAccessException e)  {
176              //  TODO Auto-generated catch block
177             e.printStackTrace();
178         }   catch  (ClassNotFoundException e)  {
179              //  TODO Auto-generated catch block
180             e.printStackTrace();
181         }
182          try   {
183             c  =  DriverManager.getConnection(url,userName,password);
184             
185         }   catch  (SQLException e)  {
186              //  TODO Auto-generated catch block
187             e.printStackTrace();
188         }
189          return  c;
190     }
191      /** */ /**
192      * 执行脚本文件
193      *  @param  path
194      *  @param  conn
195       */
196      public   static   void  runScript(String path,Connection conn) {
197          // Connection conn = connect();
198         PreparedStatement statement  =   null ;
199          StringBuffer command  =   null ;
200              try   {
201                 FileReader fileR  =   new  FileReader(path);
202                 BufferedReader lineReader  =   new  BufferedReader(fileR);
203               String line  =   null ;
204                while  ((line  =  lineReader.readLine())  !=   null )  {               
205                   command  =   new  StringBuffer();                
206                 String trimmedLine  =  line.trim();
207                  if  (trimmedLine.startsWith( " -- " ))  {
208                   System.out.println(trimmedLine);
209                 }   else   if  (trimmedLine.length()  <   1   ||  trimmedLine.startsWith( " // " ))  {
210                    // Do nothing
211                 }   else   if  (trimmedLine.endsWith( " ; " ))  {
212                   command.append(line.substring( 0 , line.lastIndexOf( " ; " )));
213                    // command.append(" ");
214                   statement  =  conn.prepareStatement(command.toString());
215
216                    // System.out.println(command);                        
217                      try   {
218                       statement.execute();
219                     }   catch  (SQLException e)  {
220                       e.printStackTrace();                      
221                     }
222                 }
223               }
224               conn.commit();
225               lineReader.close();    
226             }   catch  (SQLException e)  {
227               e.printStackTrace();
228             }   catch  (IOException e)  {
229               e.printStackTrace();
230             }   finally   {
231                  try   {
232                      if (statement != null )
233                     statement.close();
234                 }   catch  (SQLException e)  {
235                      //  TODO Auto-generated catch block
236                     e.printStackTrace();
237                 }
238             }
239     }
240
241 }
242
243