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 void traceMarker() throws Exception {
PreparedStatement preparedStatement =
connection.prepareStatement("select * from employee where name like ?");
try {
// pull back 0 records
preparedStatement.setString(1, "nomatch%");
preparedStatement.execute();
ResultSet rs = preparedStatement.getResultSet();
rs.next();
// disable plugin and re-execute same prepared statement
services.setPluginEnabled(PLUGIN_ID, false);
preparedStatement.setString(1, "john%");
preparedStatement.execute();
// re-enable plugin and iterate over 1 record to make sure that these records are
// not attributed to the previous execution
services.setPluginEnabled(PLUGIN_ID, true);
rs = preparedStatement.getResultSet();
rs.next();
rs.next();
rs.next();
rs.next();
} finally {
preparedStatement.close();
}
}
/**
* @param conn
* @param fingerprint
* @param sampleID
* @return
* @throws SQLException
*/
private static void insertSampleSet(Connection conn, Fingerprint fingerprint, Integer sampleID)
throws SQLException {
if (fingerprint.getSampleSetID() == null) {
/*
* Insert whole new SampleSetID.
*/
String query = "INSERT INTO `SampleSets`(`SampleID`) VALUES(?);";
PreparedStatement insertSampleSet =
conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
insertSampleSet.setInt(1, sampleID);
insertSampleSet.execute();
ResultSet rs = insertSampleSet.getGeneratedKeys();
if (rs.next()) {
fingerprint.setSampleSetID(rs.getInt(1));
}
rs.close();
insertSampleSet.close();
} else {
/*
* Insert new SampleID for existing SampleSetID.
*/
String query = "INSERT INTO `SampleSets`(`SampleSetID`,`SampleID`) VALUES(?, ?);";
PreparedStatement insertSampleSet = conn.prepareStatement(query);
insertSampleSet.setInt(1, fingerprint.getSampleSetID());
insertSampleSet.setInt(2, sampleID);
insertSampleSet.execute();
insertSampleSet.close();
}
}
/**
* @param date
* @param vipType 黄钻为0，红钻为1
* @param type 活跃用户为0，新增用户为1
* @param list
* @throws SQLException
*/
private void saveRecord(Date date, int vipType, int type, int[][] list) throws SQLException {
PreparedStatement pstmt;
for (int isYearVip = 0; isYearVip < 2; isYearVip++) {
for (int vipLevel = 0; vipLevel < 8; vipLevel++) {
pstmt =
con.prepareStatement(
"delete from vip_level where date=? and type=? and is_year_vip=? and vip_level=? and vip_type=?");
pstmt.setDate(1, new java.sql.Date(date.getTime()));
pstmt.setInt(2, type);
pstmt.setInt(3, isYearVip);
pstmt.setInt(4, vipLevel);
pstmt.setInt(5, vipType);
pstmt.execute();
pstmt.close();
pstmt =
con.prepareStatement(
"insert into vip_level(date,type,is_year_vip,vip_level,vip_type,count) values(?,?,?,?,?,?)");
pstmt.setDate(1, new java.sql.Date(date.getTime()));
pstmt.setInt(2, type);
pstmt.setInt(3, isYearVip);
pstmt.setInt(4, vipLevel);
pstmt.setInt(5, vipType);
pstmt.setInt(6, list[isYearVip][vipLevel]);
pstmt.execute();
pstmt.close();
}
}
}
public void save() throws SQLException {
try (Connection connection = DbHelper.getConnection()) {
if (id == -1) {
Contact.LOGGER.debug("Adding new contact: {}", this);
final String sql = "INSERT INTO contacts(name, contacts) VALUES(?, ?)";
try (PreparedStatement pstmt =
connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
pstmt.setString(1, name);
pstmt.setString(2, contacts);
pstmt.execute();
try (final ResultSet rs = pstmt.getGeneratedKeys()) {
rs.next();
id = rs.getLong(1);
}
}
} else {
Contact.LOGGER.debug("Updating existing contact: {}", this);
final String sql = "UPDATE contacts SET name = ?, contacts = ? WHERE id = ?";
try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
pstmt.setString(1, name);
pstmt.setString(2, contacts);
pstmt.setLong(3, id);
pstmt.execute();
}
}
}
}
/** 同一个PreparedStatement先查询后更新 */
@Test
public void testExecute2() throws SQLException {
TGroupConnection conn = null;
PreparedStatement stat = null;
try {
conn = tgds.getConnection();
stat = conn.prepareStatement("select * from xxx where id=?");
stat.setByte(1, (byte) 5);
boolean res = stat.execute();
Assert.assertEquals(res, true);
res = stat.execute("update t set name = 'newName'");
Assert.assertEquals(res, false);
MockDataSource.showTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
}
}
}
}
}
@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;
}
}
public static void do_edit(String name, String num, String img_path) {
try {
Connection conn = MyConnection1.connect();
String s0 =
"update "
+ MyDB.getNames()
+ ".category set cat_name='"
+ name
+ "',img_path='"
+ img_path
+ "' where cat_num='"
+ num
+ "'";
PreparedStatement stmt = conn.prepareStatement(s0);
stmt.execute();
String s1 =
"update "
+ MyDB.getNames()
+ ".category_type set cat_name='"
+ name
+ "' where cat_num='"
+ num
+ "'";
PreparedStatement stmt1 = conn.prepareStatement(s1);
stmt1.execute();
Prompt.call("Updated Successfully");
// JOptionPane.showMessageDialog(null, "Updated Successfully");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection1.close();
}
}
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());
}
private void testSetObject(Connection conn) throws SQLException {
Statement stat = conn.createStatement();
stat.execute("CREATE TABLE TEST(C CHAR(1))");
PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?)");
prep.setObject(1, 'x');
prep.execute();
stat.execute("DROP TABLE TEST");
stat.execute("CREATE TABLE TEST(ID INT, DATA BINARY, JAVA OTHER)");
prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
prep.setInt(1, 1);
prep.setObject(2, 11);
prep.setObject(3, null);
prep.execute();
prep.setInt(1, 2);
prep.setObject(2, 101, Types.OTHER);
prep.setObject(3, 103, Types.OTHER);
prep.execute();
PreparedStatement p2 = conn.prepareStatement("SELECT * FROM TEST ORDER BY ID");
ResultSet rs = p2.executeQuery();
rs.next();
Object o = rs.getObject(2);
assertTrue(o instanceof byte[]);
assertTrue(rs.getObject(3) == null);
rs.next();
o = rs.getObject(2);
assertTrue(o instanceof byte[]);
o = rs.getObject(3);
assertTrue(o instanceof Integer);
assertEquals(103, ((Integer) o).intValue());
assertFalse(rs.next());
stat.execute("DROP TABLE TEST");
}
public void save(Proj project, String rval, String oval) throws SQLException {
if (!rval.equals(project._ResultComment)) {
String sql =
"update [PROD_UPG_DATA].[dbo].[RES_ATTACHMENTS] set [mycomment] = '"
+ rval
+ "' where [attachmenttype] = 'COMMENTRESULT' and [ordno] = '"
+ currentSelection
+ "'";
PreparedStatement ps = con.prepareStatement(sql);
boolean b = ps.execute();
ps.close();
project._ResultComment = rval;
}
if (!oval.equals(project._OrderComment)) {
String sql =
"update [PROD_UPG_DATA].[dbo].[RES_ATTACHMENTS] set [mycomment] = '"
+ oval
+ "' where [attachmenttype] = 'COMMENTORDER' and [ordno] = '"
+ currentSelection
+ "'";
PreparedStatement ps = con.prepareStatement(sql);
boolean b = ps.execute();
ps.close();
project._OrderComment = oval;
}
}
/**
* Initialize the DB with the apiman gateway DDL.
*
* @param connection
*/
private static void initDB(Connection connection) throws Exception {
ClassLoader cl = JdbcMetricsAccessorTest.class.getClassLoader();
URL resource = cl.getResource("ddls/apiman-gateway_h2.ddl");
try (InputStream is = resource.openStream()) {
System.out.println("=======================================");
System.out.println("Initializing database.");
DdlParser ddlParser = new DdlParser();
List<String> statements = ddlParser.parse(is);
for (String sql : statements) {
System.out.println(sql);
PreparedStatement statement = connection.prepareStatement(sql);
statement.execute();
}
System.out.println("=======================================");
}
System.out.println("--------------------------------------");
System.out.println("Adding test data to the database.");
resource = cl.getResource("JdbcMetricsAccessorTest/bulk-data.ddl");
try (InputStream is = resource.openStream()) {
DdlParser ddlParser = new DdlParser();
List<String> statements = ddlParser.parse(is);
for (String sql : statements) {
System.out.println(sql);
PreparedStatement statement = connection.prepareStatement(sql);
statement.execute();
}
}
System.out.println("--------------------------------------");
}
public static int userCount(String user) throws SQLException {
String query = "select count(id) from pages where finder=?";
System.out.print(query);
Connection j = textdisplay.DatabaseWrapper.getConnection();
PreparedStatement p = j.prepareStatement(query);
p.setString(1, user);
ResultSet rs = p.executeQuery();
rs.next();
int amount = rs.getInt(1);
query = "select * from leaders where uname=?";
p = j.prepareStatement(query);
p.setString(1, user);
rs = p.executeQuery();
if (!rs.next()) {
query = "insert into leaders (uname,count) values(?,0)";
p = j.prepareStatement(query);
p.setString(1, user);
p.execute();
}
query = "update leaders set count=? where uname=?";
p = j.prepareStatement(query);
p.setInt(1, amount);
p.setString(2, user);
p.execute();
j.close();
return amount;
}
/** This method executes create user table query on the azure SQL database. */
@Override
public void createUserTable() throws Exception {
Connection connection = null;
Connection connection1 = null;
PreparedStatement preparedStatement = null;
PreparedStatement preparedStatement1 = null;
try {
connection = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
preparedStatement = connection.prepareStatement(AzureChatSQLConstants.CREATE_USER_TABLE);
preparedStatement.execute();
connection1 = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
preparedStatement1 =
connection1.prepareStatement(AzureChatSQLConstants.CREATE_USER_TABLE_INDEX);
preparedStatement1.execute();
} catch (Exception e) {
LOGGER.error(
"Exception occurred while executing create user table query on the azure SQL database. Exception Message : "
+ e.getMessage());
throw new AzureChatSystemException(
"Exception occurred while executing create user table query on the azure SQL database. Exception Message : "
+ e.getMessage());
} finally {
AzureChatUtils.closeDatabaseResources(preparedStatement, connection);
AzureChatUtils.closeDatabaseResources(preparedStatement1, connection1);
}
}
@Test
public void testUpdateWriteWriteRollbackConcurrent() throws Exception {
Connection c1 = methodWatcher.createConnection();
c1.setAutoCommit(false);
PreparedStatement ps1 =
c1.prepareStatement(
String.format("update %s.%s set col2 = ? where col1 = ?", CLASS_NAME, TABLE_NAME_1));
ps1.setInt(1, 100);
ps1.setInt(2, 1);
ps1.execute();
Connection c2 = methodWatcher.createConnection();
c2.setAutoCommit(false);
try { // catch problem with rollback
try { // catch write-write conflict
PreparedStatement ps2 =
c2.prepareStatement(
String.format(
"update %s.%s set col2 = ? where col1 = ?", CLASS_NAME, TABLE_NAME_1));
ps2.setInt(1, 1000);
ps2.setInt(2, 1);
ps2.execute();
Assert.fail("Didn't raise write-conflict exception");
} catch (Exception e) {
c2.rollback();
}
} catch (Exception e) {
Assert.fail("Unexpected exception " + e);
}
}
protected void prepareDb() {
try {
Connection conn = pds.getConnection();
String createUserTableSql = "create table Users (userId varchar(255))";
PreparedStatement st = conn.prepareStatement(createUserTableSql);
st.execute();
String createGroupTableSql =
"create table Groups (groupId varchar(255), userId varchar(255))";
st = conn.prepareStatement(createGroupTableSql);
st.execute();
// insert user rows
String insertUser = "******";
st = conn.prepareStatement(insertUser);
st.setString(1, "john");
st.execute();
// insert group rows
String insertGroup = "insert into Groups (groupId, userId) values (?, ?)";
st = conn.prepareStatement(insertGroup);
st.setString(1, "PM");
st.setString(2, "john");
st.execute();
st.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void do_delete_category_type(String num) {
try {
Connection conn = MyConnection1.connect();
String s0 = "delete from " + MyDB.getNames() + ".category_type where type_num='" + num + "'";
PreparedStatement stmt = conn.prepareStatement(s0);
stmt.execute();
String s1 =
"delete from "
+ MyDB.getNames()
+ ".inventory2_stocks_left where types_num='"
+ num
+ "'";
PreparedStatement stmt1 = conn.prepareStatement(s1);
stmt1.execute();
String s2 = "delete from " + MyDB.getNames() + ".inventory2 where types_num='" + num + "'";
PreparedStatement stmt2 = conn.prepareStatement(s2);
stmt2.execute();
Prompt.call("Updated Successfully");
// JOptionPane.showMessageDialog(null, "Updated Successfully");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
MyConnection1.close();
}
}
private void insertData() throws SQLException {
String orderSql = "INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (?, ?, ?)";
String orderItemSql =
"INSERT INTO `t_order_item` (`order_item_id`, `order_id`, `user_id`, `status`) VALUES (?, ?, ?, ?)";
for (int orderId = 1; orderId <= 4; orderId++) {
for (int userId = 1; userId <= 2; userId++) {
try (Connection connection = getShardingDataSource().getConnection()) {
PreparedStatement pstmt = connection.prepareStatement(orderSql);
pstmt.setInt(1, orderId);
pstmt.setInt(2, userId);
pstmt.setString(3, "insert");
pstmt.execute();
pstmt.close();
pstmt = connection.prepareStatement(orderItemSql);
pstmt.setInt(1, orderId);
pstmt.setInt(2, orderId);
pstmt.setInt(3, userId);
pstmt.setString(4, "insert");
pstmt.execute();
pstmt.close();
pstmt = connection.prepareStatement(orderItemSql);
pstmt.setInt(1, orderId + 4);
pstmt.setInt(2, orderId);
pstmt.setInt(3, userId);
pstmt.setString(4, "insert");
pstmt.execute();
pstmt.close();
}
}
}
}
public static void saveZutaten(Rezept r, Connection con) throws SQLException {
Set set = r.zutaten.keySet();
Iterator iter = set.iterator();
PreparedStatement stmt = null;
try {
String sql = "delete from zut2rez where rez_id = ?";
stmt = con.prepareStatement(sql);
stmt.setLong(1, r.getId());
stmt.execute();
stmt.close();
sql = "insert into zut2rez(zut_id, rez_id, menge) values(?,?,?)";
stmt = con.prepareStatement(sql);
while (iter.hasNext()) {
Long id = (Long) iter.next();
Long wert = r.zutaten.get(id);
stmt.setLong(1, id);
stmt.setLong(2, r.getId());
stmt.setLong(3, wert);
stmt.execute();
stmt.clearParameters();
}
} finally {
DbUtil.close(stmt);
}
}
// ===================================================================================
// DataSource
// ==========
public void test_DataSource_Connection_always_close() throws SQLException {
Connection conn = null;
DataSource dataSource = getDataSource();
conn = dataSource.getConnection();
try {
PreparedStatement ps = conn.prepareStatement("select * from MEMBER");
ps.execute();
ps.close();
} finally {
conn.close();
}
conn = dataSource.getConnection();
try {
PreparedStatement ps =
conn.prepareStatement("update MEMBER set MEMBER_NAME = ? where MEMBER_ID = 3");
ps.setString(1, "aaa");
ps.execute();
ps.close();
} finally {
conn.close();
}
conn = dataSource.getConnection();
try {
PreparedStatement ps = conn.prepareStatement("select * from MEMBER");
ps.execute();
ps.close();
} finally {
conn.close();
}
}
@Override
public void writeAt(long index, LogEntry logEntry) {
if (index >= this.nextIndex.get() || index < this.startIndex.get()) {
throw new IllegalArgumentException("index out of range");
}
try {
PreparedStatement ps = this.connection.prepareStatement(UPDATE_ENTRY_SQL);
ps.setLong(1, logEntry.getTerm());
ps.setByte(2, logEntry.getValueType().toByte());
ps.setBytes(3, logEntry.getValue());
ps.setLong(4, index);
ps.execute();
ps = this.connection.prepareStatement(TRIM_TABLE_SQL);
ps.setLong(1, index);
ps.execute();
ps = this.connection.prepareStatement(UPDATE_SEQUENCE_SQL);
ps.setLong(1, index + 1);
ps.execute();
this.connection.commit();
this.nextIndex.set(index + 1);
this.lastEntry = logEntry;
} catch (Throwable error) {
this.logger.error("failed to write an entry at a specific index", error);
throw new RuntimeException("log store error", error);
}
}
public static void main(String[] args) throws SQLException {
try (Connection con = new MeuPoolConection().getConnection()) {
// buscar os dados
String sqlLeitura = "select * from leitura where leitura_cod = ?";
int[] codigos = new int[] {148, 119, 117, 137, 146};
for (int i : codigos) {
try (PreparedStatement stmp =
con.prepareStatement(sqlLeitura, Statement.RETURN_GENERATED_KEYS)) {
stmp.setInt(1, i);
stmp.execute();
ResultSet res = stmp.getResultSet();
while (res.next()) {
// insere as leituras dos equipamentos
String sqlInsert = "INSERT INTO leitura_eq(RMS,codigo) VALUES(?,?)";
try (PreparedStatement stmpInsert =
con.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS)) {
stmpInsert.setDouble(1, res.getDouble(4));
stmpInsert.setInt(2, 0);
stmpInsert.execute();
System.out.println(stmpInsert.getGeneratedKeys());
}
}
res.close();
stmp.close();
}
}
}
// inserir os dados
}
@RequestMapping("/tabelas")
public String criaBanco() throws SQLException {
Connection c = new ConnectionFactory().getConnection();
PreparedStatement st1 = c.prepareStatement("drop table contas if exists");
st1.execute();
PreparedStatement st11 =
c.prepareStatement(
"create table contas (id int identity, descricao varchar(255), valor double, paga boolean, dataPagamento datetime, tipo varchar(20))");
st11.execute();
PreparedStatement st2 = c.prepareStatement("drop table usuarios if exists");
st2.execute();
PreparedStatement st22 =
c.prepareStatement("create table usuarios (login VARCHAR(255),senha VARCHAR(255));");
st22.execute();
PreparedStatement st3 =
c.prepareStatement("insert into usuarios (login, senha) values ('caelum', 'online');");
st3.execute();
c.close();
return "infra-ok";
}
public void generateTag() throws Exception {
String sql = "replace into tag(node_id,name,weight) values(?,?,?);";
Connection connection = MysqlInfo.getMysqlConnection();
PreparedStatement stmt = connection.prepareStatement(sql);
char init = 'A' - 1;
for (int i = 0; i <= 25; i++) {
init++;
String typeName = "type" + init;
int node_id = i;
char subInit = 'a' - 1;
int weight = (int) (Math.random() * 10);
stmt.setString(1, Integer.toString(node_id));
stmt.setString(2, typeName);
stmt.setInt(3, weight);
stmt.execute();
for (int j = 0; j <= 10; j++) {
subInit++;
String subType = typeName + subInit;
String node = i + "." + j;
weight = (int) (Math.random() * 10);
stmt.setString(1, node);
stmt.setString(2, subType);
stmt.setInt(3, weight);
stmt.execute();
}
}
stmt.close();
connection.close();
}
public void deleteUser(String username) {
if (isReadOnly()) {
// Reject the operation since the provider is read-only
throw new UnsupportedOperationException();
}
Connection con = null;
PreparedStatement pstmt = null;
boolean abortTransaction = false;
try {
// Delete all of the users's extended properties
con = DbConnectionManager.getTransactionConnection();
pstmt = con.prepareStatement(DELETE_USER_PROPS);
pstmt.setString(1, username);
pstmt.execute();
pstmt.close();
// Delete the actual user entry
pstmt = con.prepareStatement(DELETE_USER);
pstmt.setString(1, username);
pstmt.execute();
} catch (Exception e) {
Log.error(e);
abortTransaction = true;
} finally {
DbConnectionManager.closeTransactionConnection(pstmt, con, abortTransaction);
}
}
@Test
public void testTxnClosedCorrecty() throws Exception {
String selectSql = "SELECT * FROM " + FULL_TABLE_NAME;
try (Connection conn = DriverManager.getConnection(getUrl())) {
conn.setAutoCommit(false);
ResultSet rs = conn.createStatement().executeQuery(selectSql);
assertFalse(rs.next());
String upsert =
"UPSERT INTO "
+ FULL_TABLE_NAME
+ "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(upsert);
// upsert two rows
TestUtil.setRowKeyColumns(stmt, 1);
stmt.execute();
TestUtil.setRowKeyColumns(stmt, 2);
stmt.execute();
// verify rows can be read even though commit has not been called
rs = conn.createStatement().executeQuery(selectSql);
TestUtil.validateRowKeyColumns(rs, 1);
TestUtil.validateRowKeyColumns(rs, 2);
assertFalse(rs.next());
conn.close();
// wait for any open txns to time out
Thread.sleep(DEFAULT_TXN_TIMEOUT_SECONDS * 1000 + 10000);
assertTrue("There should be no invalid transactions", txManager.getInvalidSize() == 0);
}
}
/**
* Method updateOwnerInDB.
*
* @param clan Clan
*/
private void updateOwnerInDB(Clan clan) {
_owner = clan;
Connection con = null;
PreparedStatement statement = null;
try {
con = DatabaseFactory.getInstance().getConnection();
statement =
con.prepareStatement("UPDATE clan_data SET hasFortress=0 WHERE hasFortress=? LIMIT 1");
statement.setInt(1, getId());
statement.execute();
DbUtils.close(statement);
if (clan != null) {
statement =
con.prepareStatement("UPDATE clan_data SET hasFortress=? WHERE clan_id=? LIMIT 1");
statement.setInt(1, getId());
statement.setInt(2, getOwnerId());
statement.execute();
clan.broadcastClanStatus(true, false, false);
}
} catch (Exception e) {
_log.error("", e);
} finally {
DbUtils.closeQuietly(con, statement);
}
}
@Test
public void testReadOwnWrites() throws Exception {
String selectSql = "SELECT * FROM " + FULL_TABLE_NAME;
try (Connection conn = DriverManager.getConnection(getUrl())) {
conn.setAutoCommit(false);
ResultSet rs = conn.createStatement().executeQuery(selectSql);
assertFalse(rs.next());
String upsert =
"UPSERT INTO "
+ FULL_TABLE_NAME
+ "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(upsert);
// upsert two rows
TestUtil.setRowKeyColumns(stmt, 1);
stmt.execute();
TestUtil.setRowKeyColumns(stmt, 2);
stmt.execute();
// verify rows can be read even though commit has not been called
rs = conn.createStatement().executeQuery(selectSql);
TestUtil.validateRowKeyColumns(rs, 1);
TestUtil.validateRowKeyColumns(rs, 2);
assertFalse(rs.next());
conn.commit();
// verify rows can be read after commit
rs = conn.createStatement().executeQuery(selectSql);
TestUtil.validateRowKeyColumns(rs, 1);
TestUtil.validateRowKeyColumns(rs, 2);
assertFalse(rs.next());
}
}
/**
* Method called by the Form panel to delete existing data.
*
* @param persistentObject value object to delete
* @return an ErrorResponse value object in case of errors, VOResponse if the operation is
* successfully completed
*/
public Response deleteRecord(ValueObject persistentObject) throws Exception {
PreparedStatement stmt = null;
try {
EmpVO vo = (EmpVO) persistentObject;
// delete from WORKING_DAYS...
stmt = conn.prepareStatement("delete from WORKING_DAYS where EMP_CODE=?");
stmt.setString(1, vo.getEmpCode());
stmt.execute();
stmt.close();
// delete from EMP...
stmt = conn.prepareStatement("delete from EMP where EMP_CODE=?");
stmt.setString(1, vo.getEmpCode());
stmt.execute();
gridFrame.reloadData();
frame.getGrid().clearData();
return new VOResponse(vo);
} catch (SQLException ex) {
ex.printStackTrace();
return new ErrorResponse(ex.getMessage());
} finally {
try {
stmt.close();
conn.commit();
} catch (SQLException ex1) {
}
}
}
protected void storeResource(HttpServletRequest req, HttpServletResponse res)
throws SQLException {
String data = req.getParameter("data");
PreparedStatement stmt = database.prepareStatement("SELECT ID FROM sites WHERE Name = ?");
stmt.setString(1, this.currentResource);
if (stmt.executeQuery().next()) {
PreparedStatement store =
database.prepareStatement("UPDATE sites SET Site = ? WHERE Name = ?");
store.setString(1, data);
store.setString(2, this.currentResource);
store.execute();
} else {
PreparedStatement store =
database.prepareStatement("INSERT INTO sites (Name, Site) VALUES (?, ?)");
store.setString(2, data);
store.setString(1, this.currentResource);
store.execute();
}
}