|
1 import java.io.*;
2 import java.util.*;
3 import java.sql.*;
4
5
9
11
17 public class JSQLexec
18 {
20 public String  dbtype = null;
21 public String dbname = null;
22 public String dbserver = null;
23 public int dbport = 0;
24 public String dbportStr;
25 public String dbuser = null;
26 public String dbpasswd = null;
27 Vector files = new Vector();
28 String replPatternsFile = null;
29
30 private Connection con;
31 private String dbURL;
32 private Statement stmt;
33 private boolean generateFile = false;
34 private String fileName;
35 private File sqlFile = null;
36 private PrintWriter fWriter = null;
37 private int numFailedCmds = 0;
38 private int numCmds = 0;
39 private int sqlErrorAction;
40 private boolean quietExec;
41 private int indexDbType = 0;
42 private Vector replacePatterns = new Vector();
43
44 private static boolean isPostgresql( String dbtype ) { return dbtype.toLowerCase().startsWith("postgresql"); }
46 private static boolean isOracle( String dbtype ) { return dbtype.toLowerCase().startsWith("oracle"); }
47 private static boolean isSybase( String dbtype ) { return dbtype.toLowerCase().startsWith("sybase"); }
48
50
51
52 public int start( String [] args ) {
54 files = new Vector();
56
57 String dbportStr = "0";
58 int sqlErrorAction = 0;
59 boolean quietExec = false;
60
61 if ( args.length < 1 ) { showUsage(); System.exit( 0 ); }
62
63 int l = 0;
64 try {
65 while ( l < args.length ) {
66
67 if ( args[l].equals("-U") ) dbuser = args[l + 1];
69 else if ( args[l].equals("-P") ) dbpasswd = args[l + 1];
70 else if ( args[l].equals("-t") ) dbtype = args[l + 1];
71 else if ( args[l].equals("-r") ) replPatternsFile = args[l + 1];
72 else if ( args[l].equals("-s") ) dbserver = args[l + 1];
73 else if ( args[l].equals("-p") ) dbportStr = args[l + 1];
74 else if ( args[l].equals("-d") ) dbname = args[l + 1];
75 else if ( args[l].equals("-f") )
76 {
77 generateFile = true;
78 fileName = args[l + 1];
79 }
80 else if ( args[l].equals("-w") ) { try {
82 Properties dbp = new Properties();
83 dbp.load( new FileInputStream( args[l+1] ) );
84 dbtype = dbp.getProperty("DATABASE");
85 dbuser = dbp.getProperty("USER");
86 dbpasswd = dbp.getProperty("PASSWD");
87 StringTokenizer st = new StringTokenizer( dbp.getProperty("HOST"), ":");
88 if ( st.hasMoreTokens() ) dbserver = st.nextToken();
89 if ( isPostgresql( dbtype ) ) {
90 if ( st.hasMoreTokens() ) dbportStr = st.nextToken("/").substring(1);
91 if ( st.hasMoreTokens() ) dbname = st.nextToken(":").substring(1);
92 }
93 else {
94 if ( st.hasMoreTokens() ) dbportStr = st.nextToken();
95 if ( st.hasMoreTokens() ) dbname = st.nextToken();
96 }
97 }
98 catch ( IOException e ) {
99 System.err.println( "! Could not find file: " + args[l+1] );
100 System.exit( 1 );
101 }
102 }
103
104 else {
105
106 if ( args[l].equals("-h")) { showUsage(); System.exit( 0 ); }
108 else if (args[l].equals("-q")) quietExec = true;
109 else if (args[l].equals("-e")) sqlErrorAction = 2;
110 else if (args[l].equals("-i")) sqlErrorAction = 1;
111 else {
112 if ( args[l].startsWith("-") ) {
113 System.err.println("! Unknown option '" + args[l] + "'.");
114 System.exit( 1 );
115 }
116
117 files.add(args[l]);
118 }
119 l--;
120 }
121 l+=2;
122 }
123 }
124 catch ( ArrayIndexOutOfBoundsException aob ) {
125 System.err.println( "! Missing argument. Try '-h' for help.");
126 System.exit( 1 );
127 }
128
129 return
130 start(
131
132 );
133 }
134
135
136 public int start( String args ) {
138 String [] argList = new String [] {""};
140 try {
141 Vector tokens = new Vector();
142 StreamTokenizer st = new StreamTokenizer( new StringReader( args));
143 st.resetSyntax(); st.whitespaceChars(0, ' ');
145 st.wordChars('\u0021', '\u00FF');
146 st.quoteChar('\'');
147 st.quoteChar('"');
148 st.eolIsSignificant(false);
149
150 while( st.nextToken() != StreamTokenizer.TT_EOF) {
151 switch( st.ttype) {
152 case '"':
153 case '\'':
154 case StreamTokenizer.TT_WORD: tokens.add( st.sval);
155 break;
156 }
157 }
158 argList = new String [ tokens.size() ];
159 for( int i = tokens.size(); i >= 0; i-- ) argList[i-1] = (String ) tokens.elementAt(i-1);
160
161 }
162 catch ( IOException e ) { System.err.println( "! " + e.getMessage()); System.exit(9); }
163 return start( argList );
164 }
165
166
167 public int start() {
169 try {
171 dbport = 0;
172 if (!generateFile)
173 {
174 try {
175 dbport = Integer.parseInt( dbportStr );
176 if ( (dbport > 65535) || (dbport < 1024) ) throw new NumberFormatException ("number out of [1024,65535]");
177 }
178 catch ( NumberFormatException e ) {
179 System.err.println("! Invalid port number '" + dbportStr + "' (" + e.getMessage() + ")");
180 System.exit( 1 );
181 }
182 }
183 dbtype = dbtype.toLowerCase();
184 int tryDbType = 0;
185 try {
186 tryDbType++;
187 if ( isOracle( dbtype ) ) {
188 if (!generateFile)
189 Class.forName("oracle.jdbc.driver.OracleDriver");
190 dbURL = "jdbc:oracle:thin:@" + dbserver + ":" + dbport + ":" + dbname;
191 indexDbType = tryDbType;
192 }
193 tryDbType++;
194 if ( isPostgresql( dbtype ) ) {
195 if (!generateFile)
196 Class.forName("org.postgresql.Driver");
197 dbURL = "jdbc:postgresql://" + dbserver + ":" + dbport + "/" + dbname;
198 indexDbType = tryDbType;
199 }
200 tryDbType++;
201 if ( isSybase( dbtype ) ) {
202 dbname = null;
203 if (!generateFile)
204 Class.forName("com.sybase.jdbc.SybDriver");
205 dbURL = "jdbc:sybase:Tds:" + dbserver + ":" + dbport;
207 indexDbType = tryDbType;
208 }
209 if ( indexDbType < 1 ) {
210 System.err.println("! Invalid db type.");
211 System.exit( 1 );
212 }
213 if (!generateFile)
214 {
215 con = DriverManager.getConnection( dbURL, dbuser, dbpasswd );
216 con.setAutoCommit( false );
217 }
218 }
219 catch ( Exception e ) {
220 System.out.println(e);
221 if ( dbtype == null ) System.err.println( "! Database of type: " + dbtype);
222 System.err.println( e.getMessage() + "\n" + "! No database connection.");
223 System.exit( 1 );
224 }
225 System.out.println("* Connected (dbtype="+dbtype+", user="+dbuser+", server="+dbserver+", port="+dbport+(dbname!=null ? ", db="+dbname : "")+"; autoCommit=false).");
226
227 if (generateFile)
228 {
229 System.out.println("Generating SQL File ...");
230 sqlFile = new File(fileName);
231 fWriter = new PrintWriter(new BufferedWriter(new FileWriter(sqlFile)));
232 if (isPostgresql( dbtype ))
233 fWriter.println("BEGIN;");
234 }
235
236 loadReplacementPatterns( replPatternsFile );
237
238 Iterator it = files.iterator();
239 while ( it.hasNext() )
240 execJsqlFile(
241 ((String ) it.next()).replace( '/', File.separatorChar ).replace( '\\', File.separatorChar ), fWriter
242 );
243
244
245 if (con != null)
246 con.close();
247 if (fWriter != null)
248 {
249 if (isPostgresql( dbtype ))
250 {
251 fWriter.println("COMMIT;");
252 }
253 fWriter.close();
254 }
255 System.out.println(
256 "* Disconnected (" + numFailedCmds +
257 " command"+ (numFailedCmds==1?"":"s") + " failed out of " + numCmds+ ")."
258 );
259 }
260 catch ( Exception e ) {
261 System.err.println( "! " + e );
262 e.printStackTrace();
263 System.exit( 1 );
264 }
265 return numFailedCmds;
266 }
267
268
269
270 private void execJsqlFile( String fname, PrintWriter fWriter) throws IOException
271 {
272 LineNumberReader in = null;
273 System.out.println("* Executing " + fname);
274 try {
275 in = new LineNumberReader( new FileReader( fname ));
276 String line = null;
277 String tline = null;
278 boolean inComment = false;
279 boolean isComment;
280 int numCmdsFailed = 0;
281
282 String cmd = "";
283 while ((line = in.readLine()) != null) {
284 tline = line.trim().toUpperCase();
285 if ( tline.startsWith("//") ) continue;
286 isComment = inComment || tline.startsWith("/*");
287 inComment = isComment && (! tline.endsWith("*/"));
288 if ( isComment ) continue;
289 if ( tline.equals("COMMIT") ) {
290 execJsqlCmd( cmd, in.getLineNumber(), fname, fWriter );
291 cmd = "";
292 if (! quietExec )
293 System.out.println("* COMMIT, line " + in.getLineNumber() + " of " + fname );
294 if (!generateFile)
295 con.commit();
296 continue;
297 }
298 if ( tline.length() < 1 ) {
299 execJsqlCmd( cmd, in.getLineNumber(), fname, fWriter );
300 cmd = "";
301 continue;
302 }
303 cmd += (line + "\n");
304 }
305 execJsqlCmd( cmd, in.getLineNumber(), fname, fWriter);
306 cmd = "";
307 }
308 catch ( FileNotFoundException e ) {
309 System.err.println( "! Could not find file: " + fname);
310 System.exit( 2 );
311 }
312 catch ( IOException e ) {
313 System.err.println( "! I/O-Error while reading:" + fname);
314 System.exit( 2 );
315 }
316 catch ( SQLException e ) {
317 System.err.println( "! " + e.getMessage() );
318 System.exit( 2 );
319 }
320 finally {
321 try { in.close(); } catch ( IOException e ) { }
322 }
323 }
324
325
326
327 private void execJsqlCmd( String cmd , int linenumber, String fname, PrintWriter writer)
328 {
329 try {
330 String fstr, rstr;
331 int p;
332 Enumeration re = replacePatterns.elements();
333 while( re.hasMoreElements() ) {
334 fstr = (String ) ( re.nextElement() );
335 rstr = (String ) ( re.nextElement() );
336 while ( (p = cmd.indexOf( fstr)) >= 0 ) {
337 cmd = cmd.substring( 0, p ) + rstr + cmd.substring( p + fstr.length() );
338 }
339 }
340 if ( cmd.length() < 1 ) return;
341
342 numCmds++;
343
344 if (! quietExec ) {
345 System.out.println("________________________________");
346 System.out.print( cmd );
347 System.out.println("_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _");
348 }
349 if (generateFile)
350 {
351 if (isPostgresql( dbtype ))
352 writer.println(cmd + ";");
353 else
354 writer.println(cmd);
355 }
356 else
357 {
358 if ( stmt == null) stmt = con.createStatement();
359 boolean hasResult = stmt.execute( cmd );
360 if ( quietExec ) return;
361 if ( hasResult ) {
362 ResultSet rs = stmt.getResultSet();
363 int z = rs.getMetaData().getColumnCount();
364 while( rs.next() ) {
365 for( int i = 1; i <= z; i++) {
366 if (i < z)
367 System.out.print( rs.getString( i) + ", ");
368 else
369 System.out.println( rs.getString( i));
370 }
371 }
372 }
373 else {
374 System.out.println("* Affected rows: "+ stmt.getUpdateCount());
375 }
376 }
377 }
378 catch ( SQLException e ) {
379 numFailedCmds++;
380 if ( (! quietExec) || (sqlErrorAction > 0) ) {
381 System.err.println( "! SQLException while reading line " + linenumber + " of " + fname);
382 System.err.println( "! " + e.getMessage() );
383 }
384 if ( sqlErrorAction > 1 ) System.exit( 3 );
385 if ( sqlErrorAction > 0 )
386 try { System.out.println("-- press <enter> to continue"); System.in.read(); }
387 catch ( IOException ioe ) {} ;
388 }
389 }
390
391
392 public void loadReplacementPatterns( String fname ) {
394
396 if ( fname == null ) return;
397
398 LineNumberReader in = null;
399 String line;
400
401 try {
402 in = new LineNumberReader( new FileReader( fname ));
403 while ((line = in.readLine()) != null) {
404 if ( line.startsWith("#") ) continue;
405 StreamTokenizer st = new StreamTokenizer( new StringReader( line));
406 st.resetSyntax(); st.whitespaceChars(0, ' ');
408 st.wordChars('\u0021', '\u00FF');
409 st.quoteChar('\'');
410 st.quoteChar('"');
411 st.eolIsSignificant(false);
412 Vector replVec = new Vector();
413 String findStr = null;
414 int indexOfPattern = 0;
415
416 while( st.nextToken() != StreamTokenizer.TT_EOF) {
417 switch( st.ttype) {
418 case '"':
419 case '\'':
420 case StreamTokenizer.TT_WORD:
421 if (findStr == null) {
422 findStr = st.sval; indexOfPattern = 0;
424 }
425 else {
426 indexOfPattern++;
427 if ( indexOfPattern == indexDbType ) {
428 replacePatterns.addElement( findStr);
429 replacePatterns.addElement( st.sval);
430 }
431 }
432 break;
433 default:
434 System.err.println(
435 "- Warning. (internal) token type: " + st.ttype +
436 " while parsing replacement table. Continuing."
437 );
438 break;
439 }
440 }
441
442 }
443 int numPatterns = replacePatterns.size() / 2;
444 System.out.println( "* " + numPatterns + " replacement pattern" + (numPatterns==1?"":"s") + " loaded.");
445 }
446 catch ( FileNotFoundException e ) {
447 System.err.println( "! Could not find file: " + fname);
448 System.exit( 2 );
449 }
450 catch ( IOException e) {
451 System.err.println("! IOException while parsing replacements list.");
452 System.err.println( "! " + e.getMessage());
453 System.exit( 2 );
454 }
455 finally {
456 try { in.close(); } catch ( IOException e ) { }
457 }
458 }
459
460
461 public static void main( String [] args) {
463 new JSQLexec().start( args );
465 }
466
467
468 private static void showUsage() {
470 System.out.println("\nExecute sequences of (j)sql commands given in files");
472 System.out.println("and delimited by empty lines or 'commit'-commands.");
473 System.out.println("java JSQLexec [options] <files ...>");
474 System.out.println("");
475 System.out.println("options:");
476 System.out.println(" -t TYPE database type, see below");
477 System.out.println(" -U NAME username for db connection");
478 System.out.println(" -P PASSWD password for db connection");
479 System.out.println(" -s SERVER db server address");
480 System.out.println(" -p PORT db server port");
481 System.out.println(" -w FILE load webmandb.ini data");
482 System.out.println(" -d NAME db name, if required (see below)");
483 System.out.println(" -r FILE load replacement patterns for commands from FILE");
484 System.out.println(" -q quiet, do not show commands and results");
485 System.out.println(" -e exit after first failing SQL command");
486 System.out.println(" -i pause after each failing SQL command");
487 System.out.println(" -f generate SQL File - no database activity");
488 System.out.println(" -h help");
489 System.out.println("");
490 System.out.println("Supported db types: (* if database name required)");
491 System.out.println(" * oracle");
492 System.out.println(" * postgresql");
493 System.out.println(" sybase");
494 System.out.println("");
495 System.out.println("JSQL syntax:");
496 System.out.println("- commands terminated by empty line or by line with single");
497 System.out.println(" word 'commit'");
498 System.out.println("- lines beginning with '//' are skipped");
499 System.out.println("- multiline comments also within '/*' and '*/', both recognized");
500 System.out.println(" literally(!) even across quoted elements (strings,...)");
501 System.out.println("- hint: replace \" by ' when making jsql from sql");
502 System.out.println("Replacement table syntax:");
503 System.out.println("- comments have '#' in first column");
504 System.out.println("- replacement definitions each span 1 line with");
505 System.out.println(" 1st word = string to find, 2nd = replacement for oracle,");
506 System.out.println(" 3rd for postgresql, 4th for sybase");
507 System.out.println("- words can be delimited using \" or ', with usual escaping");
508 System.out.println(" via '\\'; \"\" is the empty string");
509 System.out.println("");
510 System.out.println("");
511 }
512
513 }
514 |
Popular Tags |
Java API By Example, From Geeks To Geeks.