Java Code Examples for org.sql2o.Connection

The following examples show how to use org.sql2o.Connection. These examples are extracted from open source projects. 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 Project: tutorials   Source File: Sql2oIntegrationTest.java    License: 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 2
Source Project: PeerWasp   Source File: RemoteFileDao.java    License: 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 3
Source Project: runelite   Source File: ExamineService.java    License: 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 4
Source Project: PeerWasp   Source File: LocalFileDao.java    License: 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 5
Source Project: runelite   Source File: ItemService.java    License: 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 Project: tutorials   Source File: Sql2oIntegrationTest.java    License: 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 Project: runelite   Source File: XpTrackerService.java    License: 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 8
Source Project: runelite   Source File: AccountService.java    License: 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 9
Source Project: tutorials   Source File: Sql2oIntegrationTest.java    License: 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 10
Source Project: tutorials   Source File: Sql2oIntegrationTest.java    License: 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 11
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 12
Source Project: runelite   Source File: CacheDAO.java    License: 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 13
Source Project: tutorials   Source File: Sql2oIntegrationTest.java    License: 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 14
Source Project: ddd-wro-warehouse   Source File: ProductStockSql2oEventsStore.java    License: 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 15
Source Project: BlogService_SparkExample   Source File: Sql2oModel.java    License: 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 Project: p2   Source File: TargetStore.java    License: BSD 3-Clause "New" or "Revised" License 5 votes vote down vote up
private TargetStore() {
    final Configuration configuration = Configuration.getInstance();
    HashMap<Class, Converter> converters = new HashMap<>();
    Adapter.register(converters);
    Quirks quirks = new NoQuirks(converters);
    database = new Sql2o(configuration.getDbUrl(), configuration.getDbUsername(), configuration.getDbPassword(), quirks);
    try (Connection connection = database.open()) {
        connection.createQuery(CREATE_TARGET_TABLE).executeUpdate();
    }
}
 
Example 17
Source Project: tutorials   Source File: Sql2oIntegrationTest.java    License: 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 18
Source Project: tutorials   Source File: Sql2oIntegrationTest.java    License: MIT License 5 votes vote down vote up
@Test
public void whenTransactionRollback_thenNoDataInserted() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.beginTransaction()) {
        connection.createQuery("create table PROJECT_12 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_12 (NAME, URL) VALUES ('tutorials', 'https://github.com/eugenp/tutorials')").executeUpdate();
        connection.createQuery("INSERT INTO PROJECT_12 (NAME, URL) VALUES ('REST with Spring', 'https://github.com/eugenp/REST-With-Spring')").executeUpdate();
        connection.rollback();
        List<Map<String, Object>> list = connection.createQuery("SELECT * FROM PROJECT_12").executeAndFetchTable().asList();
        assertEquals(0, list.size());
    }
}
 
Example 19
Source Project: BlogService_SparkExample   Source File: Sql2oModel.java    License: MIT License 5 votes vote down vote up
@Override
public boolean existPost(UUID post) {
    try (Connection conn = sql2o.open()) {
        List<Post> posts = conn.createQuery("select * from posts where post_uuid=:post")
                .addParameter("post", post)
                .executeAndFetch(Post.class);
        return posts.size() > 0;
    }
}
 
Example 20
Source Project: PeerWasp   Source File: LocalFileDao.java    License: MIT License 5 votes vote down vote up
public void deleteByPath(final Path file) {
	final String sql = String.format(
			"DELETE FROM %s WHERE path = :path", FILE_TABLE);

	try (Connection con = sql2o.open()) {
		con.createQuery(sql)
			.addParameter("path", file.toString())
			.executeUpdate();
	}
}
 
Example 21
public void add(int userId, GrandExchangeTrade grandExchangeTrade)
{
	try (Connection con = sql2o.open())
	{
		con.createQuery("insert into ge_trades (user, action, item, quantity, price) values (:user," +
			" :action, :item, :quantity, :price)")
			.addParameter("user", userId)
			.addParameter("action", grandExchangeTrade.isBuy() ? "BUY" : "SELL")
			.addParameter("item", grandExchangeTrade.getItemId())
			.addParameter("quantity", grandExchangeTrade.getQty())
			.addParameter("price", grandExchangeTrade.getSpent() / grandExchangeTrade.getQty())
			.executeUpdate();
	}
}
 
Example 22
public Collection<TradeEntry> get(int userId, int limit, int offset)
{
	try (Connection con = sql2o.open())
	{
		return con.createQuery("select id, user, action, item, quantity, price, time from ge_trades where user = :user limit :limit offset :offset")
			.addParameter("user", userId)
			.addParameter("limit", limit)
			.addParameter("offset", offset)
			.executeAndFetch(TradeEntry.class);
	}
}
 
Example 23
@Scheduled(fixedDelay = 60 * 60 * 1000)
public void expire()
{
	try (Connection con = sql2o.open())
	{
		con.createQuery("delete from ge_trades where time < current_timestamp - interval " + historyDays + " day")
			.executeUpdate();
	}
}
 
Example 24
Source Project: runelite   Source File: ExamineService.java    License: BSD 2-Clause "Simplified" License 5 votes vote down vote up
@Autowired
public ExamineService(@Qualifier("Runelite SQL2O") Sql2o sql2o)
{
	this.sql2o = sql2o;

	try (Connection con = sql2o.open())
	{
		con.createQuery(CREATE_EXAMINE)
			.executeUpdate();
	}
}
 
Example 25
Source Project: runelite   Source File: ExamineService.java    License: BSD 2-Clause "Simplified" License 5 votes vote down vote up
public void insert(ExamineType type, int id, String examine)
{
	try (Connection con = sql2o.open())
	{
		con.createQuery("insert into examine (type, id, time, count, text) values "
			+ "(:type, :id, :time, :count, :text) on duplicate key update count = count + 1")
			.addParameter("type", type.toString())
			.addParameter("id", id)
			.addParameter("time", Timestamp.from(Instant.now()))
			.addParameter("count", 1)
			.addParameter("text", examine)
			.executeUpdate();
	}
}
 
Example 26
Source Project: runelite   Source File: ItemService.java    License: BSD 2-Clause "Simplified" License 5 votes vote down vote up
public ItemEntry getItem(int itemId)
{
	try (Connection con = sql2o.open())
	{
		return con.createQuery("select id, name, description, type from items where id = :id")
			.addParameter("id", itemId)
			.executeAndFetchFirst(ItemEntry.class);
	}
}
 
Example 27
Source Project: BlogService_SparkExample   Source File: Sql2oModel.java    License: MIT License 5 votes vote down vote up
@Override
public Optional<Post> getPost(UUID uuid) {
    try (Connection conn = sql2o.open()) {
        List<Post> posts = conn.createQuery("select * from posts where post_uuid=:post_uuid")
                .addParameter("post_uuid", uuid)
                .executeAndFetch(Post.class);
        if (posts.size() == 0) {
            return Optional.empty();
        } else if (posts.size() == 1) {
            return Optional.of(posts.get(0));
        } else {
            throw new RuntimeException();
        }
    }
}
 
Example 28
Source Project: runelite   Source File: ItemService.java    License: BSD 2-Clause "Simplified" License 5 votes vote down vote up
public List<PriceEntry> getPrices(int... itemIds)
{
	try (Connection con = sql2o.open())
	{
		Set<Integer> seen = new HashSet<>();
		List<PriceEntry> priceEntries = new ArrayList<>(itemIds.length);

		for (int itemId : itemIds)
		{
			if (seen.contains(itemId))
			{
				continue;
			}
			seen.add(itemId);

			PriceEntry priceEntry = getPrice(con, itemId, null);

			if (priceEntry == null)
			{
				continue;
			}

			priceEntries.add(priceEntry);
		}

		return priceEntries;
	}
}
 
Example 29
Source Project: runelite   Source File: ItemService.java    License: BSD 2-Clause "Simplified" License 5 votes vote down vote up
public ItemEntry fetchItem(int itemId)
{
	try
	{
		RSItem rsItem = fetchRSItem(itemId);

		try (Connection con = sql2o.open())
		{
			con.createQuery("insert into items (id, name, description, type) values (:id,"
				+ " :name, :description, :type) ON DUPLICATE KEY UPDATE name = :name,"
				+ " description = :description, type = :type")
				.addParameter("id", rsItem.getId())
				.addParameter("name", rsItem.getName())
				.addParameter("description", rsItem.getDescription())
				.addParameter("type", rsItem.getType())
				.executeUpdate();
		}

		ItemEntry item = new ItemEntry();
		item.setId(itemId);
		item.setName(rsItem.getName());
		item.setDescription(rsItem.getDescription());
		item.setType(ItemType.of(rsItem.getType()));
		return item;
	}
	catch (IOException ex)
	{
		log.warn("unable to fetch item {}", itemId, ex);
		return null;
	}
}
 
Example 30
Source Project: PeerWasp   Source File: LocalFileDao.java    License: MIT License 5 votes vote down vote up
public FileComponent getFileByPath(final Path path) {
	final String sql = String.format(
			"SELECT %s FROM %s WHERE path = :path;", DEFAULT_COLUMNS, FILE_TABLE);

	try (Connection con = sql2o.open()) {
		return con.createQuery(sql)
				.addParameter("path", path.toString())
				.executeAndFetchFirst(new FileComponentResultSetHandler());
	}
}