@Test
public void testInlinedBindValuesForDatetime() throws Exception {
jOOQAbstractTest.reset = false;
Date d1 = Date.valueOf("1981-07-10");
Time t1 = Time.valueOf("12:01:15");
Timestamp ts1 = Timestamp.valueOf("1981-07-10 12:01:15");
Factory create = create(new Settings()
.withStatementType(StatementType.STATIC_STATEMENT));
DATE date = create.newRecord(TDates());
date.setValue(TDates_ID(), 1);
assertEquals(1, date.store());
date.setValue(TDates_ID(), 2);
date.setValue(TDates_D(), d1);
date.setValue(TDates_T(), t1);
date.setValue(TDates_TS(), ts1);
assertEquals(1, date.store());
Result<Record> dates =
create.select(TDates_ID(), TDates_D(), TDates_T(), TDates_TS())
.from(TDates())
.orderBy(TDates_ID())
.fetch();
assertEquals(2, dates.size());
assertEquals(asList(1, 2), dates.getValues(TDates_ID()));
assertEquals(asList(1, null, null, null), asList(dates.get(0).intoArray()));
assertEquals(asList((Object) 2, d1, t1, ts1), asList(dates.get(1).intoArray()));
}
@Test
public void testInlinedBindValuesForNumberTypes() throws Exception {
jOOQAbstractTest.reset = false;
Double db1 = 1234.5678E9;
Float f1 = 1234.5678E9f;
// Inlining bind values globally, through the factory settings
// -----------------------------------------------------------
{
Factory create = create(new Settings()
.withStatementType(StatementType.STATIC_STATEMENT));
// [#1557] Check correct inlining of floating point values with
// exponential notation.
assertEquals(2,
create.insertInto(T639(), T639_ID(), T639_BIG_DECIMAL())
.values(1, db1)
.values(2, f1)
.execute());
Result<T639> result = create.selectFrom(T639()).orderBy(T639_ID()).fetch();
assertEquals(1, (int) result.getValue(0, T639_ID()));
assertEquals(2, (int) result.getValue(1, T639_ID()));
assertEquals(1234, (int) (result.get(0).getValue(T639_BIG_DECIMAL(), Double.class) / 1E9));
assertEquals(1234, (int) (result.get(0).getValue(T639_BIG_DECIMAL(), Float.class) / 1E9f));
}
}
@Override
final Field<BigDecimal> getFunction0(Configuration configuration) {
switch (configuration.getDialect()) {
case ASE:
case CUBRID:
case DB2:
case DERBY:
case FIREBIRD:
case H2:
case HSQLDB:
case INGRES:
case MYSQL:
case ORACLE:
case POSTGRES:
case SQLSERVER:
case SYBASE:
return Factory.exp(one());
case SQLITE:
return inline(Math.E, BigDecimal.class);
// The Euler number doesn't seem to exist in any dialect...
default:
return function("e", getDataType());
}
}
/** {@inheritDoc} */
@Override
public org.jooq.Row6<
java.lang.Integer,
java.lang.String,
java.lang.String,
java.sql.Date,
java.lang.Integer,
java.lang.Object>
valuesRow() {
return org.jooq.impl.Factory.row(value1(), value2(), value3(), value4(), value5(), value6());
}
/** {@inheritDoc} */
@Override
public org.jooq.Row6<
java.lang.Integer,
java.lang.String,
java.lang.String,
java.sql.Date,
java.lang.Integer,
java.lang.Object>
fieldsRow() {
return org.jooq.impl.Factory.row(field1(), field2(), field3(), field4(), field5(), field6());
}
public PartitionableThing createRandomThing(Map<String, ?> attributes) {
String id = RandomStringUtils.randomAlphabetic(10);
String version = RandomStringUtils.randomAlphabetic(10);
PartitionableThing record = new PartitionableThing(attributes);
record.setId(id);
record.setVersion(version);
Connection connection = getConnection();
Factory db = getFactory(connection);
db.execute(
"insert into things (id, version,entry_date) values (?,?,?)",
id,
version,
record.getEntryDate());
closeConnection(connection, true);
return record;
}
/** {@inheritDoc} */
@Override
public org.jooq.Row9<
java.lang.Integer,
java.lang.Integer,
java.lang.Integer,
java.lang.Integer,
java.sql.Timestamp,
java.lang.String,
java.lang.Integer,
java.math.BigDecimal,
java.sql.Timestamp>
valuesRow() {
return org.jooq.impl.Factory.row(
value1(), value2(), value3(), value4(), value5(), value6(), value7(), value8(), value9());
}
/** {@inheritDoc} */
@Override
public org.jooq.Row9<
java.lang.Integer,
java.lang.Integer,
java.lang.Integer,
java.lang.Integer,
java.sql.Timestamp,
java.lang.String,
java.lang.Integer,
java.math.BigDecimal,
java.sql.Timestamp>
fieldsRow() {
return org.jooq.impl.Factory.row(
field1(), field2(), field3(), field4(), field5(), field6(), field7(), field8(), field9());
}
/** {@inheritDoc} */
@Override
public org.jooq.Row11<
java.lang.Integer,
java.lang.Integer,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.Integer>
fieldsRow() {
return org.jooq.impl.Factory.row(
field1(), field2(), field3(), field4(), field5(), field6(), field7(), field8(), field9(),
field10(), field11());
}
/** {@inheritDoc} */
@Override
public org.jooq.Row11<
java.lang.Integer,
java.lang.Integer,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.Integer>
valuesRow() {
return org.jooq.impl.Factory.row(
value1(), value2(), value3(), value4(), value5(), value6(), value7(), value8(), value9(),
value10(), value11());
}
private static final Fields init(String alias, Class<?> arrayType) {
List<Field<?>> result = new ArrayList<Field<?>>();
// [#1114] VARRAY/TABLE of OBJECT have more than one field
if (UDTRecord.class.isAssignableFrom(arrayType)) {
try {
UDTRecord<?> record = (UDTRecord<?>) arrayType.newInstance();
for (Field<?> f : record.fields()) {
result.add(fieldByName(f.getDataType(), alias, f.getName()));
}
} catch (Exception e) {
throw new DataTypeException("Bad UDT Type : " + arrayType, e);
}
}
// Simple array types have a synthetic field called "COLUMN_VALUE"
else {
result.add(fieldByName(Factory.getDataType(arrayType), alias, "COLUMN_VALUE"));
}
return new Fields(result);
}
private <R extends UpdatableRecord<R>> void testStoreWithOptimisticLock0(
UpdatableTable<R> table, TableField<R, Integer> id, TableField<R, String> string) throws Exception {
Factory create = create(new Settings().withExecuteWithOptimisticLocking(true));
// Storing without changing shouldn't execute any queries
R record1 = create.fetchOne(table, id.equal(1));
assertEquals(0, record1.store());
assertEquals(0, record1.store());
// Succeed if there are no concurrency issues
record1.setValue(string, "New Title 1");
assertEquals(1, record1.store());
assertEquals("New Title 1", create.fetchOne(table, id.equal(1)).getValue(string));
// Get new books
R record2 = create.fetchOne(table, id.equal(1));
R record3 = create.fetchOne(table, id.equal(1));
// Still won't fail, but this will cause record3 to be stale
record2.setValue(string, "New Title 2");
assertEquals(1, record2.store());
assertEquals("New Title 2", create.fetchOne(table, id.equal(1)).getValue(string));
// Storing without changing shouldn't execute any queries
assertEquals(0, record3.store());
// This should fail as record3 is stale
record3.setValue(string, "New Title 3");
try {
record3.store();
fail();
}
catch (DataChangedException expected) {}
assertEquals("New Title 2", create.fetchOne(table, id.equal(1)).getValue(string));
// Refreshing first will work, though
record3.refresh();
record3.setValue(string, "New Title 3");
assertEquals(1, record3.store());
assertEquals("New Title 3", create.fetchOne(table, id.equal(1)).getValue(string));
// Get new books
R record4 = create.fetchOne(table, id.equal(1));
R record5 = create.fetchOne(table, id.equal(1));
// Delete the book
assertEquals(1, record4.delete());
// Storing without changing shouldn't execute any queries
assertEquals(0, record5.store());
// This should fail, as the database record no longer exists
record5.setValue(string, "New Title 5");
try {
record5.store();
fail();
}
catch (DataChangedException expected) {}
// Restore the book, refresh the copy, then it should work
assertEquals(1, record4.store());
record5.refresh();
record5.setValue(string, "New Title 5");
assertEquals(1, record5.store());
assertEquals("New Title 5", create.fetchOne(table, id.equal(1)).getValue(string));
// Deleting the original should no longer be possible
try {
record4.delete();
fail();
}
catch (DataChangedException expected) {}
// Refreshing and deleting should work
record4.refresh();
assertEquals(1, record4.delete());
// Now the other record cannot be deleted anymore
try {
record5.delete();
fail();
}
catch (DataChangedException expected) {}
}
@SuppressWarnings("unchecked")
@Override
final Field<T> getFunction0(Configuration configuration) {
SQLDialect dialect = configuration.getDialect();
// ---------------------------------------------------------------------
// XXX: Bitwise operators
// ---------------------------------------------------------------------
// DB2, H2 and HSQLDB know functions, instead of operators
if (BIT_AND == operator && asList(DB2, H2, HSQLDB, ORACLE).contains(dialect)) {
return function("bitand", getDataType(), getArguments());
} else if (BIT_AND == operator && FIREBIRD == dialect) {
return function("bin_and", getDataType(), getArguments());
} else if (BIT_XOR == operator && asList(DB2, H2, HSQLDB).contains(dialect)) {
return function("bitxor", getDataType(), getArguments());
} else if (BIT_XOR == operator && FIREBIRD == dialect) {
return function("bin_xor", getDataType(), getArguments());
} else if (BIT_OR == operator && asList(DB2, H2, HSQLDB).contains(dialect)) {
return function("bitor", getDataType(), getArguments());
} else if (BIT_OR == operator && FIREBIRD == dialect) {
return function("bin_or", getDataType(), getArguments());
}
// Oracle has to simulate or/xor
else if (BIT_OR == operator && ORACLE == dialect) {
return lhs.sub(bitAnd(lhsAsNumber(), rhsAsNumber())).add(rhsAsNumber());
}
// ~(a & b) & (a | b)
else if (BIT_XOR == operator && asList(ORACLE, SQLITE).contains(dialect)) {
return (Field<T>)
bitAnd(bitNot(bitAnd(lhsAsNumber(), rhsAsNumber())), bitOr(lhsAsNumber(), rhsAsNumber()));
}
// Many dialects don't support shifts. Use multiplication/division instead
else if (SHL == operator
&& asList(ASE, DB2, H2, HSQLDB, INGRES, ORACLE, SQLSERVER, SYBASE).contains(dialect)) {
return lhs.mul(Factory.power(two(), rhsAsNumber()));
} else if (SHR == operator
&& asList(ASE, DB2, H2, HSQLDB, INGRES, ORACLE, SQLSERVER, SYBASE).contains(dialect)) {
return lhs.div(Factory.power(two(), rhsAsNumber()));
}
// Some dialects support shifts as functions
else if (SHL == operator && FIREBIRD == dialect) {
return function("bin_shl", getDataType(), getArguments());
} else if (SHR == operator && FIREBIRD == dialect) {
return function("bin_shr", getDataType(), getArguments());
}
// These operators are not supported in any dialect
else if (BIT_NAND == operator) {
return (Field<T>) bitNot(bitAnd(lhsAsNumber(), rhsAsNumber()));
} else if (BIT_NOR == operator) {
return (Field<T>) bitNot(bitOr(lhsAsNumber(), rhsAsNumber()));
} else if (BIT_XNOR == operator) {
return (Field<T>) bitNot(bitXor(lhsAsNumber(), rhsAsNumber()));
}
// ---------------------------------------------------------------------
// XXX: Date time arithmetic operators
// ---------------------------------------------------------------------
// [#585] Date time arithmetic for numeric or interval RHS
else if (asList(ADD, SUBTRACT).contains(operator)
&& lhs.getDataType().isDateTime()
&& (rhs.get(0).getDataType().isNumeric() || rhs.get(0).getDataType().isInterval())) {
return new DateExpression();
}
// ---------------------------------------------------------------------
// XXX: Other operators
// ---------------------------------------------------------------------
// Use the default operator expression for all other cases
else {
return new DefaultExpression();
}
}
@Test
public void testUpdatablesVersionAndTimestamp() throws Exception {
if (TBook_REC_TIMESTAMP() == null && TBook_REC_VERSION() == null) {
log.info("SKIPPING", "Record version and timestamp tests");
}
jOOQAbstractTest.reset = false;
Factory create = create(new Settings().withExecuteWithOptimisticLocking(true));
boolean t = TBook_REC_TIMESTAMP() != null;
boolean v = TBook_REC_VERSION() != null;
// Test data integrity check
// -------------------------
if (t) assertEquals(2, create.selectCount().from(TBook()).where(TBook_REC_TIMESTAMP().isNotNull()).fetchOne(0));
if (v) assertEquals(2, create.selectCount().from(TBook()).where(TBook_REC_VERSION().isNotNull()).fetchOne(0));
// Version and timestamp shouldn't change when there are constraint violations
// -------------------------
B book1 = create.newRecord(TBook());
book1.setValue(TBook_ID(), 5);
try {
book1.store();
fail();
}
catch (DataAccessException expected) {}
if (t) assertNull(book1.getValue(TBook_REC_TIMESTAMP()));
if (v) assertNull(book1.getValue(TBook_REC_VERSION()));
// Test non-nullability of version and timestamp for new books
// -------------------------
B book2 = newBook(5);
assertEquals(1, book2.store());
Timestamp t2 = t ? book2.getValue(TBook_REC_TIMESTAMP()) : null;
Integer v2 = v ? book2.getValue(TBook_REC_VERSION()) : null;
if (t) assertNotNull(t2);
if (v) assertNotNull(v2);
// Test immutability of version and timestamp for non-stored books
// -------------------------
book2.refresh();
assertEquals(0, book2.store());
assertEquals(t2, t ? book2.getValue(TBook_REC_TIMESTAMP()) : null);
assertEquals(v2, v ? book2.getValue(TBook_REC_VERSION()) : null);
// Test resetting of version and timestamp for copied books
// -------------------------
B book3 = book2.copy();
book3.setValue(TBook_ID(), 6);
assertEquals(1, book3.store());
Timestamp t3 = t ? book3.getValue(TBook_REC_TIMESTAMP()) : null;
Integer v3 = v ? book3.getValue(TBook_REC_VERSION()) : null;
if (t) assertNotNull(t3);
if (v) assertNotNull(v3);
if (t && t2 != null) assertFalse(t2.equals(t3));
if (v && v2 != null) assertFalse(v2.equals(v3));
// Check if updating all records will lead to updated version and timestamp values
// -------------------------
// BOOK[ID=4] has version and timestamp set to null
B book4 = create().fetchOne(TBook(), TBook_ID().equal(4));
book4.setValue(TBook_TITLE(), "Blah");
assertEquals(1, book4.store());
Timestamp t4 = t ? book4.getValue(TBook_REC_TIMESTAMP()) : null;
Integer v4 = v ? book4.getValue(TBook_REC_VERSION()) : null;
if (t) assertNotNull(t4);
if (v) assertEquals(Integer.valueOf(1), v4);
book4.refresh();
if (t) assertEquals(t4, book4.getValue(TBook_REC_TIMESTAMP()));
if (v) assertEquals(v4, book4.getValue(TBook_REC_VERSION()));
// Increment both values
book4.setValue(TBook_TITLE(), "Blah 1");
assertEquals(1, book4.store());
Timestamp t4a = t ? book4.getValue(TBook_REC_TIMESTAMP()) : null;
Integer v4a = v ? book4.getValue(TBook_REC_VERSION()) : null;
if (t) assertNotNull(t4a);
if (v) assertEquals(Integer.valueOf(2), v4a);
book4.refresh();
if (t) assertEquals(t4a, book4.getValue(TBook_REC_TIMESTAMP()));
if (v) assertEquals(v4a, book4.getValue(TBook_REC_VERSION()));
// Don't change the book
assertEquals(0, book4.store());
if (t) assertEquals(t4a, book4.getValue(TBook_REC_TIMESTAMP()));
if (v) assertEquals(v4a, book4.getValue(TBook_REC_VERSION()));
book4.refresh();
if (t) assertEquals(t4a, book4.getValue(TBook_REC_TIMESTAMP()));
if (v) assertEquals(v4a, book4.getValue(TBook_REC_VERSION()));
}
@Test
public void testInlinedBindValues() throws Exception {
// [#1158] TODO get this working for derby as well
boolean derby = (getDialect() == DERBY);
// [#1147] Some data types need special care when inlined
// Selection from DUAL
// -------------------
String s1 = "test";
String s2 = "no SQL 'injection here; <<`'";
String s3 = "''";
String s4 = (derby ? s1 : null);
Byte b1 = Byte.valueOf("1");
Byte b2 = (derby ? b1 : null);
Short sh1 = Short.valueOf("2");
Short sh2 = (derby ? sh1 : null);
Integer i1 = 3;
Integer i2 = (derby ? i1 : null);
Long l1 = 4L;
Long l2 = (derby ? l1 : null);
BigInteger bi1 = new BigInteger("5");
BigInteger bi2 = (derby ? bi1 : null);
BigDecimal bd1 = new BigDecimal("6.01");
BigDecimal bd2 = (derby ? bd1 : null);
Double db1 = 7.25;
Double db2 = (derby ? db1 : null);
Float f1 = 8.5f;
Float f2 = (derby ? f1 : null);
Date d1 = Date.valueOf("1981-07-10");
Date d2 = (derby ? d1 : null);
Time t1 = Time.valueOf("12:01:15");
Time t2 = (derby ? t1 : null);
Timestamp ts1 = Timestamp.valueOf("1981-07-10 12:01:15");
Timestamp ts2 = (derby ? ts1 : null);
byte[] by1 = "some bytes".getBytes();
byte[] by2 = (derby ? by1 : null);
Boolean bool1 = true;
Boolean bool2 = false;
Boolean bool3 = (derby ? bool1 : null);
// Inlining bind values globally, through the factory settings
// -----------------------------------------------------------
{
Factory create = create(new Settings()
.withStatementType(StatementType.STATIC_STATEMENT));
Object[] array1 = create.select(vals(s1, s2, s3, s4)).fetchOneArray();
Object[] array2 = create.select(vals(b1, b2, sh1, sh2, i1, i2, l1, l2, bi1, bi2, bd1, bd2, db1, db2, f1, f2)).fetchOneArray();
Object[] array3 = create.select(vals(d1, d2, t1, t2, ts1, ts2)).fetchOneArray();
Object[] array4 = create.select(vals(by1, by2, bool1, bool2, bool3)).fetchOneArray();
assertEquals(4, array1.length);
assertEquals(16, array2.length);
assertEquals(6, array3.length);
assertEquals(5, array4.length);
assertEquals(asList(s1, s2, s3, s4), asList(array1));
assertEquals(asList((Number) b1, b2, sh1, sh2, i1, i2, l1, l2, bi1, bi2, bd1, bd2, db1, db2, f1, f2), asList(array2));
assertEquals(asList(d1, d2, t1, t2, ts1, ts2), asList(array3));
array4[0] = new String((byte[]) array4[0]);
array4[1] = (derby ? new String((byte[]) array4[1]) : array4[1]);
assertEquals(asList(new String(by1), (derby ? new String(by2) : by2), bool1, bool2, bool3), asList(array4));
}
// Inlining bind values locally, through bind value parameters
// -----------------------------------------------------------
{
Factory create = create();
Object[] array1 = create.select(inline(s1), inline(s2), inline(s3), inline(s4)).fetchOneArray();
Object[] array2 = create.select(inline(b1), inline(b2), inline(sh1), inline(sh2), inline(i1), inline(i2), inline(l1), inline(l2), inline(bi1), inline(bi2), inline(bd1), inline(bd2), inline(db1), inline(db2), inline(f1), inline(f2)).fetchOneArray();
Object[] array3 = create.select(inline(d1), inline(d2), inline(t1), inline(t2), inline(ts1), inline(ts2)).fetchOneArray();
Object[] array4 = create.select(inline(by1), inline(by2), inline(bool1), inline(bool2), inline(bool3)).fetchOneArray();
assertEquals(4, array1.length);
assertEquals(16, array2.length);
assertEquals(6, array3.length);
assertEquals(5, array4.length);
assertEquals(asList(s1, s2, s3, s4), asList(array1));
assertEquals(asList((Number) b1, b2, sh1, sh2, i1, i2, l1, l2, bi1, bi2, bd1, bd2, db1, db2, f1, f2), asList(array2));
assertEquals(asList(d1, d2, t1, t2, ts1, ts2), asList(array3));
array4[0] = new String((byte[]) array4[0]);
array4[1] = (derby ? new String((byte[]) array4[1]) : array4[1]);
assertEquals(asList(new String(by1), (derby ? new String(by2) : by2), bool1, bool2, bool3), asList(array4));
}
}
/** {@inheritDoc} */
@Override
public org.jooq.Row3<java.lang.Short, java.lang.Short, java.sql.Timestamp> valuesRow() {
return org.jooq.impl.Factory.row(value1(), value2(), value3());
}
/** {@inheritDoc} */
@Override
public org.jooq.Row2<java.lang.String, java.lang.String> fieldsRow() {
return org.jooq.impl.Factory.row(field1(), field2());
}
/** {@inheritDoc} */
@Override
public org.jooq.Row4<java.lang.Integer, java.lang.String, java.lang.String, java.sql.Timestamp>
valuesRow() {
return org.jooq.impl.Factory.row(value1(), value2(), value3(), value4());
}
/** {@inheritDoc} */
@Override
public org.jooq.Row1<java.lang.Integer> fieldsRow() {
return org.jooq.impl.Factory.row(field1());
}
/** {@inheritDoc} */
@Override
public org.jooq.Row1<java.lang.Integer> valuesRow() {
return org.jooq.impl.Factory.row(value1());
}
/** {@inheritDoc} */
@Override
public org.jooq.Row2<java.lang.Integer, java.lang.Short> fieldsRow() {
return org.jooq.impl.Factory.row(field1(), field2());
}
/** {@inheritDoc} */
@Override
public org.jooq.Row4<java.lang.Integer, java.lang.String, java.lang.String, java.sql.Timestamp>
fieldsRow() {
return org.jooq.impl.Factory.row(field1(), field2(), field3(), field4());
}
/** {@inheritDoc} */
@Override
public org.jooq.Row2<java.lang.Integer, java.lang.Short> valuesRow() {
return org.jooq.impl.Factory.row(value1(), value2());
}
/** {@inheritDoc} */
@Override
public org.jooq.Row2<java.lang.String, java.lang.String> valuesRow() {
return org.jooq.impl.Factory.row(value1(), value2());
}
/** {@inheritDoc} */
@Override
public org.jooq.Row3<java.lang.Short, java.lang.Short, java.sql.Timestamp> fieldsRow() {
return org.jooq.impl.Factory.row(field1(), field2(), field3());
}