001: /*
002: * ChainBuilder ESB
003: * Visual Enterprise Integration
004: *
005: * Copyright (C) 2006 Bostech Corporation
006: *
007: * This program is free software; you can redistribute it and/or modify it
008: * under the terms of the GNU General Public License as published by the
009: * Free Software Foundation; either version 2 of the License, or (at your option)
010: * any later version.
011: *
012: * This program is distributed in the hope that it will be useful,
013: * but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
014: * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
015: * for more details.
016: *
017: * You should have received a copy of the GNU General Public License along with
018: * this program; if not, write to the Free Software Foundation, Inc.,
019: * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
020: *
021: *
022: * $Id: JdbcSession.java 9616 2007-10-16 08:21:01Z lzheng $
023: */
024:
025: package com.bostechcorp.cbesb.runtime.jdbc;
026:
027: import java.io.ByteArrayInputStream;
028: import java.io.InputStreamReader;
029: import java.math.BigDecimal;
030: import java.sql.CallableStatement;
031: import java.sql.Connection;
032: import java.sql.Date;
033: import java.sql.PreparedStatement;
034: import java.sql.ResultSet;
035: import java.sql.ResultSetMetaData;
036: import java.sql.SQLException;
037: import java.sql.Statement;
038: import java.sql.Time;
039: import java.sql.Timestamp;
040: import java.sql.Types;
041: import java.text.SimpleDateFormat;
042: import java.util.Vector;
043:
044: import org.apache.commons.logging.Log;
045: import org.apache.commons.logging.LogFactory;
046:
047: import com.bostechcorp.cbesb.common.i18n.I18N;
048: import com.bostechcorp.cbesb.common.i18n.Messages;
049:
050: public class JdbcSession {
051:
052: // private Log log = LogFactory.getLog(JdbcSession.class);
053: protected final transient Log logger = LogFactory
054: .getLog(getClass());
055: private long sessionId;
056: private Connection connection = null;
057: private JdbcStatement jdbcStatement = null;
058: private PreparedStatement preparedStatement;
059: private boolean isCallableStatement;
060: private ResultSet resultSet;
061: private ResultSet keysResultSet;
062: private int rowsAffected;
063: private Vector<JdbcVar> outVars;
064: private String charset = (new InputStreamReader(
065: new ByteArrayInputStream(new byte[0]))).getEncoding();
066: private boolean autoCommit;
067:
068: public JdbcSession(Connection connection) {
069: this .connection = connection;
070:
071: }
072:
073: /**
074: * @return the sessionId
075: */
076: public long getSessionId() {
077: return sessionId;
078: }
079:
080: /**
081: * @param sessionId the sessionId to set
082: */
083: public void setSessionId(long sessionId) {
084: this .sessionId = sessionId;
085: }
086:
087: public boolean isAutoCommit() {
088: return autoCommit;
089: }
090:
091: public void setAutoCommit(boolean autoCommit) {
092: this .autoCommit = autoCommit;
093: }
094:
095: /**
096: * @return the resultSet
097: */
098: public ResultSet getResultSet() {
099: return resultSet;
100: }
101:
102: /**
103: * @param resultSet the resultSet to set
104: */
105: public void setResultSet(ResultSet resultSet) {
106: this .resultSet = resultSet;
107: }
108:
109: public ResultSet getKeysResultSet() {
110: return keysResultSet;
111: }
112:
113: public void setKeysResultSet(ResultSet keysResultSet) {
114: this .keysResultSet = keysResultSet;
115: }
116:
117: /**
118: * @return the rowsAffected
119: */
120: public int getRowsAffected() {
121: return rowsAffected;
122: }
123:
124: /**
125: * @param rowsAffected the rowsAffected to set
126: */
127: public void setRowsAffected(int rowsAffected) {
128: this .rowsAffected = rowsAffected;
129: }
130:
131: /**
132: * Closes the connection to the database and frees all resources.
133: */
134: public void close() throws SQLException {
135: if (connection != null) {
136: connection.close();
137: connection = null;
138: }
139: }
140:
141: /**
142: * Performs a commit on the current transaction
143: *
144: */
145: public void commit() throws SQLException {
146: if (connection != null)
147: connection.commit();
148: }
149:
150: /**
151: * Performs a rollback on the current transaction
152: *
153: */
154: public void rollback() throws SQLException {
155: if (connection != null)
156: connection.rollback();
157: }
158:
159: /**
160: *
161: * @param statement
162: * @return
163: * @throws SQLException
164: * @throws SQLException
165: */
166: public void executeStatement(JdbcStatement statement)
167: throws SQLException {
168: jdbcStatement = statement;
169: preparedStatement = null;
170: rowsAffected = -1;
171: resultSet = null;
172: outVars = null;
173: // System.out.println("jdbc encoding:"+this.charset);
174: // String sql = statement.convertSqlStatementToDBEncoing(this.charset);
175: String sql = statement.getSqlStatement();
176: // System.out.println("jdbc sql:"+sql);
177:
178: if (connection == null)
179: throw new SQLException(I18N
180: .getString(Messages.CONNECTION_TIMEOUT));
181:
182: if (sql.startsWith("{")) {
183: preparedStatement = connection.prepareCall(sql,
184: ResultSet.TYPE_SCROLL_SENSITIVE,
185: ResultSet.CONCUR_READ_ONLY);
186: isCallableStatement = true;
187: } else {
188: preparedStatement = connection.prepareStatement(sql,
189: ResultSet.TYPE_SCROLL_SENSITIVE,
190: ResultSet.CONCUR_READ_ONLY);
191: isCallableStatement = false;
192: }
193:
194: bindVars();
195: boolean result = preparedStatement.execute();
196: processResult(result);
197:
198: }
199:
200: /**
201: *
202: * @param statement
203: * @return
204: * @throws SQLException
205: * @throws SQLException
206: */
207: public void executeInsertStatement(JdbcStatement statement)
208: throws SQLException {
209: jdbcStatement = statement;
210: preparedStatement = null;
211: rowsAffected = -1;
212: resultSet = null;
213: outVars = null;
214: // System.out.println("jdbc encoding:"+this.charset);
215: // String sql = statement.convertSqlStatementToDBEncoing(this.charset);
216: String sql = statement.getSqlStatement();
217: if (!sql.toLowerCase().startsWith("insert")) {
218: throw new SQLException("It is not an INSERT statement.");
219: }
220:
221: // System.out.println("jdbc sql:"+sql);
222: if (sql.startsWith("{")) {
223: preparedStatement = connection.prepareCall(sql,
224: ResultSet.TYPE_SCROLL_SENSITIVE,
225: ResultSet.CONCUR_READ_ONLY,
226: Statement.RETURN_GENERATED_KEYS);
227: isCallableStatement = true;
228: } else {
229: try {
230: preparedStatement = connection.prepareStatement(sql,
231: ResultSet.TYPE_SCROLL_SENSITIVE,
232: ResultSet.CONCUR_READ_ONLY,
233: Statement.RETURN_GENERATED_KEYS);
234: } catch (SQLException ex) {
235: //For oracle , it can not support Statement.RETURN_GENERATED_KEYS
236: preparedStatement = connection.prepareStatement(sql,
237: ResultSet.TYPE_SCROLL_SENSITIVE,
238: ResultSet.CONCUR_READ_ONLY);
239:
240: }
241: isCallableStatement = false;
242: }
243:
244: bindVars();
245: boolean result = preparedStatement.execute();
246: processResult(result, true);
247:
248: }
249:
250: public void getNextResult() throws SQLException {
251: boolean result = preparedStatement.getMoreResults();
252: processResult(result);
253: }
254:
255: private void processResult(boolean result) throws SQLException {
256: processResult(result, false);
257: }
258:
259: private void processResult(boolean result, boolean bGeneratedKeys)
260: throws SQLException {
261: if (result) {
262: //A resultset is present
263: resultSet = preparedStatement.getResultSet();
264: if (bGeneratedKeys)
265: keysResultSet = preparedStatement.getGeneratedKeys();
266: } else {
267: //No resultset
268: resultSet = null;
269: rowsAffected = preparedStatement.getUpdateCount();
270: }
271: }
272:
273: private void bindVars() throws SQLException {
274: for (int i = 0; i < jdbcStatement.getBindVarCount(); i++) {
275: int index = i + 1;
276: JdbcVar var = jdbcStatement.getBindVar(i);
277: int datatype = var.getDataType();
278: if (var.getMode() == JdbcVar.MODE_IN
279: || var.getMode() == JdbcVar.MODE_INOUT) {
280: //Add encoding convert
281: // String value = var.convertValueToDBEncoding(this.charset);
282: String value = var.getValue();
283: if (value == null || value.equals("")) {
284: preparedStatement.setNull(index, datatype);
285: } else if ((datatype == Types.CHAR)
286: || (datatype == Types.VARCHAR)
287: || (datatype == Types.LONGVARCHAR)) {
288: preparedStatement.setString(index, value);
289: } else if ((datatype == Types.NUMERIC)
290: || (datatype == Types.DECIMAL)) {
291: BigDecimal bdValue = new BigDecimal(value.trim());
292: preparedStatement.setBigDecimal(index, bdValue);
293: } else if (datatype == Types.BIT) {
294: boolean boolValue = Boolean.valueOf(value.trim())
295: .booleanValue();
296: preparedStatement.setBoolean(index, boolValue);
297: } else if (datatype == Types.TINYINT) {
298: // byte byteValue = Byte.valueOf(value.trim()).byteValue();
299: // preparedStatement.setByte(index, byteValue);
300: short shortValue = Short.valueOf(value.trim())
301: .shortValue();
302: preparedStatement.setShort(index, shortValue);
303:
304: } else if (datatype == Types.SMALLINT) {
305: short shortValue = Short.valueOf(value.trim())
306: .shortValue();
307: preparedStatement.setShort(index, shortValue);
308: } else if (datatype == Types.INTEGER) {
309: int intValue = Integer.valueOf(value.trim())
310: .intValue();
311: preparedStatement.setInt(index, intValue);
312: } else if (datatype == Types.BIGINT) {
313: long longValue = Long.valueOf(value.trim())
314: .longValue();
315: preparedStatement.setLong(index, longValue);
316: } else if (datatype == Types.REAL) {
317: float floatValue = Float.valueOf(value.trim())
318: .floatValue();
319: preparedStatement.setFloat(index, floatValue);
320: } else if ((datatype == Types.FLOAT)
321: || (datatype == Types.DOUBLE)) {
322: double doubleValue = Double.valueOf(value.trim())
323: .doubleValue();
324: preparedStatement.setDouble(index, doubleValue);
325: } else if ((datatype == Types.BINARY)
326: || (datatype == Types.VARBINARY)
327: || (datatype == Types.LONGVARBINARY)) {
328: byte[] byteArrayValue = value.getBytes();
329: preparedStatement.setBytes(index, byteArrayValue);
330: } else if (datatype == Types.DATE) {
331: Date dateValue = Date.valueOf(value.trim());
332: preparedStatement.setDate(index, dateValue);
333: } else if (datatype == Types.TIME) {
334: Time timeValue = Time.valueOf(value.trim());
335: preparedStatement.setTime(index, timeValue);
336: } else if (datatype == Types.TIMESTAMP) {
337:
338: try {
339: Timestamp timestampValue = Timestamp
340: .valueOf(value.trim());
341: preparedStatement.setTimestamp(index,
342: timestampValue);
343:
344: } catch (Exception ex) {
345: Date dateValue = Date.valueOf(value.trim());
346: preparedStatement.setDate(index, dateValue);
347:
348: }
349: }
350: }
351: if (isCallableStatement
352: && (var.getMode() == JdbcVar.MODE_OUT || var
353: .getMode() == JdbcVar.MODE_INOUT)) {
354: CallableStatement cStatement = (CallableStatement) preparedStatement;
355: cStatement.registerOutParameter(index, datatype);
356: }
357: }
358: }
359:
360: public void unbindVars() throws SQLException {
361: outVars = new Vector<JdbcVar>();
362: if (isCallableStatement && jdbcStatement.getBindVarCount() > 0) {
363: CallableStatement cStatement = (CallableStatement) preparedStatement;
364: for (int i = 0; i < jdbcStatement.getBindVarCount(); i++) {
365: int index = i + 1;
366: JdbcVar jdbcvar = jdbcStatement.getBindVar(index);
367: int mode = jdbcvar.getMode();
368: if (mode == JdbcVar.MODE_OUT
369: || mode == JdbcVar.MODE_INOUT) {
370: int datatype = jdbcvar.getDataType();
371: if ((datatype == Types.CHAR)
372: || (datatype == Types.VARCHAR)
373: || (datatype == Types.LONGVARCHAR)) {
374: String strValue = cStatement.getString(index);
375: jdbcvar.setValue(strValue);
376: } else if ((datatype == Types.NUMERIC)
377: || (datatype == Types.DECIMAL)) {
378: BigDecimal bdValue = cStatement
379: .getBigDecimal(index);
380: jdbcvar.setValue(bdValue.toString());
381: } else if (datatype == Types.BIT) {
382: boolean boolValue = cStatement
383: .getBoolean(index);
384: jdbcvar.setValue(Boolean.toString(boolValue));
385: } else if (datatype == Types.TINYINT) {
386: byte byteValue = cStatement.getByte(index);
387: jdbcvar.setValue(Byte.toString(byteValue));
388: } else if (datatype == Types.SMALLINT) {
389: short shortValue = cStatement.getShort(index);
390: jdbcvar.setValue(Short.toString(shortValue));
391: } else if (datatype == Types.INTEGER) {
392: int intValue = cStatement.getInt(index);
393: jdbcvar.setValue(Integer.toString(intValue));
394: } else if (datatype == Types.BIGINT) {
395: long longValue = cStatement.getLong(index);
396: jdbcvar.setValue(Long.toString(longValue));
397: } else if (datatype == Types.REAL) {
398: float floatValue = cStatement.getFloat(index);
399: jdbcvar.setValue(Float.toString(floatValue));
400: } else if ((datatype == Types.FLOAT)
401: || (datatype == Types.DOUBLE)) {
402: double doubleValue = cStatement
403: .getDouble(index);
404: jdbcvar.setValue(Double.toString(doubleValue));
405: } else if ((datatype == Types.BINARY)
406: || (datatype == Types.VARBINARY)
407: || (datatype == Types.LONGVARBINARY)) {
408: byte[] byteArrayValue = cStatement
409: .getBytes(index);
410: jdbcvar.setValue(new String(byteArrayValue));
411: } else if (datatype == Types.DATE) {
412: Date dateValue = cStatement.getDate(index);
413: jdbcvar.setValue(dateValue.toString());
414: } else if (datatype == Types.TIME) {
415: Time timeValue = cStatement.getTime(index);
416: jdbcvar.setValue(timeValue.toString());
417: } else if (datatype == Types.TIMESTAMP) {
418:
419: Timestamp timestampValue = cStatement
420: .getTimestamp(index);
421: jdbcvar.setValue(timestampValue.toString());
422: }
423: outVars.add(jdbcvar);
424: }
425: }
426: }
427: }
428:
429: public int getOutBindVarCount() {
430: if (outVars == null) {
431: return 0;
432: } else {
433: return outVars.size();
434: }
435: }
436:
437: public JdbcVar getOutBindVar(int index) {
438: JdbcVar var = null;
439: if (outVars != null) {
440: if (index >= 0 && index < outVars.size())
441: var = outVars.get(index);
442: }
443: return var;
444: }
445:
446: public String getCharset() {
447: return charset;
448: }
449:
450: public void setCharset(String charset) {
451: this .charset = charset;
452: }
453:
454: public String getResultSetValue(ResultSet resultSet, int index)
455: throws SQLException {
456: if (index <= 0)
457: return "";
458: ResultSetMetaData metedata = resultSet.getMetaData();
459: int datatype = metedata.getColumnType(index);
460: String value = "";
461: if ((datatype == Types.CHAR) || (datatype == Types.VARCHAR)
462: || (datatype == Types.LONGVARCHAR)) {
463: value = resultSet.getString(index);
464:
465: } else if ((datatype == Types.NUMERIC)
466: || (datatype == Types.DECIMAL)) {
467: BigDecimal bdValue = resultSet.getBigDecimal(index);
468: if (bdValue != null)
469: value = bdValue.toString();
470: } else if (datatype == Types.BIT) {
471: boolean boolValue = resultSet.getBoolean(index);
472: value = Boolean.toString(boolValue);
473: } else if (datatype == Types.TINYINT) {
474: // byte byteValue = resultSet.getByte(index);
475: // value = Byte.toString(byteValue);
476: short shortValue = resultSet.getShort(index);
477: value = Short.toString(shortValue);
478:
479: } else if (datatype == Types.SMALLINT) {
480: short shortValue = resultSet.getShort(index);
481: value = Short.toString(shortValue);
482: } else if (datatype == Types.INTEGER) {
483: int intValue = resultSet.getInt(index);
484: value = Integer.toString(intValue);
485: } else if (datatype == Types.BIGINT) {
486: long longValue = resultSet.getLong(index);
487: value = Long.toString(longValue);
488: } else if (datatype == Types.REAL) {
489: float floatValue = resultSet.getFloat(index);
490: value = Float.toString(floatValue);
491: } else if ((datatype == Types.FLOAT)
492: || (datatype == Types.DOUBLE)) {
493: double doubleValue = resultSet.getDouble(index);
494: value = Double.toString(doubleValue);
495: } else if ((datatype == Types.BINARY)
496: || (datatype == Types.VARBINARY)
497: || (datatype == Types.LONGVARBINARY)) {
498: byte[] byteArrayValue = resultSet.getBytes(index);
499: value = new String(byteArrayValue);
500: } else if (datatype == Types.DATE) {
501: Date dateValue = resultSet.getDate(index);
502: value = dateValue.toString();
503: } else if (datatype == Types.TIME) {
504: Time timeValue = resultSet.getTime(index);
505: value = timeValue.toString();
506: } else if (datatype == Types.TIMESTAMP) {
507: Timestamp timestampValue = resultSet.getTimestamp(index);
508: value = timestampValue.toString();
509: // SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
510: // value =format.format(timestampValue);
511: }
512: return value;
513: }
514: }
|