/** * Extract the Large Object Input Stream from PostgreSQL * * @param resultSet * the Result Set to extract the blob from * @param columnIndex * the index of column * @return the Large Object Input Stream from PostgreSQL * @throws SQLException */ public static InputStream getPostgreSqlnputStream(ResultSet resultSet, int columnIndex) throws SQLException { InputStream in; Statement statement = resultSet.getStatement(); Connection conn = statement.getConnection(); // Get the Large Object Manager to perform operations with LargeObjectManager lobj = ((org.postgresql.PGConnection) conn) .getLargeObjectAPI(); long oid = resultSet.getLong(columnIndex); if (oid < 1) { return null; } LargeObject obj = lobj.open(oid, LargeObjectManager.READ); in = obj.getInputStream(); return in; }
@Test public void testAutomaticLogin() throws Exception { final String tableName = "automaticAllowedClients"; // Avatica should log in for us with this info String url = jdbcUrl + ";principal=" + SpnegoTestUtil.CLIENT_PRINCIPAL + ";keytab=" + clientKeytab; LOG.info("Updated JDBC url: {}", url); try (Connection conn = DriverManager.getConnection(url); Statement stmt = conn.createStatement()) { assertFalse(stmt.execute("DROP TABLE IF EXISTS " + tableName)); assertFalse(stmt.execute("CREATE TABLE " + tableName + "(pk integer)")); assertEquals(1, stmt.executeUpdate("INSERT INTO " + tableName + " VALUES(1)")); assertEquals(1, stmt.executeUpdate("INSERT INTO " + tableName + " VALUES(2)")); assertEquals(1, stmt.executeUpdate("INSERT INTO " + tableName + " VALUES(3)")); ResultSet results = stmt.executeQuery("SELECT count(1) FROM " + tableName); assertTrue(results.next()); assertEquals(3, results.getInt(1)); } }
public static List<ProductoUnidadMedida> selectAllProductoUnidadMedida(String condition) throws SQLException{ Connection conect=ConnectionConfiguration.conectar(); String query = " select * from producto_unidad_medida "+condition; Statement statement = null; ResultSet rs=null; List<ProductoUnidadMedida> objetos = new ArrayList<ProductoUnidadMedida>(); try { statement = conect.createStatement(); rs=statement.executeQuery(query); while(rs.next()){ ProductoUnidadMedida objeto = new ProductoUnidadMedida(); objeto.setUnidadMedidaId(rs.getInt("unidad_medida_id")); objeto.setUnidadMedidaNombre(rs.getString("uni_nombre")); objetos.add(objeto); } } catch (SQLException e) {e.printStackTrace();} finally{ if (statement != null) {statement.close();} if (conect != null) {conect.close();} } return objetos; }
@Test public void test() throws Exception { JdbcAssert .withNoDefaultSchema() .withConnection( new Function<Connection, Void>() { public Void apply( Connection connection ) { try { Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery( "USE dfs_test.tmp" ); // TODO: Purge nextUntilEnd(...) and calls when remaining fragment // race conditions are fixed (not just DRILL-2245 fixes). // resultSet.close( resultSet ); statement.close(); // connection.close() is in withConnection(...) return null; } catch ( SQLException e ) { throw new RuntimeException( e ); } } }); }
public static void deleteProyectoSnipAutorizado(String id, String entidad_id, String entidad_nivel_id, String proyecto_snip_id, String organismo_financiador_id, String fuente_financiamiento_id){ Connection conect=ConnectionConfiguration.conectar(); Statement statement = null; String query = "delete from proyecto_snip_autorizado "; //if (id!="") query+= "id=\""+id+"\", "; /*if (nombre!="") query+= "nombre=\""+nombre+"\", "; if (descripcion!="") query+= "descripcion=\""+descripcion+"\", "; if (anho!="") query+= "anho=\""+anho+"\", "; if (monto!="") query+= "monto=\""+monto+"\", "; //if (entidad_id!="") query+= "entidad_id=\""+entidad_id+"\", "; //if (entidad_nivel_id!="") query+= "entidad_nivel_id=\""+entidad_nivel_id+"\", "; //if (proyecto_snip_id!="") query+= "proyecto_snip_id=\""+proyecto_snip_id+"\", "; //if (organismo_financiador_id!="") query+= "organismo_financiador_id=\""+organismo_financiador_id+"\", "; //if (fuente_financiamiento_id!="") query+= "fuente_financiamiento_id=\""+fuente_financiamiento_id+"\", "; query = query.substring(0, query.length()-2);*/ query+="where id="+id+" and entidad_id="+entidad_id+" and entidad_nivel_id="+entidad_nivel_id+" and proyecto_snip_id="+proyecto_snip_id+" and organismo_financiador_id="+organismo_financiador_id+" and fuente_financiamiento_id="+fuente_financiamiento_id; try { statement=conect.createStatement(); statement.execute(query); conect.close(); } catch (SQLException e) {e.printStackTrace();} }
@Test public void shouldApplyMigrations() throws Exception { try (DrinkWaterApplication app = DrinkWaterApplication.create(options().use(TestMigrationConfiguration.class).autoStart())) { EmbeddedPostgresDataStore store = app.getStore("test"); store.executeNoQuery("INSERT INTO contact(id, first_name, last_name) VALUES (2 , 'Jean-Marc', 'Canon');"); try (Connection c = store.getConnection()) { Statement s = c.createStatement(); ResultSet rs = s.executeQuery("SELECT * from contact"); assertTrue(rs.next()); assertEquals(2, rs.getInt(1)); assertFalse(rs.next()); } } }
private static void deleteDynamicDeploymentSpecs(Statement stmt) throws SQLException{ // for any given VS/Region/Tenant, having both static and dynamic deployment specs, // delete the dynamic deployment spec String sql = " DELETE FROM DEPLOYMENT_SPEC ds WHERE EXISTS " + " ( " + " select * from ( " + " select ds1.ID, ds1.region, ds1.tenant_id, ds1.vs_fk, ds1.DYNAMIC, sel_cnt.countByVSRegTenant from DEPLOYMENT_SPEC AS ds1 " + " JOIN " + " ( " + " select region, tenant_id, vs_fk, count(*) as countByVSRegTenant from DEPLOYMENT_SPEC group by region, tenant_id, vs_fk " + " ) AS sel_cnt " + " ON ds1.region = sel_cnt.region and ds1.tenant_id = sel_cnt.tenant_id and ds1.vs_fk = sel_cnt.vs_fk " + " ) AS sel " + " WHERE ds.region = sel.region AND ds.tenant_id = sel.tenant_id AND ds.vs_fk = sel.vs_fk AND sel.countByVSRegTenant =2 and ds.DYNAMIC=TRUE" + " ) ; "; execSql(stmt, sql); }
private static void setFKColumnsNull(Connection conn, String user, String tableName) throws SQLException { ResultSet rs = conn.getMetaData() .getExportedKeys(null, null, tableName); Statement stmt = conn.createStatement(); while (rs.next()) { String sourceTableName = rs.getString("FKTABLE_NAME"); String columnName = rs.getString("FKCOLUMN_NAME"); ResultSet columns = conn.getMetaData().getColumns(null, user, sourceTableName, columnName); while (columns.next()) { if (columns.getInt("NULLABLE") != DatabaseMetaData.columnNoNulls) { String queryString = String.format( "UPDATE %s SET %s = NULL", sourceTableName, columnName); stmt.executeUpdate(queryString); } } } }
public void testChangeUser() throws Exception { Properties props = getPropertiesFromTestsuiteUrl(); Connection testConn = getConnectionWithProps(props); Statement testStmt = testConn.createStatement(); for (int i = 0; i < 500; i++) { ((com.mysql.jdbc.Connection) testConn).changeUser(props.getProperty(NonRegisteringDriver.USER_PROPERTY_KEY), props.getProperty(NonRegisteringDriver.PASSWORD_PROPERTY_KEY)); if (i % 10 == 0) { try { ((com.mysql.jdbc.Connection) testConn).changeUser("bubba", props.getProperty(NonRegisteringDriver.PASSWORD_PROPERTY_KEY)); } catch (SQLException sqlEx) { if (versionMeetsMinimum(5, 6, 13)) { assertTrue(testConn.isClosed()); testConn = getConnectionWithProps(props); testStmt = testConn.createStatement(); } } } this.rs = testStmt.executeQuery("SELECT 1"); } testConn.close(); }
/** * Returns true if the specified database already exists on the DBMS. * @param aDatabaseName * @return */ public boolean databaseAlreadyExists(String aDatabaseName) { aDatabaseName = aDatabaseName.toLowerCase(); try { Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery( "SELECT datname FROM pg_catalog.pg_database WHERE datname='" + aDatabaseName + "'"); if (!rs.next()) { return false; } else { return true; } } catch (SQLException e) { e.printStackTrace(); } return false; }
Connection getConnection() throws SQLException { DBHostConfig cfg = getConfig(); Connection connection = DriverManager.getConnection(cfg.getUrl(), cfg.getUser(), cfg.getPassword()); String initSql=getHostConfig().getConnectionInitSql(); if (initSql != null && !"".equals(initSql)) { Statement statement = null; try { statement = connection.createStatement(); statement.execute(initSql); } finally { if (statement != null) { statement.close(); } } } return connection; }
/** * Checks the blob type columns have the correct type * * @throws SQLException not really thrown */ @Test public void testBlobType() throws SQLException { ConnectionResourcesBean databaseConnection = new ConnectionResourcesBean(); databaseConnection.setDatabaseName("database"); databaseConnection.setDatabaseType(H2.IDENTIFIER); Connection connection = databaseConnection.getDataSource().getConnection(); try { Statement createStatement = connection.createStatement(); createStatement.execute("CREATE TABLE test ( BLOBCOL longvarbinary )"); DatabaseMetaDataProvider provider = new DatabaseMetaDataProvider(connection, null); Table table = provider.getTable("test"); assertEquals("Exactly 1 column expected", 1, table.columns().size()); assertEquals("Column name not correct", "BLOBCOL", table.columns().get(0).getName()); assertEquals("Column type not correct", DataType.BLOB, table.columns().get(0).getType()); assertEquals("Column width not correct", 2147483647, table.columns().get(0).getWidth()); assertEquals("Column scale not correct", 0, table.columns().get(0).getScale()); } finally { connection.close(); } }
/** * Executes a query containing the clause LIMIT with a Statement and a PreparedStatement, using a combination of * Connection properties, maxRows value and limit clause value, and tests if the results count is the expected. */ private void testBug71396MultiSettingsCheck(String connProps, int maxRows, int limitClause, int expRowCount) throws SQLException { Connection testConn = getConnectionWithProps(connProps); Statement testStmt = testConn.createStatement(); if (maxRows > 0) { testStmt.setMaxRows(maxRows); } testStmt.execute("SELECT 1"); // force limit to be applied into current session testBug71396StatementCheck(testStmt, String.format("SELECT * FROM testBug71396 LIMIT %d", limitClause), expRowCount); testBug71396PrepStatementCheck(testConn, String.format("SELECT * FROM testBug71396 LIMIT %d", limitClause), expRowCount, maxRows); testStmt.close(); testConn.close(); }
public static void updateUnidadMedida(String id, String nombre, String abrev, String simbolo, String descripcion) { Connection conect = ConnectionConfiguration.conectar(); Statement statement = null; String query = "update unidad_medida set "; // if (id!="") query+= "id=\""+id+"\", "; if (nombre != "") query += "nombre=\"" + nombre + "\", "; if (abrev != "") query += "abrev=\"" + abrev + "\", "; if (simbolo != "") query += "simbolo=\"" + simbolo + "\", "; if (descripcion != "") query += "descripcion=\"" + descripcion + "\", "; query = query.substring(0, query.length() - 2); query += "where id=" + id; try { statement = conect.createStatement(); statement.execute(query); conect.close(); } catch (SQLException e) { e.printStackTrace(); } }
private void initSql() { try { Connection conn = mockDataSource.getConnection(); Statement stmt = conn.createStatement(); List<String> aa = IOUtils.readLines(getClass().getClassLoader().getResourceAsStream("sqlite.sql")); String s = StringUtils.join(aa, "\r\n"); String[] sqls = s.split(";"); for (int i = 0; i < sqls.length; i++) { String sql = sqls[i]; System.out.println("初始化sql : " + sql); stmt.execute(sql); } stmt.close(); conn.close(); } catch (Exception e) { } }
public static Connection getConnection() { /* Connection connection = null; try { Class.forName("com.postgres.jdbc.Driver"); connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); } catch (Exception e) { e.printStackTrace(); } return connection;*/ Connection con = null; Statement st = null; ResultSet rs = null; try {Class.forName("org.postgresql.Driver");} catch (ClassNotFoundException e) {e.printStackTrace();} String url = ""; String user = "postgres"; String password = "postgres"; try {con = DriverManager.getConnection("jdbc:postgresql://pg01.stp.gov.py/tablero2015v3?useUnicode=true&characterEncoding=UTF-8&user=postgres&password=postgres");} catch (SQLException ex) { Logger lgr = Logger.getLogger(SqlHelper.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } return con; }
public static boolean borradoAccionHasGeoPoligono(AccionHasGeoPoligono objeto, String usuarioResponsable){ Connection conect=ConnectionConfiguration.conectar(); Statement statement = null; objeto.changeBorrado(); String query = "update accion_has_geo_poligono set borrado='"+objeto.isBorrado()+"'"; query += ", usuario_responsable='" + usuarioResponsable + "'"; query+=" where accion_id ="+objeto.getAccionId()+" AND geo_poligono_id="+objeto.getGeoPoligonoId()+" AND geo_poligono_geo_poligono_id="+objeto.getGeoPoligonoGeoPoligonoId(); try { statement=conect.createStatement(); statement.execute(query); conect.close(); return true; } catch (SQLException e) {e.printStackTrace(); return false;} }
@Test public void testSimpleStatement() { for (Data d : data) { try { String q = String.format("select * from %s", d.tablename); Statement sel = conn.createStatement(); sel.execute(q); ResultSet rs = sel.getResultSet(); int rowCount = 0; while (rs.next()) { rowCount++; } assertEquals(d.good.length, rowCount); } catch(SQLException e) { System.err.printf("ERROR(SELECT): %s: %s\n", d.typename, e.getMessage()); fail(); } } }
/** * 保存Template配置文件信息 * * @param Config * @throws Exception */ public static int saveTemplateConfig(TemplateConfig config, String name) throws Exception { Connection conn = null; Statement stat = null; ResultSet rs = null; try { conn = getConnection(); stat = conn.createStatement(); String jsonStr = JSON.toJSONString(config); String sql = String.format("replace into TemplateConfig(name,value) values('%s', '%s')", name, jsonStr); int result = stat.executeUpdate(sql); return result; } finally { if (rs != null) rs.close(); if (stat != null) stat.close(); if (conn != null) conn.close(); } }
/** * Performs a preformatted statement or group of statements and throws * if the result does not match the expected one. * @param line start line in the script file for this test * @param stat Statement object used to access the database * @param s Contains the type, expected result and SQL for the test */ static void test(Statement stat, String s, int line) { //maintain the interface for this method HsqlArrayList section = new HsqlArrayList(); section.add(s); testSection(stat, section, line); }
public static List<AreasAga> selectAreasAgaCat() throws SQLException{ Connection conect=ConnectionConfiguration.conectar(); String query = " select * from areas_aga "; Statement statement = null; ResultSet rs=null; List<AreasAga> objetos = new ArrayList<AreasAga>(); try { statement = conect.createStatement(); rs=statement.executeQuery(query); while(rs.next()){ AreasAga objeto = new AreasAga(); objeto.setId(rs.getInt("id")); objeto.setNombre(rs.getString("nombre")); objetos.add(objeto); } } catch (SQLException e) {e.printStackTrace();} finally{ if (statement != null) {statement.close();} if (conect != null) {conect.close();} } return objetos; }
/** * @see Connection#createStatement(int, int, int) */ public java.sql.Statement createStatement(int arg0, int arg1, int arg2) throws SQLException { checkClosed(); try { return StatementWrapper.getInstance(this, this.pooledConnection, this.mc.createStatement(arg0, arg1, arg2)); } catch (SQLException sqlException) { checkAndFireConnectionError(sqlException); } return null; // we don't reach this code, compiler can't tell }
public ObservableList<Medicine> getMedicine() { int code = 0, quantity = 0, sgst = 0, cgst = 0, igst = 0; String name, salt, company, type, batch, hsn, expiry; float mrp, cost; medicines = FXCollections.observableArrayList(); try { Connection dbConnection = JDBC.databaseConnect(); Statement sqlStatement = dbConnection.createStatement(); ResultSet medicineResultSet = sqlStatement.executeQuery("SELECT medicine.medicine_id,medicine.name,medicine.salt,medicine.company,medicine.type,medicine.hsn_number,medicine_info.batch_number,medicine_info.expiry_date,medicine_info.mrp,medicine_info.cost_price,quantity.piece,gst.sgst,gst.cgst,gst.igst FROM medicine JOIN medicine_info ON medicine.medicine_id=medicine_info.medicine_id JOIN quantity ON medicine_info.medicine_info_id=quantity.medicine_info_id JOIN gst ON medicine.medicine_id=gst.medicine_id"); while (medicineResultSet.next()) { code = medicineResultSet.getInt("medicine_id"); name = medicineResultSet.getString("name"); salt = medicineResultSet.getString("salt"); company = medicineResultSet.getString("company"); type = medicineResultSet.getString("type"); hsn = medicineResultSet.getString("hsn_number"); sgst = medicineResultSet.getInt("sgst"); cgst = medicineResultSet.getInt("cgst"); igst = medicineResultSet.getInt("igst"); batch = medicineResultSet.getString("batch_number"); expiry = medicineResultSet.getString("expiry_date"); mrp = medicineResultSet.getFloat("mrp"); cost = medicineResultSet.getFloat("cost_price"); quantity = medicineResultSet.getInt("piece"); medicines.add(new Medicine(code, name, salt, company, type, hsn, batch, expiry, quantity, mrp, cost, sgst, cgst, igst)); } } catch (Exception e) { e.printStackTrace(); } return medicines; }
@Override public List<String> getTestNames() { try { return db.withConnection(new ConnectionAction<List<String>>() { public List<String> execute(Connection connection) throws SQLException { List<String> testNames = new ArrayList<String>(); Statement statement = null; ResultSet testExecutions = null; try { statement = connection.createStatement(); testExecutions = statement.executeQuery("select distinct testId from testExecution order by testId"); while (testExecutions.next()) { testNames.add(testExecutions.getString(1)); } } finally { closeStatement(statement); closeResultSet(testExecutions); } return testNames; } }); } catch (Exception e) { throw new RuntimeException(String.format("Could not load test history from datastore '%s'.", db.getUrl()), e); } }
private static void upgrade34to35(Statement stmt) throws SQLException { // @formatter:off execSql(stmt, "alter table ALARM add column enable_alarm bit default 0;"); execSql(stmt, "update ALARM set enable_alarm=0;"); execSql(stmt, "alter table ALARM alter column enable_alarm bit not null;"); execSql(stmt, "alter table ALARM alter column recipient_email varchar(255) null;"); // @formatter:on }
private int execute(Statement statement, String sql, String autoColumn) throws SQLException { Log.debug(SQL, sql); // Execute statement without auto-generated key: if (autoColumn == null) { statement.execute(sql); return -1; } // Execute statement with auto-generated key: statement.execute(sql, new String[]{autoColumn.toUpperCase()}); return getAutoGeneratedKey(statement); }
public static void main(String args[]) { try { // Open database connection Class.forName("org.sqlite.JDBC"); Connection c = DriverManager.getConnection("jdbc:sqlite:./db/company.db"); c.createStatement().execute("PRAGMA foreign_keys=ON"); System.out.println("Database connection opened."); // Get the employee info from the command prompt System.out.println("Please, input the department info:"); BufferedReader reader = new BufferedReader(new InputStreamReader(System.in)); System.out.print("Name: "); String name = reader.readLine(); System.out.print("Address: "); String address = reader.readLine(); // Insert new record: begin Statement stmt = c.createStatement(); String sql = "INSERT INTO departments (name, address) " + "VALUES ('" + name + "', '" + address + "');"; stmt.executeUpdate(sql); stmt.close(); System.out.println("Department info processed"); System.out.println("Records inserted."); // Insert new record: end // Close database connection c.close(); System.out.println("Database connection closed."); } catch (Exception e) { e.printStackTrace(); } }
static List<SqlParam> getOutputColumnInfo(final Connection connection, final String sqlSelectStatement) throws SQLException { List<SqlParam> paramList = new ArrayList<>(); Statement stmt = connection.createStatement(); ResultSet resultSet = stmt.executeQuery(sqlSelectStatement); ResultSetMetaData metaData = resultSet.getMetaData(); if (metaData.getColumnCount()>0){ for (int i=1; i<=metaData.getColumnCount(); i++) { SqlParam param = new SqlParam(metaData.getColumnName(i)); param.setJdbcType(JDBCType.valueOf(metaData.getColumnType(i))); paramList.add(param); } } return paramList; }
/** * Tests fix for Bug#16634180 - LOCK WAIT TIMEOUT EXCEEDED CAUSES SQLEXCEPTION, SHOULD CAUSE SQLTRANSIENTEXCEPTION * * @throws Exception * if the test fails. */ public void testBug16634180() throws Exception { createTable("testBug16634180", "(pk integer primary key, val integer)", "InnoDB"); this.stmt.executeUpdate("insert into testBug16634180 values(0,0)"); Connection c1 = null; Connection c2 = null; try { c1 = getConnectionWithProps(new Properties()); c1.setAutoCommit(false); Statement s1 = c1.createStatement(); s1.executeUpdate("update testBug16634180 set val=val+1 where pk=0"); c2 = getConnectionWithProps(new Properties()); c2.setAutoCommit(false); Statement s2 = c2.createStatement(); try { s2.executeUpdate("update testBug16634180 set val=val+1 where pk=0"); fail("ER_LOCK_WAIT_TIMEOUT should be thrown."); } catch (SQLTransientException ex) { assertEquals(MysqlErrorNumbers.ER_LOCK_WAIT_TIMEOUT, ex.getErrorCode()); assertEquals(SQLError.SQL_STATE_ROLLBACK_SERIALIZATION_FAILURE, ex.getSQLState()); assertEquals("Lock wait timeout exceeded; try restarting transaction", ex.getMessage()); } } finally { if (c1 != null) { c1.close(); } if (c2 != null) { c2.close(); } } }
@Override public ResultSetInternalMethods preProcess(String sql, com.mysql.jdbc.Statement interceptedStatement, com.mysql.jdbc.Connection connection) throws SQLException { if (sql == null) { sql = ""; } if (sql.length() == 0 && interceptedStatement instanceof com.mysql.jdbc.PreparedStatement) { sql = ((com.mysql.jdbc.PreparedStatement) interceptedStatement).asSql(); } if (sql.indexOf("nonexistent_table") >= 0) { assertTrue("Different connection expected.", !connection.equals(previousConnection)); previousConnection = connection; } return null; }
private static int getNumCompletedDatabaseCreations(Connection conn, String db) throws SQLException { Statement cmd = conn.createStatement(); ResultSet resultSet = cmd.executeQuery("SELECT COUNT(*) FROM sys.dm_operation_status \r\n" + "WHERE resource_type = 0 -- 'Database' \r\n AND major_resource_id = '" + db + "' \r\n" + "AND state = 2 -- ' COMPLETED'"); if (resultSet.next()) { return resultSet.getInt(1); } return -1; }
private void testInterfaceImplementation(Connection connToCheck) throws Exception { Method[] dbmdMethods = java.sql.DatabaseMetaData.class.getMethods(); // can't do this statically, as we return different // implementations depending on JDBC version DatabaseMetaData dbmd = connToCheck.getMetaData(); checkInterfaceImplemented(dbmdMethods, dbmd.getClass(), dbmd); Statement stmtToCheck = connToCheck.createStatement(); checkInterfaceImplemented(java.sql.Statement.class.getMethods(), stmtToCheck.getClass(), stmtToCheck); PreparedStatement pStmtToCheck = connToCheck.prepareStatement("SELECT 1"); ParameterMetaData paramMd = pStmtToCheck.getParameterMetaData(); checkInterfaceImplemented(java.sql.PreparedStatement.class.getMethods(), pStmtToCheck.getClass(), pStmtToCheck); checkInterfaceImplemented(java.sql.ParameterMetaData.class.getMethods(), paramMd.getClass(), paramMd); pStmtToCheck = ((com.mysql.jdbc.Connection) connToCheck).serverPrepareStatement("SELECT 1"); checkInterfaceImplemented(java.sql.PreparedStatement.class.getMethods(), pStmtToCheck.getClass(), pStmtToCheck); ResultSet toCheckRs = connToCheck.createStatement().executeQuery("SELECT 1"); checkInterfaceImplemented(java.sql.ResultSet.class.getMethods(), toCheckRs.getClass(), toCheckRs); toCheckRs = connToCheck.createStatement().executeQuery("SELECT 1"); checkInterfaceImplemented(java.sql.ResultSetMetaData.class.getMethods(), toCheckRs.getMetaData().getClass(), toCheckRs.getMetaData()); if (versionMeetsMinimum(5, 0, 0)) { createProcedure("interfaceImpl", "(IN p1 INT)\nBEGIN\nSELECT 1;\nEND"); CallableStatement cstmt = connToCheck.prepareCall("{CALL interfaceImpl(?)}"); checkInterfaceImplemented(java.sql.CallableStatement.class.getMethods(), cstmt.getClass(), cstmt); } checkInterfaceImplemented(java.sql.Connection.class.getMethods(), connToCheck.getClass(), connToCheck); }
public boolean checkExist(String table) { try { // String sql = "SELECT COUNT(*) AS c FROM sqlite_master WHERE type ='table' AND name ='" + table + "' "; String sql = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='" + table + "';"; Statement statement = mConnection.createStatement(); ResultSet rs = statement.executeQuery(sql); int count = 0; if (rs != null && rs.next()) { ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); if (columnCount > 0) { count = rs.getInt(1); } } statement.close(); rs.close(); return count > 0; } catch (Exception e) { e.printStackTrace(); } return false; }
@Test public void testAddGetAndClean() throws SQLException { RunningOperationsStore subject = createSubject(); String sql = "CREATE TABLE FOO (ID INT64 NOT NULL, NAME STRING(100)) PRIMARY KEY (ID)"; for (int counter = 1; counter <= 2; counter++) { boolean exception = counter % 2 == 0; subject.addOperation(Arrays.asList(sql), mockOperation(exception)); try (ResultSet rs = subject.getOperations(mock(Statement.class))) { assertNotNull(rs); int count = 0; while (rs.next()) { count++; assertEquals("TEST_OPERATION", rs.getString("NAME")); assertNotNull(rs.getTimestamp("TIME_STARTED")); assertEquals(sql, rs.getString("STATEMENT")); assertFalse(rs.getBoolean("DONE")); if (count % 2 == 0) { assertEquals("INVALID_ARGUMENT: Some exception", rs.getString("EXCEPTION")); } else { assertNull(rs.getString("EXCEPTION")); } } assertEquals(counter, count); } subject.clearFinishedOperations(); } reportDone = true; subject.clearFinishedOperations(); try (ResultSet rs = subject.getOperations(mock(Statement.class))) { assertFalse(rs.next()); } }
/** * Test Bug#21296840 - CONNECTION DATA IS NOT UPDATED DURING FAILOVER. * Test Bug#17910835 - SERVER INFORMATION FROM FABRIC NOT REFRESHED WITH SHORTER TTL. * * Test that the local cache is refreshed after expired TTL. This test connects to the master of "ha_config1_group" and requires the master to be changed * manually during the wait period. The Fabric must also be setup to communicate a TTL of less than 10s to the client. */ public void manualTestRefreshFabricStateCache() throws Exception { if (!this.isSetForFabricTest) { return; } this.conn = (FabricMySQLConnection) getNewDefaultDataSource().getConnection(this.username, this.password); this.conn.setServerGroupName("ha_config1_group"); this.conn.setReadOnly(false); this.conn.setAutoCommit(false); Statement stmt = this.conn.createStatement(); ResultSet rs = stmt.executeQuery("show variables like 'server_uuid'"); rs.next(); String firstServerUuid = rs.getString(2); rs.close(); this.conn.commit(); // sleep for TTL+1 secs int seconds = 10; System.err.println("Waiting " + seconds + " seconds for new master to be chosen"); Thread.sleep(TimeUnit.SECONDS.toMillis(1 + seconds)); // force the LB proxy to pick a new connection this.conn.rollback(); // verify change is seen by client rs = stmt.executeQuery("show variables like 'server_uuid'"); rs.next(); String secondServerUuid = rs.getString(2); rs.close(); System.err.println("firstServerUuid=" + firstServerUuid + "\nsecondServerUuid=" + secondServerUuid); if (firstServerUuid.equals(secondServerUuid)) { fail("Server ID should change to reflect new topology"); } this.conn.close(); }
public void removeGame(String titleID) throws SQLException{ Statement stmt = connection.createStatement(); stmt.executeUpdate("delete from local_roms where titleID = '"+titleID+"'"); connection.commit(); stmt.close(); LOGGER.info("removed \""+titleID+"\" from ROM database"); }
public void setDonation(User user, String amount) { try { Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); statement.closeOnCompletion(); try(ResultSet results = statement.executeQuery(String.format("SELECT user_id, donated_amount FROM PROFILES WHERE user_id = %s", user.getId()))) { if(results.next()) { results.updateString("donated_amount", amount); results.updateRow(); } else { results.moveToInsertRow(); results.updateLong("user_id", user.getIdLong()); results.updateString("donated_amount", amount); results.insertRow(); } } } catch(SQLException e) { LOG.warn(e.toString()); } }
/** * Fonction permettant d'exécuter la requete SQL de lecture dont le résultat sera retourné. * * @param querySQL * @param aliases */ public QueryResults select(final String querySQL, final Map<String, Map<String, Object>> aliases) throws WireMockCsvException { try (final Statement stmt = this.dbConnection.createStatement(); final ResultSet results = stmt.executeQuery(querySQL)) { return new QueryResults(results, aliases); } catch (final SQLException e) { throw new WireMockCsvException("Erreur lors du select dans la base de données CSV pour la requête " + querySQL + " : " + e.getMessage(), e); } }
@Override public T loadObject(String uniqueId) throws InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, IntrospectionException, SQLException, SecurityException, ClassNotFoundException { Connection connection = null; Statement statement = null; ResultSet resultSet = null; if (DEBUG) plugin.getLogger().info("DEBUG: loading object for " + uniqueId); try { connection = databaseConnecter.createConnection(); String query = "SELECT " + getColumns(false) + " FROM `" + type.getCanonicalName() + "` WHERE uniqueId = ? LIMIT 1"; PreparedStatement preparedStatement = connection.prepareStatement(query); preparedStatement.setString(1, uniqueId); if (DEBUG) plugin.getLogger().info("DEBUG: load Object query = " + preparedStatement.toString()); resultSet = preparedStatement.executeQuery(); List<T> result = createObjects(resultSet); if (!result.isEmpty()) { return result.get(0); } return null; } finally { MySQLDatabaseResourceCloser.close(resultSet); MySQLDatabaseResourceCloser.close(statement); MySQLDatabaseResourceCloser.close(connection); } }
protected void testQuery(final String sql) throws Exception { testAction(new JdbcAction() { @Override public ResultSet getResult(Connection c) throws SQLException { Statement s = c.createStatement(); ResultSet r = s.executeQuery(sql); return r; } }); }