001: /*
002: DBPool - JDBC Connection Pool Manager
003: Copyright (c) Giles Winstanley
004: */
005: package snaq.db;
006:
007: import java.io.*;
008: import java.sql.*;
009: import java.util.StringTokenizer;
010:
011: /**
012: * Command-line utility to send SQL commands to a database.
013: * This class is useful for easily creating a large number of database tables
014: * and/or records from a user-defined file.
015: * It relies on the <tt>ConnectionPoolManager</tt> class to assist
016: * with the creation of a connection to the database, which in turn requires
017: * the appropriate <tt>dbpool.properties</tt> file in the classpath.
018: * <pre>
019: * Usage: java snaq.db.SQLUpdate <pool> <input file> [<separator>]
020: * </pre>
021: * where <tt>pool</tt> is the name of the connection pool as defined in
022: * the dbpool.properties file, <tt>input file</tt> is the name of the text
023: * file containing the SQL statements to be issued to the defined database,
024: * and <tt>separator</tt> is an optional parameter to specify a delimiter
025: * for the SQL statements in the file. If the separator is not specified then
026: * each line of the file is assumed to be a separate statement.
027: * <p>(Note: comments are allowed in the input file by starting the line with
028: * either # or --).
029: * @see snaq.db.ConnectionPoolManager
030: * @author Giles Winstanley
031: */
032: public class SQLUpdate {
033: private ConnectionPoolManager cpm;
034: private Connection con;
035: private Statement statement;
036: private String dbName;
037:
038: private ByteArrayOutputStream logBuffer;
039: private PrintWriter log;
040:
041: public SQLUpdate(String db) throws IOException {
042: dbName = db;
043: cpm = ConnectionPoolManager.getInstance();
044: }
045:
046: /**
047: * Opens the database connection.
048: */
049: private void openConnection(String s) throws SQLException {
050: if (s == null || s.equals("")) {
051: System.out.println("Please specify a database name");
052: System.exit(1);
053: }
054: con = cpm.getConnection(s);
055: try {
056: statement = con.createStatement();
057: } catch (SQLException e) {
058: }
059: logBuffer = new ByteArrayOutputStream();
060: log = new PrintWriter(logBuffer);
061: }
062:
063: /**
064: * Closes the database connection.
065: */
066: private void closeConnection() {
067: try {
068: statement.close();
069: con.close();
070: } catch (SQLException e) {
071: }
072:
073: // Output errors to log file
074: log.flush();
075: log.close();
076: if (logBuffer.size() > 0) {
077: try {
078: FileOutputStream fos = new FileOutputStream(
079: "SQLUpdate.log", true);
080: fos.write(logBuffer.toByteArray());
081: fos.flush();
082: fos.close();
083: } catch (IOException ioe) {
084: }
085: }
086: cpm.release();
087: }
088:
089: /**
090: * Issues a statement to the database.
091: */
092: private void doStatement(String sql) {
093: int count = 0;
094: try {
095: count = statement.executeUpdate(sql);
096: System.out.print(".");
097: } catch (SQLException sqle) {
098: System.out.print("x");
099: log.println();
100: log.println(sql);
101: log.println(sqle.getMessage());
102: // sqle.printStackTrace(log);
103: }
104: }
105:
106: public static void main(String args[]) throws Exception {
107: if (args == null || args.length < 2) {
108: System.out
109: .println("Usage: java snaq.db.SQLUpdate <database> <text file> [<separator>]");
110: System.exit(0);
111: }
112:
113: String db = args[0];
114: String file = args[1];
115: String separator = args.length < 3 ? null : args[2];
116:
117: // Load file
118: String contents = null;
119: try {
120: ByteArrayOutputStream bao = new ByteArrayOutputStream();
121: FileInputStream fis = new FileInputStream(file);
122: byte[] b = new byte[4096];
123: int n;
124: while ((n = fis.read(b)) != -1)
125: bao.write(b, 0, n);
126: fis.close();
127: contents = new String(bao.toByteArray());
128: } catch (IOException ioe) {
129: System.out.println("I/O error with file " + file);
130: System.exit(1);
131: }
132:
133: // Open a database connection
134: SQLUpdate sql = null;
135: try {
136: sql = new SQLUpdate(db);
137: } catch (IOException ioe) {
138: System.err
139: .println("Unable to create instance of SQLUpdate");
140: ioe.printStackTrace();
141: System.exit(1);
142: }
143: sql.openConnection(db);
144:
145: if (separator == null) {
146: StringTokenizer st = new StringTokenizer(contents, "\n\r");
147: while (st.hasMoreTokens()) {
148: String token = st.nextToken().trim();
149: if (!token.startsWith("#") && !token.equals(""))
150: sql.doStatement(token);
151: }
152: } else {
153: System.out.println("Separator: " + separator);
154:
155: StringBuffer sb = new StringBuffer();
156: StringTokenizer st = new StringTokenizer(contents, "\n\r");
157: while (st.hasMoreTokens()) {
158: // Get next line
159: String line = st.nextToken();
160:
161: // If line is a comment...ignore it
162: if (line.startsWith("#") || line.startsWith("--")) {
163: sb.setLength(0);
164: } else {
165: int pos = line.indexOf(separator);
166: if (pos >= 0) {
167: sb.append(line.substring(0, pos));
168: sql.doStatement(sb.toString());
169: sb.setLength(0);
170: } else
171: sb.append(line);
172: }
173: }
174: }
175:
176: sql.closeConnection();
177: System.out.println();
178: }
179: }
|