Java Code Examples for java.sql.PreparedStatement#isClosed()

/**
 * Closes PreparedStatemet, it's connection and last ResultSet
 * 
 * @param statement
 *            statement to close
 */
public static void close(PreparedStatement statement) {

	try {
		if (statement.isClosed()) {
			// noinspection ThrowableInstanceNeverThrown
			log.warn("Attempt to close PreparedStatement that is closes already", new Exception());
			return;
		}

		Connection c = statement.getConnection();
		statement.close();
		c.close();
	} catch (Exception e) {
		log.error("Error while closing PreparedStatement", e);
	}
}
 

/**
 * Gets a prepared statement
 *
 * @param name of the statement
 * @return the statement
 * @throws SQLException when something goes wrong
 */
public PreparedStatement getPreparedStatement(String name) throws SQLException {
	if(preparedStatementMap.isEmpty() || !preparedStatementMap.containsKey(name)) {
		prepareStatements();
	}

	PreparedStatement preparedStatement = preparedStatementMap.get(name);
	if(preparedStatement != null && !(this instanceof SQLiteStorageImpl) && preparedStatement.isClosed()) {
		prepareStatements();
		preparedStatement = preparedStatementMap.get(name);
	}

	if(preparedStatement == null) {
		throw new IllegalArgumentException("Invalid statement enum");
	}

	preparedStatement.clearParameters();
	return preparedStatement;
}
 

private void tryCloseConnection(Connection connection, PreparedStatement ps, ResultSet resultSet) {
    try {
        if (resultSet != null && !resultSet.isClosed()) {
            resultSet.close();
        }
        if (ps != null && !ps.isClosed()) {
            ps.close();
        }
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }

    } catch (SQLException ex) {
        throw new JdbcTemplateException("Cannot close the database", ex);
    }
}
 

/**
 * Get cached prepared statement (if any).
 *
 * @param sql SQL.
 * @return Prepared statement or {@code null}.
 * @throws SQLException On error.
 */
private @Nullable PreparedStatement cachedPreparedStatement(String sql, byte qryFlags) throws SQLException {
    H2CachedStatementKey key = new H2CachedStatementKey(schema, sql, qryFlags);

    PreparedStatement stmt = statementCache.get(key);

    // Nothing found.
    if (stmt == null)
        return null;

    // Is statement still valid?
    if (
        stmt.isClosed() ||                                 // Closed.
            stmt.unwrap(JdbcStatement.class).isCancelled() ||  // Cancelled.
            GridSqlQueryParser.prepared(stmt).needRecompile() // Outdated (schema has been changed concurrently).
    ) {
        statementCache.remove(schema, sql, qryFlags);

        return null;
    }

    return stmt;
}
 

public void gift(int recipient, String from, String message, int sn, int ringid) {
    PreparedStatement ps = null;
    Connection con = null;
    try {
        con = DatabaseConnection.getConnection();
        ps = con.prepareStatement("INSERT INTO `gifts` VALUES (DEFAULT, ?, ?, ?, ?, ?)");
        ps.setInt(1, recipient);
        ps.setString(2, from);
        ps.setString(3, message);
        ps.setInt(4, sn);
        ps.setInt(5, ringid);
        ps.executeUpdate();
        con.close();
    } catch (SQLException sqle) {
        sqle.printStackTrace();
    } finally {
        try {
            if (ps != null && !ps.isClosed()) ps.close();
            if (con != null && !con.isClosed()) con.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}
 

public void execute(String... sqls) throws Exception{
	if (sqls.length < 1) {
		return;
	}
	PreparedStatement stmt = null;
	try {
		for (String sql : sqls) {
			stmt = conn.prepareStatement(sql);
			stmt.execute();
		}
		if (null != stmt && !stmt.isClosed()) {
			stmt.close();
		}
		D.autoCloseConn(conn);
	} catch (Exception e) {
		D.autoCloseConn(conn);
		throw e;
	}
}
 

public void updateMacs(String macData) {
	macs.addAll(Arrays.asList(macData.split(", ")));
	StringBuilder newMacData = new StringBuilder();
	Iterator<String> iter = macs.iterator();
	PreparedStatement ps = null;
	while (iter.hasNext()) {
		String cur = iter.next();
		newMacData.append(cur);
		if (iter.hasNext()) {
			newMacData.append(", ");
		}
	}
               Connection con = null;
	try {
                       con = DatabaseConnection.getConnection();
		ps = con.prepareStatement("UPDATE accounts SET macs = ? WHERE id = ?");
		ps.setString(1, newMacData.toString());
		ps.setInt(2, accId);
		ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		try {
			if (ps != null && !ps.isClosed()) {
				ps.close();
			}
                               if (con != null && !con.isClosed()) {
				con.close();
			}
		} catch (SQLException ex) {
                               ex.printStackTrace();
		}
	}
}
 

/**
 * Receives a list of {@link PreparedStatement} and quietly closes all of them, which
 * triggers also closing their dependent objects, like a {@link ResultSet}
 *
 * @param pstmt2Close
 */
public static void closePstmts(List<PreparedStatement> pstmt2Close) {
    for (PreparedStatement pstmt : pstmt2Close) {
        try {
            if (pstmt != null && !pstmt.isClosed()) {
                pstmt.close();
            }
        } catch (SQLException e) {
            // It's not possible to recover from this and we need to continue closing
            e.printStackTrace();
        }
    }
}
 

private static void removeGuildFromAllianceOnDb(int guildId) {
    PreparedStatement ps = null;
    Connection con = null;
    try {
        con = DatabaseConnection.getConnection();
        
        ps = con.prepareStatement("DELETE FROM `allianceguilds` WHERE guildid = ?");
        ps.setInt(1, guildId);
        ps.executeUpdate();
        ps.close();
        
        con.close();
    } catch (SQLException sqle) {
        sqle.printStackTrace();
    } finally {
        try {
            if (ps != null && !ps.isClosed()) {
                ps.close();
            }
            if (con != null && !con.isClosed()) {
                con.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}
 

public static void disbandAlliance(int allianceId) {
    PreparedStatement ps = null;
    Connection con = null;
    try {
        con = DatabaseConnection.getConnection();
        
        ps = con.prepareStatement("DELETE FROM `alliance` WHERE id = ?");
        ps.setInt(1, allianceId);
        ps.executeUpdate();
        ps.close();
        
        ps = con.prepareStatement("DELETE FROM `allianceguilds` WHERE allianceid = ?");
        ps.setInt(1, allianceId);
        ps.executeUpdate();
        ps.close();
        
        con.close();
        Server.getInstance().allianceMessage(allianceId, MaplePacketCreator.disbandAlliance(allianceId), -1, -1);
        Server.getInstance().disbandAlliance(allianceId);
    } catch (SQLException sqle) {
        sqle.printStackTrace();
    } finally {
        try {
            if (ps != null && !ps.isClosed()) {
                ps.close();
            }
            if (con != null && !con.isClosed()) {
                con.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}
 

public boolean hasLevel30Character() {
    PreparedStatement ps = null;
    ResultSet rs = null;
    Connection con = null;
    try {
        con = DatabaseConnection.getConnection();
        ps = con.prepareStatement("SELECT `level` FROM `characters` WHERE accountid = ?");
        ps.setInt(1, getPlayer().getAccountID());
        rs = ps.executeQuery();
        while (rs.next()) {
            if (rs.getInt("level") >= 30) {
                ps.close();
                rs.close();
                return true;
            }
        }
    } catch (SQLException sqle) {
        sqle.printStackTrace();
    } finally {
        try {
            if (ps != null && !ps.isClosed()) {
                ps.close();
            }
            if (rs != null && !rs.isClosed()) {
                rs.close();
            }
            if (con != null && !con.isClosed()) {
                con.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    return getPlayer().getLevel() >= 30;
}
 

private void closeStatement(PreparedStatement statement) {
    try {
        if (!statement.isClosed()) {
            if (statement instanceof CachedStatement) {
                CachedStatement delegate = (CachedStatement) statement;
                delegate.closeDelegate();
            }
        }
    } catch (SQLException ignored) {
        ignored.printStackTrace();
    }
}
 

/**
 * Helper method for silently close PreparedStament object.<br>
 * Associated connection object will not be closed.
 * 
 * @param st
 *            prepared statement to close
 */
public static void close(PreparedStatement st) {
	if (st == null) {
		return;
	}

	try {
		if (!st.isClosed()) {
			st.close();
		}
	} catch (SQLException e) {
		log.error("Can't close Prepared Statement", e);
	}
}
 

private static int createPackage(int mesos, String message, String sender, int toCid, boolean quick) {
    try {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
    
        try {
            con = DatabaseConnection.getConnection();
            ps = con.prepareStatement("INSERT INTO `dueypackages` (ReceiverId, SenderName, Mesos, TimeStamp, Message, Type, Checked) VALUES (?, ?, ?, ?, ?, ?, 1)", Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, toCid);
            ps.setString(2, sender);
            ps.setInt(3, mesos);
            ps.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
            ps.setString(5, message);
            ps.setInt(6, quick ? 1 : 0);

            int updateRows = ps.executeUpdate();
            if (updateRows < 1) {
                FilePrinter.printError(FilePrinter.INSERT_CHAR, "Error trying to create package [mesos: " + mesos + ", " + sender + ", quick: " + quick + ", to CharacterId: " + toCid + "]");
                return -1;
            }
            
            int packageId;
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                packageId = rs.getInt(1);
            } else {
                FilePrinter.printError(FilePrinter.INSERT_CHAR, "Failed inserting package [mesos: " + mesos + ", " + sender + ", quick: " + quick + ", to CharacterId: " + toCid + "]");
                return -1;
            }
            
            return packageId;
        } finally {
            if (rs != null && !rs.isClosed()) {
                rs.close();
            }
            
            if (ps != null && !ps.isClosed()) {
                ps.close();
            }
            
            if (con != null && !con.isClosed()) {
                con.close();
            }
        }
    } catch (SQLException sqle) {
        sqle.printStackTrace();
    }
    
    return -1;
}
 

private void retrieveGlobal() {
    PreparedStatement ps = null;
    ResultSet rs = null;
    Connection con = null;

    try {
        con = DatabaseConnection.getConnection();
        ps = con.prepareStatement("SELECT * FROM drop_data_global WHERE chance > 0");
        rs = ps.executeQuery();

        while (rs.next()) {
            globaldrops.add(
                    new MonsterGlobalDropEntry(
                            rs.getInt("itemid"),
                            rs.getInt("chance"),
                            rs.getByte("continent"),
                            rs.getInt("minimum_quantity"),
                            rs.getInt("maximum_quantity"),
                            rs.getShort("questid")));
        }

        rs.close();
        ps.close();
        con.close();
    } catch (SQLException e) {
        System.err.println("Error retrieving drop" + e);
    } finally {
        try {
            if (ps != null && !ps.isClosed()) {
                ps.close();
            }
            if (rs != null && !rs.isClosed()) {
                rs.close();
            }
            if (con != null && !con.isClosed()) {
                con.close();
            }
        } catch (SQLException ignore) {
            ignore.printStackTrace();
        }
    }
}
 

public final List<MonsterDropEntry> retrieveDrop(final int monsterId) {
    if (drops.containsKey(monsterId)) {
        return drops.get(monsterId);
    }
    final List<MonsterDropEntry> ret = new LinkedList<>();
    
    PreparedStatement ps = null;
    ResultSet rs = null;
    Connection con = null;
    try {
        con = DatabaseConnection.getConnection();
        ps = con.prepareStatement("SELECT itemid, chance, minimum_quantity, maximum_quantity, questid FROM drop_data WHERE dropperid = ?");
        ps.setInt(1, monsterId);
        rs = ps.executeQuery();

        while (rs.next()) {
            ret.add(new MonsterDropEntry(rs.getInt("itemid"), rs.getInt("chance"), rs.getInt("minimum_quantity"), rs.getInt("maximum_quantity"), rs.getShort("questid")));
        }

        con.close();
    } catch (SQLException e) {
        e.printStackTrace();
        return ret;
    } finally {
        try {
            if (ps != null && !ps.isClosed()) {
                ps.close();
            }
            if (rs != null && !rs.isClosed()) {
                rs.close();
            }
            if (con != null && !con.isClosed()) {
                con.close();
            }
        } catch (SQLException ignore) {
            ignore.printStackTrace();
            return ret;
        }
    }
    drops.put(monsterId, ret);
    return ret;
}
 

public CashShop(int accountId, int characterId, int jobType) throws SQLException {
    this.accountId = accountId;
    this.characterId = characterId;

    if (!YamlConfig.config.server.USE_JOINT_CASHSHOP_INVENTORY) {
        if (jobType == 0) {
            factory = ItemFactory.CASH_EXPLORER;
        } else if (jobType == 1) {
            factory = ItemFactory.CASH_CYGNUS;
        } else if (jobType == 2) {
            factory = ItemFactory.CASH_ARAN;
        }
    } else {
        factory = ItemFactory.CASH_OVERALL;
    }

    Connection con = DatabaseConnection.getConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        ps = con.prepareStatement("SELECT `nxCredit`, `maplePoint`, `nxPrepaid` FROM `accounts` WHERE `id` = ?");
        ps.setInt(1, accountId);
        rs = ps.executeQuery();

        if (rs.next()) {
            this.nxCredit = rs.getInt("nxCredit");
            this.maplePoint = rs.getInt("maplePoint");
            this.nxPrepaid = rs.getInt("nxPrepaid");
        }

        rs.close();
        ps.close();

        for (Pair<Item, MapleInventoryType> item : factory.loadItems(accountId, false)) {
            inventory.add(item.getLeft());
        }

        ps = con.prepareStatement("SELECT `sn` FROM `wishlists` WHERE `charid` = ?");
        ps.setInt(1, characterId);
        rs = ps.executeQuery();

        while (rs.next()) {
            wishList.add(rs.getInt("sn"));
        }

        rs.close();
        ps.close();
        con.close();
    } finally {
        if (ps != null && !ps.isClosed()) ps.close();
        if (rs != null && !rs.isClosed()) rs.close();
        if (con != null && !con.isClosed()) con.close();
    }
}
 

@Test
public void testPreparedStatementConfig() throws SQLException {
    PreparedStatement preparedStatement = swConnection.prepareStatement("INSERT INTO test VALUES( ? , ?)", 1);
    preparedStatement.setInt(1, 1);
    preparedStatement.setString(2, "a");
    preparedStatement.getUpdateCount();
    preparedStatement.setFetchDirection(1);
    preparedStatement.getFetchDirection();
    preparedStatement.getResultSetConcurrency();
    preparedStatement.getResultSetType();
    preparedStatement.isClosed();
    preparedStatement.setPoolable(false);
    preparedStatement.isPoolable();
    preparedStatement.getWarnings();
    preparedStatement.clearWarnings();
    preparedStatement.setCursorName("test");
    preparedStatement.setMaxFieldSize(11);
    preparedStatement.getMaxFieldSize();
    preparedStatement.setMaxRows(10);
    preparedStatement.getMaxRows();
    preparedStatement.getParameterMetaData();
    preparedStatement.setEscapeProcessing(true);
    preparedStatement.setFetchSize(1);
    preparedStatement.getFetchSize();
    preparedStatement.setQueryTimeout(1);
    preparedStatement.getQueryTimeout();
    Connection connection = preparedStatement.getConnection();

    preparedStatement.execute();

    preparedStatement.getMoreResults();
    preparedStatement.getMoreResults(1);
    preparedStatement.getResultSetHoldability();
    preparedStatement.getMetaData();
    preparedStatement.getResultSet();

    preparedStatement.close();
    verify(mysqlPreparedStatement).getUpdateCount();
    verify(mysqlPreparedStatement).getMoreResults();
    verify(mysqlPreparedStatement).setFetchDirection(anyInt());
    verify(mysqlPreparedStatement).getFetchDirection();
    verify(mysqlPreparedStatement).getResultSetType();
    verify(mysqlPreparedStatement).isClosed();
    verify(mysqlPreparedStatement).setPoolable(anyBoolean());
    verify(mysqlPreparedStatement).getWarnings();
    verify(mysqlPreparedStatement).clearWarnings();
    verify(mysqlPreparedStatement).setCursorName(anyString());
    verify(mysqlPreparedStatement).setMaxFieldSize(anyInt());
    verify(mysqlPreparedStatement).getMaxFieldSize();
    verify(mysqlPreparedStatement).setMaxRows(anyInt());
    verify(mysqlPreparedStatement).getMaxRows();
    verify(mysqlPreparedStatement).setEscapeProcessing(anyBoolean());
    verify(mysqlPreparedStatement).getResultSetConcurrency();
    verify(mysqlPreparedStatement).getResultSetConcurrency();
    verify(mysqlPreparedStatement).getResultSetType();
    verify(mysqlPreparedStatement).getMetaData();
    verify(mysqlPreparedStatement).getParameterMetaData();
    verify(mysqlPreparedStatement).getMoreResults(anyInt());
    verify(mysqlPreparedStatement).setFetchSize(anyInt());
    verify(mysqlPreparedStatement).getFetchSize();
    verify(mysqlPreparedStatement).getQueryTimeout();
    verify(mysqlPreparedStatement).setQueryTimeout(anyInt());
    verify(mysqlPreparedStatement).getResultSet();
    assertThat(connection, CoreMatchers.<Connection>is(swConnection));
}
 

private static List<Pair<Integer, List<Pair<String, Integer>>>> updatePlayerRankingFromDB(int worldid) {
    List<Pair<Integer, List<Pair<String, Integer>>>> rankSystem = new ArrayList<>();
    List<Pair<String, Integer>> rankUpdate = new ArrayList<>(0);
    
    PreparedStatement ps = null;
    ResultSet rs = null;
    Connection con = null;
    try {
        con = DatabaseConnection.getConnection();
        
        String worldQuery;
        if (!YamlConfig.config.server.USE_WHOLE_SERVER_RANKING) {
            if(worldid >= 0) {
                worldQuery = (" AND `characters`.`world` = " + worldid);
            } else {
                worldQuery = (" AND `characters`.`world` >= 0 AND `characters`.`world` <= " + -worldid);
            }
        } else {
            worldQuery = (" AND `characters`.`world` >= 0 AND `characters`.`world` <= " + Math.abs(worldid));
        }
        
        ps = con.prepareStatement("SELECT `characters`.`name`, `characters`.`level`, `characters`.`world` FROM `characters` LEFT JOIN accounts ON accounts.id = characters.accountid WHERE `characters`.`gm` < 2 AND `accounts`.`banned` = '0'" + worldQuery + " ORDER BY " + (!YamlConfig.config.server.USE_WHOLE_SERVER_RANKING ? "world, " : "") + "level DESC, exp DESC, lastExpGainTime ASC LIMIT 50");
        rs = ps.executeQuery();
        
        if (!YamlConfig.config.server.USE_WHOLE_SERVER_RANKING) {
            int currentWorld = -1;
            while(rs.next()) {
                int rsWorld = rs.getInt("world");
                if(currentWorld < rsWorld) {
                    currentWorld = rsWorld;
                    rankUpdate = new ArrayList<>(50);
                    rankSystem.add(new Pair<>(rsWorld, rankUpdate));
                }

                rankUpdate.add(new Pair<>(rs.getString("name"), rs.getInt("level")));
            }
        } else {
            rankUpdate = new ArrayList<>(50);
            rankSystem.add(new Pair<>(0, rankUpdate));
            
            while(rs.next()) {
                rankUpdate.add(new Pair<>(rs.getString("name"), rs.getInt("level")));
            }
        }
        
        ps.close();
        rs.close();
        con.close();
    } catch(SQLException ex) {
        ex.printStackTrace();
    } finally {
        try {
            if(ps != null && !ps.isClosed()) {
                ps.close();
            }
            if(rs != null && !rs.isClosed()) {
                rs.close();
            }
            if(con != null && !con.isClosed()) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    return rankSystem;
}
 

private static void safeClose(final PreparedStatement ps) {
	// c3p0 sometimes throws a NPE on isClosed()
	try { if (ps != null && !ps.isClosed()) ps.close(); }
	catch (final Throwable e) { /* ignore */ }
}