下面列出了javax.sql.DataSource#getConnection ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
private TestServer() {
DataSource ds = (DataSource) SingletonServiceFactory.getBean(DataSource.class);
try (Connection connection = ds.getConnection()) {
String schemaResourceName = "/create_h2.sql";
InputStream in = TestServer.class.getResourceAsStream(schemaResourceName);
if (in == null) {
throw new RuntimeException("Failed to load resource: " + schemaResourceName);
}
InputStreamReader reader = new InputStreamReader(in, UTF_8);
RunScript.execute(connection, reader);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testDelete() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
Statement stmt = conn.createStatement();
stmt.execute("delete from test where name='leo0'");
Statement stmt2 = conn.createStatement();
stmt2.execute("select name from test where name='leo0'");
ResultSet rs = stmt2.getResultSet();
while (rs.next()) {
Assert.fail();
}
Assert.assertTrue(true);
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
public static void checkAndCreateSchema(DataSource ds) throws SQLException, IOException {
Connection c = ds.getConnection();
try {
if (tablesExist(c)) {
logger.info("COPPER schema already exists");
return;
}
logger.info("Creating COPPER schema...");
String sql = getResourceAsString(H2Dialect.class, "/h2/create-schema.sql");
Statement stmt = c.createStatement();
try {
stmt.execute(sql);
} finally {
stmt.close();
}
} finally {
c.close();
}
logger.info("Created COPPER schema.");
}
public RegistrationDAO() throws SQLException {
try {
Context ctx = new InitialContext();
ds = (DataSource) ctx.lookup("java:comp/env/jdbc/blueprintsdb");
if (ds == null)
throw new SQLException("Can't get data source");
// get database connection
con = ds.getConnection();
if (con == null)
throw new SQLException("Can't get database connection");
} catch (NamingException e) {
e.printStackTrace();
}
}
@Before
public void init() throws SQLException {
String sql = "INSERT INTO Tb (id, uid, name) VALUES (?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?)";
DataSource ds = getDataSource();
Connection conn = ds.getConnection();
for (int i = 0; i < 8; ++i) {
PreparedStatement pst = conn.prepareStatement(sql);
for (int j = 1; j <= 10; ++j) {
pst.setInt(3*(j-1)+1, j);
pst.setInt(3*(j-1)+2, j*8+i);
pst.setString(3*(j-1)+3, "Tb-"+i+"-"+j);
}
pst.executeUpdate();
pst.close();
}
conn.close();
}
/**
* Creates a in-memory database on HSQLDB. The database can optionally use a connection pool.
* The test method can set {@code pooled} to {@code true} if it needs the data to survive when
* the connection is closed and re-opened.
*
* @param name the database name (without {@code "jdbc:hsqldb:mem:"} prefix).
* @param pooled whether the database should use a connection pool.
* @return connection to the test database.
* @throws SQLException if an error occurred while creating the database.
*
* @see <a href="http://hsqldb.org/doc/apidocs/org/hsqldb/jdbc/JDBCDataSource.html">JDBC data source for HSQL</a>
*
* @since 1.0
*/
public static TestDatabase createOnHSQLDB(final String name, final boolean pooled) throws SQLException {
final DataSource ds;
final JDBCPool pool;
final String url = "jdbc:hsqldb:mem:".concat(name);
if (pooled) {
pool = new JDBCPool();
pool.setURL(url);
ds = pool;
} else {
final JDBCDataSource simple = new JDBCDataSource();
simple.setURL(url);
ds = simple;
pool = null;
}
return new TestDatabase(ds) {
@Override public void close() throws SQLException {
try (Connection c = ds.getConnection(); Statement s = c.createStatement()) {
s.execute("SHUTDOWN");
}
if (pool != null) {
pool.close(2);
}
}
};
}
private static void printSimpleSelect(final DataSource dataSource) throws SQLException {
String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=?";
try (
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
preparedStatement.setInt(1, 10);
preparedStatement.setInt(2, 1001);
try (ResultSet rs = preparedStatement.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt(1));
System.out.println(rs.getInt(2));
System.out.println(rs.getInt(3));
}
}
}
}
/**
* Verify that a user data dir (in this case empty) throws the old message
*
*/
public void test_5526() throws SQLException {
String mydatadirStr = BaseTestCase.getSystemProperty("derby.system.home") +
File.separator + "mydatadir";
File mydatadir = new File(mydatadirStr);
assertTrue(mydatadir.mkdir());
DataSource ds = JDBCDataSource.getDataSource(mydatadirStr);
JDBCDataSource.setBeanProperty(ds, "createDatabase", "create");
try {
ds.getConnection();
fail("Should not be able to create database on existing directory " + mydatadirStr);
} catch (SQLException se) {
// should be nested exception XJ041 -> XBM0J (Directory exists)
assertSQLState("XJ041",se);
se = se.getNextException();
assertSQLState("XBM0J",se);
} finally {
BaseTestCase.removeDirectory(mydatadir);
}
}
/**
* Check session_attempts_on_site_id_and_state_flags_partial_2 index exists
* @throws Exception
*/
@Test
public void checkMigration_20190318175338_AddIndexToSessionAttempts()
{
assumeTrue(server.isRemoteDatabase());
try {
server.start();
DataSource ds = server.getTestDBDataSource();
Connection con = ds.getConnection();
con.createStatement().execute("drop index session_attempts_on_site_id_and_state_flags_partial_2");
}
catch (Exception e) {
fail(e.toString());
}
}
@Test
public void testMultiRouterResult11() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
Statement stmt = conn.createStatement();
stmt.execute("select name from test");
ResultSet rs = stmt.getResultSet();
List<String> rows = new ArrayList<String>();
while (rs.next()) {
rows.add(rs.getString("name"));
}
Assert.assertEquals(17, rows.size());
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void testMultiRouterResult4() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn.prepareStatement("select score from test where id!=1 order by score desc");
stmt.execute();
ResultSet rs = stmt.getResultSet();
List<Integer> rows = new ArrayList<Integer>();
while (rs.next()) {
rows.add(rs.getInt("score"));
}
Assert.assertEquals(14, rows.size());
List<Integer> expectedResult = Arrays.asList(new Integer[]{20,8,8,7,7,6,6,5,5,4,3,3,1,1});
for (int i = 0; i < expectedResult.size(); i++) {
Assert.assertEquals(expectedResult.get(i).intValue(), rows.get(i).intValue());
}
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void testMultiRouterResult13_1() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn.prepareStatement("select sum(score) score from test where id = ?");
stmt.setInt(1, 1);
stmt.execute();
ResultSet rs = stmt.getResultSet();
List<Long> rows = new ArrayList<Long>();
while (rs.next()) {
rows.add(rs.getLong("score"));
}
Assert.assertEquals(1, rows.size());
Assert.assertEquals(5, rows.get(0).intValue());
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
@Test
public void testLimit2_2() throws Exception {
DataSource ds = (DataSource) context.getBean("zebraDS");
Connection conn = null;
try {
conn = ds.getConnection();
PreparedStatement stmt = conn.prepareStatement("select name from test limit 10,?");
stmt.setInt(1, 5);
stmt.execute();
ResultSet rs = stmt.getResultSet();
List<String> rows = new ArrayList<String>();
while (rs.next()) {
rows.add(rs.getString("name"));
}
Assert.assertEquals(5, rows.size());
} catch (Exception e) {
Assert.fail();
} finally {
if (conn != null) {
conn.close();
}
}
}
public static java.sql.Connection getSqlConnection() {
if (conn == null) {
try {
InitialContext ctx = new InitialContext();
DataSource datasource = (DataSource)ctx.lookup("java:comp/env/jdbc/BenchmarkDB");
conn = datasource.getConnection();
conn.setAutoCommit(false);
} catch (SQLException | NamingException e) {
System.out.println("Problem with getSqlConnection.");
e.printStackTrace();
}
}
return conn;
}
/**
* Ensures that Derby can handle the case where the backup file already
* exists when editing the service properties.
*/
public void testBackupWithBackupExisting()
throws IOException, SQLException {
// Prepare
String db = "spfTestBWBE";
copyDbAs(db);
assertPresence(true, false);
// Make sure 'db.storage.logArchiveMode' isn't present already.
assertEquals(0, grepForToken(LOG_A_MODE, spf));
// Connect, then enable log archive mode to trigger edit.
DataSource ds = JDBCDataSource.getDataSource();
JDBCDataSource.setBeanProperty(ds, "databaseName", "singleUse/" + db);
Connection con = ds.getConnection();
// Create the service properties file backup.
createSPFBackup(true);
// Trigger service properties file edit.
Statement stmt = con.createStatement();
stmt.execute("CALL SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(0)");
con.close();
// Shut down the database.
JDBCDataSource.shutdownDatabase(ds);
assertNormalPresence();
assertEquals(1, grepForToken(LOG_A_MODE + "=false", spf));
}
public void testQueries(DataSource dataSource, String input) throws SQLException {
String sql = "select * from Users where name = " + input;
Connection connection = dataSource.getConnection();
try {
MySqlWrapper wrapper = new MySqlWrapper(connection);
ResultSet resultSet = wrapper.executeQuery(sql);
System.out.println(resultSet.next());
} finally {
connection.close();
}
}
public static Connection getConnection(String key){
DataSource dataSource = getDataSource(key);
if(dataSource == null){
throw ExceptionUtil.getException("没有可用的数据源配置！");
}
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw ExceptionUtil.getException("获取数据库连接："+key+" 出错",e);
}
}
protected void assertSystemShutdownOK(
String dbName, String user, String password)
throws SQLException {
DataSource ds;
if (usingEmbedded())
{
// we cannot use JDBCDataSource.getDataSource() (which uses the
// default database name), unless we specifically clear the
// databaseName. Otherwise, only the database will be shutdown.
// The alternative is to use jDBCDataSource.getDatasource(dbName),
// where dbName is an empty string - this will in the current code
// be interpreted as a system shutdown.
ds = JDBCDataSource.getDataSource();
JDBCDataSource.clearStringBeanProperty(ds, "databaseName");
}
else
{
// With client, we cannot user clearStringBeanProperty on the
// databaseName, that will result in error 08001 -
// Required DataSource property databaseName not set.
// So, we pass an empty string as databaseName, which the current
// code interprets as a system shutdown.
ds = JDBCDataSource.getDataSource(dbName);
}
JDBCDataSource.setBeanProperty(ds, "shutdownDatabase", "shutdown");
try {
ds.getConnection(user, password);
fail("expected system shutdown resulting in XJ015 error");
} catch (SQLException e) {
// expect XJ015, system shutdown, on successful shutdown
assertSQLState("XJ015", e);
}
}
public static void runTest8() {
try {
Context ctx = cache.getJNDIContext();
DataSource ds2 = (DataSource) ctx.lookup("java:/SimpleDataSource");
ds2.getConnection();
GemFireTransactionDataSource ds = (GemFireTransactionDataSource) ctx
.lookup("java:/XAPooledDataSource");
UserTransaction utx = (UserTransaction) ctx
.lookup("java:/UserTransaction");
utx.begin();
Connection conn = ds.getConnection();
String sql = "create table newTable1 (id integer)";
Statement sm = conn.createStatement();
sm.execute(sql);
utx.setTransactionTimeout(30);
Thread.sleep(5000);
utx.setTransactionTimeout(20);
utx.setTransactionTimeout(10);
sql = "insert into newTable1 values (1)";
sm.execute(sql);
utx.commit();
sql = "select * from newTable1 where id = 1";
ResultSet rs = sm.executeQuery(sql);
if (!rs.next()) fail("Transaction not committed");
sql = "drop table newTable1";
sm.execute(sql);
sm.close();
conn.close();
}
catch (Exception e) {
fail("Exception occured in test Commit due to " + e);
e.printStackTrace();
}
}
@Test
public void testMultiSkApi() throws SQLException {
DataSource ds = getDataSource();
Connection conn = ds.getConnection();
Statement st = conn.createStatement();
ResultSet rs = null;
Set<TbEntity> expected = null;
HashSet<String> hintShardColumns = new HashSet<String>();
HashSet<String> hintShardColumns2 = new HashSet<String>();
hintShardColumns.add("uid");
hintShardColumns.add("tid");
hintShardColumns2.add("tid");
// table = Tb, sk = uid
rs = st.executeQuery("SELECT * FROM `Tb` WHERE `uid` IN (0,1,2,3)");
Set<TbEntity> actual = parseAndCloseResultSet(rs);
expected = generateEntities(new TbEntity(1, 0, 4, "uid-0"), new TbEntity(1, 1, 4, "uid-1"), new TbEntity(1, 2, 4, "uid-2"), new TbEntity(1, 3, 4, "uid-3"));
Assert.assertEquals(expected, actual);
// table = Tb, update tid(auxiliary)
ShardDataSourceHelper.setHintShardColumn(hintShardColumns2);
st.execute("UPDATE `Tb` SET `Name` = 'tid-update-2' WHERE `tid` = 2");
rs = st.executeQuery("SELECT * FROM `Tb` WHERE `tid` = 2");
actual = parseAndCloseResultSet(rs);
expected = generateEntities(new TbEntity(2, 4, 2, "tid-update-2"));
Assert.assertEquals(expected, actual);
// table = Tb, sk = uid + tid
actual = new HashSet<TbEntity>();
for (int i = 0; i <= 4; ++i) {
ShardDataSourceHelper.setHintShardColumn(hintShardColumns);
rs = st.executeQuery("SELECT * FROM `Tb` WHERE `uid` = " + i + " AND `tid` = 1");
actual.addAll(parseAndCloseResultSet(rs));
}
expected = generateEntities(new TbEntity(1, 1, 1, "ut-11"), new TbEntity(1, 2, 1, "ut-21"), new TbEntity(1, 3, 1, "ut-31"), new TbEntity(1, 4, 1, "ut-41"));
Assert.assertEquals(expected, actual);
// table = Tbut, sk = uid + tid
int[][] ut = new int[][] { { 4, 4, 5, 5 }, { 0, 1, 0, 1 } };
actual = new HashSet<TbEntity>();
for (int i = 0; i < 4; ++i) {
ShardDataSourceHelper.setHintShardColumn(hintShardColumns);
rs = st.executeQuery("/*+zebra:sk=uid*/SELECT * FROM `Tbut` WHERE `uid` = " + ut[0][i] + " AND `tid` = " + ut[1][i]);
actual.addAll(parseAndCloseResultSet(rs));
}
expected = generateEntities(new TbEntity(2, 4, 0, "u_t-00"), new TbEntity(2, 4, 1, "u_t-01"), new TbEntity(2, 5, 0, "u_t-10"), new TbEntity(2, 5, 1, "u_t-11"));
Assert.assertEquals(expected, actual);
st.close();
conn.close();
}