8.13 Accessing a JDBC Database from a Jython Servlet

Credit: Brian Zhou

8.13.1 Problem

You're writing a servlet in Jython, and you need to connect to a database server (e.g., Oracle, Sybase, MS SQL Server, MySQL) via JDBC.

8.13.2 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):
    response.setContentType("text/plain")
    out = response.getOutputStream(  )
    self.dbQuery(out)
    out.close(  )
  def dbQuery(self, out):
    driver = "oracle.jdbc.driver.OracleDriver"
    java.lang.Class.forName(driver).newInstance(  )
    server, db = "server", "ORCL"
    url = "jdbc:oracle:thin:@" + server + ":" + db
    usr, passwd = "scott", "tiger"
    conn = java.sql.DriverManager.getConnection(url, usr, passwd)
    query = "SELECT EMPNO, ENAME, JOB FROM EMP"
    stmt = conn.createStatement(  )
    if stmt.execute(query):
        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:

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(  )

8.13.3 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 to whatever content type 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 gives us the relevant Java class, and that class's 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, but overall, the procedure is similar in all cases.

8.13.4 See Also

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