32549 - JDBC - Lab exercise - JDBC in a JavaBean 31242/ 32549Advanced Internet Programming Module: JDBC Lab exercise - JDBC in a JavaBean This exercise demonstrates one way of using a JavaBean for data access. In this example, we use a JavaBean to issue arbitrary SQL commands on a database. The JavaBean is quite generic - however it does have the database details hard-coded!!! (don't do this in real life :-). Level of Difficulty: 2 (moderately easy) Estimated time: 20 minutes Pre-requisites: Run 'setEnv.sh' to set your environment correctly Completed the Hello World JDBC lab exercise JDBC in a JavaBean This time we are creating a JavaBean. We will also create a small program to test the JavaBean before we try and use it in a JSP. This is an example of unit testing, which is currently seen as a good software engineering approach. Create the following Java class. This is the JavaBean. package myapp;
import java.sql.*;
import java.util.*;
// All JavaBeans must be Serializable
public class OracleBean implements java.io.Serializable {
private static final String dbDriver =
"oracle.jdbc.driver.OracleDriver";
private static final String dbURL =
"jdbc:oracle:thin:@smaug.it.uts.edu.au:1522:ell";
/* Defining the attributes.
Notice that they are all declared private.
They cannot (and must not) be accessed directly
from other objects, or it would violate the
JavaBean rules.
*/
private String dbUser;
private String dbPass;
private Connection conn;
public void connect()
throws ClassNotFoundException, SQLException {
if (dbUser != null) {
Properties props = new Properties();
props.setProperty("user", dbUser);
props.setProperty("password", dbPass);
Class.forName(dbDriver);
this.conn = DriverManager.getConnection(dbURL, props);
}
}
public void close() throws SQLException {
this.conn.close();
}
public ResultSet query(String sql) throws SQLException {
Statement s = conn.createStatement();
return (s.executeQuery(sql));
}
public int update(String sql) throws SQLException {
Statement s = conn.createStatement();
return (s.executeUpdate(sql));
}
/* get/set the dbUser property */
public String getDbUser() { return this.dbUser; }
public void setDbUser(String dbUser) {
this.dbUser = dbUser;
}
/* get/set the dbPass property */
public String getDbPass() { return this.dbPass; }
public void setDbPass(String dbPass) {
this.dbPass = dbPass;
}
}
Notice that this JavaBean has two properties: dbUser and dbPass. These must be set at runtime before calling the connect() method. A unit test for the JDBC JavaBean Now we need to unit test the JavaBean. Add the following main() method to your JavaBean. Vary the SQL query if you wish, to see different results. You can also test updating by using the o.Update() method. Now you can test the bean by using Right Mouse Button -> Run As -> Java Application public static void main (String [] args)
throws ClassNotFoundException, SQLException {
OracleBean o = new OracleBean();
o.setDbUser("oracle_userid");
o.setDbPass("oracle_password");
o.connect();
ResultSet r = o.query("SELECT * FROM addressbook");
while (r.next()) {
System.out.print(r.getString(1) + ",");
System.out.print(r.getString(2) + ",");
System.out.print(r.getString(3) + ",");
System.out.print(r.getLong(4) + ",");
System.out.println(r.getString(5));
}
}
© 2008 University of Technology, Sydney. All Rights Reserved. Redistribution without permission prohibited.
