Recipe7.18.Accessing a JDBC Database from a Jython Servlet

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.

Recipe7.18.Accessing a JDBC Database from a Jython Servlet