001: /*
002: Copyright (C) 2003 Know Gate S.L. All rights reserved.
003: C/Oņa, 107 1š2 28050 Madrid (Spain)
004:
005: Redistribution and use in source and binary forms, with or without
006: modification, are permitted provided that the following conditions
007: are met:
008:
009: 1. Redistributions of source code must retain the above copyright
010: notice, this list of conditions and the following disclaimer.
011:
012: 2. The end-user documentation included with the redistribution,
013: if any, must include the following acknowledgment:
014: "This product includes software parts from hipergate
015: (http://www.hipergate.org/)."
016: Alternately, this acknowledgment may appear in the software itself,
017: if and wherever such third-party acknowledgments normally appear.
018:
019: 3. The name hipergate must not be used to endorse or promote products
020: derived from this software without prior written permission.
021: Products derived from this software may not be called hipergate,
022: nor may hipergate appear in their name, without prior written
023: permission.
024:
025: This library is distributed in the hope that it will be useful,
026: but WITHOUT ANY WARRANTY; without even the implied warranty of
027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
028:
029: You should have received a copy of hipergate License with this code;
030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
031: */
032:
033: package com.knowgate.dataobjs;
034:
035: import java.sql.SQLException;
036: import java.sql.PreparedStatement;
037: import java.sql.Statement;
038: import java.sql.ResultSet;
039:
040: import java.util.TreeSet;
041:
042: import com.knowgate.debug.DebugFile;
043: import com.knowgate.jdc.JDCConnection;
044:
045: /**
046: * Load a primary keys set from the database into a java.util.TreeSet
047: * @author Sergio Montoro Ten
048: * @version 1.0
049: */
050:
051: public class DBKeySet extends TreeSet {
052: private String sTable;
053: private String sColumn;
054: private String sWhere;
055: private int iMaxRows;
056:
057: /**
058: * @param sTableName Table Name
059: * @param sColumnName Column Name
060: * @param sWhereClause SQL WHERE clause
061: * @param iLimit Absolute maximum number of primary key to be loaded
062: */
063: public DBKeySet(String sTableName, String sColumnName,
064: String sWhereClause, int iLimit) {
065: sTable = sTableName;
066: sColumn = sColumnName;
067: sWhere = sWhereClause;
068: iMaxRows = (iLimit > 0 ? iLimit : 2147483647);
069: }
070:
071: // ---------------------------------------------------------------------------
072:
073: private String composeSQL(int iDbms, int iMaxRows) {
074: String sSQL;
075: if (iMaxRows < 2147483647) {
076: switch (iDbms) {
077: case JDCConnection.DBMS_MSSQL:
078: sSQL = "SELECT " + sColumn + " FROM " + sTable
079: + " WHERE " + sWhere + " OPTION FAST ("
080: + String.valueOf(iMaxRows) + ")";
081: break;
082: case JDCConnection.DBMS_MYSQL:
083: sSQL = "SELECT " + sColumn + " FROM " + sTable
084: + " WHERE " + sWhere + " LIMIT 0,"
085: + String.valueOf(iMaxRows);
086: break;
087: case JDCConnection.DBMS_POSTGRESQL:
088: sSQL = "SELECT " + sColumn + " FROM " + sTable
089: + " WHERE " + sWhere + " LIMIT "
090: + String.valueOf(iMaxRows);
091: break;
092: case JDCConnection.DBMS_ORACLE:
093: sSQL = "SELECT " + sColumn + " FROM " + sTable
094: + " WHERE ROWNUM<=" + String.valueOf(iMaxRows)
095: + " AND " + sWhere;
096: break;
097: default:
098: sSQL = "SELECT " + sColumn + " FROM " + sTable
099: + " WHERE " + sWhere;
100: }
101: } else {
102: sSQL = "SELECT " + sColumn + " FROM " + sTable + " WHERE "
103: + sWhere;
104: }
105: return sSQL;
106: }
107:
108: // ---------------------------------------------------------------------------
109:
110: /**
111: * Load primary keys from the database to this TreeSet
112: * @param oConn JDBC Database Connection
113: * @return Number of keys actually readed
114: * @throws SQLException
115: */
116: public int load(JDCConnection oConn) throws SQLException {
117: String sSQL = composeSQL(oConn.getDataBaseProduct(), iMaxRows);
118: Statement oStmt = oConn
119: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
120: ResultSet.CONCUR_READ_ONLY);
121: if (DebugFile.trace)
122: DebugFile.writeln("Statement.eecuteQuery(" + sSQL + ")");
123: ResultSet oRSet = oStmt.executeQuery(sSQL);
124: try {
125: if (iMaxRows < 2147483647)
126: oRSet.setFetchSize(iMaxRows);
127: else
128: oRSet.setFetchSize(1000);
129: } catch (SQLException ignore) {
130: }
131: clear();
132: int iReaded = 0;
133: while (oRSet.next()) {
134: add(oRSet.getObject(1));
135: iReaded++;
136: }
137: oRSet.close();
138: oStmt.close();
139: return iReaded;
140: }
141:
142: // ---------------------------------------------------------------------------
143:
144: /**
145: * Load primary keys from the database to this TreeSet
146: * @param oConn JDBC Database Connection
147: * @param aParams Parameters to be binded to prepared SQL
148: * @return Number of keys actually readed
149: * @throws SQLException
150: */
151: public int load(JDCConnection oConn, Object[] aParams)
152: throws SQLException {
153: String sSQL = composeSQL(oConn.getDataBaseProduct(), iMaxRows);
154: if (DebugFile.trace)
155: DebugFile.writeln("Connection.prepareStatement(" + sSQL
156: + ")");
157: PreparedStatement oStmt = oConn
158: .prepareStatement(sSQL, ResultSet.TYPE_FORWARD_ONLY,
159: ResultSet.CONCUR_READ_ONLY);
160: for (int p = 0; p < aParams.length; p++)
161: oStmt.setObject(p + 1, aParams[p]);
162: ResultSet oRSet = oStmt.executeQuery();
163: try {
164: if (iMaxRows < 2147483647)
165: oRSet.setFetchSize(iMaxRows);
166: else
167: oRSet.setFetchSize(1000);
168: } catch (SQLException ignore) {
169: }
170: clear();
171: int iReaded = 0;
172: while (oRSet.next()) {
173: add(oRSet.getObject(1));
174: iReaded++;
175: }
176: oRSet.close();
177: oStmt.close();
178: return iReaded;
179: }
180:
181: // ---------------------------------------------------------------------------
182:
183: public int count(JDCConnection oConn) throws SQLException {
184: Statement oStmt = oConn.createStatement();
185: ResultSet oRSet = oStmt.executeQuery("SELECT COUNT(" + sColumn
186: + ") FROM " + sTable + " WHERE " + sWhere);
187: int iRetVal = oRSet.getInt(1);
188: oRSet.close();
189: oStmt.close();
190: return iRetVal;
191: }
192:
193: // ---------------------------------------------------------------------------
194: }
|