- Timestamp:
- 02/25/10 20:57:30 (2 years ago)
- Files:
-
- 1 modified
Legend:
- Unmodified
- Added
- Removed
-
trunk/org.bridgedb.rdb/src/org/bridgedb/rdb/SimpleGdbImpl2.java
r308 r316 44 44 private static final int GDB_COMPAT_VERSION = 2; //Preferred schema version 45 45 46 private final SimpleGdb. LazyPst pstDatasources = new SimpleGdb.LazyPst(46 private final SimpleGdb.QueryLifeCycle qDatasources = new SimpleGdb.QueryLifeCycle( 47 47 "SELECT codeRight FROM link GROUP BY codeRight" 48 48 ); 49 private final SimpleGdb. LazyPst pstInfo = new SimpleGdb.LazyPst(49 private final SimpleGdb.QueryLifeCycle qInfo = new SimpleGdb.QueryLifeCycle( 50 50 "SELECT * FROM info" 51 51 ); 52 private final SimpleGdb. LazyPst pstXrefExists = new SimpleGdb.LazyPst(52 private final SimpleGdb.QueryLifeCycle qXrefExists = new SimpleGdb.QueryLifeCycle( 53 53 "SELECT id FROM " + "datanode" + " WHERE " + 54 54 "id = ? AND code = ?" 55 55 ); 56 private final SimpleGdb. LazyPst pstBackpage = new SimpleGdb.LazyPst(56 private final SimpleGdb.QueryLifeCycle qBackpage = new SimpleGdb.QueryLifeCycle( 57 57 "SELECT backpageText FROM datanode " + 58 58 " WHERE id = ? AND code = ?" 59 59 ); 60 private final SimpleGdb. LazyPst pstAttribute = new SimpleGdb.LazyPst(60 private final SimpleGdb.QueryLifeCycle qAttribute = new SimpleGdb.QueryLifeCycle( 61 61 "SELECT attrvalue FROM attribute " + 62 62 " WHERE id = ? AND code = ? AND attrname = ?" 63 63 ); 64 private final SimpleGdb. LazyPst pstAllAttributes = new SimpleGdb.LazyPst(64 private final SimpleGdb.QueryLifeCycle qAllAttributes = new SimpleGdb.QueryLifeCycle( 65 65 "SELECT attrname, attrvalue FROM attribute " + 66 66 " WHERE id = ? AND code = ?" 67 67 ); 68 private final SimpleGdb. LazyPst pstAttributesSet = new SimpleGdb.LazyPst(68 private final SimpleGdb.QueryLifeCycle qAttributesSet = new SimpleGdb.QueryLifeCycle( 69 69 "SELECT attrname FROM attribute GROUP BY attrname" 70 70 ); 71 private final SimpleGdb. LazyPst pstCrossRefs = new SimpleGdb.LazyPst(71 private final SimpleGdb.QueryLifeCycle qCrossRefs = new SimpleGdb.QueryLifeCycle ( 72 72 "SELECT dest.idRight, dest.codeRight FROM link AS src JOIN link AS dest " + 73 73 "ON src.idLeft = dest.idLeft and src.codeLeft = dest.codeLeft " + 74 74 "WHERE src.idRight = ? AND src.codeRight = ?" 75 75 ); 76 private final SimpleGdb. LazyPst pstCrossRefsWithCode = new SimpleGdb.LazyPst(76 private final SimpleGdb.QueryLifeCycle qCrossRefsWithCode = new SimpleGdb.QueryLifeCycle ( 77 77 "SELECT dest.idRight, dest.codeRight FROM link AS src JOIN link AS dest " + 78 78 "ON src.idLeft = dest.idLeft and src.codeLeft = dest.codeLeft " + 79 79 "WHERE src.idRight = ? AND src.codeRight = ? AND dest.codeRight = ?" 80 80 ); 81 private final SimpleGdb. LazyPst pstRefsByAttribute = new SimpleGdb.LazyPst(81 private final SimpleGdb.QueryLifeCycle qRefsByAttribute = new SimpleGdb.QueryLifeCycle ( 82 82 "SELECT datanode.id, datanode.code FROM datanode " + 83 83 " LEFT JOIN attribute ON attribute.code = datanode.code AND attribute.id = datanode.id " + 84 84 "WHERE attrName = ? AND attrValue = ?" 85 85 ); 86 private final SimpleGdb. LazyPst pstFreeSearch = new SimpleGdb.LazyPst(86 private final SimpleGdb.QueryLifeCycle qFreeSearch = new SimpleGdb.QueryLifeCycle ( 87 87 "SELECT id, code FROM datanode WHERE " + 88 88 "LOWER(ID) LIKE ?" 89 89 ); 90 private final SimpleGdb. LazyPst pstAttributeSearch = new SimpleGdb.LazyPst(90 private final SimpleGdb.QueryLifeCycle qAttributeSearch = new SimpleGdb.QueryLifeCycle ( 91 91 "SELECT id, code, attrvalue FROM attribute WHERE " + 92 92 "attrname = 'Symbol' AND LOWER(attrvalue) LIKE ?" 93 93 ); 94 private final SimpleGdb. LazyPst pstIdSearchWithAttributes = new SimpleGdb.LazyPst(94 private final SimpleGdb.QueryLifeCycle qIdSearchWithAttributes = new SimpleGdb.QueryLifeCycle ( 95 95 "SELECT id, code, attrvalue FROM attribute WHERE " + 96 96 "attrname = 'Symbol' AND LOWER(ID) LIKE ?" 97 97 ); 98 98 99 99 /** {@inheritDoc} */ 100 100 public boolean xrefExists(Xref xref) throws IDMapperException 101 101 { 102 final QueryLifeCycle pst = qXrefExists; 102 103 try 103 104 { 104 PreparedStatement pst = pstXrefExists.getPreparedStatement();105 pst.init(); 105 106 pst.setString(1, xref.getId()); 106 107 pst.setString(2, xref.getDataSource().getSystemCode()); … … 116 117 throw new IDMapperException (e); 117 118 } 119 finally {pst.cleanup(); } 118 120 return false; 119 121 } … … 126 128 private Map<String, String> getInfo() throws IDMapperException 127 129 { 130 final QueryLifeCycle pst = qInfo; 128 131 Map<String, String> result = new HashMap<String, String>(); 129 132 try 130 133 { 131 PreparedStatement pst = pstInfo.getPreparedStatement();134 pst.init(); 132 135 ResultSet rs = pst.executeQuery(); 133 136 … … 162 165 private String getBpInfo(Xref ref) throws IDMapperException 163 166 { 167 final QueryLifeCycle pst = qBackpage; 164 168 try { 165 PreparedStatement pst = pstBackpage.getPreparedStatement();169 pst.init(); 166 170 pst.setString (1, ref.getId()); 167 171 pst.setString (2, ref.getDataSource().getSystemCode()); … … 174 178 return result; 175 179 } catch (SQLException e) { throw new IDMapperException (e); } //Gene not found 180 finally {pst.cleanup(); } 176 181 } 177 182 … … 179 184 public Set<Xref> mapID (Xref idc, DataSource... resultDs) throws IDMapperException 180 185 { 186 final QueryLifeCycle pst = resultDs.length != 1 ? qCrossRefs : qCrossRefsWithCode; 181 187 Set<Xref> refs = new HashSet<Xref>(); 182 188 … … 184 190 try 185 191 { 186 PreparedStatement pst; 187 if (resultDs.length != 1) 188 { 189 pst = pstCrossRefs.getPreparedStatement(); 190 } 191 else 192 { 193 pst = pstCrossRefsWithCode.getPreparedStatement(); 194 pst.setString(3, resultDs[0].getSystemCode()); 195 } 196 192 pst.init(); 197 193 pst.setString(1, idc.getId()); 198 194 pst.setString(2, idc.getDataSource().getSystemCode()); 195 if (resultDs.length == 1) pst.setString(3, resultDs[0].getSystemCode()); 199 196 200 197 Set<DataSource> dsFilter = new HashSet<DataSource>(Arrays.asList(resultDs)); … … 214 211 throw new IDMapperException (e); 215 212 } 213 finally {pst.cleanup(); } 216 214 217 215 return refs; … … 222 220 // Logger.log.trace("Fetching cross references by attribute: " + attrName + " = " + attrValue); 223 221 List<Xref> refs = new ArrayList<Xref>(); 224 222 final QueryLifeCycle pst = qRefsByAttribute; 225 223 try { 226 PreparedStatement pst = pstRefsByAttribute.getPreparedStatement();224 pst.init(); 227 225 pst.setString(1, attrName); 228 226 pst.setString(2, attrValue); … … 235 233 throw new IDMapperException (e); 236 234 } 235 finally {pst.cleanup(); } 237 236 // Logger.log.trace("End fetching cross references by attribute"); 238 237 return refs; … … 373 372 { 374 373 Set<Xref> result = new HashSet<Xref>(); 374 final QueryLifeCycle pst = qFreeSearch; 375 375 try { 376 PreparedStatement ps1 = pstFreeSearch.getPreparedStatement(); 377 ps1.setQueryTimeout(QUERY_TIMEOUT); 378 if(limit > NO_LIMIT) 379 { 380 ps1.setMaxRows(limit); 381 } 382 383 ps1.setString(1, "%" + text.toLowerCase() + "%"); 384 ResultSet r = ps1.executeQuery(); 376 pst.init(limit); 377 pst.setString(1, "%" + text.toLowerCase() + "%"); 378 ResultSet r = pst.executeQuery(); 385 379 while(r.next()) { 386 380 String id = r.getString(1); … … 394 388 throw new IDMapperException(e); 395 389 } 390 finally {pst.cleanup(); } 396 391 return result; 397 392 } … … 530 525 { 531 526 Set<DataSource> result = new HashSet<DataSource>(); 527 final QueryLifeCycle pst = qDatasources; 532 528 try 533 529 { 534 PreparedStatement pst = pstDatasources.getPreparedStatement();530 pst.init(); 535 531 ResultSet rs = pst.executeQuery(); 536 532 while (rs.next()) … … 544 540 throw new IDMapperException(ignore); 545 541 } 542 finally {pst.cleanup(); } 546 543 return result; 547 544 } … … 585 582 { 586 583 Set<String> result = new HashSet<String>(); 584 final QueryLifeCycle pst = qAttribute; 587 585 588 586 if (ATTRIBUTES_FROM_BACKPAGE.containsKey(attrname)) … … 601 599 602 600 try { 603 PreparedStatement pst = pstAttribute.getPreparedStatement();601 pst.init(); 604 602 pst.setString (1, ref.getId()); 605 603 pst.setString (2, ref.getDataSource().getSystemCode()); … … 612 610 return result; 613 611 } catch (SQLException e) { throw new IDMapperException ("Xref:" + ref + ", Attribute: " + attrname, e); } // Database unavailable 612 finally {pst.cleanup(); } 614 613 } 615 614 … … 619 618 { 620 619 Map<String, Set<String>> result = new HashMap<String, Set<String>>(); 620 final QueryLifeCycle pst = qAllAttributes; 621 621 622 622 String bpInfo = getBpInfo(ref); … … 637 637 638 638 try { 639 PreparedStatement pst = pstAllAttributes.getPreparedStatement();639 pst.init(); 640 640 pst.setString (1, ref.getId()); 641 641 pst.setString (2, ref.getDataSource().getSystemCode()); … … 658 658 return result; 659 659 } catch (SQLException e) { throw new IDMapperException ("Xref:" + ref, e); } // Database unavailable 660 finally {pst.cleanup(); } 660 661 } 661 662 … … 680 681 { 681 682 Map<Xref, String> result = new HashMap<Xref, String>(); 683 final QueryLifeCycle pst = (MATCH_ID.equals (attrType)) ? 684 qIdSearchWithAttributes : qAttributeSearch; 682 685 try { 683 PreparedStatement pst = (MATCH_ID.equals (attrType)) ? 684 pstIdSearchWithAttributes.getPreparedStatement() : pstAttributeSearch.getPreparedStatement(); 685 pst.setQueryTimeout(QUERY_TIMEOUT); 686 if(limit > NO_LIMIT) pst.setMaxRows(limit); 686 pst.init(limit); 687 687 pst.setString(1, "%" + query.toLowerCase() + "%"); 688 688 ResultSet r = pst.executeQuery(); … … 698 698 throw new IDMapperException (e); 699 699 } 700 finally {pst.cleanup(); } 700 701 return result; 701 702 } … … 705 706 { 706 707 Set<String> result = new HashSet<String>(); 708 final QueryLifeCycle pst = qAttributesSet; 707 709 try 708 710 { 709 PreparedStatement pst = pstAttributesSet.getPreparedStatement();711 pst.init(); 710 712 ResultSet rs = pst.executeQuery(); 711 713 while (rs.next()) … … 718 720 throw new IDMapperException(ignore); 719 721 } 722 finally {pst.cleanup(); } 720 723 return result; 721 724 }
