public void testBoolean() throws Throwable {
// String crtab = "create table #testBigInt (a bigint)";
String crtab = "create table #testBit (a BIT NULL)";
dropTable("#testBit");
Statement stmt = con.createStatement();
stmt.executeUpdate(crtab);
stmt.executeUpdate("insert into #testBit values (NULL)");
stmt.executeUpdate("insert into #testBit values (0)");
stmt.executeUpdate("insert into #testBit values (1)");
ResultSet rs = stmt.executeQuery("select * from #testBit where a is NULL");
rs.next();
rs.getBoolean(1);
rs = stmt.executeQuery("select * from #testBit where a = 0");
rs.next();
rs.getBoolean(1);
rs = stmt.executeQuery("select * from #testBit where a = 1");
rs.next();
rs.getBoolean(1);
stmt.close();
PreparedStatement pstmt = con.prepareStatement("insert into #testBit values (?)");
pstmt.setBoolean(1, true);
assertTrue(!pstmt.execute());
assertTrue(pstmt.getUpdateCount() == 1);
pstmt.setBoolean(1, false);
assertTrue(!pstmt.execute());
assertTrue(pstmt.getUpdateCount() == 1);
pstmt.setNull(1, java.sql.Types.BIT);
assertTrue(!pstmt.execute());
assertTrue(pstmt.getUpdateCount() == 1);
pstmt.close();
}
@Override
public boolean insereVenda(Venda venda) {
PreparedStatement stmt;
ResultSet rs;
Statement st;
try {
stmt =
this.con.prepareStatement(
""
+ " INSERT INTO `imobiliaria`.`venda`"
+ "(`id`,"
+ " `idPessoaProprietario`,"
+ " `idImovel`,"
+ " `valor`)"
+ "VALUES (?,"
+ "?,"
+ "?,"
+ "?);");
stmt.setInt(1, venda.getIdVenda());
stmt.setInt(2, venda.getIdPessoaProprietario());
stmt.setInt(3, venda.getIdImovel());
stmt.setFloat(4, venda.getValorVenda());
stmt.execute();
if (stmt.getUpdateCount() > 0) { // se gravou a venda entra para atualziar o imovel
// atualiza imóvel para vendido
stmt =
this.con.prepareStatement(
""
+ "UPDATE `imobiliaria`.`imoveln`"
+ " SET `vendido` = 1" // Vendido = 1, Não Vendido = 0;
+ " WHERE `id` = ?;");
stmt.setInt(1, venda.getIdImovel());
stmt.execute();
if (stmt.getUpdateCount() > 0) { // se atualizar o imovel retorna true
return true;
} else {
return false; // senão atualizar o imovel retorna false
}
} else {
return false; // se não gravar a venda retorna false e não entra para atualizar o imovel
}
} catch (SQLException ex) {
Logger.getLogger(ControladorIncluirBanco.class.getName()).log(Level.SEVERE, null, ex);
Mensagens erro = new Mensagens();
erro.jopError(
"Erro ao gravar dados no servidor de banco de dados:\nSQLException: "
+ ex.getMessage()
+ "\n insereVenda");
return false;
}
}
private void _runPreparedStatement(Connection conn, DaoStatement st, Object[][] paramMatrix)
throws SQLException {
ValueAdaptor[] adaptors = st.getAdaptors();
if (adaptors.length != paramMatrix[0].length)
throw Lang.makeThrow("DaoStatement adaptor MUST same width with param matrix.");
boolean statIsClosed = false;
String sql = st.toPreparedStatement();
PreparedStatement pstat = null;
// 打印调试信息
if (log.isDebugEnabled()) log.debug(st);
try {
// 创建 SQL 语句
pstat = conn.prepareStatement(sql);
// 就一条记录，不要批了吧
if (paramMatrix.length == 1) {
for (int i = 0; i < paramMatrix[0].length; i++) {
adaptors[i].set(pstat, paramMatrix[0][i], i + 1);
}
pstat.execute();
st.getContext().setUpdateCount(pstat.getUpdateCount());
pstat.close();
statIsClosed = true;
}
// 恩，批
else {
for (Object[] params : paramMatrix) {
for (int i = 0; i < params.length; i++) {
adaptors[i].set(pstat, params[i], i + 1);
}
pstat.addBatch(); // 需要配置一下batchSize,嘻嘻,不然分分钟爆内存!!
}
int[] counts = pstat.executeBatch();
// 计算总共影响的行数
int sum = 0;
for (int i : counts) if (i > 0) sum += i;
if (sum == 0) sum = pstat.getUpdateCount();
pstat.close();
statIsClosed = true;
st.getContext().setUpdateCount(sum);
}
} finally {
if (!statIsClosed) Daos.safeClose(pstat);
}
// 打印更详细的调试信息
if (log.isTraceEnabled()) log.trace("...DONE");
}
public int runExecute() throws SQLException {
if (!type.isOkForExecute()) {
throw new IllegalArgumentException("Cannot call execute on a " + type + " statement");
}
preparedStatement.execute();
return preparedStatement.getUpdateCount();
}
private static boolean changeCharAccessLevel(
L2PcInstance targetPlayer, String player, L2PcInstance activeChar, int lvl) {
if (targetPlayer != null) {
targetPlayer.setAccessLevel(lvl);
targetPlayer.logout();
activeChar.sendMessage(targetPlayer.getName() + " has been banned.");
} else {
try (Connection con = L2DatabaseFactory.getInstance().getConnection()) {
PreparedStatement statement =
con.prepareStatement("UPDATE characters SET accesslevel=? WHERE char_name=?");
statement.setInt(1, lvl);
statement.setString(2, player);
statement.execute();
int count = statement.getUpdateCount();
statement.close();
if (count == 0) {
activeChar.sendMessage("Character not found or access level unaltered.");
return false;
}
activeChar.sendMessage(player + " now has an access level of " + lvl + ".");
} catch (SQLException se) {
activeChar.sendMessage("SQLException while changing character's access level");
if (Config.DEBUG) se.printStackTrace();
return false;
}
}
return true;
}
private static void jailOfflinePlayer(L2PcInstance activeChar, String name, int delay) {
try (Connection con = L2DatabaseFactory.getInstance().getConnection()) {
PreparedStatement statement =
con.prepareStatement(
"UPDATE characters SET x=?, y=?, z=?, punish_level=?, punish_timer=? WHERE char_name=?");
statement.setInt(1, -114356);
statement.setInt(2, -249645);
statement.setInt(3, -2984);
statement.setInt(4, L2PcInstance.PunishLevel.JAIL.value());
statement.setLong(5, (delay > 0 ? delay * 60000L : 0));
statement.setString(6, name);
statement.execute();
int count = statement.getUpdateCount();
statement.close();
if (count == 0) activeChar.sendMessage("Character not found!");
else
activeChar.sendMessage(
name + " have been jailed for " + (delay > 0 ? delay + " minutes." : "ever!"));
} catch (SQLException se) {
activeChar.sendMessage("SQLException while jailing player");
if (Config.DEBUG) se.printStackTrace();
}
}
private static void banChatOfflinePlayer(
L2PcInstance activeChar, String name, int delay, boolean ban) {
int level = 0;
long value = 0;
if (ban) {
level = L2PcInstance.PunishLevel.CHAT.value();
value = (delay > 0 ? delay * 60000L : 60000);
} else {
level = L2PcInstance.PunishLevel.NONE.value();
value = 0;
}
try (Connection con = L2DatabaseFactory.getInstance().getConnection()) {
PreparedStatement statement =
con.prepareStatement(
"UPDATE characters SET punish_level=?, punish_timer=? WHERE char_name=?");
statement.setInt(1, level);
statement.setLong(2, value);
statement.setString(3, name);
statement.execute();
int count = statement.getUpdateCount();
statement.close();
if (count == 0) activeChar.sendMessage("Character isn't found.");
else if (ban)
activeChar.sendMessage(
name + " is chat banned for " + (delay > 0 ? delay + " minutes." : "ever !"));
else activeChar.sendMessage(name + "'s chat ban have been lifted.");
} catch (SQLException se) {
activeChar.sendMessage("SQLException while chat-banning player");
if (Config.DEBUG) se.printStackTrace();
}
}
public void deleteTSecGroupByTenantVisIdx(
CFSecurityAuthorization Authorization, long TenantId, boolean IsVisible) {
final String S_ProcName = "deleteTSecGroupByTenantVisIdx";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
String sql = "exec sp_delete_tsecgrp_by_tenantvisidx ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?";
if (stmtDeleteByTenantVisIdx == null) {
stmtDeleteByTenantVisIdx = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByTenantVisIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByTenantVisIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByTenantVisIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByTenantVisIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByTenantVisIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByTenantVisIdx.setLong(argIdx++, TenantId);
if (IsVisible) {
stmtDeleteByTenantVisIdx.setString(argIdx++, "Y");
} else {
stmtDeleteByTenantVisIdx.setString(argIdx++, "N");
}
Object stuff = null;
boolean moreResults = stmtDeleteByTenantVisIdx.execute();
while (stuff == null) {
try {
moreResults = stmtDeleteByTenantVisIdx.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
stuff = stmtDeleteByTenantVisIdx.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtDeleteByTenantVisIdx.getUpdateCount()) {
break;
}
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public boolean set(int sequence, String message) throws IOException {
Connection connection = null;
PreparedStatement insert = null;
ResultSet rs = null;
try {
connection = dataSource.getConnection();
insert = connection.prepareStatement(SQL_INSERT_MESSAGE);
int offset = setSessionIdParameters(insert, 1);
insert.setInt(offset++, sequence);
insert.setString(offset, message);
insert.execute();
} catch (SQLException ex) {
if (connection != null) {
PreparedStatement update = null;
try {
update = connection.prepareStatement(SQL_UPDATE_MESSAGE);
update.setString(1, message);
int offset = setSessionIdParameters(update, 2);
update.setInt(offset, sequence);
boolean status = update.execute();
return !status ? update.getUpdateCount() > 0 : false;
} catch (SQLException e) {
throw (IOException) new IOException(e.getMessage()).initCause(e);
} finally {
JdbcUtil.close(sessionID, update);
}
}
} finally {
JdbcUtil.close(sessionID, rs);
JdbcUtil.close(sessionID, insert);
JdbcUtil.close(sessionID, connection);
}
return true;
}
public void deleteContactTag(CFSecurityAuthorization Authorization, CFCrmContactTagBuff Buff) {
final String S_ProcName = "deleteContactTag";
try {
Connection cnx = schema.getCnx();
long TenantId = Buff.getRequiredTenantId();
long ContactId = Buff.getRequiredContactId();
long TagId = Buff.getRequiredTagId();
String sql =
"exec sp_delete_ctc_tag ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?";
if (stmtDeleteByPKey == null) {
stmtDeleteByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByPKey.setLong(argIdx++, TenantId);
stmtDeleteByPKey.setLong(argIdx++, ContactId);
stmtDeleteByPKey.setLong(argIdx++, TagId);
stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
;
Object stuff = null;
boolean moreResults = stmtDeleteByPKey.execute();
while (stuff == null) {
try {
moreResults = stmtDeleteByPKey.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
stuff = stmtDeleteByPKey.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtDeleteByPKey.getUpdateCount()) {
break;
}
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
}
public int getUpdateCount() throws SQLException {
Profiler profiler = _profilerPoint.start();
try {
return _preparedStatement.getUpdateCount();
} finally {
profiler.finish();
}
}
public void deleteISOTimezoneByOffsetIdx(
CFSecurityAuthorization Authorization, short argTZHourOffset, short argTZMinOffset) {
final String S_ProcName = "deleteISOTimezoneByOffsetIdx";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
String sql = "exec sp_delete_isotz_by_offsetidx ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?";
if (stmtDeleteByOffsetIdx == null) {
stmtDeleteByOffsetIdx = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByOffsetIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByOffsetIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByOffsetIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByOffsetIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByOffsetIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByOffsetIdx.setShort(argIdx++, argTZHourOffset);
stmtDeleteByOffsetIdx.setShort(argIdx++, argTZMinOffset);
Object stuff = null;
boolean moreResults = stmtDeleteByOffsetIdx.execute();
while (stuff == null) {
try {
moreResults = stmtDeleteByOffsetIdx.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
stuff = stmtDeleteByOffsetIdx.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtDeleteByOffsetIdx.getUpdateCount()) {
break;
}
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
private void testGetMoreResults(Connection conn) throws SQLException {
Statement stat = conn.createStatement();
PreparedStatement prep;
ResultSet rs;
stat.execute("CREATE TABLE TEST(ID INT)");
stat.execute("INSERT INTO TEST VALUES(1)");
prep = conn.prepareStatement("SELECT * FROM TEST");
// just to check if it doesn't throw an exception - it may be null
prep.getMetaData();
assertTrue(prep.execute());
rs = prep.getResultSet();
assertFalse(prep.getMoreResults());
assertEquals(-1, prep.getUpdateCount());
// supposed to be closed now
assertThrows(ErrorCode.OBJECT_CLOSED, rs).next();
assertEquals(-1, prep.getUpdateCount());
prep = conn.prepareStatement("UPDATE TEST SET ID = 2");
assertFalse(prep.execute());
assertEquals(1, prep.getUpdateCount());
assertFalse(prep.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
assertEquals(-1, prep.getUpdateCount());
// supposed to be closed now
assertThrows(ErrorCode.OBJECT_CLOSED, rs).next();
assertEquals(-1, prep.getUpdateCount());
prep = conn.prepareStatement("DELETE FROM TEST");
prep.executeUpdate();
assertFalse(prep.getMoreResults());
assertEquals(-1, prep.getUpdateCount());
}
public void testBigInt() throws Throwable {
// String crtab = "create table #testBigInt (a bigint)";
String crtab = "create table #testBigInt (a NUMERIC(19) NULL)";
dropTable("#testBigInt");
Statement stmt = con.createStatement();
stmt.executeUpdate(crtab);
stmt.close();
PreparedStatement pstmt = con.prepareStatement("insert into #testBigInt values (?)");
pstmt.setNull(1, java.sql.Types.BIGINT);
assertTrue(!pstmt.execute());
assertTrue(pstmt.getUpdateCount() == 1);
pstmt.setLong(1, 99999999999L);
assertTrue(!pstmt.execute());
assertTrue(pstmt.getUpdateCount() == 1);
pstmt.setLong(1, -99999999999L);
assertTrue(!pstmt.execute());
assertTrue(pstmt.getUpdateCount() == 1);
pstmt.setLong(1, 9999999999999L);
assertTrue(!pstmt.execute());
assertTrue(pstmt.getUpdateCount() == 1);
pstmt.setLong(1, -9999999999999L);
assertTrue(!pstmt.execute());
assertTrue(pstmt.getUpdateCount() == 1);
pstmt.setLong(1, 99999999999L);
assertTrue(!pstmt.execute());
assertTrue(pstmt.getUpdateCount() == 1);
pstmt.close();
}
private long executeBatch(PreparedStatement stmt) throws SQLException {
if (configuration.getUseLiterals()) {
return stmt.executeUpdate();
} else if (configuration.getTemplates().isBatchCountViaGetUpdateCount()) {
stmt.executeBatch();
return stmt.getUpdateCount();
} else {
long rv = 0;
for (int i : stmt.executeBatch()) {
rv += i;
}
return rv;
}
}
/** Local PreparedStatement insert WITHOUT bind variables */
@Test
public void testPreparedStatementInsert() throws Exception {
Connection connection = makeConnection(new ArrayList<JdbcTest.Employee>());
assertFalse(connection.isClosed());
String sql = "insert into \"foo\".\"bar\" values (1, 1, 'second', 2, 2)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
assertFalse(preparedStatement.isClosed());
boolean status = preparedStatement.execute();
assertFalse(status);
ResultSet resultSet = preparedStatement.getResultSet();
assertTrue(resultSet == null);
int updateCount = preparedStatement.getUpdateCount();
assertTrue(updateCount == 1);
}
/** Remote PreparedStatement insert WITHOUT bind variables */
@Test
public void testRemotePreparedStatementInsert() throws Exception {
final Connection connection =
DriverManager.getConnection(
"jdbc:avatica:remote:factory=" + LocalServiceModifiableFactory.class.getName());
assertThat(connection.isClosed(), is(false));
String sql = "insert into \"foo\".\"bar\" values (1, 1, 'second', 2, 2)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
assertThat(preparedStatement.isClosed(), is(false));
boolean status = preparedStatement.execute();
assertThat(status, is(false));
ResultSet resultSet = preparedStatement.getResultSet();
assertThat(resultSet, nullValue());
int updateCount = preparedStatement.getUpdateCount();
assertThat(updateCount, is(1));
}
public boolean createUser(User user) {
if (findByUsername(user.username) != null) return false;
try {
PreparedStatement stmt = connection.prepareStatement(INSERT_USER);
stmt.setString(1, user.username);
stmt.setString(2, user.password);
stmt.setString(3, user.forename);
stmt.setString(4, user.surname);
stmt.setString(5, user.address);
stmt.execute();
return stmt.getUpdateCount() == 1;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public void deleteFeeDetailByIdIdx(
CFSecurityAuthorization Authorization, long TenantId, long FeeDetailId) {
final String S_ProcName = "deleteFeeDetailByIdIdx";
try {
Connection cnx = schema.getCnx();
String sql = "exec sp_delete_feedtl ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?";
if (stmtDeleteByPKey == null) {
stmtDeleteByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByPKey.setLong(argIdx++, TenantId);
stmtDeleteByPKey.setLong(argIdx++, FeeDetailId);
Object stuff = null;
boolean moreResults = stmtDeleteByPKey.execute();
while (stuff == null) {
try {
moreResults = stmtDeleteByPKey.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
stuff = stmtDeleteByPKey.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtDeleteByPKey.getUpdateCount()) {
break;
}
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
}
@Override
public boolean useAdminCommand(String command, L2PcInstance activeChar) {
String[] parts = command.split(" ");
if (parts.length == 2) {
try {
int lvl = Integer.parseInt(parts[1]);
if (activeChar.getTarget() instanceof L2PcInstance) {
onlineChange(activeChar, (L2PcInstance) activeChar.getTarget(), lvl);
} else {
activeChar.sendPacket(SystemMessageId.INCORRECT_TARGET);
}
} catch (Exception e) {
activeChar.sendMessage("Usage: //changelvl <target_new_level> | <player_name> <new_level>");
}
} else if (parts.length == 3) {
String name = parts[1];
int lvl = Integer.parseInt(parts[2]);
L2PcInstance player = L2World.getInstance().getPlayer(name);
if (player != null) {
onlineChange(activeChar, player, lvl);
} else {
try (Connection con = ConnectionFactory.getInstance().getConnection();
PreparedStatement ps =
con.prepareStatement("UPDATE characters SET accesslevel=? WHERE char_name=?")) {
ps.setInt(1, lvl);
ps.setString(2, name);
ps.execute();
if (ps.getUpdateCount() == 0) {
activeChar.sendMessage("角色不在线或者权限等级没有变化.");
} else {
activeChar.sendMessage("将角色的权限等级谁定为 " + lvl);
}
} catch (SQLException se) {
activeChar.sendMessage("角色权限等级变化时SQL异常");
if (Config.DEBUG) {
se.printStackTrace();
}
}
}
}
return true;
}
private static void unjailOfflinePlayer(L2PcInstance activeChar, String name) {
try (Connection con = L2DatabaseFactory.getInstance().getConnection()) {
PreparedStatement statement =
con.prepareStatement(
"UPDATE characters SET x=?, y=?, z=?, punish_level=?, punish_timer=? WHERE char_name=?");
statement.setInt(1, 17836);
statement.setInt(2, 170178);
statement.setInt(3, -3507);
statement.setInt(4, 0);
statement.setLong(5, 0);
statement.setString(6, name);
statement.execute();
int count = statement.getUpdateCount();
statement.close();
if (count == 0) activeChar.sendMessage("Character isn't found.");
else activeChar.sendMessage(name + " have been unjailed.");
} catch (SQLException se) {
activeChar.sendMessage("SQLException while jailing player");
if (Config.DEBUG) se.printStackTrace();
}
}
/**
* Execute an update
*
* @param statementScope - the request scope
* @param conn - the database connection
* @param sql - the sql statement to execute
* @param parameters - the parameters for the sql statement
* @return - the number of records changed
* @throws SQLException - if the update fails
*/
public int executeUpdate(
StatementScope statementScope, Connection conn, String sql, Object[] parameters)
throws SQLException {
ErrorContext errorContext = statementScope.getErrorContext();
errorContext.setActivity("executing update");
errorContext.setObjectId(sql);
PreparedStatement ps = null;
setupResultObjectFactory(statementScope);
int rows = 0;
try {
errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
ps = prepareStatement(statementScope.getSession(), conn, sql);
setStatementTimeout(statementScope.getStatement(), ps);
errorContext.setMoreInfo("Check the parameters (set parameters failed).");
statementScope.getParameterMap().setParameters(statementScope, ps, parameters);
errorContext.setMoreInfo("Check the statement (update failed).");
ps.execute();
rows = ps.getUpdateCount();
} finally {
closeStatement(statementScope.getSession(), ps);
}
return rows;
}
private static CurVO executeBatchBindSQL(String sql, List<Object> voList, Connection connect)
throws Exception {
if (voList == null || voList.size() == 0) return new CurVO();
PreparedStatement ps = null;
CurVO resultVO;
try {
// 哪些变量需要绑定
List<String> fieldNameList = SqlTool.GetSQLBindFieldNameList(sql, voList.get(0).getClass());
// 获得批量处理的绑定变量SQL
ps = connect.prepareStatement(SqlTool.GetBindSQL(sql));
// 绑定变量
for (Object vo : voList) {
// 当前SQL要绑定的所有参数
List<Object> valueList = SqlTool.GetSQLFieldValueList(vo, fieldNameList);
// 绑定
for (int i = 0; i < valueList.size(); i++)
DBTool.BindJavaValueByJavaType(ps, valueList.get(i), i + 1, connect);
/** ---------------------打印------------------------- */
P.printBindSQL(sql, vo);
/** ---------------------打印------------------------- */
// 添加到批量处理
ps.addBatch();
}
ps.executeBatch();
resultVO = new CurVO();
// 绑定变量只能获得总更新记录,无法这么处理.setBatchBesult(ps.executeBatch());
resultVO.setResult(ps.getUpdateCount());
return resultVO;
} catch (Exception e) {
throw e;
} finally {
DBTool.close(null, ps, null);
}
}
/**
* Default implementation for query execution using a prepared statement. Subclasses may override
* this method.
*/
protected int execute(ExecuteContext ctx, ExecuteListener listener) throws SQLException {
int result = 0;
PreparedStatement stmt = ctx.statement();
try {
listener.executeStart(ctx);
// [#1829] Statement.execute() is preferred over Statement.executeUpdate(), as
// we might be executing plain SQL and returning results.
if (!stmt.execute()) {
result = stmt.getUpdateCount();
ctx.rows(result);
}
listener.executeEnd(ctx);
return result;
}
// [#3011] [#3054] Consume additional exceptions if there are any
catch (SQLException e) {
consumeExceptions(ctx.configuration(), stmt, e);
throw e;
}
}
public void updateISOCountry(
CFSecurityAuthorization Authorization, CFSecurityISOCountryBuff Buff) {
final String S_ProcName = "updateISOCountry";
ResultSet resultSet = null;
try {
short Id = Buff.getRequiredId();
String ISOCode = Buff.getRequiredISOCode();
String Name = Buff.getRequiredName();
int Revision = Buff.getRequiredRevision();
Connection cnx = schema.getCnx();
String sql =
"exec sp_update_iso_cntry ?, ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?";
if (stmtUpdateByPKey == null) {
stmtUpdateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtUpdateByPKey.setString(argIdx++, "ISOC");
stmtUpdateByPKey.setShort(argIdx++, Id);
stmtUpdateByPKey.setString(argIdx++, ISOCode);
stmtUpdateByPKey.setString(argIdx++, Name);
stmtUpdateByPKey.setInt(argIdx++, Revision);
stmtUpdateByPKey.execute();
boolean moreResults = true;
resultSet = null;
while (resultSet == null) {
try {
moreResults = stmtUpdateByPKey.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
resultSet = stmtUpdateByPKey.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtUpdateByPKey.getUpdateCount()) {
break;
}
}
if (resultSet == null) {
throw CFLib.getDefaultExceptionFactory()
.newNullArgumentException(getClass(), S_ProcName, 0, "resultSet");
}
if (resultSet.next()) {
CFSecurityISOCountryBuff updatedBuff = unpackISOCountryResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
Buff.setRequiredISOCode(updatedBuff.getRequiredISOCode());
Buff.setRequiredName(updatedBuff.getRequiredName());
Buff.setRequiredRevision(updatedBuff.getRequiredRevision());
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected a single-record response, " + resultSet.getRow() + " rows selected");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public CFSecurityISOCountryBuff lockBuff(
CFSecurityAuthorization Authorization, CFSecurityISOCountryPKey PKey) {
final String S_ProcName = "lockBuff";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
short Id = PKey.getRequiredId();
String sql = "{ call sp_lock_iso_cntry( ?, ?, ?, ?, ?" + ", " + "?" + " ) }";
if (stmtLockBuffByPKey == null) {
stmtLockBuffByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtLockBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtLockBuffByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtLockBuffByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtLockBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtLockBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtLockBuffByPKey.setShort(argIdx++, Id);
stmtLockBuffByPKey.execute();
boolean moreResults = true;
resultSet = null;
while (resultSet == null) {
try {
moreResults = stmtLockBuffByPKey.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
resultSet = stmtLockBuffByPKey.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtLockBuffByPKey.getUpdateCount()) {
break;
}
}
if ((resultSet != null) && resultSet.next()) {
CFSecurityISOCountryBuff buff = unpackISOCountryResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
return (buff);
} else {
return (null);
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void createISOCurrency(
CFSecurityAuthorization Authorization, CFSecurityISOCurrencyBuff Buff) {
final String S_ProcName = "createISOCurrency";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
String ISOCode = Buff.getRequiredISOCode();
String Name = Buff.getRequiredName();
String UnitSymbol = Buff.getOptionalUnitSymbol();
short Precis = Buff.getRequiredPrecis();
Connection cnx = schema.getCnx();
String sql =
"exec sp_create_iso_ccy ?, ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?";
if (stmtCreateByPKey == null) {
stmtCreateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtCreateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtCreateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtCreateByPKey.setString(argIdx++, "ISCY");
stmtCreateByPKey.setString(argIdx++, ISOCode);
stmtCreateByPKey.setString(argIdx++, Name);
if (UnitSymbol != null) {
stmtCreateByPKey.setString(argIdx++, UnitSymbol);
} else {
stmtCreateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR);
}
stmtCreateByPKey.setShort(argIdx++, Precis);
stmtCreateByPKey.execute();
boolean moreResults = true;
resultSet = null;
while (resultSet == null) {
try {
moreResults = stmtCreateByPKey.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
resultSet = stmtCreateByPKey.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtCreateByPKey.getUpdateCount()) {
break;
}
}
if (resultSet == null) {
throw CFLib.getDefaultExceptionFactory()
.newNullArgumentException(getClass(), S_ProcName, 0, "resultSet");
}
if (resultSet.next()) {
CFSecurityISOCurrencyBuff createdBuff = unpackISOCurrencyResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
Buff.setRequiredISOCurrencyId(createdBuff.getRequiredISOCurrencyId());
Buff.setRequiredISOCode(createdBuff.getRequiredISOCode());
Buff.setRequiredName(createdBuff.getRequiredName());
Buff.setOptionalUnitSymbol(createdBuff.getOptionalUnitSymbol());
Buff.setRequiredPrecis(createdBuff.getRequiredPrecis());
Buff.setRequiredRevision(createdBuff.getRequiredRevision());
Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
Buff.setCreatedAt(createdBuff.getCreatedAt());
Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
Buff.setUpdatedAt(createdBuff.getUpdatedAt());
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected a single-record response, " + resultSet.getRow() + " rows selected");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void createHostNode(CFSecurityAuthorization Authorization, CFSecurityHostNodeBuff Buff) {
final String S_ProcName = "createHostNode";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
long ClusterId = Buff.getRequiredClusterId();
String Description = Buff.getRequiredDescription();
String HostName = Buff.getRequiredHostName();
Connection cnx = schema.getCnx();
String sql =
"exec sp_create_hostnode ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?";
if (stmtCreateByPKey == null) {
stmtCreateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtCreateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtCreateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtCreateByPKey.setString(argIdx++, "HSND");
stmtCreateByPKey.setLong(argIdx++, ClusterId);
stmtCreateByPKey.setString(argIdx++, Description);
stmtCreateByPKey.setString(argIdx++, HostName);
stmtCreateByPKey.execute();
boolean moreResults = true;
resultSet = null;
while (resultSet == null) {
try {
moreResults = stmtCreateByPKey.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
resultSet = stmtCreateByPKey.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtCreateByPKey.getUpdateCount()) {
break;
}
}
if (resultSet == null) {
throw CFLib.getDefaultExceptionFactory()
.newNullArgumentException(getClass(), S_ProcName, 0, "resultSet");
}
if (resultSet.next()) {
CFSecurityHostNodeBuff createdBuff = unpackHostNodeResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
Buff.setRequiredClusterId(createdBuff.getRequiredClusterId());
Buff.setRequiredHostNodeId(createdBuff.getRequiredHostNodeId());
Buff.setRequiredDescription(createdBuff.getRequiredDescription());
Buff.setRequiredHostName(createdBuff.getRequiredHostName());
Buff.setRequiredRevision(createdBuff.getRequiredRevision());
Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
Buff.setCreatedAt(createdBuff.getCreatedAt());
Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
Buff.setUpdatedAt(createdBuff.getUpdatedAt());
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected a single-record response, " + resultSet.getRow() + " rows selected");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
protected int getUpdateCount(PreparedStatement statement) throws SQLException {
return statement.getUpdateCount();
}
public int getUpdateCount() throws SQLException {
this.assertPs();
return ps.getUpdateCount();
}