Java Code Examples for java.sql.Connection

static void resetSchema(String[] statements) throws SQLException {
  Connection connection=Base.connection();
  for (  String statement : statements) {
    if (Util.blank(statement))     continue;
    Statement st=connection.createStatement();
    st.executeUpdate(statement);
    st.close();
  }
}
 

public boolean isRecordLocked(SessionDataInfo sessionData,TableInfo table,int rowId) throws SQLException, ObjectNotFoundException {
  TableDataInfo tableData=new TableData(table);
  Connection conn=null;
  try {
    conn=this.dataSource.getConnection();
    conn.setAutoCommit(false);
    return tableData.isRecordLocked(conn,sessionData,rowId);
  }
  finally {
    if (conn != null) {
      conn.close();
    }
  }
}
 

/** 
 * Test that things get cleaned up. This runs after all other tests that create or otherwise manipulate blobs.
 */
@Override public void testCleanup() throws Exception {
  super.testCleanup();
  Connection connection=DriverManager.getConnection("jdbc:derby:" + dbDir);
  ResultSet rs=connection.createStatement().executeQuery("SELECT * FROM " + TransactionalStore.NAME_TABLE);
  assertFalse(rs.next(),"unexpected entries in name-map table;");
  rs=connection.createStatement().executeQuery("SELECT * FROM " + TransactionalStore.DEL_TABLE);
  assertFalse(rs.next(),"unexpected entries in deleted-list table;");
}
 

/** 
 * ????????? URL ???????
 */
public Connection connect(String url,Properties info) throws SQLException {
  Properties p=new Properties();
  p.putAll(info);
  Connection conn=driver.connect(url,p);
  CharsetParameter param=new CharsetParameter();
  param.setClientEncoding(this.getClientEncoding());
  param.setServerEncoding(this.getServerEncoding());
  return factory.getConnection(param,conn);
}
 

@Override public ArastrejuGate create(DomainIdentifier identifier) throws GateInitializationException {
  String storageName=identifier.getStorage();
  ArastrejuProfile profile=getProfile();
  RdbConnectionProvider provider=new RdbConnectionProvider(profile.getProperty(DRIVER),profile.getProperty(USER),profile.getProperty(PASS),profile.getProperty(PROTOCOL) + profile.getProperty(DB),storageName,MAX_CONNECTIONS);
  Connection con=provider.getConnection();
  if (!DBOperations.tableExists(con,storageName))   DBOperations.createTable(con,storageName);
  provider.close(con);
  return new RdbGate(provider,identifier);
}
 

public static ConnectionManager newInstance(File dir) throws SQLException, ClassNotFoundException {
  Class.forName("org.hsqldb.jdbcDriver");
  String path=dir.getAbsolutePath() + "/index";
  String url="jdbc:hsqldb:file:" + path;
  String user="sa";
  String password="";
  Connection connection=DriverManager.getConnection(url,user,password);
  return new ConnectionManager(connection);
}
 

private void closeDatabaseConnection(){
  try {
    final Connection connection=databaseConnectionProducer.get().getConnection();
    if (!connection.isClosed()) {
      connection.close();
    }
  }
 catch (  Exception e) {
    throw new DBUnitConnectionException("Unable to close connection.",e);
  }
}
 

public Location searchIp(String ip){
  Connection conn=null;
  Statement stat=null;
  try {
    conn=poolMgr.getConnection();
    stat=conn.createStatement();
    long inetAton=ValidateIP.ip2long(ip);
    String query="SELECT * FROM IP_COUNTRY WHERE ipFROM <= " + inetAton + " ORDER BY ipFROM DESC LIMIT 1";
    ResultSet result=stat.executeQuery(query);
    while (result.next()) {
      String c=result.getString("countrySHORT");
      String rn=result.getString("ipREGION");
      String cn=result.getString("ipCITY");
      String lat=result.getString("ipLATITUDE");
      String lng=result.getString("ipLONGITUDE");
      Location loc=new Location(c,rn,cn,lat,lng);
      return loc;
    }
    return Location.UNKNOWN;
  }
 catch (  Exception e) {
    e.printStackTrace();
  }
 finally {
    try {
      conn.close();
      stat.close();
    }
 catch (    Exception e) {
      e.printStackTrace();
    }
  }
  return null;
}
 

private void testSQL(AjaxRequestTarget target){
  String url=sqlUrlField.getDefaultModelObject().toString();
  String username=sqlUsernameField.getModelObject().toString();
  String password=sqlPasswordField.getModelObject().toString();
  boolean result=false;
  try {
    Class.forName(sqlDriver);
    Connection conn=DriverManager.getConnection(url,username,password);
    result=conn != null;
  }
 catch (  Exception e) {
    log.error("Failed SQL test",e);
  }
  sqlTestImage.setBoolean(result);
  target.addComponent(sqlTestImage);
}
 

@Override public Connection getConnection() throws SQLException {
  long start=System.nanoTime();
  try {
    acquirePermit();
    boolean checkedOut=false;
    try {
      Connection connection=createConnection();
      checkedOut=true;
      return connection;
    }
  finally {
      if (!checkedOut) {
        semaphore.release();
      }
    }
  }
  finally {
    stats.connectionCheckedOut(nanosSince(start));
  }
}
 

private void bulkloadTableFromResource(String table,Resource resource){
  log.debug("bulk-loading data from '" + resource.getFilename() + "' into table '"+ table+ "'");
  String sql="COPY " + table + " FROM STDIN WITH DELIMITER E'\t' NULL AS 'NULL'";
  try {
    Connection con=DataSourceUtils.getConnection(dataSource);
    PGConnection pgCon=(PGConnection)con;
    pgCon.getCopyAPI().copyIn(sql,resource.getInputStream());
    DataSourceUtils.releaseConnection(con,dataSource);
  }
 catch (  SQLException e) {
    throw new DatabaseAccessException(e);
  }
catch (  IOException e) {
    throw new FileAccessException(e);
  }
}
 

/** 
 * Returns a <code>Map<String, Object></code> of properties stored in the database
 * @throws Exception
 */
synchronized Map<String,Object> load() throws Exception {
  Map<String,Object> map=new HashMap<String,Object>();
  Connection conn=null;
  PreparedStatement pstmt=null;
  ResultSet rs=null;
  try {
    conn=getConnection();
    pstmt=conn.prepareStatement(query.toString());
    rs=pstmt.executeQuery();
    while (rs.next()) {
      String key=(String)rs.getObject(keyColumnName);
      Object value=rs.getObject(valueColumnName);
      map.put(key,value);
    }
  }
 catch (  SQLException e) {
    throw e;
  }
 finally {
    close(conn,pstmt,rs);
  }
  return map;
}
 

@Test public void shouldBeAbleToInjectMembersIntoTestClass() throws Exception {
  Assert.assertNotNull(version);
  Assert.assertEquals(new Integer(6),version);
  Assert.assertNotNull(name);
  Assert.assertEquals("Jetty",name);
  Assert.assertNotNull(containerType);
  Assert.assertEquals("Embedded",containerType);
  Assert.assertNotNull(ds);
  Connection c=null;
  try {
    c=ds.getConnection();
    Assert.assertEquals("H2",c.getMetaData().getDatabaseProductName());
    c.close();
  }
 catch (  Exception e) {
    Assert.fail(e.getMessage());
  }
 finally {
    if (c != null && !c.isClosed()) {
      c.close();
    }
  }
  Assert.assertNotNull(testBean);
  Assert.assertEquals("Jetty",testBean.getName());
}
 

@Test public void shouldBeAbleToInjectMembersIntoTestClass() throws Exception {
  Assert.assertNotNull(version);
  Assert.assertEquals(new Integer(6),version);
  Assert.assertNotNull(name);
  Assert.assertEquals("Jetty",name);
  Assert.assertNotNull(containerType);
  Assert.assertEquals("Embedded",containerType);
  Assert.assertNotNull(ds);
  Connection c=null;
  try {
    c=ds.getConnection();
    Assert.assertEquals("H2",c.getMetaData().getDatabaseProductName());
    c.close();
  }
 catch (  Exception e) {
    Assert.fail(e.getMessage());
  }
 finally {
    if (c != null && !c.isClosed()) {
      c.close();
    }
  }
  Assert.assertNotNull(testBean);
  Assert.assertEquals("Jetty",testBean.getName());
}
 

private Connection getConnection2(long timeoutMs) throws SQLException {
synchronized (this) {
    if (isDisposed) {
      throw new IllegalStateException("Connection pool has been disposed.");
    }
  }
  try {
    if (!semaphore.tryAcquire(timeoutMs,TimeUnit.MILLISECONDS)) {
      throw new TimeoutException();
    }
  }
 catch (  InterruptedException e) {
    throw new RuntimeException("Interrupted while waiting for a database connection.",e);
  }
  boolean ok=false;
  try {
    Connection conn=getConnection3();
    ok=true;
    return conn;
  }
  finally {
    if (!ok) {
      semaphore.release();
    }
  }
}
 

private Connection getConnection2(long timeoutMs) throws SQLException {
synchronized (this) {
    if (isDisposed) {
      throw new IllegalStateException("Connection pool has been disposed.");
    }
  }
  try {
    if (!semaphore.tryAcquire(timeoutMs,TimeUnit.MILLISECONDS)) {
      throw new TimeoutException();
    }
  }
 catch (  InterruptedException e) {
    throw new RuntimeException("Interrupted while waiting for a database connection.",e);
  }
  boolean ok=false;
  try {
    Connection conn=getConnection3();
    ok=true;
    return conn;
  }
  finally {
    if (!ok) {
      semaphore.release();
    }
  }
}
 

private Connection getConnection2(long timeoutMs) throws SQLException {
synchronized (this) {
    if (isDisposed) {
      throw new IllegalStateException("Connection pool has been disposed.");
    }
  }
  try {
    if (!semaphore.tryAcquire(timeoutMs,TimeUnit.MILLISECONDS)) {
      throw new TimeoutException();
    }
  }
 catch (  InterruptedException e) {
    throw new RuntimeException("Interrupted while waiting for a database connection.",e);
  }
  boolean ok=false;
  try {
    Connection conn=getConnection3();
    ok=true;
    return conn;
  }
  finally {
    if (!ok) {
      semaphore.release();
    }
  }
}
 

/** 
 * Performs the application rollbacks
 * @param context       the database context to run the patches in
 * @param rollbackLevel the level the system should rollback to
 * @param forceRollback is a boolean indicating if the application should ignore a check to see if all patches are rollbackable
 * @return the number of patches applied
 * @throws SQLException       if an unrecoverable database error occurs while working with the patches table.
 * @throws MigrationException if an unrecoverable error occurs during the migration
 */
public int doRollbacks(JdbcMigrationContext context,int[] rollbackLevel,boolean forceRollback) throws SQLException, MigrationException {
  PatchInfoStore patchTable=createPatchStore(context);
  lockPatchStore(context);
  int executedPatchCount=0;
  try {
    Connection conn=context.getConnection();
    boolean commitState=conn.getAutoCommit();
    conn.setAutoCommit(false);
    try {
      executedPatchCount=migrationProcess.doRollbacks(patchTable,rollbackLevel,context,forceRollback);
    }
  finally {
      if ((conn != null) && !conn.isClosed()) {
        conn.setAutoCommit(commitState);
      }
    }
  }
 catch (  MigrationException me) {
    patchTable.unlockPatchStore();
    throw me;
  }
  try {
    migrationProcess.doPostPatchMigrations(context);
    return executedPatchCount;
  }
  finally {
    try {
      patchTable.unlockPatchStore();
    }
 catch (    MigrationException e) {
      log.error("Error unlocking patch table: ",e);
    }
  }
}
 

@Override protected void createStatements(String pluginName,Connection connection) throws Exception {
  this.loadAreas=dbConnection.getConnection().prepareStatement("SELECT areaOwner, x, z FROM zones");
  this.insertArea=dbConnection.getConnection().prepareStatement("INSERT INTO zones (areaOwner, x, z) VALUES(?, ?, ?)");
  this.updateArea=dbConnection.getConnection().prepareStatement("UPDATE zones SET areaOwner = ? , creationDate = ? WHERE x = ? AND z = ?");
  this.checkExistance=dbConnection.getConnection().prepareStatement("SELECT 1 FROM zones WHERE x = ? AND z = ? LIMIT 1");
}
 

public Connection getDatabase(){
  if (connection == null) {
    try {
      Class.forName(DatabaseOptions.db_driver);
      connection=DriverManager.getConnection(DatabaseOptions.db_uri,DatabaseOptions.db_user,DatabaseOptions.db_pass);
    }
 catch (    Exception e) {
      System.out.println("JAVA_ERROR: failed to load " + DatabaseOptions.db_driver + " driver.");
      e.printStackTrace();
    }
  }
  return connection;
}
 

/** 
 * <p>Executes a sql script.</p>
 * @param session the hibernate session
 * @param fileName     the file name
 * @throws java.io.IOException if any error occurs
 */
public static void runScript(Session session,String fileName) throws IOException {
  InputStream input=Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName);
  BufferedReader inputReader=new BufferedReader(new InputStreamReader(input));
  StringBuilder stringBuilder=new StringBuilder();
  String line;
  while ((line=inputReader.readLine()) != null) {
    if (!line.startsWith("--")) {
      stringBuilder.append(line);
    }
  }
  String[] commands=stringBuilder.toString().split(";");
  for (  final String command : commands) {
    session.doWork(new Work(){
      public void execute(      Connection connection) throws SQLException {
        connection.prepareStatement(command).execute();
      }
    }
);
  }
}