Examples with Query org.sql2o.Query used on opensource projects

Example 1 with Query

use of org.sql2o.Query in project sql2o by aaberg.

the class PojoPerformanceTest method createPostTable.

private void createPostTable() {
    sql2o.createQuery("DROP TABLE IF EXISTS post").executeUpdate();
    sql2o.createQuery("\n CREATE TABLE post" + "\n (" + "\n     id INT NOT NULL IDENTITY PRIMARY KEY" + "\n   , text VARCHAR(255)" + "\n   , creation_date DATETIME" + "\n   , last_change_date DATETIME" + "\n   , counter1 INT" + "\n   , counter2 INT" + "\n   , counter3 INT" + "\n   , counter4 INT" + "\n   , counter5 INT" + "\n   , counter6 INT" + "\n   , counter7 INT" + "\n   , counter8 INT" + "\n   , counter9 INT" + "\n )" + "\n;").executeUpdate();
    Random r = new Random();
    Query insQuery = sql2o.createQuery("insert into post (text, creation_date, last_change_date, counter1, counter2, counter3, counter4, counter5, counter6, counter7, counter8, counter9) values (:text, :creation_date, :last_change_date, :counter1, :counter2, :counter3, :counter4, :counter5, :counter6, :counter7, :counter8, :counter9)");
    for (int idx = 0; idx < ITERATIONS; idx++) {
        insQuery.addParameter("text", "a name " + idx).addParameter("creation_date", new DateTime(System.currentTimeMillis() + r.nextInt()).toDate()).addParameter("last_change_date", new DateTime(System.currentTimeMillis() + r.nextInt()).toDate()).addParameter("counter1", r.nextDouble() > 0.5 ? r.nextInt() : null).addParameter("counter2", r.nextDouble() > 0.5 ? r.nextInt() : null).addParameter("counter3", r.nextDouble() > 0.5 ? r.nextInt() : null).addParameter("counter4", r.nextDouble() > 0.5 ? r.nextInt() : null).addParameter("counter5", r.nextDouble() > 0.5 ? r.nextInt() : null).addParameter("counter6", r.nextDouble() > 0.5 ? r.nextInt() : null).addParameter("counter7", r.nextDouble() > 0.5 ? r.nextInt() : null).addParameter("counter8", r.nextDouble() > 0.5 ? r.nextInt() : null).addParameter("counter9", r.nextDouble() > 0.5 ? r.nextInt() : null).addToBatch();
    }
    insQuery.executeBatch();
}

Example 2 with Query

use of org.sql2o.Query in project sql2o by aaberg.

the class OracleTest method testUUiID.

@Test
@Ignore
public void testUUiID() {
    UUID uuid1 = UUID.randomUUID();
    UUID uuid2 = UUID.randomUUID();
    final String ddl = "create table testUUID(id integer primary key, uuidval raw(16))";
    final String insertSql = "insert into testUUID(id, uuidval) values (:id, :val)";
    final String selectSql = "select uuidval from testUUID where id = :id";
    try {
        try (Connection connection = sql2o.open()) {
            connection.createQuery(ddl).executeUpdate();
            Query insertQuery = connection.createQuery(insertSql);
            insertQuery.addParameter("id", 1).addParameter("val", uuid1).executeUpdate();
            insertQuery.addParameter("id", 2).addParameter("val", uuid2).executeUpdate();
            Query selectQuery = connection.createQuery(selectSql);
            UUID uuid1FromDb = selectQuery.addParameter("id", 1).executeScalar(UUID.class);
            UUID uuid2FromDb = selectQuery.addParameter("id", 2).executeScalar(UUID.class);
            assertEquals(uuid1, uuid1FromDb);
            assertEquals(uuid2, uuid2FromDb);
        }
    } catch (Exception e) {
        e.printStackTrace();
        fail("test failed. Exception");
    } finally {
        try (Connection con = sql2o.open()) {
            con.createQuery("drop table testUUID").executeUpdate();
        }
    }
}

Example 3 with Query

use of org.sql2o.Query in project sql2o by aaberg.

the class PostgresTest method testIssue10StatementsOnPostgres_noTransaction.

@Test
public void testIssue10StatementsOnPostgres_noTransaction() {
    try {
        try (Connection connection = sql2o.open()) {
            connection.createQuery("create table test_table(id SERIAL, val varchar(20))").executeUpdate();
        }
        try (Connection connection = sql2o.open()) {
            Long key = connection.createQuery("insert into test_table (val) values(:val)", true).addParameter("val", "something").executeUpdate().getKey(Long.class);
            assertNotNull(key);
            assertTrue(key > 0);
            String selectSql = "select id, val from test_table";
            Table resultTable = connection.createQuery(selectSql).executeAndFetchTable();
            assertThat(resultTable.rows().size(), is(1));
            Row resultRow = resultTable.rows().get(0);
            assertThat(resultRow.getLong("id"), equalTo(key));
            assertThat(resultRow.getString("val"), is("something"));
        }
    } finally {
        try (final Connection connection = sql2o.open();
            final Query query = connection.createQuery("drop table if exists test_table")) {
            query.executeUpdate();
        }
    }
}

Example 4 with Query

use of org.sql2o.Query in project runelite by runelite.

the class XteaService method submit.

@RequestMapping(method = POST)
public void submit(@RequestBody XteaRequest xteaRequest) {
    try (Connection con = sql2o.beginTransaction()) {
        CacheEntry cache = cacheService.findMostRecent();
        if (cache == null) {
            throw new InternalServerErrorException("No most recent cache");
        }
        Query query = con.createQuery("insert into xtea (region, rev, key1, key2, key3, key4) " + "values (:region, :rev, :key1, :key2, :key3, :key4)");
        for (XteaKey key : xteaRequest.getKeys()) {
            int region = key.getRegion();
            int[] keys = key.getKeys();
            XteaEntry xteaEntry = findLatestXtea(con, region);
            if (keys.length != 4) {
                throw new IllegalArgumentException("Key length must be 4");
            }
            // already have these?
            if (xteaEntry != null && xteaEntry.getKey1() == keys[0] && xteaEntry.getKey2() == keys[1] && xteaEntry.getKey3() == keys[2] && xteaEntry.getKey4() == keys[3]) {
                continue;
            }
            if (!checkKeys(cache, region, keys)) {
                continue;
            }
            query.addParameter("region", region).addParameter("rev", xteaRequest.getRevision()).addParameter("key1", keys[0]).addParameter("key2", keys[1]).addParameter("key3", keys[2]).addParameter("key4", keys[3]).addToBatch();
        }
        query.executeBatch();
        con.commit();
    }
}

Example 5 with Query

use of org.sql2o.Query in project sql2o by aaberg.

the class H2Tests method testUUID.

/**
 * Ref issue #73
 */
@Test
public void testUUID() {
    try (Connection connection = new Sql2o(ds).beginTransaction()) {
        connection.createQuery("create table uuidtest(id uuid primary key, val uuid null)").executeUpdate();
        UUID uuid1 = UUID.randomUUID();
        UUID uuid2 = UUID.randomUUID();
        UUID uuid3 = UUID.randomUUID();
        UUID uuid4 = null;
        Query insQuery = connection.createQuery("insert into uuidtest(id, val) values (:id, :val)");
        insQuery.addParameter("id", uuid1).addParameter("val", uuid2).executeUpdate();
        insQuery.addParameter("id", uuid3).addParameter("val", uuid4).executeUpdate();
        Table table = connection.createQuery("select * from uuidtest").executeAndFetchTable();
        assertThat((UUID) table.rows().get(0).getObject("id"), is(equalTo(uuid1)));
        assertThat((UUID) table.rows().get(0).getObject("val"), is(equalTo(uuid2)));
        assertThat((UUID) table.rows().get(1).getObject("id"), is(equalTo(uuid3)));
        assertThat(table.rows().get(1).getObject("val"), is(nullValue()));
        connection.rollback();
    }
}