32549 - JDBC - Lab exercise - Hello World JDBC 31242/ 32549Advanced Internet Programming Module: JDBC Lab exercise - Hello World JDBC JDBC is the Java API for accessing databases. Eventually we will use JDBC for connecting the business logic parts of our application to the necessary databases. But first we begin at the beginning - with a Hello World example. This exercise is to create a simple command-line application that connects to a database. Level of Difficulty: 1 (easy) Estimated time: 20 minutes Pre-requisites: Run 'setEnv.sh' to set your environment correctly Completed the Oracle familiarisation lab exercise A HelloWorld JDBC application Because we are creating a standalone application, you do not need a WAR file, and we will not be installing it into the application server. It also does not really matter which directory you create this program in. Create the following Java class & change the oracle_userid oracle_password to your Oracle userid and password!! import java.sql.*;
public class HelloWorldJDBC {
private static final String dbdriver =
"oracle.jdbc.driver.OracleDriver";
private static final String dburl =
"jdbc:oracle:thin:@smaug.it.uts.edu.au:1522:ell";
private static final String dbuser = "oracle_userid"; // change to your id
private static final String dbpass = "oracle_password";
public static void main (String [] args) {
try {
// Load the driver and create connection
Class.forName(dbdriver);
Connection conn =
DriverManager.getConnection(dburl, dbuser, dbpass);
// Create a statement and execute the query
Statement stmt = conn.createStatement();
stmt.executeQuery("select * from addressbook");
// Get the result set
ResultSet rs = stmt.getResultSet();
// Loop through and print out the values
while (rs.next()) {
System.out.print(rs.getString(1) + ",");
System.out.print(rs.getString(2) + ",");
System.out.print(rs.getString(3) + ",");
System.out.print(rs.getLong(4) + ",");
System.out.println(rs.getString(5));
}
// now release the connection.
conn.close();
} catch (Exception e) {
// If anything goes wrong, print the Exception message
e.printStackTrace();
}
}
}
To connect to the database, two essential pieces of information are required (other than the username and password): JDBC driver class - the name of a Java class that knows how to communicate with the particular kind of database you are connecting to. You will need to specify a different driver class for each kind of database you wish to connect to. JDBC URL - a URL that specifies which database to connect to. JDBC URLs always start with the protocol "jdbc:", but the format of the remainder of the URL differs depending on the kind of database you are connecting to (i.e. each driver class has its own URL format). In the case of Oracle, the URL includes the driver type ("thin"), the hostname ("smaug.it.uts.edu.au"), the port number ("1522") and the database name ("ell"). Running in Workshop First you have to load the Oracle JDBC library. To do this, select the project, Right Mouse Button -> Build Path -> Add External Archives then navigate to /opt/bea10/wlserver_10.0/server/lib and select ojdbc14.jar (in Windows: c:\bea\wlserver_10.0\server\lib) Note: You should pick the appropriate database driver jar file if you are using another database eg: mysql-comnector-java-commercial-5.0.3.jar for mysql To test your application, click Right Mouse Button -> Run As -> Java Application The output should appear in the Console. Running from the Command Prompt To test your application, compile it using javac (hint: did you remember to run wlenv to set your classpath?) and then run it using the command-line Java interpreter: java HelloWorldJDBC
Notes Note that hardcoding the JDBC URL, userid and password is not good practice to follow. You would be better off storing these as seperately as parameter files. © 2008 University of Technology, Sydney. All Rights Reserved. Redistribution without permission prohibited.
