[phylr] r55 committed - Fixed database connection problem.
codesite...@google.com
Author: dazhi.jiao
Date: Thu Aug 13 13:00:59 2009
Log: Fixed database connection problem.
http://code.google.com/p/phylr/source/detail?r=55
Added:
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/BioSQLResultSetHandler.java
Modified:
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/BioSQLDCRecordResolver.java
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/BioSQLQueryTranslator.java
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/RecordResolver.java
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/RelationalQueryResult.java
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/RelationalRecordIterator.java
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/SRWRelationalDatabase.java
/trunk/phylr-gsoc/src/main/resources/BioSQL.props
=======================================
--- /dev/null
+++
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/BioSQLResultSetHandler.java
Thu Aug 13 13:00:59 2009
@@ -0,0 +1,24 @@
+package org.nescent.phylr.relational;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+
+import org.apache.commons.dbutils.ResultSetHandler;
+
+public class BioSQLResultSetHandler implements ResultSetHandler {
+
+ public List handle(ResultSet rs) throws SQLException {
+ List result = new ArrayList();
+
+ while (rs.next()) {
+ Object[] row = new Object[2];
+ row[0] = rs.getInt(1);
+ row[1] = rs.getString(2);
+ result.add(row);
+ }
+ return result;
+ }
+
+}
=======================================
---
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/BioSQLDCRecordResolver.java
Wed Jul 29 07:01:00 2009
+++
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/BioSQLDCRecordResolver.java
Thu Aug 13 13:00:59 2009
@@ -31,36 +31,33 @@
import org.apache.commons.logging.LogFactory;
import org.oclc.os.SRW.Record;
-
/**
- *
+ *
* @author djiao
*/
public class BioSQLDCRecordResolver implements RecordResolver {
- static Log log=LogFactory.getLog(BioSQLDCRecordResolver.class);
- static final String
LUCENE_SCHEMA_ID="info:srw/schema/1/LuceneDocument";
-
- /** Creates a new instance of BasicLuceneRecordResolver */
- public BioSQLDCRecordResolver() {
- }
-
- public void init(Properties properties) {
- }
-
- public Record resolve(ResultSet result, String IdFieldName,
ExtraDataType extraDataType) {
- String str = null;
- try {
- int id = result.getInt(1);
- String xml = result.getString(2); // it expects a list dc elements
- StringBuffer sb = new StringBuffer("<dc
xmlns:dc=\"http://purl.org/dc/elements/1.1/\">");
- sb.append(xml);
- sb.append("</dc>");
- str = sb.toString();
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
+ static Log log = LogFactory.getLog(BioSQLDCRecordResolver.class);
+ static final String LUCENE_SCHEMA_ID = "info:srw/schema/1/LuceneDocument";
+
+ /** Creates a new instance of BasicLuceneRecordResolver */
+ public BioSQLDCRecordResolver() {
+ }
+
+ public void init(Properties properties) {
+ }
+
+ public Record resolve(Object hit, String IdFieldName,
+ ExtraDataType extraDataType) {
+ Object[] result = (Object[])hit;
+ String str = null;
+ int id = (Integer) result[0];
+ String xml = (String) result[1]; // it expects a list dc elements
+ StringBuffer sb = new StringBuffer(
+ "<dc xmlns:dc=\"http://purl.org/dc/elements/1.1/\">");
+ sb.append(xml);
+ sb.append("</dc>");
+ str = sb.toString();
return new Record(str, "info:srw/schema/1/dc-v1.1");
- }
-}
+ }
+}
=======================================
---
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/BioSQLQueryTranslator.java
Wed Jul 29 13:48:57 2009
+++
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/BioSQLQueryTranslator.java
Thu Aug 13 13:00:59 2009
@@ -22,27 +22,8 @@
private static Log log= LogFactory.getLog(BioSQLQueryTranslator.class);
SRWRelationalDatabase database = null;
Map<String, String> indexMapping = null;
-
- private String outTreeSql = "select tree_id, xmlagg(xvalue) from ( " +
- "SELECT tree_id, xmlforest(tq.value as \"dc:identifier\") as
xvalue " +
- "FROM tree_qualifier_value tq, term te " +
- "WHERE tq.term_id = te.term_id and te.name='dc.identifier' " +
- "UNION ALL " +
- "SELECT tq.tree_id, xmlforest(tq.value as \"dc:title\") as xvalue " +
- "FROM tree_qualifier_value tq, term te " +
- "WHERE tq.term_id = te.term_id and te.name='dc.title' " +
- "UNION ALL " +
- "SELECT tq.tree_id, xmlforest(tq.value as \"dc:abstract\") as
xvalue " +
- "FROM tree_qualifier_value tq, term te " +
- "WHERE tq.term_id = te.term_id and te.name='dc.abstract' " +
- "UNION ALL " +
- "SELECT tq.tree_id, xmlforest(tq.value as \"dc:contributor\") as
xvalue " +
- "FROM tree_qualifier_value tq, term te " +
- "WHERE tq.term_id = te.term_id and te.name='dc.contributor' " +
- ") as tab " +
- "WHERE tree_id in (?) " +
- "GROUP BY tree_id";
- private String outNodeSql = "";
+ String outTreeSql = null;
+ String outNodeSql = null;
public void init(Properties properties, SRWRelationalDatabase ldb)
throws InstantiationException {
this.database = ldb;
=======================================
---
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/RecordResolver.java
Wed Jul 29 07:01:00 2009
+++
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/RecordResolver.java
Thu Aug 13 13:00:59 2009
@@ -40,7 +40,7 @@
* @param extraDataType - nonstandard SRW request parameters
* @return record if found.
*/
- public Record resolve(ResultSet hits, String IdFieldName,
ExtraDataType extraDataType);
+ public Record resolve(Object hit, String IdFieldName, ExtraDataType
extraDataType);
/**
* Initialize the resolver.
=======================================
---
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/RelationalQueryResult.java
Wed Jul 29 13:27:43 2009
+++
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/RelationalQueryResult.java
Thu Aug 13 13:00:59 2009
@@ -2,6 +2,8 @@
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.util.List;
+import java.util.ListIterator;
import gov.loc.www.zing.srw.ExtraDataType;
import org.apache.commons.logging.Log;
@@ -12,7 +14,7 @@
public class RelationalQueryResult extends QueryResult {
static final Log log=LogFactory.getLog(RelationalQueryResult.class);
- ResultSet hits=null;
+ List hits=null;
SRWRelationalDatabase ldb=null;
long count = -1;
RelationalRecordIterator localIterator = null;
@@ -22,7 +24,7 @@
}
/** Creates a new instance of LuceneQueryResult */
- public RelationalQueryResult(SRWRelationalDatabase ldb, ResultSet
hits) {
+ public RelationalQueryResult(SRWRelationalDatabase ldb, List hits) {
this.ldb=ldb;
this.hits=hits;
}
@@ -30,17 +32,6 @@
@Override
public void close() {
super.close();
- log.info("RelationalResultSet close");
- try {
- if (this.hits != null) {
- if (!this.hits.isClosed()) {
- this.hits.close();
- }
- }
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
}
@Override
@@ -50,14 +41,7 @@
if(hits==null) {
count = 0;
} else {
- try {
- hits.last();
- count = hits.getRow();
- hits.beforeFirst();
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
+ count = hits.size();
}
return count;
}
@@ -68,14 +52,9 @@
}
@Override
- public RecordIterator newRecordIterator(long whichRec, int numRecs,
- String schemaId, ExtraDataType edt) throws InstantiationException {
+ public RecordIterator newRecordIterator(long whichRec, int numRecs,
String schemaId, ExtraDataType edt) throws InstantiationException {
log.debug("whichRec="+whichRec+", numRecs="+numRecs+",
schemaId="+schemaId+", edt="+edt);
- if (this.localIterator == null) {
- this.localIterator = new RelationalRecordIterator(whichRec,
schemaId, this, (RecordResolver)ldb.resolvers.get(schemaId), edt);
- } else {
- localIterator.go(whichRec);
- }
- return localIterator;
+ List sublist = this.hits.subList(new Long(whichRec).intValue()-1,
new Long(whichRec + numRecs).intValue()-1);
+ return new RelationalRecordIterator(sublist, schemaId, edt,
(RecordResolver)ldb.resolvers.get(schemaId), null);
}
}
=======================================
---
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/RelationalRecordIterator.java
Wed Jul 29 13:27:43 2009
+++
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/RelationalRecordIterator.java
Thu Aug 13 13:00:59 2009
@@ -25,6 +25,8 @@
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.util.List;
+import java.util.ListIterator;
import java.util.NoSuchElementException;
import org.apache.commons.logging.Log;
@@ -40,56 +42,28 @@
static final Log log=LogFactory.getLog(RelationalRecordIterator.class);
ExtraDataType edt;
- int numRecs;
- long whichRec;
- RelationalQueryResult result;
RecordResolver resolver;
String schemaId;
+ List result;
+ ListIterator iterator;
+ String idFieldName;
/** Creates a new instance of LuceneRecordIterator */
- public RelationalRecordIterator(long whichRec, String schemaId,
- RelationalQueryResult result, RecordResolver resolver, ExtraDataType
edt) {
- log.debug("whichRec="+whichRec+", schemaId="+schemaId+",
result="+result+", resolver="+resolver+", edt="+edt);
- this.whichRec=whichRec;
+ public RelationalRecordIterator(List result, String schemaId,
ExtraDataType edt, RecordResolver resolver, String idFIeldName) {
+ log.debug("schemaId="+schemaId+", result="+result+",
resolver="+resolver+", edt="+edt);
this.schemaId=schemaId;
this.result=result;
this.resolver=resolver;
this.edt=edt;
- }
-
- public void go(long whichRec) {
- ResultSet hits = result.hits;
- try {
- hits.absolute(new Long(whichRec-1).intValue());
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
+ this.idFieldName = idFIeldName;
+ this.iterator = result.listIterator();
}
public void close() {
- try {
- if (this.result.hits != null) {
- if (!this.result.hits.isClosed())
- this.result.hits.close();
- }
- } catch (Exception ex) {
- log.error("Failed to close ResultSet object: " + ex.getMessage());
- }
}
public boolean hasNext() {
- log.debug("whichRec="+whichRec+",
result.getNumberOfRecords()="+result.getNumberOfRecords());
- try {
- if (result.hits.isAfterLast()) {
- return false;
- } else {
- return true;
- }
- } catch (SQLException e) {
- log.error(e.getMessage());
- }
- return true;
+ return iterator.hasNext();
}
public Object next() throws NoSuchElementException {
@@ -97,20 +71,16 @@
}
public Record nextRecord() throws NoSuchElementException {
- ResultSet hits = result.hits;
try {
- hits.next();
- return resolver.resolve(hits, result.ldb.idFieldName, edt);
+ return resolver.resolve(iterator.next(), idFieldName, edt);
} catch(Exception e) {
log.error(e, e);
- log.error("whichRec="+whichRec);
throw new NoSuchElementException(e.getMessage());
}
- finally {
- whichRec++;
- }
}
- public void remove() {
- }
-}
+ public void remove() {
+ // TODO Auto-generated method stub
+
+ }
+}
=======================================
---
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/SRWRelationalDatabase.java
Thu Aug 13 11:30:51 2009
+++
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/SRWRelationalDatabase.java
Thu Aug 13 13:00:59 2009
@@ -8,10 +8,12 @@
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Hashtable;
+import java.util.List;
import java.util.Properties;
import java.util.StringTokenizer;
@@ -19,6 +21,10 @@
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
+import org.apache.commons.dbutils.DbUtils;
+import org.apache.commons.dbutils.QueryRunner;
+import org.apache.commons.dbutils.ResultSetHandler;
+import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.pool.ObjectPool;
@@ -39,16 +45,6 @@
CqlQueryTranslator translator = null;
Hashtable<String, RecordResolver> resolvers = new Hashtable<String,
RecordResolver>();
String idFieldName = null, indexInfo = null;
-
- Connection connection = null;
-
- public Connection getConnection() {
- return connection;
- }
-
- public void setConnection(Connection connection) {
- this.connection = connection;
- }
@Override
public void addRenderer(String schemaName, String schemaID, Properties
props)
@@ -186,8 +182,11 @@
public QueryResult getQueryResult(String queryStr,
SearchRetrieveRequestType request) throws InstantiationException {
log.debug("entering SRWRelationalDatabase.getQueryResult");
+
+ Connection connection = null;
ResultSet results = null;
- Statement stmt = null;
+ Statement stmt = null;
+
try {
if (log.isDebugEnabled())
log.debug("query=" + queryStr);
@@ -205,11 +204,26 @@
log.info("lucene search=" + query);
// perform search
+ connection = DriverManager.getConnection("jdbc:apache:commons:dbcp:" +
dbname);
+ /*
stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
-
- results = stmt.executeQuery(query);
- return new RelationalQueryResult(this, results);
+ */
+ QueryRunner run = new QueryRunner();
+
+ // Create a ResultSetHandler implementation to convert the
+ // first row into an Object[].
+ String handlerName = dbProperties.getProperty(
+ "SRWRelationalDatabase.ResultSetHandler",
+ "org.nescent.phylr.relational.BioSQLResultSetHandler");
+ Class resultSetHandler = Class.forName(handlerName);
+ log.debug("creating instance of class " + resultSetHandler);
+ ResultSetHandler handler = (ResultSetHandler)
resultSetHandler.newInstance();
+
+ //results = stmt.executeQuery(query);
+ List result = (List)run.query(connection, query, handler);
+ return new RelationalQueryResult(this, result);
+
} catch (SRWDiagnostic e) {
RelationalQueryResult lqr = new RelationalQueryResult();
lqr.addDiagnostic(e.getCode(), e.getAddInfo());
@@ -229,7 +243,20 @@
RelationalQueryResult lqr = new RelationalQueryResult();
lqr.addDiagnostic(SRWDiagnostic.GeneralSystemError, e.getMessage());
return lqr;
+ } catch (ClassNotFoundException e) {
+ log.error(e, e);
+ RelationalQueryResult lqr = new RelationalQueryResult();
+ lqr.addDiagnostic(SRWDiagnostic.GeneralSystemError, e.getMessage());
+ return lqr;
+ } catch (IllegalAccessException e) {
+ log.error(e, e);
+ RelationalQueryResult lqr = new RelationalQueryResult();
+ lqr.addDiagnostic(SRWDiagnostic.GeneralSystemError, e.getMessage());
+ return lqr;
} finally {
+ try {
+ DbUtils.close(connection);
+ } catch (SQLException e) {}
}
}
@@ -305,14 +332,6 @@
log.error("Database Connection URL not specified for database " +
dbname);
throw new InstantiationException("Database Connection URL not
specified");
}
-
- try {
- connection = DriverManager.getConnection("jdbc:apache:commons:dbcp:" +
dbname);
- } catch (Exception e) {
- log.error("Unable to create connection for database " + dbname);
- log.error(e, e);
- throw new InstantiationException(e.getMessage());
- }
String translatorName = dbProperties.getProperty(
"SRWRelationalDatabase.CqlToSQLTranslator",
=======================================
--- /trunk/phylr-gsoc/src/main/resources/BioSQL.props Wed Jul 29 13:48:57
2009
+++ /trunk/phylr-gsoc/src/main/resources/BioSQL.props Thu Aug 13 13:00:59
2009
@@ -21,10 +21,11 @@
# Database Connection Settings
SRWRelationalDatabase.driver=org.postgresql.Driver
SRWRelationalDatabase.user=djiao
-SRWRelationalDatabase.password=phylr
-SRWRelationalDatabase.url=jdbc:postgresql://pyran.ath.cx/biosql
+SRWRelationalDatabase.password=45fFsw5lA1
+SRWRelationalDatabase.url=jdbc:postgresql://karya.nescent.org/biosql
SRWRelationalDatabase.CqlToSQLTranslator=org.nescent.phylr.relational.BioSQLQueryTranslator
+SRWRelationalDatabase.ResultSetHandler=org.nescent.phylr.relational.BioSQLResultSetHandler
# Possible result formats
#xmlSchemas=nexml,dc, LuceneDocument