Java Code Examples for java.sql.Statement

The following are top voted examples for showing how to use java.sql.Statement. These examples are extracted from open source projects. You can vote up the examples you like and your votes will be used in our system to generate more good examples.
Example 1
Project: aceql-http   File: PostgreSqlUtil.java   View source code 7 votes vote down vote up
/**
    * 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;
   }
 
Example 2
Project: spr   File: SqlUpdates.java   View source code 7 votes vote down vote up
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();
	}
}
 
Example 3
Project: calcite-avatica   File: AvaticaSpnegoTest.java   View source code 6 votes vote down vote up
@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));
  }
}
 
Example 4
Project: spr   File: SqlSelects.java   View source code 6 votes vote down vote up
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;
   }
 
Example 5
Project: QDrill   File: Bug1735ResultSetCloseReleasesBuffersTest.java   View source code 6 votes vote down vote up
@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 );
          }
        }
      });
}
 
Example 6
Project: spr   File: SqlDelete.java   View source code 6 votes vote down vote up
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();}
 }
 
Example 7
Project: drinkwater-java   File: TestMigration.java   View source code 6 votes vote down vote up
@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());
        }
    }
}
 
Example 8
Project: osc-core   File: ReleaseUpgradeMgr.java   View source code 6 votes vote down vote up
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);
}
 
Example 9
Project: oscm   File: TestDBSetup.java   View source code 6 votes vote down vote up
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);
            }
        }
    }
}
 
Example 10
Project: ProyectoPacientes   File: ConnectionRegressionTest.java   View source code 6 votes vote down vote up
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();
}
 
Example 11
Project: JITRAX   File: DbmsDriver.java   View source code 6 votes vote down vote up
/**
 * 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;
}
 
Example 12
Project: mycat-src-1.6.1-RELEASE   File: JDBCDatasource.java   View source code 6 votes vote down vote up
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;
  }
 
Example 13
Project: morf   File: TestDatabaseMetaDataProvider.java   View source code 6 votes vote down vote up
/**
 * 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();
  }
}
 
Example 14
Project: BibliotecaPS   File: StatementRegressionTest.java   View source code 6 votes vote down vote up
/**
 * 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();
}
 
Example 15
Project: dubbo-mock   File: BootStartServer.java   View source code 6 votes vote down vote up
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) {
	}
}
 
Example 16
Project: tcp   File: ConnectionConfiguration.java   View source code 6 votes vote down vote up
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;
}
 
Example 17
Project: tcp   File: SqlUpdates.java   View source code 6 votes vote down vote up
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;}
}
 
Example 18
Project: s-store   File: TestJDBCQueries.java   View source code 6 votes vote down vote up
@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();
        }
    }
}
 
Example 19
Project: vertx-generator   File: ConfigUtil.java   View source code 6 votes vote down vote up
/**
 * 保存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();
	}
}
 
Example 20
Project: parabuild-ci   File: TestUtil.java   View source code 5 votes vote down vote up
/**
 * 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);
}
 
Example 21
Project: tcp   File: SqlSelects.java   View source code 5 votes vote down vote up
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; 
}
 
Example 22
Project: the-vigilantes   File: ConnectionWrapper.java   View source code 5 votes vote down vote up
/**
 * @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
}
 
Example 23
Project: alchem   File: InventoryController.java   View source code 5 votes vote down vote up
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;
}
 
Example 24
Project: Reer   File: CrossVersionResultsStore.java   View source code 5 votes vote down vote up
@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);
    }
}
 
Example 25
Project: osc-core   File: ReleaseUpgradeMgr.java   View source code 5 votes vote down vote up
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
}
 
Example 26
Project: waterrower-workout   File: DatabaseConnectionService.java   View source code 5 votes vote down vote up
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);
}
 
Example 27
Project: JPA-Demo   File: SQLInsert.java   View source code 5 votes vote down vote up
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();
	}
}
 
Example 28
Project: syndesis   File: DatabaseMetaDataHelper.java   View source code 5 votes vote down vote up
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;
}
 
Example 29
Project: OpenVertretung   File: ConnectionRegressionTest.java   View source code 5 votes vote down vote up
/**
 * 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();
        }
    }
}
 
Example 30
Project: the-vigilantes   File: ConnectionRegressionTest.java   View source code 5 votes vote down vote up
@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;
}
 
Example 31
Project: elastic-db-tools-for-java   File: Program.java   View source code 5 votes vote down vote up
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;
}
 
Example 32
Project: OpenVertretung   File: ConnectionTest.java   View source code 5 votes vote down vote up
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);
}
 
Example 33
Project: KBUnitTest   File: ShadowSQLiteDatabase.java   View source code 5 votes vote down vote up
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;
}
 
Example 34
Project: spanner-jdbc   File: RunningOperationsStoreTest.java   View source code 5 votes vote down vote up
@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());
	}
}
 
Example 35
Project: BibliotecaPS   File: TestRegressions.java   View source code 5 votes vote down vote up
/**
 * 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();
}
 
Example 36
Project: cemu_UI   File: DBController.java   View source code 5 votes vote down vote up
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");
}
 
Example 37
Project: Endless   File: DonatorsDataManager.java   View source code 5 votes vote down vote up
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());
    }
}
 
Example 38
Project: WireMockCsv   File: DbManager.java   View source code 5 votes vote down vote up
/**
 * 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);
	}
}
 
Example 39
Project: bskyblock   File: MySQLDatabaseHandler.java   View source code 5 votes vote down vote up
@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);
    }
}
 
Example 40
Project: QDrill   File: JdbcTestActionBase.java   View source code 5 votes vote down vote up
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;
    }

  });
}