...
- You can use zxJDBC, a pythonic (DB API 2.0) interface allowing the use of databases accessible via JDBC. The driver (postgresql-*.jar or ojdbc6.jar) used here is available in classpath because we copied it from /dspace/lib/.
- You can use Java libraries, demonstrated here by java.util.Properties used to read dspace.cfg.
- Here we read the database driver, connection string, user and password from dspace.cfg and then pass it to zxJDBC to crate a connection.
- We could use DB API 2.0 methods like cursor.fetchall() to get query results. Here I chose to use the Python zip() function to return the query results in a custom format.
- You can use a context manager (Python "with" keyword) around a zxJDBC cursor to manage the scope of the DB transaction.
- You can't use the Python "with" keyword around the java.util.Properties as it is a Java class which doesn't implement a Python context manager.
| Code Block | ||||
|---|---|---|---|---|
| ||||
# -*- coding: utf-8 -*-
from javax.servlet.http import HttpServlet
from com.ziclix.python.sql import zxJDBC
from java.util import Properties
DSPACE_DIR = '/dspace'
class db_example(HttpServlet):
def doGet(self, request, response):
self.doPost(request, response)
def doPost(self, request, response):
response.setContentType("text/html")
response.setCharacterEncoding("utf-8")
toClient = response.getWriter()
toClient.println("<h1>Example of connection to the DSpace DB via ZxJDBC</h1>")
rows = self.get_data_from_db()
toClient.println("<h2>Results</h2>")
toClient.println("<table>")
toClient.println("<tr>")
for column in rows[0]:
toClient.println("<th>%s</th>" % column)
toClient.println("</tr>")
for row in rows:
toClient.println("<tr>")
for column in row:
toClient.println("<td>%s</td>" % row[column])
toClient.println("</tr>")
toClient.println("</table>")
def read_dspace_config(self, filename):
"""read dspace.cfg"""
with open(filename, 'r') as f:
props = Properties()
props.load(f)
return props
def connect_db(self):
"""
get DB config from DSpace config, connect in autocommit mode (each
individual query is commited automatically)
"""
self.conn = zxJDBC.connect(
self.props.getProperty('db.url'),
self.props.getProperty('db.username'),
self.props.getProperty('db.password'),
self.props.getProperty('db.driver'),
)
self.conn.autocommit = True
def init(self, config):
"""servlet startup"""
try:
self.props = self.read_dspace_config(DSPACE_DIR + '/config/local.cfg')
except IOError:
self.props = self.read_dspace_config(DSPACE_DIR + '/config/dspace.cfg')
self.connect_db()
def destroy(self):
"""servlet shutdown: clean up DB connections"""
self.conn.close()
def get_data_from_db(self):
"""
Query the DB and return a list of rows
where each row is a dict of column names and values
"""
with self.conn.cursor() as c:
c.execute("SELECT version, description FROM schema_version ORDER BY version DESC")
columns = [col[0] for col in c.description]
rows = []
for row in c:
rowdata = dict(zip(columns, row))
rows.append(rowdata)
return rows |
...