Credit: Brian Zhou
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.
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( )
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.
The Jython site (http://www.jython.org); JDBC’s home page (http://java.sun.com/products/jdbc).