org.sql2o.Connection Java Examples

The following examples show how to use org.sql2o.Connection. 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: LootTrackerService.java    From runelite with BSD 2-Clause "Simplified" License 6 votes vote down vote up
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();
		}
	}
}
 
Example #2
Source File: XpTrackerService.java    From runelite with BSD 2-Clause "Simplified" License 6 votes vote down vote up
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;
}
 
Example #3
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 #4
Source File: CacheDAO.java    From runelite with BSD 2-Clause "Simplified" License 6 votes vote down vote up
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;
}
 
Example #5
Source File: ItemService.java    From runelite with BSD 2-Clause "Simplified" License 6 votes vote down vote up
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);
	}
}
 
Example #6
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 #7
Source File: LocalFileDao.java    From PeerWasp with MIT License 6 votes vote down vote up
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();
	}
}
 
Example #8
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 #9
Source File: ExamineService.java    From runelite with BSD 2-Clause "Simplified" License 6 votes vote down vote up
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;
}
 
Example #10
Source File: ProductStockSql2oEventsStore.java    From ddd-wro-warehouse with MIT License 6 votes vote down vote up
@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);
    }
}
 
Example #11
Source File: RemoteFileDao.java    From PeerWasp with MIT License 6 votes vote down vote up
/**
 * 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();
	}
}
 
Example #12
Source File: AccountService.java    From runelite with BSD 2-Clause "Simplified" License 6 votes vote down vote up
@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();
	}
}
 
Example #13
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 #14
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 #15
Source File: Sql2oModel.java    From BlogService_SparkExample with MIT License 6 votes vote down vote up
@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;
    }
}
 
Example #16
Source File: CacheDAO.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up
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);
}
 
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: 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 #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: 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 #21
Source File: Sql2oModel.java    From BlogService_SparkExample with MIT License 5 votes vote down vote up
@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;
    }
}
 
Example #22
Source File: LootTrackerService.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up
@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();
	}
}
 
Example #23
Source File: OSBGrandExchangeService.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up
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);
	}
}
 
Example #24
Source File: CacheDAO.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up
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);
}
 
Example #25
Source File: RemoteFileDao.java    From PeerWasp with MIT License 5 votes vote down vote up
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;
	}
}
 
Example #26
Source File: CacheDAO.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up
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);
}
 
Example #27
Source File: CacheDAO.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up
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);
}
 
Example #28
Source File: CacheDAO.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up
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);
}
 
Example #29
Source File: CacheService.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up
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);
	}
}
 
Example #30
Source File: CacheService.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up
public ArchiveEntry findArchiveForIndex(IndexEntry indexEntry, int archiveId)
{
	try (Connection con = sql2o.open())
	{
		CacheDAO cacheDao = new CacheDAO();
		return cacheDao.findArchiveForIndex(con, indexEntry, archiveId);
	}
}