List of usage examples for java.sql Statement getResultSet
ResultSet getResultSet() throws SQLException;
ResultSet object. From source file:mom.trd.opentheso.bdd.helper.FacetHelper.java
/** * Cette fonction permet de retourner le concept paretn d'une facette * * @param ds// w w w. ja v a2 s . c o m * @param idFacet * @param idThesaurus * @param lang * @return ArrayList of Id Facet (int) */ public NodeConceptTree getConceptOnFacet(HikariDataSource ds, int idFacet, String idThesaurus, String lang) { Connection conn; Statement stmt; ResultSet resultSet; NodeConceptTree nct = new NodeConceptTree(); try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "select thesaurus_array.id_concept_parent, term.lexical_value " + "from thesaurus_array, term, preferred_term" + " where thesaurus_array.id_concept_parent=preferred_term.id_concept" + " and preferred_term.id_term=term.id_term" + " and thesaurus_array.id_thesaurus=term.id_thesaurus" + " and term.lang='" + lang.trim() + "'" + " and thesaurus_array.id_thesaurus = '" + idThesaurus + "'" + " and facet_id = '" + idFacet + "'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet.next()) { nct.setHaveChildren(true); nct.setIdLang(lang); nct.setIdThesaurus(idThesaurus); nct.setIdConcept(resultSet.getString("id_concept_parent")); nct.setTitle(resultSet.getString("lexical_value")); } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting Concept of Facet : " + idFacet, sqle); } return nct; }
From source file:vitro.vspEngine.service.persistence.DBCommons.java
synchronized public boolean isRegisteredGateway(String pGatewayRegisteredName, StringBuilder out_descFromDB) { boolean retBool = false; java.sql.Connection conn = null; try {//from ww w . j a v a2 s.c o m Class.forName(jdbcdriverClassName).newInstance(); conn = DriverManager.getConnection(connString, usrStr, pwdStr); String echomessage = ""; if (!conn.isClosed()) { //echomessage = "Successfully connected to "+ "MySQL server using TCP/IP..."; Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); if (stmt.execute( "SELECT idregisteredgateway, registeredName, friendlyName, friendlyDescription, ip, listeningport, FROM_UNIXTIME(lastadvtimestamp, \"%d/%m/%Y %H:%i:%s\") lastdate FROM `" + dbSchemaStr + "`.`registeredgateway` WHERE registeredName=\'" + pGatewayRegisteredName + "\'")) { rs = stmt.getResultSet(); } if (rs != null) { while (rs.next()) { int gateId = rs.getInt("idregisteredgateway"); String registeredName = rs.getString("registeredName") == null ? "" : rs.getString("registeredName"); // this is the one used in registration messages String friendlyName = rs.getString("friendlyName") == null ? "" : rs.getString("friendlyName"); String friendlyDescription = rs.getString("friendlyDescription") == null ? "" : rs.getString("friendlyDescription"); out_descFromDB.append(friendlyName); String gateIp = rs.getString("ip") == null ? "" : rs.getString("ip"); String gatePort = rs.getString("listeningport") == null ? "" : rs.getString("listeningport"); String lastdate = rs.getString("lastdate") == null ? "N/A" : rs.getString("lastdate"); if (registeredName.equalsIgnoreCase(pGatewayRegisteredName)) { retBool = true; break; } } } } catch (SQLException ex) { // handle any errors System.err.println("SQLException4: " + ex.getMessage()); System.err.println("SQLState4: " + ex.getSQLState()); System.err.println("VendorError4: " + ex.getErrorCode()); } finally { // it is a good idea to release // resources in a finally{} block // in reverse-order of their creation // if they are no-longer needed if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) { } // ignore rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { } // ignore stmt = null; } } } else { echomessage = "Error accessing DB server..."; } // DEBUG //System.out.println(echomessage); } catch (Exception e) { System.err.println("Exception: " + e.getMessage()); } finally { try { if (conn != null) conn.close(); } catch (SQLException e) { } } return retBool; }
From source file:org.apache.jackrabbit.core.persistence.bundle.BundleDbPersistenceManager.java
/** * {@inheritDoc}// w w w.java 2s . c o m */ public void checkConsistency(String[] uuids, boolean recursive, boolean fix) { log.info("{}: checking workspace consistency...", name); int count = 0; int total = 0; Collection modifications = new ArrayList(); if (uuids == null) { // get all node bundles in the database with a single sql statement, // which is (probably) faster than loading each bundle and traversing the tree ResultSet rs = null; DataInputStream din = null; try { String sql; if (getStorageModel() == SM_BINARY_KEYS) { sql = "select NODE_ID, BUNDLE_DATA from " + schemaObjectPrefix + "BUNDLE"; } else { sql = "select NODE_ID_HI, NODE_ID_LO, BUNDLE_DATA from " + schemaObjectPrefix + "BUNDLE"; } Statement stmt = connectionManager.executeStmt(sql, new Object[0]); rs = stmt.getResultSet(); // iterate over all nodebundles in the db while (rs.next()) { NodeId id; Blob blob; if (getStorageModel() == SM_BINARY_KEYS) { id = new NodeId(new UUID(rs.getBytes(1))); blob = rs.getBlob(2); } else { id = new NodeId(new UUID(rs.getLong(1), rs.getLong(2))); blob = rs.getBlob(3); } din = new DataInputStream(blob.getBinaryStream()); try { // parse and check bundle // check bundle will log any problems itself if (binding.checkBundle(din)) { // reset stream for readBundle() din = new DataInputStream(blob.getBinaryStream()); NodePropBundle bundle = binding.readBundle(din, id); checkBundleConsistency(id, bundle, fix, modifications); } else { log.error("invalid bundle '" + id + "', see previous BundleBinding error log entry"); } } catch (Exception e) { log.error("Error in bundle " + id + ": " + e); } count++; if (count % 1000 == 0) { log.info(name + ": checked " + count + " bundles..."); } } } catch (Exception e) { log.error("Error loading bundle", e); } finally { IOUtils.closeQuietly(din); closeResultSet(rs); total = count; } } else { // check only given uuids, handle recursive flag // 1) convert uuid array to modifiable list // 2) for each uuid do // a) load node bundle // b) check bundle, store any bundle-to-be-modified in collection // c) if recursive, add child uuids to list of uuids List uuidList = new ArrayList(uuids.length); // convert uuid string array to list of UUID objects for (int i = 0; i < uuids.length; i++) { try { uuidList.add(new UUID(uuids[i])); } catch (IllegalArgumentException e) { log.error("Invalid uuid for consistency check, skipping: '" + uuids[i] + "': " + e); } } // iterate over UUIDs (including ones that are newly added inside the loop!) for (int i = 0; i < uuidList.size(); i++) { final UUID uuid = (UUID) uuidList.get(i); try { // load the node from the database NodeId id = new NodeId(uuid); NodePropBundle bundle = loadBundle(id, true); if (bundle == null) { log.error("No bundle found for uuid '" + uuid + "'"); continue; } checkBundleConsistency(id, bundle, fix, modifications); if (recursive) { Iterator iter = bundle.getChildNodeEntries().iterator(); while (iter.hasNext()) { NodePropBundle.ChildNodeEntry entry = (NodePropBundle.ChildNodeEntry) iter.next(); uuidList.add(entry.getId().getUUID()); } } count++; if (count % 1000 == 0) { log.info(name + ": checked " + count + "/" + uuidList.size() + " bundles..."); } } catch (ItemStateException e) { // problem already logged (loadBundle called with logDetailedErrors=true) } } total = uuidList.size(); } // repair collected broken bundles if (consistencyFix && !modifications.isEmpty()) { log.info(name + ": Fixing " + modifications.size() + " inconsistent bundle(s)..."); Iterator iterator = modifications.iterator(); while (iterator.hasNext()) { NodePropBundle bundle = (NodePropBundle) iterator.next(); try { log.info(name + ": Fixing bundle '" + bundle.getId() + "'"); bundle.markOld(); // use UPDATE instead of INSERT storeBundle(bundle); } catch (ItemStateException e) { log.error(name + ": Error storing fixed bundle: " + e); } } } log.info(name + ": checked " + count + "/" + total + " bundles."); }
From source file:org.apache.jackrabbit.core.persistence.bundle.BundleDbPersistenceManager.java
/** * Loads a bundle from the underlying system and optionally performs * a check on the bundle first./*from www . j a v a 2 s . co m*/ * * @param id the node id of the bundle * @param checkBeforeLoading check the bundle before loading it and log * detailed information about it (slower) * @return the loaded bundle or <code>null</code> if the bundle does not * exist. * @throws ItemStateException if an error while loading occurs. */ protected synchronized NodePropBundle loadBundle(NodeId id, boolean checkBeforeLoading) throws ItemStateException { ResultSet rs = null; try { Statement stmt = connectionManager.executeStmt(bundleSelectSQL, getKey(id.getUUID())); rs = stmt.getResultSet(); if (!rs.next()) { return null; } Blob b = rs.getBlob(1); byte[] bytes = getBytes(b); DataInputStream din = new DataInputStream(new ByteArrayInputStream(bytes)); if (checkBeforeLoading) { if (binding.checkBundle(din)) { // reset stream for readBundle() din = new DataInputStream(new ByteArrayInputStream(bytes)); } else { // gets wrapped as proper ItemStateException below throw new Exception("invalid bundle, see previous BundleBinding error log entry"); } } NodePropBundle bundle = binding.readBundle(din, id); bundle.setSize(bytes.length); return bundle; } catch (Exception e) { String msg = "failed to read bundle: " + id + ": " + e; log.error(msg); throw new ItemStateException(msg, e); } finally { closeResultSet(rs); } }
From source file:org.apache.jackrabbit.core.persistence.bundle.BundleDbPersistenceManager.java
/** * {@inheritDoc}/*from www. j av a 2s . c om*/ */ public synchronized NodeIdIterator getAllNodeIds(NodeId bigger, int maxCount) throws ItemStateException, RepositoryException { ResultSet rs = null; try { UUID lowUuid; Object[] keys; String sql; if (bigger == null) { sql = bundleSelectAllIdsSQL; lowUuid = null; keys = new Object[0]; } else { sql = bundleSelectAllIdsFromSQL; lowUuid = bigger.getUUID(); keys = getKey(lowUuid); } if (maxCount > 0) { // get some more rows, in case the first row is smaller // only required for SM_LONGLONG_KEYS // probability is very low to get get the wrong first key, < 1 : 2^64 // see also bundleSelectAllIdsFrom SQL statement maxCount += 10; } Statement stmt = connectionManager.executeStmt(sql, keys, false, maxCount); rs = stmt.getResultSet(); ArrayList result = new ArrayList(); while ((maxCount == 0 || result.size() < maxCount) && rs.next()) { UUID current; if (getStorageModel() == SM_BINARY_KEYS) { current = new UUID(rs.getBytes(1)); } else { long high = rs.getLong(1); long low = rs.getLong(2); current = new UUID(high, low); } if (lowUuid != null) { // skip the keys that are smaller or equal (see above, maxCount += 10) if (current.compareTo(lowUuid) <= 0) { continue; } } result.add(current); } return new ListNodeIdIterator(result); } catch (SQLException e) { String msg = "getAllNodeIds failed."; log.error(msg, e); throw new ItemStateException(msg, e); } finally { closeResultSet(rs); } }
From source file:vitro.vspEngine.service.persistence.DBCommons.java
public DBRegisteredGateway getRegisteredGateway(String pGwId) { DBRegisteredGateway retRegGw = null; if (pGwId != null && !pGwId.isEmpty()) { java.sql.Connection conn = null; try {/*from ww w .ja v a 2s .co m*/ Class.forName(jdbcdriverClassName).newInstance(); conn = DriverManager.getConnection(connString, usrStr, pwdStr); String echomessage = ""; if (!conn.isClosed()) { //echomessage = "Successfully connected to "+ "MySQL server using TCP/IP..."; Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); if (stmt.execute( "SELECT idregisteredgateway, registeredName, friendlyName, friendlyDescription, ip, listeningport, lastadvtimestamp, disabled, FROM_UNIXTIME(lastadvtimestamp, \'%d/%m/%Y %H:%i:%s\') lastdate FROM `" + dbSchemaStr + "`.`registeredgateway` WHERE registeredName=\'" + pGwId + "\'")) { rs = stmt.getResultSet(); } if (rs != null) { while (rs.next()) { int gateId = rs.getInt("idregisteredgateway"); String registeredName = rs.getString("registeredName") == null ? "" : rs.getString("registeredName"); // this is the one used in registration messages String friendlyName = rs.getString("friendlyName") == null ? "" : rs.getString("friendlyName"); String friendlyDescription = rs.getString("friendlyDescription") == null ? "" : rs.getString("friendlyDescription"); String gateIp = rs.getString("ip") == null ? "" : rs.getString("ip"); String gatePort = rs.getString("listeningport") == null ? "" : rs.getString("listeningport"); int lastadvtimestampInt = rs.getInt("lastadvtimestamp"); String lastdate = rs.getString("lastdate") == null ? "N/A" : rs.getString("lastdate"); Boolean status = rs.getBoolean("disabled"); if (!registeredName.isEmpty() && !registeredName.equalsIgnoreCase("")) { retRegGw = new DBRegisteredGateway(gateId, registeredName, friendlyName, friendlyDescription, gateIp, gatePort, lastadvtimestampInt, lastdate, status); } break; // we only need one result, so break here } } } catch (SQLException ex) { // handle any errors System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); System.out.println("VendorError: " + ex.getErrorCode()); } finally { // it is a good idea to release // resources in a finally{} block // in reverse-order of their creation // if they are no-longer needed if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) { System.out.println("SQLException on rs close(): " + sqlEx.getMessage()); } // ignore rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { System.out.println("SQLException on stmt close(): " + sqlEx.getMessage()); } // ignore stmt = null; } } } else { echomessage = "Error accessing DB server..."; } System.out.println(echomessage); } catch (Exception e) { System.err.println("Exception: " + e.getMessage()); } finally { try { if (conn != null) conn.close(); } catch (SQLException e) { } } } return retRegGw; }
From source file:mom.trd.opentheso.bdd.helper.NoteHelper.java
/** * Cette focntion permet de retourner la liste de l'historique des notes * pour un concept (type CustomNote, ScopeNote, HistoryNote) * * @param ds//from w w w. jav a 2 s. c o m * @param idConcept * @param idThesaurus * @param idTerm * @param idLang * @return ArrayList des notes sous forme de Class NodeNote */ public ArrayList<NodeNote> getNoteHistoriqueAll(HikariDataSource ds, String idConcept, String idThesaurus, String idTerm, String idLang) { ArrayList<NodeNote> nodeNotes = new ArrayList<>(); Connection conn; Statement stmt; ResultSet resultSet; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT id, notetypecode, lexicalvalue, modified, username FROM note_historique, users" + " WHERE id_thesaurus = '" + idThesaurus + "'" + " and lang ='" + idLang + "'" + " and (id_concept = '" + idConcept + "' OR id_term = '" + idTerm + "' )" + " and note_historique.id_user=users.id_user" + " order by modified DESC"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); while (resultSet.next()) { NodeNote nodeNote = new NodeNote(); nodeNote.setId_concept(idConcept); nodeNote.setId_term(idTerm); nodeNote.setId_note(resultSet.getInt("id")); nodeNote.setLang(idLang); nodeNote.setLexicalvalue(resultSet.getString("lexicalvalue")); nodeNote.setModified(resultSet.getDate("modified")); nodeNote.setNotetypecode(resultSet.getString("notetypecode")); nodeNote.setIdUser(resultSet.getString("username")); nodeNotes.add(nodeNote); } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting all historique Notes of Concept : " + idConcept, sqle); } return nodeNotes; }
From source file:mom.trd.opentheso.bdd.helper.NoteHelper.java
/** * Cette focntion permet de retourner la liste des notes pour un Term(type * HistoryNote, Definition, EditotrialNote) * * @param ds/*from ww w . j a v a2 s . c o m*/ * @param idThesaurus * @param idTerm * @param idLang * @return ArrayList des notes sous forme de Class NodeNote */ public ArrayList<NodeNote> getListNotesTerm(HikariDataSource ds, String idTerm, String idThesaurus, String idLang) { ArrayList<NodeNote> nodeNotes = new ArrayList<>(); Connection conn; Statement stmt; ResultSet resultSet; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT note.id, note.notetypecode," + " note.lexicalvalue, note.created," + " note.modified FROM note, note_type" + " WHERE note.notetypecode = note_type.code" + " and note_type.isterm = true" + " and note.id_term = '" + idTerm + "'" + " and note.lang ='" + idLang + "'" + " and note.id_thesaurus = '" + idThesaurus + "'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); while (resultSet.next()) { NodeNote nodeNote = new NodeNote(); nodeNote.setId_term(idTerm); nodeNote.setId_note(resultSet.getInt("id")); nodeNote.setLang(idLang); nodeNote.setLexicalvalue(resultSet.getString("lexicalvalue")); nodeNote.setModified(resultSet.getDate("modified")); nodeNote.setCreated(resultSet.getDate("created")); nodeNote.setNotetypecode(resultSet.getString("notetypecode")); nodeNotes.add(nodeNote); } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting Notes of Term : " + idTerm, sqle); } return nodeNotes; }
From source file:mom.trd.opentheso.bdd.helper.NoteHelper.java
/** * pour pouvoir obtener une list des Notes a partir du idTerm * sans conter avec le language//from w ww . j a v a 2 s . c o m * @param ds * @param idTerm * @param idThesaurus * @return */ public ArrayList<NodeNote> getListNotesTerm2(HikariDataSource ds, String idTerm, String idThesaurus) { ArrayList<NodeNote> nodeNotes = new ArrayList<>(); Connection conn; Statement stmt; ResultSet resultSet; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT note.id, note.lang, note.notetypecode," + " note.lexicalvalue, note.created," + " note.modified FROM note, note_type" + " WHERE note.notetypecode = note_type.code" + " and note_type.isterm = true" + " and note.id_term = '" + idTerm + "'" + " and note.id_thesaurus = '" + idThesaurus + "'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); while (resultSet.next()) { NodeNote nodeNote = new NodeNote(); nodeNote.setId_term(idTerm); nodeNote.setId_note(resultSet.getInt("id")); nodeNote.setLexicalvalue(resultSet.getString("lexicalvalue")); nodeNote.setModified(resultSet.getDate("modified")); nodeNote.setCreated(resultSet.getDate("created")); nodeNote.setNotetypecode(resultSet.getString("notetypecode")); nodeNote.setLang(resultSet.getString("lang")); nodeNotes.add(nodeNote); } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting Notes of Term : " + idTerm, sqle); } return nodeNotes; }
From source file:mom.trd.opentheso.bdd.helper.NoteHelper.java
/** * Cette focntion permet de retourner la liste des notes pour un concept * (type CustomNote, ScopeNote, HistoryNote) * * @param ds/* w w w.j a v a 2 s .co m*/ * @param idConcept * @param idThesaurus * @param idLang * @return ArrayList des notes sous forme de Class NodeNote */ public ArrayList<NodeNote> getListNotesConcept(HikariDataSource ds, String idConcept, String idThesaurus, String idLang) { ArrayList<NodeNote> nodeNotes = new ArrayList<>(); Connection conn; Statement stmt; ResultSet resultSet; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT note.id, note.notetypecode," + " note.lexicalvalue, note.created," + " note.modified FROM note, note_type" + " WHERE note.notetypecode = note_type.code" + " and note_type.isconcept = true" + " and note.id_concept = '" + idConcept + "'" + " and note.lang ='" + idLang + "'" + " and note.id_thesaurus = '" + idThesaurus + "'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); while (resultSet.next()) { NodeNote nodeNote = new NodeNote(); nodeNote.setId_concept(idConcept); nodeNote.setId_note(resultSet.getInt("id")); nodeNote.setLang(idLang); nodeNote.setLexicalvalue(resultSet.getString("lexicalvalue")); nodeNote.setModified(resultSet.getDate("modified")); nodeNote.setCreated(resultSet.getDate("created")); nodeNote.setNotetypecode(resultSet.getString("notetypecode")); nodeNotes.add(nodeNote); } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting Notes of Concept : " + idConcept, sqle); } return nodeNotes; }