...
- 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 create 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.
- DB connection here is open in init() and closed in destroy() only to demonstrate the servlet's constructor and destructor. You should not keep a DB connection open for the whole time the servlet is loaded.
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 |
...