org.sql2o.Query Java Examples

@Test
public void whenLazyFetch_thenResultsAreObjects() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.open()) {
        connection.createQuery("create table PROJECT_16 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_16 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_16 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')").executeUpdate();
        Query query = connection.createQuery("select * from PROJECT_16 order by id");
        try(ResultSetIterable<Project> projects = query.executeAndFetchLazy(Project.class)) {
            for(Project p : projects) {
                assertNotNull(p.getName());
                assertNotNull(p.getUrl());
                assertNull(p.getCreationDate());
            }
        }
        connection.createQuery("drop table PROJECT_16").executeUpdate();
    }
}
 

@Test
public void whenBatch_thenMultipleInserts() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.beginTransaction()) {
        connection.createQuery("create table PROJECT_15 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))").executeUpdate();
        Query query = connection.createQuery("INSERT INTO PROJECT_15 (NAME, URL) VALUES (:name, :url)");
        for(int i = 0; i < 1000; i++) {
            query.addParameter("name", "tutorials" + i);
            query.addParameter("url", "https://github.com/eugenp/tutorials" + i);
            query.addToBatch();
        }
        query.executeBatch();
        connection.commit();
    }
    try(Connection connection = sql2o.beginTransaction()) {
        assertEquals(1000L, connection.createQuery("SELECT count(*) FROM PROJECT_15").executeScalar());
    }
}
 

@Test
public void whenParameters_thenReplacement() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.open()) {
        connection.createQuery("create table PROJECT_10 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))").executeUpdate();
        Query query = connection.createQuery("INSERT INTO PROJECT_10 (NAME, URL) VALUES (:name, :url)")
                .addParameter("name", "REST with Spring")
                .addParameter("url", "github.com/eugenp/REST-With-Spring");
        assertEquals(1, query.executeUpdate().getResult());

        List<Project> list = connection.createQuery("SELECT * FROM PROJECT_10 WHERE NAME = 'REST with Spring'").executeAndFetch(Project.class);
        assertEquals(1, list.size());

        connection.createQuery("drop table PROJECT_10").executeUpdate();
    }
}
 

@Test
public void whenFetchTable_thenResultsAreRows() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.open()) {
        connection.createQuery("create table PROJECT_5 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100), creation_date date)").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_5 (NAME, URL, creation_date) VALUES ('tutorials', 'github.com/eugenp/tutorials', '2019-01-01')").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_5 (NAME, URL, creation_date) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring', '2019-02-01')").executeUpdate();
        Query query = connection.createQuery("select * from PROJECT_5 order by id");
        Table table = query.executeAndFetchTable();
        List<Row> rows = table.rows();

        assertEquals("tutorials", rows.get(0).getString("name"));
        assertEquals("REST with Spring", rows.get(1).getString("name"));

        connection.createQuery("drop table PROJECT_5").executeUpdate();
    }
}
 

@Test
public void whenFetchTable_thenResultsAreMaps() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.open()) {
        connection.createQuery("create table PROJECT_5 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100), creation_date date)").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_5 (NAME, URL, creation_date) VALUES ('tutorials', 'github.com/eugenp/tutorials', '2019-01-01')").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_5 (NAME, URL, creation_date) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring', '2019-02-01')").executeUpdate();
        Query query = connection.createQuery("select * from PROJECT_5 order by id");
        Table table = query.executeAndFetchTable();
        List<Map<String, Object>> list = table.asList();

        assertEquals("tutorials", list.get(0).get("name"));
        assertEquals("REST with Spring", list.get(1).get("name"));

        connection.createQuery("drop table PROJECT_5").executeUpdate();
    }
}
 

@Test
public void whenSelectMapping_thenResultsAreObjects() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.open()) {
        connection.createQuery("create table PROJECT_5 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100), creation_date date)").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_5 (NAME, URL, creation_date) VALUES ('tutorials', 'github.com/eugenp/tutorials', '2019-01-01')").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_5 (NAME, URL, creation_date) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring', '2019-02-01')").executeUpdate();
        Query query = connection.createQuery("select * from PROJECT_5 order by id")
                .addColumnMapping("CrEaTiOn_date", "creationDate");
        List<Project> list = query.executeAndFetch(Project.class);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        assertEquals("2019-01-01", sdf.format(list.get(0).getCreationDate()));
        assertEquals("2019-02-01", sdf.format(list.get(1).getCreationDate()));

        connection.createQuery("drop table PROJECT_5").executeUpdate();
    }
}
 

@Test
public void whenSelectAlias_thenResultsAreObjects() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.open()) {
        connection.createQuery("create table PROJECT_4 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100), creation_date date)").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_4 (NAME, URL, creation_date) VALUES ('tutorials', 'github.com/eugenp/tutorials', '2019-01-01')").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_4 (NAME, URL, creation_date) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring', '2019-02-01')").executeUpdate();
        Query query = connection.createQuery("select NAME, URL, creation_date as creationDate from PROJECT_4 order by id");
        List<Project> list = query.executeAndFetch(Project.class);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        assertEquals("2019-01-01", sdf.format(list.get(0).getCreationDate()));
        assertEquals("2019-02-01", sdf.format(list.get(1).getCreationDate()));

        connection.createQuery("drop table PROJECT_4").executeUpdate();
    }
}
 

@Test
public void whenSelect_thenResultsAreObjects() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.open()) {
        connection.createQuery("create table PROJECT_3 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_3 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_3 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')").executeUpdate();
        Query query = connection.createQuery("select * from PROJECT_3 order by id");
        List<Project> list = query.executeAndFetch(Project.class);

        assertEquals("tutorials", list.get(0).getName());
        assertEquals("REST with Spring", list.get(1).getName());

        connection.createQuery("drop table PROJECT_3").executeUpdate();
    }
}
 

@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();
    }
}
 

@Test
public void testDiscriminator608() throws SQLException {
    Connection connection = DbHelper.getDbConnection(DbHelper.TargetDB.POSTGRESQL);
    if ( connection == null) return;
    try {
        SingleConnectionDataSource scds = new SingleConnectionDataSource(connection, true);
        Sql2o sql2o = new Sql2o(scds);

        Query query = sql2o.open().createQuery("with t(id, type, name) as (values(1, 's', 'solar'), (2, 'e', 'electric')) select * from t" +
                "");
        query.setAutoDeriveColumnNames(true);

        JdbcMapperFactory jdbcMapperFactory = JdbcMapperFactory
                .newInstance()
                .discriminator(Device.class,
                        "type",
                        ResultSet::getString,
                        b ->
                                b.when("e", ElectricDevice.class)
                                        .when("s", SolarDevice.class));

        query.setResultSetHandlerFactoryBuilder(new SfmResultSetHandlerFactoryBuilder(jdbcMapperFactory));


        List<Device> devices = query.executeAndFetch(Device.class);


        assertEquals(2, devices.size());

        assertEquals(new SolarDevice(1, "s", "solar"), devices.get(0));
        assertEquals(new ElectricDevice(2, "e", "electric"), devices.get(1));



    } finally {
        connection.close();
    }
}
 

@Test
public void whenSelectCount_thenResultIsScalar() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.open()) {
        connection.createQuery("create table PROJECT_6 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100), creation_date date)").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_6 (NAME, URL, creation_date) VALUES ('tutorials', 'github.com/eugenp/tutorials', '2019-01-01')").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_6 (NAME, URL, creation_date) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring', '2019-02-01')").executeUpdate();
        Query query = connection.createQuery("select count(*) from PROJECT_6");
        assertEquals(2.0, query.executeScalar(Double.TYPE), 0.001);
        connection.createQuery("drop table PROJECT_6").executeUpdate();
    }
}
 

@Test
public void whenIdentityColumn_thenInsertReturnsNewId() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.open()) {
        connection.createQuery("create table PROJECT_2 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))").executeUpdate();
        Query query = connection.createQuery(
                "INSERT INTO PROJECT_2 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')",
                true);
        assertEquals(0, query.executeUpdate().getKey());
        query = connection.createQuery("INSERT INTO PROJECT_2 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')",
                true);
        assertEquals(1, query.executeUpdate().getKeys()[0]);
        connection.createQuery("drop table PROJECT_2").executeUpdate();
    }
}
 

public boolean remove(String refNo) {
    try (Connection transaction = database.beginTransaction();
         Query query = transaction
                 .createQuery("delete from warehouse.CartDefinition where refNo = :refNo")) {
        int rows = query.addParameter("refNo", refNo)
                .executeUpdate().getResult();
        return rows > 0;
    }
}
 

public boolean save(String refNo, String json) {
    if (validate(json)) return false;
    try (Connection transaction = database.beginTransaction();
         Query query = transaction
                 .createQuery("insert into warehouse.CartDefinition (refNo, definition) values (:refNo, :definition)")) {

        query.addParameter("refNo", refNo)
                .addParameter("definition", json);
        return true;
    }
}
 

public Optional<String> getJson(String refNo) {
    try (Connection transaction = database.beginTransaction();
         Query query = transaction
                 .createQuery("select definition from warehouse.CartDefinition where refNo = :refNo")) {
        String json = query.addParameter("refNo", refNo)
                .executeAndFetchFirst(String.class);
        return Optional.ofNullable(json);
    }
}
 

public List<String> getDefinedRefNos() {
    try (Connection transaction = database.beginTransaction();
         Query query = transaction
                 .createQuery("select refNo from warehouse.CartDefinition")) {
        return query.executeAndFetch(String.class);
    }
}
 

@Override
public List<Object> readEventsSince(String refNo, EventId since) {
    try (Connection connection = sql2o.open();
         Query query = connection.createQuery(
                 "select * from warehouse.ProductStockHistory where refNo = :refNo and id > :since order by id");
         ResultSetIterable<ProductStockHistoryEvent> result = query
                 .addParameter("refNo", refNo)
                 .addParameter("since", since.getId())
                 .executeAndFetchLazy(ProductStockHistoryEvent.class)) {
        return deserialize(result);
    }
}
 

@Override
public List<Object> readEventsInStock(String refNo) {
    try (Connection connection = sql2o.open();
         Query query = connection.createQuery(
                 "select * from warehouse.ProductStockHistory where refNo = :refNo and inStock = true order by id");
         ResultSetIterable<ProductStockHistoryEvent> result = query
                 .addParameter("refNo", refNo)
                 .executeAndFetchLazy(ProductStockHistoryEvent.class)) {
        return deserialize(result);
    }
}
 

/**
 * Store LootRecord
 *
 * @param records   LootRecords to store
 * @param accountId runelite account id to tie data too
 */
public void store(Collection<LootRecord> records, int accountId)
{
	try (Connection con = sql2o.beginTransaction())
	{
		Query killQuery = con.createQuery(INSERT_KILL_QUERY, true);
		Query insertDrop = con.createQuery(INSERT_DROP_QUERY);

		for (LootRecord record : records)
		{
			killQuery
				.addParameter("accountId", accountId)
				.addParameter("type", record.getType())
				.addParameter("eventId", record.getEventId())
				.executeUpdate();
			Object[] keys = con.getKeys();

			for (GameItem drop : record.getDrops())
			{
				insertDrop
					.addParameter("killId", keys[0])
					.addParameter("itemId", drop.getId())
					.addParameter("itemQuantity", drop.getQty())
					.addToBatch();
			}

			insertDrop.executeBatch();
		}

		con.commit(false);
	}
}
 

public List<PriceEntry> fetchPrices()
{
	try (Connection con = sql2o.beginTransaction())
	{
		Query query = con.createQuery("select t2.item, t3.name, t2.time, prices.price, prices.fetched_time from (select t1.item as item, max(t1.time) as time from prices t1 group by item) t2 " +
			" join prices on t2.item=prices.item and t2.time=prices.time" +
			" join items t3 on t2.item=t3.id");
		return query.executeAndFetch(PriceEntry.class);
	}
}
 

private List<PriceEntry> fetchPrice(int itemId)
{
	RSPrices rsprice;
	try
	{
		rsprice = fetchRSPrices(itemId);
	}
	catch (IOException ex)
	{
		log.warn("unable to fetch price for item {}", itemId, ex);
		return null;
	}

	try (Connection con = sql2o.beginTransaction())
	{
		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())
		{
			long ts = entry.getKey(); // ms since epoch
			int price = entry.getValue(); // gp

			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(false);

		return entries;
	}
}
 

@Scheduled(initialDelay = 1000 * 5, fixedDelay = 1000 * 60 * 30)
private void updateDatabase()
{
	try
	{
		Map<Integer, OsbuddySummaryItem> summary = CLIENT.getSummary();

		try (Connection con = sql2o.beginTransaction())
		{
			Instant updateTime = Instant.now();

			Query query = con.createQuery("INSERT INTO osb_ge (item_id, buy_average, sell_average, overall_average,"
				+ " last_update) VALUES (:itemId, :buyAverage, :sellAverage, :overallAverage, :lastUpdate)"
				+ " ON DUPLICATE KEY UPDATE buy_average = VALUES(buy_average), sell_average = VALUES(sell_average),"
				+ " overall_average = VALUES(overall_average), last_update = VALUES(last_update)");

			for (Map.Entry<Integer, OsbuddySummaryItem> entry : summary.entrySet())
			{
				Integer itemId = entry.getKey();
				OsbuddySummaryItem item = entry.getValue();

				if (item.getBuy_average() <= 0 || item.getSell_average() <= 0 || item.getOverall_average() <= 0)
				{
					continue;
				}

				query
					.addParameter("itemId", itemId)
					.addParameter("buyAverage", item.getBuy_average())
					.addParameter("sellAverage", item.getSell_average())
					.addParameter("overallAverage", item.getOverall_average())
					.addParameter("lastUpdate", Timestamp.from(updateTime))
					.addToBatch();
			}

			query.executeBatch();
			con.commit(false);
		}
	}
	catch (IOException e)
	{
		log.warn("Error while updating the osb grand exchange table", e);
	}
}