Java Code Examples for java.sql.ResultSet

/** 
 */
public SQLPlayerFactory(){
  insertPlayer=Database.DATABASE.prepare("INSERT INTO `ac_players` (`name`) VALUES (?);");
  final ResultSet rs=Database.DATABASE.query("SELECT `name`,`id` FROM `ac_players`");
  try {
    while (rs.next()) {
      players.put(rs.getString("name"),rs.getLong("id"));
    }
    rs.close();
  }
 catch (  final SQLException e) {
    ACLogger.severe("Problem when getting players from the DB",e);
  }
  DebugLog.INSTANCE.info("Players found : " + Joiner.on(", ").join(players.keySet()));
}
 

private boolean areaExists(int x,int z){
  try {
    this.checkExistance.setInt(1,x);
    this.checkExistance.setInt(2,z);
    ResultSet result=this.checkExistance.executeQuery();
    return result != null && result.next();
  }
 catch (  Exception e) {
    e.printStackTrace();
    return true;
  }
}
 

private static Map<String,String> getKeyToDisplayMapping(Connection conn,String internalSourceName,String internalKeyFieldName,String internalDisplayFieldName) throws SQLException {
  String SQLCode="SELECT " + internalKeyFieldName + ", "+ internalDisplayFieldName;
  SQLCode+=" FROM " + internalSourceName;
  PreparedStatement statement=conn.prepareStatement(SQLCode);
  ResultSet results=statement.executeQuery();
  Map<String,String> displayLookup=new HashMap<String,String>();
  while (results.next()) {
    displayLookup.put(results.getString(internalKeyFieldName),results.getString(internalDisplayFieldName));
  }
  results.close();
  statement.close();
  return displayLookup;
}
 

/** 
 * {@inheritDoc}
 */
@Override public int load(){
  PreparedStatement ps=DB.prepareStatement("SELECT `value` FROM `server_variables` WHERE `key`='time'");
  try {
    ResultSet rs=ps.executeQuery();
    if (rs.next())     return Integer.parseInt(rs.getString("value"));
  }
 catch (  SQLException e) {
    Logger.getLogger(MySQL5GameTimeDAO.class).error("Error loading last saved server time",e);
  }
 finally {
    DB.close(ps);
  }
  return 0;
}
 

/** 
 * Test that things get cleaned up. This runs after all other tests that create or otherwise manipulate blobs.
 */
@Override public void testCleanup() throws Exception {
  super.testCleanup();
  Connection connection=DriverManager.getConnection("jdbc:derby:" + dbDir);
  ResultSet rs=connection.createStatement().executeQuery("SELECT * FROM " + TransactionalStore.NAME_TABLE);
  assertFalse(rs.next(),"unexpected entries in name-map table;");
  rs=connection.createStatement().executeQuery("SELECT * FROM " + TransactionalStore.DEL_TABLE);
  assertFalse(rs.next(),"unexpected entries in deleted-list table;");
}
 

@Test public void shouldExtractCorpusPath() throws SQLException {
  String pathAlias=uniqueString(5);
  String path1=uniqueString(3);
  String path2=uniqueString(3);
  String path3=uniqueString(3);
  ResultSet resultSet=mock(ResultSet.class);
  Array array=createJdbcArray(path1,path2,path3);
  given(resultSet.getArray(pathAlias)).willReturn(array);
  List<String> path=extractor.extractCorpusPath(resultSet,pathAlias);
  assertThat(path,is(asList(path3,path2,path1)));
}
 

private long runTimeQuery(String query){
  long result=0;
  try {
    ResultSet rs=skCase.runQuery(query);
    result=rs.getLong(1);
    Statement s=rs.getStatement();
    rs.close();
    if (s != null)     s.close();
  }
 catch (  SQLException ex) {
    Logger.getLogger(RecentFilesFilterChildren.class.getName()).log(Level.WARNING,"Couldn't get search results",ex);
  }
  return result;
}
 

/** 
 * @param sql sql
 * @param paramList paramList
 * @param connection connection
 * @param ifOnlyOne ifOnlyOne to determine return object or array.
 * @param tableName tableName
 * @return JSONObject
 * @throws SQLException SQLException
 * @throws JSONException JSONException
 * @throws RepositoryException respsitoryException
 */
private static JSONObject queryJson(final String sql,final List<Object> paramList,final Connection connection,final boolean ifOnlyOne,final String tableName) throws SQLException, JSONException, RepositoryException {
  LOGGER.log(Level.FINEST,"querySql: {0}",sql);
  final PreparedStatement preparedStatement=connection.prepareStatement(sql);
  for (int i=1; i <= paramList.size(); i++) {
    preparedStatement.setObject(i,paramList.get(i - 1));
  }
  final ResultSet resultSet=preparedStatement.executeQuery();
  final JSONObject jsonObject=resultSetToJsonObject(resultSet,ifOnlyOne,tableName);
  preparedStatement.close();
  return jsonObject;
}
 

@Override public String query(String q){
  Connection connection=null;
  try {
    connection=connection();
    ResultSet rs=connection.createStatement().executeQuery(q);
    return JsonTable.queryToJson(rs,q,null,null);
  }
 catch (  SQLException e) {
    throw new BeeException(e);
  }
}
 

public Location searchIp(String ip){
  Connection conn=null;
  Statement stat=null;
  try {
    conn=poolMgr.getConnection();
    stat=conn.createStatement();
    long inetAton=ValidateIP.ip2long(ip);
    String query="SELECT * FROM IP_COUNTRY WHERE ipFROM <= " + inetAton + " ORDER BY ipFROM DESC LIMIT 1";
    ResultSet result=stat.executeQuery(query);
    while (result.next()) {
      String c=result.getString("countrySHORT");
      String rn=result.getString("ipREGION");
      String cn=result.getString("ipCITY");
      String lat=result.getString("ipLATITUDE");
      String lng=result.getString("ipLONGITUDE");
      Location loc=new Location(c,rn,cn,lat,lng);
      return loc;
    }
    return Location.UNKNOWN;
  }
 catch (  Exception e) {
    e.printStackTrace();
  }
 finally {
    try {
      conn.close();
      stat.close();
    }
 catch (    Exception e) {
      e.printStackTrace();
    }
  }
  return null;
}
 

@Test public void testInitFromResource() throws Exception {
  Connection connection=null;
  Statement statement=null;
  ResultSet resultSet=null;
  try {
    H2EmbeddedDataSourceConfig config=new H2EmbeddedDataSourceConfig().setFilename(file.getAbsolutePath()).setInitScript("io/airlift/dbpool/h2.ddl").setCipher(Cipher.AES).setFilePassword("filePassword");
    H2EmbeddedDataSource dataSource=new H2EmbeddedDataSource(config);
    connection=dataSource.getConnection();
    statement=connection.createStatement();
    resultSet=statement.executeQuery("select * from message");
  }
  finally {
    closeQuietly(resultSet);
    closeQuietly(statement);
    closeQuietly(connection);
  }
}
 

public static void main(String[] args){
  ResultSet rs=null;
  try {
    rs=getResultSet();
    StringWriter sw=new StringWriter();
    CSVWriter writer=new CSVWriter(sw);
    writer.writeAll(rs,false);
    writer.close();
    System.out.println(sw);
  }
 catch (  Exception ex) {
    ex.printStackTrace();
  }
 finally {
    if (rs != null) {
      try {
        rs.close();
      }
 catch (      SQLException ignore) {
      }
    }
  }
}
 

public static ArrayList<Map<String,String>> select(Connection con,String table,Map<String,String> conditions){
  Statement stm=createStatement(con);
  ArrayList<Map<String,String>> result=new ArrayList<Map<String,String>>();
  try {
    ResultSet rs=stm.executeQuery(SQLQueryBuilder.createSelect(table,conditions));
    ResultSetMetaData meta;
    while (rs.next()) {
      meta=rs.getMetaData();
      HashMap<String,String> temp=new HashMap<String,String>();
      for (int i=0; i < meta.getColumnCount(); i++) {
        temp.put(meta.getColumnLabel(i + 1),rs.getString(i + 1));
      }
      result.add(temp);
    }
  }
 catch (  SQLException e) {
    e.printStackTrace();
  }
  return result;
}
 

/** 
 * Returns a <code>Map<String, Object></code> of properties stored in the database
 * @throws Exception
 */
synchronized Map<String,Object> load() throws Exception {
  Map<String,Object> map=new HashMap<String,Object>();
  Connection conn=null;
  PreparedStatement pstmt=null;
  ResultSet rs=null;
  try {
    conn=getConnection();
    pstmt=conn.prepareStatement(query.toString());
    rs=pstmt.executeQuery();
    while (rs.next()) {
      String key=(String)rs.getObject(keyColumnName);
      Object value=rs.getObject(valueColumnName);
      map.put(key,value);
    }
  }
 catch (  SQLException e) {
    throw e;
  }
 finally {
    close(conn,pstmt,rs);
  }
  return map;
}
 

private int count(String sql,KUID id) throws SQLException {
  PreparedStatement ps=cm.prepareStatement(sql);
  try {
    setBytes(ps,1,id);
    ResultSet rs=ps.executeQuery();
    try {
      if (rs.next()) {
        return rs.getInt(1);
      }
    }
  finally {
      Utils.close(rs);
    }
  }
  finally {
    Utils.close(ps);
  }
  return 0;
}
 

private synchronized void setup() throws SQLException {
  Connection con=null;
  Statement st=null;
  ResultSet rs=null;
  try {
    con=conPool.getValidConnection();
    st=con.createStatement();
    st.executeUpdate("CREATE TABLE IF NOT EXISTS " + tableName + " ("+ "id INTEGER AUTO_INCREMENT,"+ columnName+ " VARCHAR(255) NOT NULL,"+ columnPassword+ " VARCHAR(255) NOT NULL,"+ columnIp+ " VARCHAR(40) NOT NULL,"+ columnLastLogin+ " BIGINT,"+ "CONSTRAINT table_const_prim PRIMARY KEY (id));");
    rs=con.getMetaData().getColumns(null,null,tableName,columnIp);
    if (!rs.next()) {
      st.executeUpdate("ALTER TABLE " + tableName + " ADD COLUMN "+ columnIp+ " VARCHAR(40) NOT NULL;");
    }
    rs.close();
    rs=con.getMetaData().getColumns(null,null,tableName,columnLastLogin);
    if (!rs.next()) {
      st.executeUpdate("ALTER TABLE " + tableName + " ADD COLUMN "+ columnLastLogin+ " BIGINT;");
    }
  }
  finally {
    close(rs);
    close(st);
    close(con);
  }
  ConsoleLogger.info("MySQL Setup finished");
}
 

private synchronized void setup() throws SQLException {
  Connection con=null;
  Statement st=null;
  ResultSet rs=null;
  try {
    con=conPool.getValidConnection();
    st=con.createStatement();
    st.executeUpdate("CREATE TABLE IF NOT EXISTS " + tableName + " ("+ "id INTEGER AUTO_INCREMENT,"+ columnName+ " VARCHAR(255) NOT NULL UNIQUE,"+ columnPassword+ " VARCHAR(255) NOT NULL,"+ columnIp+ " VARCHAR(40) NOT NULL,"+ columnLastLogin+ " BIGINT,"+ "x smallint(6) DEFAULT '0',"+ "y smallint(6) DEFAULT '0',"+ "z smallint(6) DEFAULT '0',"+ "CONSTRAINT table_const_prim PRIMARY KEY (id));");
    rs=con.getMetaData().getColumns(null,null,tableName,columnIp);
    if (!rs.next()) {
      st.executeUpdate("ALTER TABLE " + tableName + " ADD COLUMN "+ columnIp+ " VARCHAR(40) NOT NULL;");
    }
    rs.close();
    rs=con.getMetaData().getColumns(null,null,tableName,columnLastLogin);
    if (!rs.next()) {
      st.executeUpdate("ALTER TABLE " + tableName + " ADD COLUMN "+ columnLastLogin+ " BIGINT;");
    }
    rs.close();
    rs=con.getMetaData().getColumns(null,null,tableName,"x");
    if (!rs.next()) {
      st.executeUpdate("ALTER TABLE " + tableName + " ADD COLUMN x smallint(6) NOT NULL DEFAULT '0' AFTER "+ columnLastLogin+ " , ADD y smallint(6) NOT NULL DEFAULT '0' AFTER x , ADD z smallint(6) NOT NULL DEFAULT '0' AFTER y;");
    }
  }
  finally {
    close(rs);
    close(st);
    close(con);
  }
  ConsoleLogger.info("MySQL Setup finished");
}
 

private synchronized void setup() throws SQLException {
  Connection con=null;
  Statement st=null;
  ResultSet rs=null;
  try {
    con=conPool.getValidConnection();
    st=con.createStatement();
    st.executeUpdate("CREATE TABLE IF NOT EXISTS " + tableName + " ("+ "id INTEGER AUTO_INCREMENT,"+ columnName+ " VARCHAR(255) NOT NULL UNIQUE,"+ columnPassword+ " VARCHAR(255) NOT NULL,"+ columnIp+ " VARCHAR(40) NOT NULL,"+ columnLastLogin+ " BIGINT,"+ "x smallint(6) DEFAULT '0',"+ "y smallint(6) DEFAULT '0',"+ "z smallint(6) DEFAULT '0',"+ "CONSTRAINT table_const_prim PRIMARY KEY (id));");
    rs=con.getMetaData().getColumns(null,null,tableName,columnIp);
    if (!rs.next()) {
      st.executeUpdate("ALTER TABLE " + tableName + " ADD COLUMN "+ columnIp+ " VARCHAR(40) NOT NULL;");
    }
    rs.close();
    rs=con.getMetaData().getColumns(null,null,tableName,columnLastLogin);
    if (!rs.next()) {
      st.executeUpdate("ALTER TABLE " + tableName + " ADD COLUMN "+ columnLastLogin+ " BIGINT;");
    }
    rs.close();
    rs=con.getMetaData().getColumns(null,null,tableName,"x");
    if (!rs.next()) {
      st.executeUpdate("ALTER TABLE " + tableName + " ADD COLUMN x smallint(6) NOT NULL DEFAULT '0' AFTER "+ columnLastLogin+ " , ADD y smallint(6) NOT NULL DEFAULT '0' AFTER x , ADD z smallint(6) NOT NULL DEFAULT '0' AFTER y;");
    }
  }
  finally {
    close(rs);
    close(st);
    close(con);
  }
  ConsoleLogger.info("MySQL Setup finished");
}
 

/** 
 * {@inheritDoc}
 */
public int getPatchLevel() throws MigrationException {
  createPatchStoreIfNeeded();
  Connection conn=null;
  PreparedStatement stmt=null;
  ResultSet rs=null;
  try {
    conn=context.getConnection();
    stmt=conn.prepareStatement(getSql("level.read"));
    stmt.setString(1,context.getSystemName());
    rs=stmt.executeQuery();
    if (rs.next()) {
      return rs.getInt(1);
    }
    SqlUtil.close(conn,stmt,rs);
    conn=null;
    stmt=null;
    rs=null;
    return 0;
  }
 catch (  SQLException e) {
    throw new MigrationException("Unable to get patch level",e);
  }
 finally {
    SqlUtil.close(conn,stmt,rs);
  }
}
 

Object prepareDropAttributeRequest() throws SQLException {
  try {
    if (!this.sql.startsWith("alter table") || this.sql.indexOf(" drop ") < 0) {
      throw new SQLException("unsupported alter table statement");
    }
    int pos=this.sql.indexOf(" ","alter table ".length() + 1);
    String domain=convertSQLIdentifierToCatalogFormat(this.sql.substring("alter table ".length(),pos).trim(),DELIMITED_IDENTIFIER_QUOTE);
    pos=this.sql.indexOf("drop ");
    String attrName=convertSQLIdentifierToCatalogFormat(this.sql.substring(pos + "drop ".length()).trim(),DELIMITED_IDENTIFIER_QUOTE);
    this.conn.removePendingColumn(domain,attrName);
    Attribute attr=new Attribute().withName(attrName).withValue(null);
    List<Attribute> attrs=new ArrayList<Attribute>();
    attrs.add(attr);
    this.sql="select itemName from " + DELIMITED_IDENTIFIER_QUOTE + domain+ DELIMITED_IDENTIFIER_QUOTE+ " where "+ DELIMITED_IDENTIFIER_QUOTE+ attrName+ DELIMITED_IDENTIFIER_QUOTE+ " is not null";
    ResultSet rs=executeQuery(this.sql);
    List<DeleteAttributesRequest> reqs=new ArrayList<DeleteAttributesRequest>();
    while (rs.next()) {
      String item=rs.getString(1);
      DeleteAttributesRequest dar=new DeleteAttributesRequest().withDomainName(domain).withItemName(item);
      dar.setAttributes(attrs);
      reqs.add(dar);
    }
    return reqs;
  }
 catch (  Exception e) {
    throw wrapIntoSqlException(e);
  }
}
 

/** 
 * Returns the list of all employees.
 * @return the list of employees.
 * @throws SQLException
 */
public List<Employee> getAllEmployees() throws SQLException {
  Connection connection=null;
  Statement statement=null;
  List<Employee> employees=new ArrayList<Employee>();
  try {
    connection=DBConnectionUtil.getConnection();
    statement=connection.createStatement();
    ResultSet resultSet=statement.executeQuery("select * from Employees");
    Employee employee=null;
    while (resultSet.next()) {
      employee=new Employee();
      employee.setEmpId(resultSet.getInt("empId"));
      employee.setFirstName(resultSet.getString("firstName"));
      employee.setLastName(resultSet.getString("lastName"));
      employee.setDepartment(resultSet.getString("department"));
      employees.add(employee);
    }
  }
  finally {
    try {
      if (statement != null) {
        statement.close();
      }
      if (connection != null) {
        connection.close();
      }
    }
 catch (    SQLException e) {
      e.printStackTrace();
    }
  }
  return employees;
}
 

public Object nullSafeGet(ResultSet resultSet,String[] names,Object owner) throws HibernateException, SQLException {
  String name=resultSet.getString(names[0]);
  Object result=null;
  if (!resultSet.wasNull()) {
    result=Enum.valueOf(clazz,name);
  }
  return result;
}
 

/** 
 * This method will return a Long, functioning like getLong, but with the ability to recognize null values, instead of converting them to zero.
 * @see ResultSet#getLong(String)
 * @see ResultSet#getObject(String)
 * @param rs The ResultSet to look in.
 * @param field The field name to look for.
 * @return The Long value of the field, may be null.
 * @throws SQLException If thrown by ResultSet.
 */
protected static Long getLong(final ResultSet rs,final String field) throws SQLException {
  if (rs.getObject(field) == null) {
    return null;
  }
  return Long.valueOf(rs.getLong(field));
}
 

public byte[] map(int index,ResultSet rs,StatementContext ctx) throws SQLException {
  Blob blob=null;
  try {
    blob=rs.getBlob(columnName);
    return getBytes(blob);
  }
  finally {
    if (blob != null) {
      blob.free();
    }
  }
}
 

/** 
 * {@inheritDoc}
 */
public Stock mapRow(ResultSet rs,int rowNum) throws SQLException {
  int index=1;
  Stock result=new Stock();
  result.setId(rs.getLong(index++));
  result.setName(rs.getString(index++));
  result.setSymbol(rs.getString(index++));
  result.setValue(rs.getBigDecimal(index++));
  result.setDate(rs.getDate(index++));
  return result;
}
 

public Object nullSafeGet(ResultSet resultSet,String[] names,Object owner) throws HibernateException, SQLException {
  Long result=null;
  Long id=resultSet.getLong(names[0]);
  if (!resultSet.wasNull()) {
    result=new Long(id);
  }
  return result;
}
 

@Override @SuppressWarnings("deprecation") public Object nullSafeGet(ResultSet rs,String[] names,Object owner) throws HibernateException, SQLException {
  I s=sct.nullSafeGet(rs,names[0]);
  if (s == null) {
    return null;
  }
  return fromNonNullInternalType(s);
}