Python Cookbook 2Nd Edition Jun 1002005 [Electronic resources] نسخه متنی

Recipe 7.18. Accessing a JDBC Database from a Jython Servlet

Credit: Brian Zhou

Problem

You're writing a
servlet in Jython, and you need to connect to a database server (such
as Oracle, Sybase, Microsoft SQL Server, or MySQL) via JDBC.

Solution

The technique is basically the same for any kind of database, give or
take a couple of statements. Here's the code for
when your database is Oracle:

import java, javax
class emp(javax.servlet.http.HttpServlet):
def doGet(self, request, response):
''' a Servlet answers a Get query by writing to the response's
output stream.  In this case we ignore the request, though
in normal, non-toy cases that's where we get form input from.
'''
# we answer in plain text, so set the content type accordingly
response.setContentType("text/plain")
# get the output stream, use it for the query, then close it
out = response.getOutputStream( )
self.dbQuery(out)
out.close( )
def dbQuery(self, out):
# connect to the Oracle driver, building an instance of it
driver = "oracle.jdbc.driver.OracleDriver"
java.lang.Class.forName(driver).newInstance( )
# get a connection to the Oracle driver w/given user and password
server, db = "server", "ORCL"
url = "jdbc:oracle:thin:@" + server + ":" + db
usr, passwd = "scott", "tiger"
conn = java.sql.DriverManager.getConnection(url, usr, passwd)
# send an SQL query to the connection
query = "SELECT EMPNO, ENAME, JOB FROM EMP"
stmt = conn.createStatement( )
if stmt.execute(query):
# get query results and print the out to the out stream
rs = stmt.getResultSet( )
while rs and rs.next( ):
out.println(rs.getString("EMPNO"))
out.println(rs.getString("ENAME"))
out.println(rs.getString("JOB"))
out.println( )
stmt.close( )
conn.close( )

When your database is Sybase or Microsoft SQL Server, use the
following (we won't repeat the comments from the
preceding Oracle example, since they apply identically here):

import java, javax
class titles(javax.servlet.http.HttpServlet):
def doGet(self, request, response):
response.setContentType("text/plain")
out = response.getOutputStream( )
self.dbQuery(out)
out.close( )
def dbQuery(self, out):
driver = "sun.jdbc.odbc.JdbcOdbcDriver"
java.lang.Class.forName(driver).newInstance( )
# Use "pubs" DB for mssql and "pubs2" for Sybase
url = "jdbc:odbc:myDataSource"
usr, passwd = "sa", "password"
conn = java.sql.DriverManager.getConnection(url, usr, passwd)
query = "select title, price, ytd_sales, pubdate from titles"
stmt = conn.createStatement( )
if stmt.execute(query):
rs = stmt.getResultSet( )
while rs and rs.next( ):
out.println(rs.getString("title"))
if rs.getObject("price"):
out.println("%2.2f" % rs.getFloat("price"))
else:
out.println("null")
if rs.getObject("ytd_sales"):
out.println(rs.getInt("ytd_sales"))
else:
out.println("null")
out.println(rs.getTimestamp("pubdate").toString( ))
out.println( )
stmt.close( )
conn.close( )

And here's the code for when your database is
MySQL:

import java, javax
class goosebumps(javax.servlet.http.HttpServlet):
def doGet(self, request, response):
response.setContentType("text/plain")
out = response.getOutputStream( )
self.dbQuery(out)
out.close( )
def dbQuery(self, out):
driver = "org.gjt.mm.mysql.Driver"
java.lang.Class.forName(driver).newInstance( )
server, db = "server", "test"
usr, passwd = "root", "password"
url = "jdbc:mysql://%s/%s?user=%s&password=%s" % (
server, db, usr, passwd)
conn = java.sql.DriverManager.getConnection(url)
query = "select country, monster from goosebumps"
stmt = conn.createStatement( )
if stmt.execute(query):
rs = stmt.getResultSet( )
while rs and rs.next( ):
out.println(rs.getString("country"))
out.println(rs.getString("monster"))
out.println( )
stmt.close( )

Discussion

You might want to use different JDBC drivers and URLs, but you can
see that the basic technique is quite simple and straightforward.
This recipe's code uses a content type of
text/plain because the recipe is about accessing
the database, not about formatting the data you get from it.
Obviously, you can change this content type to whichever is
appropriate for your application.

In each case, the basic technique is first to instantiate the needed
driver (whose package name, as a string, we place in variable
driver) via the Java dynamic loading facility. The
forName method of the
java.lang.Class class loads and provides the
relevant Java class, and that class'
newInstance method ensures that the driver we need
is instantiated. Then, we can call the
getConnection method of
java.sql.DriverManager with the appropriate URL
(or username and password, where needed) and thus obtain a connection
object to place in the conn variable. From the
connection object, we can create a statement object with the
createStatement method and use it to execute a
query that we have in the query string variable with
the execute method. If the query succeeds, we can
obtain the results with the geTResultSet method.
Finally, Oracle and MySQL allow easy sequential navigation of the
result set to present all results, while Sybase and Microsoft SQL
Server need a bit more care. Overall, the procedure is similar in all
cases.

See Also

The Jython site (http://www.jython.org);
JDBC's home page (http://java.sun.com/products/jdbc).