Servlet NewJavaServlet Test

CHAPTER 12 
■ 
DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
247 
 
    FROM python_implementations 
    WHERE id = 1; 
 
  RETURN; 
END; 
$$ LANGUAGE plpgsql; 
Listing 12-21. Jython Calling Code 
>>> result = cursor.callproc('proc_test') 
>>> cursor.fetchall() 
[(u'Jython',)] 
Although this example was relatively trivial, it is easily to see how the use of database procedures 
from zxJDBC could easily become important. Combining database procedures and functions with 
application code is a powerful technique, but it does tie an application to a specific database so it should 
be used wisely. 
Customizing zxJDBC Calls 
At times, it is convenient to have the ability to alter or manipulate a SQL statement automatically. This 
can be done before the statement is sent to the database, after it is sent to the database, or even just to 
obtain information about the statement that has been sent. To manipulate or customize data calls, it is 
possible to make use of the DataHandler interface that is available via zxJDBC. There are basically three 
different methods for handling type mappings when using DataHandler. They are called at different 
times in the process, one when fetching and the other when binding objects for use in a prepared 
statement. These datatype mapping callbacks are categorized into four different groups: life cycle, 
developer support, binding prepared statements, and building results. 
At first mention, customizing and manipulating statements can seem overwhelming and perhaps 
even a bit daunting. However, the zxJDBC DataHandler makes this task fairly trivial. Simply create a 
handler class and implement the functionality that is required by overriding a given handler method. 
What follows is a listing of the various methods that can be overridden, and we’ll look at a simple 
example afterward. 
Life Cycle 
public void preExecute(Statement stmt) throws SQLException; 
A callback prior to each execution of the statement. If the statement is a PreparedStatement (created 
when parameters are sent to the execute method), all the parameters will have been set. 
public void postExecute(Statement stmt) throws SQLException; 
A callback after successfully executing the statement. This is particularly useful for cases such as 
auto-incrementing columns where the statement knows the inserted value. 
Developer Support 
public String getMetaDataName(String name); 
A callback for determining the proper case of a name used in a DatabaseMetaData method, such as 
getTables(). This is particularly useful for Oracle which expects all names to be upper case. 
public PyObject getRowId(Statement stmt) throws SQLException; 
A callback for returning the row id of the last insert statement. 
www.it-ebooks.info

---

CHAPTER 12 
■
 DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
248 
 
Binding Prepared Statements 
public Object getJDBCObject(PyObject object, int type); 
This method is called when a PreparedStatement is created through use of the execute method. 
When the parameters are being bound to the statement, the DataHandler gets a callback to map the 
type. This is only called if type bindings are present. 
public Object getJDBCObject(PyObject object); 
This method is called when no type bindings are present during the execution of a 
PreparedStatement. 
Building Results 
public PyObject getPyObject(ResultSet set, int col, int type); 
This method is called upon fetching data from the database. Given the JDBC type, return the 
appropriate PyObject subclass from the Java object at column col in the ResultSet set. 
Now we’ll examine a simple example of utilizing this technique. The recipe basically
 
follows these steps: 
1.  Create a handler class to implement a particular functionality (must 
implement the DataHandler interface). 
2.  Assign the created handler class to a given cursor object. 
3.  Use the cursor object to make database calls. 
In Listing 12-22, we override the preExecute method to print a message stating that the functionality 
has been altered. As you can see, it is quite easy to do and opens up numerous possibilities. 
Listing 12-22. PyHandler.py 
from com.ziclix.python.sql import DataHandler 
 
class PyHandler(DataHandler): 
    def __init__(self, handler): 
        self.handler = handler 
        print 'Inside DataHandler' 
    def getPyObject(self, set, col, datatype): 
        return self.handler.getPyObject(set, col, datatype) 
    def getJDBCObject(self, object, datatype): 
        print "handling prepared statement" 
        return self.handler.getJDBCObject(object, datatype) 
    def preExecute(self, stmt): 
        print "calling pre-execute to alter behavior" 
        return self.handler.preExecute(stmt) 
Jython Interpreter Code 
>>> cursor.datahandler = PyHandler(cursor.datahandler) 
Inside DataHandler 
>>> cursor.execute("insert into test values (?,?)", [1,2]) 
calling pre-execute 
www.it-ebooks.info

---

CHAPTER 12 
■ 
DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
249 
 
History 
zxJDBC was contributed by Brian Zimmer, one-time lead committer for Jython. This API was written to 
enable Jython developers to have the capability of working with databases using techniques that more 
closely resembled the Python DB API. The package eventually became part of the Jython distribution 
and today it is one of the most important underlying APIs for working with higher level frameworks such 
as Django. The zxJDBC API is evolving at the time of this publication, and it is likely to become more 
useful in future releases. 
Object Relational Mapping 
Although zxJDBC certainly offers a viable option for database access via Jython, there are many other 
solutions available. Many developers today are choosing to use ORM (Object Relational Mapping) 
solutions to work with the database. This section is not an introduction to ORM, we assume that you are 
at least a bit familiar with the topic. Furthermore, the ORM solutions that are about to be discussed have 
an enormous amount of very good documentation already available either on the web or in book format. 
Therefore, this section will give insight on how to use these technologies with Jython, but it will not go 
into great detail on how each ORM solution works. With that said, there is no doubt in stating that these 
solutions are all very powerful and capable for standalone and enterprise applications alike. 
In the next couple of sections, we’ll cover how to use some of the most popular ORM solutions 
available today with Jython. You’ll learn how to set up your environment and how to code Jython to work 
with each ORM. By the end of this chapter, you should have enough knowledge to begin working with 
these ORMs using Jython, and even start building Jython ORM applications. 
SqlAlchemy 
No doubt about it, SqlAlchemy is one of the most widely known and used ORM solutions for the Python 
programming language. It has been around long enough that its maturity and stability make it a great 
contender for use in your applications. It is simple to setup, and easy-to-use for both new databases and 
legacy databases alike. You can download and install SqlAlchemy and begin using it in a very short 
amount of time. The syntax for using this solution is very straight forward, and as with other ORM 
technologies, working with database entities occurs via the use of a mapper that links a special Jython 
class to a particular table in the database. The overall result is that the application  persists through the 
use of entity classes as opposed to database SQL transactions. 
In this section we will cover the installation and configuration of SqlAlchemy with Jython. The 
section will then show you how to get started using it through a few short examples; we will not get into 
great detail as there are plenty of excellent references on SqlAlchemy already. However, this section 
should fill in the gaps for making use of this great solution on Jython. 
Installation 
We’ll begin by downloading SqlAlchemy from the web site (www.sqlalchemy.org), at the time of this 
writing the version that should be used is 0.6. This version has been installed and tested with the Jython 
2.5.0 release. Once you’ve downloaded the package, unzip it to a directory on your workstation and then 
traverse to that directory in your terminal or command prompt. Once you are inside of your SqlAlchemy 
directory, issue the following command to install: 
jython setup.py install 
Once you’ve completed this process, SqlAlchemy should be successfully installed into your jython 
Libsite-packages directory. You can now access the SqlAlchemy modules from Jython, and you can open 
up your terminal and check to ensure that the install was a success by importing sqlalchemy and 
checking the version. See Listing 12-23. 
www.it-ebooks.info

---