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   Source Code and License 8 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   Source Code and License 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: dubbo-mock   File: BootStartServer.java   Source Code and License 7 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 4
Project: calcite-avatica   File: AvaticaSpnegoTest.java   Source Code and License 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 5
Project: spr   File: SqlSelects.java   Source Code and License 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 6
Project: QDrill   File: Bug1735ResultSetCloseReleasesBuffersTest.java   Source Code and License 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 7
Project: spr   File: SqlDelete.java   Source Code and License 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 8
Project: drinkwater-java   File: TestMigration.java   Source Code and License 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 9
Project: osc-core   File: ReleaseUpgradeMgr.java   Source Code and License 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 10
Project: oscm   File: TestDBSetup.java   Source Code and License 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 11
Project: ProyectoPacientes   File: ConnectionRegressionTest.java   Source Code and License 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 12
Project: JITRAX   File: DbmsDriver.java   Source Code and License 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 13
Project: mycat-src-1.6.1-RELEASE   File: JDBCDatasource.java   Source Code and License 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 14
Project: morf   File: TestDatabaseMetaDataProvider.java   Source Code and License 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 15
Project: BibliotecaPS   File: StatementRegressionTest.java   Source Code and License 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 16
Project: tcp   File: ConnectionConfiguration.java   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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   Source Code and License 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;
    }

  });
}
 
Example 41
Project: osc-core   File: ServerDebugApis.java   Source Code and License 5 votes vote down vote up
private StringBuilder query(String sql) throws IOException {
    StringBuilder output = new StringBuilder();
    output.append("Query: ").append(sql).append("\n");

    try (Connection conn = this.dbConnectionManager.getSQLConnection();
         Statement statement = conn.createStatement()) {
        try (ResultSet result = statement.executeQuery(sql)){
            processResultSetForQuery(output, result);
        }
    } catch (Exception ex) {
        output.append(getStackTrace(ex));
    }

    return output;
}
 
Example 42
Project: osc-core   File: ReleaseUpgradeMgr.java   Source Code and License 5 votes vote down vote up
private static void upgrade57to58(Statement stmt) throws SQLException {
    // @formatter:off

    execSql(stmt, "alter table ALERT alter column object_id bigint null");
    execSql(stmt, "alter table ALERT alter column object_type varchar(255) null");
    execSql(stmt, "alter table ALERT alter column object_name varchar(255) null");

    // @formatter:on
}
 
Example 43
Project: lams   File: StatementWrapper.java   Source Code and License 5 votes vote down vote up
protected static StatementWrapper getInstance(ConnectionWrapper c, MysqlPooledConnection conn, Statement toWrap) throws SQLException {
    if (!Util.isJdbc4()) {
        return new StatementWrapper(c, conn, toWrap);
    }

    return (StatementWrapper) Util.handleNewInstance(JDBC_4_STATEMENT_WRAPPER_CTOR, new Object[] { c, conn, toWrap }, conn.getExceptionInterceptor());
}
 
Example 44
Project: family-tree-xml-parser   File: DocumentRepository.java   Source Code and License 5 votes vote down vote up
private int saveEntry(String name, Integer parentId) {
  String query = "INSERT INTO ENTRY (NAME, PARENT_ID) VALUES (?, ?)";

  KeyHolder keyHolder = new GeneratedKeyHolder();

  jdbcTemplate.update(conn -> {
    PreparedStatement ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
    ps.setString(1, name);
    ps.setObject(2, parentId);
    return ps;
  }, keyHolder);

  return keyHolder.getKey().intValue();
}
 
Example 45
Project: OpenDiabetes   File: TestStoredProcedure.java   Source Code and License 5 votes vote down vote up
public void testFour() throws SQLException {

        Connection conn = newConnection();
        Statement  st   = conn.createStatement();

        st.execute("declare varone int default 0;");
        st.execute(
            "create procedure proc_inout_result_two (inout intp int) "
            + " language java reads sql data dynamic result sets 2 external name 'CLASSPATH:org.hsqldb.test.TestStoredProcedure.procWithResultTwo'");

        CallableStatement cs =
            conn.prepareCall("call proc_inout_result_two(varone)");
        boolean isResult = cs.execute();

        assertFalse(isResult);
        cs.getMoreResults();

        ResultSet rs = cs.getResultSet();

        rs.next();
        assertEquals(rs.getString(1), "SYSTEM_LOBS");
        assertEquals(rs.getString(2), "LOB_IDS");
        rs.close();

        if (cs.getMoreResults()) {
            rs = cs.getResultSet();

            rs.next();
            assertEquals(rs.getString(1), "SYSTEM_LOBS");
            assertEquals(rs.getString(2), "LOBS");
            rs.close();
        }
    }
 
Example 46
Project: SistemaAlmoxarifado   File: SetorDAO.java   Source Code and License 5 votes vote down vote up
public static Setor create(Setor setor) throws SQLException {
    Statement stm
            = Database.createConnection().
                    createStatement();
    String sql
            = "INSERT INTO setores (`nome`, `sigla`) VALUES ('"
            + setor.getNome() + "','"
            + setor.getSigla() + "')";

    stm.execute(sql, Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stm.getGeneratedKeys();
    rs.next();
    setor.setId(rs.getInt(1));
    return setor;
}
 
Example 47
Project: the-vigilantes   File: ResultSetRegressionTest.java   Source Code and License 5 votes vote down vote up
public void testBug19724() throws Exception {
    if (versionMeetsMinimum(4, 1)) {
        // can't set this via session on 4.0 :(

        createTable("test19724", "(col1 INTEGER NOT NULL, col2 VARCHAR(255) NULL, PRIMARY KEY (col1))");

        this.stmt.execute("INSERT IGNORE INTO test19724 VALUES (0, 'Blah'),(1,'Boo')");

        Connection ansiConn = null;
        Statement updStmt = null;

        Properties props = new Properties();
        props.setProperty("sessionVariables", "sql_mode=ansi");

        try {
            ansiConn = getConnectionWithProps(props);
            updStmt = ansiConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            this.rs = updStmt.executeQuery("SELECT * FROM test19724");

            this.rs.beforeFirst();

            this.rs.next();

            this.rs.updateString("col2", "blah2");
            this.rs.updateRow();
        } finally {
            if (ansiConn != null) {
                ansiConn.close();
            }
        }
    }
}
 
Example 48
Project: Homework   File: DAOEX01.java   Source Code and License 5 votes vote down vote up
public List<String> getPTList()
{
	ResultSet rs = null;
	Statement stat = null;
	List<String> PTList = new ArrayList<>();
	try
	{
		String sql = "SELECT * FROM tbl_pet_type";
		stat = conn.createStatement();
		rs = stat.executeQuery(sql);

		Pettype pt = null;
		while(rs.next())
		{
			//int typeId = rs.getInt("type_id");
			String typeName = rs.getString("tyoe_name");
			//pt = new Pettype(typeId, typeName);
			PTList.add(typeName);
		}	
	}
	catch(SQLException e)
	{
		LOGGER.catching(e);
	}
	finally
	{
		BaseDao.closeResultSet(rs);	
		BaseDao.closeStatement(stat);	
	}	
	return PTList;
}
 
Example 49
Project: OpenDiabetes   File: FindFile.java   Source Code and License 5 votes vote down vote up
/**
 * @throws SQLException
 */
static void listFiles(Connection conn, String name) throws SQLException {

    System.out.println("Files like '" + name + "'");

    // Convert to upper case, so the search is case-insensitive
    name = name.toUpperCase();

    // Create a statement object
    Statement stat = conn.createStatement();

    // Now execute the search query
    // UCASE: This is a case insensitive search
    // ESCAPE ':' is used so it can be easily searched for '\'
    ResultSet result = stat.executeQuery("SELECT Path FROM Files WHERE "
                                         + "UCASE(Path) LIKE '%" + name
                                         + "%' ESCAPE ':'");

    // Moves to the next record until no more records
    while (result.next()) {

        // Print the first column of the result
        // could use also getString("Path")
        System.out.println(result.getString(1));
    }

    // Close the ResultSet - not really necessary, but recommended
    result.close();
}
 
Example 50
Project: xm-ms-entity   File: TenantDatabaseService.java   Source Code and License 5 votes vote down vote up
/**
 * Drop database schema for tenant.
 *
 * @param tenantKey - the tenant key
 */
public void drop(String tenantKey) {
    StopWatch stopWatch = createStarted();
    log.info("START - SETUP:DeleteTenant:liquibase tenantKey={}", tenantKey);
    try (Connection connection = dataSource.getConnection();
         Statement statement = connection.createStatement()) {
        statement.executeUpdate(String.format(schemaDropResolver.getSchemaDropCommand(), tenantKey));
    } catch (SQLException e) {
        throw new RuntimeException("Can not connect to database", e);
    }
    log.info("STOP - SETUP:DeleteTenant:liquibase tenantKey={}, time={}ms", tenantKey, stopWatch.getTime());
}
 
Example 51
Project: Money-Manager   File: UserBasic.java   Source Code and License 5 votes vote down vote up
public String getSavedSecurityQuestion() {
	String question = null;
	String sql = "SELECT securityQuestion \n"
			+ "FROM Credentials \n"
			+ "WHERE ID = 1";
	try (Connection conn = connector();
			Statement stmt = conn.createStatement();
			ResultSet result = stmt.executeQuery(sql)) {
		question = result.getString("securityQuestion");
	} catch (Exception e) {
		e.printStackTrace();
	}
	return question;
}
 
Example 52
Project: OpenDiabetes   File: TestTextTables.java   Source Code and License 5 votes vote down vote up
void initDatabase() throws Exception {

        Connection conn = newConnection();
        Statement  st   = conn.createStatement();

        st.execute("set files write delay 0");
        st.execute("set database transaction control locks");
    }
 
Example 53
Project: morpheus-core   File: DbSink.java   Source Code and License 5 votes vote down vote up
/**
 * Returns a apply of column type info for the target table
 * @param frame     the DataFrame reference
 * @return          the apply of column type info
 */
@SuppressWarnings("unchecked")
private List<ColumnAdapter> getColumnAdapters(DataFrame<R,C> frame, DbSinkOptions<R,C> options) {
    final Connection conn = options.getConnection();
    final String tableName = options.getTableName();
    final SQLPlatform platform = options.getPlatform().orElseThrow(() -> new IllegalStateException("No SQL platform specified in options"));
    final Map<C,String> columnMap1 = frame.cols().keys().collect(Collectors.toMap(c -> c, c -> options.getColumnNames().apply(c)));
    final Map<String,C> columnMap2 = Collect.reverse(columnMap1);
    try (Statement stmt = conn.createStatement()) {
        final String sql = "select * from \"" + tableName + "\" where 1=2";
        final List<ColumnAdapter> columnList = new ArrayList<>();
        final ResultSetMetaData metaData = stmt.executeQuery(sql).getMetaData();
        final SQLType.TypeResolver typeResolver = SQLType.getTypeResolver(platform);
        for (int i=0; i<metaData.getColumnCount(); ++i) {
            final String sqlColName = metaData.getColumnName(i+1);
            final int sqlTypeCode = metaData.getColumnType(i+1);
            final String sqlTypeName = metaData.getColumnTypeName(i+1);
            final SQLType sqlType = typeResolver.getType(sqlTypeCode, sqlTypeName);
            if (options.getRowKeyColumn().map(name -> name.equals(sqlColName)).orElse(false)) {
                columnList.add(new RowKeyAdapter(sqlColName, sqlType, options));
            } else if (options.getAutoIncrementColumnName().map(name -> !name.equalsIgnoreCase(sqlColName)).orElse(true)) {
                final C colKey = columnMap2.get(sqlColName);
                final Class<?> dataType = frame.cols().type(colKey);
                final DataFrameCursor<R,C> cursor = frame.cursor().atColKey(colKey);
                final Function1<DataFrameValue<R,C>,?> mapper = options.getColumnMappings().getMapper(dataType);
                columnList.add(new ValueAdapter(sqlColName, sqlType, cursor, mapper));
            }
        }
        return columnList;
    } catch (Exception ex) {
        throw new DataFrameException("Failed to resolve SQL column types for table " + tableName, ex);
    }
}
 
Example 54
Project: Android_Code_Arbiter   File: Jdbc.java   Source Code and License 5 votes vote down vote up
public void executeExecuteLargeUpdateSamples(String sql) throws SQLException {

        Statement stmt = con.createStatement();

        stmt.executeLargeUpdate(sql);
        stmt.executeLargeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        stmt.executeLargeUpdate(sql, new int[]{1, 2, 3});
        stmt.executeLargeUpdate(sql, new String[]{"firstname", "middlename", "lastname"});
    }
 
Example 55
Project: dev-courses   File: Testdb.java   Source Code and License 5 votes vote down vote up
public void shutdown() throws SQLException {

        Statement st = conn.createStatement();

        // db writes out to files and performs clean shuts down
        // otherwise there will be an unclean shutdown
        // when program ends
        st.execute("SHUTDOWN");
        conn.close();    // if there are no other open connection
    }
 
Example 56
Project: TurteTracker_APIServer   File: DatabaseConnection.java   Source Code and License 5 votes vote down vote up
private static long getWaitTimeout(Connection con) throws SQLException {
    try (Statement stmt = con.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SHOW VARIABLES LIKE 'wait_timeout'")) {
            if (rs.next()) {
                return Math.max(1000, rs.getInt(2) * 1000 - 1000);
            } else {
                return -1;
            }
        }
    }
}
 
Example 57
Project: dev-courses   File: TestSql.java   Source Code and License 5 votes vote down vote up
public void testAny() {

        try {
            String ddl =
                "drop table PRICE_RELATE_USER_ORDER_V2 if exists;"
                + "create table PRICE_RELATE_USER_ORDER_V2 "
                + "(ID_ORDER_V2 BIGINT, ID_USER NUMERIC, DATE_CREATE TIMESTAMP)";
            String sql = "insert into PRICE_RELATE_USER_ORDER_V2 "
                         + "(ID_ORDER_V2, ID_USER, DATE_CREATE) " + "values "
                         + "(?, ?, ?)";
            Statement st = connection.createStatement();

            st.execute(ddl);

            PreparedStatement ps = connection.prepareStatement(sql);

            ps.setLong(1, 1);
            ps.setNull(2, Types.NUMERIC);
            ps.setTimestamp(
                3, new java.sql.Timestamp(System.currentTimeMillis()));
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("TestSql.testAny() error: " + e.getMessage());
        }

        System.out.println("testAny complete");
    }
 
Example 58
Project: monarch   File: BlockingTimeOutJUnitTest.java   Source Code and License 5 votes vote down vote up
private static void createTable(String tableName) throws Exception {
  Context ctx = cache.getJNDIContext();
  DataSource ds = (DataSource) ctx.lookup("java:/SimpleDataSource");
  String sql = "create table " + tableName
      + " (id integer NOT NULL, name varchar(50), CONSTRAINT the_key PRIMARY KEY(id))";
  logger.debug(sql);
  Connection conn = ds.getConnection();
  Statement sm = conn.createStatement();
  sm.execute(sql);
  sm.close();
  sm = conn.createStatement();
  for (int i = 1; i <= 10; i++) {
    sql = "insert into " + tableName + " values (" + i + ",'name" + i + "')";
    sm.addBatch(sql);
    logger.debug(sql);
  }
  sm.executeBatch();
  conn.close();
}
 
Example 59
Project: osc-core   File: ReleaseUpgradeMgr.java   Source Code and License 5 votes vote down vote up
private static void upgrade92to93(Statement stmt) throws SQLException {
	execSql(stmt,
            "alter table SECURITY_GROUP add column sfc_fk bigint;");

	execSql(stmt,
	"alter table SECURITY_GROUP " +
	"add constraint FK_SG_SFC " +
	"foreign key (sfc_fk) " +
	"references SERVICE_FUNCTION_CHAIN;");
}
 
Example 60
Project: L2J-Global   File: ClanTable.java   Source Code and License 5 votes vote down vote up
private void restorewars()
{
	try (Connection con = DatabaseFactory.getInstance().getConnection();
		Statement statement = con.createStatement();
		ResultSet rset = statement.executeQuery("SELECT clan1, clan2, clan1Kill, clan2Kill, winnerClan, startTime, endTime, state FROM clan_wars"))
	{
		while (rset.next())
		{
			final L2Clan attacker = getClan(rset.getInt("clan1"));
			final L2Clan attacked = getClan(rset.getInt("clan2"));
			if ((attacker != null) && (attacked != null))
			{
				final ClanWarState state = ClanWarState.values()[rset.getInt("state")];
				
				final ClanWar clanWar = new ClanWar(attacker, attacked, rset.getInt("clan1Kill"), rset.getInt("clan2Kill"), rset.getInt("winnerClan"), rset.getLong("startTime"), rset.getLong("endTime"), state);
				attacker.addWar(attacked.getId(), clanWar);
				attacked.addWar(attacker.getId(), clanWar);
			}
			else
			{
				LOGGER.log(Level.WARNING, getClass().getSimpleName() + ": Restorewars one of clans is null attacker:" + attacker + " attacked:" + attacked);
			}
		}
	}
	catch (Exception e)
	{
		LOGGER.log(Level.SEVERE, getClass().getSimpleName() + ": Error restoring clan wars data.", e);
	}
}