001 /******************************************************************************
002 * Copyright (C) MActor Developers. 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 ******************************************************************************/
020 package org.mactor.framework.data.jdbc;
021
022 import java.io.BufferedReader;
023 import java.io.File;
024 import java.io.FileInputStream;
025 import java.io.IOException;
026 import java.io.InputStreamReader;
027 import java.sql.Connection;
028 import java.sql.DriverManager;
029 import java.sql.ResultSet;
030 import java.sql.ResultSetMetaData;
031 import java.sql.SQLException;
032 import java.sql.Statement;
033
034 import org.apache.log4j.Logger;
035 import org.mactor.framework.MactorException;
036 import org.mactor.framework.data.DataTable;
037
038 public class JdbcUtil {
039 private static Logger log = Logger.getLogger(JdbcUtil.class);
040 private String driver;
041 private String url;
042 private String username;
043 private String password;
044 public JdbcUtil(String driver, String url, String username, String password) {
045 this.driver = driver;
046 this.url = url;
047 this.username = username;
048 this.password = password;
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(driver).newInstance();
058 java.sql.Connection conn = d.connect(url, props);
059 return conn;
060 } else {
061 return DriverManager.getConnection(url, props);
062 }
063 } catch (ClassNotFoundException cfn) {
064 throw new MactorException("The specified JDBC driver class '" + driver + "'was not found in the classpath");
065 } catch (InstantiationException ie) {
066 throw new MactorException("Unable to instantiate the specified JDBC driver class '" + driver + "'", ie);
067 } catch (IllegalAccessException iae) {
068 throw new MactorException("Unable to instantiate the specified JDBC driver class '" + driver + "'", iae);
069 } catch (SQLException sqle) {
070 throw new MactorException("Unable to connect to the specfied db-url '" + url + "'. Error: " + sqle.getMessage(), sqle);
071 }
072 }
073 public String execSingleCellScriptQuery(File file) throws MactorException {
074 return execSingleCellQuerySql(getExpressionFromFile(file));
075 }
076 private String getExpressionFromFile(File file) throws MactorException {
077 if (!file.exists())
078 throw new MactorException("The specifed sql script file '" + file.getAbsolutePath() + "' does not exist");
079 try {
080 BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file)));
081 StringBuffer command = new StringBuffer();
082 String line = br.readLine();
083 while (line != null) {
084 command.append(line).append("\n");
085 line = br.readLine();
086 }
087 return command.toString();
088 } catch (IOException ioe) {
089 throw new MactorException("IO exception while reading the sql script file '" + file.getAbsolutePath() + "'. Error: " + ioe.getMessage(), ioe);
090 }
091 }
092 public DataTable execQuerySql(String sql) throws MactorException {
093 Connection conn = getConnection();
094 try {
095 return execQuery(conn, sql);
096 } catch (SQLException sqle) {
097 throw new MactorException("Failed to execute the query '" + sql + "'. Error: " + sqle.getMessage(), sqle);
098 } finally {
099 close(conn);
100 }
101 }
102 public DataTable execScriptQuery(File file) throws MactorException {
103 return execQuerySql(getExpressionFromFile(file));
104 }
105 public void execScript(File file, boolean ignoreSqlException) throws MactorException {
106 if (!file.exists())
107 throw new MactorException("The specifed sql script file '" + file.getAbsolutePath() + "' does not exist");
108 try {
109 BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file)));
110 String line = br.readLine();
111 StringBuffer command = new StringBuffer();
112 boolean inBlock = false;
113 Connection conn = null;
114 try {
115 conn = getConnection();
116 while (line != null) {
117 line = line.trim();
118 if (line.length() > 0) {
119 if (line.equals("/") && inBlock) {
120 inBlock = false;
121 execUpdate(conn, command.toString(), ignoreSqlException);
122 command = new StringBuffer();
123 } else if (line.equals("--/")) {
124 inBlock = true;
125 } else if (inBlock) {
126 command.append(line).append("\n");
127 } else {
128 if (line.endsWith(";")) {
129 command.append(line.substring(0, line.length() - 1)).append("\n");
130 execUpdate(conn, command.toString(), ignoreSqlException);
131 command = new StringBuffer();
132 } else {
133 command.append(line).append("\n");
134 }
135 }
136 }
137 line = br.readLine();
138 }
139 } finally {
140 close(conn);
141 }
142 } catch (IOException ioe) {
143 throw new MactorException("IO exception while reading the sql script file '" + file.getAbsolutePath() + "'. Error: " + ioe.getMessage(), ioe);
144 }
145 }
146 public void execUpdateSql(String sql) throws MactorException {
147 Connection conn = getConnection();
148 try {
149 execUpdate(conn, sql, false);
150 } finally {
151 close(conn);
152 }
153 }
154 public String execSingleCellQuerySql(String sql) throws MactorException {
155 log.debug("Executing query:" + sql);
156 Connection conn = getConnection();
157 try {
158 return execSingleCellQuery(conn, sql);
159 } catch (SQLException sqle) {
160 throw new MactorException("Failed to execute the query '" + sql + "'. Error: " + sqle.getMessage(), sqle);
161 } finally {
162 close(conn);
163 }
164 }
165 private void execUpdate(Connection conn, String sql, boolean ignoreSqlException) throws MactorException {
166 log.debug("Executing update:" + sql);
167 Statement s = null;
168 try {
169 s = conn.createStatement();
170 s.executeUpdate(sql);
171 close(s);
172 } catch (SQLException sqle) {
173 MactorException me = new MactorException("Failed to execute the update '" + sql + "'. Error: " + sqle.getMessage(), sqle);
174 if (!ignoreSqlException)
175 throw me;
176 log.info("Ignoring:" + me.getMessage(), sqle);
177 } finally {
178 close(s);
179 }
180 }
181 private void close(Connection s) {
182 if (s != null) {
183 try {
184 s.close();
185 } catch (SQLException _) {
186 }
187 }
188 }
189 private void close(Statement s) {
190 if (s != null) {
191 try {
192 s.close();
193 } catch (SQLException _) {
194 }
195 }
196 }
197 private void close(ResultSet s) {
198 if (s != null) {
199 try {
200 s.close();
201 } catch (SQLException _) {
202 }
203 }
204 }
205 private String execSingleCellQuery(Connection conn, String sql) throws SQLException {
206 Statement s = null;
207 ResultSet rs = null;
208 try {
209 s = conn.createStatement();
210 rs = s.executeQuery(sql);
211 if (!rs.next()) {
212 log.info("no rows");
213 return null;
214 } else {
215 ResultSetMetaData md = rs.getMetaData();
216 if (md.getColumnCount() > 0) {
217 return rs.getString(1);
218 } else {
219 log.info("no colums");
220 }
221 }
222 return null;
223 } finally {
224 close(rs);
225 close(s);
226 }
227 }
228 private DataTable execQuery(Connection conn, String sql) throws SQLException {
229 Statement s = null;
230 ResultSet rs = null;
231 try {
232 s = conn.createStatement();
233 rs = s.executeQuery(sql);
234 if (!rs.next()) {
235 log.info("no rows");
236 return null;
237 } else {
238 ResultSetMetaData md = rs.getMetaData();
239 int count = md.getColumnCount();
240 if (count == 0) {
241 log.info("no colums");
242 return null;
243 } else {
244 DataTable table = new DataTable();
245 for (int i = 1; i <= count; i++) {
246 table.addColumn(md.getColumnName(i));
247 }
248 do {
249 String[] row = new String[count];
250 for (int i = 1; i <= count; i++) {
251 row[i - 1] = rs.getString(i);
252 }
253 table.addRow(row);
254 } while (rs.next());
255 return table;
256 }
257 }
258 } finally {
259 close(rs);
260 close(s);
261 }
262 }
263 }