IT403 Rapid Application Development Lab 4

Java Database Connectivity (JDBC) Requirements: MySQL Server 5.* mysql-connector-java

MySQL is already installed in each of the systems in Lab 2. You will first need to start the mysql server manually using your command shell [if it's not already running]. I am actually creating a script to start it automatically during system start up so that you won't face admin privilege problems for starting it. But as we don't have centralized server to manage all the clients, I have to manually add that script to each of the machines in the lab and need sometime to do it. So, for this lab session you will start it manually as follows: 1. Start MySQL server using the command [U need an Admin password to do this] $sudo /etc/init.d/mysql start 2. Once MySQL is successfully started and running, login using the following syntax: $mysql -uroot -p Then you will be asked to provide password for the root user, which is the default MySQL user. Provide rad123 as the password. 3. You can now use any of the SQL commands to create databases or tables and query results from stored databases. Eg commands: create database; use database; create table ....; etc. 4. For the sake of this lab session, create a database called rad. Then create a table called tbl_user with fields userId(integer), userName(varchar), userPassword(varchar) under rad. You can then insert some values to this new table.

Introduction JDBC provides a standard library for accessing relational databases. Using the JDBC API, you can access a wide variety of different SQL databases with exactly the same Java syntax. You can connect to Oracle, MS SQL, or MySQL using the same Java code. There are different standard steps in querying databases using JDBC as defined below. The first three steps are actually enough to create connection to your database. The remaining steps are for executing queries or updates to the database. Load the driver Define the connection URL Establish the connection Create a statement object Execute a query or update Process the results Close the connection

Loading the Driver The driver is a piece of software that knows how to talk to the actual database server. To load the driver, all you need to do is load the appropriate class; a static block in the class itself automatically makes a driver instance and registers it with the JDBC driver manager. You will use Class.forName method to load appropriate driver. This method takes a string representing a fully qualified class name. Some of the most known drivers include: connect.microsoft.MicrosoftDriver To connect with MS SQL Server oracle.jdbc.driver.OracleDriver To connect with Oracle server com.mysql.jdbc.Driver To connect with MySQL server For instance, if you want to connect to MySQL database server then you can load the driver as follows: Class.forName(com.mysql.jdbc.Driver) Define Connection URL Once you have loaded the JDBC driver, you need to specify the location of the database server. URLs referring to databases use the jdbc: protocol. The connection URL must embed three important parameters within it: Server name (host), port, and database name. The following code snippet defines a connection URL in order to connect to MySQL server. String host = "localhost"; // Can be IP address or full name of the database server String dbName = "databaseName"; // Existing database name int port = 3306; // Default MySQL port number. String mySqlUrl = "jdbc:mysql://" + host + ":" + port + "/" + dbName; Establish the Connection To make the actual network connection, pass the URL defined above, and embed the database username, and the password to the getConnection method of the DriverManager class, as illustrated in the following example. Here you need to provide the MySQL database username and password in order to successfully create the connection. String DbUsername = "root"; // MySQL username String password = "rad123"; // password for the MySQL username Connection con = DriverManager.getConnection(mySqlUrl, DbUsername, password); You will now be able to get connected to the MySQL server using the above three steps. Check if you can successfully connect the database by providing appropriate values to each of the parameters. Note It's always a very good programming practice to include error handling block in your programs. Like other programming languages, Java has its own mechanism for handling errors. It uses a trycatch block to handle errors within the program. So, its mandatory that you included a try-catch block in your program for this and the remaining lab sessions. NetBeans can generate a corresponding try-catch block for you if you don't wish to write it by yourselves.

Now let's summarize the above procedures. Create a new Java class and name it something like DbConnection. Then, the driver loading, URL connection definition, and connection establishment steps can be summarized including a try-catch block with in the class as follows:

Create a simple main function, call the DbConnection function from your main and check if you can connect to MySQL database. public static void main(String args[]) { DbConnection jdbc = new DbConnection(); jdbc.DbConnection(); } Note You probably had encountered an error message after running the above JDBC program. This is due to the fact that you need an additional jdbc-connector class file to be included into your package. The particular JAR file that must be added into the Java package to help you connect to a MySQL database is mysql-connector-java-5.0.8-bin.jar. Here the version may be different but this is the latest mysql-connector I have and you need to include it into your project. You can get this file from Moodle, download and extract it to your computer. Its easy to add libraries and JAR files in NetBeans. Expand your project, right click the Library tree and select Add JAR/Folder... Then navigate to the mysql-connector that you have downloaded. Add the one with the .jar file extension.

If you can successfully connect to the database, then we will proceed to create statements for any kind of database query, insertion or updation. If you are experiencing an error in the above programs, please check it carefully. Creating a Statement A Statement object is used to send queries and commands to the database and is created from the Connection object as follows: Statement statement = con.createStatement(); // Con is the Connection object created previously Executing a Query Once you have a Statement object, you can use it to send SQL queries by using the executeQuery method, which returns an object of type ResultSet. For instance, if you have a tbl_user table in your rad database and contains userId, userName, userPassword as its fields, then you can have following query statements: String query = "SELECT userId, userName, userPassword FROM tbl_user"; ResultSet rs = statement.executeQuery(query); We use executeQuery when we need to query data from the database. But we use executeUpdate if we want to modify(INSERT, DELETE or UPDATE) our database. Processing the Results The simplest way to handle the results is to process them one row at a time, using the ResultSet's next method to move through the table a row at a time. Within a row, ResultSet provides various getXxx methods that take a column index or column name as an argument and return the result as a variety of different data types. For instance, use getInt if the value should be an integer, getString for a String, and so on for most other data types. If you just want to display the results, you can use getString regardless of the actual column type. However, if you use the version that takes a column index, note that columns are indexed starting at 1 (following the SQL convention), not at 0 as with arrays, vectors, and most other data structures in the Java programming language. Note that the first column in a ResultSet row has index 1, not 0. Let us display the result of the above query as follows: while(resultSet.next()) { System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3)); } Closing the Connection Finally, you need to close the Connection, ResultSet and other objects as follows. Closing of such objects must be at the end any database operation.

con.close(); rs.close(); Now embed the above code, starting from Statement creation, with your previous one and display the output from your database. Exercise 1. Create another table called tbl_Employees in your database with fields such as empID, empFirstName, empLastName, empAge, empEmail, etc. Then Add a Java code to INSERT values to the tbl_Employees table in the database. 2. Time to move to JSP now. Create a new JSP page in your project and call it userRegistration. Provide user registration fields such as username, password, first name, last name, age, email, address, etc. Then instead of displaying user data into the browser in your request handling code, insert it into the database that was created previously. That is, enter appropriate values to either of tbl_user or tbl_employees tables in your database. You can do this by creating a request handling JSP page and then calling your Java database connection code from this page. 3. [optional] Create another JSP page called userLogin. Provide entry fields for username and password. Then write corresponding Java programs and JSP request handling pages to authenticate the user. That is, check whether that particular user can login to the system by checking the values he/she provides with the already existing values in the database.