0001: /*
0002: * Copyright 2001-2007 Geert Bevin <gbevin[remove] at uwyn dot com>
0003: * Distributed under the terms of either:
0004: * - the common development and distribution license (CDDL), v1.0; or
0005: * - the GNU Lesser General Public License, v2.1 or later
0006: * $Id: TestUpdatePgsql.java 3695 2007-03-16 09:26:50Z gbevin $
0007: */
0008: package com.uwyn.rife.database.queries;
0009:
0010: import com.uwyn.rife.database.BeanImpl;
0011: import com.uwyn.rife.database.BeanImplConstrained;
0012: import com.uwyn.rife.database.DbPreparedStatement;
0013: import com.uwyn.rife.database.DbPreparedStatementHandler;
0014: import com.uwyn.rife.database.exceptions.FieldsRequiredException;
0015: import com.uwyn.rife.database.exceptions.TableNameRequiredException;
0016: import com.uwyn.rife.database.exceptions.UnsupportedSqlFeatureException;
0017: import com.uwyn.rife.database.types.SqlNull;
0018: import java.math.BigDecimal;
0019: import java.sql.Time;
0020: import java.sql.Timestamp;
0021: import java.util.Arrays;
0022: import java.util.Calendar;
0023:
0024: public class TestUpdatePgsql extends TestUpdate {
0025: public TestUpdatePgsql(String name) {
0026: super (name);
0027: }
0028:
0029: public void testInstantiationPgsql() {
0030: Update query = new Update(mPgsql);
0031: assertNotNull(query);
0032: try {
0033: query.getSql();
0034: fail();
0035: } catch (TableNameRequiredException e) {
0036: assertEquals(e.getQueryName(), "Update");
0037: }
0038: }
0039:
0040: public void testIncompleteQueryPgsql() {
0041: Update query = new Update(mPgsql);
0042: try {
0043: query.getSql();
0044: fail();
0045: } catch (TableNameRequiredException e) {
0046: assertEquals(e.getQueryName(), "Update");
0047: }
0048: query.table("tablename4");
0049: try {
0050: query.getSql();
0051: fail();
0052: } catch (FieldsRequiredException e) {
0053: assertEquals(e.getQueryName(), "Update");
0054: }
0055: query.field("col1", "val1");
0056: assertNotNull(query.getSql());
0057: }
0058:
0059: public void testClearPgsql() {
0060: Update query = new Update(mPgsql);
0061: query.table("tablename4").field("col1", "val1");
0062: assertNotNull(query.getSql());
0063: query.clear();
0064: try {
0065: query.getSql();
0066: fail();
0067: } catch (TableNameRequiredException e) {
0068: assertEquals(e.getQueryName(), "Update");
0069: }
0070: }
0071:
0072: public void testHintPgsql() {
0073: Update query = new Update(mPgsql).hint("NO_INDEX").table(
0074: "tablename").field("propertyByte", (byte) 16);
0075: try {
0076: query.getSql();
0077: fail();
0078: } catch (UnsupportedSqlFeatureException e) {
0079: assertTrue(true);
0080: }
0081: }
0082:
0083: public void testFieldPgsql() {
0084: Calendar cal = Calendar.getInstance();
0085: cal.set(2002, 7, 19, 12, 17, 52);
0086: cal.set(Calendar.MILLISECOND, 462);
0087: Update query = new Update(mPgsql);
0088: query.table("tablename").where("propertyByte = 89").field(
0089: "nullColumn", SqlNull.NULL).field("propertyBigDecimal",
0090: new BigDecimal("98347.876438637")).field(
0091: "propertyBoolean", true).field("propertyByte",
0092: (byte) 16).field("propertyCalendar", cal.getTime())
0093: .field("propertyChar", 'M').field("propertyDate", cal)
0094: .field("propertyDouble", 12.3d).field("propertyFloat",
0095: 13.4f).field("propertyInt", 34).field(
0096: "propertyLong", 45L).field("propertyShort",
0097: (short) 12).field("propertySqlDate",
0098: new java.sql.Date(cal.getTime().getTime()))
0099: .field("propertyString", "string'value").field(
0100: "propertyStringbuffer",
0101: new StringBuffer("stringbuffer'value")).field(
0102: "propertyTime",
0103: new Time(cal.getTime().getTime())).field(
0104: "propertyTimestamp",
0105: new Timestamp(cal.getTime().getTime()));
0106: assertEquals(
0107: query.getSql(),
0108: "UPDATE tablename SET nullColumn = NULL, propertyBigDecimal = 98347.876438637, propertyBoolean = true, propertyByte = 16, propertyCalendar = '2002-08-19 12:17:52.462', propertyChar = 'M', propertyDate = '2002-08-19 12:17:52.462', propertyDouble = 12.3, propertyFloat = 13.4, propertyInt = 34, propertyLong = 45, propertyShort = 12, propertySqlDate = '2002-08-19', propertyString = 'string''value', propertyStringbuffer = 'stringbuffer''value', propertyTime = '12:17:52', propertyTimestamp = '2002-08-19 12:17:52.462' WHERE propertyByte = 89");
0109: assertTrue(execute(query));
0110: }
0111:
0112: public void testFieldCustomPgsql() {
0113: Update query = new Update(mPgsql);
0114: query.table("tablename")
0115: .fieldCustom("propertySqlDate", "now()");
0116: assertEquals(query.getSql(),
0117: "UPDATE tablename SET propertySqlDate = now()");
0118: assertTrue(execute(query));
0119: }
0120:
0121: public void testFieldParametersPgsql() {
0122: Update query = new Update(mPgsql);
0123: query.table("tablename");
0124:
0125: assertNull(query.getParameters());
0126:
0127: query.fieldParameter("nullColumn").fieldParameter(
0128: "propertyBigDecimal").fieldParameter("propertyBoolean")
0129: .fieldParameter("propertyByte").fieldParameter(
0130: "propertyCalendar").fieldParameter(
0131: "propertyChar").fieldParameter("propertyDate")
0132: .fieldParameter("propertyDouble").fieldParameter(
0133: "propertyFloat").fieldParameter("propertyInt")
0134: .fieldParameter("propertyLong").fieldParameter(
0135: "propertyShort").fieldParameter(
0136: "propertySqlDate").fieldParameter(
0137: "propertyString").fieldParameter(
0138: "propertyStringbuffer").fieldParameter(
0139: "propertyTime").fieldParameter(
0140: "propertyTimestamp");
0141:
0142: assertEquals(query.getParameters().getOrderedNames().size(), 17);
0143: assertEquals(query.getParameters().getOrderedNames().get(0),
0144: "nullColumn");
0145: assertEquals(query.getParameters().getOrderedNames().get(1),
0146: "propertyBigDecimal");
0147: assertEquals(query.getParameters().getOrderedNames().get(2),
0148: "propertyBoolean");
0149: assertEquals(query.getParameters().getOrderedNames().get(3),
0150: "propertyByte");
0151: assertEquals(query.getParameters().getOrderedNames().get(4),
0152: "propertyCalendar");
0153: assertEquals(query.getParameters().getOrderedNames().get(5),
0154: "propertyChar");
0155: assertEquals(query.getParameters().getOrderedNames().get(6),
0156: "propertyDate");
0157: assertEquals(query.getParameters().getOrderedNames().get(7),
0158: "propertyDouble");
0159: assertEquals(query.getParameters().getOrderedNames().get(8),
0160: "propertyFloat");
0161: assertEquals(query.getParameters().getOrderedNames().get(9),
0162: "propertyInt");
0163: assertEquals(query.getParameters().getOrderedNames().get(10),
0164: "propertyLong");
0165: assertEquals(query.getParameters().getOrderedNames().get(11),
0166: "propertyShort");
0167: assertEquals(query.getParameters().getOrderedNames().get(12),
0168: "propertySqlDate");
0169: assertEquals(query.getParameters().getOrderedNames().get(13),
0170: "propertyString");
0171: assertEquals(query.getParameters().getOrderedNames().get(14),
0172: "propertyStringbuffer");
0173: assertEquals(query.getParameters().getOrderedNames().get(15),
0174: "propertyTime");
0175: assertEquals(query.getParameters().getOrderedNames().get(16),
0176: "propertyTimestamp");
0177: assertTrue(Arrays.equals(query.getParameters()
0178: .getOrderedNamesArray(), new String[] { "nullColumn",
0179: "propertyBigDecimal", "propertyBoolean",
0180: "propertyByte", "propertyCalendar", "propertyChar",
0181: "propertyDate", "propertyDouble", "propertyFloat",
0182: "propertyInt", "propertyLong", "propertyShort",
0183: "propertySqlDate", "propertyString",
0184: "propertyStringbuffer", "propertyTime",
0185: "propertyTimestamp" }));
0186:
0187: assertEquals(
0188: query.getSql(),
0189: "UPDATE tablename SET nullColumn = ?, propertyBigDecimal = ?, propertyBoolean = ?, propertyByte = ?, propertyCalendar = ?, propertyChar = ?, propertyDate = ?, propertyDouble = ?, propertyFloat = ?, propertyInt = ?, propertyLong = ?, propertyShort = ?, propertySqlDate = ?, propertyString = ?, propertyStringbuffer = ?, propertyTime = ?, propertyTimestamp = ?");
0190:
0191: assertTrue(execute(query, new DbPreparedStatementHandler() {
0192: public void setParameters(DbPreparedStatement statement) {
0193: Calendar cal = Calendar.getInstance();
0194: cal.set(2002, 7, 19, 12, 17, 52);
0195: cal.set(Calendar.MILLISECOND, 462);
0196: statement.setString(1, null).setBigDecimal(2,
0197: new BigDecimal("98347.876438637")).setBoolean(
0198: 3, true).setByte(4, (byte) 16).setDate(5,
0199: new java.sql.Date(cal.getTime().getTime()))
0200: .setString(6, "M").setDate(
0201: 7,
0202: new java.sql.Date(cal.getTime()
0203: .getTime()))
0204: .setDouble(8, 12.3d).setFloat(9, 13.4f).setInt(
0205: 10, 34).setLong(11, 45L).setShort(12,
0206: (short) 12).setDate(
0207: 13,
0208: new java.sql.Date(cal.getTime()
0209: .getTime())).setString(14,
0210: "string'value").setString(15,
0211: "string'value2").setTime(16,
0212: new Time(cal.getTime().getTime()))
0213: .setTimestamp(17,
0214: new Timestamp(cal.getTime().getTime()));
0215: }
0216: }));
0217: }
0218:
0219: public void testFieldParametersMixedPgsql() {
0220: Update query = new Update(mPgsql);
0221: query.table("tablename");
0222:
0223: assertNull(query.getParameters());
0224:
0225: final Calendar cal = Calendar.getInstance();
0226: cal.set(2002, 7, 19, 12, 17, 52);
0227: cal.set(Calendar.MILLISECOND, 462);
0228: query.fieldParameter("nullColumn").field("propertyBigDecimal",
0229: new BigDecimal("98347.876438637")).fieldParameter(
0230: "propertyBoolean").fieldParameter("propertyByte")
0231: .field("propertyCalendar", cal.getTime())
0232: .fieldParameter("propertyChar").field("propertyDate",
0233: cal).field("propertyDouble", 12.3d)
0234: .fieldParameter("propertyFloat").fieldParameter(
0235: "propertyInt").field("propertyLong", 45L)
0236: .field("propertyShort", (short) 12).fieldParameter(
0237: "propertySqlDate").fieldParameter(
0238: "propertyString").field("propertyStringbuffer",
0239: new StringBuffer("stringbuffer'value")).field(
0240: "propertyTime",
0241: new Time(cal.getTime().getTime()))
0242: .fieldParameter("propertyTimestamp");
0243:
0244: assertEquals(query.getParameters().getOrderedNames().size(), 9);
0245: assertEquals(query.getParameters().getOrderedNames().get(0),
0246: "nullColumn");
0247: assertEquals(query.getParameters().getOrderedNames().get(1),
0248: "propertyBoolean");
0249: assertEquals(query.getParameters().getOrderedNames().get(2),
0250: "propertyByte");
0251: assertEquals(query.getParameters().getOrderedNames().get(3),
0252: "propertyChar");
0253: assertEquals(query.getParameters().getOrderedNames().get(4),
0254: "propertyFloat");
0255: assertEquals(query.getParameters().getOrderedNames().get(5),
0256: "propertyInt");
0257: assertEquals(query.getParameters().getOrderedNames().get(6),
0258: "propertySqlDate");
0259: assertEquals(query.getParameters().getOrderedNames().get(7),
0260: "propertyString");
0261: assertEquals(query.getParameters().getOrderedNames().get(8),
0262: "propertyTimestamp");
0263: assertTrue(Arrays.equals(query.getParameters()
0264: .getOrderedNamesArray(), new String[] { "nullColumn",
0265: "propertyBoolean", "propertyByte", "propertyChar",
0266: "propertyFloat", "propertyInt", "propertySqlDate",
0267: "propertyString", "propertyTimestamp" }));
0268:
0269: assertEquals(
0270: query.getSql(),
0271: "UPDATE tablename SET nullColumn = ?, propertyBigDecimal = 98347.876438637, propertyBoolean = ?, propertyByte = ?, propertyCalendar = '2002-08-19 12:17:52.462', propertyChar = ?, propertyDate = '2002-08-19 12:17:52.462', propertyDouble = 12.3, propertyFloat = ?, propertyInt = ?, propertyLong = 45, propertyShort = 12, propertySqlDate = ?, propertyString = ?, propertyStringbuffer = 'stringbuffer''value', propertyTime = '12:17:52', propertyTimestamp = ?");
0272:
0273: assertTrue(execute(query, new DbPreparedStatementHandler() {
0274: public void setParameters(DbPreparedStatement statement) {
0275: statement.setString(1, null).setBoolean(2, true)
0276: .setByte(3, (byte) 16).setString(4, "M")
0277: .setFloat(5, 13.4f).setInt(6, 34).setDate(
0278: 7,
0279: new java.sql.Date(cal.getTime()
0280: .getTime())).setString(8,
0281: "string'value").setTimestamp(9,
0282: new Timestamp(cal.getTime().getTime()));
0283: }
0284: }));
0285: }
0286:
0287: public void testFieldsPgsql() {
0288: Calendar cal = Calendar.getInstance();
0289: cal.set(2002, 7, 19, 12, 17, 52);
0290: cal.set(Calendar.MILLISECOND, 462);
0291: Update query = new Update(mPgsql);
0292: query.table("tablename").where("propertyByte = 89").fields(
0293: new Object[] { "nullColumn", SqlNull.NULL,
0294: "propertyBigDecimal",
0295: new BigDecimal("98347.876438637"),
0296: "propertyBoolean", new Boolean(true),
0297: "propertyByte", new Byte((byte) 16),
0298: "propertyCalendar", cal.getTime(),
0299: "propertyChar", new Character('M'),
0300: "propertyDate", cal, "propertyDouble",
0301: new Double(12.3d), "propertyFloat",
0302: new Float(13.4f), "propertyInt",
0303: new Integer(34), "propertyLong", new Long(45L),
0304: "propertyShort", new Short((short) 12),
0305: "propertySqlDate",
0306: new java.sql.Date(cal.getTime().getTime()),
0307: "propertyString", new String("string'value"),
0308: "propertyStringbuffer",
0309: new StringBuffer("stringbuffer'value"),
0310: "propertyTime",
0311: new Time(cal.getTime().getTime()),
0312: "propertyTimestamp",
0313: new Timestamp(cal.getTime().getTime()) });
0314: assertEquals(
0315: query.getSql(),
0316: "UPDATE tablename SET nullColumn = NULL, propertyBigDecimal = 98347.876438637, propertyBoolean = true, propertyByte = 16, propertyCalendar = '2002-08-19 12:17:52.462', propertyChar = 'M', propertyDate = '2002-08-19 12:17:52.462', propertyDouble = 12.3, propertyFloat = 13.4, propertyInt = 34, propertyLong = 45, propertyShort = 12, propertySqlDate = '2002-08-19', propertyString = 'string''value', propertyStringbuffer = 'stringbuffer''value', propertyTime = '12:17:52', propertyTimestamp = '2002-08-19 12:17:52.462' WHERE propertyByte = 89");
0317: assertTrue(execute(query));
0318: }
0319:
0320: public void testWhereConstructionPgsql() {
0321: Update query = new Update(mPgsql);
0322: query.table("tablename").field("propertyBoolean", true).field(
0323: "propertyByte", (byte) 16).where("propertyInt = 545")
0324: .whereAnd("propertyLong < 50000").whereOr(
0325: "propertyChar = 'v'");
0326: assertEquals(
0327: query.getSql(),
0328: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyInt = 545 AND propertyLong < 50000 OR propertyChar = 'v'");
0329: assertTrue(execute(query));
0330: }
0331:
0332: public void testWhereConstructionGroupPgsql() {
0333: Update query = new Update(mPgsql);
0334: query.table("tablename").field("propertyBoolean", true).field(
0335: "propertyByte", (byte) 16).where("propertyInt = 545")
0336: .whereAnd("propertyLong < 50000").startWhereOr()
0337: .whereParameter("propertyString", "=").whereAnd(
0338: "propertyByte", "<=", (byte) 0).startWhereAnd()
0339: .where("propertyBoolean", "!=", true).whereParameterOr(
0340: "propertyStringbuffer", "LIKE").end().end()
0341: .whereOr("propertyChar = 'v'");
0342:
0343: assertEquals(query.getParameters().getOrderedNames().get(0),
0344: "propertyString");
0345: assertEquals(query.getParameters().getOrderedNames().get(1),
0346: "propertyStringbuffer");
0347: assertTrue(Arrays.equals(query.getParameters()
0348: .getOrderedNamesArray(), new String[] {
0349: "propertyString", "propertyStringbuffer" }));
0350:
0351: assertEquals(
0352: query.getSql(),
0353: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyInt = 545 AND propertyLong < 50000 OR (propertyString = ? AND propertyByte <= 0 AND (propertyBoolean != true OR propertyStringbuffer LIKE ?)) OR propertyChar = 'v'");
0354:
0355: assertTrue(execute(query, new DbPreparedStatementHandler() {
0356: public void setParameters(DbPreparedStatement statement) {
0357: statement
0358: .setString("propertyString", "someotherstring")
0359: .setString("propertyStringbuffer", "stringbuff");
0360: }
0361: }));
0362: }
0363:
0364: public void testWhereTypedPgsql() {
0365: Update query = new Update(mPgsql);
0366: query.table("tablename").field("propertyBoolean", true).field(
0367: "propertyByte", (byte) 16);
0368:
0369: Calendar cal = Calendar.getInstance();
0370: cal.set(2003, 2, 3, 10, 1, 28);
0371: cal.set(Calendar.MILLISECOND, 154);
0372:
0373: query.where("propertyBigDecimal", ">=",
0374: new BigDecimal("53443433.9784567")).whereAnd(
0375: "propertyBoolean", "=", false).whereOr("propertyByte",
0376: "=", (byte) 54).whereAnd("propertyCalendar", "<=", cal)
0377: .whereOr("propertyChar", "=", 'f').whereAnd(
0378: "propertyDate", "=", cal.getTime()).whereAnd(
0379: "propertyDouble", "!=", 73453.71d).whereOr(
0380: "propertyFloat", ">=", 1987.14f).whereAnd(
0381: "propertyInt", "=", 973).whereAnd(
0382: "propertyLong", "<", 347678L).whereAnd(
0383: "propertyShort", "=", (short) 78).whereOr(
0384: "propertySqlDate", "=",
0385: new java.sql.Date(cal.getTime().getTime()))
0386: .whereAnd("propertyString", "LIKE", "someotherstring%")
0387: .whereAnd("propertyStringbuffer", "=",
0388: new StringBuffer("someotherstringbuff"))
0389: .whereOr("propertyTime", "=",
0390: new Time(cal.getTime().getTime())).whereAnd(
0391: "propertyTimestamp", "<=",
0392: new Timestamp(cal.getTime().getTime()));
0393:
0394: assertEquals(
0395: query.getSql(),
0396: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyBigDecimal >= 53443433.9784567 AND propertyBoolean = false OR propertyByte = 54 AND propertyCalendar <= '2003-03-03 10:01:28.154' OR propertyChar = 'f' AND propertyDate = '2003-03-03 10:01:28.154' AND propertyDouble != 73453.71 OR propertyFloat >= 1987.14 AND propertyInt = 973 AND propertyLong < 347678 AND propertyShort = 78 OR propertySqlDate = '2003-03-03' AND propertyString LIKE 'someotherstring%' AND propertyStringbuffer = 'someotherstringbuff' OR propertyTime = '10:01:28' AND propertyTimestamp <= '2003-03-03 10:01:28.154'");
0397: assertFalse(execute(query));
0398: }
0399:
0400: public void testWhereTypedMixedPgsql() {
0401: Update query = new Update(mPgsql);
0402: query.table("tablename").field("propertyBoolean", true).field(
0403: "propertyByte", (byte) 16);
0404:
0405: final Calendar cal = Calendar.getInstance();
0406: cal.set(2003, 2, 3, 10, 1, 28);
0407: cal.set(Calendar.MILLISECOND, 154);
0408:
0409: query.where("propertyBigDecimal", ">=",
0410: new BigDecimal("53443433.9784567")).whereAnd(
0411: "propertyBoolean", "=", false).whereOr(
0412: "propertyByte = 54").whereAnd("propertyCalendar", "<=",
0413: cal).whereOr("propertyChar", "=", 'f').whereAnd(
0414: "propertyDate", "=", cal.getTime()).whereAnd(
0415: "propertyDouble", "!=", 73453.71d).whereOr(
0416: "propertyFloat >= 1987.14").whereAnd("propertyInt",
0417: "=", 973).whereAnd("propertyLong", "<", 347678L)
0418: .whereAnd("propertyShort", "=", (short) 78)
0419: .whereParameterOr("propertySqlDate", "=").whereAnd(
0420: "propertyString", "LIKE", "someotherstring%")
0421: .whereAnd("propertyStringbuffer", "=",
0422: new StringBuffer("someotherstringbuff"))
0423: .whereOr("propertyTime", "=",
0424: new Time(cal.getTime().getTime())).whereAnd(
0425: "propertyTimestamp", "<=",
0426: new Timestamp(cal.getTime().getTime()));
0427:
0428: assertEquals(
0429: query.getSql(),
0430: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyBigDecimal >= 53443433.9784567 AND propertyBoolean = false OR propertyByte = 54 AND propertyCalendar <= '2003-03-03 10:01:28.154' OR propertyChar = 'f' AND propertyDate = '2003-03-03 10:01:28.154' AND propertyDouble != 73453.71 OR propertyFloat >= 1987.14 AND propertyInt = 973 AND propertyLong < 347678 AND propertyShort = 78 OR propertySqlDate = ? AND propertyString LIKE 'someotherstring%' AND propertyStringbuffer = 'someotherstringbuff' OR propertyTime = '10:01:28' AND propertyTimestamp <= '2003-03-03 10:01:28.154'");
0431:
0432: assertFalse(execute(query, new DbPreparedStatementHandler() {
0433: public void setParameters(DbPreparedStatement statement) {
0434: statement.setDate("propertySqlDate", new java.sql.Date(
0435: cal.getTime().getTime()));
0436: }
0437: }));
0438: }
0439:
0440: public void testWhereParametersPgsql() {
0441: Update query = new Update(mPgsql);
0442: query.table("tablename").field("propertyBoolean", true).field(
0443: "propertyByte", (byte) 16);
0444:
0445: assertNull(query.getParameters());
0446:
0447: query.whereParameter("propertyInt", "=").whereParameterAnd(
0448: "propertyLong", "<").whereParameterOr("propertyChar",
0449: "=");
0450:
0451: assertEquals(query.getParameters().getOrderedNames().size(), 3);
0452: assertEquals(query.getParameters().getOrderedNames().get(0),
0453: "propertyInt");
0454: assertEquals(query.getParameters().getOrderedNames().get(1),
0455: "propertyLong");
0456: assertEquals(query.getParameters().getOrderedNames().get(2),
0457: "propertyChar");
0458: assertTrue(Arrays.equals(query.getParameters()
0459: .getOrderedNamesArray(), new String[] { "propertyInt",
0460: "propertyLong", "propertyChar" }));
0461:
0462: assertEquals(
0463: query.getSql(),
0464: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyInt = ? AND propertyLong < ? OR propertyChar = ?");
0465: assertTrue(execute(query, new DbPreparedStatementHandler() {
0466: public void setParameters(DbPreparedStatement statement) {
0467: statement.setInt(1, 545).setLong(2, 50000).setString(3,
0468: "v");
0469: }
0470: }));
0471:
0472: query.where("propertyInt = 545");
0473: assertNull(query.getParameters());
0474: assertEquals(
0475: query.getSql(),
0476: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyInt = 545");
0477: }
0478:
0479: public void testWhereParametersMixedPgsql() {
0480: Update query = new Update(mPgsql);
0481: query.table("tablename").field("propertyBoolean", true).field(
0482: "propertyByte", (byte) 16).where("propertyInt = 545")
0483: .whereParameterAnd("propertyLong", "<")
0484: .whereParameterOr("propertyChar", "=");
0485:
0486: assertEquals(query.getParameters().getOrderedNames().get(0),
0487: "propertyLong");
0488: assertEquals(query.getParameters().getOrderedNames().get(1),
0489: "propertyChar");
0490: assertTrue(Arrays.equals(query.getParameters()
0491: .getOrderedNamesArray(), new String[] { "propertyLong",
0492: "propertyChar" }));
0493:
0494: assertEquals(
0495: query.getSql(),
0496: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyInt = 545 AND propertyLong < ? OR propertyChar = ?");
0497: assertTrue(execute(query, new DbPreparedStatementHandler() {
0498: public void setParameters(DbPreparedStatement statement) {
0499: statement.setLong(1, 50000).setString(2, "v");
0500: }
0501: }));
0502:
0503: query.where("propertyInt = 545");
0504:
0505: assertNull(query.getParameters());
0506: assertEquals(
0507: query.getSql(),
0508: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyInt = 545");
0509: }
0510:
0511: public void testFieldWhereParametersPgsql() {
0512: Update query = new Update(mPgsql);
0513: query.table("tablename");
0514:
0515: assertNull(query.getParameters());
0516:
0517: query.fieldParameter("propertyBoolean").fieldParameter(
0518: "propertyByte");
0519:
0520: assertEquals(query.getParameters().getOrderedNames().size(), 2);
0521: assertEquals(query.getParameters().getOrderedNames().get(0),
0522: "propertyBoolean");
0523: assertEquals(query.getParameters().getOrderedNames().get(1),
0524: "propertyByte");
0525: assertTrue(Arrays.equals(query.getParameters()
0526: .getOrderedNamesArray(), new String[] {
0527: "propertyBoolean", "propertyByte" }));
0528:
0529: query.whereParameter("propertyInt", "=").whereParameterAnd(
0530: "propertyLong", "<").whereParameterOr("propertyChar",
0531: "=");
0532:
0533: assertEquals(query.getParameters().getOrderedNames().size(), 5);
0534: assertEquals(query.getParameters().getOrderedNames().get(0),
0535: "propertyBoolean");
0536: assertEquals(query.getParameters().getOrderedNames().get(1),
0537: "propertyByte");
0538: assertEquals(query.getParameters().getOrderedNames().get(2),
0539: "propertyInt");
0540: assertEquals(query.getParameters().getOrderedNames().get(3),
0541: "propertyLong");
0542: assertEquals(query.getParameters().getOrderedNames().get(4),
0543: "propertyChar");
0544: assertTrue(Arrays.equals(query.getParameters()
0545: .getOrderedNamesArray(), new String[] {
0546: "propertyBoolean", "propertyByte", "propertyInt",
0547: "propertyLong", "propertyChar" }));
0548:
0549: assertEquals(
0550: query.getSql(),
0551: "UPDATE tablename SET propertyBoolean = ?, propertyByte = ? WHERE propertyInt = ? AND propertyLong < ? OR propertyChar = ?");
0552: assertTrue(execute(query, new DbPreparedStatementHandler() {
0553: public void setParameters(DbPreparedStatement statement) {
0554: statement.setBoolean(1, true).setByte(2, (byte) 16)
0555: .setInt(3, 545).setLong(4, 50000).setString(5,
0556: "v");
0557: }
0558: }));
0559:
0560: query.where("propertyInt = 545");
0561:
0562: assertEquals(query.getParameters().getOrderedNames().size(), 2);
0563: assertEquals(
0564: query.getParameters().getOrderedNamesArray().length, 2);
0565: assertEquals(query.getParameters().getOrderedNames().get(0),
0566: "propertyBoolean");
0567: assertEquals(query.getParameters().getOrderedNames().get(1),
0568: "propertyByte");
0569: assertTrue(Arrays.equals(query.getParameters()
0570: .getOrderedNamesArray(), new String[] {
0571: "propertyBoolean", "propertyByte" }));
0572: assertEquals(
0573: query.getSql(),
0574: "UPDATE tablename SET propertyBoolean = ?, propertyByte = ? WHERE propertyInt = 545");
0575: }
0576:
0577: public void testFieldsBeanPgsql() {
0578: Update query = new Update(mPgsql);
0579: query.table("tablename").where("propertyInt = 545").fields(
0580: BeanImpl.getPopulatedBean());
0581: assertEquals(
0582: query.getSql(),
0583: "UPDATE tablename SET propertyBigDecimal = 219038743.392874, propertyBoolean = true, propertyBooleanObject = false, propertyByte = 89, propertyByteObject = 34, propertyCalendar = '2002-06-18 15:26:14.764', propertyChar = 'v', propertyCharacterObject = 'r', propertyDate = '2002-06-18 15:26:14.764', propertyDouble = 53348.34, propertyDoubleObject = 143298.692, propertyEnum = 'VALUE_THREE', propertyFloat = 98634.2, propertyFloatObject = 8734.7, propertyInt = 545, propertyIntegerObject = 968, propertyLong = 34563, propertyLongObject = 66875, propertyShort = 43, propertyShortObject = 68, propertySqlDate = '2002-06-18', propertyString = 'someotherstring', propertyStringbuffer = 'someotherstringbuff', propertyTime = '15:26:14', propertyTimestamp = '2002-06-18 15:26:14.764' WHERE propertyInt = 545");
0584: assertTrue(execute(query));
0585: }
0586:
0587: public void testFieldsBeanConstrainedPgsql() {
0588: Update query = new Update(mPgsql);
0589: query.table("tablename").where("propertyInt = 545").fields(
0590: BeanImplConstrained.getPopulatedBean());
0591: assertEquals(
0592: query.getSql(),
0593: "UPDATE tablename SET propertyBigDecimal = 219038743.392874, propertyBoolean = true, propertyBooleanObject = false, propertyByteObject = 34, propertyCalendar = '2002-06-18 15:26:14.764', propertyChar = 'v', propertyCharacterObject = 'r', propertyDate = '2002-06-18 15:26:14.764', propertyDouble = 53348.34, propertyDoubleObject = 143298.692, propertyFloat = 98634.2, propertyFloatObject = 8734.7, propertyInt = 545, propertyIntegerObject = 968, propertyLongObject = 66875, propertyShort = 43, propertySqlDate = '2002-06-18', propertyString = 'someotherstring', propertyStringbuffer = 'someotherstringbuff', propertyTime = '15:26:14', propertyTimestamp = '2002-06-18 15:26:14.764' WHERE propertyInt = 545");
0594: assertTrue(execute(query));
0595: }
0596:
0597: public void testFieldsBeanNullValuesPgsql() {
0598: Update query = new Update(mPgsql);
0599: query.table("tablename").where("propertyInt = 545").fields(
0600: BeanImpl.getNullBean());
0601: assertEquals(
0602: query.getSql(),
0603: "UPDATE tablename SET propertyBoolean = false, propertyBooleanObject = false, propertyByte = 0, propertyByteObject = 0, propertyDouble = 0.0, propertyDoubleObject = 0.0, propertyFloat = 0.0, propertyFloatObject = 0.0, propertyInt = 0, propertyIntegerObject = 0, propertyLong = 0, propertyLongObject = 0, propertyShort = 0, propertyShortObject = 0 WHERE propertyInt = 545");
0604: assertTrue(execute(query));
0605: }
0606:
0607: public void testFieldsBeanIncludedPgsql() {
0608: Update query = new Update(mPgsql);
0609: query
0610: .table("tablename")
0611: .where("propertyInt = 545")
0612: .fieldsIncluded(
0613: BeanImpl.getPopulatedBean(),
0614: new String[] { "propertyByte",
0615: "propertyDouble", "propertyShort",
0616: "propertyStringbuffer", "propertyTime" });
0617: assertEquals(
0618: query.getSql(),
0619: "UPDATE tablename SET propertyByte = 89, propertyDouble = 53348.34, propertyShort = 43, propertyStringbuffer = 'someotherstringbuff', propertyTime = '15:26:14' WHERE propertyInt = 545");
0620: assertTrue(execute(query));
0621: }
0622:
0623: public void testFieldsBeanExcludedPgsql() {
0624: Update query = new Update(mPgsql);
0625: query
0626: .table("tablename")
0627: .where("propertyInt = 545")
0628: .fieldsExcluded(
0629: BeanImpl.getPopulatedBean(),
0630: new String[] { "propertyByte",
0631: "propertyDouble", "propertyShort",
0632: "propertyStringbuffer", "propertyTime" });
0633: assertEquals(
0634: query.getSql(),
0635: "UPDATE tablename SET propertyBigDecimal = 219038743.392874, propertyBoolean = true, propertyBooleanObject = false, propertyByteObject = 34, propertyCalendar = '2002-06-18 15:26:14.764', propertyChar = 'v', propertyCharacterObject = 'r', propertyDate = '2002-06-18 15:26:14.764', propertyDoubleObject = 143298.692, propertyEnum = 'VALUE_THREE', propertyFloat = 98634.2, propertyFloatObject = 8734.7, propertyInt = 545, propertyIntegerObject = 968, propertyLong = 34563, propertyLongObject = 66875, propertyShortObject = 68, propertySqlDate = '2002-06-18', propertyString = 'someotherstring', propertyTimestamp = '2002-06-18 15:26:14.764' WHERE propertyInt = 545");
0636: assertTrue(execute(query));
0637: }
0638:
0639: public void testFieldsBeanFilteredPgsql() {
0640: Update query = new Update(mPgsql);
0641: query
0642: .table("tablename")
0643: .where("propertyInt = 545")
0644: .fieldsFiltered(
0645: BeanImpl.getPopulatedBean(),
0646: new String[] { "propertyByte",
0647: "propertyDouble", "propertyShort",
0648: "propertyStringbuffer", "propertyTime" },
0649: new String[] { "propertyByte", "propertyShort",
0650: "propertyTime" });
0651: assertEquals(
0652: query.getSql(),
0653: "UPDATE tablename SET propertyDouble = 53348.34, propertyStringbuffer = 'someotherstringbuff' WHERE propertyInt = 545");
0654: assertTrue(execute(query));
0655: }
0656:
0657: public void testFieldsParametersBeanPgsql() {
0658: Update query = new Update(mPgsql);
0659: query.table("tablename").fieldsParameters(BeanImpl.class);
0660: assertEquals(
0661: query.getSql(),
0662: "UPDATE tablename SET propertyBigDecimal = ?, propertyBoolean = ?, propertyBooleanObject = ?, propertyByte = ?, propertyByteObject = ?, propertyCalendar = ?, propertyChar = ?, propertyCharacterObject = ?, propertyDate = ?, propertyDouble = ?, propertyDoubleObject = ?, propertyEnum = ?, propertyFloat = ?, propertyFloatObject = ?, propertyInt = ?, propertyIntegerObject = ?, propertyLong = ?, propertyLongObject = ?, propertyShort = ?, propertyShortObject = ?, propertySqlDate = ?, propertyString = ?, propertyStringbuffer = ?, propertyTime = ?, propertyTimestamp = ?");
0663:
0664: assertEquals(query.getParameters().getOrderedNames().size(), 25);
0665: assertEquals(query.getParameters().getOrderedNames().get(0),
0666: "propertyBigDecimal");
0667: assertEquals(query.getParameters().getOrderedNames().get(1),
0668: "propertyBoolean");
0669: assertEquals(query.getParameters().getOrderedNames().get(2),
0670: "propertyBooleanObject");
0671: assertEquals(query.getParameters().getOrderedNames().get(3),
0672: "propertyByte");
0673: assertEquals(query.getParameters().getOrderedNames().get(4),
0674: "propertyByteObject");
0675: assertEquals(query.getParameters().getOrderedNames().get(5),
0676: "propertyCalendar");
0677: assertEquals(query.getParameters().getOrderedNames().get(6),
0678: "propertyChar");
0679: assertEquals(query.getParameters().getOrderedNames().get(7),
0680: "propertyCharacterObject");
0681: assertEquals(query.getParameters().getOrderedNames().get(8),
0682: "propertyDate");
0683: assertEquals(query.getParameters().getOrderedNames().get(9),
0684: "propertyDouble");
0685: assertEquals(query.getParameters().getOrderedNames().get(10),
0686: "propertyDoubleObject");
0687: assertEquals(query.getParameters().getOrderedNames().get(11),
0688: "propertyEnum");
0689: assertEquals(query.getParameters().getOrderedNames().get(12),
0690: "propertyFloat");
0691: assertEquals(query.getParameters().getOrderedNames().get(13),
0692: "propertyFloatObject");
0693: assertEquals(query.getParameters().getOrderedNames().get(14),
0694: "propertyInt");
0695: assertEquals(query.getParameters().getOrderedNames().get(15),
0696: "propertyIntegerObject");
0697: assertEquals(query.getParameters().getOrderedNames().get(16),
0698: "propertyLong");
0699: assertEquals(query.getParameters().getOrderedNames().get(17),
0700: "propertyLongObject");
0701: assertEquals(query.getParameters().getOrderedNames().get(18),
0702: "propertyShort");
0703: assertEquals(query.getParameters().getOrderedNames().get(19),
0704: "propertyShortObject");
0705: assertEquals(query.getParameters().getOrderedNames().get(20),
0706: "propertySqlDate");
0707: assertEquals(query.getParameters().getOrderedNames().get(21),
0708: "propertyString");
0709: assertEquals(query.getParameters().getOrderedNames().get(22),
0710: "propertyStringbuffer");
0711: assertEquals(query.getParameters().getOrderedNames().get(23),
0712: "propertyTime");
0713: assertEquals(query.getParameters().getOrderedNames().get(24),
0714: "propertyTimestamp");
0715: assertTrue(Arrays.equals(query.getParameters()
0716: .getOrderedNamesArray(), new String[] {
0717: "propertyBigDecimal", "propertyBoolean",
0718: "propertyBooleanObject", "propertyByte",
0719: "propertyByteObject", "propertyCalendar",
0720: "propertyChar", "propertyCharacterObject",
0721: "propertyDate", "propertyDouble",
0722: "propertyDoubleObject", "propertyEnum",
0723: "propertyFloat", "propertyFloatObject", "propertyInt",
0724: "propertyIntegerObject", "propertyLong",
0725: "propertyLongObject", "propertyShort",
0726: "propertyShortObject", "propertySqlDate",
0727: "propertyString", "propertyStringbuffer",
0728: "propertyTime", "propertyTimestamp" }));
0729:
0730: assertTrue(execute(query, new DbPreparedStatementHandler() {
0731: public void setParameters(DbPreparedStatement statement) {
0732: Calendar cal = Calendar.getInstance();
0733: cal.set(2002, 7, 19, 12, 17, 52);
0734: cal.set(Calendar.MILLISECOND, 462);
0735: statement.setBigDecimal(1,
0736: new BigDecimal("98347.876438637")).setBoolean(
0737: 2, false).setBoolean(3, true).setByte(4,
0738: (byte) 16).setByte(5, (byte) 72)
0739: .setTimestamp(
0740: 6,
0741: new java.sql.Timestamp(cal.getTime()
0742: .getTime())).setString(7, "M")
0743: .setString(8, "p").setTimestamp(
0744: 9,
0745: new java.sql.Timestamp(cal.getTime()
0746: .getTime())).setDouble(10,
0747: 12.3d).setDouble(11, 68.7d).setString(
0748: 12, "VALUE_THREE").setFloat(13, 13.4f)
0749: .setFloat(14, 42.1f).setInt(15, 92).setInt(16,
0750: 34).setLong(17, 687L).setLong(18, 92)
0751: .setShort(19, (short) 7).setShort(20,
0752: (short) 12).setDate(
0753: 21,
0754: new java.sql.Date(cal.getTime()
0755: .getTime())).setString(22,
0756: "string'value").setString(23,
0757: "string'value2").setTime(24,
0758: new Time(cal.getTime().getTime()))
0759: .setTimestamp(25,
0760: new Timestamp(cal.getTime().getTime()));
0761: }
0762: }));
0763: }
0764:
0765: public void testFieldsParametersBeanConstrainedPgsql() {
0766: Update query = new Update(mPgsql);
0767: query.table("tablename").fieldsParameters(
0768: BeanImplConstrained.class);
0769: assertEquals(
0770: query.getSql(),
0771: "UPDATE tablename SET propertyBigDecimal = ?, propertyBoolean = ?, propertyBooleanObject = ?, propertyByteObject = ?, propertyCalendar = ?, propertyChar = ?, propertyCharacterObject = ?, propertyDate = ?, propertyDouble = ?, propertyDoubleObject = ?, propertyFloat = ?, propertyFloatObject = ?, propertyInt = ?, propertyIntegerObject = ?, propertyLongObject = ?, propertyShort = ?, propertySqlDate = ?, propertyString = ?, propertyStringbuffer = ?, propertyTime = ?, propertyTimestamp = ?");
0772:
0773: assertEquals(query.getParameters().getOrderedNames().size(), 21);
0774: assertEquals(query.getParameters().getOrderedNames().get(0),
0775: "propertyBigDecimal");
0776: assertEquals(query.getParameters().getOrderedNames().get(1),
0777: "propertyBoolean");
0778: assertEquals(query.getParameters().getOrderedNames().get(2),
0779: "propertyBooleanObject");
0780: assertEquals(query.getParameters().getOrderedNames().get(3),
0781: "propertyByteObject");
0782: assertEquals(query.getParameters().getOrderedNames().get(4),
0783: "propertyCalendar");
0784: assertEquals(query.getParameters().getOrderedNames().get(5),
0785: "propertyChar");
0786: assertEquals(query.getParameters().getOrderedNames().get(6),
0787: "propertyCharacterObject");
0788: assertEquals(query.getParameters().getOrderedNames().get(7),
0789: "propertyDate");
0790: assertEquals(query.getParameters().getOrderedNames().get(8),
0791: "propertyDouble");
0792: assertEquals(query.getParameters().getOrderedNames().get(9),
0793: "propertyDoubleObject");
0794: assertEquals(query.getParameters().getOrderedNames().get(10),
0795: "propertyFloat");
0796: assertEquals(query.getParameters().getOrderedNames().get(11),
0797: "propertyFloatObject");
0798: assertEquals(query.getParameters().getOrderedNames().get(12),
0799: "propertyInt");
0800: assertEquals(query.getParameters().getOrderedNames().get(13),
0801: "propertyIntegerObject");
0802: assertEquals(query.getParameters().getOrderedNames().get(14),
0803: "propertyLongObject");
0804: assertEquals(query.getParameters().getOrderedNames().get(15),
0805: "propertyShort");
0806: assertEquals(query.getParameters().getOrderedNames().get(16),
0807: "propertySqlDate");
0808: assertEquals(query.getParameters().getOrderedNames().get(17),
0809: "propertyString");
0810: assertEquals(query.getParameters().getOrderedNames().get(18),
0811: "propertyStringbuffer");
0812: assertEquals(query.getParameters().getOrderedNames().get(19),
0813: "propertyTime");
0814: assertEquals(query.getParameters().getOrderedNames().get(20),
0815: "propertyTimestamp");
0816: assertTrue(Arrays.equals(query.getParameters()
0817: .getOrderedNamesArray(), new String[] {
0818: "propertyBigDecimal", "propertyBoolean",
0819: "propertyBooleanObject", "propertyByteObject",
0820: "propertyCalendar", "propertyChar",
0821: "propertyCharacterObject", "propertyDate",
0822: "propertyDouble", "propertyDoubleObject",
0823: "propertyFloat", "propertyFloatObject", "propertyInt",
0824: "propertyIntegerObject", "propertyLongObject",
0825: "propertyShort", "propertySqlDate", "propertyString",
0826: "propertyStringbuffer", "propertyTime",
0827: "propertyTimestamp" }));
0828:
0829: assertTrue(execute(query, new DbPreparedStatementHandler() {
0830: public void setParameters(DbPreparedStatement statement) {
0831: Calendar cal = Calendar.getInstance();
0832: cal.set(2002, 7, 19, 12, 17, 52);
0833: cal.set(Calendar.MILLISECOND, 462);
0834: statement.setBigDecimal(1,
0835: new BigDecimal("98347.876438637")).setBoolean(
0836: 2, false).setBoolean(3, true).setByte(4,
0837: (byte) 72)
0838: .setTimestamp(
0839: 5,
0840: new java.sql.Timestamp(cal.getTime()
0841: .getTime())).setString(6, "M")
0842: .setString(7, "p").setTimestamp(
0843: 8,
0844: new java.sql.Timestamp(cal.getTime()
0845: .getTime()))
0846: .setDouble(9, 12.3d).setDouble(10, 68.7d)
0847: .setFloat(11, 13.4f).setFloat(12, 42.1f)
0848: .setInt(13, 92).setInt(14, 34).setLong(15, 92)
0849: .setShort(16, (short) 7).setDate(
0850: 17,
0851: new java.sql.Date(cal.getTime()
0852: .getTime())).setString(18,
0853: "string'value").setString(19,
0854: "string'value2").setTime(20,
0855: new Time(cal.getTime().getTime()))
0856: .setTimestamp(21,
0857: new Timestamp(cal.getTime().getTime()));
0858: }
0859: }));
0860: }
0861:
0862: public void testFieldsParametersBeanExcludedPgsql() {
0863: Update query = new Update(mPgsql);
0864: query.table("tablename").fieldsParametersExcluded(
0865: BeanImpl.class,
0866: new String[] { "propertyBoolean", "propertyByte",
0867: "propertyChar", "propertyDouble",
0868: "propertyInt", "propertyLong",
0869: "propertySqlDate", "propertyStringbuffer",
0870: "propertyTimestamp" });
0871: assertEquals(
0872: query.getSql(),
0873: "UPDATE tablename SET propertyBigDecimal = ?, propertyBooleanObject = ?, propertyByteObject = ?, propertyCalendar = ?, propertyCharacterObject = ?, propertyDate = ?, propertyDoubleObject = ?, propertyEnum = ?, propertyFloat = ?, propertyFloatObject = ?, propertyIntegerObject = ?, propertyLongObject = ?, propertyShort = ?, propertyShortObject = ?, propertyString = ?, propertyTime = ?");
0874:
0875: assertEquals(query.getParameters().getOrderedNames().size(), 16);
0876: assertEquals(query.getParameters().getOrderedNames().get(0),
0877: "propertyBigDecimal");
0878: assertEquals(query.getParameters().getOrderedNames().get(1),
0879: "propertyBooleanObject");
0880: assertEquals(query.getParameters().getOrderedNames().get(2),
0881: "propertyByteObject");
0882: assertEquals(query.getParameters().getOrderedNames().get(3),
0883: "propertyCalendar");
0884: assertEquals(query.getParameters().getOrderedNames().get(4),
0885: "propertyCharacterObject");
0886: assertEquals(query.getParameters().getOrderedNames().get(5),
0887: "propertyDate");
0888: assertEquals(query.getParameters().getOrderedNames().get(6),
0889: "propertyDoubleObject");
0890: assertEquals(query.getParameters().getOrderedNames().get(7),
0891: "propertyEnum");
0892: assertEquals(query.getParameters().getOrderedNames().get(8),
0893: "propertyFloat");
0894: assertEquals(query.getParameters().getOrderedNames().get(9),
0895: "propertyFloatObject");
0896: assertEquals(query.getParameters().getOrderedNames().get(10),
0897: "propertyIntegerObject");
0898: assertEquals(query.getParameters().getOrderedNames().get(11),
0899: "propertyLongObject");
0900: assertEquals(query.getParameters().getOrderedNames().get(12),
0901: "propertyShort");
0902: assertEquals(query.getParameters().getOrderedNames().get(13),
0903: "propertyShortObject");
0904: assertEquals(query.getParameters().getOrderedNames().get(14),
0905: "propertyString");
0906: assertEquals(query.getParameters().getOrderedNames().get(15),
0907: "propertyTime");
0908: assertTrue(Arrays.equals(query.getParameters()
0909: .getOrderedNamesArray(), new String[] {
0910: "propertyBigDecimal", "propertyBooleanObject",
0911: "propertyByteObject", "propertyCalendar",
0912: "propertyCharacterObject", "propertyDate",
0913: "propertyDoubleObject", "propertyEnum",
0914: "propertyFloat", "propertyFloatObject",
0915: "propertyIntegerObject", "propertyLongObject",
0916: "propertyShort", "propertyShortObject",
0917: "propertyString", "propertyTime" }));
0918:
0919: assertTrue(execute(query, new DbPreparedStatementHandler() {
0920: public void setParameters(DbPreparedStatement statement) {
0921: Calendar cal = Calendar.getInstance();
0922: cal.set(2002, 7, 19, 12, 17, 52);
0923: cal.set(Calendar.MILLISECOND, 462);
0924: statement.setBigDecimal(1,
0925: new BigDecimal("98347.876438637")).setBoolean(
0926: 2, true).setByte(3, (byte) 72)
0927: .setTimestamp(
0928: 4,
0929: new java.sql.Timestamp(cal.getTime()
0930: .getTime())).setString(5, "o")
0931: .setTimestamp(
0932: 6,
0933: new java.sql.Timestamp(cal.getTime()
0934: .getTime()))
0935: .setDouble(7, 86.7d)
0936: .setString(8, "VALUE_THREE").setFloat(9, 13.4f)
0937: .setFloat(10, 32.8f).setInt(11, 358).setLong(
0938: 12, 9680L).setShort(13, (short) 12)
0939: .setShort(14, (short) 78).setString(15,
0940: "string'value").setTime(16,
0941: new Time(cal.getTime().getTime()));
0942: }
0943: }));
0944: }
0945:
0946: public void testWhereBeanPgsql() {
0947: Update query = new Update(mPgsql);
0948: query.table("tablename").field("propertyBoolean", true).field(
0949: "propertyByte", (byte) 16).where(
0950: BeanImpl.getPopulatedBean());
0951: assertEquals(
0952: query.getSql(),
0953: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyBigDecimal = 219038743.392874 AND propertyBoolean = true AND propertyBooleanObject = false AND propertyByte = 89 AND propertyByteObject = 34 AND propertyCalendar = '2002-06-18 15:26:14.764' AND propertyChar = 'v' AND propertyCharacterObject = 'r' AND propertyDate = '2002-06-18 15:26:14.764' AND propertyDouble = 53348.34 AND propertyDoubleObject = 143298.692 AND propertyEnum = 'VALUE_THREE' AND propertyFloat = 98634.2 AND propertyFloatObject = 8734.7 AND propertyInt = 545 AND propertyIntegerObject = 968 AND propertyLong = 34563 AND propertyLongObject = 66875 AND propertyShort = 43 AND propertyShortObject = 68 AND propertySqlDate = '2002-06-18' AND propertyString = 'someotherstring' AND propertyStringbuffer = 'someotherstringbuff' AND propertyTime = '15:26:14' AND propertyTimestamp = '2002-06-18 15:26:14.764'");
0954: assertTrue(execute(query));
0955: }
0956:
0957: public void testWhereBeanConstrainedPgsql() {
0958: Update query = new Update(mPgsql);
0959: query.table("tablename").field("propertyBoolean", true).field(
0960: "propertyByte", (byte) 16).where(
0961: BeanImplConstrained.getPopulatedBean());
0962: assertEquals(
0963: query.getSql(),
0964: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyBigDecimal = 219038743.392874 AND propertyBoolean = true AND propertyBooleanObject = false AND propertyByte = 89 AND propertyByteObject = 34 AND propertyCalendar = '2002-06-18 15:26:14.764' AND propertyChar = 'v' AND propertyCharacterObject = 'r' AND propertyDate = '2002-06-18 15:26:14.764' AND propertyDouble = 53348.34 AND propertyDoubleObject = 143298.692 AND propertyFloat = 98634.2 AND propertyFloatObject = 8734.7 AND propertyInt = 545 AND propertyIntegerObject = 968 AND propertyLongObject = 66875 AND propertyShort = 43 AND propertySqlDate = '2002-06-18' AND propertyString = 'someotherstring' AND propertyStringbuffer = 'someotherstringbuff' AND propertyTime = '15:26:14' AND propertyTimestamp = '2002-06-18 15:26:14.764'");
0965: assertTrue(execute(query));
0966: }
0967:
0968: public void testWhereBeanNullValuesPgsql() {
0969: Update query = new Update(mPgsql);
0970: query.table("tablename").field("propertyBoolean", true).field(
0971: "propertyByte", (byte) 16)
0972: .where(BeanImpl.getNullBean());
0973: assertEquals(
0974: query.getSql(),
0975: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyBoolean = false AND propertyBooleanObject = false AND propertyByte = 0 AND propertyByteObject = 0 AND propertyDouble = 0.0 AND propertyDoubleObject = 0.0 AND propertyFloat = 0.0 AND propertyFloatObject = 0.0 AND propertyInt = 0 AND propertyIntegerObject = 0 AND propertyLong = 0 AND propertyLongObject = 0 AND propertyShort = 0 AND propertyShortObject = 0");
0976: assertTrue(execute(query));
0977: }
0978:
0979: public void testWhereBeanIncludedPgsql() {
0980: Update query = new Update(mPgsql);
0981: query.table("tablename").field("propertyBoolean", true).field(
0982: "propertyByte", (byte) 16).whereIncluded(
0983: BeanImpl.getPopulatedBean(),
0984: new String[] { "propertyByte", "propertyDouble",
0985: "propertyShort", "propertyStringbuffer",
0986: "propertyTime" });
0987: assertEquals(
0988: query.getSql(),
0989: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyByte = 89 AND propertyDouble = 53348.34 AND propertyShort = 43 AND propertyStringbuffer = 'someotherstringbuff' AND propertyTime = '15:26:14'");
0990: assertTrue(execute(query));
0991: }
0992:
0993: public void testWhereBeanExcludedPgsql() {
0994: Update query = new Update(mPgsql);
0995: query.table("tablename").field("propertyBoolean", true).field(
0996: "propertyByte", (byte) 16).whereExcluded(
0997: BeanImpl.getPopulatedBean(),
0998: new String[] { "propertyByte", "propertyDouble",
0999: "propertyShort", "propertyStringbuffer",
1000: "propertyTime" });
1001: assertEquals(
1002: query.getSql(),
1003: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyBigDecimal = 219038743.392874 AND propertyBoolean = true AND propertyBooleanObject = false AND propertyByteObject = 34 AND propertyCalendar = '2002-06-18 15:26:14.764' AND propertyChar = 'v' AND propertyCharacterObject = 'r' AND propertyDate = '2002-06-18 15:26:14.764' AND propertyDoubleObject = 143298.692 AND propertyEnum = 'VALUE_THREE' AND propertyFloat = 98634.2 AND propertyFloatObject = 8734.7 AND propertyInt = 545 AND propertyIntegerObject = 968 AND propertyLong = 34563 AND propertyLongObject = 66875 AND propertyShortObject = 68 AND propertySqlDate = '2002-06-18' AND propertyString = 'someotherstring' AND propertyTimestamp = '2002-06-18 15:26:14.764'");
1004: assertTrue(execute(query));
1005: }
1006:
1007: public void testWhereBeanFilteredPgsql() {
1008: Update query = new Update(mPgsql);
1009: query.table("tablename").field("propertyBoolean", true).field(
1010: "propertyByte", (byte) 16).whereFiltered(
1011: BeanImpl.getPopulatedBean(),
1012: new String[] { "propertyByte", "propertyDouble",
1013: "propertyShort", "propertyStringbuffer",
1014: "propertyTime" },
1015: new String[] { "propertyByte", "propertyShort",
1016: "propertyTime" });
1017: assertEquals(
1018: query.getSql(),
1019: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyDouble = 53348.34 AND propertyStringbuffer = 'someotherstringbuff'");
1020: assertTrue(execute(query));
1021: }
1022:
1023: public void testWhereParametersBeanPgsql() {
1024: Update query = new Update(mPgsql);
1025: query.table("tablename").field("propertyBoolean", true).field(
1026: "propertyByte", (byte) 16).whereParameters(
1027: BeanImpl.class);
1028: assertEquals(
1029: query.getSql(),
1030: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyBigDecimal = ? AND propertyBoolean = ? AND propertyBooleanObject = ? AND propertyByte = ? AND propertyByteObject = ? AND propertyCalendar = ? AND propertyChar = ? AND propertyCharacterObject = ? AND propertyDate = ? AND propertyDouble = ? AND propertyDoubleObject = ? AND propertyEnum = ? AND propertyFloat = ? AND propertyFloatObject = ? AND propertyInt = ? AND propertyIntegerObject = ? AND propertyLong = ? AND propertyLongObject = ? AND propertyShort = ? AND propertyShortObject = ? AND propertySqlDate = ? AND propertyString = ? AND propertyStringbuffer = ? AND propertyTime = ? AND propertyTimestamp = ?");
1031:
1032: assertEquals(query.getParameters().getOrderedNames().size(), 25);
1033: assertEquals(query.getParameters().getOrderedNames().get(0),
1034: "propertyBigDecimal");
1035: assertEquals(query.getParameters().getOrderedNames().get(1),
1036: "propertyBoolean");
1037: assertEquals(query.getParameters().getOrderedNames().get(2),
1038: "propertyBooleanObject");
1039: assertEquals(query.getParameters().getOrderedNames().get(3),
1040: "propertyByte");
1041: assertEquals(query.getParameters().getOrderedNames().get(4),
1042: "propertyByteObject");
1043: assertEquals(query.getParameters().getOrderedNames().get(5),
1044: "propertyCalendar");
1045: assertEquals(query.getParameters().getOrderedNames().get(6),
1046: "propertyChar");
1047: assertEquals(query.getParameters().getOrderedNames().get(7),
1048: "propertyCharacterObject");
1049: assertEquals(query.getParameters().getOrderedNames().get(8),
1050: "propertyDate");
1051: assertEquals(query.getParameters().getOrderedNames().get(9),
1052: "propertyDouble");
1053: assertEquals(query.getParameters().getOrderedNames().get(10),
1054: "propertyDoubleObject");
1055: assertEquals(query.getParameters().getOrderedNames().get(11),
1056: "propertyEnum");
1057: assertEquals(query.getParameters().getOrderedNames().get(12),
1058: "propertyFloat");
1059: assertEquals(query.getParameters().getOrderedNames().get(13),
1060: "propertyFloatObject");
1061: assertEquals(query.getParameters().getOrderedNames().get(14),
1062: "propertyInt");
1063: assertEquals(query.getParameters().getOrderedNames().get(15),
1064: "propertyIntegerObject");
1065: assertEquals(query.getParameters().getOrderedNames().get(16),
1066: "propertyLong");
1067: assertEquals(query.getParameters().getOrderedNames().get(17),
1068: "propertyLongObject");
1069: assertEquals(query.getParameters().getOrderedNames().get(18),
1070: "propertyShort");
1071: assertEquals(query.getParameters().getOrderedNames().get(19),
1072: "propertyShortObject");
1073: assertEquals(query.getParameters().getOrderedNames().get(20),
1074: "propertySqlDate");
1075: assertEquals(query.getParameters().getOrderedNames().get(21),
1076: "propertyString");
1077: assertEquals(query.getParameters().getOrderedNames().get(22),
1078: "propertyStringbuffer");
1079: assertEquals(query.getParameters().getOrderedNames().get(23),
1080: "propertyTime");
1081: assertEquals(query.getParameters().getOrderedNames().get(24),
1082: "propertyTimestamp");
1083: assertTrue(Arrays.equals(query.getParameters()
1084: .getOrderedNamesArray(), new String[] {
1085: "propertyBigDecimal", "propertyBoolean",
1086: "propertyBooleanObject", "propertyByte",
1087: "propertyByteObject", "propertyCalendar",
1088: "propertyChar", "propertyCharacterObject",
1089: "propertyDate", "propertyDouble",
1090: "propertyDoubleObject", "propertyEnum",
1091: "propertyFloat", "propertyFloatObject", "propertyInt",
1092: "propertyIntegerObject", "propertyLong",
1093: "propertyLongObject", "propertyShort",
1094: "propertyShortObject", "propertySqlDate",
1095: "propertyString", "propertyStringbuffer",
1096: "propertyTime", "propertyTimestamp" }));
1097:
1098: assertTrue(execute(query, new DbPreparedStatementHandler() {
1099: public void setParameters(DbPreparedStatement statement) {
1100: Calendar cal = Calendar.getInstance();
1101: cal.set(2002, 5, 18, 15, 26, 14);
1102: cal.set(Calendar.MILLISECOND, 764);
1103: statement.setBigDecimal(1,
1104: new BigDecimal("219038743.392874")).setBoolean(
1105: 2, true).setBoolean(3, false).setByte(4,
1106: (byte) 89).setByte(5, (byte) 34)
1107: .setTimestamp(
1108: 6,
1109: new java.sql.Timestamp(cal.getTime()
1110: .getTime())).setString(7, "v")
1111: .setString(8, "r").setTimestamp(
1112: 9,
1113: new java.sql.Timestamp(cal.getTime()
1114: .getTime())).setDouble(10,
1115: 53348.34d).setDouble(11, 143298.692d)
1116: .setString(12, "VALUE_THREE").setDouble(13,
1117: 98634.2d).setDouble(14, 8734.7d)
1118: .setInt(15, 545).setInt(16, 968).setLong(17,
1119: 34563L).setLong(18, 66875L).setShort(
1120: 19, (short) 43)
1121: .setShort(20, (short) 68).setDate(
1122: 21,
1123: new java.sql.Date(cal.getTime()
1124: .getTime())).setString(22,
1125: "someotherstring").setString(23,
1126: "someotherstringbuff").setTime(24,
1127: new Time(15, 26, 14)).setTimestamp(25,
1128: new Timestamp(cal.getTime().getTime()));
1129: }
1130: }));
1131: }
1132:
1133: public void testWhereParametersBeanConstrainedPgsql() {
1134: Update query = new Update(mPgsql);
1135: query.table("tablename").field("propertyBoolean", true).field(
1136: "propertyByte", (byte) 16).whereParameters(
1137: BeanImplConstrained.class);
1138: assertEquals(
1139: query.getSql(),
1140: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyBigDecimal = ? AND propertyBoolean = ? AND propertyBooleanObject = ? AND propertyByte = ? AND propertyByteObject = ? AND propertyCalendar = ? AND propertyChar = ? AND propertyCharacterObject = ? AND propertyDate = ? AND propertyDouble = ? AND propertyDoubleObject = ? AND propertyFloat = ? AND propertyFloatObject = ? AND propertyInt = ? AND propertyIntegerObject = ? AND propertyLongObject = ? AND propertyShort = ? AND propertySqlDate = ? AND propertyString = ? AND propertyStringbuffer = ? AND propertyTime = ? AND propertyTimestamp = ?");
1141:
1142: assertEquals(query.getParameters().getOrderedNames().size(), 22);
1143: assertEquals(query.getParameters().getOrderedNames().get(0),
1144: "propertyBigDecimal");
1145: assertEquals(query.getParameters().getOrderedNames().get(1),
1146: "propertyBoolean");
1147: assertEquals(query.getParameters().getOrderedNames().get(2),
1148: "propertyBooleanObject");
1149: assertEquals(query.getParameters().getOrderedNames().get(3),
1150: "propertyByte");
1151: assertEquals(query.getParameters().getOrderedNames().get(4),
1152: "propertyByteObject");
1153: assertEquals(query.getParameters().getOrderedNames().get(5),
1154: "propertyCalendar");
1155: assertEquals(query.getParameters().getOrderedNames().get(6),
1156: "propertyChar");
1157: assertEquals(query.getParameters().getOrderedNames().get(7),
1158: "propertyCharacterObject");
1159: assertEquals(query.getParameters().getOrderedNames().get(8),
1160: "propertyDate");
1161: assertEquals(query.getParameters().getOrderedNames().get(9),
1162: "propertyDouble");
1163: assertEquals(query.getParameters().getOrderedNames().get(10),
1164: "propertyDoubleObject");
1165: assertEquals(query.getParameters().getOrderedNames().get(11),
1166: "propertyFloat");
1167: assertEquals(query.getParameters().getOrderedNames().get(12),
1168: "propertyFloatObject");
1169: assertEquals(query.getParameters().getOrderedNames().get(13),
1170: "propertyInt");
1171: assertEquals(query.getParameters().getOrderedNames().get(14),
1172: "propertyIntegerObject");
1173: assertEquals(query.getParameters().getOrderedNames().get(15),
1174: "propertyLongObject");
1175: assertEquals(query.getParameters().getOrderedNames().get(16),
1176: "propertyShort");
1177: assertEquals(query.getParameters().getOrderedNames().get(17),
1178: "propertySqlDate");
1179: assertEquals(query.getParameters().getOrderedNames().get(18),
1180: "propertyString");
1181: assertEquals(query.getParameters().getOrderedNames().get(19),
1182: "propertyStringbuffer");
1183: assertEquals(query.getParameters().getOrderedNames().get(20),
1184: "propertyTime");
1185: assertEquals(query.getParameters().getOrderedNames().get(21),
1186: "propertyTimestamp");
1187: assertTrue(Arrays.equals(query.getParameters()
1188: .getOrderedNamesArray(), new String[] {
1189: "propertyBigDecimal", "propertyBoolean",
1190: "propertyBooleanObject", "propertyByte",
1191: "propertyByteObject", "propertyCalendar",
1192: "propertyChar", "propertyCharacterObject",
1193: "propertyDate", "propertyDouble",
1194: "propertyDoubleObject", "propertyFloat",
1195: "propertyFloatObject", "propertyInt",
1196: "propertyIntegerObject", "propertyLongObject",
1197: "propertyShort", "propertySqlDate", "propertyString",
1198: "propertyStringbuffer", "propertyTime",
1199: "propertyTimestamp" }));
1200:
1201: assertTrue(execute(query, new DbPreparedStatementHandler() {
1202: public void setParameters(DbPreparedStatement statement) {
1203: Calendar cal = Calendar.getInstance();
1204: cal.set(2002, 5, 18, 15, 26, 14);
1205: cal.set(Calendar.MILLISECOND, 764);
1206: statement.setBigDecimal(1,
1207: new BigDecimal("219038743.392874")).setBoolean(
1208: 2, true).setBoolean(3, false).setByte(4,
1209: (byte) 89).setByte(5, (byte) 34)
1210: .setTimestamp(
1211: 6,
1212: new java.sql.Timestamp(cal.getTime()
1213: .getTime())).setString(7, "v")
1214: .setString(8, "r").setTimestamp(
1215: 9,
1216: new java.sql.Timestamp(cal.getTime()
1217: .getTime())).setDouble(10,
1218: 53348.34d).setDouble(11, 143298.692d)
1219: .setDouble(12, 98634.2d).setDouble(13, 8734.7d)
1220: .setInt(14, 545).setInt(15, 968).setLong(16,
1221: 66875L).setShort(17, (short) 43)
1222: .setDate(
1223: 18,
1224: new java.sql.Date(cal.getTime()
1225: .getTime())).setString(19,
1226: "someotherstring").setString(20,
1227: "someotherstringbuff").setTime(21,
1228: new Time(15, 26, 14)).setTimestamp(22,
1229: new Timestamp(cal.getTime().getTime()));
1230: }
1231: }));
1232: }
1233:
1234: public void testWhereParametersBeanExcludedPgsql() {
1235: Update query = new Update(mPgsql);
1236: query.table("tablename").field("propertyBoolean", true).field(
1237: "propertyByte", (byte) 16).whereParametersExcluded(
1238: BeanImpl.class,
1239: new String[] { "propertyBoolean", "propertyByte",
1240: "propertyChar", "propertyDouble",
1241: "propertyInt", "propertyLong",
1242: "propertySqlDate", "propertyStringbuffer",
1243: "propertyTimestamp" });
1244: assertEquals(
1245: query.getSql(),
1246: "UPDATE tablename SET propertyBoolean = true, propertyByte = 16 WHERE propertyBigDecimal = ? AND propertyBooleanObject = ? AND propertyByteObject = ? AND propertyCalendar = ? AND propertyCharacterObject = ? AND propertyDate = ? AND propertyDoubleObject = ? AND propertyEnum = ? AND propertyFloat = ? AND propertyFloatObject = ? AND propertyIntegerObject = ? AND propertyLongObject = ? AND propertyShort = ? AND propertyShortObject = ? AND propertyString = ? AND propertyTime = ?");
1247:
1248: assertEquals(query.getParameters().getOrderedNames().size(), 16);
1249: assertEquals(query.getParameters().getOrderedNames().get(0),
1250: "propertyBigDecimal");
1251: assertEquals(query.getParameters().getOrderedNames().get(1),
1252: "propertyBooleanObject");
1253: assertEquals(query.getParameters().getOrderedNames().get(2),
1254: "propertyByteObject");
1255: assertEquals(query.getParameters().getOrderedNames().get(3),
1256: "propertyCalendar");
1257: assertEquals(query.getParameters().getOrderedNames().get(4),
1258: "propertyCharacterObject");
1259: assertEquals(query.getParameters().getOrderedNames().get(5),
1260: "propertyDate");
1261: assertEquals(query.getParameters().getOrderedNames().get(6),
1262: "propertyDoubleObject");
1263: assertEquals(query.getParameters().getOrderedNames().get(7),
1264: "propertyEnum");
1265: assertEquals(query.getParameters().getOrderedNames().get(8),
1266: "propertyFloat");
1267: assertEquals(query.getParameters().getOrderedNames().get(9),
1268: "propertyFloatObject");
1269: assertEquals(query.getParameters().getOrderedNames().get(10),
1270: "propertyIntegerObject");
1271: assertEquals(query.getParameters().getOrderedNames().get(11),
1272: "propertyLongObject");
1273: assertEquals(query.getParameters().getOrderedNames().get(12),
1274: "propertyShort");
1275: assertEquals(query.getParameters().getOrderedNames().get(13),
1276: "propertyShortObject");
1277: assertEquals(query.getParameters().getOrderedNames().get(14),
1278: "propertyString");
1279: assertEquals(query.getParameters().getOrderedNames().get(15),
1280: "propertyTime");
1281: assertTrue(Arrays.equals(query.getParameters()
1282: .getOrderedNamesArray(), new String[] {
1283: "propertyBigDecimal", "propertyBooleanObject",
1284: "propertyByteObject", "propertyCalendar",
1285: "propertyCharacterObject", "propertyDate",
1286: "propertyDoubleObject", "propertyEnum",
1287: "propertyFloat", "propertyFloatObject",
1288: "propertyIntegerObject", "propertyLongObject",
1289: "propertyShort", "propertyShortObject",
1290: "propertyString", "propertyTime" }));
1291:
1292: assertTrue(execute(query, new DbPreparedStatementHandler() {
1293: public void setParameters(DbPreparedStatement statement) {
1294: Calendar cal = Calendar.getInstance();
1295: cal.set(2002, 5, 18, 15, 26, 14);
1296: cal.set(Calendar.MILLISECOND, 764);
1297: statement.setBigDecimal(1,
1298: new BigDecimal("219038743.392874")).setBoolean(
1299: 2, false).setByte(3, (byte) 34)
1300: .setTimestamp(
1301: 4,
1302: new java.sql.Timestamp(cal.getTime()
1303: .getTime())).setString(5, "r")
1304: .setTimestamp(
1305: 6,
1306: new java.sql.Timestamp(cal.getTime()
1307: .getTime())).setDouble(7,
1308: 143298.692d)
1309: .setString(8, "VALUE_THREE").setDouble(9,
1310: 98634.2d).setDouble(10, 8734.7d)
1311: .setInt(11, 968).setLong(12, 66875L).setShort(
1312: 13, (short) 43)
1313: .setShort(14, (short) 68).setString(15,
1314: "someotherstring").setTime(16,
1315: new Time(15, 26, 14));
1316: }
1317: }));
1318: }
1319:
1320: public void testSubselectParamsPgsql() {
1321: Select fieldquery = new Select(mPgsql);
1322: fieldquery.from("table2").field("max(propertyLong)")
1323: .whereParameter("propertyInt", ">");
1324: Select wherequery = new Select(mPgsql);
1325: wherequery.from("table2").field("max(propertyShort)")
1326: .whereParameter("propertyShort", "!=");
1327:
1328: // Manual subselect creation
1329: Update query = new Update(mPgsql);
1330: // shuffled the structure around a bit to test the correct order usage
1331: query.where("propertyShort >= (" + wherequery + ")")
1332: .whereSubselect(wherequery).whereParameterOr(
1333: "propertyString", "propertyString2", "=")
1334: .table("tablename").fieldParameter("propertyString")
1335: .fieldCustom("propertyLong", "(" + fieldquery + ")")
1336: .fieldSubselect(fieldquery);
1337: assertEquals(
1338: query.getSql(),
1339: "UPDATE tablename SET propertyString = ?, propertyLong = (SELECT max(propertyLong) FROM table2 WHERE propertyInt > ?) WHERE propertyShort >= (SELECT max(propertyShort) FROM table2 WHERE propertyShort != ?) OR propertyString = ?");
1340: String[] parameters = query.getParameters()
1341: .getOrderedNamesArray();
1342: assertEquals(4, parameters.length);
1343: assertEquals(parameters[0], "propertyString");
1344: assertEquals(parameters[1], "propertyInt");
1345: assertEquals(parameters[2], "propertyShort");
1346: assertEquals(parameters[3], "propertyString2");
1347: assertTrue(execute(query, new DbPreparedStatementHandler() {
1348: public void setParameters(DbPreparedStatement statement) {
1349: statement.setString("propertyString", "thestring")
1350: .setInt("propertyInt", 1).setShort(
1351: "propertyShort", (short) 5).setString(
1352: "propertyString2", "anotherstring");
1353: }
1354: }));
1355:
1356: // Automated subselect creation
1357: query = new Update(mPgsql);
1358: // shuffled the structure around a bit to test the correct order usage
1359: query.where("propertyShort", ">=", wherequery)
1360: .whereParameterOr("propertyString", "propertyString2",
1361: "=").whereOr(
1362: "tablename.propertyFloat",
1363: ">",
1364: new Select(mPgsql).from("table2").field(
1365: "max(propertyLong)").whereParameter(
1366: "propertyLong", "!=")).whereAnd(
1367: "tablename.propertyDouble",
1368: "<=",
1369: new Select(mPgsql).from("table2").field(
1370: "max(propertyFloat)").whereParameter(
1371: "propertyFloat", "!=")).table(
1372: "tablename").fieldParameter("propertyString")
1373: .field("propertyLong", fieldquery);
1374: assertEquals(
1375: query.getSql(),
1376: "UPDATE tablename SET propertyString = ?, propertyLong = (SELECT max(propertyLong) FROM table2 WHERE propertyInt > ?) WHERE propertyShort >= (SELECT max(propertyShort) FROM table2 WHERE propertyShort != ?) OR propertyString = ? OR tablename.propertyFloat > (SELECT max(propertyLong) FROM table2 WHERE propertyLong != ?) AND tablename.propertyDouble <= (SELECT max(propertyFloat) FROM table2 WHERE propertyFloat != ?)");
1377: parameters = query.getParameters().getOrderedNamesArray();
1378: assertEquals(6, parameters.length);
1379: assertEquals(parameters[0], "propertyString");
1380: assertEquals(parameters[1], "propertyInt");
1381: assertEquals(parameters[2], "propertyShort");
1382: assertEquals(parameters[3], "propertyString2");
1383: assertTrue(execute(query, new DbPreparedStatementHandler() {
1384: public void setParameters(DbPreparedStatement statement) {
1385: statement.setString("propertyString", "thestring")
1386: .setInt("propertyInt", 1).setShort(
1387: "propertyShort", (short) 5).setString(
1388: "propertyString2", "anotherstring")
1389: .setLong("propertyLong", 99999999).setFloat(
1390: "propertyFloat", -1f);
1391: }
1392: }));
1393: }
1394:
1395: public void testClonePgsql() {
1396: Select fieldquery = new Select(mPgsql);
1397: fieldquery.from("table2").field("max(propertyLong)")
1398: .whereParameter("propertyInt", ">");
1399: Select wherequery = new Select(mPgsql);
1400: wherequery.from("table2").field("max(propertyShort)")
1401: .whereParameter("propertyShort", "!=");
1402:
1403: final Calendar cal = Calendar.getInstance();
1404: cal.set(2002, 7, 19, 12, 17, 52);
1405: cal.set(Calendar.MILLISECOND, 462);
1406: Update query = new Update(mPgsql);
1407: query.table("tablename").fieldParameter("nullColumn")
1408: .field("propertyBigDecimal",
1409: new BigDecimal("98347.876438637"))
1410: .fieldParameter("propertyBoolean").fieldParameter(
1411: "propertyByte").field("propertyCalendar",
1412: cal.getTime()).fieldParameter("propertyChar")
1413: .field("propertyDate", cal).field("propertyDouble",
1414: 12.3d).fieldParameter("propertyFloat")
1415: .fieldParameter("propertyInt").field("propertyShort",
1416: (short) 12).fieldParameter("propertySqlDate")
1417: .fieldParameter("propertyString").field(
1418: "propertyStringbuffer",
1419: new StringBuffer("stringbuffer'value")).field(
1420: "propertyTime",
1421: new Time(cal.getTime().getTime()))
1422: .fieldParameter("propertyTimestamp").fieldCustom(
1423: "propertyLong", "(" + fieldquery + ")")
1424: .fieldSubselect(fieldquery).where(
1425: "tablename.propertyShort >= (" + wherequery
1426: + ")").whereSubselect(wherequery)
1427: .whereParameterOr("tablename.propertyString",
1428: "propertyString", "=").whereOr(
1429: "tablename.propertyByte", "=", (byte) 54)
1430: .whereAnd("tablename.propertyDouble", "!=", 73453.71d)
1431: .whereParameterOr("tablename.propertyInt",
1432: "propertyInt", "=").whereParameterAnd(
1433: "tablename.propertyLong", "propertyLong", "<")
1434: .whereParameterOr("tablename.propertyChar",
1435: "propertyChar", "=");
1436:
1437: Update query_clone = query.clone();
1438: assertEquals(query.getSql(), query_clone.getSql());
1439: assertTrue(query != query_clone);
1440: execute(query, new DbPreparedStatementHandler() {
1441: public void setParameters(DbPreparedStatement statement) {
1442: statement.setString("nullColumn", null).setBoolean(
1443: "propertyBoolean", true).setByte(
1444: "propertyByte", (byte) 16).setString(
1445: "propertyChar", "M").setFloat("propertyFloat",
1446: 13.4f).setInt("propertyInt", 34).setDate(
1447: "propertySqlDate",
1448: new java.sql.Date(cal.getTime().getTime()))
1449: .setString("propertyString", "string'value")
1450: .setTimestamp("propertyTimestamp",
1451: new Timestamp(cal.getTime().getTime()))
1452: .setShort("propertyShort", (short) 4).setLong(
1453: "propertyLong", 34543);
1454: }
1455: });
1456: }
1457: }
|