@Override
public Set<EmpVO> getEmpsByDeptno(Integer deptno) {
Set<EmpVO> set = new LinkedHashSet<EmpVO>();
EmpVO empVO = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, userid, passwd);
pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT);
pstmt.setInt(1, deptno);
rs = pstmt.executeQuery();
while (rs.next()) {
empVO = new EmpVO();
empVO.setEmpno(rs.getInt("empno"));
empVO.setEname(rs.getString("ename"));
empVO.setJob(rs.getString("job"));
empVO.setHiredate(rs.getDate("hiredate"));
empVO.setSal(rs.getDouble("sal"));
empVO.setComm(rs.getDouble("comm"));
empVO.setDeptno(rs.getInt("deptno"));
set.add(empVO); // Store the row in the vector
}
// Handle any driver errors
} catch (ClassNotFoundException e) {
throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
// Handle any SQL errors
} catch (SQLException se) {
throw new RuntimeException("A database error occured. " + se.getMessage());
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (con != null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace(System.err);
}
}
}
return set;
}
@WebMethod
public List<Asset> getFasilitasAset(int idAset) throws SQLException {
String query =
"SELECT * FROM "
+ FASILITAS_ASET_TABLE
+ " JOIN "
+ ASSET_TABLE
+ " ON "
+ FASILITAS_ASET_TABLE
+ ".id_fasilitas="
+ ASSET_TABLE
+ ".id"
+ " WHERE id_aset = "
+ idAset;
System.out.println(query);
ResultSet rs = executeQuery(query);
List<Asset> assets = null;
while (rs.next()) {
if (rs.isFirst()) assets = new ArrayList<Asset>();
Asset asset = new Asset();
asset.setId(rs.getInt("id"));
asset.setNama(rs.getString("nama"));
asset.setKategori(rs.getString("kategori"));
asset.setKategori(rs.getString("jenis"));
asset.setTanggalMasuk(rs.getDate("tanggal_masuk"));
asset.setKondisi(AssetCondition.getInstance(rs.getString("kondisi")));
asset.setPemilik(rs.getString("pemilik"));
asset.setVendor(getVendorAset(rs.getInt("id_vendor")));
asset.setHarga(rs.getString("harga"));
asset.setPublicAsset(rs.getBoolean("is_public"));
assets.add(asset);
}
return assets;
}
/**
* Test importing an unsupported field e.g. datetime, the program should continue and assign null
* value to the field.
*
* @throws Exception
*/
@Test
public void testImportUnsupportedField() throws Exception {
ConnectionProperties p = getConnectionProperties();
Connection cn = DatabaseConnection.getConnection(p);
try {
List<Field> verifiedFields = new Vector<Field>();
String[] fields = "ListingId, Title".split(",");
String tableName = "Listings";
DatabaseConnection.verifyTable(p, tableName, fields, verifiedFields);
Collection<JsonNode> importNodes = new LinkedList<JsonNode>();
JsonNodeFactory f = JsonNodeFactory.instance;
int listingId = 1559350;
ObjectNode n;
n = new ObjectNode(f);
n.put("ListingId", listingId);
importNodes.add(n);
JsonArrayImporter importer = new JsonArrayImporter(p);
importer.doImport(tableName, verifiedFields, importNodes);
Statement st = cn.createStatement();
ResultSet rs = st.executeQuery("SELECT ListingId, Title FROM Listings");
assertTrue("Expected result set to contain a record", rs.next());
assertEquals(listingId, rs.getInt("ListingId"));
assertEquals(null, rs.getString("Title"));
} finally {
cn.close();
}
}
public StoredTransactionOutput getTransactionOutput(Sha256Hash hash, long index)
throws BlockStoreException {
maybeConnect();
PreparedStatement s = null;
try {
s =
conn.get()
.prepareStatement(
"SELECT height, value, scriptBytes FROM openOutputs "
+ "WHERE hash = ? AND index = ?");
s.setBytes(1, hash.getBytes());
// index is actually an unsigned int
s.setInt(2, (int) index);
ResultSet results = s.executeQuery();
if (!results.next()) {
return null;
}
// Parse it.
int height = results.getInt(1);
BigInteger value = new BigInteger(results.getBytes(2));
// Tell the StoredTransactionOutput that we are a coinbase, as that is encoded in height
StoredTransactionOutput txout =
new StoredTransactionOutput(hash, index, value, height, true, results.getBytes(3));
return txout;
} catch (SQLException ex) {
throw new BlockStoreException(ex);
} finally {
if (s != null)
try {
s.close();
} catch (SQLException e) {
throw new BlockStoreException("Failed to close PreparedStatement");
}
}
}
/**
* Transforma os dados obtidos atraves de uma consulta a tabela de fatos do prestador em um objeto
* do tipo ResumoFato
*
* @param rset - um ResultSet contendo o resultado da consulta a tabela de fatos do prestador
* @return um objeto do tipo ResumoFato
*/
private static final ResumoFato montaResumoFato(ResultSet rset) throws SQLException {
/* double qtdeNotas;
String strCodServico;
String strDsServico;
double qtdeServico;
double valorTotal;
strCodServico = rset.getString("pef_codigo");
strDsServico = rset.getString("Descricao");
qtdeNotas = rset.getDouble("QtdeNotas");
qtdeServico = rset.getDouble("QtdeServicos");
valorTotal = rset.getDouble("ValorTotal");
return ( new ResumoFato(qtdeNotas, strCodServico, strDsServico, qtdeServico,
valorTotal) );*/
int tipoNota = 0;
int qtdeNotas = 0;
int tipoRegistro = 0;
double valorTotal = 0;
tipoNota = rset.getInt("tipo_nota");
tipoRegistro = rset.getInt("tipo_registro");
qtdeNotas = rset.getInt("qtde_notas");
valorTotal = rset.getDouble("valor_total");
return (new ResumoFato(tipoNota, tipoRegistro, qtdeNotas, valorTotal));
} // montaResumoFato()
// Needs a connection so it can fetch more stuff lazily
Copy(ResultSet rs) throws SQLException {
super();
copyId = rs.getInt("copy#");
bibId = rs.getInt("bib#");
note = rs.getString("pac_note");
location = rs.getString("location");
locationName = rs.getString("location_name");
collectionDescr = rs.getString("collection_descr");
collection = rs.getString("collection");
callNumber =
new CallNumber(
rs.getString("call_number"),
rs.getString("call_type"),
rs.getString("copy_number"),
rs.getString("call_type_hint"));
callType = rs.getString("call_type");
callTypeHint = rs.getString("call_type_hint");
callTypeName = rs.getString("call_type_name");
mediaType = rs.getString("media_type");
mediaTypeDescr = rs.getString("media_descr");
summaryOfHoldings = rs.getBoolean("summary_of_holdings");
itemType = rs.getString("itype");
itemTypeDescr = rs.getString("idescr");
}
// metoda za pretragu po broju stanovnika
public ArrayList<Country> SearchCountryPopulation(long Population) {
ArrayList<Country> countries = new ArrayList<Country>();
try {
Connection connection = getConnected("world");
PreparedStatement statement =
connection.prepareStatement(
"SELECT * FROM country WHERE Population <= " + Population + ";");
ResultSet result = statement.executeQuery();
while (result.next()) {
countries.add(
new Country(
result.getString("Code"),
result.getString("Name"),
result.getString("Continent"),
result.getString("Region"),
result.getDouble("SurfaceArea"),
result.getInt("IndepYear"),
result.getLong("Population"),
result.getDouble("LifeExpectancy"),
result.getDouble("GNP"),
result.getDouble("GNPOld"),
result.getString("LocalName"),
result.getString("GovernmentForm"),
result.getString("HeadOfState"),
result.getInt("Capital"),
result.getString("Code2")));
}
connection.close();
} catch (Exception e) {
System.out.println(e.toString());
return null;
}
return countries;
}
private Operator fromResultSet(ResultSet rs) throws SQLException {
long id = rs.getLong(1);
if (rs.wasNull()) id = -1;
String name = rs.getString(2);
if (rs.wasNull()) name = "";
int binding = rs.getInt(3);
if (rs.wasNull()) binding = -1;
String notation = rs.getString(4);
if (rs.wasNull()) notation = "";
String symbol = rs.getString(5);
if (rs.wasNull()) symbol = "";
String symbol_intern = rs.getString(6);
if (rs.wasNull()) symbol_intern = "";
int arity = rs.getInt(7);
if (rs.wasNull()) arity = -1;
String description = rs.getString(8);
if (rs.wasNull()) description = "";
String comment = rs.getString(9);
if (rs.wasNull()) comment = "";
Operator operator =
new Operator(
id, name, binding, notation, symbol, symbol_intern, arity, description, comment);
return operator;
}
@Override
public List<Ordine> listaOrdini() throws PersistenceException { // ok
Connection connection = this.datasource.getConnection();
PreparedStatement statement = null;
List<Ordine> ordini = null;
Ordine ordine = null;
try {
String str =
"select ordini.codice as codice,stato,data,cliente,id "
+ "from ordini left outer join clienti on ordini.cliente=clienti.codice";
statement = connection.prepareStatement(str);
ResultSet result = statement.executeQuery();
ordini = new ArrayList<Ordine>();
while (result.next()) {
ordine = new Ordine();
ClienteDAOImpl cliente = new ClienteDAOImpl();
ordine.setCliente(cliente.getClienteById(result.getInt("cliente")));
ordine.setCodiceOrdine(result.getString("codice"));
ordine.setData(new java.util.Date(result.getDate("data").getDate()));
ordine.setStato(result.getString("stato"));
ordine.setId(result.getInt("id"));
ordini.add(ordine);
}
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
} finally {
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
}
}
return ordini;
}
public void mettreAjourQuantite(String nomArticle, int value) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql://192.168.2.5/inventaire", "guest", "guest");
st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = st.executeQuery("SELECT id, nom, quantite FROM articles");
// modifier la quantité pour l'article nomArticle
while (rs.next()) {
int id = rs.getInt(1);
String nom = rs.getString(2);
int quantite = rs.getInt(3);
if (nom.equals(nomArticle)) {
if ((quantite == 0) && (value == -1)) {
break;
}
rs.updateInt(3, (quantite + value));
rs.updateRow();
break;
}
}
} catch (Exception e) {
System.err.println("Exception: " + e.getMessage());
} finally {
try {
if (rs != null) rs.close();
if (st != null) st.close();
if (con != null) con.close();
} catch (SQLException e) {
}
}
}
public void listerArticles() {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url, usr, passwd);
st = con.createStatement();
rs = st.executeQuery("SELECT id, nom, quantite FROM articles");
// remise à 0 de la liste - utile pour les mises à jour
list.clear();
// Stocker les enregistrements dans la liste
while (rs.next()) {
int id = rs.getInt(1);
String nom = new String(rs.getString(2));
int quantite = rs.getInt(3);
list.add(new ElementBDD(nom, quantite)); // ajout
System.out.println(nom + " " + quantite);
}
} catch (Exception e) {
System.err.println("Exception: " + e.getMessage());
} finally {
try {
if (rs != null) rs.close();
if (st != null) st.close();
if (con != null) con.close();
} catch (SQLException e) {
}
}
}
protected CFSecurityTSecGroupBuff unpackTSecGroupResultSetToBuff(ResultSet resultSet)
throws SQLException {
final String S_ProcName = "unpackTSecGroupResultSetToBuff";
int idxcol = 1;
CFSecurityTSecGroupBuff buff = schema.getFactoryTSecGroup().newBuff();
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedAt(null);
} else {
buff.setCreatedAt(CFSecurityMySqlSchema.convertTimestampString(colString));
}
}
idxcol++;
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedByUserId(null);
} else {
buff.setCreatedByUserId(UUID.fromString(colString));
}
}
idxcol++;
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedAt(null);
} else {
buff.setUpdatedAt(CFSecurityMySqlSchema.convertTimestampString(colString));
}
}
idxcol++;
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedByUserId(null);
} else {
buff.setUpdatedByUserId(UUID.fromString(colString));
}
}
idxcol++;
buff.setRequiredTenantId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredTSecGroupId(resultSet.getInt(idxcol));
idxcol++;
buff.setRequiredName(resultSet.getString(idxcol));
idxcol++;
buff.setRequiredRevision(resultSet.getInt(idxcol));
return (buff);
}
/** Gets version number out of a database table. */
public Version(java.sql.Statement st, String sqlTable) throws SQLException {
ResultSet rs = st.executeQuery("select major,minor,rev from " + sqlTable);
rs.next();
major = rs.getInt("major");
minor = rs.getInt("minor");
rev = rs.getInt("rev");
rs.close();
}
protected CFSecuritySecFormBuff unpackSecFormResultSetToBuff(ResultSet resultSet)
throws SQLException {
final String S_ProcName = "unpackSecFormResultSetToBuff";
int idxcol = 1;
CFSecuritySecFormBuff buff = schema.getFactorySecForm().newBuff();
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedByUserId(null);
} else {
buff.setCreatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedAt(null);
} else {
buff.setCreatedAt(CFCrmDb2LUWSchema.convertTimestampString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedByUserId(null);
} else {
buff.setUpdatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedAt(null);
} else {
buff.setUpdatedAt(CFCrmDb2LUWSchema.convertTimestampString(colString));
}
idxcol++;
}
buff.setRequiredClusterId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredSecFormId(resultSet.getInt(idxcol));
idxcol++;
buff.setRequiredSecAppId(resultSet.getInt(idxcol));
idxcol++;
buff.setRequiredJEEServletMapName(resultSet.getString(idxcol));
idxcol++;
buff.setRequiredRevision(resultSet.getInt(idxcol));
return (buff);
}
protected CFInternetURLProtocolBuff unpackURLProtocolResultSetToBuff(ResultSet resultSet)
throws SQLException {
final String S_ProcName = "unpackURLProtocolResultSetToBuff";
int idxcol = 1;
CFInternetURLProtocolBuff buff = schema.getFactoryURLProtocol().newBuff();
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedByUserId(null);
} else {
buff.setCreatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedAt(null);
} else {
buff.setCreatedAt(CFDbTestPgSqlSchema.convertTimestampString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedByUserId(null);
} else {
buff.setUpdatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedAt(null);
} else {
buff.setUpdatedAt(CFDbTestPgSqlSchema.convertTimestampString(colString));
}
idxcol++;
}
buff.setRequiredURLProtocolId(resultSet.getInt(idxcol));
idxcol++;
buff.setRequiredName(resultSet.getString(idxcol));
idxcol++;
buff.setRequiredDescription(resultSet.getString(idxcol));
idxcol++;
buff.setRequiredIsSecure(resultSet.getBoolean(idxcol));
idxcol++;
buff.setRequiredRevision(resultSet.getInt(idxcol));
return (buff);
}
private List getTableColumns(
Table table, List primaryKeys, List indices, Map uniqueIndices, Map uniqueColumns)
throws SQLException {
// get the columns
List columns = new LinkedList();
ResultSet columnRs = getColumnsResultSet(table);
while (columnRs.next()) {
int sqlType = columnRs.getInt("DATA_TYPE");
String sqlTypeName = columnRs.getString("TYPE_NAME");
String columnName = columnRs.getString("COLUMN_NAME");
String columnDefaultValue = columnRs.getString("COLUMN_DEF");
String remarks = columnRs.getString("REMARKS");
if (remarks == null && dbHelper.isOracleDataBase()) {
remarks = getOracleColumnComments(table.getSqlName(), columnName);
}
// if columnNoNulls or columnNullableUnknown assume "not nullable"
boolean isNullable = (DatabaseMetaData.columnNullable == columnRs.getInt("NULLABLE"));
int size = columnRs.getInt("COLUMN_SIZE");
int decimalDigits = columnRs.getInt("DECIMAL_DIGITS");
boolean isPk = primaryKeys.contains(columnName);
boolean isIndexed = indices.contains(columnName);
String uniqueIndex = (String) uniqueIndices.get(columnName);
List columnsInUniqueIndex = null;
if (uniqueIndex != null) {
columnsInUniqueIndex = (List) uniqueColumns.get(uniqueIndex);
}
boolean isUnique = columnsInUniqueIndex != null && columnsInUniqueIndex.size() == 1;
if (isUnique) {
GLogger.trace("unique column:" + columnName);
}
Column column =
new Column(
table,
sqlType,
sqlTypeName,
columnName,
size,
decimalDigits,
isPk,
isNullable,
isIndexed,
isUnique,
columnDefaultValue,
remarks);
BeanHelper.copyProperties(
column, TableOverrideValuesProvider.getColumnOverrideValues(table, column));
columns.add(column);
}
columnRs.close();
return columns;
}
public votblOrganization getAllOrganizations(int iFKCompanyID, int iOrganisationID) {
Vector v = new Vector();
Connection con = null;
Statement st = null;
ResultSet rs = null;
votblOrganization vo = new votblOrganization();
String sql = "";
if (iOrganisationID != 0) {
sql = "SELECT * FROM tblConsultingCompany a, tblOrganization b";
sql =
sql
+ " WHERE a.CompanyID = b.FKCompanyID AND a.CompanyID = "
+ iFKCompanyID
+ " AND b.PKOrganization ="
+ iOrganisationID;
} else {
sql = "SELECT * FROM tblConsultingCompany WHERE CompanyID = " + iFKCompanyID;
}
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(sql);
if (rs.next()) {
// 27 May 2008 by Hemilda - cause iOrganisationID == 0 the query only retrieve
// tblConsultingCompany column
if (iOrganisationID != 0) {
vo.setEmailNom(rs.getString("EmailNom"));
vo.setEmailNomRemind(rs.getString("EmailNomRemind"));
vo.setEmailPart(rs.getString("EmailPart"));
vo.setEmailPartRemind(rs.getString("EmailPartRemind"));
vo.setExtraModule(rs.getInt("ExtraModule"));
vo.setFKCompanyID(rs.getInt("FKCompanyID"));
vo.setNameSequence(rs.getInt("NameSequence"));
vo.setOrganizationCode(rs.getString("OrganizationCode"));
vo.setOrganizationLogo(rs.getString("OrganizationLogo"));
vo.setOrganizationName(rs.getString("OrganizationName"));
vo.setPKOrganization(rs.getInt("PKOrganization"));
}
vo.setCompanyName(rs.getString("CompanyName"));
}
} catch (SQLException SE) {
System.err.println("Organization.java - getAllOrganizations - " + SE.getMessage());
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return vo;
}
protected SDMSObject rowToObject(SystemEnvironment env, ResultSet r) throws SDMSException {
Long id;
Long smeId;
Long trId;
Long nextTriggerTime;
Integer timesChecked;
Integer timesTriggered;
Long creatorUId;
Long createTs;
Long changerUId;
Long changeTs;
long validFrom;
long validTo;
try {
id = new Long(r.getLong(1));
smeId = new Long(r.getLong(2));
trId = new Long(r.getLong(3));
nextTriggerTime = new Long(r.getLong(4));
timesChecked = new Integer(r.getInt(5));
timesTriggered = new Integer(r.getInt(6));
creatorUId = new Long(r.getLong(7));
createTs = new Long(r.getLong(8));
changerUId = new Long(r.getLong(9));
changeTs = new Long(r.getLong(10));
validFrom = 0;
validTo = Long.MAX_VALUE;
} catch (SQLException sqle) {
SDMSThread.doTrace(null, "SQL Error : " + sqle.getMessage(), SDMSThread.SEVERITY_ERROR);
throw new FatalException(
new SDMSMessage(
env,
"01110182045",
"TriggerQueue: $1 $2",
new Integer(sqle.getErrorCode()),
sqle.getMessage()));
}
if (validTo < env.lowestActiveVersion) return null;
return new SDMSTriggerQueueGeneric(
id,
smeId,
trId,
nextTriggerTime,
timesChecked,
timesTriggered,
creatorUId,
createTs,
changerUId,
changeTs,
validFrom,
validTo);
}
protected CFSecurityServiceTypeBuff unpackServiceTypeResultSetToBuff(ResultSet resultSet)
throws SQLException {
final String S_ProcName = "unpackServiceTypeResultSetToBuff";
int idxcol = 1;
CFSecurityServiceTypeBuff buff = schema.getFactoryServiceType().newBuff();
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedByUserId(null);
} else {
buff.setCreatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedAt(null);
} else {
buff.setCreatedAt(CFSecuritySybaseSchema.convertTimestampString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedByUserId(null);
} else {
buff.setUpdatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedAt(null);
} else {
buff.setUpdatedAt(CFSecuritySybaseSchema.convertTimestampString(colString));
}
idxcol++;
}
buff.setRequiredServiceTypeId(resultSet.getInt(idxcol));
idxcol++;
buff.setRequiredDescription(resultSet.getString(idxcol));
idxcol++;
buff.setRequiredRevision(resultSet.getInt(idxcol));
return (buff);
}
protected CFSecuritySysClusterBuff unpackSysClusterResultSetToBuff(ResultSet resultSet)
throws SQLException {
final String S_ProcName = "unpackSysClusterResultSetToBuff";
int idxcol = 1;
CFSecuritySysClusterBuff buff = schema.getFactorySysCluster().newBuff();
buff.setRequiredSingletonId(resultSet.getInt(idxcol));
idxcol++;
buff.setRequiredClusterId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredRevision(resultSet.getInt(idxcol));
return (buff);
}
ConcurrentHashMap<String, WordWar> loadWars() {
Connection con = null;
Channel channel;
String user;
ConcurrentHashMap<String, WordWar> wars = new ConcurrentHashMap<>(32);
try {
con = pool.getConnection(timeout);
Statement s = con.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM `wars`");
while (rs.next()) {
channel = Tim.channelStorage.channelList.get(rs.getString("channel"));
user = rs.getString("starter");
WordWar war =
new WordWar(
rs.getLong("base_duration"),
rs.getLong("duration"),
rs.getLong("remaining"),
rs.getLong("time_to_start"),
rs.getInt("total_chains"),
rs.getInt("current_chain"),
rs.getInt("delay"),
rs.getBoolean("randomness"),
rs.getString("name"),
user,
channel,
rs.getInt("id"));
wars.put(war.getName(false).toLowerCase(), war);
}
rs.close();
s.close();
} catch (SQLException ex) {
Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
}
}
return wars;
}
protected static void logPoliciesUsingRemovedTopologyObjs(
String operation,
String topologyType,
Collection<Integer> deletedNodes,
final String constraintName,
Connection c)
throws SQLException {
String query =
"select sets.customer_id, p.policy_name, pc.policy_id, sets.user_set_id, sets.constraint_value "
+ " from policies p, policy_criteria pc, dat_saved_user_sets sets "
+ " where sets.constraint_name = "
+ QueryUtils.literal(constraintName)
+ " and "
+ QueryUtils.dbCast("sets.constraint_value", QueryUtils.CastType.INTEGER)
+ " in "
+ QueryUtils.literal(deletedNodes)
+ " and sets.user_set_id = pc.userset_id and pc.policy_id = p.policy_id";
s_logger.debug(query);
Statement s = null;
ResultSet rs = null;
try {
s = c.createStatement();
rs = s.executeQuery(query);
while (rs.next()) {
int custID = rs.getInt(1);
String policyName = rs.getString(2);
int policyID = rs.getInt(3);
int userSetID = rs.getInt(4);
int nodeID = rs.getInt(5);
StringBuilder bld =
new StringBuilder(operation)
.append(" ")
.append(topologyType)
.append(" ")
.append(nodeID)
.append(" which is refered to by userset ")
.append(userSetID)
.append(" in policy ")
.append(policyName)
.append(" with id ")
.append(policyID)
.append(" for customer ")
.append(custID);
s_logger.warn(bld.toString());
}
} finally {
if (s != null) s.close();
if (rs != null) rs.close();
}
}
public List<Oriundo> listar() throws SQLException {
List<Oriundo> resultado = new ArrayList<Oriundo>();
conexao propCon = new conexao();
Connection con =
DriverManager.getConnection(
new conexao().url,
propCon.config.getString("usuario"),
propCon.config.getString("senha"));
PreparedStatement ps = null;
ResultSet rs = null;
String sqlListar = "SELECT * FROM oriundo Order by codigo DESC";
Oriundo oriundo;
try {
ps = con.prepareStatement(sqlListar);
rs = ps.executeQuery();
// if(rs==null){
// return null;
// }
while (rs.next()) {
oriundo = new Oriundo();
oriundo.setCodigo(rs.getInt("codigo"));
oriundo.setDescricao(rs.getString("descricao"));
oriundo.setData_cadastro(rs.getDate("data_cadastro"));
oriundo.setDia_fechamento(rs.getInt("dia_fechamento"));
oriundo.setDia_pag(rs.getInt("dia_pag"));
resultado.add(oriundo);
}
} catch (NumberFormatException e) {
JOptionPane.showMessageDialog(
null, "NumberFormaterExeption Erro: " + e.getMessage(), "ClasseDAO Func.Localizar", 0);
e.printStackTrace();
} catch (NullPointerException e) {
JOptionPane.showMessageDialog(
null, "NullPointerException Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
e.printStackTrace();
} catch (SQLException e) {
JOptionPane.showMessageDialog(
null, "SQLException Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
e.printStackTrace();
} catch (Exception e) {
JOptionPane.showMessageDialog(
null, "Exception Erro: " + e.getMessage(), "ClasseDAO Func. Localizar", 0);
e.printStackTrace();
} finally {
ps.close();
con.close();
}
return resultado;
}
/**
* Creates a list of items from ResultSet.
*
* @param rs ResultSet object created from query
* @return list of created items
*/
public static List<Item> createItems(ResultSet rs) throws SQLException {
List<Item> item = new LinkedList<Item>();
while (rs.next()) {
Item newItem =
new Item(
rs.getInt("ITEM_ID"),
rs.getString("NAME"),
rs.getInt("PRICE"),
rs.getInt("QUANT"),
rs.getClob("DESCR"));
item.add(newItem);
}
return item;
}
/**
* Get Company ID by OrganisationID
*
* @param OrgID
* @return PKCompany
* @throws SQLException
* @throws Exception
*/
public int getCompanyID(int OrgID) throws SQLException, Exception {
String query = "Select FKCompanyID from tblOrganization WHERE PKOrganization = " + OrgID;
/*db.openDB();
Statement stmt = db.con.createStatement();
ResultSet rs = stmt.executeQuery(query);
if(rs.next())
return rs.getInt(1);*/
int iCompanyID = 0;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(query);
if (rs.next()) {
iCompanyID = rs.getInt(1);
}
} catch (Exception E) {
System.err.println("Organization.java - getCompanyID - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return iCompanyID;
}
public static void customStartAll(Connection conn) throws SQLException {
String method = "customStartAll";
int location = 1000;
try {
Statement stmt = conn.createStatement();
int id = 0;
int gpfdistPort = 0;
String strSQL = "SELECT id\n";
strSQL += "FROM os.custom_sql";
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
id = rs.getInt(1);
gpfdistPort = GpfdistRunner.customStart(OSProperties.osHome);
strSQL = "INSERT INTO os.ao_custom_sql\n";
strSQL +=
"(id, table_name, columns, column_datatypes, sql_text, source_type, source_server_name, source_instance_name, source_port, source_database_name, source_user_name, source_pass, gpfdist_port)\n";
strSQL +=
"SELECT id, table_name, columns, column_datatypes, sql_text, source_type, source_server_name, source_instance_name, source_port, source_database_name, source_user_name, source_pass, "
+ gpfdistPort
+ "\n";
strSQL += "FROM os.custom_sql\n";
strSQL += "WHERE id = " + id;
stmt.executeUpdate(strSQL);
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
/** Get Organisation ID by User email */
public int getOrgIDbyEmail(String UserEmail) throws SQLException, Exception {
String query = "Select COUNT(*) as TotRecord from tblEmail";
int count = 0;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(query);
if (rs.next()) {
count = rs.getInt(1);
}
} catch (Exception E) {
System.err.println("Organization.java - editRecord - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return count;
}
public Vector<Message> getInboxMessages(String username, int type) {
if (type == 4) username = "******";
ResultSet rs =
con.queryDB(
"SELECT * FROM "
+ MESSAGES
+ " WHERE toName = \""
+ username
+ "\" AND messageType = "
+ type
+ " ORDER BY timeStamp DESC");
Vector<Message> inbox = new Vector<Message>();
try {
while (rs.next()) {
inbox.add(
new Message(
rs.getString("fromName"),
username,
rs.getString("message"),
rs.getInt("messageType"),
rs.getTimestamp("timeStamp")));
}
} catch (SQLException e) {
throw new RuntimeException("SQLException in MessageDatabase::getInboxMessages");
}
return inbox;
}
/**
* Get organisation's name sequence
*
* @param iOrgID
* @return
* @throws SQLException
* @throws Exception
* @author Maruli
*/
public int getNameSeq(int iOrgID) throws SQLException, Exception {
String query = "SELECT NameSequence FROM tblOrganization WHERE PKOrganization =" + iOrgID;
int iNameSeqe = 0;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(query);
if (rs.next()) {
iNameSeqe = rs.getInt(1);
}
} catch (Exception E) {
System.err.println("Organization.java - getNameSeq - " + E);
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return iNameSeqe;
}
/**
* Deletes students who are older than a certain number of years and not registered to any course.
*
* @param year students older than year are candidates to be deleted
* @throws InvalidDBRequestException
*/
public void deleteOldStudent(int year) throws InvalidDBRequestException {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
// query all student who have been in the database longer than a number of years and not
// registered to any course
ResultSet rs =
stmt.executeQuery(
"select login, count(course_id) "
+ "from student s left join courseRoster r on login = user_login "
+ "where date_entered < SUBDATE(now(), INTERVAL "
+ new String().valueOf(year).trim()
+ " YEAR) "
+ "group by login, date_entered");
// delete them
while (rs.next()) if (rs.getInt(2) == 0) purgeStudent(rs.getString(1).trim());
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in addCourse: " + e.getMessage());
throw new InvalidDBRequestException("??? ");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
}
