001: /**
002: * Copyright (C) 2006 NetMind Consulting Bt.
003: *
004: * This library is free software; you can redistribute it and/or
005: * modify it under the terms of the GNU Lesser General Public
006: * License as published by the Free Software Foundation; either
007: * version 3 of the License, or (at your option) any later version.
008: *
009: * This library is distributed in the hope that it will be useful,
010: * but WITHOUT ANY WARRANTY; without even the implied warranty of
011: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
012: * Lesser General Public License for more details.
013: *
014: * You should have received a copy of the GNU Lesser General Public
015: * License along with this library; if not, write to the Free Software
016: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
017: */package hu.netmind.persistence;
018:
019: import java.sql.Types;
020: import java.sql.DatabaseMetaData;
021: import java.sql.ResultSet;
022: import java.sql.Connection;
023: import java.sql.PreparedStatement;
024: import java.sql.SQLException;
025: import java.util.HashMap;
026: import java.util.Map;
027: import java.util.Iterator;
028: import java.util.List;
029: import java.math.BigDecimal;
030: import oracle.sql.TIMESTAMP;
031: import oracle.sql.BLOB;
032: import org.apache.log4j.Logger;
033: import hu.netmind.persistence.parser.Expression;
034: import hu.netmind.persistence.parser.TableTerm;
035: import hu.netmind.persistence.parser.QueryStatement;
036:
037: /**
038: * Oracle database implementation.
039: * Limitations:
040: * <ul>
041: * <li>Strings are limited to 1024 characters.</li>
042: * </ul>
043: * @author Brautigam Robert
044: * @version CVS Revision: $Revision$
045: */
046: public class OracleDatabaseImpl extends GenericDatabase {
047: private static Logger logger = Logger
048: .getLogger(OracleDatabaseImpl.class);
049:
050: public OracleDatabaseImpl() {
051: super ();
052: }
053:
054: /**
055: * Set the limits of the prepared statement if offset is 0. This
056: * is to avoid using complicated limit+offset inner selects, when
057: * the first page is queried.
058: */
059: protected void prepareStatement(PreparedStatement pstmt,
060: Limits limits) throws SQLException {
061: if ((limits == null) || (limits.isEmpty()))
062: return;
063: if ((limits.getOffset() == 0) && (limits.getLimit() > 0))
064: pstmt.setMaxRows((int) limits.getLimit());
065: }
066:
067: /**
068: * Get the limit component of statement, if it can be expressed in
069: * the current database with simple statement part.
070: * @param limits The limits to apply.
071: */
072: protected String getLimitStatement(String statement, Limits limits,
073: List types) {
074: if ((limits.getLimit() == 0) || (limits.getOffset() == 0)) {
075: if (limits.getOffset() != 0) {
076: // No limit, there should be no offset
077: throw new StoreException(
078: "received a limit of 0, but offset was: "
079: + limits.getOffset());
080: }
081: // Nothing
082: return statement;
083: }
084: // Add type of rownum (see below for explanation)
085: types.add(Long.class);
086: // Now, oracle does not have limits and offsets,
087: // so don't look at me, this is the way it's done.
088: // In a stateless environment anyway.
089: return "select * from (select sub.*, rownum rnum from ("
090: + statement + ") sub where rownum <= "
091: + (limits.getOffset() + limits.getLimit())
092: + ") where rnum > " + limits.getOffset();
093: }
094:
095: /**
096: * Override to correct type conflicts an unsupported types.
097: */
098: protected String getSQLTypeName(int sqltype) {
099: switch (sqltype) {
100: case Types.LONGVARCHAR:
101: case Types.VARCHAR:
102: return "varchar2(1024)";
103: case Types.BIGINT:
104: case Types.INTEGER:
105: case Types.SMALLINT:
106: case Types.DECIMAL:
107: return "number(*,0)";
108: case Types.BIT:
109: case Types.BOOLEAN:
110: return "number(1,0)";
111: case Types.CHAR:
112: return "char(1)";
113: default:
114: return super .getSQLTypeName(sqltype);
115: }
116: }
117:
118: /**
119: * Throw exception when String is longer than Oracle can handle.
120: */
121: protected Object getSQLValue(Object value) {
122: if ((value instanceof String) && ("".equals((String) value)))
123: return new String("$"); // Don't let empty string through
124: if (value instanceof String) {
125: // Escape all $ signs
126: StringBuffer str = new StringBuffer((String) value);
127: for (int i = 0; i < str.length(); i++) {
128: if (str.charAt(i) == '$') {
129: str.insert(i, "$");
130: i++;
131: }
132: }
133: return str.toString();
134: }
135: if (value instanceof Character)
136: return value.toString();
137: if (value instanceof Boolean)
138: return new Integer(((Boolean) value).booleanValue() ? 1 : 0);
139: if ((value instanceof String)
140: && (((String) value).length() > 1024))
141: throw new StoreException(
142: "received a string which was too long for Oracle to handle (more than 1024 characters), the string started with: "
143: + ((String) value).substring(0, 100));
144: return super .getSQLValue(value);
145: }
146:
147: /**
148: * Convert incoming value from database into java format.
149: */
150: protected Object getJavaValue(Object value, int type, Class javaType) {
151: try {
152: if (value == null)
153: return null;
154: logger.debug("transforming value: " + value + ", type: "
155: + type + ", java type: " + javaType);
156: if ((Boolean.class.equals(javaType))
157: || (boolean.class.equals(javaType)))
158: return new Boolean(((Number) value).intValue() > 0);
159: if ((value instanceof String)
160: && (((String) value).length() == 1)
161: && (((String) value).charAt(0) == '$'))
162: return ""; // Deconvert dollar to empty string
163: if (value instanceof String) {
164: // Unescape all $ signs
165: StringBuffer str = new StringBuffer((String) value);
166: for (int i = 0; i < str.length(); i++) {
167: if (str.charAt(i) == '$') {
168: str.deleteCharAt(i);
169: i--;
170: }
171: }
172: return str.toString();
173: }
174: if (value instanceof BLOB)
175: return ((BLOB) value).getBytes(1, (int) ((BLOB) value)
176: .length());
177: if (value instanceof TIMESTAMP)
178: return ((TIMESTAMP) value).timestampValue();
179: if (value instanceof BigDecimal) {
180: if (javaType.equals(Long.class)
181: || javaType.equals(long.class))
182: return new Long(((BigDecimal) value).longValue());
183: if (javaType.equals(Integer.class)
184: || javaType.equals(int.class))
185: return new Integer(((BigDecimal) value).intValue());
186: }
187: return super .getJavaValue(value, type, javaType);
188: } catch (StoreException e) {
189: throw e;
190: } catch (Exception e) {
191: throw new StoreException(
192: "conversion error tried to convert: " + value
193: + ", of sql type: " + type, e);
194: }
195: }
196:
197: /**
198: * Get the count statement for the given statement.
199: */
200: protected String getCountStatement(String stmt) {
201: return "select count(*) from (" + stmt + ")";
202: }
203:
204: /**
205: * Get the data types of a given table.
206: * @return A map of names with the sql type number as value.
207: */
208: protected HashMap getTableAttributeTypes(Connection connection,
209: String tableName) throws SQLException {
210: return super .getTableAttributeTypes(connection, tableName
211: .toUpperCase());
212: }
213:
214: /**
215: * Fix custom data types not supported by database.
216: */
217: protected int getTableAttributeType(ResultSet rs)
218: throws SQLException {
219: int columnType = rs.getInt("DATA_TYPE");
220: // Recognize boolean type
221: if ((columnType == Types.DECIMAL)
222: && (rs.getInt("COLUMN_SIZE") == 1))
223: columnType = Types.BOOLEAN;
224: return columnType;
225: }
226:
227: /**
228: * Get the table declaration for a select statment.
229: */
230: protected String getTableDeclaration(String tableName, String alias) {
231: if (alias == null)
232: return tableName;
233: else
234: return tableName + " " + alias;
235: }
236:
237: /**
238: * Get the statement to add a column to a table.
239: */
240: protected String getAddColumnStatement(String tableName,
241: String columnName, String columnType) {
242: return "alter table " + tableName + " add " + columnName + " "
243: + columnType;
244: }
245:
246: /**
247: * Transform 'ilike' to upper case like.
248: * @param expr The expression to possibly transform.
249: * @return A transformed expression.
250: */
251: protected Expression transformExpression(Expression expr) {
252: Expression result = new Expression(expr);
253: result.clear();
254: for (int i = 0; i < expr.size(); i++) {
255: Object item = expr.get(i);
256: if ("ilike".equals(item)) {
257: // Here we need to upper() the argument before and after like
258: Object arg = result.removeLast();
259: result.add("upper(");
260: result.add(arg);
261: result.add(")");
262: result.add("like");
263: result.add("upper(");
264: result.add(expr.get(i + 1));
265: result.add(")");
266: i++; // We used an argument
267: } else {
268: result.add(item);
269: }
270: }
271: return result;
272: }
273:
274: /**
275: * Get an unused index name.
276: */
277: protected String getCreateIndexName(Connection connection,
278: String tableName, String field) {
279: return super.getCreateIndexName(connection, tableName
280: .toUpperCase(), field);
281: }
282: }
|