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(); } }