1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 package org.objectledge.database;
29
30 import java.io.IOException;
31 import java.io.LineNumberReader;
32 import java.io.Reader;
33 import java.sql.Connection;
34 import java.sql.DatabaseMetaData;
35 import java.sql.ResultSet;
36 import java.sql.SQLException;
37 import java.sql.Statement;
38
39 import javax.sql.DataSource;
40
41 import org.jcontainer.dna.Logger;
42 import org.jcontainer.dna.impl.Log4JLogger;
43 import org.objectledge.utils.StringUtils;
44
45 /***
46 * A set of utility functions for working with JDBC databases.
47 *
48 * @author <a href="mailto:rafal@caltha.pl">Rafal Krzewski</a>
49 * @version $Id: DatabaseUtils.java,v 1.21 2005/03/30 09:20:04 rafal Exp $
50 */
51 public class DatabaseUtils
52 {
53 /*** A logger. Bypasses LogFactory. */
54 private static Logger log =
55 new Log4JLogger(org.apache.log4j.Logger.getLogger(DatabaseUtils.class));
56
57
58 /***
59 * Private constructor to prevent subclassing and enforce static access.
60 */
61 private DatabaseUtils()
62 {
63
64 }
65
66
67
68
69 /***
70 * Closes the connection.
71 *
72 * @param conn the connection.
73 */
74 public static void close(Connection conn)
75 {
76 try
77 {
78 if (conn != null)
79 {
80 conn.close();
81 }
82 }
83
84 catch (SQLException e)
85 {
86 log.error("failed to close connection", e);
87 }
88
89 }
90
91 /***
92 * Close the statement.
93 *
94 * @param stmt the statement.
95 */
96 public static void close(Statement stmt)
97 {
98 try
99 {
100 if (stmt != null)
101 {
102 stmt.close();
103 }
104 }
105
106 catch (SQLException e)
107 {
108 log.error("failed to close statement", e);
109 }
110
111 }
112
113
114 /***
115 * Close the result set.
116 *
117 * @param rs the result set.
118 */
119 public static void close(ResultSet rs)
120 {
121 try
122 {
123 if (rs != null)
124 {
125 rs.close();
126 }
127 }
128
129 catch (SQLException e)
130 {
131 log.error("failed to close result set", e);
132 }
133
134 }
135
136 /***
137 * Close the connection.
138 *
139 * @param conn the connection.
140 * @param stmt the statement.
141 * @param rs the result set.
142 */
143 public static void close(Connection conn, Statement stmt, ResultSet rs)
144 {
145 close(rs);
146 close(stmt);
147 close(conn);
148 }
149
150 /***
151 * Unescape the string that comes from query.
152 *
153 * @param input the input string.
154 * @return the result string.
155 */
156 public static String unescapeSqlString(String input)
157 {
158 return input;
159 }
160
161 /***
162 * Escape the \ and ' in string that goes to statement.
163 *
164 * @param input the input string.
165 * @return the result string.
166 */
167 public static String escapeSqlString(String input)
168 {
169 return StringUtils.backslashEscape(input, "'//");
170 }
171
172 /***
173 * Executes an SQL script.
174 *
175 * <p>
176 * <ul>
177 * <li>Lines starting with # are ignored</li>
178 * <li>Statements may span multiple lines, line with a semicolon as the last charcter
179 * terminates the statement.</li>
180 * <li>Script execution is aborted when execution of an statement throws an exception.</li>
181 * </ul>
182 * </p>
183 *
184 * @param dataSource source of connections to the database.
185 * @param reader the reader to read script from.
186 * @throws IOException if the script cannot be read.
187 * @throws SQLException if there is a problem executing the script.
188 */
189 public static void runScript(DataSource dataSource, Reader reader)
190 throws IOException, SQLException
191 {
192 Connection conn = dataSource.getConnection();
193 try
194 {
195 runScript(conn, reader);
196 }
197 finally
198 {
199 close(conn);
200 }
201 }
202
203 /***
204 * Executes an SQL script.
205 *
206 * @see #runScript(Connection, Reader)
207 * @param conn the connections to the database.
208 * @param reader the reader to read script from.
209 * @throws IOException if the script cannot be read.
210 * @throws SQLException if there is a problem executing the script.
211 */
212 public static void runScript(Connection conn, Reader reader)
213 throws IOException, SQLException
214 {
215 Statement stmt = conn.createStatement();
216 try
217 {
218 LineNumberReader script = new LineNumberReader(reader);
219 StringBuilder buff = new StringBuilder();
220 int start;
221
222 while(script.ready())
223 {
224 buff.setLength(0);
225 String line = script.readLine().trim();
226 if(line.length() == 0 || line.charAt(0) == '#' || line.startsWith("--"))
227 {
228 continue;
229 }
230 start = script.getLineNumber();
231 while(script.ready() && line.charAt(line.length()-1) != ';')
232 {
233 buff.append(line).append('\n');
234 line = script.readLine().trim();
235 if(line.length() == 0 || line.charAt(0) == '#' || line.startsWith("--"))
236 {
237 if(script.ready())
238 {
239 line = script.readLine().trim();
240 continue;
241 }
242 else
243 {
244 break;
245 }
246 }
247 }
248 if(line.length() == 0 || line.charAt(line.length()-1) != ';')
249 {
250 throw new SQLException("unterminated statement at line "+start);
251 }
252 buff.append(line);
253 buff.setLength(buff.length()-1);
254 try
255 {
256 stmt.execute(buff.toString());
257 }
258 catch(SQLException e)
259 {
260 throw (SQLException)
261 new SQLException("error executing statement at line "+start).initCause(e);
262 }
263 }
264 }
265 finally
266 {
267 close(stmt);
268 }
269 }
270
271
272 /***
273 * Checks if the given database contains a table with the given name.
274 *
275 * @param ds DataSource for creating connections to database in question.
276 * @param table table name, case insensitive.
277 * @return <code>true</code> if the database contains the table.
278 * @throws SQLException if there is a problem executing the check.
279 */
280 public static boolean hasTable(DataSource ds, String table)
281 throws SQLException
282 {
283 Connection conn = null;
284 ResultSet tables = null;
285 try
286 {
287 conn = ds.getConnection();
288 DatabaseMetaData md = conn.getMetaData();
289 tables = md.getTables(null, null, null, null);
290 boolean result = false;
291 while(tables.next())
292 {
293 if(tables.getString("TABLE_NAME").equalsIgnoreCase(table))
294 {
295 result = true;
296 }
297 }
298 return result;
299 }
300 finally
301 {
302 close(tables);
303 close(conn);
304 }
305 }
306 }