Java Code Examples for java.sql.Statement

/**
    * 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;
   }
 

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) {
	}
}
 

@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 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;
    }

  });
}