@Nullable private <S> S queryForStruct(final String queryText, final Class<S> structClass) { final SqlQuery<S> query = new SqlQuery<S>(queryText, rowOf(structOf(structClass))); return query(query); }
@Test public void query_raw_strings() { final String queryText = "select 'C', 'String' from X1"; final SqlQuery<Object[]> query = new SqlQuery<Object[]>(queryText, rowOf(rawArray())); final Object[] strings = query(query); assertThat(strings).hasSize(2); assertThat(strings[0]).isInstanceOf(String.class); assertThat(strings[1]).isInstanceOf(String.class); assertThat(strings[0]).isEqualTo("C"); assertThat(strings[1]).isEqualTo("String"); }
/** * A simple internal function that performs a simple query without parameters * and returns the first row as an array. * @param queryText the query text. * @param columnCount count of columns. * @param columnClass class of columns. * @param <V> type of columns. * @return the first row, or null if no rows. */ @Nullable public <V> V[] queryOneRow(@NotNull final String queryText, final int columnCount, @NotNull final Class<V> columnClass) { JdbcIntermediateSeance seance1 = this.openSeance(queryText, null); try { seance1.execute(); JdbcIntermediateCursor<V[]> cursor1 = seance1.openDefaultCursor(rowOf(arrayOf(columnCount, columnClass))); try { if (cursor1.hasRows()) return cursor1.fetch(); else return null; } finally { cursor1.close(); } } finally { seance1.close(); } }
@Test public void query_calendar_values_parameters() { String queryText = queryCalendarValuesFromParameters(); if (isOracle) queryText += " from dual"; SqlQuery<CalendarValues> query = new SqlQuery<CalendarValues>(queryText, rowOf(structOf(CalendarValues.class))); CalendarValues cv = query(query, new java.sql.Timestamp(System.currentTimeMillis()), new java.sql.Timestamp(System.currentTimeMillis()), new java.sql.Timestamp(System.currentTimeMillis()), new java.sql.Timestamp(System.currentTimeMillis())/*, new java.sql.Time(System.currentTimeMillis())*/ ); assertThat(cv.javaDate) .isExactlyInstanceOf(java.util.Date.class); assertThat(cv.sqlDate) .isExactlyInstanceOf(java.sql.Date.class); assertThat(cv.sqlTimestamp).isExactlyInstanceOf(java.sql.Timestamp.class); assertThat(cv.sqlTime) .isExactlyInstanceOf(java.sql.Time.class); }
@Test public void query_raw_numbers() { final String queryText = "select 127 as B, 32767 as S, 2147483647 as I, 9223372036854775807 as L from X1"; final SqlQuery<Object[]> query = new SqlQuery<Object[]>(queryText, rowOf(rawArray())); final Object[] numbers = query(query); assertThat(numbers).hasSize(4); assertThat(numbers[0]).isInstanceOf(Number.class); assertThat(numbers[1]).isInstanceOf(Number.class); assertThat(numbers[2]).isInstanceOf(Number.class); assertThat(numbers[3]).isInstanceOf(Number.class); assertThat(((Number)numbers[0]).intValue()).isEqualTo(127); assertThat(((Number)numbers[1]).intValue()).isEqualTo(32767); assertThat(((Number)numbers[2]).intValue()).isEqualTo(2147483647); assertThat(((Number)numbers[3]).longValue()).isEqualTo(9223372036854775807L); }
@Test public void query_boxed_numbers_positive() { final String queryText = "select 127 as B, 32767 as S, 2147483647 as I, 9223372036854775807 as L from X1"; final SqlQuery<BoxedNumbers> query = new SqlQuery<BoxedNumbers>(queryText, rowOf(structOf(BoxedNumbers.class))); BoxedNumbers bn = query(query); assertThat(bn.B).isEqualTo((byte) 127); assertThat(bn.S).isEqualTo((short)32767); assertThat(bn.I).isEqualTo(2147483647); assertThat(bn.L).isEqualTo(9223372036854775807L); }
@Test public void query_primitive_numbers_positive() { final String queryText = "select 127 as B, 32767 as S, 2147483647 as I, 9223372036854775807 as L from X1"; final SqlQuery<PrimitiveNumbers> query = new SqlQuery<PrimitiveNumbers>(queryText, rowOf(structOf(PrimitiveNumbers.class))); PrimitiveNumbers pn = query(query); assertThat(pn.B).isEqualTo((byte)127); assertThat(pn.S).isEqualTo((short)32767); assertThat(pn.I).isEqualTo(2147483647); assertThat(pn.L).isEqualTo(9223372036854775807L); }
@Test public void query_primitive_numbers_negative() { final String queryText = "select -128 as B, -32768 as S, -2147483648 as I, -9223372036854775808 as L from X1"; final SqlQuery<PrimitiveNumbers> query = new SqlQuery<PrimitiveNumbers>(queryText, rowOf(structOf(PrimitiveNumbers.class))); PrimitiveNumbers pn = query(query); assertThat(pn.B).isEqualTo((byte)-128); assertThat(pn.S).isEqualTo((short)-32768); assertThat(pn.I).isEqualTo(-2147483648); assertThat(pn.L).isEqualTo(-9223372036854775808L); }
@Test public void long_array_basic() { SqlQuery<long[]> query = new SqlQuery<long[]>("select 11, 22, 33", rowOf(arrayOfLongs())); long[] array = query(query); assertThat(array).containsExactly(11L,22L,33L); }
@Test public void array_row_basic() { SqlQuery<Integer[]> query = new SqlQuery<Integer[]>("select 11, 22, 33", rowOf(arrayOf(3, Integer.class))); Integer[] array = query(query); assertThat(array).isEqualTo(new Integer[] {11, 22, 33}); }
@Test public void int_array_basic() { SqlQuery<int[]> query = new SqlQuery<int[]>("select 11, 22, 33", rowOf(arrayOfInts())); int[] array = query(query); assertThat(array).containsExactly(11,22,33); }
@Test public void query_calendar_values_now() { String queryText = "select NOW as javaDate, NOW as sqlDate, NOW as sqlTimestamp, NOW as sqlTime"; if (isOracle) queryText += " from dual"; SqlQuery<CalendarValues> query = new SqlQuery<CalendarValues>(queryText, rowOf(structOf(CalendarValues.class))) .rewrite(Rewriters.replace("NOW", sqlNow())); CalendarValues cv = query(query); assertThat(cv.javaDate) .isExactlyInstanceOf(java.util.Date.class); assertThat(cv.sqlDate) .isExactlyInstanceOf(java.sql.Date.class); assertThat(cv.sqlTimestamp).isExactlyInstanceOf(java.sql.Timestamp.class); assertThat(cv.sqlTime) .isExactlyInstanceOf(java.sql.Time.class); }
@Test public void query_number_basic() { final String[] createTable = new String[] { "create table basic_numbers_table (B tinyint, S smallint, I integer, L bigint)", "insert into basic_numbers_table values (10, 1000, 1000000, 1000000000000)" }; final String query = "select * from basic_numbers_table"; JdbcIntermediateSession session = openSession(); performStatements(session, createTable); JdbcIntermediateSeance seance = session.openSeance(query, null); seance.execute(); JdbcIntermediateCursor<Number[]> cursor = seance.openDefaultCursor(Layouts.rowOf(arrayOf(4, Number.class))); Number[] row = cursor.fetch(); seance.close(); assertThat(row).isNotNull() .hasSize(4); assertThat(row[0]).isInstanceOf(Byte.class).isEqualTo((byte) 10); assertThat(row[1]).isInstanceOf(Short.class).isEqualTo((short) 1000); assertThat(row[2]).isInstanceOf(Integer.class).isEqualTo(1000000); assertThat(row[3]).isInstanceOf(Long.class).isEqualTo(1000000000000L); }
@Test public void return_one_basic_struct() { final String queryText = "select 44 as the_int_value, \n" + " 'million' as the_string_value \n"; final SqlQuery<IntAndString> query = new SqlQuery<IntAndString>(queryText, rowOf(structOf(IntAndString.class))); myFacade.inTransaction(new InTransactionNoResult() { @Override public void run(@NotNull final DBTransaction tran) { IntAndString bs = tran.query(query).run(); assertThat(bs).isNotNull(); assertThat(bs.the_int_value).isEqualTo(44); assertThat(bs.the_string_value).isEqualTo("million"); } }); }
@Test public void query_single_row_array_of_Integers() { String queryText = "select 123, 456, 789"; JdbcIntermediateSeance seance = query(queryText); JdbcIntermediateCursor<Integer[]> cursor = seance.openDefaultCursor(rowOf(arrayOf(3, Integer.class))); assertThat(cursor.isOpened()).isTrue(); Integer[] array = cursor.fetch(); assertThat(array).isNotNull() .containsExactly(123, 456, 789); assertThat(cursor.isOpened()).isFalse(); }
@Test public void query_floats() { final String[] createTable = new String[] { "create table float_numbers_table (F float, D double, R real)", "insert into float_numbers_table values (3.1415, 2.718281828, 26.74)" }; final String query = "select * from float_numbers_table"; JdbcIntermediateSession session = openSession(); performStatements(session, createTable); JdbcIntermediateSeance seance = session.openSeance(query, null); seance.execute(); JdbcIntermediateCursor<Object[]> cursor = seance.openDefaultCursor(Layouts.rowOf(arrayOf(Float.class, Double.class, Number.class))); Object[] row = cursor.fetch(); seance.close(); assertThat(row).isNotNull() .hasSize(3); assertThat(row[0]).isInstanceOf(Float.class).isEqualTo(3.1415f); assertThat(row[1]).isInstanceOf(Double.class).isEqualTo(2.718281828d); assertThat(row[2]).isInstanceOfAny(Float.class, Double.class); }
@Test public void query_decimals() { final String[] createTable = new String[] { "create table decimal_numbers_table (D1 decimal(24), D2 decimal(36,6))", "insert into decimal_numbers_table values (123456781234567812345678, 123456789012345678901234567890.666666)" }; final String query = "select * from decimal_numbers_table"; JdbcIntermediateSession session = openSession(); performStatements(session, createTable); JdbcIntermediateSeance seance = session.openSeance(query, null); seance.execute(); JdbcIntermediateCursor<BigDecimal[]> cursor = seance.openDefaultCursor(Layouts.rowOf(arrayOf(2, BigDecimal.class))); BigDecimal[] row = cursor.fetch(); seance.close(); assertThat(row).isNotNull() .hasSize(2); assertThat(row[0]).isInstanceOf(BigDecimal.class).isEqualTo(new BigDecimal("123456781234567812345678")); assertThat(row[1]).isInstanceOf(BigDecimal.class).isEqualTo(new BigDecimal("123456789012345678901234567890.666666")); }