Java 类java.sql.Connection 实例源码

public static boolean updateBorradoTodosProductoPresupuestoDestinatario(ProductoPresupuestoDestinatario destinatarioObjeto, String usuarioResponsable) throws ParseException {
    Connection conect = ConnectionConfiguration.conectar();

    Statement statement = null;
    destinatarioObjeto.changeBorrado();

    String query = "update producto_presupuesto_destinatario set borrado='" + destinatarioObjeto.isBorrado() + "', ";
    query += "usuario_responsable='" + usuarioResponsable + "'";

    query += " where nivel =" +destinatarioObjeto.getNivel()+ " and entidad = "+ destinatarioObjeto.getEntidad()+" and tipo_presupuesto = "+destinatarioObjeto.getTipo_presupuesto()+" and programa = "+destinatarioObjeto.getPrograma()+" and subprograma = "+destinatarioObjeto.getSubprograma()+" and proyecto = "+destinatarioObjeto.getProyecto()+" and producto = "+destinatarioObjeto.getProducto();
    try {
        statement = conect.createStatement();
        statement.execute(query);

        conect.close();
        return true;
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }

}

public static DbIterator<AssetTransfer> getAccountAssetTransfers(long accountId, long assetId, int from, int to) {
    Connection con = null;
    try {
        con = Db.getConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT * FROM asset_transfer WHERE sender_id = ? AND asset_id = ?"
                + " UNION ALL SELECT * FROM asset_transfer WHERE recipient_id = ? AND sender_id <> ? AND asset_id = ? ORDER BY height DESC"
                + DbUtils.limitsClause(from, to));
        int i = 0;
        pstmt.setLong(++i, accountId);
        pstmt.setLong(++i, assetId);
        pstmt.setLong(++i, accountId);
        pstmt.setLong(++i, accountId);
        pstmt.setLong(++i, assetId);
        DbUtils.setLimits(++i, pstmt, from, to);
        return assetTransferTable.getManyBy(con, pstmt, false);
    } catch (SQLException e) {
        DbUtils.close(con);
        throw new RuntimeException(e.toString(), e);
    }
}

@Override
public void setReorderingWindow(Connection txn, ContactId c, TransportId t,
        long rotationPeriod, long base, byte[] bitmap) throws DbException {
    PreparedStatement ps = null;
    try {
        String sql = "UPDATE incomingKeys SET base = ?, bitmap = ?"
                + " WHERE contactId = ? AND transportId = ? AND period = ?";
        ps = txn.prepareStatement(sql);
        ps.setLong(1, base);
        ps.setBytes(2, bitmap);
        ps.setInt(3, c.getInt());
        ps.setString(4, t.getString());
        ps.setLong(5, rotationPeriod);
        int affected = ps.executeUpdate();
        if (affected < 0 || affected > 1) throw new DbStateException();
        ps.close();
    } catch (SQLException e) {
        tryToClose(ps);
        throw new DbException(e);
    }
}

@Override
public int insert(EventBean data) throws SQLException {
    try (Connection conn = mDataSource.getConnection();
            PreparedStatementHandle prep = (PreparedStatementHandle) conn.prepareStatement("INSERT INTO events "
                    + "(name, description, status, reserved_to_affiliates, reserved_to_partners, minimum_views, minimum_followers) VALUES "
                    + "(?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS)) {
        prep.setString(1, data.getName());
        prep.setString(2, data.getDescription());
        prep.setString(3, data.getStatus().name());
        prep.setBoolean(4, data.isReservedToAffiliates());
        prep.setBoolean(5, data.isReservedToPartners());
        prep.setInt(6, data.getMinimumViews());
        prep.setInt(7, data.getMinimumFollowers());
        prep.executeUpdate();
        try (ResultSet rs = prep.getGeneratedKeys()) {
            if (rs.next()) return rs.getInt(1);
            throw new SQLException("Cannot insert element.");
        }
    }
}

@Override
@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
public void setQueueId(Long taskId, String queueId) {
    Connection connection = null;

    try {
        connection = ds.getConnection();

        new H2SqlBuilder().update(connection, Query
                .UPDATE(TABLE.JMD_TASK())
                .SET(JMD_TASK.QUEUE_ID(), queueId)
                .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
        );
    } catch (SQLException e) {
        LOGGER.log(Level.SEVERE, e.toString(), e);
        throw new RuntimeException(e);
    } finally {
        SqlUtil.close(connection);
    }
}

public static boolean borradoBeneficiarioTipo(BeneficiarioTipo objeto, String usuarioResponsable){
     Connection conect=ConnectionConfiguration.conectar();
     Statement statement = null;
     objeto.changeBorrado();

         String query = "update beneficiario_tipo set borrado='"+objeto.isBorrado()+"'";
                query += ", usuario_responsable='" + usuarioResponsable + "'";

         query+=" where id ="+objeto.getId(); 
         try {
            statement=conect.createStatement();
            statement.execute(query);
            conect.close();
            return true;
        } catch (SQLException e) {e.printStackTrace(); return false;}
}

/**
 * 执行SQL文件
 * 
 * @param conn
 *            数据库连接
 * @param fileName
 *            文件名
 * @param params
 *            执行参数
 * @throws IOException
 *             IO异常
 * @throws SQLException
 *             SQL异常
 */
private void excuteSqlFromFile(Connection conn, String fileName, Object... params) throws IOException, SQLException {

    // 新建数据库
    java.net.URL url = this.getClass().getResource(fileName);
    // 从URL对象中获取路径信息
    String realPath = url.getPath();

    File file = new File(realPath);
    // 指定文件字符集
    BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF8"));
    String sql = new String();
    String line = new String();
    while ((line = br.readLine()) != (null)) {
        sql += line + "\r\n";
    }
    br.close();
    Statement stmt = conn.createStatement();
    sql = MessageFormat.format(sql, params);
    stmt.execute(sql);
}

/**
 * @see org.alfasoftware.morf.jdbc.SqlScriptExecutor.QueryBuilder#processWith(org.alfasoftware.morf.jdbc.SqlScriptExecutor.ResultSetProcessor)
 */
@Override
public <T> T processWith(final ResultSetProcessor<T> resultSetProcessor) {
  try {
    final Holder<T> holder = new Holder<>();

    Work work = new Work() {
      @Override
      public void execute(Connection innerConnection) throws SQLException {
        holder.set(executeQuery(query, parameterMetadata, parameterData, innerConnection, resultSetProcessor, maxRows, queryTimeout, standalone));
      }
    };

    if (connection == null) {
      // Get a new connection, and use that...
      doWork(work);
    } else {
      // Get out own connection, and use that...
      work.execute(connection);
    }

    return holder.get();
  } catch (SQLException e) {
    throw new RuntimeSqlException("Error with statement", e);
  }
}

public static void deleteUnidadResponsable(String id, String entidad_id, String entidad_nivel_id, String unidad_jerarquica_id){
     Connection conect=ConnectionConfiguration.conectar();
     Statement statement = null;
 String                             query = "delete from unidad_responsable ";
 //if (id!="")                      query+= "id=\""+id+"\", ";
 /*if (nombre!="")                  query+= "nombre=\""+nombre+"\", ";
 if (descripcion!="")               query+= "descripcion=\""+descripcion+"\", ";
 if (abrev!="")                     query+= "abrev=\""+abrev+"\", ";
 if (numero_fila!="")               query+= "numero_fila=\""+numero_fila+"\", ";
 //if (entidad_id!="")              query+= "entidad_id=\""+entidad_id+"\", ";
 //if (entidad_nivel_id!="")        query+= "entidad_nivel_id=\""+entidad_nivel_id+"\", ";
 //if (unidad_jerarquica_id!="")    query+= "unidad_jerarquica_id=\""+unidad_jerarquica_id+"\", ";
 if (anho!="")                      query+= "anho=\""+anho+"\", ";
 query = query.substring(0, query.length()-2);*/
 query+="where id="+id+" and entidad_id="+entidad_id+" and entidad_nivel_id="+entidad_nivel_id+" and unidad_jerarquica_id="+unidad_jerarquica_id;

try {
    statement=conect.createStatement();
    statement.execute(query);
    conect.close();
} catch (SQLException e) {e.printStackTrace();}
 }

public static MapleRing loadFromDb(int ringId) {
    try {
        MapleRing ret = null;
        Connection con = DatabaseConnection.getConnection(); // Get a connection to the database
        PreparedStatement ps = con.prepareStatement("SELECT * FROM rings WHERE id = ?"); // Get ring details..
        ps.setInt(1, ringId);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            ret = new MapleRing(ringId, rs.getInt("partnerRingId"), rs.getInt("partnerChrId"), rs.getInt("itemid"), rs.getString("partnerName"));
        }
        rs.close();
        ps.close();
        return ret;
    } catch (SQLException ex) {
        ex.printStackTrace();
        return null;
    }
}

/**
 * Returns a String report for the specified JDBC Connection.
 *
 * For databases with poor JDBC support, you won't get much detail.
 */
public static String getBanner(Connection c) {
    try {
        DatabaseMetaData md = c.getMetaData();
        return (md == null)
                ? null
                : SqltoolRB.jdbc_established.getString(
                        md.getDatabaseProductName(),
                        md.getDatabaseProductVersion(),
                        md.getUserName(),
                                (c.isReadOnly() ? "R/O " : "R/W ")
                                + RCData.tiToString(
                                c.getTransactionIsolation()));
    } catch (SQLException se) {
        return null;
    }
}

public void addDepart(Department depart) {
    // TODO Auto-generated method stub
    Connection con = DBconnection.getConnection();
    PreparedStatement prep = null;  
    try {
        String sql="insert into department(departname,content) values (?,?)";
        prep = con.prepareStatement(sql);
        prep.setString(1, depart.getDepartname());
        prep.setString(2, depart.getContent());

        prep.executeUpdate();

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();

    } finally {
        DBconnection.close(con);
        DBconnection.close(prep);
    }
}

@Override
public void deleteRobotSettings(long botId, long userId, String propKey) {
    if (Preconditions.isEmptyString(propKey)) {
        return;
    }
    String sql = "DELETE FROM " + getTableName() + " WHERE bot_id = ? AND user_id = ? AND prop_key = ?";
    try (
            Connection conn = this.getDataSource().getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ) {
        ps.setLong(1, botId);
        ps.setLong(2, userId);
        ps.setString(3, propKey);
        ps.executeUpdate();
    } catch (SQLException e) {
        getLogger().error(e.getMessage(), e);
    }
}

protected void doClose() {

        try {
            Connection con = getConnection("sa", "password", false);

            if (con != null) {
                Statement stmt = con.createStatement();

                stmt.execute("SHUTDOWN");
                stmt.close();
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.exit(0);
    }

/**
 * Creates a list of commands to be executed against the shards associated with the connection.
 *
 * @return Pairs of shard locations and associated commands.
 */
private List<Pair<ShardLocation, Statement>> getShardCommands() {
    return this.connection.getShardConnections().stream().map(sc -> {
        try {
            Connection conn = sc.getRight();
            if (conn.isClosed()) {
                // TODO: This hack needs to be perfected. Reopening of connection is not straight forward.
                conn = getConnectionForLocation(sc.getLeft());
            }
            Statement statement = conn.prepareStatement(this.commandText, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setQueryTimeout(this.getCommandTimeoutPerShard());
            return new ImmutablePair<>(sc.getLeft(), statement);
        }
        catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }).collect(Collectors.toList());
}

@Test public void testTypeInfo() throws Exception {
  ConnectionSpec.getDatabaseLock().lock();
  try {
    final Connection connection = getLocalConnection();
    final ResultSet resultSet =
        connection.getMetaData().getTypeInfo();
    assertTrue(resultSet.next());
    final ResultSetMetaData metaData = resultSet.getMetaData();
    assertTrue(metaData.getColumnCount() >= 18);
    assertEquals("TYPE_NAME", metaData.getColumnName(1));
    assertEquals("DATA_TYPE", metaData.getColumnName(2));
    assertEquals("PRECISION", metaData.getColumnName(3));
    assertEquals("SQL_DATA_TYPE", metaData.getColumnName(16));
    assertEquals("SQL_DATETIME_SUB", metaData.getColumnName(17));
    assertEquals("NUM_PREC_RADIX", metaData.getColumnName(18));
    resultSet.close();
    connection.close();
  } finally {
    ConnectionSpec.getDatabaseLock().unlock();
  }
}

@Test
  public void testPreparedStatementExecute() throws Exception {
    long expectedCount = getTimerCount(executeStatementName) + 1;

    Connection connection = datasource.getConnection();
    PreparedStatement preparedStatement = connection.prepareStatement(SELECT_QUERY);

    int rnd = ThreadLocalRandom.current().nextInt(0, presetElements.size());
    String key = presetElementKeys.get(rnd);
    Integer value = presetElements.get(key);

    preparedStatement.setString(1, key);

//    ResultSet resultSet = preparedStatement.executeQuery();
    preparedStatement.execute();
    ResultSet resultSet = preparedStatement.getResultSet();
    resultSet.next();
    assertEquals(value.intValue(), resultSet.getInt(2));
    connection.close();

    assertEquals(expectedCount,
        getTimerCount(executeStatementName));
  }

public int getAdvancedSectorInactiveArraySize() {
    int size = 0;
    String sqlid = "SELECT * \n"
            + "FROM Advanced_Sector_List_Add";

    try (Connection conn = connector();
            Statement stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(sqlid)) {
            while (result.next()) {
                size = size + 1;
            }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return size;
}

/**
 * Actually close the given Connection, obtained from the given DataSource.
 * Same as {@link #releaseConnection}, but throwing the original SQLException.
 * <p>Directly accessed by {@link TransactionAwareDataSourceProxy}.
 * @param con the Connection to close if necessary
 * (if this is {@code null}, the call will be ignored)
 * @param dataSource the DataSource that the Connection was obtained from
 * (may be {@code null})
 * @throws SQLException if thrown by JDBC methods
 * @see #doGetConnection
 */
public static void doReleaseConnection(Connection con, DataSource dataSource) throws SQLException {
    if (con == null) {
        return;
    }
    if (dataSource != null) {
        ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
        if (conHolder != null && connectionEquals(conHolder, con)) {
            // It's the transactional Connection: Don't close it.
            conHolder.released();
            return;
        }
    }
    logger.debug("Returning JDBC Connection to DataSource");
    doCloseConnection(con, dataSource);
}

/**
 * Tests fix for BUG#28596 - parser in client-side prepared statements runs
 * to end of statement, rather than end-of-line for '#' comments.
 * 
 * Also added support for '--' single-line comments
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug28596() throws Exception {
    String query = "SELECT #\n?, #\n? #?\r\n,-- abcdefg \n?";

    this.pstmt = ((com.mysql.jdbc.Connection) this.conn).clientPrepareStatement(query);
    this.pstmt.setInt(1, 1);
    this.pstmt.setInt(2, 2);
    this.pstmt.setInt(3, 3);

    assertEquals(3, this.pstmt.getParameterMetaData().getParameterCount());
    this.rs = this.pstmt.executeQuery();

    assertTrue(this.rs.next());

    assertEquals(1, this.rs.getInt(1));
    assertEquals(2, this.rs.getInt(2));
    assertEquals(3, this.rs.getInt(3));
}

@Override
public void close() throws IOException
{
    try
    {
        Connection connection = sqlSession.getConnection();
        if (!connection.getAutoCommit())
        {
            connection.setAutoCommit(true);
        }
    } catch (SQLException e)
    {
        throw new IOException(e);
    }
    sqlSession.close();
}

@Override
protected List<AbstractTablePartWorker> prepareWorkers(Connection source, Connection destination)
        throws SQLException
{
    totalRecordCount = converterUtils.getDestinationRecordCount(destination, table);
    List<AbstractTablePartWorker> workers;
    if (totalRecordCount > 0)
    {
        if (totalRecordCount >= 10000)
        {
            workers = createWorkers(source, destination);
        }
        else
        {
            workers = new ArrayList<>(1);
            workers.add(new SingleDeleteWorker(config, table, totalRecordCount));
        }
    }
    else
    {
        workers = Collections.emptyList();
    }

    return workers;
}

/**
 * {@inheritDoc}
 */
@Override
public Integer getOptionVotes(long pollID, long optionID) {
    Integer data = null;
    Connection con = SQLConnectionProvider.getConnection();
    try (PreparedStatement pst = con
            .prepareStatement("SELECT votesCount FROM PollOptions WHERE pollID=? AND id=?")) {
        pst.setLong(1, Long.valueOf(pollID));
        pst.setLong(2, optionID);
        try (ResultSet rs = pst.executeQuery()) {
            while (rs != null && rs.next()) {
                data = Integer.valueOf(rs.getInt(1));
            }
        }
    } catch (Exception ex) {
        throw new DAOException(
                "Unable to get votes from poll options table.", ex);
    }
    return data;
}

public boolean gainCharacterSlot() {
    if (getCharacterSlots() >= 15) {
        return false;
    }
    charslots++;
    try {
        Connection con = DatabaseConnection.getConnection();
        try (PreparedStatement ps = con
                .prepareStatement("UPDATE character_slots SET charslots = ? WHERE worldid = ? AND accid = ?")) {
            ps.setInt(1, charslots);
            ps.setInt(2, world);
            ps.setInt(3, accId);
            ps.executeUpdate();
            ps.close();
        }
    } catch (SQLException sqlE) {
        return false;
    }
    return true;
}

@Override
public int queryDataNum() throws SQLException {

    Connection conn = DBUtil.getConnection();

    String sql = "select count(*) from login;";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    ResultSet rs = pstmt.executeQuery();

    int num;
    if (rs.next()) num = rs.getInt("count(*)");
    else num = 0;

    rs.close();
    pstmt.close();

    return num;
}

@Test
public void testCommit() throws SQLException, ClassNotFoundException, NamingException {

    Client client = ClientBuilder.newClient();
    client.target("http://127.0.0.1:8090/rest/test").request().get();

    Class.forName("org.h2.Driver");
    int resultCount = 0;
    try (Connection conn = DriverManager.getConnection("jdbc:h2:~/data/testdb;AUTO_SERVER=TRUE", "SA", "SA")) {
        ResultSet rs = conn.createStatement().executeQuery("SELECT ID, OWNER FROM TASK");

        while (rs.next()) {
            resultCount++;
        }
    }

    Assert.assertEquals(1, resultCount);
}

public static void CreateTableData(String groupId){
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try{
        connection = DbConnection.getConnection();
        preparedStatement = connection.prepareStatement(
                "CREATE TABLE IF NOT EXISTS " + groupId +
                        "(" +
                        id + " TEXT PRIMARY KEY, " +
                        deskripsi + " TEXT NOT NULL, " +
                        tipe + " " +tipe_data + " NOT NULL" +
                        ")"
        );
        if (preparedStatement.executeUpdate()==1)
            System.out.println("Create table " + groupId + "berhasil");
    } catch (Exception ex){
        System.out.println("Gagal create table " + groupId + " : " + ex.toString());
    } finally {
        DbConnection.ClosePreparedStatement(preparedStatement);
        DbConnection.CloseConnection(connection);
    }
}

/**
 * @return Returns the clan notice.
 */
public String getNotice()
{
 Connection con = null;
 try
 {
     con = L2DatabaseFactory.getInstance().getConnection();
     PreparedStatement statement = con.prepareStatement("SELECT notice FROM clan_notices WHERE clanID=?");
     statement.setInt(1, getClanId());
     ResultSet rset = statement.executeQuery();

     while (rset.next())
     {
         _notice = rset.getString("notice");
     }

     rset.close();
     statement.close();
     con.close();

 } catch (Exception e)
 {
     if (Config.DEBUG)
     System.out.println("BBS: Error while getting notice from DB for clan "+ this.getClanId() + "");
     if(e.getMessage()!=null)
         if (Config.DEBUG)
         System.out.println("BBS: Exception = "+e.getMessage()+"");
 }
 return _notice;
}

/**
 * Insert new chapter to the given webtoon, and image database for that chapter.
 * @param webtoonName webtoon name
 * @param authorName author name
 * @param chapterNumber new chapter number
 * @param chapterName new chapter name
 * @param uploadedDate new uploaded date
 * @param likeNumber number of likes
 * @param dislikeNumber number of dislikes
 * @param thumbnailImage filename of thumbnail images
 */
public static void insertNewChapter(String webtoonName, String authorName,
                                    String chapterNumber, String chapterName, String uploadedDate,
                                    int likeNumber, int dislikeNumber, String thumbnailImage) {
    String url = "jdbc:sqlite:metadata";

    try {
        // Open SQL
        Connection connection = DriverManager.getConnection(url);
        Statement statement = connection.createStatement();

        String webtoonHashID = HashManager.md5(webtoonName + "_" + authorName);

        // insert chapter SQL
        String sql = "INSERT INTO ChapterListDB_" + webtoonHashID +  " VALUES('"
                + chapterNumber + "', '" + chapterName + "', '" + uploadedDate + "', "
                + likeNumber + ", " + dislikeNumber + ", '" + thumbnailImage + "');";
        statement.execute(sql);

        // create ImageListDB table SQL
        String chapterHashID = HashManager.md5(chapterNumber + "_" + chapterName);
        sql = "CREATE TABLE ImageListDB_" + webtoonHashID + "_" + chapterHashID
                + "(Image TEXT);";
        statement.execute(sql);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public void deleteSource(String sourceName) {
    String delete = "DELETE FROM Source_List WHERE sourceList = ?";

    try (Connection conn = connector();
            PreparedStatement pstmt = conn.prepareStatement(delete)){
        pstmt.setString(1, sourceName);
        pstmt.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

public static void main(String[] args) {
    //ConnectionFactory connectionFactory = new JDBCConnectionFactory();
    ConnectionFactory connectionFactory = new MyPoolConnectionFactory(new JDBCConnectionFactory());

    try (Connection connection = connectionFactory.get()) {
        System.out.println("Connected to: " + connection.getMetaData().getURL());
        System.out.println("DB name: " + connection.getMetaData().getDatabaseProductName());
        System.out.println("DB version: " + connection.getMetaData().getDatabaseProductVersion());
        System.out.println("Driver: " + connection.getMetaData().getDriverName());
    } catch (SQLException e) {
        e.printStackTrace();
    }

    connectionFactory.dispose();
}

@Override
public List<ScenarioMetaInfo> getScenarioMetaInfo( int scenarioId ) throws DatabaseAccessException {

    List<ScenarioMetaInfo> scenarioMetaInfoList = new ArrayList<>();
    Connection connection = getConnection();
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        statement = connection.prepareStatement("SELECT * FROM tScenarioMetainfo WHERE scenarioId = " + scenarioId);
        rs = statement.executeQuery();
        while (rs.next()) {
            ScenarioMetaInfo runMetainfo = new ScenarioMetaInfo();
            runMetainfo.metaInfoId = rs.getInt("metaInfoId");
            runMetainfo.scenarioId = rs.getInt("scenarioId");
            runMetainfo.name = rs.getString("name");
            runMetainfo.value = rs.getString("value");
            scenarioMetaInfoList.add(runMetainfo);
        }
    } catch (Exception e) {
        throw new DatabaseAccessException("Error retrieving scenario metainfo for scenario with id '" + scenarioId
                                          + "'", e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, statement);
    }

    return scenarioMetaInfoList;
}

/**
 * Close connection.
 *
 * @param connection
 *            the connection
 */
private void closeConnection(Connection connection) {
    if (connection != null) {
        try {
            connection.close();
        } catch (SQLException e) {
            // ignore
        }
    }
}

/**
 * @see org.alfasoftware.morf.jdbc.AbstractSqlDialectTest#verifyPostInsertStatementsInsertingUnderAutonumLimit(org.alfasoftware.morf.jdbc.SqlScriptExecutor, com.mysql.jdbc.Connection)
 */
@Override
protected void verifyPostInsertStatementsInsertingUnderAutonumLimit(SqlScriptExecutor sqlScriptExecutor, Connection connection) {
  verify(sqlScriptExecutor).execute(listCaptor.capture(),eq(connection));
  verifyPostInsertStatements(listCaptor.getValue());
  verifyNoMoreInteractions(sqlScriptExecutor);
}

/**
 * Tests fix for Bug#16634180 - LOCK WAIT TIMEOUT EXCEEDED CAUSES SQLEXCEPTION, SHOULD CAUSE SQLTRANSIENTEXCEPTION
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug16634180() throws Exception {

    if (Util.isJdbc4()) {
        // relevant JDBC4+ test is testsuite.regression.jdbc4.ConnectionRegressionTest.testBug16634180()
        return;
    }

    createTable("testBug16634180", "(pk integer primary key, val integer)", "InnoDB");
    this.stmt.executeUpdate("insert into testBug16634180 values(0,0)");

    Connection c1 = null;
    Connection c2 = null;

    try {
        c1 = getConnectionWithProps(new Properties());
        c1.setAutoCommit(false);
        Statement s1 = c1.createStatement();
        s1.executeUpdate("update testBug16634180 set val=val+1 where pk=0");

        c2 = getConnectionWithProps(new Properties());
        c2.setAutoCommit(false);
        Statement s2 = c2.createStatement();
        try {
            s2.executeUpdate("update testBug16634180 set val=val+1 where pk=0");
            fail("ER_LOCK_WAIT_TIMEOUT should be thrown.");
        } catch (MySQLTransientException ex) {
            assertEquals(MysqlErrorNumbers.ER_LOCK_WAIT_TIMEOUT, ex.getErrorCode());
            assertEquals(SQLError.SQL_STATE_ROLLBACK_SERIALIZATION_FAILURE, ex.getSQLState());
            assertEquals("Lock wait timeout exceeded; try restarting transaction", ex.getMessage());
        }
    } finally {
        if (c1 != null) {
            c1.close();
        }
        if (c2 != null) {
            c2.close();
        }
    }
}

/**
 * Clean data base with 'MDA' JIRA project.
 */
@AfterClass
public static void cleanJiraDataBaseForImport() throws SQLException {
    final DataSource datasource = new SimpleDriverDataSource(new JDBCDriver(), "jdbc:hsqldb:mem:dataSource", null, null);
    final Connection connection = datasource.getConnection();

    try {
        ScriptUtils.executeSqlScript(connection, new EncodedResource(new ClassPathResource("sql/upload/jira-drop.sql"), StandardCharsets.UTF_8));
    } finally {
        connection.close();
    }
}

@Override
public int createEvent(final Event event) {
    if (event == null) {
        throw new IllegalArgumentException("event cannot be null");
    }
    if (event.getId() != null) {
        throw new IllegalArgumentException("event.getId() must be null when creating a new Message");
    }
    final CalendarUser owner = event.getOwner();
    if (owner == null) {
        throw new IllegalArgumentException("event.getOwner() cannot be null");
    }
    final CalendarUser attendee = event.getAttendee();
    if (attendee == null) {
        throw new IllegalArgumentException("attendee.getOwner() cannot be null");
    }
    final Calendar when = event.getWhen();
    if(when == null) {
        throw new IllegalArgumentException("event.getWhen() cannot be null");
    }
    KeyHolder keyHolder = new GeneratedKeyHolder();
    this.jdbcOperations.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(
                    "insert into events (when,summary,description,owner,attendee) values (?, ?, ?, ?, ?)",
                    new String[] { "id" });
            ps.setDate(1, new java.sql.Date(when.getTimeInMillis()));
            ps.setString(2, event.getSummary());
            ps.setString(3, event.getDescription());
            ps.setInt(4, owner.getId());
            ps.setObject(5, attendee == null ? null : attendee.getId());
            return ps;
        }
    }, keyHolder);
    return keyHolder.getKey().intValue();
}

void createDBThenInit() throws Exception {
    Configurator cfg = ConfiguratorFactory.getDefaultInstance();
    String propCreate = cfg.getProperty("create", "false");
    cfg.setProperty("create", "true");
    Connection connection = ConnectionManager.getConnection(cfg);
    //create db success;
    //do DDL create table
    Statement stats = connection.createStatement();

    String[] ddls = {
        "CREATE TABLE ITEMS(\r\n" + 
        "ID BIGINT PRIMARY KEY,\r\n" + 
        "NAME VARCHAR(128),\r\n" + 
        "DESCRIPTION VARCHAR(512),\r\n" + 
        "TYPE SMALLINT,\r\n" + 
        "CONTENT CLOB,\r\n" + 
        "READ_COUNT SMALLINT,\r\n" + 
        "CREATE_TIME TIMESTAMP,\r\n" + 
        "UPDATE_TIME TIMESTAMP,\r\n" + 
        "PARENT BIGINT\r\n" + 
        ")",
        "CREATE TABLE TAGS(\r\n" + 
        "ID BIGINT PRIMARY KEY,\r\n" + 
        "NAME VARCHAR(128),\r\n" + 
        "CREATE_TIME TIMESTAMP\r\n" + 
        ")"
    };
    for(String sql:ddls) {
        stats.addBatch(sql);
    }
    int[] rsts = stats.executeBatch();
    for(int i=0;i<rsts.length;i++) {
        ProcessLogger.debug("Executed {0} DDL result:{1}",i,rsts[i]);
    }
    cfg.setProperty("create", propCreate);
}

/**
 * Tests fix for BUG#36948 - Trying to use trustCertificateKeyStoreUrl
 * causes an IllegalStateException.
 * 
 * Requires test certificates from testsuite/ssl-test-certs to be installed
 * on the server being tested.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug36948() throws Exception {
    Connection _conn = null;

    try {
        Properties props = getPropertiesFromTestsuiteUrl();
        String host = props.getProperty(NonRegisteringDriver.HOST_PROPERTY_KEY, "localhost");
        String port = props.getProperty(NonRegisteringDriver.PORT_PROPERTY_KEY, "3306");
        String db = props.getProperty(NonRegisteringDriver.DBNAME_PROPERTY_KEY, "test");

        String hostSpec = host;

        if (!NonRegisteringDriver.isHostPropertiesList(host)) {
            hostSpec = host + ":" + port;
        }

        props = getHostFreePropertiesFromTestsuiteUrl();
        props.remove("useSSL");
        props.remove("requireSSL");
        props.remove("verifyServerCertificate");
        props.remove("trustCertificateKeyStoreUrl");
        props.remove("trustCertificateKeyStoreType");
        props.remove("trustCertificateKeyStorePassword");

        final String url = "jdbc:mysql://" + hostSpec + "/" + db + "?useSSL=true&requireSSL=true&verifyServerCertificate=true"
                + "&trustCertificateKeyStoreUrl=file:src/testsuite/ssl-test-certs/ca-truststore&trustCertificateKeyStoreType=JKS"
                + "&trustCertificateKeyStorePassword=password";

        _conn = DriverManager.getConnection(url, props);
    } finally {
        if (_conn != null) {
            _conn.close();
        }
    }

}

public DBCPool(int maxPoolSize, int maxIdle,
               int minIdle, long keepAliveTime, Queue queue) {
    PoolConfig.config(maxPoolSize, maxIdle, minIdle, keepAliveTime);
    this.maxPoolSize = maxPoolSize;
    this.maxIdle = maxIdle;
    this.minIdle = minIdle;
    this.keepAliveTime = keepAliveTime;
    conpool = new ConcurrentLinkedQueue<Connection>();
    idleQueue = queue;
    initQueue();
}