KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > ejbca > util > SqlExecutor


1 /*************************************************************************
2  * *
3  * EJBCA: The OpenSource Certificate Authority *
4  * *
5  * This software is free software; you can redistribute it and/or *
6  * modify it under the terms of the GNU Lesser General Public *
7  * License as published by the Free Software Foundation; either *
8  * version 2.1 of the License, or any later version. *
9  * *
10  * See terms of license at gnu.org. *
11  * *
12  *************************************************************************/
13  
14 package org.ejbca.util;
15
16 import java.io.BufferedReader JavaDoc;
17 import java.io.File JavaDoc;
18 import java.io.FileNotFoundException JavaDoc;
19 import java.io.FileReader JavaDoc;
20 import java.io.IOException JavaDoc;
21 import java.io.Reader JavaDoc;
22 import java.sql.Connection JavaDoc;
23 import java.sql.SQLException JavaDoc;
24 import java.sql.Statement JavaDoc;
25 import java.sql.Timestamp JavaDoc;
26 import java.util.Iterator JavaDoc;
27 import java.util.LinkedList JavaDoc;
28 import java.util.List JavaDoc;
29
30 import org.apache.log4j.Logger;
31
32 /** Class to execute a file full of sql commands. Useful for running update scripts.
33  * @version $Id: SqlExecutor.java,v 1.2 2006/01/31 19:52:49 anatom Exp $
34  */
35 public class SqlExecutor {
36     static Logger log = Logger.getLogger(SqlExecutor.class);
37
38     private Connection JavaDoc con = null;
39     private int commands = 0;
40     private int errors = 0;
41     private boolean continueOnSqlError = false;
42     public Connection JavaDoc getConnection() {
43         return this.con;
44     }
45     public void setContinueOnSqlError(boolean cont) {
46         this.continueOnSqlError = cont;
47     }
48     public int getErrors() {
49         return this.errors;
50     }
51     /** Creates a new SqlExecutor. Caller is responsible for releasing the connection
52      * @param connection
53      * @param continueOnSQLError
54      */
55     public SqlExecutor(Connection JavaDoc connection, boolean continueOnSQLError) {
56         log.debug("> SqlExecutor(" + connection + "," + continueOnSQLError+ ")");
57         con = connection;
58 // try {
59 // con.setAutoCommit(false);
60 // } catch (SQLException ignore) {}
61 this.continueOnSqlError = continueOnSQLError;
62         log.debug("< SqlExecutor()");
63     }
64     
65     /** Runs a single sql update command
66      *
67      * @param command the sql command to execute
68      * @return the result returned from executeUpdate
69      * @throws SQLException
70      */
71     public int runCommand(String JavaDoc command) throws SQLException JavaDoc {
72         log.debug("> runCommand: " + command);
73         StringBuffer JavaDoc tmp = new StringBuffer JavaDoc(command);
74         int res = executeCommand(tmp);
75         log.debug(++commands + " commands executed with " + errors + " errors");
76         commands = 0;
77         errors = 0;
78         log.debug("< runCommand");
79         return res;
80     }
81     public void runCommandFile(File JavaDoc file) throws SQLException JavaDoc, FileNotFoundException JavaDoc, IOException JavaDoc {
82         log.debug("> runCommandFile: " + file.getPath());
83         Reader JavaDoc rdr = new FileReader JavaDoc(file);
84         runCommands(rdr);
85         log.debug("< runCommandFile()");
86     }
87     
88     /** Reads sql statements from the Reader object aqnd executes one statement at a time.
89      * Statements can be on one or more lines and must be terminated by ';'
90      *
91      * @param rdr Reader object to read commends from.
92      * @throws SQLException thrown on sql errors if continueOnSqlError is set to false.
93      * @throws IOException if the reader object is invalid.
94      */
95     public void runCommands(Reader JavaDoc rdr) throws SQLException JavaDoc, IOException JavaDoc {
96         log.debug(">runCommands");
97         BufferedReader JavaDoc br = new BufferedReader JavaDoc(rdr);
98         Timestamp JavaDoc start = new Timestamp JavaDoc(System.currentTimeMillis());
99         try {
100             String JavaDoc temp;
101             StringBuffer JavaDoc strBuf = new StringBuffer JavaDoc();
102             commands = 0;
103             List JavaDoc list = new LinkedList JavaDoc();
104             while ((temp = br.readLine()) != null) {
105                 if (!temp.startsWith("#")) { // Don't include comments
106 list.add(temp);
107                 }
108                 if (!temp.endsWith(";"))
109                     continue;
110             }
111             Iterator JavaDoc it = list.iterator();
112             while (it.hasNext()) {
113                 temp = (String JavaDoc) it.next();
114                 temp = temp.trim();
115                 if (temp.length() != 0) {
116                     strBuf.append(temp);
117                     if (temp.endsWith(";")) {
118                         // end of command, remove the ';' and execute
119 char ch = ' ';
120                         strBuf.setCharAt(strBuf.length() - 1, ch);
121                         executeCommand(strBuf);
122                         commands++;
123                         strBuf = new StringBuffer JavaDoc();
124                     } else {
125                         // continue to read the command
126 strBuf.append(" ");
127                     }
128                 }
129             }
130         } finally {
131             if (br != null) { br.close(); }
132         }
133         Timestamp JavaDoc stop = new Timestamp JavaDoc(System.currentTimeMillis());
134         log.debug("Execution started: " + start.toString());
135         log.debug("Execution stopped: " + stop.toString());
136         log.debug(commands + " commands executed with " + errors + " errors");
137         commands = 0;
138         errors = 0;
139         log.debug("<runCommands");
140     }
141     
142     /** Executes an sql update.
143      * SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.
144      *
145      * @param command StringBuffer holding the SQL.
146      * @return number of rows updates (returned by executeUpdate)
147      * @throws SQLException
148      */
149     private int executeCommand(StringBuffer JavaDoc command) throws SQLException JavaDoc {
150         log.debug("> executeCommand: " + command);
151         Statement JavaDoc stmt = null;
152         int res = 0;
153         try {
154             stmt = con.createStatement();
155             String JavaDoc sql = command.toString();
156             res = stmt.executeUpdate(sql);
157         } catch (SQLException JavaDoc exception) {
158             log.error("Exception: " + exception.getMessage() + ", sql: "+ command.toString());
159             if (!this.continueOnSqlError) {
160                 throw exception;
161             }
162             errors++;
163         } finally {
164             if (stmt != null) {
165                 stmt.close();
166             }
167         }
168         log.debug("< executeCommand");
169         return res;
170     } // executeCommand
171
172   /* commit and rollback commands not needed when running inside a session bean that handles transactions for us */
173   /*
174   public void commit() throws SQLException {
175         log.debug("> commit");
176         if (con != null)
177             con.commit();
178         else {
179             log.error("Connection == null vid commit()");
180         }
181         log.debug("< commit");
182     }
183     
184     public void rollback() throws SQLException {
185         log.debug("> rollback");
186         if (con != null)
187             con.rollback();
188         else {
189             log.error("Connection == null vid rollback()");
190         }
191         log.debug("< rollback");
192     }
193     */
194 }
195
Popular Tags