Java Code Examples for org.jdbi.v3.core.Jdbi#create()

@Test
public void whenIdentityColumn_thenInsertReturnsNewId() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
    jdbi.useHandle(handle -> {
        handle.execute("create table PROJECT_2 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))");
        Update update = handle.createUpdate(
                "INSERT INTO PROJECT_2 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')");
        ResultBearing generatedKeys = update.executeAndReturnGeneratedKeys();

        assertEquals(0, generatedKeys.mapToMap().findOnly().get("id"));

        update = handle.createUpdate(
                "INSERT INTO PROJECT_2 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')");

        assertEquals(1, generatedKeys.mapToMap().findOnly().get("id"));
    });
}
 

@Test
public void whenMultipleResults_thenFindOnlyThrows() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
    jdbi.useHandle(handle -> {
        handle.execute("create table PROJECT_9 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))");
        handle.execute("INSERT INTO PROJECT_9 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')");
        handle.execute("INSERT INTO PROJECT_9 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')");

        try {
            Query query = handle.createQuery("select * from project_9");
            Map<String, Object> onlyResult = query.mapToMap().findOnly();

            fail("Exception expected");
        } catch (Exception e) {
            e.printStackTrace();
        }
    });
}
 

private void upgradeSystemDB(String ironTestHome, String fullyQualifiedSystemDBURL, String user, String password,
                             List<ResourceFile> applicableSystemDBUpgrades, Path oldDir, Path newDir,
                             DefaultArtifactVersion jarFileVersion)
        throws IOException {
    Path oldDatabaseFolder = Files.createDirectory(Paths.get(oldDir.toString(), "database"));
    Path newDatabaseFolder = Files.createDirectory(Paths.get(newDir.toString(), "database"));
    String systemDBFileName = getSystemDBFileName(fullyQualifiedSystemDBURL);

    Path sourceFile = Paths.get(ironTestHome, "database", systemDBFileName);
    Path targetOldFile = Paths.get(oldDatabaseFolder.toString(), systemDBFileName);
    Path targetNewFile = Paths.get(newDatabaseFolder.toString(), systemDBFileName);
    Files.copy(sourceFile, targetOldFile);
    LOGGER.info("Copied current system database to " + oldDatabaseFolder.toString());
    Files.copy(sourceFile, targetNewFile);
    LOGGER.info("Copied current system database to " + newDatabaseFolder.toString());

    String newSystemDBURL = "jdbc:h2:" + targetNewFile.toString().replace(".mv.db", "") + ";IFEXISTS=TRUE";
    Jdbi jdbi = Jdbi.create(newSystemDBURL, user, password);

    //  run SQL scripts against the system database in the 'new' folder
    for (ResourceFile sqlFile: applicableSystemDBUpgrades) {
        try (InputStream is = getClass().getClassLoader().getResourceAsStream(sqlFile.getResourcePath())) {
            String sqlScript = IOUtils.toString(is, StandardCharsets.UTF_8.name());
            jdbi.withHandle(handle -> handle.createScript(sqlScript).execute());
        }
        LOGGER.info("Executed SQL script " + sqlFile.getResourcePath() + " in " + newSystemDBURL + ".");
    }

    //  update Version table
    jdbi.withHandle(handle -> handle
            .createUpdate("update version set version = ?, updated = CURRENT_TIMESTAMP")
            .bind(0, jarFileVersion.toString())
            .execute());
    LOGGER.info("Updated Version to " + jarFileVersion + " in " + newSystemDBURL + ".");
}
 

@Test
public void whenMultipleResults_thenFindFirstReturnsFirst() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
    jdbi.useHandle(handle -> {
        handle.execute("create table PROJECT_7 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))");
        handle.execute("INSERT INTO PROJECT_7 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')");
        handle.execute("INSERT INTO PROJECT_7 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')");

        Query query = handle.createQuery("select * from project_7 order by id");
        Optional<Map<String, Object>> first = query.mapToMap().findFirst();

        assertTrue(first.isPresent());
        assertEquals("tutorials", first.get().get("name"));
    });
}
 

@Before
public void setup() {
  jdbi = Jdbi.create("jdbc:h2:mem:rosetta;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE");
  jdbi.installPlugin(new SqlObjectPlugin());
  jdbi.registerRowMapper(new RosettaRowMapperFactory());
  jdbi.useHandle(handle -> {
    handle.execute("CREATE TABLE IF NOT EXISTS test_table (id INT, name VARCHAR(255) NOT NULL, PRIMARY KEY (id))");
    handle.execute("TRUNCATE TABLE test_table");
  });
}
 

@Test
public void testMapToDbObject() throws Exception {
    Jdbi dbi = Jdbi.create(DbHelper.getHsqlDataSource());
    dbi.installPlugins();
    Handle handle = dbi.open();
    try {
        DbObject dbObject = handle.createQuery(DbHelper.TEST_DB_OBJECT_QUERY).mapTo(DbObject.class).findFirst().get();
        DbHelper.assertDbObjectMapping(dbObject);
    } finally {
        handle.close();
    }
}
 

@Override
public Jdbi createDBI(Properties properties) {
    Jdbi dbi = Jdbi.create(properties.getProperty("db.url"), properties.getProperty("db.username"),
            properties.getProperty("db.password"));
    dbi.installPlugins();
    return dbi;
}
 

@Test
public void whenTableCreated_thenInsertIsPossible() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
    jdbi.useHandle(handle -> {
        int updateCount = handle.execute("create table PROJECT_1 (id integer identity, name varchar(50), url varchar(100))");

        assertEquals(0, updateCount);

        updateCount = handle.execute("INSERT INTO PROJECT_1 VALUES (1, 'tutorials', 'github.com/eugenp/tutorials')");

        assertEquals(1, updateCount);
    });
}
 

@PostConstruct
public void postConstruct() {
    logger.debug("JDBC Storage constructed successfully.");

    jdbi = Jdbi.create(dataSource);
    
    this.shareForEveryone = config.isShareForEveryone();
    
    if (config.isJdbcInit()) {
        synchronized (dbMutex) {
            if (!isDatabaseInitialized()) {
                logger.debug("Database not initialized.");
                initializeDatabase();
            } else {
                logger.debug("Database was already initialized, skipping.");
            }
            
            if (!isDatabaseCurrent()) {
                logger.debug("Old database version detected, upgrading.");
                upgradeDatabase();
            }
        }
    } else {
        if (!isDatabaseInitialized()) {
            logger.error("Database not initialized.  Please use the DDL scripts to initialize the database before starting the application.");
            throw new RuntimeException("Database not initialized.");
        }
        
        if (!isDatabaseCurrent()) {
            logger.error("Detected an old version of the database.  Please use the DDL upgrade scripts to bring your database up to date.");
            throw new RuntimeException("Database not upgraded.");
        }
    }
}
 

@Test
public void whenNoResults_thenFindOnlyThrows() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
    jdbi.useHandle(handle -> {
        handle.execute("create table PROJECT_8 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))");

        try {
            handle.createQuery("select * from project_8").mapToMap().findOnly();

            fail("Exception expected");
        } catch (Exception e) {
            e.printStackTrace();
        }
    });
}
 

@Test
public void whenSelectMapToMap_thenResultsAreMapEntries() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
    jdbi.useHandle(handle -> {
        handle.execute("create table PROJECT_3 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))");
        handle.execute("INSERT INTO PROJECT_3 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')");
        handle.execute("INSERT INTO PROJECT_3 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')");
        Query query = handle.createQuery("select * from PROJECT_3 order by id");
        List<Map<String, Object>> list = query.mapToMap().list();

        assertEquals("tutorials", list.get(0).get("name"));
        assertEquals("REST with Spring", list.get(1).get("name"));
    });
}
 

@Test
public void whenSelectMapToString_thenResultIsString() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
    jdbi.useHandle(handle -> {
        handle.execute("create table PROJECT_4 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))");
        handle.execute("INSERT INTO PROJECT_4 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')");
        handle.execute("INSERT INTO PROJECT_4 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')");
        Query query = handle.createQuery("select name, url from PROJECT_4 order by id");
        List<String> list = query.mapTo(String.class).list();

        assertEquals("tutorials", list.get(0));
        assertEquals("REST with Spring", list.get(1));
    });
}
 

public static MysqlClient create(
    String host,
    int port,
    String user,
    String password,
    boolean mTlsEnabled,
    TlsConfiguration tlsConfig) {
  return new MysqlClient(
      Jdbi.create(createMysqlDataSource(host, port, user, password, mTlsEnabled, tlsConfig)));
}
 

private DefaultArtifactVersion getSystemDBVersionStr(String fullyQualifiedSystemDBURL, String user, String password) {
    Jdbi jdbi = Jdbi.create(fullyQualifiedSystemDBURL, user, password);
    return IronTestUtils.getSystemDBVersion(jdbi);
}
 

public BasicTeststepRun run() throws Exception {
    Teststep teststep = getTeststep();
    BasicTeststepRun basicTeststepRun = new BasicTeststepRun();
    DBAPIResponse response = new DBAPIResponse();
    String request = (String) teststep.getRequest();

    List<String> statements = IronTestUtils.getStatements(request);
    sanityCheckTheStatements(statements);

    Endpoint endpoint = teststep.getEndpoint();
    Jdbi jdbi;
    if (endpoint.getUsername() == null) {
        jdbi = Jdbi.create(endpoint.getUrl());
    } else {
        jdbi = Jdbi.create(endpoint.getUrl(), endpoint.getUsername(), getDecryptedEndpointPassword());
    }
    Handle handle = jdbi.open();
    if (SQLStatementType.isSelectStatement(statements.get(0))) {    //  the request is a select statement
        RetainingColumnOrderResultSetMapper resultSetMapper = new RetainingColumnOrderResultSetMapper();
        //  use statements.get(0) instead of the raw request, as Oracle does not support trailing semicolon in select statement
        Query query = handle.createQuery(statements.get(0))
                .setMaxRows(5000);          //  limit the number of returned rows
        //  obtain columnNames in case the query returns no row
        final List<String> columnNames = new ArrayList<String>();
        query.addCustomizer(new StatementCustomizer() {
            public void afterExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException {
                ResultSetMetaData metaData = stmt.getMetaData();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    columnNames.add(metaData.getColumnLabel(i));
                }
            }
        });

        List<Map<String, Object>> rows = query.map(resultSetMapper).list();
        response.setColumnNames(columnNames);
        response.setRowsJSON(jacksonObjectMapper.writeValueAsString(rows));
    } else {                                          //  the request is one or more non-select statements
        Script script = handle.createScript(request);
        int[] returnValues = script.execute();
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < returnValues.length; i++) {
            String statementType = SQLStatementType.getByStatement(statements.get(i)).toString();
            sb.append(returnValues[i]).append(" row(s) ").append(statementType.toLowerCase())
                .append(statementType.endsWith("E") ? "d" : "ed").append("\n");
            response.setStatementExecutionResults(sb.toString());
        }
    }

    handle.close();

    basicTeststepRun.setResponse(response);
    return basicTeststepRun;
}
 

/**
 * Create a namespace service.
 * @param ds the datasource
 */
@Inject
public DBNamespaceService(final DataSource ds) {
    this(Jdbi.create(ds));
}
 

@Test
public void whenJdbiCreated_thenSuccess() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");

    Jdbi.create("WRONG");
}
 

/**
 * Create a new DB enhanced MementoService object.
 * @param ds the DataSource object
 * @param service the memento service implementation
 */
public DBWrappedMementoService(final DataSource ds, final MementoService service) {
    this(Jdbi.create(ds), service);
}
 

/**
 * Create a Database-backed resource service.
 *
 * <p>This constructor is generally used by CDI proxies and should
 * not be invoked directly.
 */
public DBResourceService() {
    this(Jdbi.create(getConfig().getOptionalValue(CONFIG_JDBC_URL, String.class).orElse("")),
            DEFAULT_BATCH_SIZE, false, new DefaultIdentifierService());
}
 

/**
 * Create a namespace service.
 *
 * <p>Note: this is generally used for CDI proxies and should not be invoked directly
 */
public DBNamespaceService() {
    this(Jdbi.create(getConfig().getOptionalValue(DBResourceService.CONFIG_JDBC_URL, String.class).orElse("")));
}