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

MgmtEntry.Publisher createPublisher(String name) throws CaMgmtException {
  final String sql = sqlSelectPublisher;
  PreparedStatement stmt = null;
  ResultSet rs = null;
  try {
    stmt = prepareStatement(sql);
    stmt.setString(1, name);
    rs = stmt.executeQuery();

    if (!rs.next()) {
      throw new CaMgmtException("unkown Publisher " + name);
    }

    return new MgmtEntry.Publisher(new NameId(rs.getInt("ID"), name),
        rs.getString("TYPE"), rs.getString("CONF"));
  } catch (SQLException ex) {
    throw new CaMgmtException(datasource.translate(sql, ex));
  } finally {
    datasource.releaseResources(stmt, rs);
  }
}
 

protected ColumnData createColumnData(ResultSet columnSet) throws SQLException {
    final ColumnData columnData = new ColumnData();
    columnData.columnName = columnSet.getString("COLUMN_NAME");
    columnData.type = columnSet.getString("TYPE_NAME").toLowerCase();
    columnData.size = columnSet.getInt("COLUMN_SIZE");
    columnData.decimalDegits = columnSet.getInt("DECIMAL_DIGITS");
    columnData.nullable = columnSet.getInt("NULLABLE");
    columnData.defaultValue = columnSet.getString("COLUMN_DEF");

    if (columnData.defaultValue != null) {
        if ("bit".equals(columnData.type)) {
            final byte[] bits = columnData.defaultValue.getBytes();

            columnData.defaultValue = "";

            for (int i = 0; i < bits.length; i++) {
                columnData.defaultValue += bits[i];
            }
        }
    }

    columnData.description = columnSet.getString("REMARKS");

    return columnData;
}
 

public void testPRLRUMemOverflow() throws Exception {
  startVMs(1, 1);
  clientSQLExecute(
      1,
      " create table trade.bigcustomers (cid int not null, cust_name varchar(2000), cust_addr varchar(2000), cust_addr2 varchar(2000)) " +
      " MAXPARTSIZE 1 EVICTION BY LRUMEMSIZE 1 EVICTACTION overflow synchronous");
  Connection conn = TestUtil.getConnection();
  PreparedStatement ps = conn.prepareStatement("insert into trade.bigcustomers values(?, ?, ?, ?)");
  insertNBigElements(2000, ps, 0);
  Statement s = conn.createStatement();
  s.execute("select count(*) from trade.bigcustomers");
  ResultSet rs = s.getResultSet();
  int cnt = 0;
  if (rs.next()) {
    cnt = rs.getInt(1);
  }
  TestUtil.getLogger().info("cnt: "+cnt);
  assertEquals("expected 2000 elements but found " + cnt, 2000, cnt);
}
 

@Override
public AssertResult mapRow(ResultSet rs, int rowNum) throws SQLException {
	// TODO Auto-generated method stub
	int id = rs.getInt("id");
	int taskId = rs.getInt("task_id");
	String url = rs.getString("url");
	String requestContent = rs.getString("requestContent");
	String responseContent = rs.getString("responseContent");
	String assertResult = rs.getString("assertresult");
	Timestamp createTime = rs.getTimestamp("createtime");
	// 把数据封装对象
	DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	AssertResult assertResultObjest = new AssertResult();
	assertResultObjest.setId(id);
	assertResultObjest.setTaskId(taskId);
	assertResultObjest.setUrl(url);
	assertResultObjest.setRequestContent(requestContent);
	assertResultObjest.setResponseContent(responseContent);
	assertResultObjest.setAssertResult(assertResult);
	assertResultObjest.setCreateTime(df.format(createTime));
	return assertResultObjest;
}
 

public void fetch(){
  try{
   ResultSet rs = executor.getConnection().getMetaData().getColumns(null, tableInfo.getSchemaName(), tableInfo.getTableName(), null);
   FullyQualifiedTableName = tableInfo.getFullyQualifiedTableName().toUpperCase();
  
  while ( rs.next() ){
    boolean isNull;
    String columnName = rs.getString("COLUMN_NAME");
    int columnType = rs.getInt("DATA_TYPE");   
    int columnSize = rs.getInt("COLUMN_SIZE");
    if ( rs.getInt("NULLABLE") == 0 ) 
       isNull = false;
    else 
      isNull = true;
    fullyQualifiedColumnName = FullyQualifiedTableName + "." + columnName;
    columnList.add(new ColumnInfo(columnName , columnType , columnSize , isNull , valueGenerator.getValueList( fullyQualifiedColumnName,  columnType ,  columnSize )));  
  }
  
  tableInfo.setColumnList(columnList);
  rs.close();
  } catch (SQLException se ) {
    throw new TestException ("Error while retrieving the column information " + TestHelper.getStackTrace(se));
  }
}
 

public static Organism fromResultSet(ResultSet resultSet) throws SQLException {
  return new Organism(
      resultSet.getString(1),
      resultSet.getString(2),
      resultSet.getInt(3)
  );
}
 

/**
 * DataStream 调用一次 run() 方法用来获取数据
 *
 * @param ctx
 * @throws Exception
 */
@Override
public void run(SourceContext<Student> ctx) throws Exception {
    ResultSet resultSet = ps.executeQuery();
    while (resultSet.next()) {
        Student student = new Student(
                resultSet.getInt("id"),
                resultSet.getString("name").trim(),
                resultSet.getString("password").trim(),
                resultSet.getInt("age"));
        ctx.collect(student);
    }
}
 

/**
 * Returns an array of the primary keys found in a given table. An array of size
 * 0 is returned in the event of a db access error.
 * 
 * @param table The entity node representing the table from which we wish to get
 *              the primary IDs
 * @return The primary IDs of all records found in the given table
 */
public static int[] getPKs(final EntityNode table) {
	final JDBCDriver dbd;
	final ColumnNaming cn;
	final ResultSet result;

	try {
		dbd = table.getMModel().getDatabase().getJDBCDriver();
		cn = new ColumnNaming(dbd);
		result = dbd.executeQuery("SELECT * FROM " + table.getName());

		result.last(); // move to last row to get row count

		final int[] pKs = new int[result.getRow()];

		// move back to first row
		result.beforeFirst();
		result.next();

		final String pIdName = cn.tablePK(table);

		for (int i = 0; i < pKs.length; i++) {
			pKs[i] = result.getInt(pIdName);

			result.next();
		}

		return pKs;
	} catch (SQLException e) {
		System.err.println("Error in DatabaseUtil: " + e.getMessage());

		return new int[0];
	}
}
 

public static List<DUnitMethodInfo> getAllDUnitMethods() throws SQLException {
  ArrayList<DUnitMethodInfo> ducis = new ArrayList<DUnitMethodInfo>();
  ResultSet rs = Database
      .executeQuery("SELECT id,name,test_class_id FROM dunit_test_method");
  while (rs.next()) {
    DUnitMethodInfo duc = new DUnitMethodInfo(rs.getInt(1), rs.getString(2),
        rs.getInt(3));
    ducis.add(duc);
  }
  return ducis;
}
 

public void map(Key key, Row value, Context context) throws IOException , InterruptedException {           
  try {        
    if ( ! value.getEventType().equals(Type.AFTER_DELETE)) {
    ResultSet rs = value.getRowAsResultSet();
    int oid=rs.getInt("oid");
    Key k = new Key();
    k.setKey(CacheServerHelper.serialize(oid));
    context.write(k, new TradeSellOrdersRow(oid, rs.getInt("cid"), rs.getInt("sid"), rs.getInt("qty"),  rs.getString("status"),  rs.getBigDecimal("ask"), rs.getTimestamp("order_time") , rs.getInt("tid")) );
    }
  } catch (SQLException se) {
    System.err.println("mapper -  -Error logging result set" + se);
    throw new  IOException(se);
  }
}
 

@Test
public void testScanGreaterEqualsLess() throws Exception{
    ResultSet rs=methodWatcher.executeQuery(format("select * from %s where pk_1>= 'pk_1_1' and pk_1 <'pk_1_"+(pk1Size-2)+"'",this.getTableReference(TABLE_NAME)));
    List<String> results=Lists.newArrayListWithExpectedSize(1);
    while(rs.next()){
        String pk1=rs.getString(1);
        String pk2=rs.getString(2);
        int val=rs.getInt(3);
        Pair<String, String> pair=Pair.newPair(pk1,pk2);
        Assert.assertTrue("could not find pair!",correctData.containsKey(pair));
        Assert.assertEquals("Incorrect value for pair!",correctData.get(pair).intValue(),val);
        results.add(String.format("pk_1: %s,pk_2: %s, val:%d",pk1,pk2,val));
    }
    Assert.assertEquals("Incorrect number of rows returned!",pk2Size*(pk1Size-2),results.size());
}
 

public int queryUserGroupCount(String siteUserId) throws SQLException {
	long startTime = System.currentTimeMillis();
	int result = 0;
	String sql = "SELECT COUNT(*) FROM " + USER_GROUP_TABLE + " AS a LEFT JOIN " + SQLConst.SITE_GROUP_PROFILE
			+ " AS b WHERE a.site_group_id=b.site_group_id AND b.group_status>0 AND a.site_user_id=?;";

	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try {
		conn = DatabaseConnection.getSlaveConnection();
		pst = conn.prepareStatement(sql);
		pst.setString(1, siteUserId);

		rs = pst.executeQuery();
		if (rs.next()) {
			result = rs.getInt(1);
		}
	} catch (Exception e) {
		throw e;
	} finally {
		DatabaseConnection.returnConnection(conn, pst, rs);
	}

	LogUtils.dbDebugLog(logger, startTime, result, sql, siteUserId);
	return result;
}
 

protected <V> V getScalarFromResultSet(ResultSet rs, Class<V> returnType) throws SQLException {
    Object value = null;

    if (Integer.class == returnType) {
        value = rs.getInt(1);
    } else if (Long.class == returnType) {
        value = rs.getLong(1);
    } else if (String.class == returnType) {
        value = rs.getString(1);
    } else if (Boolean.class == returnType) {
        value = rs.getBoolean(1);
    } else if (Double.class == returnType) {
        value = rs.getDouble(1);
    } else if (Date.class == returnType) {
        value = rs.getDate(1);
    } else if (Timestamp.class == returnType) {
        value = rs.getTimestamp(1);
    } else {
        value = rs.getObject(1);
    }

    if (null == value) {
        throw new NullPointerException("Cannot get value from ResultSet of type " + returnType.getName());
    }

    return returnType.cast(value);
}
 

private int findOrderCntAccAccId(int acc) throws SQLException {
  int results = 0;
  if (findOrderCntAccAccIdPS == null) {
    findOrderCntAccAccIdPS = this.connection.prepareStatement(findOrderCntAccAccIdStr);
  }
  findOrderCntAccAccIdPS.setInt(1, acc);
  ResultSet rs = findOrderCntAccAccIdPS.executeQuery();
  while (rs.next()) {
    rs.getInt(1);
    ++results;
  }
  return results;
}
 

public int countRecordsWithText(String text){
    try (Connection conn = getDbConnection();
         PreparedStatement st = conn.prepareStatement("select count(*) from text where text = ?")) {
        st.setString(1, text);
        ResultSet rs = st.executeQuery();
        rs.next();
        return rs.getInt(1);
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return 0;
}
 

@Test
public void testScanWithGreaterThanOrEqualsOperator() throws Exception{
    ResultSet rs=methodWatcher.executeQuery(format("select * from %s where pk_1 >= 'pk_1_1'",this.getTableReference(TABLE_NAME)));
    List<String> results=Lists.newArrayListWithExpectedSize(1);
    while(rs.next()){
        String pk1=rs.getString(1);
        String pk2=rs.getString(2);
        int val=rs.getInt(3);
        Pair<String, String> pair=Pair.newPair(pk1,pk2);
        Assert.assertTrue("could not find pair!",correctData.containsKey(pair));
        Assert.assertEquals("Incorrect value for pair!",correctData.get(pair).intValue(),val);
        results.add(String.format("pk_1: %s,pk_2: %s, val:%d",pk1,pk2,val));
    }
    Assert.assertEquals("Incorrect number of rows returned!",pk2Size*(pk1Size-1),results.size());
}
 

public static int create(int leaderId, int leaderId2) {
    try {
        Connection con = DatabaseConnection.getConnection();
        PreparedStatement ps = con.prepareStatement("SELECT id FROM sidekicks WHERE firstid = ? OR secondid = ? OR firstid = ? OR secondid = ?");
        ps.setInt(1, leaderId);
        ps.setInt(2, leaderId2);
        ps.setInt(3, leaderId);
        ps.setInt(4, leaderId2);
        ResultSet rs = ps.executeQuery();
        if (rs.first()) {
            rs.close();
            ps.close();
            return 0;
        }
        ps.close();
        rs.close();
        ps = con.prepareStatement("INSERT INTO sidekicks (firstid, secondid) VALUES (?, ?)", 1);
        ps.setInt(1, leaderId);
        ps.setInt(2, leaderId2);
        ps.executeUpdate();
        rs = ps.getGeneratedKeys();
        int ret = 0;
        if (rs.next()) {
            ret = rs.getInt(1);
        }
        rs.close();
        ps.close();
        return ret;
    } catch (SQLException se) {
        System.err.println("Error create sidekick" + se);
    }
    return 0;
}
 

public static Query<Map<String, Ping>> fetchPingDataOfServerByGeolocation(UUID serverUUID) {
    String selectPingOfServer = SELECT +
            PingTable.MAX_PING + ", " +
            PingTable.MIN_PING + ", " +
            PingTable.AVG_PING + ", " +
            PingTable.USER_UUID + ", " +
            PingTable.SERVER_UUID +
            FROM + PingTable.TABLE_NAME;

    String selectGeolocations = SELECT +
            GeoInfoTable.USER_UUID + ", " +
            GeoInfoTable.GEOLOCATION + ", " +
            GeoInfoTable.LAST_USED +
            FROM + GeoInfoTable.TABLE_NAME;
    String selectLatestGeolocationDate = SELECT +
            GeoInfoTable.USER_UUID + ", " +
            "MAX(" + GeoInfoTable.LAST_USED + ") as m" +
            FROM + GeoInfoTable.TABLE_NAME +
            GROUP_BY + GeoInfoTable.USER_UUID;

    String selectPingByGeolocation = SELECT + GeoInfoTable.GEOLOCATION +
            ", MIN(" + PingTable.MIN_PING + ") as minPing" +
            ", MAX(" + PingTable.MAX_PING + ") as maxPing" +
            ", AVG(" + PingTable.AVG_PING + ") as avgPing" +
            FROM + "(" + selectGeolocations + ") AS q1" +
            INNER_JOIN + "(" + selectLatestGeolocationDate + ") AS q2 ON q1.uuid = q2.uuid" +
            INNER_JOIN + '(' + selectPingOfServer + ") sp on sp." + PingTable.USER_UUID + "=q1.uuid" +
            WHERE + GeoInfoTable.LAST_USED + "=m" +
            AND + "sp." + PingTable.SERVER_UUID + "=?" +
            GROUP_BY + GeoInfoTable.GEOLOCATION;

    return new QueryStatement<Map<String, Ping>>(selectPingByGeolocation) {
        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setString(1, serverUUID.toString());
        }

        @Override
        public Map<String, Ping> processResults(ResultSet set) throws SQLException {
            // TreeMap to sort alphabetically
            Map<String, Ping> pingByGeolocation = new TreeMap<>();
            while (set.next()) {
                Ping ping = new Ping(
                        0L,
                        serverUUID,
                        set.getInt("minPing"),
                        set.getInt("maxPing"),
                        (int) set.getDouble("avgPing")
                );
                pingByGeolocation.put(set.getString(GeoInfoTable.GEOLOCATION), ping);
            }
            return pingByGeolocation;
        }
    };
}
 

/**
   * Get the column value returned by a SELECT statement for a specific entity attribute
   * @param resultSet
   * @param mapping
   * @param entityClass
   * @return
   */
  public Object getColumnValueFromResultSet(ResultSet resultSet, AttributeMapping mapping, Class entityClass)
  {
    // calling getObject on Oracle DB returns correct type, however, on SQLLite is alywas return String
    // so we need to proper get method
    String attrName = mapping.getAttributeName();
    Class javaType = EntityUtils.getJavaType(entityClass, attrName);
    Object value = null;
    try
    {
      if (resultSet.getObject(mapping.getColumnName()) == null)
      {
        return value;
      }
      else if (javaType == String.class)
      {
        value = resultSet.getString(mapping.getColumnName());
      }
      else if (javaType == java.util.Date.class || javaType == java.sql.Date.class)
      {
        // dates are saved to SQLIte as timestamp to not loose time part
        Timestamp ts = resultSet.getTimestamp(mapping.getColumnName());
//        value = resultSet.getDate(mapping.getColumnName());
        // ts can be null when stored using wrong format, should be dd-MM-yyyy hh:mm:ss
        if (ts!=null)
        {
          value = new java.sql.Date(ts.getTime());          
        }
      }
      else if (javaType == Time.class || javaType == Timestamp.class)
      {
        value = resultSet.getTime(mapping.getColumnName());
      }
      else if (javaType == Integer.class)
      {
        value = new Integer(resultSet.getInt(mapping.getColumnName()));
      }
      else if (javaType == Long.class)
      {
        value = new Long(resultSet.getLong(mapping.getColumnName()));
      }
      else if (javaType == Float.class)
      {
        value = new Float(resultSet.getFloat(mapping.getColumnName()));
      }
      else if (javaType == Double.class)
      {
        value = new Float(resultSet.getDouble(mapping.getColumnName()));
      }
      else if (javaType == Double.class)
      {
        value = new Float(resultSet.getDouble(mapping.getColumnName()));
      }
      else if (javaType == Clob.class)
      {
        value = resultSet.getClob(mapping.getColumnName());
      }
      else if (javaType == Blob.class)
      {
        value = resultSet.getBlob(mapping.getColumnName());
      }
      else if (javaType == Short.class)
      {
        value = new Short(resultSet.getShort(mapping.getColumnName()));
      }
      else
      {
        value = resultSet.getObject(mapping.getColumnName());
      }
    }
    catch (SQLException e)
    {
      throw new AdfException("Error getting SQL resultSet value for column " + mapping.getColumnName() + ": " +
                             e.getLocalizedMessage(), AdfException.ERROR);
    }
    return value;
  }
 

public static void getTypeInfo(DatabaseMetaData dmd,PrintStream out)
  throws SQLException
  {
    ResultSet rs = dmd.getTypeInfo();
    out.println("Submitted getTypeInfo request");
    while (rs.next())
    {
      // 1.TYPE_NAME String => Type name
      String typeName = rs.getString(1);

      // 2.DATA_TYPE short => SQL data type from java.sql.Types
      short dataType = rs.getShort(2);

      // 3.PRECISION int => maximum precision
      int precision = rs.getInt(3);

      // 4.LITERAL_PREFIX String => prefix used to quote a literal
      // (may be null)
      String literalPrefix = rs.getString(4);

      // 5.LITERAL_SUFFIX String => suffix used to quote a literal
      // (may be null)
      String literalSuffix = rs.getString(5);

      // 6.CREATE_PARAMS String => parameters used in creating the type
      // (may be null)
      String createParams = rs.getString(6);

      // 7.NULLABLE short => can you use NULL for this type?
//    typeNoNulls - does not allow NULL values
//    typeNullable - allows NULL values
//    typeNullableUnknown - nullability unknown
      short nullable = rs.getShort(7);

      // 8.CASE_SENSITIVE boolean=> is it case sensitive?
      boolean caseSensitive = rs.getBoolean(8);

      // 9.SEARCHABLE short => can you use "WHERE" based on this type:
//    typePredNone - No support
//    typePredChar - Only supported with WHERE .. LIKE
//    typePredBasic - Supported except for WHERE .. LIKE
//    typeSearchable - Supported for all WHERE ..
      short searchable = rs.getShort(9);

      // 10.UNSIGNED_ATTRIBUTE boolean => is it unsigned?
      boolean unsignedAttribute = rs.getBoolean(10);

      // 11.FIXED_PREC_SCALE boolean => can it be a money value?
      boolean fixedPrecScale = rs.getBoolean(11);

      // 12.AUTO_INCREMENT boolean => can it be used for an
      // auto-increment value?
      boolean autoIncrement = rs.getBoolean(12);

      // 13.LOCAL_TYPE_NAME String => localized version of type name
      // (may be null)
      String localTypeName = rs.getString(13);

      // 14.MINIMUM_SCALE short => minimum scale supported
      short minimumScale = rs.getShort(14);

      // 15.MAXIMUM_SCALE short => maximum scale supported
      short maximumScale = rs.getShort(15);

      // 16.SQL_DATA_TYPE int => unused

      // 17.SQL_DATETIME_SUB int => unused

      // 18.NUM_PREC_RADIX int => usually 2 or 10

      //out.println(typeName);
    }
    rs.close();
  }