org.sql2o.Query Java Examples

The following examples show how to use org.sql2o.Query. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: Sql2oIntegrationTest.java    From tutorials with MIT License 6 votes vote down vote up
@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();
    }
}
 
Example #2
Source File: Sql2oIntegrationTest.java    From tutorials with MIT License 6 votes vote down vote up
@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());
    }
}
 
Example #3
Source File: Sql2oIntegrationTest.java    From tutorials with MIT License 6 votes vote down vote up
@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();
    }
}
 
Example #4
Source File: Sql2oIntegrationTest.java    From tutorials with MIT License 6 votes vote down vote up
@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();
    }
}
 
Example #5
Source File: Sql2oIntegrationTest.java    From tutorials with MIT License 6 votes vote down vote up
@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();
    }
}
 
Example #6
Source File: Sql2oIntegrationTest.java    From tutorials with MIT License 6 votes vote down vote up
@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();
    }
}
 
Example #7
Source File: Sql2oIntegrationTest.java    From tutorials with MIT License 6 votes vote down vote up
@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();
    }
}
 
Example #8
Source File: Sql2oIntegrationTest.java    From tutorials with MIT License 6 votes vote down vote up
@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();
    }
}
 
Example #9
Source File: Sql2oIntegrationTest.java    From SimpleFlatMapper with MIT License 6 votes vote down vote up
@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 #10
Source File: Sql2oIntegrationTest.java    From SimpleFlatMapper with MIT License 5 votes vote down vote up
@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();
    }
}
 
Example #11
Source File: Sql2oIntegrationTest.java    From tutorials with MIT License 5 votes vote down vote up
@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();
    }
}
 
Example #12
Source File: Sql2oIntegrationTest.java    From tutorials with MIT License 5 votes vote down vote up
@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();
    }
}
 
Example #13
Source File: CartDefinitionRepository.java    From ddd-wro-warehouse with MIT License 5 votes vote down vote up
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;
    }
}
 
Example #14
Source File: CartDefinitionRepository.java    From ddd-wro-warehouse with MIT License 5 votes vote down vote up
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;
    }
}
 
Example #15
Source File: CartDefinitionRepository.java    From ddd-wro-warehouse with MIT License 5 votes vote down vote up
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);
    }
}
 
Example #16
Source File: CartDefinitionRepository.java    From ddd-wro-warehouse with MIT License 5 votes vote down vote up
public List<String> getDefinedRefNos() {
    try (Connection transaction = database.beginTransaction();
         Query query = transaction
                 .createQuery("select refNo from warehouse.CartDefinition")) {
        return query.executeAndFetch(String.class);
    }
}
 
Example #17
Source File: ProductStockSql2oEventsStore.java    From ddd-wro-warehouse with MIT License 5 votes vote down vote up
@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);
    }
}
 
Example #18
Source File: ProductStockSql2oEventsStore.java    From ddd-wro-warehouse with MIT License 5 votes vote down vote up
@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);
    }
}
 
Example #19
Source File: LootTrackerService.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up
/**
 * 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);
	}
}
 
Example #20
Source File: ItemService.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up
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);
	}
}
 
Example #21
Source File: ItemService.java    From runelite with BSD 2-Clause "Simplified" License 4 votes vote down vote up
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;
	}
}
 
Example #22
Source File: OSBGrandExchangeService.java    From runelite with BSD 2-Clause "Simplified" License 4 votes vote down vote up
@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);
	}
}