下面列出了java.sql.ResultSet#getString ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
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;
}
@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;
}
@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());
}
}
/**
* 获得配置文件信息
*
* @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 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<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;
}
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();
}
@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 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);
}
@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);
}
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;
}
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");
}
/**
* @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;
}
@Override
public String getNullableResult(ResultSet rs, int columnIndex)
throws SQLException {
return rs.getString(columnIndex);
}
@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;
}
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();
}