Java JdbcConnectionPool.getConnection方法代码示例

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
private void testPerformance() throws SQLException {
    String url = getURL("connectionPool", true), user = getUser();
    String password = getPassword();
    JdbcConnectionPool man = JdbcConnectionPool.create(url, user, password);
    Connection conn = man.getConnection();
    int len = 1000;
    long time = System.currentTimeMillis();
    for (int i = 0; i < len; i++) {
        man.getConnection().close();
    }
    man.dispose();
    trace((int) (System.currentTimeMillis() - time));
    time = System.currentTimeMillis();
    for (int i = 0; i < len; i++) {
        DriverManager.getConnection(url, user, password).close();
    }
    trace((int) (System.currentTimeMillis() - time));
    conn.close();
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
private void testShutdown() throws SQLException {
    String url = getURL("connectionPool2", true), user = getUser();
    String password = getPassword();
    JdbcConnectionPool cp = JdbcConnectionPool.create(url, user, password);
    StringWriter w = new StringWriter();
    cp.setLogWriter(new PrintWriter(w));
    Connection conn1 = cp.getConnection();
    Connection conn2 = cp.getConnection();
    conn1.close();
    conn2.createStatement().execute("shutdown immediately");
    cp.dispose();
    assertTrue(w.toString().length() > 0);
    cp.dispose();
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
private void testWrongUrl() {
    JdbcConnectionPool cp = JdbcConnectionPool.create(
            "jdbc:wrong:url", "", "");
    try {
        cp.getConnection();
    } catch (SQLException e) {
        assertEquals(8001, e.getErrorCode());
    }
    cp.dispose();
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
private void testTimeout() throws Exception {
    String url = getURL("connectionPool", true), user = getUser();
    String password = getPassword();
    final JdbcConnectionPool man = JdbcConnectionPool.create(url, user, password);
    man.setLoginTimeout(1);
    createClassProxy(man.getClass());
    assertThrows(IllegalArgumentException.class, man).
            setMaxConnections(-1);
    man.setMaxConnections(2);
    // connection 1 (of 2)
    Connection conn = man.getConnection();
    Task t = new Task() {
        @Override
        public void call() {
            while (!stop) {
                // this calls notifyAll
                man.setMaxConnections(1);
                man.setMaxConnections(2);
            }
        }
    };
    t.execute();
    long time = System.currentTimeMillis();
    try {
        // connection 2 (of 1 or 2) may fail
        man.getConnection();
        // connection 3 (of 1 or 2) must fail
        man.getConnection();
        fail();
    } catch (SQLException e) {
        assertTrue(e.toString().toLowerCase().contains("timeout"));
        time = System.currentTimeMillis() - time;
        assertTrue("timeout after " + time + " ms", time > 1000);
    } finally {
        conn.close();
        t.get();
    }

    man.dispose();
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
private void testUncommittedTransaction() throws SQLException {
    String url = getURL("connectionPool", true), user = getUser();
    String password = getPassword();
    JdbcConnectionPool man = JdbcConnectionPool.create(url, user, password);

    assertEquals(30, man.getLoginTimeout());
    man.setLoginTimeout(1);
    assertEquals(1, man.getLoginTimeout());
    man.setLoginTimeout(0);
    assertEquals(30, man.getLoginTimeout());
    assertEquals(10, man.getMaxConnections());

    PrintWriter old = man.getLogWriter();
    PrintWriter pw = new PrintWriter(new StringWriter());
    man.setLogWriter(pw);
    assertTrue(pw == man.getLogWriter());
    man.setLogWriter(old);

    Connection conn1 = man.getConnection();
    assertTrue(conn1.getAutoCommit());
    conn1.setAutoCommit(false);
    conn1.close();
    assertTrue(conn1.isClosed());

    Connection conn2 = man.getConnection();
    assertTrue(conn2.getAutoCommit());
    conn2.close();

    man.dispose();
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
private void testKeepOpen() throws Exception {
    JdbcConnectionPool man = getConnectionPool(1);
    Connection conn = man.getConnection();
    Statement stat = conn.createStatement();
    stat.execute("create local temporary table test(id int)");
    conn.close();
    conn = man.getConnection();
    stat = conn.createStatement();
    stat.execute("select * from test");
    conn.close();
    man.dispose();
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
private void testConnect() throws SQLException {
    JdbcConnectionPool pool = getConnectionPool(3);
    for (int i = 0; i < 100; i++) {
        Connection conn = pool.getConnection();
        conn.close();
    }
    pool.dispose();
    DataSource ds = pool;
    assertThrows(IllegalStateException.class, ds).
            getConnection();
    assertThrows(UnsupportedOperationException.class, ds).
            getConnection(null, null);
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
public static void dump(String sql, JdbcConnectionPool jdbcConnectionPool, PrintStream out) throws RuntimeSqlException {
    try (Connection connection = jdbcConnectionPool.getConnection()) {
        out.println("# DUMP " + sql);
        out.println();
        try (Statement stmt = connection.createStatement()) {
            try (ResultSet rst = stmt.executeQuery(sql)) {
                dump(rst, out);
            }
        }
        out.println("----");
    } catch (SQLException e) {
        throw new RuntimeSqlException(e);
    }
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
public static void silentlyDeleteTableRows(JdbcConnectionPool jdbcConnectionPool, String... tables) {
    for (String table : tables) {
        try (Connection cnn = jdbcConnectionPool.getConnection()) {
            try (Statement stmt = cnn.createStatement()) {
                int deleted = stmt.executeUpdate("delete from " + table);
            }
        } catch (SQLException e) {
            if (e.getErrorCode() == 42102) {
                // ignore "table not found"
            } else {
                throw new RuntimeSqlException(e);
            }
        }
    }
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
public void setConnectionPool(JdbcConnectionPool cp) throws SQLException {
    this.cp = cp;
    Connection conn = cp.getConnection();
    Statement stat = conn.createStatement();
    stat.execute("create table if not exists datastore_meta" +
            "(id varchar primary key, level int, lastMod bigint)");
    stat.execute("create table if not exists datastore_data" +
            "(id varchar primary key, data binary)");
    stat.close();
    conn.close();
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
private void testThreads() throws Exception {
    final int len = getSize(4, 20);
    final JdbcConnectionPool man = getConnectionPool(len - 2);
    final boolean[] stop = { false };

    /**
     * This class gets and returns connections from the pool.
     */
    class TestRunner implements Runnable {
        @Override
        public void run() {
            try {
                while (!stop[0]) {
                    Connection conn = man.getConnection();
                    if (man.getActiveConnections() >= len + 1) {
                        throw new Exception("a: " +
                                man.getActiveConnections()  +
                                " is not smaller than b: " + (len + 1));
                    }
                    Statement stat = conn.createStatement();
                    stat.execute("SELECT 1 FROM DUAL");
                    conn.close();
                    Thread.sleep(100);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    Thread[] threads = new Thread[len];
    for (int i = 0; i < len; i++) {
        threads[i] = new Thread(new TestRunner());
        threads[i].start();
    }
    Thread.sleep(1000);
    stop[0] = true;
    for (int i = 0; i < len; i++) {
        threads[i].join();
    }
    assertEquals(0, man.getActiveConnections());
    man.dispose();
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
@Test
public void jdbcPoolProcessorDemoTest()
        throws FactoryException, AuditException, UnsupportedEncodingException, SQLException {

    Map<String, String> props = new HashMap<>();

    // the DB configuration
    props.put(MapBasedConnPropsBuilder.KEY_DRIVER, H2Server.DRIVER);
    props.put(MapBasedConnPropsBuilder.KEY_URL, H2Server.URL);
    props.put(MapBasedConnPropsBuilder.KEY_USERNAME, H2Server.USER);
    props.put(MapBasedConnPropsBuilder.KEY_PASSWORD, H2Server.PASSWORD);

    // the JDBC Processor configuration
    props.put(MapBasedJdbcPropsBuilder.KEY_EVENT_ID_FIELD_NAME, EVENT_ID_FIELD_NAME);
    props.put(MapBasedJdbcPropsBuilder.KEY_INSERT_EVENT_SQL_STMT,
            "INSERT INTO events (eventId, auditStreamName, eventJson) VALUES (?, ?, ?)");
    props.put(MapBasedJdbcPropsBuilder.KEY_INSERT_INDEXED_FIELD_SQL_STMT,
            "INSERT INTO fields (eventId, auditStreamName, fieldName, fieldValue) VALUES (?, ?, ?, ?)");
    props.put(MapBasedJdbcPropsBuilder.KEY_INDEXED_FIELDS, "subject,actor:myActor,byteField,invalidField:neverExists");

    // the audit library common configuration
    CommonProperties properties = MapBasedCommonPropsBuilder.build(props);
    properties.setDefaultAuditStream("1234567890");
    properties.setEncoding("UTF-8");
    properties.setFailOnMissingProcessors(true);
    properties.setProcessors(JdbcPoolProcessor.class.getCanonicalName());

    Audit audit = AuditFactory.getInstance(properties.getAuditClassName(), properties);

    // the event ID is required for this processor to work
    Field eventIdField = new EventField(EVENT_ID_FIELD_NAME, EVENT_ID.getBytes("UTF-8"));

    Field field = new EventField("byteField", new Hex().encode("1234".getBytes("UTF-8")), Encodings.HEX);

    // create the event, using (amongst others) the custom field above
    Event event = new EventBuilder(properties)
            .setField(eventIdField)
            .setSubject("SubjectId-1234".toCharArray())
            .setObject("ObjectId-3456".toCharArray())
            .setActor("ActorId-5678".toCharArray())
            .setResult("Some result".toCharArray())
            .setField(field)
            .build();

    audit.audit(event);

    // demo that the audit operation was successful
    JdbcConnectionPool cp = JdbcConnectionPool.create(H2Server.URL, H2Server.USER, H2Server.PASSWORD);
    Connection con = cp.getConnection();
    String eventStmt = "SELECT * FROM events";
    ResultSet eventRs = con.prepareStatement(eventStmt).executeQuery();

    while (eventRs.next()) {
        String result = "event: " +
                "\n\t id: " + eventRs.getString("id") +
                "\n\t eventId: " + eventRs.getString("eventId") +
                "\n\t auditStreamName: " + eventRs.getString("auditStreamName") +
                "\n\t eventJson: " + eventRs.getString("eventJson");
        String eventId = eventRs.getString("eventId");

        String fieldStmt = "SELECT * FROM fields WHERE eventId = '" + eventId + "'";
        ResultSet fieldRs = con.prepareStatement(fieldStmt).executeQuery();
        int count = 1;
        while (fieldRs.next()) {
            result += "\nfield " + count++ + " in event " + eventId + ":" +
                    "\n\t id: " + fieldRs.getString("id") +
                    "\n\t eventId: " + fieldRs.getString("eventId") +
                    "\n\t auditStreamName: " + fieldRs.getString("auditStreamName") +
                    "\n\t fieldName: " + fieldRs.getString("fieldName") +
                    "\n\t fieldValue: " + fieldRs.getString("fieldValue");
        }

        LOG.info(result);
    }

    con.close();
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
/**
 * Test that, when trying to insert an oversized field in the index table, that field is truncated
 * to the correct maximum length.
 */
@Test
public void indexedFieldsOversizedFieldInsertTest()
        throws FactoryException, AuditException, UnsupportedEncodingException, SQLException {

    Map<String, String> props = new HashMap<>();

    // the DB configuration
    props.put(MapBasedConnPropsBuilder.KEY_DRIVER, H2Server.DRIVER);
    props.put(MapBasedConnPropsBuilder.KEY_URL, H2Server.URL);
    props.put(MapBasedConnPropsBuilder.KEY_USERNAME, H2Server.USER);
    props.put(MapBasedConnPropsBuilder.KEY_PASSWORD, H2Server.PASSWORD);

    // the JDBC Processor configuration
    props.put(MapBasedJdbcPropsBuilder.KEY_EVENT_ID_FIELD_NAME, EVENT_ID_FIELD_NAME);
    props.put(MapBasedJdbcPropsBuilder.KEY_INSERT_EVENT_SQL_STMT,
            "INSERT INTO events (eventId, auditStreamName, eventJson) VALUES (?, ?, ?)");
    props.put(MapBasedJdbcPropsBuilder.KEY_INSERT_INDEXED_FIELD_SQL_STMT,
            "INSERT INTO fields (eventId, auditStreamName, fieldName, fieldValue) VALUES (?, ?, ?, ?)");
    props.put(MapBasedJdbcPropsBuilder.KEY_INDEXED_FIELDS, "subject");
    props.put(MapBasedJdbcPropsBuilder.KEY_INDEXED_FIELDS_MAX_LENGTH, "3");

    // the audit library common configuration
    CommonProperties properties = MapBasedCommonPropsBuilder.build(props);
    properties.setDefaultAuditStream(T_AUDIT_STREAM_NAME);
    properties.setEncoding("UTF-8");
    properties.setFailOnMissingProcessors(true);
    properties.setProcessors(JdbcPoolProcessor.class.getCanonicalName());

    Audit audit = AuditFactory.getInstance(properties.getAuditClassName(), properties);

    Event event = getTestEvent(properties);

    audit.audit(event);

    // assert that the audit operation was successful - get the strings back from the DB!
    JdbcConnectionPool cp = JdbcConnectionPool.create(H2Server.URL, H2Server.USER, H2Server.PASSWORD);
    Connection con = cp.getConnection();
    String eventStmt = "SELECT * FROM events";
    ResultSet eventRs = con.prepareStatement(eventStmt).executeQuery();

    while (eventRs.next()) {

        String eventId = eventRs.getString("eventId");

        String fieldStmt = "SELECT * FROM fields WHERE eventId = '" + eventId + "'";
        ResultSet fieldRs = con.prepareStatement(fieldStmt).executeQuery();
        while (fieldRs.next()) {

            String fieldName = fieldRs.getString("fieldName");
            String fieldValue = fieldRs.getString("fieldValue");

            // there should only be one field (and one iteration) of this loop - the `subject` field:
            String error = "The fieldName does not match the expected value";
            assertThat(error, fieldName, is(equalTo("subject")));
            // the subject should have been truncated to the first three chars:
            error = "The fieldValue does not match the expected value";
            assertThat(error, fieldValue, is(equalTo("Sub")));
        }

    }

    con.close();
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
/**
 * Test that a field is converted to lowercase if so configured.
 */
@Test
public void indexedFieldsToLowerFieldInsertTest()
        throws FactoryException, AuditException, UnsupportedEncodingException, SQLException {

    Map<String, String> props = new HashMap<>();

    // the DB configuration
    props.put(MapBasedConnPropsBuilder.KEY_DRIVER, H2Server.DRIVER);
    props.put(MapBasedConnPropsBuilder.KEY_URL, H2Server.URL);
    props.put(MapBasedConnPropsBuilder.KEY_USERNAME, H2Server.USER);
    props.put(MapBasedConnPropsBuilder.KEY_PASSWORD, H2Server.PASSWORD);

    // the JDBC Processor configuration
    props.put(MapBasedJdbcPropsBuilder.KEY_EVENT_ID_FIELD_NAME, EVENT_ID_FIELD_NAME);
    props.put(MapBasedJdbcPropsBuilder.KEY_INSERT_EVENT_SQL_STMT,
            "INSERT INTO events (eventId, auditStreamName, eventJson) VALUES (?, ?, ?)");
    props.put(MapBasedJdbcPropsBuilder.KEY_INSERT_INDEXED_FIELD_SQL_STMT,
            "INSERT INTO fields (eventId, auditStreamName, fieldName, fieldValue) VALUES (?, ?, ?, ?)");
    props.put(MapBasedJdbcPropsBuilder.KEY_INDEXED_FIELDS, "someCaseSensitiveField");
    props.put(MapBasedJdbcPropsBuilder.KEY_INDEXED_FIELDS_TO_LOWER, "true");

    // the audit library common configuration
    CommonProperties properties = MapBasedCommonPropsBuilder.build(props);
    properties.setDefaultAuditStream(T_AUDIT_STREAM_NAME);
    properties.setEncoding("UTF-8");
    properties.setFailOnMissingProcessors(true);
    properties.setProcessors(JdbcPoolProcessor.class.getCanonicalName());

    Audit audit = AuditFactory.getInstance(properties.getAuditClassName(), properties);

    Event event = getTestEvent(properties);
    Field field = new EventField("someCaseSensitiveField", "CaseSensitiveText".getBytes(StandardCharsets.UTF_8));
    event.setField(field);

    audit.audit(event);

    // assert that the audit operation was successful - get the strings back from the DB!
    JdbcConnectionPool cp = JdbcConnectionPool.create(H2Server.URL, H2Server.USER, H2Server.PASSWORD);
    Connection con = cp.getConnection();
    String eventStmt = "SELECT * FROM events";
    ResultSet eventRs = con.prepareStatement(eventStmt).executeQuery();

    while (eventRs.next()) {

        String eventId = eventRs.getString("eventId");

        String fieldStmt = "SELECT * FROM fields WHERE eventId = '" + eventId + "'";
        ResultSet fieldRs = con.prepareStatement(fieldStmt).executeQuery();
        while (fieldRs.next()) {

            String fieldName = fieldRs.getString("fieldName");
            String fieldValue = fieldRs.getString("fieldValue");

            // there should only be one field (and one iteration) of this loop - the `someCaseSensitiveField` field:
            String error = "The fieldName does not match the expected value";
            assertThat(error, fieldName, is(equalTo("someCaseSensitiveField")));
            // the field value should be all lowercase
            error = "The fieldValue does not match the expected value";
            assertThat(error, fieldValue, is(equalTo("CaseSensitiveText".toLowerCase())));
        }

    }

    con.close();
}
 

import org.h2.jdbcx.JdbcConnectionPool; //导入方法依赖的package包/类
/**
 * Test that a field value is properly normalized to NFC.
 */
@Test
public void indexedFieldsNormalizedFieldInsertTest()
        throws FactoryException, AuditException, UnsupportedEncodingException, SQLException {

    Map<String, String> props = new HashMap<>();

    // the DB configuration
    props.put(MapBasedConnPropsBuilder.KEY_DRIVER, H2Server.DRIVER);
    props.put(MapBasedConnPropsBuilder.KEY_URL, H2Server.URL);
    props.put(MapBasedConnPropsBuilder.KEY_USERNAME, H2Server.USER);
    props.put(MapBasedConnPropsBuilder.KEY_PASSWORD, H2Server.PASSWORD);

    // the JDBC Processor configuration
    props.put(MapBasedJdbcPropsBuilder.KEY_EVENT_ID_FIELD_NAME, EVENT_ID_FIELD_NAME);
    props.put(MapBasedJdbcPropsBuilder.KEY_INSERT_EVENT_SQL_STMT,
            "INSERT INTO events (eventId, auditStreamName, eventJson) VALUES (?, ?, ?)");
    props.put(MapBasedJdbcPropsBuilder.KEY_INSERT_INDEXED_FIELD_SQL_STMT,
            "INSERT INTO fields (eventId, auditStreamName, fieldName, fieldValue) VALUES (?, ?, ?, ?)");
    props.put(MapBasedJdbcPropsBuilder.KEY_INDEXED_FIELDS, "umlautField,accentField");

    // the audit library common configuration
    CommonProperties properties = MapBasedCommonPropsBuilder.build(props);
    properties.setDefaultAuditStream(T_AUDIT_STREAM_NAME);
    properties.setEncoding("UTF-8");
    properties.setFailOnMissingProcessors(true);
    properties.setProcessors(JdbcPoolProcessor.class.getCanonicalName());

    Audit audit = AuditFactory.getInstance(properties.getAuditClassName(), properties);

    Event event = getTestEvent(properties);
    // LATIN CAPITAL LETTER A + COMBINING DIAERESES
    Field field = new EventField("umlautField", "\u0041\u0308".getBytes(StandardCharsets.UTF_8));
    event.setField(field);
    // LATIN SMALL LETTER E + COMBINING ACUTE ACCENT
    field = new EventField("accentField", "\u0065\u0301".getBytes(StandardCharsets.UTF_8));
    event.setField(field);

    audit.audit(event);

    // assert that the audit operation was successful - get the strings back from the DB!
    JdbcConnectionPool cp = JdbcConnectionPool.create(H2Server.URL, H2Server.USER, H2Server.PASSWORD);
    Connection con = cp.getConnection();
    String eventStmt = "SELECT * FROM events";
    ResultSet eventRs = con.prepareStatement(eventStmt).executeQuery();

    while (eventRs.next()) {

        String eventId = eventRs.getString("eventId");

        String fieldStmt = "SELECT * FROM fields WHERE eventId = '" + eventId + "'";
        ResultSet fieldRs = con.prepareStatement(fieldStmt).executeQuery();
        while (fieldRs.next()) {

            String fieldName = fieldRs.getString("fieldName");
            String fieldValue = fieldRs.getString("fieldValue");

            switch (fieldName) {
                case "umlautField":
                    String error = "The fieldValue does not match the expected value";
                    // LATIN CAPITAL LETTER A WITH DIAERESES
                    assertThat(error, fieldValue, is(equalTo("\u00C4")));
                    break;
                case "accentField":
                    error = "The fieldValue does not match the expected value";
                    // LATIN SMALL LETTER E WITH ACUTE
                    assertThat(error, fieldValue, is(equalTo("\u00E9")));
                    break;
                default:
                    throw new AssertionError("Unexpected field name: " + fieldName);
            }
        }

    }

    con.close();
}