org.sql2o.Connection Java Examples

public void delete(int accountId, String eventId)
{
	try (Connection con = sql2o.open())
	{
		if (eventId == null)
		{
			con.createQuery(DELETE_LOOT_ACCOUNT)
				.addParameter("accountId", accountId)
				.executeUpdate();
		}
		else
		{
			con.createQuery(DELETE_LOOT_ACCOUNT_EVENTID)
				.addParameter("accountId", accountId)
				.addParameter("eventId", eventId)
				.executeUpdate();
		}
	}
}
 

private synchronized PlayerEntity findOrCreatePlayer(Connection con, String username)
{
	PlayerEntity playerEntity = con.createQuery("select * from player where name = :name")
		.addParameter("name", username)
		.executeAndFetchFirst(PlayerEntity.class);
	if (playerEntity != null)
	{
		return playerEntity;
	}

	Instant now = Instant.now();

	int id = con.createQuery("insert into player (name, tracked_since) values (:name, :tracked_since)")
		.addParameter("name", username)
		.addParameter("tracked_since", now)
		.executeUpdate()
		.getKey(int.class);

	playerEntity = new PlayerEntity();
	playerEntity.setId(id);
	playerEntity.setName(username);
	playerEntity.setTracked_since(now);
	playerEntity.setLast_updated(now);
	return playerEntity;
}
 

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

public ArchiveEntry findArchive(Connection con, IndexEntry index,
	int archiveId, int nameHash, int crc, int revision)
{
	if (findArchive == null)
	{
		findArchive = con.createQuery("select distinct archive.id, archive.archiveId, archive.nameHash,"
			+ " archive.crc, archive.revision, archive.hash from archive "
			+ " join index_archive on index_archive.archive = archive.id"
			+ " join `index` on index.id = index_archive.index"
			+ " where archive.archiveId = :archiveId"
			+ " and archive.nameHash = :nameHash"
			+ " and archive.crc = :crc"
			+ " and archive.revision = :revision"
			+ " and index.indexId = :indexId");
	}

	ArchiveEntry entry = findArchive
		.addParameter("archiveId", archiveId)
		.addParameter("nameHash", nameHash)
		.addParameter("crc", crc)
		.addParameter("revision", revision)
		.addParameter("indexId", index.getIndexId())
		.executeAndFetchFirst(ArchiveEntry.class);
	return entry;
}
 

private PriceEntry getPrice(Connection con, int itemId, Instant time)
{
	if (time != null)
	{
		return con.createQuery("select item, name, price, time, fetched_time from prices t1 join items t2 on t1.item=t2.id where item = :item and time <= :time order by time desc limit 1")
			.addParameter("item", itemId)
			.addParameter("time", time.toString())
			.executeAndFetchFirst(PriceEntry.class);
	}
	else
	{
		return con.createQuery("select item, name, price, time, fetched_time from prices t1 join items t2 on t1.item=t2.id where item = :item order by time desc limit 1")
			.addParameter("item", itemId)
			.executeAndFetchFirst(PriceEntry.class);
	}
}
 

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

public void createTable() {
	final String tableSql =
			"CREATE TABLE IF NOT EXISTS " + FILE_TABLE + " ( "
				    + "id IDENTITY NOT NULL PRIMARY KEY auto_increment, "
				    + "path NVARCHAR NOT NULL UNIQUE, "
				    + "is_file BOOLEAN NOT NULL, "
				    + "content_hash NVARCHAR(64) NOT NULL, "
				    + "is_synchronized BOOLEAN NOT NULL DEFAULT(false), "
				    + "is_uploaded BOOLEAN NOT NULL DEFAULT(false), "
				    + "current_state NVARCHAR(32), "
				    + "next_state NVARCHAR(32), "
				    + "to_delete BOOLEAN NOT NULL DEFAULT(false) "
			+ ");";


		final String indexSql =
				"CREATE UNIQUE INDEX IF NOT EXISTS files_path "
				+ "ON "+ FILE_TABLE + " (path);";

	try (Connection con = sql2o.beginTransaction()) {
		con.createQuery(tableSql).executeUpdate();
		con.createQuery(indexSql).executeUpdate();
		con.commit();
	}
}
 

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

public String get(ExamineType type, int id)
{
	try (Connection con = sql2o.open())
	{
		ExamineEntry entry = con.createQuery("select text from examine where type = :type and id = :id "
			+ "order by count desc limit 1")
			.addParameter("type", type.toString())
			.addParameter("id", id)
			.executeAndFetchFirst(ExamineEntry.class);

		if (entry != null)
		{
			return entry.getText();
		}
	}

	return null;
}
 

@Override
public EventId persist(PaletteLabel storageUnit, Object event) {
    String json = Persistence.serialization.serialize(event);
    String alias = Persistence.serialization.of(event.getClass()).getAlias();

    try (Connection connection = sql2o.beginTransaction()) {
        Long eventId = connection.createQuery(
                "insert into warehouse.ProductStockHistory(refNo, unit, type, content) " +
                        "values (:refNo, :unit, :type, cast(:content AS json))", true)
                .addParameter("refNo", storageUnit.getRefNo())
                .addParameter("unit", storageUnit.getId())
                .addParameter("type", alias)
                .addParameter("content", json)
                .executeUpdate()
                .getKey(Long.class);

        if (archiveUnit.test(event)) {
            connection.createQuery("update warehouse.ProductStockHistory set inStock = false where unit = :unit")
                    .addParameter("unit", storageUnit.getId())
                    .executeUpdate();
        }
        connection.commit();
        return new EventId(eventId);
    }
}
 

/**
 * Creates tables and indices (if they do not exist yet)
 */
public void createTable() {
	final String tableSql =
			"CREATE TABLE IF NOT EXISTS " + REMOTE_FILE_TABLE + " ( "
					+ "id IDENTITY NOT NULL PRIMARY KEY auto_increment, "
					+ "path NVARCHAR NOT NULL UNIQUE, "
					+ "is_file BOOLEAN NOT NULL, "
					+ "content_hash NVARCHAR(64) NOT NULL, "
					+ "to_delete BOOLEAN NOT NULL DEFAULT(FALSE)"
			+ ");";

	final String indexSql =
			"CREATE UNIQUE INDEX IF NOT EXISTS remote_files_path "
			+ "ON "+ REMOTE_FILE_TABLE + " (path);";


	try (Connection con = sql2o.beginTransaction()) {
		con.createQuery(tableSql).executeUpdate();
		con.createQuery(indexSql).executeUpdate();
		con.commit();
	}
}
 

@GetMapping("/logout")
public void logout(HttpServletRequest request, HttpServletResponse response) throws IOException
{
	SessionEntry session = auth.handle(request, response);

	if (session == null)
	{
		return;
	}

	auth.invalidate(session.getUuid());

	try (Connection con = sql2o.open())
	{
		con.createQuery("delete from sessions where uuid = :uuid")
			.addParameter("uuid", session.getUuid().toString())
			.executeUpdate();
	}
}
 

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

@Override
public UUID createPost(String title, String content, List<String> categories) {
    try (Connection conn = sql2o.beginTransaction()) {
        UUID postUuid = uuidGenerator.generate();
        conn.createQuery("insert into posts(post_uuid, title, content, publishing_date) VALUES (:post_uuid, :title, :content, :date)")
                .addParameter("post_uuid", postUuid)
                .addParameter("title", title)
                .addParameter("content", content)
                .addParameter("date", new Date())
                .executeUpdate();
        categories.forEach((category) ->
                conn.createQuery("insert into posts_categories(post_uuid, category) VALUES (:post_uuid, :category)")
                .addParameter("post_uuid", postUuid)
                .addParameter("category", category)
                .executeUpdate());
        conn.commit();
        return postUuid;
    }
}
 

public ArchiveEntry findArchiveByName(Connection con, CacheEntry cache, IndexType index, int nameHash)
{
	return con.createQuery("select archive.id, archive.archiveId, archive.nameHash,"
		+ " archive.crc, archive.revision, archive.hash from archive "
		+ "join index_archive on index_archive.archive = archive.id "
		+ "join `index` on index.id = index_archive.index "
		+ "where index.cache = :cacheId "
		+ "and index.indexId = :indexId "
		+ "and archive.nameHash = :nameHash "
		+ "limit 1")
		.addParameter("cacheId", cache.getId())
		.addParameter("indexId", index.getNumber())
		.addParameter("nameHash", nameHash)
		.executeAndFetchFirst(ArchiveEntry.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);
    }
}
 

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

/**
 * 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 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);
    }
}
 

@Override
public List<Post> getAllPosts() {
    try (Connection conn = sql2o.open()) {
        List<Post> posts = conn.createQuery("select * from posts")
                .executeAndFetch(Post.class);
        posts.forEach((post) -> post.setCategories(getCategoriesFor(conn, post.getPost_uuid())));
        return posts;
    }
}
 

@Autowired
public LootTrackerService(@Qualifier("Runelite SQL2O") Sql2o sql2o)
{
	this.sql2o = sql2o;

	// Ensure necessary tables exist
	try (Connection con = sql2o.open())
	{
		con.createQuery(CREATE_KILLS).executeUpdate();
		con.createQuery(CREATE_DROPS).executeUpdate();
	}
}
 

public GrandExchangeEntry get(int itemId)
{
	try (Connection con = sql2o.open())
	{
		return con.createQuery("SELECT item_id, buy_average, sell_average, overall_average, last_update"
			+ " FROM osb_ge WHERE item_id = :itemId")
			.addParameter("itemId", itemId)
			.executeAndFetchFirst(GrandExchangeEntry.class);
	}
}
 

public CacheEntry findCache(Connection con, int cacheId)
{
	return con.createQuery("select id, revision, date from cache "
		+ "where id = :cacheId")
		.addParameter("cacheId", cacheId)
		.executeAndFetchFirst(CacheEntry.class);
}
 

public boolean tableExists() {
	final String sql = String.format(
			"SELECT tbl.cnt FROM "
			+ "(SELECT count(*) cnt FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = UPPER('%s')) as tbl",
			REMOTE_FILE_TABLE);

	try (Connection con = sql2o.open()) {
		int cnt = con.createQuery(sql).executeScalar(Integer.class);
		return cnt > 0;
	}
}
 

public ArchiveEntry findArchiveForIndex(Connection con, IndexEntry indexEntry, int archiveId)
{
	return con.createQuery("select archive.id, archive.archiveId, archive.nameHash,"
		+ " archive.crc, archive.revision, archive.hash from index_archive "
		+ "join archive on index_archive.archive = archive.id "
		+ "where index_archive.index = :id "
		+ "and archive.archiveId = :archiveId")
		.addParameter("id", indexEntry.getId())
		.addParameter("archiveId", archiveId)
		.executeAndFetchFirst(ArchiveEntry.class);
}
 

public ResultSetIterable<ArchiveEntry> findArchivesForIndex(Connection con, IndexEntry indexEntry)
{
	return con.createQuery("select archive.id, archive.archiveId, archive.nameHash,"
		+ " archive.crc, archive.revision, archive.hash from index_archive "
		+ "join archive on index_archive.archive = archive.id "
		+ "where index_archive.index = :id")
		.addParameter("id", indexEntry.getId())
		.executeAndFetchLazy(ArchiveEntry.class);
}
 

public IndexEntry findIndexForCache(Connection con, CacheEntry cache, int indexId)
{
	return con.createQuery("select id, indexId, crc, revision from `index` "
		+ "where cache = :id "
		+ "and indexId = :indexId")
		.addParameter("id", cache.getId())
		.addParameter("indexId", indexId)
		.executeAndFetchFirst(IndexEntry.class);
}
 

public ArchiveEntry findArchiveForTypeAndName(CacheEntry cache, IndexType index, int nameHash)
{
	try (Connection con = sql2o.open())
	{
		CacheDAO cacheDao = new CacheDAO();
		return cacheDao.findArchiveByName(con, cache, index, nameHash);
	}
}
 

public ArchiveEntry findArchiveForIndex(IndexEntry indexEntry, int archiveId)
{
	try (Connection con = sql2o.open())
	{
		CacheDAO cacheDao = new CacheDAO();
		return cacheDao.findArchiveForIndex(con, indexEntry, archiveId);
	}
}