Java源码示例：org.sql2o.Sql2o
示例1
@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));
}
示例2
@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();
}
}
示例3
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();
}
示例4
@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();
}
}
示例5
@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();
}
}
示例6
@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();
}
}
示例7
@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();
}
}
示例8
@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();
}
}
示例9
@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();
}
}
示例10
@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();
}
}
示例11
@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();
}
}
示例12
@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());
}
}
示例13
@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();
}
}
示例14
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();
}
}
示例15
@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();
}
}
示例16
@Autowired
public ExamineService(@Qualifier("Runelite SQL2O") Sql2o sql2o)
{
this.sql2o = sql2o;
try (Connection con = sql2o.open())
{
con.createQuery(CREATE_EXAMINE)
.executeUpdate();
}
}
示例17
@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();
}
}
示例18
@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
}
}
}
示例19
@Autowired
public OSBGrandExchangeService(@Qualifier("Runelite SQL2O") Sql2o sql2o)
{
this.sql2o = sql2o;
try (Connection con = sql2o.open())
{
con.createQuery(CREATE_GRAND_EXCHANGE_PRICES).executeUpdate();
}
}
示例20
@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();
}
}
示例21
@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));
}
示例22
@Autowired
public CacheUpdater(
@Qualifier("Runelite Cache SQL2O") Sql2o sql2o,
MinioClient minioClient
)
{
this.sql2o = sql2o;
this.minioClient = minioClient;
}
示例23
@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()));
}
示例24
@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();
}
}
示例25
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();
}
}
示例26
@Inject
public LocalFileDao(DbContext dbContext) {
this.dbContext = dbContext;
this.sql2o = new Sql2o(this.dbContext.getDataSource());
createTable();
}
示例27
@Inject
public RemoteFileDao(DbContext dbContext) {
this.dbContext = dbContext;
this.sql2o = new Sql2o(this.dbContext.getDataSource());
createTable();
}
示例28
@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());
}
}
示例29
@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();
}
}
示例30
@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();
}
}
