List of usage examples for java.sql PreparedStatement cancel
void cancel() throws SQLException;
Statement object if both the DBMS and driver support aborting an SQL statement. From source file:org.apache.marmotta.kiwi.sparql.persistence.KiWiSparqlConnection.java
/** * Evaluate a statement pattern join or filter on the database by translating it into an appropriate SQL statement. * Copied and adapted from KiWiReasoningConnection.query() * * @param join//from w w w. j a v a 2 s. c om * @param dataset * @return */ public CloseableIteration<BindingSet, SQLException> evaluateJoin(TupleExpr join, final BindingSet bindings, final Dataset dataset) throws SQLException, InterruptedException { Preconditions .checkArgument(join instanceof Join || join instanceof Filter || join instanceof StatementPattern || join instanceof Distinct || join instanceof Slice || join instanceof Reduced); // some definitions String[] positions = new String[] { "subject", "predicate", "object", "context" }; // collect all patterns in a list, using depth-first search over the join List<StatementPattern> patterns = new PatternCollector(join).patterns; long offset = new LimitFinder(join).offset; long limit = new LimitFinder(join).limit; boolean distinct = new DistinctFinder(join).distinct; // associate a name with each pattern; the names are used in the database query to refer to the triple // that matched this pattern and in the construction of variable names for the HQL query int patternCount = 0; final Map<StatementPattern, String> patternNames = new HashMap<StatementPattern, String>(); for (StatementPattern p : patterns) { patternNames.put(p, "P" + (++patternCount)); } // find all variables occurring in the patterns and create a map to map them to // field names in the database query; each variable will have one or several field names, // one for each pattern it occurs in; field names are constructed automatically by a counter // and the pattern name to ensure the name is a valid HQL identifier int variableCount = 0; // a map for the variable names; will look like { ?x -> "V1", ?y -> "V2", ... } final Map<Var, String> variableNames = new HashMap<>(); // a map for mapping variables to field names; each variable might have one or more field names, // depending on the number of patterns it occurs in; will look like // { ?x -> ["P1_V1", "P2_V1"], ?y -> ["P2_V2"], ... } Map<Var, List<String>> queryVariables = new HashMap<>(); Map<Var, List<String>> queryVariableIds = new HashMap<>(); // a map for defining alternative context values for each variable used in the context part of a pattern Map<StatementPattern, List<Resource>> variableContexts = new HashMap<>(); for (StatementPattern p : patterns) { // check graph restrictions in datasets (MARMOTTA-340) Resource[] contexts; Value contextValue = p.getContextVar() != null ? p.getContextVar().getValue() : null; Set<URI> graphs = null; boolean emptyGraph = false; if (dataset != null) { if (p.getScope() == StatementPattern.Scope.DEFAULT_CONTEXTS) { graphs = dataset.getDefaultGraphs(); emptyGraph = graphs.isEmpty() && !dataset.getNamedGraphs().isEmpty(); } else { graphs = dataset.getNamedGraphs(); emptyGraph = graphs.isEmpty() && !dataset.getDefaultGraphs().isEmpty(); } } if (emptyGraph) { // Search zero contexts return new EmptyIteration<BindingSet, SQLException>(); } else if (graphs == null || graphs.isEmpty()) { if (contextValue != null) { contexts = new Resource[] { (Resource) contextValue }; } else { contexts = new Resource[0]; } } else if (contextValue != null) { if (graphs.contains(contextValue)) { contexts = new Resource[] { (Resource) contextValue }; } else { // Statement pattern specifies a context that is not part of // the dataset return new EmptyIteration<BindingSet, SQLException>(); } } else { contexts = new Resource[graphs.size()]; int i = 0; for (URI graph : graphs) { URI context = null; if (!SESAME.NIL.equals(graph)) { context = graph; } contexts[i++] = context; } } // build pattern Var[] fields = new Var[] { p.getSubjectVar(), p.getPredicateVar(), p.getObjectVar(), p.getContextVar() }; for (int i = 0; i < fields.length; i++) { if (fields[i] != null && !fields[i].hasValue()) { Var v = fields[i]; if (variableNames.get(v) == null) { variableNames.put(v, "V" + (++variableCount)); queryVariables.put(v, new LinkedList<String>()); queryVariableIds.put(v, new LinkedList<String>()); } String pName = patternNames.get(p); String vName = variableNames.get(v); if (hasNodeCondition(fields[i], join)) { queryVariables.get(v).add(pName + "_" + positions[i] + "_" + vName); } queryVariableIds.get(v).add(pName + "." + positions[i]); } } // build an OR query for the value of the context variable if (contexts.length > 0) { variableContexts.put(p, Arrays.asList(contexts)); } } // build the select clause by projecting for each query variable the first name StringBuilder selectClause = new StringBuilder(); if (distinct) { selectClause.append("DISTINCT "); } final List<Var> selectVariables = new LinkedList<Var>(); for (Iterator<Var> it = queryVariableIds.keySet().iterator(); it.hasNext();) { Var v = it.next(); String projectedName = variableNames.get(v); String fromName = queryVariableIds.get(v).get(0); selectClause.append(fromName); selectClause.append(" as "); selectClause.append(projectedName); if (it.hasNext()) { selectClause.append(", "); } selectVariables.add(v); } // build the from-clause of the query; the from clause is constructed as follows: // 1. for each pattern P, there will be a "KiWiTriple P" in the from clause // 2. for each variable V in P occurring in // - subject, there will be a "inner join P.subject as P_S_V" or "left outer join P.subject as P_S_V", // depending on whether the "optional" parameter is false or true // - property, there will be a "inner join P.property as P_P_V" or "left outer join p.property as P_P_V" // - object, there will be a "inner join P.object as P_O_V" or "left outer join p.object as P_O_V" // - context, there will be a "inner join P.context as P_C_V" or "left outer join p.context as P_C_V" StringBuilder fromClause = new StringBuilder(); for (Iterator<StatementPattern> it = patterns.iterator(); it.hasNext();) { StatementPattern p = it.next(); String pName = patternNames.get(p); fromClause.append("triples " + pName); Var[] fields = new Var[] { p.getSubjectVar(), p.getPredicateVar(), p.getObjectVar(), p.getContextVar() }; for (int i = 0; i < fields.length; i++) { if (fields[i] != null && !fields[i].hasValue() && hasNodeCondition(fields[i], join)) { String vName = variableNames.get(fields[i]); fromClause.append(" INNER JOIN nodes AS "); fromClause.append(pName + "_" + positions[i] + "_" + vName); fromClause.append(" ON " + pName + "." + positions[i] + " = "); fromClause.append(pName + "_" + positions[i] + "_" + vName + ".id "); } } if (it.hasNext()) { fromClause.append(",\n "); } } // build the where clause as follows: // 1. iterate over all patterns and for each resource and literal field in subject, // property, object, or context, and set a query condition according to the // nodes given in the pattern // 2. for each variable that has more than one occurrences, add a join condition // 3. for each variable in the initialBindings, add a condition to the where clause // list of where conditions that will later be connected by AND List<String> whereConditions = new LinkedList<String>(); // 1. iterate over all patterns and for each resource and literal field in subject, // property, object, or context, and set a query condition according to the // nodes given in the pattern for (StatementPattern p : patterns) { String pName = patternNames.get(p); Var[] fields = new Var[] { p.getSubjectVar(), p.getPredicateVar(), p.getObjectVar(), p.getContextVar() }; for (int i = 0; i < fields.length; i++) { // find node id of the resource or literal field and use it in the where clause // in this way we can avoid setting too many query parameters long nodeId = -1; if (fields[i] != null && fields[i].hasValue()) { Value v = valueFactory.convert(fields[i].getValue()); if (v instanceof KiWiNode) { nodeId = ((KiWiNode) v).getId(); } else { throw new IllegalArgumentException( "the values in this query have not been created by the KiWi value factory"); } if (nodeId >= 0) { String condition = pName + "." + positions[i] + " = " + nodeId; whereConditions.add(condition); } } } } // 2. for each variable that has more than one occurrences, add a join condition for (Var v : queryVariableIds.keySet()) { List<String> vNames = queryVariableIds.get(v); for (int i = 1; i < vNames.size(); i++) { String vName1 = vNames.get(i - 1); String vName2 = vNames.get(i); whereConditions.add(vName1 + " = " + vName2); } } // 3. for each variable in the initialBindings, add a condition to the where clause setting it // to the node given as binding if (bindings != null) { for (String v : bindings.getBindingNames()) { for (Map.Entry<Var, List<String>> entry : queryVariableIds.entrySet()) { if (entry.getKey().getName() != null && entry.getKey().getName().equals(v) && entry.getValue() != null && entry.getValue().size() > 0) { List<String> vNames = entry.getValue(); String vName = vNames.get(0); Value binding = valueFactory.convert(bindings.getValue(v)); if (binding instanceof KiWiNode) { whereConditions.add(vName + " = " + ((KiWiNode) binding).getId()); } else { throw new IllegalArgumentException( "the values in this binding have not been created by the KiWi value factory"); } } } } } // 4. for each pattern, ensure that the matched triple is not marked as deleted for (StatementPattern p : patterns) { String pName = patternNames.get(p); whereConditions.add(pName + ".deleted = false"); } // 5. for each filter condition, add a statement to the where clause List<ValueExpr> filters = new FilterCollector(join).filters; for (ValueExpr expr : filters) { whereConditions.add(evaluateExpression(expr, queryVariables, null)); } // 6. for each context variable with a restricted list of contexts, we add a condition to the where clause // of the form (V.id = R1.id OR V.id = R2.id ...) for (Map.Entry<StatementPattern, List<Resource>> vctx : variableContexts.entrySet()) { // the variable String varName = patternNames.get(vctx.getKey()); // the string we are building StringBuilder cCond = new StringBuilder(); cCond.append("("); for (Iterator<Resource> it = vctx.getValue().iterator(); it.hasNext();) { Value v = valueFactory.convert(it.next()); if (v instanceof KiWiNode) { long nodeId = ((KiWiNode) v).getId(); cCond.append(varName); cCond.append(".context = "); cCond.append(nodeId); if (it.hasNext()) { cCond.append(" OR "); } } else { throw new IllegalArgumentException( "the values in this query have not been created by the KiWi value factory"); } } cCond.append(")"); whereConditions.add(cCond.toString()); } // construct the where clause StringBuilder whereClause = new StringBuilder(); for (Iterator<String> it = whereConditions.iterator(); it.hasNext();) { whereClause.append(it.next()); whereClause.append("\n "); if (it.hasNext()) { whereClause.append("AND "); } } // construct limit and offset StringBuilder limitClause = new StringBuilder(); if (limit > 0) { limitClause.append("LIMIT "); limitClause.append(limit); limitClause.append(" "); } if (offset >= 0) { limitClause.append("OFFSET "); limitClause.append(offset); limitClause.append(" "); } // build the query string String queryString = "SELECT " + selectClause + "\n " + "FROM " + fromClause + "\n " + "WHERE " + whereClause + "\n " + limitClause; log.debug("original SPARQL syntax tree:\n {}", join); log.debug("constructed SQL query string:\n {}", queryString); log.debug("SPARQL -> SQL node variable mappings:\n {}", queryVariables); log.debug("SPARQL -> SQL ID variable mappings:\n {}", queryVariableIds); final PreparedStatement queryStatement = parent.getJDBCConnection().prepareStatement(queryString); if (parent.getDialect().isCursorSupported()) { queryStatement.setFetchSize(parent.getConfiguration().getCursorSize()); } Future<ResultSet> queryFuture = executorService.submit(new Callable<ResultSet>() { @Override public ResultSet call() throws Exception { try { return queryStatement.executeQuery(); } catch (SQLException ex) { if (Thread.interrupted()) { log.info("SQL query execution cancelled; not returning result (Thread={})", Thread.currentThread()); throw new InterruptedException("SPARQL query execution cancelled"); } else { throw ex; } } } }); try { ResultSet result = queryFuture.get(); ResultSetIteration<BindingSet> it = new ResultSetIteration<BindingSet>(result, true, new ResultTransformerFunction<BindingSet>() { @Override public BindingSet apply(ResultSet row) throws SQLException { MapBindingSet resultRow = new MapBindingSet(); long[] nodeIds = new long[selectVariables.size()]; for (int i = 0; i < selectVariables.size(); i++) { nodeIds[i] = row.getLong(variableNames.get(selectVariables.get(i))); } KiWiNode[] nodes = parent.loadNodesByIds(nodeIds); for (int i = 0; i < selectVariables.size(); i++) { Var v = selectVariables.get(i); resultRow.addBinding(v.getName(), nodes[i]); } if (bindings != null) { for (Binding binding : bindings) { resultRow.addBinding(binding); } } return resultRow; } }); // materialize result to avoid having more than one result set open at the same time return new CloseableIteratorIteration<BindingSet, SQLException>(Iterations.asList(it).iterator()); } catch (InterruptedException | CancellationException e) { log.info("SPARQL query execution cancelled"); queryFuture.cancel(true); queryStatement.cancel(); queryStatement.close(); throw new InterruptedException("SPARQL query execution cancelled"); } catch (ExecutionException e) { log.error("error executing SPARQL query", e.getCause()); if (e.getCause() instanceof SQLException) { throw (SQLException) e.getCause(); } else if (e.getCause() instanceof InterruptedException) { throw (InterruptedException) e.getCause(); } else { throw new SQLException("error executing SPARQL query", e); } } }
From source file:SeedGenerator.MainForm.java
private void jButtonPrepareEndpointLcnWordTableActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonPrepareEndpointLcnWordTableActionPerformed try {/* w w w .j a v a 2s .com*/ PreparedStatement pstmtendpoints = con .prepareStatement("select id, endpointurl from endpoints where lastCheckedDate is null;"); ResultSet rs1 = pstmtendpoints.executeQuery(); int endpointid = 0; int queryid = 27;//class with label collector while (rs1.next()) { try { endpointid = rs1.getInt("id"); PreparedStatement pstmt = con.prepareStatement( "select s,p,o from endpointtriples where queryid=? and endpointid=?;"); pstmt.setInt(1, queryid); pstmt.setInt(2, endpointid); // execute the query, and get a java resultset ResultSet rs = pstmt.executeQuery(); PreparedStatement insertpstmt = con.prepareStatement( "insert into recommender_class_label_hypernym (endpointid,local_class_name, class_count, word, hypernym) values(?,?,?,?,?);"); // iterate through the java resultset HashMap<String, Integer> wordCount = new HashMap(); HashMap<String, Integer> lcnWordCount = new HashMap(); int totalNumberOfWords = 0; while (rs.next()) { String classURI = rs.getString("s"); int classcount = Integer.parseInt(rs.getString("o")); PreparedStatement pstmtcounts = con.prepareStatement( "select s,o from endpointtriples where queryid=? and endpointid=? and s =?;"); pstmtcounts.setInt(1, 26); pstmtcounts.setInt(2, endpointid); pstmtcounts.setString(3, classURI); ResultSet rs2 = pstmtcounts.executeQuery(); String className, label; if (rs2.next()) { label = rs2.getString("o"); className = limitString(classURI.split("/")[classURI.split("/").length - 1], 45); pstmtcounts.cancel(); rs2.close(); // String p = rs.getString("p"); // String o = rs.getString("o"); if (label != null) { String words[] = label.split(" "); for (String word : words) { String cleanword; cleanword = word.replaceAll("[^\\p{L}\\p{Nd}]+", ""); if (!cleanword.equals("")) { if (!word.equals(cleanword)) { word = cleanword;//System.out.println(word+"--"+cleanword); } if (wordCount.containsKey(word)) { int currentCount = wordCount.get(word); wordCount.replace(word, currentCount + 1); } else { wordCount.put(word, 1); } for (String hyp : getHypernyms(word)) { insertpstmt.setInt(1, endpointid); // if (s.split("/")[s.split("/").length - 1].length() > 45) { // insertpstmt.setString(2, s.split("/")[s.split("/").length - 1].substring(0, 44)); // } else { // insertpstmt.setString(2, s.split("/")[s.split("/").length - 1]); // } insertpstmt.setString(2, className); insertpstmt.setInt(3, classcount); insertpstmt.setString(4, word); insertpstmt.setString(5, hyp); insertpstmt.addBatch(); // insertpstmt.setInt(6,); } } totalNumberOfWords++; } } } else { // System.out.println("bos"); } } insertpstmt.executeBatch(); PreparedStatement pstmtupdateendpoint = con .prepareStatement("update endpoints set lastCheckedDate=? where id=?;"); pstmtupdateendpoint.setTimestamp(1, new java.sql.Timestamp(System.currentTimeMillis())); pstmtupdateendpoint.setInt(2, endpointid); pstmtupdateendpoint.execute(); pstmtupdateendpoint.close(); //String firstName = rs.getString("url"); pstmt.close(); rs.close(); // Iterator it = lcnWordCount.entrySet().iterator(); // while (it.hasNext()) { // Map.Entry pair = (Map.Entry) it.next(); // //<> // if (Integer.parseInt(pair.getValue().toString()) > 1) { // PreparedStatement insertpstmt // = con.prepareStatement("insert into recommender_class_label_hypernym (endpointid,local_class_name, class_count, word, hypernym,tf) values(?,?,?,?,?);"); // if (pair.getKey().toString().length() > 44) { // insertpstmt.setString(1, pair.getKey().toString().substring(0, 44)); // } else { // insertpstmt.setString(1, pair.getKey().toString()); // } // insertpstmt.setInt(2, Integer.parseInt(pair.getValue().toString())); // insertpstmt.setInt(3, endpointid); // insertpstmt.setInt(4, queryid); // insertpstmt.executeUpdate(); // insertpstmt.close(); // } // it.remove(); // avoids a ConcurrentModificationException // } // PreparedStatement updatepstmt // = con.prepareStatement("update endpoints set commentsWordCount=? where id=?;"); // updatepstmt.setInt(1, totalNumberOfWords); // updatepstmt.setInt(2, endpointid); // updatepstmt.executeUpdate(); // updatepstmt.close(); } catch (Exception e) { System.err.println("inner while" + e.getMessage()); } } pstmtendpoints.close(); } catch (Exception e) { //System.err.println("Got an exception! "); System.err.println(e.getMessage()); } // TODO add your handling code here: }