Java Code Examples for java.sql.ResultSet#getString()

/**
 * 获得配置文件信息
 * 
 * @return
 * @throws Exception
 */
public static List<HistoryConfig> getHistoryConfigs() throws Exception {
	Connection conn = null;
	Statement stat = null;
	ResultSet rs = null;
	try {
		conn = getConnection();
		stat = conn.createStatement();
		String sql = "select * from HistoryConfig ";
		rs = stat.executeQuery(sql);
		List<HistoryConfig> configs = new ArrayList<>();
		while (rs.next()) {
			String value = rs.getString("value");
			configs.add(JSON.parseObject(value, HistoryConfig.class));
		}
		return configs;
	} finally {
		if (rs != null)
			rs.close();
		if (stat != null)
			stat.close();
		if (conn != null)
			conn.close();
	}
}
 

@Override
public String getTagName(long jobExecutionId) {
	logger.entering(CLASSNAME, "getTagName", jobExecutionId);
	String apptag = null;
	Connection conn = null;
	PreparedStatement statement = null;
	ResultSet rs = null;
	String query = "SELECT A.apptag FROM jobinstancedata A INNER JOIN executioninstancedata B ON A.jobinstanceid = B.jobinstanceid"
			+ " WHERE B.jobexecid = ?";
	try {
		conn = getConnection();
		statement = conn.prepareStatement(query);
		statement.setLong(1, jobExecutionId);
		rs = statement.executeQuery();
		if(rs.next()) {
			apptag = rs.getString(1);
		}
	} catch (SQLException e) {
		throw new PersistenceException(e);
	} finally {
		cleanupConnection(conn, rs, statement);
	}
	logger.exiting(CLASSNAME, "getTagName");
	return apptag;
}
 

private PlayerAuth buildAuthFromResultSet(ResultSet row) throws SQLException {
    String salt = !col.SALT.isEmpty() ? row.getString(col.SALT) : null;

    return PlayerAuth.builder()
        .name(row.getString(col.NAME))
        .email(row.getString(col.EMAIL))
        .realName(row.getString(col.REAL_NAME))
        .password(row.getString(col.PASSWORD), salt)
        .totpKey(row.getString(col.TOTP_KEY))
        .lastLogin(getNullableLong(row, col.LAST_LOGIN))
        .lastIp(row.getString(col.LAST_IP))
        .registrationDate(row.getLong(col.REGISTRATION_DATE))
        .registrationIp(row.getString(col.REGISTRATION_IP))
        .locX(row.getDouble(col.LASTLOC_X))
        .locY(row.getDouble(col.LASTLOC_Y))
        .locZ(row.getDouble(col.LASTLOC_Z))
        .locWorld(row.getString(col.LASTLOC_WORLD))
        .locYaw(row.getFloat(col.LASTLOC_YAW))
        .locPitch(row.getFloat(col.LASTLOC_PITCH))
        .build();
}
 

public static ArrayList<String> getAllPlayerNames(){
	
	ArrayList<String> names = new ArrayList<String>(); 
	try (Connection con = L2DatabaseFactory.getInstance().getConnection())
	{
		PreparedStatement statement = con.prepareStatement("SELECT char_name FROM characters");
		ResultSet nameList = statement.executeQuery();
		
		while (nameList.next())// fills the package
		{
			String name = nameList.getString("char_name");
			names.add(name);
		}
		
		nameList.close();
		statement.close();			
	}
	catch (SQLException e)
	{
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return names;
}
 

@Override
public Source map(int i, ResultSet resultSet, StatementContext statementContext) throws SQLException {
    String resultSource = resultSet.getString("source");
    if (StringUtils.isEmpty(resultSource)) {
        return null;
    }

    Source source;
    try {
        source = new Source(new URL(resultSource));
    } catch (MalformedURLException e) {
        throw new RuntimeException(e);
    }

    Array tags = resultSet.getArray("tags");
    source.setTags(tags == null
        ? Collections.emptyList()
        : Arrays.asList((Object[]) tags.getArray()).stream().map(Object::toString).collect(Collectors.toList())
    );
    source.setFrontpage(resultSet.getBoolean("frontpage"));
    return source;
}
 

public static ArrayList<ProductList> TableGenerator(){
        ArrayList<ProductList> list = new ArrayList<>();
        try {
            Connection con = DriverManager.getConnection("jdbc:sqlite:DBs/electronicsDB.db");
            Statement ps = con.createStatement();
            ResultSet rs = ps.executeQuery("SELECT mbrand, mmodel, mprice,mquantity, mdescription, mphoto FROM electronics");
            
            ProductList pl;
            
            while(rs.next()){
                pl = new ProductList(rs.getString("mbrand"),rs.getString("mmodel"),
                        rs.getInt("mprice"),rs.getInt("mquantity"),rs.getString("mdescription"),
                        rs.getString("mphoto"));
                
                list.add(pl);

            }
            
        } catch (SQLException ex) {
            Logger.getLogger(MobileDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
}
 

@Test
public void selectOverlappedPageTest() {
  String[] res = {
          "11,111",
          "12,112",
          "13,113",
          "14,114",
          "15,215",
          "16,216",
          "17,217",
          "18,218",
          "19,219",
          "20,220",
          "21,221",
          "22,222",
          "23,223",
          "24,224"
  };

  try (Connection connection = DriverManager
          .getConnection(Config.IOTDB_URL_PREFIX + "127.0.0.1:6667/", "root", "root");
       Statement statement = connection.createStatement()) {
    String sql = "select s0 from root.vehicle.d0 where time >= 1 and time <= 50 AND root.vehicle.d0.s0 >= 111";
    ResultSet resultSet = statement.executeQuery(sql);
    int cnt = 0;
    while (resultSet.next()) {
      String ans = resultSet.getString(TIMESTAMP_STR) + "," + resultSet.getString("root.vehicle.d0.s0");
      assertEquals(res[cnt], ans);
      cnt++;
    }
  } catch (Exception e) {
    e.printStackTrace();
    fail(e.getMessage());
  }
}
 

@Override
public Map<String, BannedMacEntry> load() {
	Map<String, BannedMacEntry> map = new FastMap<String, BannedMacEntry>();
	PreparedStatement ps = DB.prepareStatement("SELECT `address`,`time`,`details` FROM `banned_mac`");
	try {
		ResultSet rs = ps.executeQuery();
		while (rs.next())
		{
			String address = rs.getString("address");
			map.put(address, new BannedMacEntry(address, rs.getTimestamp("time"), rs.getString("details")));
		}
	}
	catch (SQLException e) {
		log.error("Error loading last saved server time", e);
	}
	finally {
		DB.close(ps);
	}
	return map;
}
 

@Override
public String getNullableResult(ResultSet rs, int columnIndex)
        throws SQLException {
    return rs.getString(columnIndex);
}
 

@Override
public Revision mapRow(final ResultSet rs, final int i) throws SQLException {
    final String entityId = rs.getString("ENTITY_ID");
    final Long version = rs.getLong("VERSION");
    final String clientId = rs.getString("CLIENT_ID");
    return new Revision(version, clientId, entityId);
}
 

/**
 * Helper method to retrieve the set of catalogs that match 
 * a name provided.
 * 
 * @param completions The current set of object completions.
 * @param conn The connection to the database.
 * @param name A partially completed catalog name.
 */
protected void getCatalogs(Set<String> completions,
        Connection conn, String name) {
    
    int count = 0;
    
    try {
        
        ResultSet results = conn.getMetaData().getCatalogs();
        while (results.next()) {
            
            String catalog = results.getString(1);
            if (name == null || catalog.startsWith(name)) {
                
                ++count;
                completions.add(catalog);
            }
        }
    }
    catch (SQLException e) {
        
        /* IGNORED */
    }
    
    if (LOG.isLoggable(Level.FINE)) {

        LOG.fine("Found " + count + " catalogs matching '" + name + "'");
    }
}
 

String fixupColumnDefWrite(TransferTable t, ResultSetMetaData meta,
                           String columnType, ResultSet columnDesc,
                           int columnIndex) throws SQLException {

    if (columnType.equals("SERIAL")) {
        String SeqName = new String("_" + columnDesc.getString(4)
                                    + "_seq");
        int spaceleft = 31 - SeqName.length();

        if (t.Stmts.sDestTable.length() > spaceleft) {
            SeqName = t.Stmts.sDestTable.substring(0, spaceleft)
                      + SeqName;
        } else {
            SeqName = t.Stmts.sDestTable + SeqName;
        }

        String DropSequence = "DROP SEQUENCE " + SeqName + ";";

        t.Stmts.sDestDrop += DropSequence;
    }

    for (int Idx = 0; Idx < Funcs.length; Idx++) {
        String HSQLDB_func = Funcs[Idx][HSQLDB];
        int    iStartPos   = columnType.indexOf(HSQLDB_func);

        if (iStartPos >= 0) {
            String NewColumnType = columnType.substring(0, iStartPos);

            NewColumnType += Funcs[Idx][ORACLE];
            NewColumnType += columnType.substring(iStartPos
                                                  + HSQLDB_func.length());
            columnType = NewColumnType;
        }
    }

    return (columnType);
}
 

@Test
@Ignore("Not working when multi-threaed for me - JL")
public void testSortIndexedRows() throws Exception{
	PreparedStatement ps = methodWatcher.prepareStatement("select " +
			"t.tablename,t.schemaid " +
			"from " +
			"sys.systables t " +
			"order by " +
			"t.tablename");

	final Map<String,String> correctSort = new TreeMap<String,String>();
	List<String> results = Lists.newArrayList();
	ResultSet rs = ps.executeQuery();
	while(rs.next()){
		String tableName = rs.getString(1);
		String schemaId = rs.getString(2);
		Assert.assertNotNull("no table name returned!",tableName);
		Assert.assertNotNull("no schema returned!",schemaId);
		results.add(String.format("tableName=%s,schemaId=%s",tableName,schemaId));
		correctSort.put(tableName, schemaId);
	}
	int pos=0;
	for(String correct:correctSort.keySet()){
		String correctResult = String.format("tableName=%s,schemaId=%s",correct,correctSort.get(correct));
		Assert.assertEquals("sorting is incorrect! " + correctResult + " : " + results.get(pos),correctResult,results.get(pos));
		LOG.info(results.get(pos));
		pos++;
	}
	Assert.assertTrue("No rows returned!",results.size()>0);
}
 

private static SqlNode readNode(ResultSet rs) throws SQLException {
    long id = rs.getLong("id");
    String permission = rs.getString("permission");
    boolean value = rs.getBoolean("value");
    String server = rs.getString("server");
    String world = rs.getString("world");
    long expiry = rs.getLong("expiry");
    String contexts = rs.getString("contexts");
    return SqlNode.fromSqlFields(id, permission, value, server, world, expiry, contexts);
}
 

/**
 * @param tablesToSkip list of some tables we are not interested in
 * @return description about all important tables
 */
@Override
public List<TableDescription> getTableDescriptions( List<String> tablesToSkip ) {

    Connection connection = ConnectionPool.getConnection(dbConnection);

    List<TableDescription> tables = new ArrayList<TableDescription>();

    if (tablesToSkip == null) {
        tablesToSkip = new ArrayList<>();
    }
    try {
        DatabaseMetaData databaseMetaData = connection.getMetaData();

        final String schemaPattern = (this instanceof OracleDbProvider
                                                                       ? dbConnection.getUser()
                                                                       : null);

        ResultSet tablesResultSet = databaseMetaData.getTables(null, schemaPattern, null,
                                                               new String[]{ "TABLE" });
        while (tablesResultSet.next()) {

            String tableName = tablesResultSet.getString(3);

            // check for tables the DB driver rejects to return
            if (!isTableAccepted(tablesResultSet, dbConnection.db, tableName)) {
                continue;
            }
            // check for tables the user is not interested in
            boolean skipThisTable = false;
            for (String tableToSkip : tablesToSkip) {
                if (tableName.equalsIgnoreCase(tableToSkip)) {
                    skipThisTable = true;
                    break;
                }
            }
            if (skipThisTable) {
                continue;
            }

            log.debug("Extracting description about '" + tableName + "' table");

            TableDescription table = new TableDescription();
            table.setName(tableName);
            table.setSchema(tablesResultSet.getString("TABLE_SCHEM"));

            table.setPrimaryKeyColumn(extractPrimaryKeyColumn(tableName, databaseMetaData,
                                                              schemaPattern));
            table.setIndexes(extractTableIndexes(tableName, databaseMetaData,
                                                 connection.getCatalog()));

            List<String> columnDescriptions = new ArrayList<>();
            extractTableColumns(tableName, databaseMetaData, schemaPattern, columnDescriptions);
            table.setColumnDescriptions(columnDescriptions);

            tables.add(table);
        }
    } catch (SQLException sqle) {
        throw new DbException("Error extracting DB schema information", sqle);
    }

    return tables;
}
 

@Test
public void getRowKeyBytesAndVerify() throws Exception {
    try (Connection conn = DriverManager.getConnection(getUrl())) {
        int[] values = {3,7,9,158,5};
        String tableName = generateUniqueName();
        String ddl =
                "CREATE TABLE IF NOT EXISTS " + tableName + " "
                        + "(id INTEGER NOT NULL, pkcol VARCHAR, page_id UNSIGNED_LONG,"
                        + " \"DATE\" BIGINT, \"value\" INTEGER,"
                        + " constraint pk primary key(id, pkcol)) COLUMN_ENCODED_BYTES = 0";
        conn.createStatement().execute(ddl);

        conn.createStatement().execute("UPSERT INTO " + tableName
                + " (id, pkcol, page_id, \"DATE\", \"value\") VALUES (1, 'a', 8, 1," + values[0] + ")");
        conn.createStatement().execute("UPSERT INTO " + tableName
                + " (id, pkcol, page_id, \"DATE\", \"value\") VALUES (2, 'ab', 8, 2," + values[1] + ")");
        conn.createStatement().execute("UPSERT INTO " + tableName
                + " (id, pkcol, page_id, \"DATE\", \"value\") VALUES (3, 'abc', 8, 3," + values[2] + ")");
        conn.createStatement().execute("UPSERT INTO " + tableName
                + " (id, pkcol, page_id, \"DATE\", \"value\") VALUES (5, 'abcde', 8, 5," + values[4] + ")");
        conn.createStatement().execute("UPSERT INTO " + tableName
                + " (id, pkcol, page_id, \"DATE\", \"value\") VALUES (4, 'abcd', 8, 4," + values[3] + ")");
        conn.commit();

        ResultSet rs =
                conn.createStatement().executeQuery("SELECT ROWKEY_BYTES_STRING() FROM " + tableName);
        try (org.apache.hadoop.hbase.client.Connection hconn =
                ConnectionFactory.createConnection(config)) {
            Table table = hconn.getTable(TableName.valueOf(tableName));
            int i = 0;
            while (rs.next()) {
                String s = rs.getString(1);
                Get get = new Get(Bytes.toBytesBinary(s));
                Result hbaseRes = table.get(get);
                assertFalse(hbaseRes.isEmpty());
                assertTrue(Bytes.equals(hbaseRes.getValue(QueryConstants.DEFAULT_COLUMN_FAMILY_BYTES, Bytes.toBytes("value")), 
                    PInteger.INSTANCE.toBytes(values[i])));
                i++;
            }
        }
    }
}
 

@Override
public Character get(ResultSet rs, String[] names,
                     SessionImplementor session, Object owner) throws SQLException {
    String value = rs.getString(names[0]);
    return (value != null && value.length() > 0) ? value.charAt(0) : null;
}
 

protected void runSelectForUpdate_PR_composite_key_partially_in_projection(
    final Connection conn) throws Exception {
  String jdbcSQL = "create table Employee "
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      + "workdept varchar(50), bonus int not null, primary key (firstname, lastname))";
  
  clientSQLExecute(1, jdbcSQL);
  
  jdbcSQL = "insert into employee values('neeraj', 'kumar', 'rnd', 0), "
      + "('asif', 'shahid', 'rnd', 0), "
      + "('dada', 'ji', 'rnd', 0), ('sum', 'wale', 'rnd', 0)";

  clientSQLExecute(1, jdbcSQL);

  String sql = "SELECT firstname, workdept, bonus "
      + "FROM EMPLOYEE FOR UPDATE of BONUS";

  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);

  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);
  
  stmt.execute(sql);

  this.serverVMs.get(0).invoke(getClass(), "executeSelectForUpdateQuery",
      new Object[] { sql, Boolean.TRUE, getIsolationLevel() });

  ResultSet uprs = stmt.getResultSet();

  String theDept = "rnd";
  while (uprs.next()) {
    String workDept = uprs.getString("WORKDEPT");
    if (workDept.equals(theDept)) {
      uprs.updateInt("bonus", 10);
      uprs.updateRow();
    }
  }
  conn.commit();
  sql = "select * from employee";

  stmt.execute(sql);
  ResultSet rs = stmt.getResultSet();
  int cnt = 0;
  while(rs.next()) {
    cnt++;
    int bonus = rs.getInt(4);
    assertEquals(10, bonus);
  }
  assertEquals(4, cnt);
  
  conn.commit();
  sqlExecuteVerify(new int[] { 1 }, new int[] {1}, sql, goldenTextFile, "equal_bonus");
}
 

public void _testSelectForUpdateForDebugging() throws Exception {
  Connection conn = getConnection();
  Statement stmtForTableAndInsert = conn.createStatement();
  stmtForTableAndInsert.execute("create table Employee"
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      + "workdept varchar(50), bonus decimal(10,4), "
      + "primary key (firstname, lastname))");
  stmtForTableAndInsert
      .execute("insert into employee values('neeraj', 'kumar', 'rnd', 0.0), "
          + "('asif', 'shahid', 'rnd', 1.0), "
          + "('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  conn.commit();
  // conn.setAutoCommit(false);
  // Create the statement with concurrency mode CONCUR_UPDATABLE
  // to allow result sets to be updatable
  conn.setTransactionIsolation(getIsolationLevel());
  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  // Statement stmt = conn.createStatement();
  // Updatable statements have some requirements
  // for example, select must be on a single table
  // Only bonus can be updated
  // ResultSet uprs = stmt.executeQuery(
  // "SELECT WORKDEPT, BONUS /*, firstname, LastNaME*/ " +
  // "FROM EMPLOYEE where lastname = 'kumar' FOR UPDATE of BONUS");

  // Only bonus can be updated
  ResultSet uprs = stmt.executeQuery("SELECT workdept, bonus "
      + "FROM EMPLOYEE where lastname = 'kumar' FOR UPDATE of BONUS");
  // ResultSet uprs = stmt.executeQuery(
  // "SELECT firstname, count(*) " +
  // "FROM EMPLOYEE group by firstname FOR UPDATE of BONUS"); // Only bonus
  // can be updated

  while (uprs.next()) {
    uprs.getString("WORKDEPT");
    BigDecimal bonus = uprs.getBigDecimal("BONUS");
    // if (workDept.equals(theDept)) {
    if (true) {
      // if the current row meets our criteria,
      // update the updatable column in the row
      uprs.updateBigDecimal("BONUS", bonus.add(BigDecimal.valueOf(250L)));
      // uprs.updateBigDecimal("BONUS", null);
      uprs.updateRow();
      // System.out.println("Updating bonus for employee:" +
      // firstnme +" "+ lastName);
    }
  }
  conn.commit(); // commit the transaction
  // close object
  uprs.close();
  ResultSet rs = stmt.executeQuery("select * from employee");
  while (rs.next()) {
    System.out.println(rs.getString(1) + ", " + rs.getString(2) + ", "
        + rs.getString(3) + ", " + rs.getBigDecimal(4));
  }
  conn.commit();
  stmt.close();
  // Close connection if the application does not need it any more
  conn.close();

}
 

public static ObservableList getHistory(ObservableList list, int dateRange) {
	ResultSet rs = null;
	dateTime = new Date();

	// past dates denpending upon the function parameter 'dateRange'
	dateFormate = new SimpleDateFormat("yy-MM-dd");
	final Calendar cal = Calendar.getInstance();
	cal.add(Calendar.DATE, dateRange);
	String pastDate = dateFormate.format(cal.getTime());
	pastDate = "'" + pastDate + "'";

	String qeury;
	try {
		// Class.forName("org.sqlite.JDBC");
		// c = DriverManager.getConnection("jdbc:sqlite:History.db");
		// user aske for today or yesterday history
		if (dateRange == 0 || dateRange == -1) {
			qeury = "select * from (select * from history order by Time DESC) history where Date like" + pastDate
					+ ";";
			perp = SqliteConnection.Connector().prepareStatement(qeury);
			rs = perp.executeQuery();
		}
		// if user asks for more two day history
		else {
			qeury = "select * from (select * from history order by Time DESC) history where Date>=" + pastDate
					+ " Order BY Date DESC;";
			perp = SqliteConnection.Connector().prepareStatement(qeury);
			rs = perp.executeQuery();
		}

		while (rs.next())// loop for data fetching and pass it to GUI table
			// view
		{
			String email1 = rs.getString(1);
			String link1 = rs.getString(2);
			String time1 = rs.getString(3);
			String date1 = rs.getString(4);
			String domain1 = rs.getString(5);
			String title1 = rs.getString(6);

			list = HistoryController.addDataInList(email1,link1, time1, date1, domain1, title1, list);
		}

		rs.close();
		perp.close();
		SqliteConnection.Connector().close();
	} catch (Exception e) {
		e.printStackTrace();
		System.out.println("isseus in getHistory method ");
	}
	return list;
}