List of usage examples for java.sql Statement getResultSet
ResultSet getResultSet() throws SQLException;
ResultSet object. From source file:mom.trd.opentheso.bdd.helper.ConceptHelper.java
/** * Cette fonction permet de rcuprer la liste des concepts suivant l'id du * Concept-Pre et le thsaurus sous forme de classe NodeConceptTree (sans * les relations)//from w w w .ja v a2s . c om * * @param ds * @param idConcept * @param idThesaurus * @param idLang * @return Objet class NodeConceptTree */ /*public ArrayList<NodeConceptTree> getListConcepts(HikariDataSource ds, String idConcept, String idThesaurus, String idLang) { Connection conn; Statement stmt; ResultSet resultSet; ArrayList<NodeConceptTree> nodeConceptTree = null; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT DISTINCT term.lexical_value, term.lang," + " term.id_thesaurus, preferred_term.id_concept, concept.status" + " FROM term,preferred_term,concept,hierarchical_relationship" + " WHERE preferred_term.id_term = term.id_term AND" + " preferred_term.id_thesaurus = term.id_thesaurus AND" + " concept.id_concept = preferred_term.id_concept AND" + " concept.id_thesaurus = preferred_term.id_thesaurus AND" + " hierarchical_relationship.id_concept2 = concept.id_concept" + " and concept.id_thesaurus = '" + idThesaurus + "'" + " and hierarchical_relationship.role = 'NT'" + " and hierarchical_relationship.id_concept1 = '" + idConcept + "'" + " and term.lang = '" + idLang + "'"; //" ORDER BY unaccent_string(term.lexical_value) ASC;"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { nodeConceptTree = new ArrayList<>(); while (resultSet.next()) { NodeConceptTree nodeConceptTree1 = new NodeConceptTree(); nodeConceptTree1.setIdConcept(resultSet.getString("id_concept")); nodeConceptTree1.setStatusConcept(resultSet.getString("status")); nodeConceptTree1.setIdThesaurus(idThesaurus); nodeConceptTree1.setIdLang(idLang); if (resultSet.getString("lexical_value").trim().equals("")) { nodeConceptTree1.setTitle(""); } else { nodeConceptTree1.setTitle(resultSet.getString("lexical_value").trim()); } nodeConceptTree1.setHaveChildren( haveChildren(ds, idThesaurus, nodeConceptTree1.getIdConcept())); nodeConceptTree.add(nodeConceptTree1); } } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting ListConcept of Concept : " + idConcept, sqle); } Collections.sort(nodeConceptTree); return nodeConceptTree; }*/ public ArrayList<NodeConceptTree> getListConcepts(HikariDataSource ds, String idConcept, String idThesaurus, String idLang) { Connection conn; Statement stmt; ResultSet resultSet; ArrayList<NodeConceptTree> nodeConceptTree = null; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "select id_concept2 from hierarchical_relationship" + " where id_thesaurus = '" + idThesaurus + "'" + " and id_concept1 = '" + idConcept + "'" + " and role = '" + "NT" + "'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { nodeConceptTree = new ArrayList<>(); while (resultSet.next()) { NodeConceptTree nodeConceptTree1 = new NodeConceptTree(); nodeConceptTree1.setIdConcept(resultSet.getString("id_concept2")); nodeConceptTree1.setIdThesaurus(idThesaurus); nodeConceptTree1.setIdLang(idLang); nodeConceptTree.add(nodeConceptTree1); } } for (NodeConceptTree nodeConceptTree1 : nodeConceptTree) { /* dsactiv, ne marche pas pour les termes dprcis query = "SELECT term.lexical_value, term.status FROM term, preferred_term" + " WHERE preferred_term.id_term = term.id_term" + " and preferred_term.id_concept ='" + nodeConceptTree1.getIdConcept() + "'" + " and term.lang = '" + idLang + "'" + " and term.id_thesaurus = '" + idThesaurus + "'"; */ query = "SELECT term.lexical_value, concept.status" + " FROM concept, preferred_term, term" + " WHERE concept.id_concept = preferred_term.id_concept AND" + " concept.id_thesaurus = preferred_term.id_thesaurus AND" + " preferred_term.id_term = term.id_term AND" + " preferred_term.id_thesaurus = term.id_thesaurus AND" + " concept.id_concept = '" + nodeConceptTree1.getIdConcept() + "' AND" + " term.lang = '" + idLang + "' AND" + " term.id_thesaurus = '" + idThesaurus + "';"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { resultSet.next(); if (resultSet.getRow() == 0) { nodeConceptTree1.setTitle(""); nodeConceptTree1.setStatusConcept(""); } else { nodeConceptTree1.setTitle(resultSet.getString("lexical_value")); nodeConceptTree1.setStatusConcept(resultSet.getString("status")); } nodeConceptTree1.setHaveChildren( haveChildren(ds, idThesaurus, nodeConceptTree1.getIdConcept())); } } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting ListConcept of Concept : " + idConcept, sqle); } Collections.sort(nodeConceptTree); return nodeConceptTree; }
From source file:com.evolveum.midpoint.testing.sanity.TestSanity.java
License:asdf
/** * Add Derby account to user. This should result in account provisioning. Check if * that happens in repo and in Derby./* ww w. j av a 2 s. co m*/ */ @Test public void test014AddDerbyAccountToUser() throws IOException, JAXBException, FaultMessage, ObjectNotFoundException, SchemaException, DirectoryException, SQLException { TestUtil.displayTestTile("test014AddDerbyAccountToUser"); // GIVEN checkRepoDerbyResource(); assertNoRepoCache(); ObjectDeltaType objectChange = unmarshallValueFromFile(REQUEST_USER_MODIFY_ADD_ACCOUNT_DERBY_FILENAME, ObjectDeltaType.class); // WHEN ObjectTypes.USER.getTypeQName(), OperationResultType result = modifyObjectViaModelWS(objectChange); // THEN assertNoRepoCache(); displayJaxb("modifyObject result", result, SchemaConstants.C_RESULT); TestUtil.assertSuccess("modifyObject has failed", result); // Check if user object was modified in the repo OperationResult repoResult = new OperationResult("getObject"); PrismObject<UserType> uObject = repositoryService.getObject(UserType.class, USER_JACK_OID, null, repoResult); UserType repoUser = uObject.asObjectable(); repoResult.computeStatus(); display("User (repository)", repoUser); List<ObjectReferenceType> accountRefs = repoUser.getLinkRef(); // OpenDJ account was added in previous test, hence 2 accounts assertEquals(2, accountRefs.size()); ObjectReferenceType accountRef = null; for (ObjectReferenceType ref : accountRefs) { if (!ref.getOid().equals(accountShadowOidOpendj)) { accountRef = ref; } } accountShadowOidDerby = accountRef.getOid(); assertFalse(accountShadowOidDerby.isEmpty()); // Check if shadow was created in the repo repoResult = new OperationResult("getObject"); PrismObject<ShadowType> repoShadow = repositoryService.getObject(ShadowType.class, accountShadowOidDerby, null, repoResult); ShadowType repoShadowType = repoShadow.asObjectable(); repoResult.computeStatus(); TestUtil.assertSuccess("addObject has failed", repoResult); display("Shadow (repository)", repoShadowType); assertNotNull(repoShadowType); assertEquals(RESOURCE_DERBY_OID, repoShadowType.getResourceRef().getOid()); // Check the "name" property, it should be set to DN, not entryUUID assertEquals("Wrong name property", PrismTestUtil.createPolyStringType(USER_JACK_DERBY_LOGIN), repoShadowType.getName()); // check attributes in the shadow: should be only identifiers (ICF UID) String uid = checkRepoShadow(repoShadow); // check if account was created in DB Table Statement stmt = derbyController.getExecutedStatementWhereLoginName(uid); ResultSet rs = stmt.getResultSet(); System.out.println("RS: " + rs); assertTrue("No records found for login name " + uid, rs.next()); assertEquals(USER_JACK_DERBY_LOGIN, rs.getString(DerbyController.COLUMN_LOGIN)); assertEquals("Cpt. Jack Sparrow", rs.getString(DerbyController.COLUMN_FULL_NAME)); // TODO: check password //assertEquals("3lizab3th",rs.getString(DerbyController.COLUMN_PASSWORD)); System.out.println("Password: " + rs.getString(DerbyController.COLUMN_PASSWORD)); assertFalse("Too many records found for login name " + uid, rs.next()); rs.close(); stmt.close(); // Use getObject to test fetch of complete shadow assertNoRepoCache(); Holder<OperationResultType> resultHolder = new Holder<OperationResultType>(); Holder<ObjectType> objectHolder = new Holder<ObjectType>(); SelectorQualifiedGetOptionsType options = new SelectorQualifiedGetOptionsType(); // WHEN modelWeb.getObject(ObjectTypes.SHADOW.getTypeQName(), accountShadowOidDerby, options, objectHolder, resultHolder); // THEN assertNoRepoCache(); displayJaxb("getObject result", resultHolder.value, SchemaConstants.C_RESULT); TestUtil.assertSuccess("getObject has failed", resultHolder.value); ShadowType modelShadow = (ShadowType) objectHolder.value; display("Shadow (model)", modelShadow); AssertJUnit.assertNotNull(modelShadow); AssertJUnit.assertEquals(RESOURCE_DERBY_OID, modelShadow.getResourceRef().getOid()); assertAttribute(modelShadow, ConnectorFactoryIcfImpl.ICFS_UID, USER_JACK_DERBY_LOGIN); assertAttribute(modelShadow, ConnectorFactoryIcfImpl.ICFS_NAME, USER_JACK_DERBY_LOGIN); assertAttribute(modelShadow, resourceDerby, "FULL_NAME", "Cpt. Jack Sparrow"); }
From source file:mom.trd.opentheso.bdd.helper.ConceptHelper.java
/** * Cette fonction permet de rcuprer le nom d'un Concept sinon renvoie un * une chaine vide/*ww w .j a v a 2 s.c om*/ * * @param ds * @param idConcept * @param idThesaurus * @param idLang * @return Objet class Concept */ public String getLexicalValueOfConcept(HikariDataSource ds, String idConcept, String idThesaurus, String idLang) { Connection conn; Statement stmt; ResultSet resultSet; String lexicalValue = ""; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "select lexical_value from term, preferred_term where" + " preferred_term.id_term = term.id_term AND" + " preferred_term.id_thesaurus = term.id_thesaurus" + " and term.id_thesaurus = '" + idThesaurus + "'" + " and preferred_term.id_concept = '" + idConcept + "'" + " and term.lang = '" + idLang + "'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet.next()) { lexicalValue = resultSet.getString("lexical_value"); } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting LexicalValue of Concept : " + idConcept, sqle); } return lexicalValue; }
From source file:mom.trd.opentheso.bdd.helper.ConceptHelper.java
/** * Cette fonction permet de rcuprer les identifiants des Group des parents * d'un concept SAUF les groupes du parent pass en paramtre * * @param ds/*from w w w .j a v a2 s . co m*/ * @param idConceptParent * @param idThesaurus * @param idNoGroup le parent dont on ne souhaite pas avoir les groupes * @return String idGroup */ public ArrayList<String> getListGroupIdParentOfConceptOtherThan(HikariDataSource ds, ArrayList<String> idConceptParent, String idThesaurus, String idNoGroup) { Connection conn; Statement stmt; ResultSet resultSet; ArrayList<String> idGroup = new ArrayList<>(); try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT DISTINCT id_group from concept where id_thesaurus = '" + idThesaurus + "' and ("; for (String s : idConceptParent) { query += "id_concept = '" + s + "' or "; } query = query.substring(0, query.length() - 4); query += ") and id_concept != '" + idNoGroup + "'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { while (resultSet.next()) { idGroup.add(resultSet.getString("id_group")); } } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting Id group of parent of Concept", sqle); } return idGroup; }
From source file:org.ramadda.repository.Repository.java
/** * _more_//from w ww .j a va 2 s . co m * * @throws Exception _more_ */ public void readGlobals() throws Exception { Statement statement = getDatabaseManager().select(Tables.GLOBALS.COLUMNS, Tables.GLOBALS.NAME, new Clause[] {}); Properties tmp = new Properties(); ResultSet results = statement.getResultSet(); while (results.next()) { String name = results.getString(1); String value = results.getString(2); if (name.equals(PROP_PROPERTIES)) { tmp.load(new ByteArrayInputStream(value.getBytes())); } tmp.put(name, value); } getDatabaseManager().closeAndReleaseConnection(statement); dbProperties = tmp; }
From source file:mom.trd.opentheso.bdd.helper.ConceptHelper.java
/** * Cette fonction permet d'ajouter un Concept la table Concept, en * paramtre un objet Classe Concept//w ww.j a va2s .com * * @param conn * @param concept * @param idUser * @return */ public String addConceptInTable(Connection conn, Concept concept, int idUser) { String idConcept = null; String idArk = ""; // Connection conn; Statement stmt; ResultSet resultSet; try { // Get connection from pool // conn = ds.getConnection(); try { stmt = conn.createStatement(); String query; try { if (identifierType.equalsIgnoreCase("1")) { // identifiants types alphanumrique ToolsHelper toolsHelper = new ToolsHelper(); idConcept = toolsHelper.getNewId(10); while (isIdExiste(conn, idConcept, concept.getIdThesaurus())) { idConcept = toolsHelper.getNewId(10); } concept.setIdConcept(idConcept); } else { query = "select max(id) from concept"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); resultSet.next(); int idNumerique = resultSet.getInt(1); idNumerique++; idConcept = "" + (idNumerique); // si le nouveau Id existe, on l'incrmente while (isIdExiste(conn, idConcept, concept.getIdThesaurus())) { idConcept = "" + (++idNumerique); } concept.setIdConcept(idConcept); } query = "Insert into concept " + "(id_concept, id_thesaurus, id_ark, status, notation, top_concept, id_group)" + " values (" + "'" + idConcept + "'" + ",'" + concept.getIdThesaurus() + "'" + ",'" + idArk + "'" + ",'" + concept.getStatus() + "'" + ",'" + concept.getNotation() + "'" + "," + concept.isTopConcept() + ",'" + concept.getIdGroup() + "')"; stmt.executeUpdate(query); /** * Ajout des informations dans la table Concept */ if (!addConceptHistorique(conn, concept, idUser)) { stmt.close(); return null; } } finally { stmt.close(); } } finally { // conn.close(); } } catch (SQLException sqle) { // Log exception if (!sqle.getMessage().contains("duplicate key value violates unique constraint")) { log.error("Error while adding Concept : " + idConcept, sqle); } idConcept = null; } return idConcept; }
From source file:mom.trd.opentheso.bdd.helper.ConceptHelper.java
public ArrayList<String> getListChildrenOfConceptNotExist(HikariDataSource ds, String idConcept, String idThesaurus, int id_alignement_source) { Connection conn;// w w w. j ava 2 s. co m Statement stmt; ResultSet resultSet; ArrayList<String> listIdsOfConcept = new ArrayList<>(); try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "select id_concept2 from hierarchical_relationship" + " where id_concept2 not in ( SELECT " + " hierarchical_relationship.id_concept2" + " FROM " + " public.alignement," + " public.hierarchical_relationship " + " WHERE " + " alignement.internal_id_concept = hierarchical_relationship.id_concept2 AND" + " alignement.internal_id_thesaurus = hierarchical_relationship.id_thesaurus AND" + " alignement.id_alignement_source = " + id_alignement_source + " AND " + " hierarchical_relationship.role = 'NT'" + " AND hierarchical_relationship.id_thesaurus = '" + idThesaurus + "'" + " and hierarchical_relationship.id_concept1 = '" + idConcept + "')" + " and id_thesaurus = '" + idThesaurus + "'" + " and role ='NT'" + " and id_concept1= '" + idConcept + "'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); while (resultSet.next()) { listIdsOfConcept.add(resultSet.getString("id_concept2")); } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting List of Id of Concept : " + idConcept, sqle); } return listIdsOfConcept; }
From source file:mom.trd.opentheso.bdd.helper.TermHelper.java
/** * Cette fonction permet de rcuprer un liste des terms pour * l'autocompltion/*from ww w . j a v a 2s. c o m*/ * * @param ds * @param idThesaurus * @param text * @param idLang * @return Objet class Concept */ public List<NodeAutoCompletion> getAutoCompletionTerm(HikariDataSource ds, String idThesaurus, String idLang, String text) { Connection conn; Statement stmt; ResultSet resultSet; List<NodeAutoCompletion> nodeAutoCompletionList = new ArrayList<>(); text = new StringPlus().convertString(text); try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { // cette partie permettait de se placer directement sur un terme, mais quand on tappe le temre exacte, on a le resultat avec le teme en double /* String query = "SELECT DISTINCT term.lexical_value, concept.id_concept, concept.id_group " + "FROM preferred_term, term, concept WHERE " + "preferred_term.id_term = term.id_term AND " + "preferred_term.id_thesaurus = term.id_thesaurus AND " + "concept.id_concept = preferred_term.id_concept AND " + "concept.id_thesaurus = preferred_term.id_thesaurus AND " + "term.id_thesaurus = '" + idThesaurus + "' AND " + "term.lexical_value iLIKE '" + text + "' AND " + "term.lang = '" + idLang + "' AND " + "concept.status != 'hidden'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { while (resultSet.next()) { if (resultSet.getRow() != 0) { NodeAutoCompletion nodeAutoCompletion = new NodeAutoCompletion(); nodeAutoCompletion.setIdConcept(resultSet.getString("id_concept")); nodeAutoCompletion.setTermLexicalValue(resultSet.getString("lexical_value")); nodeAutoCompletion.setGroupLexicalValue( new GroupHelper().getLexicalValueOfGroup(ds, resultSet.getString("id_group"), idThesaurus, idLang)); //resultSet.getString("lexicalvalue")); nodeAutoCompletion.setIdGroup(resultSet.getString("id_group")); nodeAutoCompletionList.add(nodeAutoCompletion); } } }*/ String query = "SELECT DISTINCT term.lexical_value, concept.id_concept, concept.id_group " + "FROM preferred_term, term, concept WHERE " + "preferred_term.id_term = term.id_term AND " + "preferred_term.id_thesaurus = term.id_thesaurus AND " + "concept.id_concept = preferred_term.id_concept AND " + "concept.id_thesaurus = preferred_term.id_thesaurus AND " + "term.id_thesaurus = '" + idThesaurus + "' AND " + "term.lang = '" + idLang + "' AND " + "concept.status != 'hidden' AND " + "unaccent_string(term.lexical_value) ILIKE unaccent_string('" + text + "%')" + " ORDER BY term.lexical_value ASC LIMIT 20"; /* query = "SELECT DISTINCT term.lexical_value, concept.id_concept," + " concept_group_label.lexicalvalue, concept_group_label.idgroup FROM" + " concept, preferred_term, term, concept_group_label" + " WHERE concept.id_concept = preferred_term.id_concept" + " AND concept.id_group = concept_group_label.idgroup" + " AND preferred_term.id_term = term.id_term" + " AND term.id_thesaurus = concept.id_thesaurus" + " AND concept.status != 'hidden'" + " AND term.id_thesaurus = '" + idThesaurus + "'" + " AND term.lang = '" + idLang + "'" + " AND concept_group_label.lang = '" + idLang + "'" + " AND unaccent_string(term.lexical_value) ILIKE unaccent_string('" + text + "%')" + " ORDER BY term.lexical_value ASC LIMIT 20"; */ stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { while (resultSet.next()) { if (resultSet.getRow() != 0) { NodeAutoCompletion nodeAutoCompletion = new NodeAutoCompletion(); nodeAutoCompletion.setIdConcept(resultSet.getString("id_concept")); nodeAutoCompletion.setTermLexicalValue(resultSet.getString("lexical_value")); nodeAutoCompletion.setGroupLexicalValue(new GroupHelper().getLexicalValueOfGroup(ds, resultSet.getString("id_group"), idThesaurus, idLang)); nodeAutoCompletion.setIdGroup(resultSet.getString("id_group")); // if(!nodeAutoCompletionList.contains(nodeAutoCompletion)) nodeAutoCompletionList.add(nodeAutoCompletion); } } } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting List of autocompletion of Text : " + text, sqle); } return nodeAutoCompletionList; }
From source file:com.nextep.designer.sqlgen.postgre.impl.PostgreSqlCapturer.java
@Override public Collection<IBasicTable> getTables(ICaptureContext context, IProgressMonitor monitor) { final Collection<IBasicTable> tables = jdbcCapturer.getTables(context, monitor); Map<String, IBasicTable> tablesMap = new HashMap<String, IBasicTable>(); Map<IReference, IBasicTable> tablesRefMap = new HashMap<IReference, IBasicTable>(); Map<String, IKeyConstraint> ukMap = new HashMap<String, IKeyConstraint>(); Map<String, IBasicColumn> columnsMap = new HashMap<String, IBasicColumn>(); final Connection conn = (Connection) context.getConnectionObject(); Statement stmt = null; ResultSet rset = null;//from w w w . java 2s .c om for (IBasicTable table : tables) { tablesMap.put(table.getName(), table); tablesRefMap.put(table.getReference(), table); } // Fetching PostGreSql inheritance relations try { stmt = conn.createStatement(); rset = stmt.executeQuery("SELECT " //$NON-NLS-1$ + " c.relname AS name " //$NON-NLS-1$ + " , p.relname AS parent " //$NON-NLS-1$ + "FROM pg_inherits " //$NON-NLS-1$ + " JOIN pg_class AS c ON (inhrelid=c.oid) " //$NON-NLS-1$ + " JOIN pg_class as p ON (inhparent=p.oid) " //$NON-NLS-1$ + "ORDER BY 1"); //$NON-NLS-1$ while (rset.next()) { monitor.worked(1); final String tabName = rset.getString(1); final String inheritsFrom = rset.getString(2); IVersionable<?> v = (IVersionable<?>) tablesMap.get(tabName); if (v == null) { LOGGER.warn("Skipping inherits constraint '" + inheritsFrom + "' on table " + tabName + ": child table not in the imported set."); continue; } IVersionable<?> vi = (IVersionable<?>) tablesMap.get(inheritsFrom); if (vi == null) { LOGGER.warn("Skipping inherits constraint '" + inheritsFrom + "' on table " + tabName + ": parent table not in the imported set."); continue; } IPostgreSqlTable t = (IPostgreSqlTable) v.getVersionnedObject().getModel(); IPostgreSqlTable i = (IPostgreSqlTable) vi.getVersionnedObject().getModel(); t.addInheritance(i); } } catch (SQLException e) { LOGGER.warn(MessageFormat.format(SQLGenMessages.getString("capturer.error.genericCapturerError"), //$NON-NLS-1$ context.getConnection().getDBVendor().toString()) + e.getMessage(), e); } finally { CaptureHelper.safeClose(rset, null); } for (IBasicTable table : tables) { // tablesMap.put(table.getName(), table); List<IBasicColumn> ir = new ArrayList<IBasicColumn>(); for (IBasicColumn c : table.getColumns()) { if (!isInherited(c, table, tablesRefMap)) { columnsMap.put(CaptureHelper.getUniqueColumnName(c), c); final IDatatype colType = c.getDatatype(); final IDatatype d = convertDatatype(colType); final String dataDefault = c.getDefaultExpr(); if ("SERIAL".equalsIgnoreCase(d.getName()) && dataDefault != null //$NON-NLS-1$ && !"".equals(dataDefault.trim())) { //$NON-NLS-1$ d.setName("INTEGER"); //$NON-NLS-1$ } if ("BIGSERIAL".equalsIgnoreCase(d.getName()) && dataDefault != null //$NON-NLS-1$ && !"".equals(dataDefault.trim())) { //$NON-NLS-1$ d.setName("BIGINT"); //$NON-NLS-1$ } if ("TIMESTAMP".equalsIgnoreCase(d.getName())) { //$NON-NLS-1$ d.setLength(0); d.setPrecision(0); } // Fix for bug DES-933 regarding interval data types if ("INTERVAL".equalsIgnoreCase(d.getName())) { //$NON-NLS-1$ d.setLength(d.getPrecision()); d.setPrecision(0); } if (("VARCHAR".equalsIgnoreCase(d.getName())) //$NON-NLS-1$ || ("CHARACTER VARYING".equalsIgnoreCase(d.getName()))) { //$NON-NLS-1$ if (d.getLength() == Integer.MAX_VALUE) { d.setLength(0); } } if ("NUMERIC".equalsIgnoreCase(d.getName())) { //$NON-NLS-1$ if (d.getLength() == 131089) { d.setLength(0); } } c.setDatatype(d); } else { ir.add(c); LOGGER.warn( "Not adding column '" + c.getName() + "' on table " + table.getName() + ": inherited."); } } for (IBasicColumn c : ir) { table.removeColumn(c); } } // Fetching unique constraints try { stmt = conn.createStatement(); stmt.execute("SELECT tc.constraint_name,tc.constraint_type, " //$NON-NLS-1$ + " tc.table_name, kcu.column_name, tc.is_deferrable, " //$NON-NLS-1$ + " tc.initially_deferred, " //$NON-NLS-1$ + " ccu.table_name AS references_table, " //$NON-NLS-1$ + " ccu.column_name AS references_field " //$NON-NLS-1$ + " FROM information_schema.table_constraints tc " //$NON-NLS-1$ + "LEFT JOIN information_schema.key_column_usage kcu " //$NON-NLS-1$ + " ON tc.constraint_catalog = kcu.constraint_catalog " //$NON-NLS-1$ + " AND tc.constraint_schema = kcu.constraint_schema " //$NON-NLS-1$ + " AND tc.constraint_name = kcu.constraint_name " //$NON-NLS-1$ + "LEFT JOIN information_schema.referential_constraints rc " //$NON-NLS-1$ + " ON tc.constraint_catalog = rc.constraint_catalog " //$NON-NLS-1$ + " AND tc.constraint_schema = rc.constraint_schema " //$NON-NLS-1$ + " AND tc.constraint_name = rc.constraint_name " //$NON-NLS-1$ + "LEFT JOIN information_schema.constraint_column_usage ccu " //$NON-NLS-1$ + " ON rc.unique_constraint_catalog = ccu.constraint_catalog" //$NON-NLS-1$ + " AND rc.unique_constraint_schema = ccu.constraint_schema" //$NON-NLS-1$ + " AND rc.unique_constraint_name = ccu.constraint_name" //$NON-NLS-1$ + " WHERE tc.constraint_schema not in ( 'information_schema', 'pg_catalog') " //$NON-NLS-1$ + " and tc.constraint_type='UNIQUE'"); //$NON-NLS-1$ rset = stmt.getResultSet(); IKeyConstraint currentUk = null; while (rset.next()) { final String name = rset.getString(1); final String tableName = rset.getString(3); final String columnName = rset.getString(4); if (currentUk == null || !name.equals(currentUk.getName())) { currentUk = CorePlugin.getTypedObjectFactory().create(UniqueKeyConstraint.class); currentUk.setName(name); final IBasicTable table = tablesMap.get(tableName); if (table != null) { currentUk.setConstrainedTable(table); table.addConstraint(currentUk); ukMap.put(name, currentUk); } else { continue; } } final IBasicColumn column = columnsMap .get(CaptureHelper.getUniqueObjectName(tableName, columnName)); if (column != null) { currentUk.addColumn(column); } else { LOGGER.warn(MessageFormat.format(SQLGenMessages.getString("capturer.columnNotFound"), //$NON-NLS-1$ CaptureHelper.getUniqueObjectName(currentUk.getName(), columnName), tableName)); } } } catch (SQLException e) { LOGGER.warn(MessageFormat.format(SQLGenMessages.getString("capturer.error.genericCapturerError"), //$NON-NLS-1$ context.getConnection().getDBVendor().toString()) + e.getMessage(), e); } finally { CaptureHelper.safeClose(rset, stmt); } // Fetching check constraints try { stmt = conn.createStatement(); rset = stmt.executeQuery("SELECT " //$NON-NLS-1$ + " CASE " //$NON-NLS-1$ + " WHEN contypid = 0 THEN conrelid::regclass::name " //$NON-NLS-1$ + " ELSE contypid::regtype::name " //$NON-NLS-1$ + " END AS TABLE, " //$NON-NLS-1$ + " conname AS name, " //$NON-NLS-1$ + " consrc AS expression " //$NON-NLS-1$ + "FROM pg_constraint c " //$NON-NLS-1$ + " JOIN pg_namespace ON (connamespace = pg_namespace.oid) " //$NON-NLS-1$ + " LEFT JOIN pg_class i ON (conname = relname) " //$NON-NLS-1$ + " LEFT JOIN pg_tablespace t ON (i.reltablespace = t.oid) " //$NON-NLS-1$ + " LEFT JOIN pg_am ON (relam = pg_am.oid) " //$NON-NLS-1$ + "WHERE (nspname != 'pg_catalog' AND nspname != 'information_schema') " //$NON-NLS-1$ + " AND conislocal " //$NON-NLS-1$ + " AND contype = 'c' " //$NON-NLS-1$ + " ORDER BY 1, 2"); //$NON-NLS-1$ while (rset.next()) { monitor.worked(1); final String tabName = rset.getString(1); final String constraintName = rset.getString(2); final String conditionName = rset.getString(3); IVersionable<?> v = (IVersionable<?>) tablesMap.get(tabName); if (v == null) { LOGGER.warn("Skipping check constraint '" + constraintName + "' on table " + tabName + ": table not in the imported set."); continue; } IPostgreSqlTable t = (IPostgreSqlTable) v.getVersionnedObject().getModel(); ICheckConstraint c = CorePlugin.getTypedObjectFactory().create(ICheckConstraint.class); c.setConstrainedTable(t); c.setName(constraintName); c.setCondition(conditionName); t.addCheckConstraint(c); } } catch (SQLException e) { LOGGER.warn(MessageFormat.format(SQLGenMessages.getString("capturer.error.genericCapturerError"), //$NON-NLS-1$ context.getConnection().getDBVendor().toString()) + e.getMessage(), e); } finally { CaptureHelper.safeClose(rset, null); } // Fetching PostgreSql tablespaces fillTablespaces("r", context, tablesMap, ITablePhysicalProperties.class); //$NON-NLS-1$ // Filling primary keys in the unique key map for proper management of // tablespaces for (IBasicTable t : tables) { UniqueKeyConstraint pk = DBGMHelper.getPrimaryKey(t); if (pk != null) { ukMap.put(pk.getName(), pk); } } fillTablespaces("i", context, ukMap, IIndexPhysicalProperties.class); //$NON-NLS-1$ return tables; }
From source file:us.daveread.basicquery.BasicQuery.java
/** * Populates model with the query results. The query executed is the * currently selected query in the combo-box. * /* ww w . j av a 2s . com*/ * @param rawSqlStatement * The SQL statement to execute * @param model * The model to populate with the results * @param tripleFile * The location to write the results to as triples. */ private void execute(String rawSqlStatement, ListTableModel<Object> model, File tripleFile) { String sqlStatement = rawSqlStatement; Statement stmt = null; ResultSet result = null; ResultSetMetaData meta = null; List<Object> rowData = null; int retValue = 0; SQLWarning warning = null; int[] myType; Object value; String typeName; String colName; String metaName; boolean hasResults = false; boolean hasBLOB = false; Date connAsk = null; Date connGot = null; Date stmtGot = null; Date queryStart = null; Date queryReady = null; Date queryRSFetched = null; Date queryRSProcessed = null; long rows = 0; int cols = 0; boolean hasParams = false; final List<StatementParameter> allParams = new ArrayList<StatementParameter>(); List<Object> outParams = null; modeOfCurrentTable = whichModeValue(); mapOfCurrentTables = new HashMap<String, String>(); // Try to prevent incorrect selection of query type by checking // beginning of SQL statement for obvious stuff // First check "Select" and Describe query types if (!isOkayQueryType(getQuery().getSql())) { // If the query type is wrong, and the user doesn't override then // Get Out Of Here! return; } // If there were BLOB columns included in the last query the connection // will have been left open. Since we are executing a new query we // can close that old connection now. if (conn != null) { try { conn.close(); } catch (Throwable any) { if (LOGGER.isDebugEnabled()) { LOGGER.debug("Error (expected) closing connection", any); } } } conn = null; try { messageOut(Resources.getString("msgExecuteQuery", asQuery.isSelected() ? Resources.getString("msgQuery") : asDescribe.isSelected() ? Resources.getString("msgDescribe") : Resources.getString("msgUpdate"), sqlStatement), STYLE_BOLD); if (poolConnect.isSelected()) { messageOut(Resources.getString("msgPoolStats") + " ", STYLE_SUBTLE, false); if (getDBPool() != null) { messageOut(Resources.getString("msgPoolStatsCount", getDBPool().getNumActive() + "", getDBPool().getNumIdle() + "")); LOGGER.debug("Retrieved existing DB connection pool"); } else { LOGGER.debug("No existing DB pool"); messageOut(Resources.getString("msgPoolNone")); } } if (getDBPool() == null || /* conn == null */ !((String) connectString.getEditor().getItem()).equals(lastConnection) || !userId.getText().equals(lastUserId) || !new String(password.getPassword()).equals(lastPassword)) { removeDBPool(); lastConnection = (String) connectString.getEditor().getItem(); lastUserId = userId.getText(); lastPassword = new String(password.getPassword()); if (poolConnect.isSelected()) { setupDBPool(lastConnection, lastUserId, lastPassword); } messageOut(Resources.getString("msgConnCreated", lastConnection, lastUserId), STYLE_SUBTLE); } connAsk = new java.util.Date(); if (poolConnect.isSelected()) { conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:" + DBPOOL_NAME); LOGGER.debug("Got pooled connection"); messageOut(Resources.getString("msgGotPoolConn"), STYLE_GREEN); } else { conn = DriverManager.getConnection(lastConnection, lastUserId, lastPassword); LOGGER.debug("Got non-pooled connection"); messageOut(Resources.getString("msgGotDirectConn"), STYLE_GREEN); } if (hasParams = sqlStatement.indexOf("$PARAM[") > -1) { sqlStatement = makeParams(sqlStatement, allParams); } connGot = new java.util.Date(); conn.setAutoCommit(autoCommit.isSelected()); conn.setReadOnly(readOnly.isSelected()); if (!hasParams) { stmt = conn.createStatement(); } else { stmt = conn.prepareCall(sqlStatement); setupCall((CallableStatement) stmt, allParams); } stmtGot = new java.util.Date(); try { if (!maxRows.getSelectedItem().equals(Resources.getString("proNoLimit"))) { stmt.setMaxRows(Integer.parseInt((String) maxRows.getSelectedItem())); messageOut("\n" + Resources.getString("msgMaxRows", stmt.getMaxRows() + ""), STYLE_SUBTLE); } } catch (Exception any) { LOGGER.warn("Unable to set maximum rows", any); messageOut(Resources.getString("errFailSetMaxRows", (String) maxRows.getSelectedItem(), any.getMessage()), STYLE_YELLOW); } if (asQuery.isSelected() || asDescribe.isSelected()) { queryStart = new java.util.Date(); if (!hasParams) { int updateCount; // Execute the query synchronously stmt.execute(sqlStatement); messageOut(Resources.getString("msgQueryExecutedByDB"), STYLE_GREEN); // Process the query results and/or report status if ((updateCount = stmt.getUpdateCount()) > -1) { do { LOGGER.debug("Looking for results [update=" + updateCount + "]"); stmt.getMoreResults(); } while ((updateCount = stmt.getUpdateCount()) > -1); } result = stmt.getResultSet(); } else { result = ((PreparedStatement) stmt).executeQuery(); } queryReady = new java.util.Date(); meta = result.getMetaData(); cols = meta.getColumnCount(); } else { queryStart = new java.util.Date(); if (!hasParams) { retValue = stmt.executeUpdate(sqlStatement); } else { retValue = ((PreparedStatement) stmt).executeUpdate(); } queryReady = new java.util.Date(); } if (asQuery.isSelected()) { for (int col = 0; col < cols; ++col) { colName = meta.getColumnName(col + 1); if (colName == null || colName.trim().length() == 0) { colName = Resources.getString("msgUnnamedColumn", meta.getColumnLabel(col + 1)); } if (configDisplayColumnDataType.isSelected()) { metaName = meta.getColumnTypeName(col + 1) + " " + meta.getColumnDisplaySize(col + 1) + " ("; // have had oracle tables report large precision values // for BLOB fields that caused exception to be thrown // by getPrecision() since the value was beyond int try { metaName += meta.getPrecision(col + 1); } catch (Exception any) { metaName += "?"; LOGGER.warn("Unable to get column precision", any); } metaName += "."; metaName += meta.getScale(col + 1); metaName += ")"; colName += " [" + metaName + "]"; } model.addColumn(colName); // Keep collection of tables used for Insert and Update Menu // Selections try { mapOfCurrentTables.put(meta.getTableName(col + 1), null); } catch (Exception any) { // Probably unimplemented method - Sybase driver LOGGER.warn("Failed to obtain table name from metadata", any); messageOut(Resources.getString("errFailReqTableName", any.getMessage()), STYLE_SUBTLE); } } rowData = new ArrayList<Object>(); myType = new int[cols]; for (int col = 0; col < cols; ++col) { typeName = meta.getColumnTypeName(col + 1).toUpperCase(); if (typeName.equals("NUMBER")) { if (meta.getScale(col + 1) > 0) { myType[col] = COLUMN_DATA_TYPE_DOUBLE; // DOUBLE } else if (meta.getPrecision(col + 1) <= MAX_DIGITS_FOR_INT) { myType[col] = COLUMN_DATA_TYPE_INT; // INTEGER } else { myType[col] = COLUMN_DATA_TYPE_LONG; // LONG } } else if (typeName.equals("LONG")) { myType[col] = COLUMN_DATA_TYPE_LONG; } else if (typeName.equals("DATETIME")) { myType[col] = COLUMN_DATA_TYPE_DATETIME; // Date/Time } else if (typeName.equals("DATE")) { myType[col] = COLUMN_DATA_TYPE_DATE; // Date/Time } else if (typeName.equals("BLOB")) { myType[col] = COLUMN_DATA_TYPE_BLOB; hasBLOB = true; } else { myType[col] = 0; // Default - String } } if (tripleFile != null) { try { final RdbToRdf exporter = new RdbToRdf(tripleFile.getAbsolutePath(), getQuery().getSql(), result); exporter.run(); rows = exporter.getLatestNumberOfRowsExported(); messageOut(""); messageOut(Resources.getString("msgEndExportToFile"), STYLE_BOLD); } catch (Throwable throwable) { messageOut(Resources.getString("errFailDataSave", throwable.toString()), STYLE_RED); LOGGER.error("Failed to save data to triples file: " + tripleFile.getAbsolutePath(), throwable); } } else if (fileLogResults.isSelected()) { writeDataAsCSV(sqlStatement, model, DBRESULTS_NAME, result, myType, false); } else { while (result.next()) { ++rows; rowData = new ArrayList<Object>(); for (int col = 0; col < cols; ++col) { value = getResultField(result, col + 1, myType[col]); rowData.add(value); } model.addRowFast(rowData); hasResults = true; } model.updateCompleted(); } queryRSProcessed = new java.util.Date(); } else if (asDescribe.isSelected()) { String colLabel; meta = result.getMetaData(); myType = new int[DESC_TABLE_COLUMN_COUNT]; for (int col = 0; col < DESC_TABLE_COLUMN_COUNT; ++col) { switch (col) { case DESC_TABLE_NAME_COLUMN: // Col Name colLabel = Resources.getString("proColumnName"); myType[col] = COLUMN_DATA_TYPE_STRING; break; case DESC_TABLE_TYPE_COLUMN: // Col Type colLabel = Resources.getString("proColumnType"); myType[col] = COLUMN_DATA_TYPE_STRING; break; case DESC_TABLE_LENGTH_COLUMN: // Col Length colLabel = Resources.getString("proColumnLength"); myType[col] = COLUMN_DATA_TYPE_INT; break; case DESC_TABLE_PRECISION_COLUMN: // Col precision colLabel = Resources.getString("proColPrecision"); myType[col] = COLUMN_DATA_TYPE_INT; break; case DESC_TABLE_SCALE_COLUMN: // Col scale colLabel = Resources.getString("proColScale"); myType[col] = COLUMN_DATA_TYPE_INT; break; case DESC_TABLE_NULLS_OK_COLUMN: // Nulls Okay? colLabel = Resources.getString("proColNullsAllowed"); myType[col] = COLUMN_DATA_TYPE_STRING; break; default: // oops colLabel = Resources.getString("proColUndefined"); break; } if (configDisplayColumnDataType.isSelected()) { colLabel += " ["; colLabel += myType[col] == 0 ? Resources.getString("proColCharType") : Resources.getString("proColNumeric"); colLabel += "]"; } model.addColumn(colLabel); } rowData = new ArrayList<Object>(); for (int col = 0; col < cols; ++col) { rowData = new ArrayList<Object>(); for (int row = 0; row < DESC_TABLE_COLUMN_COUNT; ++row) { switch (row) { case DESC_TABLE_NAME_COLUMN: // Name colName = meta.getColumnName(col + 1); if (colName == null || colName.trim().length() == 0) { colName = Resources.getString("msgUnnamedColumn", meta.getColumnLabel(col + 1)); } value = colName; break; case DESC_TABLE_TYPE_COLUMN: // Type value = meta.getColumnTypeName(col + 1) + " (" + meta.getColumnType(col + 1) + ")"; break; case DESC_TABLE_LENGTH_COLUMN: // Length value = new Integer(meta.getColumnDisplaySize(col + 1)); break; case DESC_TABLE_PRECISION_COLUMN: // Precision try { value = new Integer(meta.getPrecision(col + 1)); } catch (Exception any) { value = "?"; LOGGER.warn("Unable to obtain column precision", any); } break; case DESC_TABLE_SCALE_COLUMN: // Scale value = new Integer(meta.getScale(col + 1)); break; case DESC_TABLE_NULLS_OK_COLUMN: // Nulls Okay? value = meta.isNullable(col + 1) == ResultSetMetaData.columnNullable ? Resources.getString("proYes") : meta.isNullable(col + 1) == ResultSetMetaData.columnNoNulls ? Resources.getString("proNo") : Resources.getString("proUnknown"); break; default: value = null; break; } rowData.add(value); // Keep collection of tables used for Insert and Update Menu // Selections try { mapOfCurrentTables.put(meta.getTableName(col + 1), null); } catch (Exception any) { // Probably unimplemented method - Sybase driver LOGGER.warn("Failed to obtain table name from metadata", any); messageOut(Resources.getString("errFailReqTableName", any.getMessage()), STYLE_SUBTLE); } } model.addRow(rowData); } while (result.next()) { rows++; for (int col = 0; col < cols; ++col) { result.getObject(col + 1); } } queryRSFetched = new java.util.Date(); } else { messageOut("\n" + Resources.getString("msgReturnValue") + " " + retValue, STYLE_BOLD, false); rows = stmt.getUpdateCount(); } messageOut("\n" + Resources.getString("msgRows") + " ", STYLE_NORMAL, false); if (rows == stmt.getMaxRows() && rows > 0) { messageOut("" + rows, STYLE_YELLOW); } else { messageOut("" + rows, STYLE_BOLD); } messageOut(""); } catch (SQLException sql) { LOGGER.error("Error executing SQL", sql); messageOut(Resources.getString("errFailSQL", sql.getClass().getName(), sql.getMessage()), STYLE_RED); userMessage(Resources.getString("errFailSQLText", sql.getMessage()), Resources.getString("errFailSQLTitle"), JOptionPane.ERROR_MESSAGE); while ((sql = sql.getNextException()) != null) { LOGGER.error("Next Exception", sql); } modeOfCurrentTable = -1; } catch (Throwable any) { LOGGER.error("Error executing SQL", any); messageOut(Resources.getString("errFailSQL", any.getClass().getName(), any.getMessage()), STYLE_RED); userMessage(Resources.getString("errFailSQLText", any.getMessage()), Resources.getString("errFailSQLTitle"), JOptionPane.ERROR_MESSAGE); modeOfCurrentTable = -1; } finally { fileSaveBLOBs.setEnabled(hasBLOB); setExportAvailable((hasResults && model.getRowCount() > 0) || tripleFile != null); queryMakeInsert.setEnabled( modeOfCurrentTable == Query.MODE_DESCRIBE || modeOfCurrentTable == Query.MODE_QUERY); if (hasParams) { outParams = getOutParams((CallableStatement) stmt, allParams); } LOGGER.debug("Check for more results"); try { int resultCount = 0; while (stmt.getMoreResults()) { int updateCount; ++resultCount; updateCount = stmt.getUpdateCount(); LOGGER.debug("More results [" + resultCount + "][updateCount=" + updateCount + "]"); } } catch (SQLException sql) { LOGGER.error("Failed checking for more results", sql); messageOut(Resources.getString("errFailAddlResults", sql.getClass().getName(), sql.getMessage())); } LOGGER.debug("No more results"); if (result != null) { try { result.close(); LOGGER.info("Resultset closed"); } catch (Throwable any) { LOGGER.error("Unable to close resultset", any); } } if (stmt != null) { try { warning = stmt.getWarnings(); while (warning != null) { LOGGER.warn("Stmt Warning: " + warning.toString()); messageOut(Resources.getString("errStmtWarning", warning.toString()), STYLE_YELLOW); warning = warning.getNextWarning(); } } catch (Throwable any) { LOGGER.warn("Error retrieving statement SQL warnings", any); } try { stmt.close(); LOGGER.debug("Statement closed"); } catch (Throwable any) { LOGGER.error("Unable to close statement", any); } } if (conn != null) { try { warning = conn.getWarnings(); while (warning != null) { LOGGER.warn("Connt Warning: " + warning.toString()); messageOut(Resources.getString("errConnWarning", warning.toString()), STYLE_YELLOW); warning = warning.getNextWarning(); } } catch (Throwable any) { LOGGER.warn("Error retrieving connection SQL warnings", any); } } // Close the connection if there are no BLOBs. // If the user decides to save a BLOB we will need to DB connection // to remain open, hence we only close here if there are no BLOBs if (!hasBLOB && conn != null) { try { conn.close(); conn = null; LOGGER.debug("DB Connection closed"); } catch (Throwable any) { LOGGER.error("Unable to close DB connection", any); } } reportStats(sqlStatement, connAsk, connGot, stmtGot, queryStart, queryReady, queryRSFetched, queryRSProcessed, rows, cols, asDescribe.isSelected() ? model : null, outParams); // reportResults(SQL, model); } }