Java PreparedStatement.executeQuery方法代码示例

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Calculates the unique number of patients who have a distinct disease. Inserts result of query
 * into the occurrences column. Calculates the total living population of patients. Inserts result
 * into the population column. Calls for completeSyntheaFields to calculate the prevalence rate
 * and percent.
 */
private static void allConditions(Connection connection, List<LinkedHashMap<String, String>> data)
    throws SQLException {

  PreparedStatement stmt = connection
      .prepareStatement("select count(*) from person where person.DATE_OF_DEATH is null");
  ResultSet rs = stmt.executeQuery();
  rs.next();
  int totalPopulation = rs.getInt(1);

  stmt = connection.prepareStatement(
      "select distinct c.display as DistinctDisplay, "
      + "count(distinct c.person_id) as CountDisplay \n"
      + "from condition c, person p\n" 
      + "where c.person_id = p.id\n"
      + "and p.date_of_death is null\n" 
      + "group by c.display\n" 
      + "order by c.display ASC");
  rs = stmt.executeQuery();
  while (rs.next()) {
    String disease = rs.getString("DistinctDisplay");
    int count = rs.getInt("CountDisplay");
    LinkedHashMap<String, String> line = new LinkedHashMap<String, String>();
    line.put(ITEM, disease);
    line.put(OCCUR, Integer.toString(count));
    line.put(POP, Integer.toString(totalPopulation));
    data.add(line);
    completeSyntheaFields(connection, line);
  }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public Metadata getGroupMetadata(Connection txn, GroupId g)
		throws DbException {
	PreparedStatement ps = null;
	ResultSet rs = null;
	try {
		String sql = "SELECT key, value FROM groupMetadata"
				+ " WHERE groupId = ?";
		ps = txn.prepareStatement(sql);
		ps.setBytes(1, g.getBytes());
		rs = ps.executeQuery();
		Metadata metadata = new Metadata();
		while (rs.next()) metadata.put(rs.getString(1), rs.getBytes(2));
		rs.close();
		ps.close();
		return metadata;
	} catch (SQLException e) {
		tryToClose(rs);
		tryToClose(ps);
		throw new DbException(e);
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
protected int runCompareTables(Connection connection, String table1,
    String table2) throws SQLException {
  PreparedStatement stmt;
  stmt = connection.prepareStatement(
      "select count(*) from (select * from (select * from "
              + table1
              + " minus select * from "
              + table2
              + ") union all select * from (select * from "
              + table2
              + " minus select * from " + table1 + "))");
  ResultSet results = stmt.executeQuery();
  results.next();
  int numDifferences = results.getInt(1);
  return numDifferences;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public ArrayList<AcademicInstitute> getAllAcademiclnstitution() throws SQLException {
	ArrayList<AcademicInstitute> a = new ArrayList<AcademicInstitute>();
	AcademicInstitute academic = null;
	PreparedStatement stm = c.prepareStatement(SQLStatements.selectAcademicInstitute);
	ResultSet rs = stm.executeQuery();
	while (rs.next()) {
		City city = getCityById(rs.getInt(DataContract.AcademicInstituteTable.COL_CITY));
		Area area = getAreaById(rs.getInt(DataContract.AcademicInstituteTable.COL_AREA));
		academic = new AcademicInstitute(rs.getInt(DataContract.AcademicInstituteTable.COL_ID),
				rs.getString(DataContract.AcademicInstituteTable.COL_NAME), area.getName(), city.getName(),
				area.getId(), city.getId());
		a.add(academic);
	}
	rs.close();
	stm.close();

	return a;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void retrieveGlobal() {
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        final Connection con = DatabaseConnection.getConnection();
        ps = con.prepareStatement("SELECT * FROM drop_data_global WHERE chance > 0");
        rs = ps.executeQuery();

        while (rs.next()) {
            globaldrops.add(
                    new MonsterGlobalDropEntry(
                    rs.getInt("itemid"),
                    rs.getInt("chance"),
                    rs.getInt("continent"),
                    rs.getByte("dropType"),
                    rs.getInt("minimum_quantity"),
                    rs.getInt("maximum_quantity"),
                    rs.getShort("questid")));
        }
        rs.close();
        ps.close();
    } catch (SQLException e) {
        System.err.println("Error retrieving drop" + e);
    } finally {
        try {
            if (ps != null) {
                ps.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ignore) {
        }
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
* Gets a description of the foreign key columns in the foreign key
* table that reference the primary key columns of the primary key
* table (describe how one table imports another's key.) This
* should normally return a single foreign key/primary key pair
* (most tables only import a foreign key from a table once.)  They
* are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and
* KEY_SEQ.
*/
public ResultSet getCrossReference(String catalog, String schema, String table, String foreignCatalog, String foreignSchema, String foreignTable) throws SQLException
{
    if (getCapitializeUsername() && schema != null) schema = schema.toUpperCase();
    String query = (String)properties.get(PROP_CROSSREF_QUERY);
    if (query != null) {
        if (con != null) {
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1, catalog);
            stmt.setString(2, schema);
            stmt.setString(3, table);
            stmt.setString(4, foreignCatalog);
            stmt.setString(5, foreignSchema);
            stmt.setString(6, foreignTable);
            return stmt.executeQuery();
        } else throw new SQLException(bundle.getString("EXC_NoConnection")); // NOI18N
    }

    if (dmd == null) throw new SQLException(bundle.getString("EXC_NoDBMetadata")); // NOI18N
    return dmd.getCrossReference(catalog, schema, table, foreignCatalog, foreignSchema, foreignTable);
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private boolean rowExists(byte[] id) throws SQLException {
	assertNotNull(id);
	String sql = "SELECT NULL FROM foo WHERE uniqueID = ?";
	try {
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setBytes(1, id);
		ResultSet rs = ps.executeQuery();
		boolean found = rs.next();
		assertFalse(rs.next());
		rs.close();
		ps.close();
		return found;
	} catch (SQLException e) {
		connection.close();
		throw e;
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public List<Object> getObjects(String sql, Class<?> clazz, Object... parameters) throws Exception {
    if (null == sql) {
        throw new RuntimeException("sql is null");
    }
    PreparedStatement ps = null;
    ResultSet rs = null;
    ps = conn.prepareStatement(sql);

    if (null != parameters) {
        logger.debug("parameters.length==" + parameters.length);
        // 设置参数
        for (int i = 0; i < parameters.length; i++) {
            ps.setObject(i + 1, parameters[i]);
        }
    }
    rs = ps.executeQuery();

    List<Object> objects = new ArrayList<Object>();
    // Method[] ms = clazz.getMethods();
    while (rs.next()) {
        objects.add(getBean(clazz, rs));
    }
    free(rs, ps);
    return objects;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public Set<OutputTopicVo> queryOutputTopic(String key) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    Set<OutputTopicVo> set = new HashSet<OutputTopicVo>();
    ExtractorVo extVo = ExtractorConfigContainer.getInstances().getExtractorConfig();
    try {
        conn = DataSourceContainer.getInstances().getConn(key);
        ps = conn.prepareStatement(getQueryConfigSql());
        ps.setString(1, extVo.getDbName());
        ps.setString(2, extVo.getDbType());
        rs = ps.executeQuery();
        while (rs.next()) {
        	OutputTopicVo vo = new OutputTopicVo();
        	vo.setDsName(rs.getString("ds_name"));
        	vo.setDsType(rs.getString("ds_type"));
        	vo.setTopic(rs.getString("topic"));
            vo.setControlTopic(rs.getString("ctrl_topic"));
        	set.add(vo);
        }
    } catch (Exception e) {
    	logger.error("[db-LoadDbusConfigDao]", e);
    } finally {
        DBUtil.close(rs);
        DBUtil.close(ps);
        DBUtil.close(conn);
    }
    return set;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public static long getCurrentScn(Connection connection) throws SQLException {
  String sql = "SELECT current_scn FROM v$database";
  PreparedStatement statement = connection.prepareStatement(sql);
  ResultSet resultSet = statement.executeQuery();

  resultSet.next();
  long result = resultSet.getLong(1);
  resultSet.close();
  statement.close();

  return result;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public Map<MessageId, State> getMessageDependents(Connection txn,
		MessageId m) throws DbException {
	PreparedStatement ps = null;
	ResultSet rs = null;
	try {
		String sql = "SELECT d.messageId, m.state"
				+ " FROM messageDependencies AS d"
				+ " JOIN messages AS m"
				+ " ON d.messageId = m.messageId"
				+ " WHERE dependencyId = ?";
		ps = txn.prepareStatement(sql);
		ps.setBytes(1, m.getBytes());
		rs = ps.executeQuery();
		Map<MessageId, State> dependents = new HashMap<MessageId, State>();
		while (rs.next()) {
			MessageId dependent = new MessageId(rs.getBytes(1));
			State state = State.fromValue(rs.getInt(2));
			dependents.put(dependent, state);
		}
		rs.close();
		ps.close();
		return dependents;
	} catch (SQLException e) {
		tryToClose(rs);
		tryToClose(ps);
		throw new DbException(e);
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public List<ScenarioMetaInfo> getScenarioMetaInfo( int scenarioId ) throws DatabaseAccessException {

    List<ScenarioMetaInfo> scenarioMetaInfoList = new ArrayList<>();
    Connection connection = getConnection();
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        statement = connection.prepareStatement("SELECT * FROM \"tScenarioMetainfo\" WHERE scenarioId = "
                                                + scenarioId);
        rs = statement.executeQuery();
        while (rs.next()) {
            ScenarioMetaInfo runMetainfo = new ScenarioMetaInfo();
            runMetainfo.metaInfoId = rs.getInt("metaInfoId");
            runMetainfo.scenarioId = rs.getInt("scenarioId");
            runMetainfo.name = rs.getString("name");
            runMetainfo.value = rs.getString("value");
            scenarioMetaInfoList.add(runMetainfo);
        }
    } catch (Exception e) {
        throw new DatabaseAccessException("Error retrieving scenario metainfo for scenario with id '"
                                          + scenarioId + "'", e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, statement);
    }

    return scenarioMetaInfoList;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
    * @see TradeServices#getAllQuotes(String)
    */
public Collection<QuoteDataBean> getAllQuotes() throws Exception {
	Collection<QuoteDataBean> quotes = new ArrayList<QuoteDataBean>();
	QuoteDataBean quoteData = null;

	Connection conn = null;
	try {
		conn = getConn();

		PreparedStatement stmt = getStatement(conn, getAllQuotesSQL);

		ResultSet rs = stmt.executeQuery();

		while (!rs.next()) {
			quoteData = getQuoteDataFromResultSet(rs);
			quotes.add(quoteData);
		}

		stmt.close();
	} catch (Exception e) {
		Log.error("TradeDirect:getAllQuotes", e);
		rollBack(conn, e);
	} finally {
		releaseConn(conn);
	}

	return quotes;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public static int createGuild(int leaderId, String name) {
    try {
        Connection con = DatabaseConnection.getConnection();
        PreparedStatement ps = con.prepareStatement("SELECT guildid FROM guilds WHERE name = ?");
        ps.setString(1, name);
        ResultSet rs = ps.executeQuery();
        if (rs.first()) {
            ps.close();
            rs.close();
            return 0;
        }
        ps.close();
        rs.close();
        ps = con.prepareStatement("INSERT INTO guilds (`leader`, `name`, `signature`) VALUES (?, ?, ?)");
        ps.setInt(1, leaderId);
        ps.setString(2, name);
        ps.setInt(3, (int) System.currentTimeMillis());
        ps.execute();
        ps.close();
        ps = con.prepareStatement("SELECT guildid FROM guilds WHERE leader = ?");
        ps.setInt(1, leaderId);
        rs = ps.executeQuery();
        rs.first();
        int guildid = rs.getInt("guildid");
        rs.close();
        ps.close();
        return guildid;
    } catch (Exception e) {
        return 0;
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public static int getWantedLevel(Player player) throws SQLException {
    PreparedStatement stmt = plugin.getDb().getConnection().prepareStatement("SELECT locked_wanted FROM users_locked WHERE uuid = '" + player.getUniqueId() + "' LIMIT 1");
    ResultSet set = stmt.executeQuery();
    set.next();
    return set.getInt("locked_wanted");
}