我會走向何方

??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