org.sql2o.Sql2o Java Examples

@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 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 whenPOJOParameters_thenReplacement() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.open()) {
        connection.createQuery("create table PROJECT_11 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))").executeUpdate();

        Project project = new Project();
        project.setName("REST with Spring");
        project.setUrl("github.com/eugenp/REST-With-Spring");
        connection.createQuery("INSERT INTO PROJECT_11 (NAME, URL) VALUES (:name, :url)")
                .bind(project).executeUpdate();
        assertEquals(1, connection.getResult());

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

        connection.createQuery("drop table PROJECT_11").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 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();
    }
}
 

public static void main(String[] args) {
    Sql2o database = new Sql2o(args[0], args[1], args[2]);
    OpsSupport support = new OpsSupport();
    EventMappings mappings = new EventMappings();

    TLabelsFormats labels = new TLabelsFormats(0);
    ProductStockEventStore stockEvents = new ProductStockSql2oEventsStore(database);
    CartDefinitionRepository cartDefinitions = new CartDefinitionRepository(database, support);
    ProductStockEventSourcingRepository stocks = new ProductStockEventSourcingRepository(mappings, stockEvents, support, cartDefinitions);
    FifoViewProjection fifo = new FifoViewProjection(stockEvents, stocks, support);
    QualityReportService quality = new QualityReportService(stockEvents, mappings);
    mappings.dependencies(stocks, fifo, support);

    new ProductStocks(labels, stocks, fifo, support).exposeApi();
    new Inbox(labels, quality, support).exposeApi();
    new MasterData(cartDefinitions, support).exposeApi();
}
 

@Override
public void preHandle(Blade blade) {
    Ioc ioc = blade.ioc();

    boolean devMode = true;
    if (blade.environment().hasKey("app.dev")) {
        devMode = blade.environment().getBoolean("app.dev", true);
    }
    if (blade.environment().hasKey("app.devMode")) {
        devMode = blade.environment().getBoolean("app.devMode", true);
    }
    SqliteJdbc.importSql(devMode);

    Sql2o sql2o = new Sql2o(SqliteJdbc.DB_SRC, null, null);
    Base.open(sql2o);
    Commons.setSiteService(ioc.getBean(SiteService.class));
}
 

@Autowired
public ItemService(@Qualifier("Runelite SQL2O") Sql2o sql2o,
	CacheService cacheService)
{
	this.sql2o = sql2o;
	this.cacheService = cacheService;

	try (Connection con = sql2o.open())
	{
		con.createQuery(CREATE_ITEMS)
			.executeUpdate();

		con.createQuery(CREATE_PRICES)
			.executeUpdate();
	}
}
 

@Inject
public RemoteFileDao(DbContext dbContext) {
	this.dbContext = dbContext;
	this.sql2o = new Sql2o(this.dbContext.getDataSource());

	createTable();
}
 

@Autowired
public GrandExchangeService(
	@Qualifier("Runelite SQL2O") Sql2o sql2o,
	@Value("${runelite.ge.history}") int historyDays
)
{
	this.sql2o = sql2o;
	this.historyDays = historyDays;

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

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

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

	try (Connection con = sql2o.open())
	{
		con.createQuery(CREATE_EXAMINE)
			.executeUpdate();
	}
}
 

@Autowired
public XteaService(
	@Qualifier("Runelite SQL2O") Sql2o sql2o,
	CacheService cacheService
)
{
	this.sql2o = sql2o;
	this.cacheService = cacheService;

	try (Connection con = sql2o.beginTransaction())
	{
		con.createQuery(CREATE_SQL)
			.executeUpdate();
	}
}
 

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

@Test
public void whenTableCreated_thenInsertIsPossible() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.open()) {
        connection.createQuery("create table PROJECT_1 (id integer identity, name varchar(50), url varchar(100))").executeUpdate();
        assertEquals(0, connection.getResult());
        connection.createQuery("INSERT INTO PROJECT_1 VALUES (1, 'tutorials', 'github.com/eugenp/tutorials')").executeUpdate();
        assertEquals(1, connection.getResult());
        connection.createQuery("drop table PROJECT_1").executeUpdate();
    }
}
 

@Test
public void whenSql2oCreated_thenSuccess() {
    Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
    try(Connection connection = sql2o.open()) {
        java.sql.Connection jdbcConnection = connection.getJdbcConnection();
        assertFalse(jdbcConnection.isClosed());
    } catch (SQLException e) {
        fail(e.getMessage());
    }
}
 

@Autowired
public AccountService(
	@Qualifier("Runelite SQL2O") Sql2o sql2o,
	@Value("${oauth.client-id}") String oauthClientId,
	@Value("${oauth.client-secret}") String oauthClientSecret,
	@Value("${oauth.callback}") String oauthCallback,
	AuthFilter auth,
	RedisPool jedisPool
)
{
	this.sql2o = sql2o;
	this.oauthClientId = oauthClientId;
	this.oauthClientSecret = oauthClientSecret;
	this.oauthCallback = oauthCallback;
	this.auth = auth;
	this.jedisPool = jedisPool;

	try (Connection con = sql2o.open())
	{
		con.createQuery(CREATE_SESSIONS)
			.executeUpdate();

		con.createQuery(CREATE_USERS)
			.executeUpdate();

		try
		{
			con.createQuery(SESSIONS_FK)
				.executeUpdate();
		}
		catch (Sql2oException ex)
		{
			// Ignore, happens when index already exists
		}
	}
}
 

@Inject
public LocalFileDao(DbContext dbContext) {
	this.dbContext = dbContext;
	this.sql2o = new Sql2o(this.dbContext.getDataSource());

	createTable();
}
 

private static void dumpTableToCsv(final Path file, final String selectQuery, final Sql2o sql2o) {
	final String sql =
			"CALL "
			+ "CSVWRITE( "
			+ String.format("'%s', ", file.toString())
			+ String.format("'%s', 'charset=UTF-8 fieldSeparator=;' ", selectQuery)
			+ " );";

	try (Connection con = sql2o.open()) {
		con.createQuery(sql).executeUpdate();
	}
}
 

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

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

@Test
public void sql2oInsert() throws Exception {
    Sql2o sql2o = new Sql2o("jdbc:postgresql://localhost/postgres", "postgres", "");

    ProductStockEvent event = new ProductStockEvent(
            -1, null, "refNi", "NewEntry", "{\"key\":\"value\"}"
    );

    try (org.sql2o.Connection connection = sql2o.beginTransaction()) {
        connection.createQuery(
                "insert into warehouse.ProductStockHistory(refNo, type, content) " +
                        "values (:refNo, :type, cast(:content AS json))")
                .addParameter("refNo", event.getRefNo())
                .addParameter("type", event.getType())
                .addParameter("content", event.getContent())
                .executeUpdate();
        connection.commit();
    }

    List<ProductStockEvent> events = sql2o.open()
            .createQuery("select * from warehouse.ProductStockHistory where refNo = :refNo order by id")
            .addParameter("refNo", event.getRefNo())
            .executeAndFetch(ProductStockEvent.class);

    Assertions.assertThat(events)
            .isNotEmpty()
            .extracting(ProductStockEvent::getRefNo, ProductStockEvent::getType, ProductStockEvent::getContent)
            .contains(tuple(event.getRefNo(), event.getType(), event.getContent()));
}
 

@Autowired
public CacheUpdater(
	@Qualifier("Runelite Cache SQL2O") Sql2o sql2o,
	MinioClient minioClient
)
{
	this.sql2o = sql2o;
	this.minioClient = minioClient;
}
 

@Bean
@Qualifier("Runelite Cache SQL2O")
public Sql2o sql2o(DataSource dataSource)
{
	Map<Class, Converter> converters = new HashMap<>();
	converters.put(Instant.class, new InstantConverter());
	return new Sql2o(dataSource, new NoQuirks(converters));
}