Java Code Examples for 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();
}