public static void createColumnIfNotExist(Connection conn) throws SQLException { ResultSetMetaData metaData = conn.createStatement().executeQuery("SELECT * FROM STAGES").getMetaData(); for (int i = 1; i <= metaData.getColumnCount(); i++) { String columnName = metaData.getColumnName(i); if (columnName.toLowerCase().equals("artifactsdeleted")) { return; } } conn.createStatement().executeUpdate("ALTER TABLE STAGES ADD COLUMN `ARTIFACTSDELETED` Boolean DEFAULT FALSE NOT NULL"); } }
Statement stmt = db.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); numero = stmt.executeUpdate(); ResultSet rs = stmt.getGeneratedKeys(); if (rs.next()){ risultato=rs.getInt(1); }
public static void main(String[] args) throws Exception { Class.forName("org.sqlite.JDBC"); Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db"); Statement stat = conn.createStatement(); stat.executeUpdate("drop table if exists people;"); stat.executeUpdate("create table people (name, occupation);"); PreparedStatement prep = conn.prepareStatement( "insert into people values (?, ?);"); conn.setAutoCommit(true); ResultSet rs = stat.executeQuery("select * from people;"); while (rs.next()) { System.out.println("name = " + rs.getString("name")); System.out.println("job = " + rs.getString("occupation")); conn.close();
public static void falsePositive(String url, String[] schemas) { Connection conn = null; Statement stm = null; try { conn = DriverManager.getConnection(url); stm = conn.createStatement(); for (String schema : schemas) { String sql = "create schema " + schema + ";"; stm.executeUpdate(sql); } } catch (SQLException e) { log("exception", e); } finally { if (stm != null) try { stm.close(); } catch (SQLException e1) { log("exception", e1); } if (null != conn) { try { conn.close(); } catch (SQLException e) { log("exception", e); } } } }
static public void loadTestData2Database(Connection con, int nrOfPersons, int nrOfProducts, int nrOfRels) throws ClassNotFoundException, SQLException { System.out.println(createRandomName()); System.out.println(createRandomName()); System.out.println(createRandomName()); final Statement st = con.createStatement(); // tables may not exist, this is not serious problem. try { st.executeUpdate(dropPersons); } catch (final Exception ignored) { } try { st.executeUpdate(dropProducts); } catch (final Exception ignored) { } try { st.executeUpdate(dropRelationships); } catch (final Exception ignored) { } st.executeUpdate(createPersons); st.executeUpdate(createProducts); st.executeUpdate(createRelationships); for (int i = 0; i < nrOfPersons; i++) loadPersons(st, i); for (int i = 0; i < nrOfProducts; i++) loadProducts(st, i); for (int i = 0; i < nrOfRels; i++) loadRelationships(st, i); st.close(); }
try { Statement stmt = conn.createStatement(); stmt.executeUpdate("INSERT INTO Customer VALUES" + "('101', 'Downtown Cafe', '2004-01-29', '17 N. Main Street'," + " 'Asheville', 'NC', '55515')"); stmt.executeUpdate("INSERT INTO Customer VALUES" + "('102', 'Main Street Grocery', '2005-02-10'," + " '110 E. Main Street'," + " 'Canton', 'NC', '55555')"); stmt.executeUpdate("INSERT INTO Customer VALUES" + "('103', 'The Coffee Place', '2006-08-31', '101 Center Plaza'," + " 'Waynesville', 'NC', '55516')"); } catch (SQLException ex) { System.out.println("ERROR: " + ex.getMessage());
/** * This method is used to delete all rows from the timestamped table created by createTable() in * CacheUtils class. */ public int deleteRows(String tableName) throws NamingException, SQLException { Context ctx = cache.getJNDIContext(); DataSource da = (DataSource) ctx.lookup("java:/SimpleDataSource"); // doesn't req txn Connection conn = da.getConnection(); Statement stmt = conn.createStatement(); int rowsDeleted = 0; // assume that rows are always inserted in CacheUtils String sql = ""; sql = "select * from " + tableName; ResultSet rs = stmt.executeQuery(sql); if (rs.next()) { sql = "delete from " + tableName; rowsDeleted = stmt.executeUpdate(sql); } rs.close(); stmt.close(); conn.close(); return rowsDeleted; }
private static void performTransfer(Statement statSource, String urlTarget, String user, String password, String serverList) throws SQLException { // Delete the target database first. try (Connection connTarget = DriverManager.getConnection(urlTarget + ";CLUSTER=''", user, password); Statement statTarget = connTarget.createStatement()) { statTarget.execute("DROP ALL OBJECTS DELETE FILES"); } try (PipedReader pipeReader = new PipedReader()) { Future<?> threadFuture = startWriter(pipeReader, statSource); // Read data from pipe reader, restore on target. try (Connection connTarget = DriverManager.getConnection(urlTarget, user, password); Statement statTarget = connTarget.createStatement()) { RunScript.execute(connTarget, pipeReader); // Check if the writer encountered any exception try { threadFuture.get(); } catch (ExecutionException ex) { throw new SQLException(ex.getCause()); } catch (InterruptedException ex) { throw new SQLException(ex); } // set the cluster to the serverList on both databases statSource.executeUpdate("SET CLUSTER '" + serverList + "'"); statTarget.executeUpdate("SET CLUSTER '" + serverList + "'"); } } catch (IOException ex) { throw new SQLException(ex); } }
/** * Carry out an UPDATE SQL command on the database. * * @param query * the UPDATE SQL command to carry out * @throws SQLException * if there is a problem executing the update. */ private synchronized void update(String query) throws SQLException { Statement st = connection.createStatement(); int ok = st.executeUpdate(query); if (ok == -1) { throw new SQLException("DB problem with query: " + query); } st.close(); }
connection = DriverManager.getConnection("jdbc:hsqldb:" + cacheFile.toURI().toString(), "sa", ""); boolean mustCreateTable = false; if (clearCache) { Statement st = connection.createStatement(); st.executeUpdate("DROP TABLE MARYCACHE IF EXISTS"); st.close(); mustCreateTable = true; } else { // don't clear -- check if table exists DatabaseMetaData dbInfo = connection.getMetaData(); ResultSet rs = dbInfo.getTables(null, null, "MARYCACHE", new String[] { "TABLE" }); if (rs.next()) {
@Override public int executeUpdate(final String sql, final int autoGeneratedKeys) throws SQLException { clearPrevious(); int result = 0; for (String each : masterSlaveRouter.route(sql)) { Statement statement = connection.getConnection(each).createStatement(resultSetType, resultSetConcurrency, resultSetHoldability); routedStatements.add(statement); result += statement.executeUpdate(sql, autoGeneratedKeys); } return result; }
long generateCompactionQueueId(Statement stmt) throws SQLException, MetaException { // Get the id for the next entry in the queue String s = sqlGenerator.addForUpdateClause("select ncq_next from NEXT_COMPACTION_QUEUE_ID"); LOG.debug("going to execute query <" + s + ">"); try (ResultSet rs = stmt.executeQuery(s)) { if (!rs.next()) { throw new IllegalStateException("Transaction tables not properly initiated, " + "no record found in next_compaction_queue_id"); } long id = rs.getLong(1); s = "update NEXT_COMPACTION_QUEUE_ID set ncq_next = " + (id + 1); LOG.debug("Going to execute update <" + s + ">"); stmt.executeUpdate(s); return id; } }
@Test public void testSetupDatabase() throws SQLException { HikariConfig hikariConfig = new HikariConfig(); hikariConfig.setJdbcUrl(mssqlServer.getJdbcUrl()); hikariConfig.setUsername(mssqlServer.getUsername()); hikariConfig.setPassword(mssqlServer.getPassword()); HikariDataSource ds = new HikariDataSource(hikariConfig); Statement statement = ds.getConnection().createStatement(); statement.executeUpdate("CREATE DATABASE [test];"); statement = ds.getConnection().createStatement(); statement.executeUpdate("CREATE TABLE [test].[dbo].[Foo](ID INT PRIMARY KEY);"); statement = ds.getConnection().createStatement(); statement.executeUpdate("INSERT INTO [test].[dbo].[Foo] (ID) VALUES (3);"); statement = ds.getConnection().createStatement(); statement.execute("SELECT * FROM [test].[dbo].[Foo];"); ResultSet resultSet = statement.getResultSet(); resultSet.next(); int resultSetInt = resultSet.getInt("ID"); assertEquals("A basic SELECT query succeeds", 3, resultSetInt); } }
@Override public Savepoint setSavepoint(String name) throws SQLException { checkClosed(); if (getAutoCommit()) { throw new PSQLException(GT.tr("Cannot establish a savepoint in auto-commit mode."), PSQLState.NO_ACTIVE_SQL_TRANSACTION); } PSQLSavepoint savepoint = new PSQLSavepoint(name); // Note we can't use execSQLUpdate because we don't want // to suppress BEGIN. Statement stmt = createStatement(); stmt.executeUpdate("SAVEPOINT " + savepoint.getPGName()); stmt.close(); return savepoint; }
@Override public Connection getConnection() throws SQLException { Connection connection = super.getConnection(); try(Statement statement = connection.createStatement()) { if ( dbName == null ) { try(ResultSet rs = statement.executeQuery( "SELECT DB_NAME()" )) { rs.next(); dbName = rs.getString( 1 ); } } statement.executeUpdate(String.format( RCS, dbName, "ON" )); statement.executeUpdate(String.format( SI, dbName, "ON" )); } catch (SQLException se) { fail( se.getMessage()); } return connection; } }
@Test public void testSqlUpdateWithThreadConnection() throws Exception { final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4"; int rowsAffected = 33; given(this.statement.executeUpdate(sql)).willReturn(rowsAffected); given(this.connection.createStatement()).willReturn(this.statement); int actualRowsAffected = this.template.update(sql); assertTrue("Actual rows affected is correct", actualRowsAffected == rowsAffected); verify(this.statement).close(); verify(this.connection).close(); }
@Test public void testConvertToBytes() throws ClassNotFoundException, SQLException, IOException { final Statement st = con.createStatement(); st.executeUpdate("insert into restaurants values (1, 'Irifunes', 'San Mateo')"); st.executeUpdate("insert into restaurants values (2, 'Estradas', 'Daly City')"); st.executeUpdate("insert into restaurants values (3, 'Prime Rib House', 'San Francisco')"); final ResultSet resultSet = st.executeQuery("select R.*, ROW_NUMBER() OVER () as rownr from restaurants R"); final ByteArrayOutputStream outStream = new ByteArrayOutputStream(); JdbcCommon.convertToAvroStream(resultSet, outStream, false); final byte[] serializedBytes = outStream.toByteArray(); assertNotNull(serializedBytes); System.out.println("Avro serialized result size in bytes: " + serializedBytes.length); st.close(); // Deserialize bytes to records final InputStream instream = new ByteArrayInputStream(serializedBytes); final DatumReader<GenericRecord> datumReader = new GenericDatumReader<>(); try (final DataFileStream<GenericRecord> dataFileReader = new DataFileStream<>(instream, datumReader)) { GenericRecord record = null; while (dataFileReader.hasNext()) { // Reuse record object by passing it to next(). This saves us from // allocating and garbage collecting many objects for files with // many items. record = dataFileReader.next(record); System.out.println(record); } } }
throw new HiveMetaException("Asked to update catalog " + catName + " but not given any changes to update"); System.out.println("Updating catalog " + catName); try { conn.setAutoCommit(false); try (Statement stmt = conn.createStatement()) { Object updateLocation = location == null ? schemaTool.quote("<q>LOCATION_URI<q>") : "'" + location + "'"; Object updateDescription = description == null ? schemaTool.quote("<qa>DESC<qa>") : "'" + description + "'"; catName); LOG.debug("Going to run " + update); int count = stmt.executeUpdate(update); if (count != 1) { throw new HiveMetaException("Failed to find catalog " + catName + " to update");
@Test public void testHsqlMaxValueIncrementer() throws SQLException { given(dataSource.getConnection()).willReturn(connection); given(connection.createStatement()).willReturn(statement); given(statement.executeQuery("select max(identity()) from myseq")).willReturn(resultSet); given(resultSet.next()).willReturn(true); given(resultSet.getLong(1)).willReturn(0L, 1L, 2L, 3L, 4L, 5L); HsqlMaxValueIncrementer incrementer = new HsqlMaxValueIncrementer(); incrementer.setDataSource(dataSource); incrementer.setIncrementerName("myseq"); incrementer.setColumnName("seq"); incrementer.setCacheSize(3); incrementer.setPaddingLength(3); incrementer.afterPropertiesSet(); assertEquals(0, incrementer.nextIntValue()); assertEquals(1, incrementer.nextLongValue()); assertEquals("002", incrementer.nextStringValue()); assertEquals(3, incrementer.nextIntValue()); assertEquals(4, incrementer.nextLongValue()); verify(statement, times(6)).executeUpdate("insert into myseq values(null)"); verify(statement).executeUpdate("delete from myseq where seq < 2"); verify(statement).executeUpdate("delete from myseq where seq < 5"); verify(resultSet, times(6)).close(); verify(statement, times(2)).close(); verify(connection, times(2)).close(); }
/** {@inheritDoc} */ @Override protected void beforeTest() throws Exception { try (Connection conn = DriverManager.getConnection(URL)) { conn.createStatement().executeUpdate("DROP TABLE IF EXISTS Table1"); conn.createStatement().executeUpdate("DROP TABLE IF EXISTS Table2"); conn.createStatement().executeUpdate("CREATE TABLE Table1 (id INT AUTO_INCREMENT, key INT, value INT)"); conn.createStatement().executeUpdate("CREATE TABLE Table2 (id INT AUTO_INCREMENT, key INT, value INT)"); } loadCacheCnt.set(0); loadCnt.set(0); writeCnt.set(0); deleteCnt.set(0); reuseCnt.set(0); write.set(false); fail.set(false); }