001: /*
002: * Copyright 2001-2007 Geert Bevin <gbevin[remove] at uwyn dot com>
003: * Distributed under the terms of either:
004: * - the common development and distribution license (CDDL), v1.0; or
005: * - the GNU Lesser General Public License, v2.1 or later
006: * $Id: TestCapabilities.java 3634 2007-01-08 21:42:24Z gbevin $
007: */
008: package com.uwyn.rife.database;
009:
010: import com.uwyn.rife.database.exceptions.DatabaseException;
011: import com.uwyn.rife.database.exceptions.ExecutionErrorException;
012: import com.uwyn.rife.database.exceptions.UndefinedVirtualParameterException;
013: import com.uwyn.rife.database.queries.CreateTable;
014: import com.uwyn.rife.database.queries.DropTable;
015: import com.uwyn.rife.database.queries.Insert;
016: import com.uwyn.rife.database.queries.Select;
017: import com.uwyn.rife.pcj.list.IntArrayList;
018: import java.sql.ResultSet;
019: import java.sql.SQLException;
020: import junit.framework.TestCase;
021:
022: public class TestCapabilities extends TestCase {
023: private Datasource mDatasource = null;
024:
025: public TestCapabilities(Datasource datasource,
026: String datasourceName, String name) {
027: super (name);
028: mDatasource = datasource;
029: }
030:
031: public void setUp() {
032: DbQueryManager manager = new DbQueryManager(mDatasource);
033:
034: CreateTable createtable = new CreateTable(mDatasource);
035: createtable.table("tablename").columns(BeanImpl.class)
036: .precision("propertyBigDecimal", 18, 9).precision(
037: "propertyChar", 1).precision("propertyDouble",
038: 12, 3).precision("propertyDoubleObject", 12, 3)
039: .precision("propertyFloat", 13, 2).precision(
040: "propertyFloatObject", 13, 2).precision(
041: "propertyString", 255).precision(
042: "propertyStringbuffer", 100);
043:
044: try {
045: // prepare table and data
046: manager.executeUpdate(createtable);
047:
048: Insert insert = new Insert(mDatasource);
049: insert.into("tablename")
050: .fields(BeanImpl.getPopulatedBean());
051: manager.executeUpdate(insert);
052:
053: insert.clear();
054: insert.into("tablename").fields(BeanImpl.getNullBean());
055: manager.executeUpdate(insert);
056:
057: BeanImpl impl = BeanImpl.getPopulatedBean();
058: insert.clear();
059: impl.setPropertyInt(3);
060: insert.into("tablename").fields(impl);
061: manager.executeUpdate(insert);
062: insert.clear();
063: impl.setPropertyInt(4);
064: insert.into("tablename").fields(impl);
065: manager.executeUpdate(insert);
066: insert.clear();
067: impl.setPropertyInt(5);
068: insert.into("tablename").fields(impl);
069: manager.executeUpdate(insert);
070: } catch (DatabaseException e) {
071: tearDown();
072: throw new RuntimeException(e);
073: }
074: }
075:
076: public void tearDown() {
077: DbQueryManager manager = new DbQueryManager(mDatasource);
078:
079: // clean up nicely
080: DropTable drop_table = new DropTable(mDatasource);
081: try {
082: drop_table.table("tablename");
083: manager.executeUpdate(drop_table);
084: } catch (DatabaseException e) {
085: System.out.println(e.toString());
086: }
087: }
088:
089: public void testLimitOffset() {
090: DbQueryManager manager = new DbQueryManager(mDatasource);
091:
092: final IntArrayList limit_ids = new IntArrayList();
093:
094: Select query = new Select(mDatasource);
095: query.from("tablename").orderBy("propertyInt").limit(3);
096:
097: assertTrue(manager.executeFetchAll(query, new DbRowProcessor() {
098: public boolean processRow(ResultSet resultSet)
099: throws SQLException {
100: limit_ids.add(resultSet.getInt("propertyInt"));
101: return true;
102: }
103: }));
104: assertEquals(3, limit_ids.size());
105: assertEquals(0, limit_ids.get(0));
106: assertEquals(3, limit_ids.get(1));
107: assertEquals(4, limit_ids.get(2));
108:
109: final IntArrayList offset_ids = new IntArrayList();
110:
111: query.offset(1);
112:
113: assertTrue(manager.executeFetchAll(query, new DbRowProcessor() {
114: public boolean processRow(ResultSet resultSet)
115: throws SQLException {
116: offset_ids.add(resultSet.getInt("propertyInt"));
117: return true;
118: }
119: }));
120: assertEquals(3, offset_ids.size());
121: assertEquals(3, offset_ids.get(0));
122: assertEquals(4, offset_ids.get(1));
123: assertEquals(5, offset_ids.get(2));
124:
125: query.clear();
126:
127: final IntArrayList plain_ids = new IntArrayList();
128:
129: query.from("tablename").orderBy("propertyInt").offset(10);
130:
131: assertTrue(manager.executeFetchAll(query, new DbRowProcessor() {
132: public boolean processRow(ResultSet resultSet)
133: throws SQLException {
134: plain_ids.add(resultSet.getInt("propertyInt"));
135: return true;
136: }
137: }));
138: assertEquals(5, plain_ids.size());
139: assertEquals(0, plain_ids.get(0));
140: assertEquals(3, plain_ids.get(1));
141: assertEquals(4, plain_ids.get(2));
142: assertEquals(5, plain_ids.get(3));
143: assertEquals(545, plain_ids.get(4));
144: }
145:
146: public void testLimitOffsetParameters() {
147: DbQueryManager manager = new DbQueryManager(mDatasource);
148:
149: final IntArrayList limit_ids = new IntArrayList();
150:
151: Select query = new Select(mDatasource);
152: query.from("tablename").orderBy("propertyInt").limitParameter(
153: "limit");
154:
155: assertTrue(manager.executeFetchAll(query, new DbRowProcessor() {
156: public boolean processRow(ResultSet resultSet)
157: throws SQLException {
158: limit_ids.add(resultSet.getInt("propertyInt"));
159: return true;
160: }
161: }, new DbPreparedStatementHandler() {
162: public void setParameters(DbPreparedStatement statement) {
163: statement.setInt("limit", 3);
164: }
165: }));
166: assertEquals(3, limit_ids.size());
167: assertEquals(0, limit_ids.get(0));
168: assertEquals(3, limit_ids.get(1));
169: assertEquals(4, limit_ids.get(2));
170:
171: final IntArrayList offset_ids = new IntArrayList();
172:
173: query.offsetParameter("offset");
174:
175: assertTrue(manager.executeFetchAll(query, new DbRowProcessor() {
176: public boolean processRow(ResultSet resultSet)
177: throws SQLException {
178: offset_ids.add(resultSet.getInt("propertyInt"));
179: return true;
180: }
181: }, new DbPreparedStatementHandler() {
182: public void setParameters(DbPreparedStatement statement) {
183: statement.setInt("limit", 3).setInt("offset", 1);
184: }
185: }));
186: assertEquals(3, offset_ids.size());
187: assertEquals(3, offset_ids.get(0));
188: assertEquals(4, offset_ids.get(1));
189: assertEquals(5, offset_ids.get(2));
190:
191: query.clear();
192:
193: final IntArrayList plain_ids = new IntArrayList();
194:
195: query.from("tablename").orderBy("propertyInt").offsetParameter(
196: "offset");
197:
198: assertTrue(manager.executeFetchAll(query, new DbRowProcessor() {
199: public boolean processRow(ResultSet resultSet)
200: throws SQLException {
201: plain_ids.add(resultSet.getInt("propertyInt"));
202: return true;
203: }
204: }));
205: assertEquals(5, plain_ids.size());
206: assertEquals(0, plain_ids.get(0));
207: assertEquals(3, plain_ids.get(1));
208: assertEquals(4, plain_ids.get(2));
209: assertEquals(5, plain_ids.get(3));
210: assertEquals(545, plain_ids.get(4));
211: }
212:
213: public void testLimitOffsetParametersMissing() {
214: DbQueryManager manager = new DbQueryManager(mDatasource);
215:
216: Select query = new Select(mDatasource);
217: query.from("tablename").orderBy("propertyInt").limitParameter(
218: "limit");
219:
220: try {
221: manager.executeFetchAll(query, new DbRowProcessor() {
222: public boolean processRow(ResultSet resultSet)
223: throws SQLException {
224: return true;
225: }
226: });
227: assertTrue("org.hsqldb.jdbcDriver".equals(mDatasource
228: .getAliasedDriver())); // hsqldb 1.8.0 doesn't throw an exception when no limit parameter is provided
229: } catch (ExecutionErrorException e) {
230: assertTrue(e.getCause() instanceof SQLException);
231: } catch (UndefinedVirtualParameterException e) {
232: assertEquals("limit", e.getParameterName());
233: }
234:
235: query.offsetParameter("offset");
236:
237: try {
238: manager.executeFetchAll(query, new DbRowProcessor() {
239: public boolean processRow(ResultSet resultSet)
240: throws SQLException {
241: return true;
242: }
243: }, new DbPreparedStatementHandler() {
244: public void setParameters(DbPreparedStatement statement) {
245: statement.setInt("limit", 3);
246: }
247: });
248: assertTrue("org.hsqldb.jdbcDriver".equals(mDatasource
249: .getAliasedDriver())); // hsqldb 1.8.0 doesn't throw an exception when no offset parameter is provided
250: } catch (ExecutionErrorException e) {
251: assertTrue(e.getCause() instanceof SQLException);
252: } catch (UndefinedVirtualParameterException e) {
253: assertEquals("offset", e.getParameterName());
254: }
255: }
256: }
|