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

Example 6 with Query

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

the class UUIDTest method testUUID.

@Test
public void testUUID() throws Exception {
    try (Connection connection = sql2o.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();
    }
}

Example 7 with Query

use of org.sql2o.Query in project SimpleFlatMapper by arnaudroger.

the class Sql2oIntegrationTest method testSql2O.

@Test
public void testSql2O() throws SQLException, ParseException {
    Connection connection = DbHelper.objectDb();
    try {
        SingleConnectionDataSource scds = new SingleConnectionDataSource(connection, true);
        Sql2o sql2o = new Sql2o(scds);
        Query query = sql2o.open().createQuery(DbHelper.TEST_DB_OBJECT_QUERY);
        query.setAutoDeriveColumnNames(true);
        query.setResultSetHandlerFactoryBuilder(new SfmResultSetHandlerFactoryBuilder());
        List<DbObject> dbObjects = query.executeAndFetch(DbObject.class);
        assertEquals(1, dbObjects.size());
        DbHelper.assertDbObjectMapping(dbObjects.get(0));
    } finally {
        connection.close();
    }
}

Example 8 with Query

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

the class ItemService method fetchPrice.

public List<PriceEntry> fetchPrice(int itemId) {
    try (Connection con = sql2o.beginTransaction()) {
        RSPrices rsprice = fetchRSPrices(itemId);
        List<PriceEntry> entries = new ArrayList<>();
        Instant now = Instant.now();
        Query query = con.createQuery("insert into prices (item, price, time, fetched_time) values (:item, :price, :time, :fetched_time) " + "ON DUPLICATE KEY UPDATE price = VALUES(price), fetched_time = VALUES(fetched_time)");
        for (Map.Entry<Long, Integer> entry : rsprice.getDaily().entrySet()) {
            // ms since epoch
            long ts = entry.getKey();
            // gp
            int price = entry.getValue();
            Instant time = Instant.ofEpochMilli(ts);
            PriceEntry priceEntry = new PriceEntry();
            priceEntry.setItem(itemId);
            priceEntry.setPrice(price);
            priceEntry.setTime(time);
            priceEntry.setFetched_time(now);
            entries.add(priceEntry);
            query.addParameter("item", itemId).addParameter("price", price).addParameter("time", time).addParameter("fetched_time", now).addToBatch();
        }
        query.executeBatch();
        con.commit();
        return entries;
    } catch (IOException ex) {
        log.warn("unable to fetch price for item {}", itemId, ex);
        return null;
    }
}

Example 9 with Query

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

the class ItemService method batchInsertItems.

private void batchInsertItems(RSSearch search) {
    try (Connection con = sql2o.beginTransaction()) {
        Query q = con.createQuery("insert into items (id, name, description, type) values (:id," + " :name, :description, :type) ON DUPLICATE KEY UPDATE name = :name," + " description = :description, type = :type");
        for (RSItem rsItem : search.getItems()) {
            q.addParameter("id", rsItem.getId()).addParameter("name", rsItem.getName()).addParameter("description", rsItem.getDescription()).addParameter("type", rsItem.getType()).addToBatch();
        }
        q.executeBatch();
        con.commit();
    }
}