Java Code Examples for org.sql2o.Sql2o#open()

The following examples show how to use org.sql2o.Sql2o#open() . 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 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();
    }
}
Example 3
Source File: ItemService.java    From runelite with BSD 2-Clause "Simplified" License 6 votes vote down vote up 
@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();
	}
}
Example 4
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 5
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 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: 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 8
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 9
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 10
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 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: TargetStore.java    From p2 with 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 13
Source File: Sql2oIntegrationTest.java    From tutorials with MIT License 5 votes vote down vote up 
@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();
    }
}
Example 14
Source File: Sql2oIntegrationTest.java    From tutorials with MIT License 5 votes vote down vote up 
@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());
    }
}
Example 15
Source File: DaoUtils.java    From PeerWasp with MIT License 5 votes vote down vote up 
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();
	}
}
Example 16
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 17
Source File: OSBGrandExchangeService.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up 
@Autowired
public OSBGrandExchangeService(@Qualifier("Runelite SQL2O") Sql2o sql2o)
{
	this.sql2o = sql2o;

	try (Connection con = sql2o.open())
	{
		con.createQuery(CREATE_GRAND_EXCHANGE_PRICES).executeUpdate();
	}
}
Example 18
Source File: AccountService.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up 
@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
		}
	}
}
Example 19
Source File: ExamineService.java    From runelite with 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 20
Source File: GrandExchangeService.java    From runelite with BSD 2-Clause "Simplified" License 5 votes vote down vote up 
@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();
	}
}