- Jython Servlet Containers
- Defining Simple Servlet Classes
- More About GenericServlet
- HttpServlet
- PyServlet
- Cookies
- Sessions
- Databases and Servlets
- JSP
This chapter is from the book
This chapter is from the book
This chapter is from the book
Databases and Servlets
Connecting to a database, executing statements and traversing result sets is no different in a jylet than it is in other Jython database applications. Managing connections and other database resources, however, is a primary concern because many web applications contain numerous jylets that use database content. Creating a database connection in each jylet quickly consumes the database resources, which has bad consequences. Alternatively, creating and closing a database connection for each request is unacceptable overhead.
There are many options for managing database resources, but two primary approaches are a connection per jylet, or connection pooling. Using a connection for each jylet is a popular but resource intensive approach. This approach involves establishing a database connection in the jytlet's init method, and closes that connection only when they jylet is unloaded. This eliminates any connection overhead while responding to client requests. However, this is only reasonable if the number of connections you require is well within resource limits. Most situations call for more prudent resource management.
Listing 12.9 implements a jylet that obtains a database connection and cursor object in its init method, which are closed in its destroy method. Therefore, the jylet incurs the connection overhead only at initialization, not during each client request. The database implementation in Listing 12.9 uses the zxJDBC package and MySQL database described in Chapter 11, "Database Programming." You must therefore include the classes required for MySQL and zxJDBC in the context's lib directory. For Listing 12.9, the mm_mysql-2_0_4-bin.jar file and the zxJDBC.jar file should be placed in the directory %TOMCAT_HOME%\webapps\jython\WEB-INF\lib. You should restart Tomcat after adding jar files to the lib directory to ensure it detects the new jar files.
Listing 12.9 Jython Servlet with Database Connection
# file: DBDisplay.py
from javax.servlet import http
from com.ziclix.python.sql import zxJDBC
class DBDisplay(http.HttpServlet):
def init(self, cnfg):
#define the JDBC url
url = "jdbc:mysql://192.168.1.77/products"
usr = "productsUser" # replace with real user name
passwd = "secret" # replace with real password
driver = "org.gjt.mm.mysql.Driver"
#connect to the database and get cursor object
self.db = zxJDBC.connect(url, usr, passwd, driver)
self.c = self.db.cursor()
def doGet(self, req, res):
res.setContentType("text/html")
out = res.getWriter()
print >>out, """
<html>
<head>
<title>Jylet Database Connection</title>
</head>
<body>
<table align="center">
<tr>
<td><b>ID</b></td>
<td><b>Title</b></td>
<td><b>Description</b></td>
<td><b>Price</b></td>
</tr>"""
self.c.execute("select code, name, description, price from products")
for row in self.c.fetchall():
print >>out, """
<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>""" % row
print >>out, """
</table>
</body>
</html>"""
def destroy(self):
self.c.close()
self.db.close()
Listing 12.9 assumes there is a database named products that includes the products table, and that the products table has at least the fields code, name, description, and price. To create such a database, use the following SQL statement:
create database products
To create the products table, use the following SQL statements:
CREATE TABLE products ( primarykey int(11) NOT NULL auto_increment, code varchar(55) default NULL, name varchar(255) default NULL, description text, price float(5,2) default NULL, PRIMARY KEY (primarykey) ) TYPE=MyISAM;
After creating the database and table, creating some arbitrary values for each of the fields and placing the DBDisplay.py file in the context's root directory, you should be able to point your browser to http://localhost:8080/jython/DBDisplay.py to see the database data.
If your web application begins using excessive connections, consider instead using connection pooling. Connection pooling allows for both prudent resource management and elimination of connection overhead. A connection pool maintains a certain number of active database connections that jylets borrow when replying to client requests and return to the pool when done. This creates a predictable, static number of connections. It is also possible to use a statement pool, giving statements the same advantage. A popular, free, tested, and well-documented connection-pooling tool is PoolMan from http://www.codestudio.com/. Other Java connection-pooling packages exist as well, and all should work seamlessly with Jython.