001: /******************************************************************************
002: * Copyright (C) Lars Ivar Almli. All rights reserved. *
003: * ---------------------------------------------------------------------------*
004: * This file is part of MActor. *
005: * *
006: * MActor is free software; you can redistribute it and/or modify *
007: * it under the terms of the GNU General Public License as published by *
008: * the Free Software Foundation; either version 2 of the License, or *
009: * (at your option) any later version. *
010: * *
011: * MActor is distributed in the hope that it will be useful, *
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of *
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
014: * GNU General Public License for more details. *
015: * *
016: * You should have received a copy of the GNU General Public License *
017: * along with MActor; if not, write to the Free Software *
018: * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA *
019: ******************************************************************************/package org.mactor.framework.commandexecutors.sql;
020:
021: import java.io.BufferedReader;
022: import java.io.File;
023: import java.io.FileInputStream;
024: import java.io.IOException;
025: import java.io.InputStreamReader;
026: import java.sql.Connection;
027: import java.sql.DriverManager;
028: import java.sql.ResultSet;
029: import java.sql.ResultSetMetaData;
030: import java.sql.SQLException;
031: import java.sql.Statement;
032: import org.apache.log4j.Logger;
033: import org.mactor.framework.MactorException;
034:
035: public class JdbcUtil {
036: private static Logger log = Logger.getLogger(JdbcUtil.class);
037: private String driver;
038: private String url;
039: private String username;
040: private String password;
041:
042: public JdbcUtil(String driver, String url, String username,
043: String password) {
044: this .driver = driver;
045: this .url = url;
046: this .username = username;
047: this .password = password;
048: }
049:
050: private Connection getConnection() throws MactorException {
051: java.util.Properties props = new java.util.Properties();
052: props.put("user", username);
053: props.put("password", password);
054: // props.put("protocol", "thin");
055: try {
056: if (driver != null) {
057: java.sql.Driver d = (java.sql.Driver) Class.forName(
058: driver).newInstance();
059: java.sql.Connection conn = d.connect(url, props);
060: return conn;
061: } else {
062: return DriverManager.getConnection(url, props);
063: }
064: } catch (ClassNotFoundException cfn) {
065: throw new MactorException(
066: "The specified JDBC driver class '" + driver
067: + "'was not found in the classpath");
068: } catch (InstantiationException ie) {
069: throw new MactorException(
070: "Unable to instantiate the specified JDBC driver class '"
071: + driver + "'", ie);
072: } catch (IllegalAccessException iae) {
073: throw new MactorException(
074: "Unable to instantiate the specified JDBC driver class '"
075: + driver + "'", iae);
076: } catch (SQLException sqle) {
077: throw new MactorException(
078: "Unable to connect to the specfied db-url '" + url
079: + "'. Error: " + sqle.getMessage(), sqle);
080: }
081: }
082:
083: public String execScriptQuery(String filename)
084: throws MactorException {
085: File file = new File(filename);
086: if (!file.exists())
087: throw new MactorException("The specifed sql script file '"
088: + file.getAbsolutePath() + "' does not exist");
089: try {
090: BufferedReader br = new BufferedReader(
091: new InputStreamReader(new FileInputStream(file)));
092: StringBuffer command = new StringBuffer();
093: String line = br.readLine();
094: while (line != null) {
095: command.append(line).append("\n");
096: line = br.readLine();
097: }
098: return execQuerySql(command.toString());
099: } catch (IOException ioe) {
100: throw new MactorException(
101: "IO exception while reading the sql script file '"
102: + file.getAbsolutePath() + "'. Error: "
103: + ioe.getMessage(), ioe);
104: }
105: }
106:
107: public void execScript(String filename, boolean ignoreSqlException)
108: throws MactorException {
109: File file = new File(filename);
110: if (!file.exists())
111: throw new MactorException("The specifed sql script file '"
112: + file.getAbsolutePath() + "' does not exist");
113: try {
114: BufferedReader br = new BufferedReader(
115: new InputStreamReader(new FileInputStream(file)));
116: String line = br.readLine();
117: StringBuffer command = new StringBuffer();
118: boolean inBlock = false;
119: Connection conn = null;
120: try {
121: conn = getConnection();
122: while (line != null) {
123: line = line.trim();
124: if (line.length() > 0) {
125: if (line.equals("/") && inBlock) {
126: inBlock = false;
127: execUpdate(conn, command.toString(),
128: ignoreSqlException);
129: command = new StringBuffer();
130: } else if (line.equals("--/")) {
131: inBlock = true;
132: } else if (inBlock) {
133: command.append(line).append("\n");
134: } else {
135: if (line.endsWith(";")) {
136: command.append(
137: line.substring(0,
138: line.length() - 1))
139: .append("\n");
140: execUpdate(conn, command.toString(),
141: ignoreSqlException);
142: command = new StringBuffer();
143: } else {
144: command.append(line).append("\n");
145: }
146: }
147: }
148: line = br.readLine();
149: }
150: } finally {
151: close(conn);
152: }
153: } catch (IOException ioe) {
154: throw new MactorException(
155: "IO exception while reading the sql script file '"
156: + file.getAbsolutePath() + "'. Error: "
157: + ioe.getMessage(), ioe);
158: }
159: }
160:
161: public void execUpdateSql(String sql) throws MactorException {
162: Connection conn = getConnection();
163: try {
164: execUpdate(conn, sql, false);
165: } finally {
166: close(conn);
167: }
168: }
169:
170: public String execQuerySql(String sql) throws MactorException {
171: log.debug("Executing query:" + sql);
172: Connection conn = getConnection();
173: try {
174: return execQuery(conn, sql);
175: } catch (SQLException sqle) {
176: throw new MactorException("Failed to execute the query '"
177: + sql + "'. Error: " + sqle.getMessage(), sqle);
178: } finally {
179: close(conn);
180: }
181: }
182:
183: private void execUpdate(Connection conn, String sql,
184: boolean ignoreSqlException) throws MactorException {
185: log.debug("Executing update:" + sql);
186: Statement s = null;
187: try {
188: s = conn.createStatement();
189: s.executeUpdate(sql);
190: close(s);
191: } catch (SQLException sqle) {
192: MactorException me = new MactorException(
193: "Failed to execute the update '" + sql
194: + "'. Error: " + sqle.getMessage(), sqle);
195: if (!ignoreSqlException)
196: throw me;
197: log.info("Ignoring:" + me.getMessage(), sqle);
198: } finally {
199: close(s);
200: }
201: }
202:
203: private void close(Connection s) {
204: if (s != null) {
205: try {
206: s.close();
207: } catch (SQLException _) {
208: }
209: }
210: }
211:
212: private void close(Statement s) {
213: if (s != null) {
214: try {
215: s.close();
216: } catch (SQLException _) {
217: }
218: }
219: }
220:
221: private void close(ResultSet s) {
222: if (s != null) {
223: try {
224: s.close();
225: } catch (SQLException _) {
226: }
227: }
228: }
229:
230: private String execQuery(Connection conn, String sql)
231: throws SQLException {
232: Statement s = null;
233: ResultSet rs = null;
234: try {
235: s = conn.createStatement();
236: rs = s.executeQuery(sql);
237: if (!rs.next()) {
238: log.info("no rows");
239: return null;
240: } else {
241: ResultSetMetaData md = rs.getMetaData();
242: if (md.getColumnCount() > 0) {
243: return rs.getString(1);
244: } else {
245: log.info("no colums");
246: }
247: }
248: return null;
249: } finally {
250: close(rs);
251: close(s);
252: }
253: }
254: }
|