001: /*
002: * Copyright 2004 Outerthought bvba and Schaubroeck nv
003: *
004: * Licensed under the Apache License, Version 2.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016: package org.outerj.daisy.jdbcutil;
017:
018: import java.lang.reflect.Constructor;
019: import java.sql.*;
020: import java.util.HashMap;
021: import java.util.Map;
022:
023: import javax.sql.DataSource;
024:
025: import org.apache.commons.logging.Log;
026:
027: public abstract class JdbcHelper {
028: private Log log;
029: private static Map<String, Class> jdbcHelpers = new HashMap<String, Class>();
030:
031: static {
032: jdbcHelpers.put("MySQL", MySqlJdbcHelper.class);
033: jdbcHelpers.put("PostgreSQL", PostgresqlJdbcHelper.class);
034: jdbcHelpers.put("Oracle", OracleJdbcHelper.class);
035: }
036:
037: public static JdbcHelper getInstance(String databaseProductName,
038: Log log) {
039: Class clazz = jdbcHelpers.get(databaseProductName);
040: if (clazz == null) {
041: throw new RuntimeException("Unsupported database: "
042: + databaseProductName);
043: }
044:
045: try {
046: Constructor constructor = clazz.getConstructor(Log.class);
047: return (JdbcHelper) constructor.newInstance(log);
048: } catch (Exception e) {
049: throw new RuntimeException("Error creating JdbcHelper.", e);
050: }
051: }
052:
053: public static JdbcHelper getInstance(DataSource dataSource, Log log) {
054: String databaseProductName = null;
055: try {
056: Connection conn = dataSource.getConnection();
057: try {
058: databaseProductName = conn.getMetaData()
059: .getDatabaseProductName();
060: } finally {
061: conn.close();
062: }
063: } catch (Exception e) {
064: throw new RuntimeException(
065: "Problem determining database product name.", e);
066: }
067: return JdbcHelper.getInstance(databaseProductName, log);
068: }
069:
070: private JdbcHelper(Log log) {
071: this .log = log;
072: }
073:
074: public void closeStatement(Statement stmt) {
075: if (stmt == null)
076: return;
077:
078: try {
079: // note: calling close on an already closed statement has no effect, so we don't have to worry about that
080: stmt.close();
081: } catch (Exception e) {
082: log.error("Error closing SQL statement.", e);
083: }
084: }
085:
086: public void closeConnection(Connection conn) {
087: if (conn == null)
088: return;
089:
090: try {
091: if (!conn.getAutoCommit()) {
092: conn.commit(); // in case a commit has somewhere been forgotten
093: conn.setAutoCommit(true);
094: }
095: } catch (Throwable e) {
096: log
097: .error(
098: "Error setting connection to autocommit + committing.",
099: e);
100: }
101:
102: try {
103: conn.close();
104: } catch (Throwable e) {
105: log.error("Error closing connection.", e);
106: }
107: }
108:
109: public void rollback(Connection conn) {
110: try {
111: if (conn != null)
112: conn.rollback();
113: } catch (Throwable e) {
114: log.error("Error rolling back transaction.", e);
115: }
116: }
117:
118: public abstract void startTransaction(Connection conn)
119: throws SQLException;
120:
121: public abstract String getSharedLockClause();
122:
123: /** Returns the name of a SQL function that gets the length of a string (in characters, not in bytes). */
124: public String[] getStringLengthFunction() {
125: return new String[] { "CHAR_LENGTH(", ")" };
126: }
127:
128: public String[] getStringConcatFunction() {
129: return new String[] { "CONCAT(", ")" };
130: }
131:
132: public String getStringLeftFunction() {
133: return "LEFT";
134: }
135:
136: public String getStringRightFunction() {
137: return "RIGHT";
138: }
139:
140: public String getSubstringFunction() {
141: return "SUBSTRING";
142: }
143:
144: public String getLowerCaseFunction() {
145: return "LOWER";
146: }
147:
148: public String getUpperCaseFunction() {
149: return "UPPER";
150: }
151:
152: public String[] getExtractYearFunction() {
153: return new String[] { "EXTRACT(YEAR FROM ", ")" };
154: }
155:
156: /** 1 = January */
157: public String[] getExtractMonthFunction() {
158: return new String[] { "EXTRACT(MONTH FROM ", ")" };
159: }
160:
161: public String[] getDayOfWeekFunction() {
162: return new String[] { "DAYOFWEEK(", ")" };
163: }
164:
165: public String[] getDayOfMonthFunction() {
166: return new String[] { "DAYOFMONTH(", ")" };
167: }
168:
169: public String[] getDayOfYearFunction() {
170: return new String[] { "DAYOFYEAR(", ")" };
171: }
172:
173: /** First week = week with a sunday. */
174: public String[] getWeekFunction() {
175: return new String[] { "WEEK(", ")" };
176: }
177:
178: public void setNullableIdField(PreparedStatement stmt, int column,
179: long idValue) throws SQLException {
180: if (idValue == -1) {
181: stmt.setNull(column, Types.BIGINT);
182: } else {
183: stmt.setLong(column, idValue);
184: }
185: }
186:
187: public long getNullableIdField(ResultSet rs, String column)
188: throws SQLException {
189: long id = rs.getLong(column);
190: if (id == 0)
191: return -1;
192: else
193: return id;
194: }
195:
196: public long getNullableIdField(ResultSet rs, int column)
197: throws SQLException {
198: long id = rs.getLong(column);
199: if (id == 0)
200: return -1;
201: else
202: return id;
203: }
204:
205: static class MySqlJdbcHelper extends JdbcHelper {
206: public MySqlJdbcHelper(Log log) {
207: super (log);
208: }
209:
210: public void startTransaction(Connection conn)
211: throws SQLException {
212: // Note: the default transaction level of MySQL is repeatable read and
213: // offers consistent reads
214: conn.setAutoCommit(false);
215: }
216:
217: public String getSharedLockClause() {
218: return "lock in share mode";
219: }
220:
221: public String[] getWeekFunction() {
222: return new String[] { "WEEK(", ", 6)" };
223: }
224: }
225:
226: static class PostgresqlJdbcHelper extends JdbcHelper {
227: public PostgresqlJdbcHelper(Log log) {
228: super (log);
229: }
230:
231: public void startTransaction(Connection conn)
232: throws SQLException {
233: conn
234: .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
235: conn.setAutoCommit(false);
236: }
237:
238: public String getSharedLockClause() {
239: return "for update";
240: }
241: }
242:
243: static class OracleJdbcHelper extends JdbcHelper {
244: public OracleJdbcHelper(Log log) {
245: super (log);
246: }
247:
248: public void startTransaction(Connection conn)
249: throws SQLException {
250: conn
251: .setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
252: conn.setAutoCommit(false);
253: }
254:
255: public String getSharedLockClause() {
256: return "for update";
257: }
258: }
259: }
|