|
1
55 package org.lateralnz.common.util;
56
57 import java.sql.Connection  ;
58 import java.sql.DatabaseMetaData ;
59 import java.sql.Driver ;
60 import java.sql.DriverManager ;
61 import java.sql.Statement ;
62 import java.sql.PreparedStatement ;
63 import java.sql.ResultSet ;
64 import java.sql.ResultSetMetaData ;
65 import java.sql.SQLException ;
66 import java.sql.Timestamp ;
67 import java.sql.Types ;
68 import java.util.Date ;
69 import java.util.HashMap ;
70 import java.util.ResourceBundle ;
71 import java.util.MissingResourceException ;
72 import javax.naming.NamingException ;
73 import javax.sql.DataSource ;
74
75 import org.apache.log4j.Logger;
76
77
83 public class DAOUtils implements Constants {
84 private static final Logger log = Logger.getLogger(DAOUtils.class.getName());
85 private static final ResourceBundle resources = ResourceUtils.getStaticBundle(DAOUtils.class.getName(), DAOUtils.class);
86
87 private static HashMap columnInfoMap = new HashMap ();
88 private static HashMap placeholders = new HashMap ();
90 private static final String DUPLICATE_KEY = "duplicate key";
91
92 private static String DEFAULT_DATASOURCE;
93
94 static {
95 try {
96 DEFAULT_DATASOURCE = resources.getString("default_datasource");
97 }
98 catch (Exception e) {
99 e.printStackTrace();
100 }
101 }
102
103 public static final String buildSQL(ResourceBundle sqlstatements, String [] keys) {
104 StringBuffer sb = new StringBuffer ();
105
106 try {
107 for (int i = 0; i < keys.length; i++) {
108 if (i > 0) {
109 sb.append(SPACE);
110 }
111 sb.append(sqlstatements.getString(keys[i]));
112 }
113
114 return sb.toString();
115 }
116 catch (MissingResourceException mre) {
117 throw new RuntimeException ("missing sql statement resource " + mre.getKey());
118 }
119 }
120
121 public static boolean commit(Connection conn) {
122 try {
123 if (conn != null) {
124 conn.commit();
125 }
126
127 return true;
128 }
129 catch (SQLException se) {
130 se.printStackTrace();
131 return false;
132 }
133 }
134
135 public static boolean close(Statement st) {
136 if (st != null) {
137 try {
138 st.clearWarnings();
139 }
140 catch (SQLException se) {
141 if (log.isDebugEnabled()) {
142 log.debug("statement clear warnings failed " + se.getMessage());
143 }
144 return false;
145 }
146
147 try {
148 st.close();
149 }
150 catch (SQLException se) {
151 se.printStackTrace();
152 return false;
153 }
154 }
155
156 return true;
157 }
158
159 public static boolean close(ResultSet rs) {
160 if (rs != null) {
161 try {
162 rs.clearWarnings();
163 }
164 catch (SQLException se) {
165 if (log.isDebugEnabled()) {
166 log.debug("resultset clear warnings failed " + se.getMessage());
167 }
168 return false;
169 }
170
171 try {
172 rs.close();
173 }
174 catch (SQLException se) {
175 se.printStackTrace();
176 return false;
177 }
178 }
179
180 return true;
181 }
182
183 public static boolean close(Connection conn) {
184 if (conn != null) {
185 try {
186 conn.clearWarnings();
187 }
188 catch (SQLException se) {
189 if (log.isDebugEnabled()) {
190 log.debug("connection clear warnings failed " + se.getMessage());
191 }
192 return false;
193 }
194
195 try {
196 conn.close();
197 }
198 catch (SQLException se) {
199 se.printStackTrace();
200 return false;
201 }
202 }
203
204 return true;
205 }
206
207 public static final String createParameterPlaceholders(int count) {
208 String key = Integer.toString(count);
209 if (!placeholders.containsKey(key)) {
210 synchronized (placeholders) {
211 if (!placeholders.containsKey(key)) {
212 StringBuffer sb = new StringBuffer ();
213 int end = count - 1;
214 for (int i = 0; i < count; i++) {
215 sb.append(QUESTION_MARK);
216 if (i < end) {
217 sb.append(COMMA);
218 }
219 }
220 placeholders.put(key, sb.toString());
221 }
222 }
223 }
224 return (String )placeholders.get(key);
225 }
226
227 public static final String createSQLArrayString(String [] vals, int start, int len) {
228 StringBuffer sb = new StringBuffer (LEFT_BRACE);
229 int stopcomma = len - 1;
230 for (int i = start; i < len; i++) {
231 sb.append(QUOTE).append(vals[i]).append(QUOTE);
232 if (i < stopcomma) {
233 sb.append(COMMA);
234 }
235 }
236 sb.append(RIGHT_BRACE);
237 return sb.toString();
238 }
239
240 public static String dump(ResultSet rs) {
241 try {
242 StringBuffer sb = new StringBuffer ();
243 ResultSetMetaData meta = rs.getMetaData();
244 for (int i = 1; i <= meta.getColumnCount(); i++) {
245 sb.append(meta.getColumnName(i)).append(COMMA).append(meta.getColumnLabel(i)).append(NEWLINE);
246 }
247 return sb.toString();
248 }
249 catch (SQLException se) {
250 return se.getMessage();
251 }
252 }
253
254 public static String dumpData(ResultSet rs) {
255 try {
256 StringBuffer sb = new StringBuffer ();
257 ResultSetMetaData meta = rs.getMetaData();
258 while (rs.next()) {
259 for (int i = 1; i <= meta.getColumnCount(); i++) {
260 sb.append(rs.getString(i)).append(COMMA);
261 }
262 sb.append(NEWLINE);
263 }
264 return sb.toString();
265 }
266 catch (SQLException se) {
267 return se.getMessage();
268 }
269 }
270
271
274 public static boolean getCharAsBoolean(ResultSet rs, String column, boolean defaultValue) throws SQLException {
275 String s = rs.getString(column);
276 if (StringUtils.isEmpty(s)) {
277 return defaultValue;
278 }
279 else if (s.equalsIgnoreCase(Y)) {
280 return true;
281 }
282 else {
283 return false;
284 }
285 }
286
287
291 public static Date getDate(ResultSet rs, String columnName) throws SQLException {
292 return new Date (rs.getTimestamp(columnName).getTime());
293 }
294
295
296
301 public static boolean hasColumnMetaData(String schema, String table, String column) {
302 String key = table + DOT + column;
304 return columnInfoMap.containsKey(key);
305 }
306
307
312 public static ColumnMetaData getColumnMetaData(Connection conn, String schema, String table, String column) throws SQLException {
313 String key = table + DOT + column;
314 ColumnMetaData cmd = null;
315 if (columnInfoMap.containsKey(key)) {
316 cmd = (ColumnMetaData)columnInfoMap.get(key);
317 }
318 else {
319 ResultSet rs = null;
320 try {
321 DatabaseMetaData meta = conn.getMetaData();
322 rs = meta.getColumns(null, null, table, column);
324 if (rs.next()) {
325 cmd = new ColumnMetaData(rs.getShort(5), rs.getInt(7), (rs.getInt(11) == DatabaseMetaData.columnNoNulls ? true : false));
326 columnInfoMap.put(key, cmd);
327 }
328 else {
329 throw new SQLException ("invalid metadata for " + key);
330 }
331 }
332 finally {
333 close(rs);
334 }
335 }
336
337 return cmd;
338 }
339
340
341
345 public static Connection getConnection(String datasource) throws SQLException {
346 DataSource ds = null;
347 try {
348 if (StringUtils.isEmpty(datasource)) {
349 datasource = DEFAULT_DATASOURCE;
350 }
351
352 ds = (DataSource )JNDIUtils.get(JNDIUtils.DAO_CONTEXT, datasource);
353
354 Connection con = ds.getConnection();
355
356 if (con == null) {
357 throw new SQLException ("unable to get connection for datasource: '" + datasource + "'");
358 }
359
360 return con;
361 }
362 catch (NamingException ne) {
363 throw new SQLException ("unable to find datasource: '" + datasource + "'");
364 }
365 }
366
367 public static boolean isDuplicateKeyException(Throwable t) {
368 if (t == null || !(t instanceof SQLException )) {
369 return false;
370 }
371 else {
372 return isDuplicateKeyException(t);
373 }
374 }
375
376 public static boolean isDuplicateKeyException(SQLException se) {
377 if (se == null) {
378 return false;
379 }
380 else {
381 String msg = se.getMessage();
382 return (msg != null && msg.indexOf(DUPLICATE_KEY) != -1);
383 }
384 }
385
386 public static final String parseInClause(String sql, int numInList) {
387 String inclause = createParameterPlaceholders(numInList);
388
389 return StringUtils.replace(sql, "@@@", inclause);
390 }
391
392 public static final void registerDriver(String driverClass) throws Exception {
393 Class c = Class.forName(driverClass);
394 Driver d = (Driver )c.newInstance();
395 DriverManager.registerDriver(d);
396 }
397
398
405 public static void setParam(PreparedStatement ps, int col, String value) throws SQLException {
406 if (value == null) {
407 ps.setNull(col, Types.VARCHAR);
408 }
409 else {
410 ps.setString(col, value);
411 }
412 }
413
414
423 public static void setParam(PreparedStatement ps, int col, int value) throws SQLException {
424 setParam(ps, col, value, false);
425 }
426
427
436 public static void setParam(PreparedStatement ps, int col, int value, boolean preserve) throws SQLException {
437 if (preserve || value != Integer.MIN_VALUE) {
438 ps.setInt(col, value);
439 }
440 else {
441 ps.setNull(col, Types.INTEGER);
442 }
443 }
444
445
453 public static void setParam(PreparedStatement ps, int col, float value) throws SQLException {
454 if (value != Float.NEGATIVE_INFINITY) {
455 ps.setFloat(col, value);
456 }
457 else {
458 ps.setNull(col, Types.FLOAT);
459 }
460 }
461
462
469 public static void setParam(PreparedStatement ps, int col, Date value) throws SQLException {
470 if (value == null) {
471 ps.setNull(col, Types.DATE);
472 }
473 else {
474 ps.setTimestamp(col, new Timestamp (value.getTime()));
475 }
476 }
477
478 public static void setParam(PreparedStatement ps, int col, boolean value) throws SQLException {
479 if (value) {
480 ps.setString(col, Y);
481 }
482 else {
483 ps.setString(col, N);
484 }
485 }
486
487 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.