Now that we know how to create a new web application. Lets go a step further, see if we can interact with the database. In this exercise, we'll build a simple app where we enter details of a student and try to retrieve the data that we just entered.
Before we get started with the actual app development, lets first install IBM DB2 (alternatively if you just want to learn how to build an app you can try installing MySQL).
Note: IBM DB2 Express C 9.5 has compatibility issues with Windows Vista and Windows 7. So I suggest you download the latest version of DB2 Express C 9.7 here, which is working fine on my windows 7 machine.
Step 1:
Now, we need create a sample database, with a database name say "Exercise" and then create a table, say "Student". Here I am assuming you have basic knowledge of DBMS. If not, you can go through a couple of basic tutorials on
- DB Design:
| Field | Datatype |
|---|---|
| USN | VARCHAR(10) |
| Name | VARCHAR(25) |
| Semester | INTEGER |
| Branch | VARCHAR(10) |
| DOB | DATE |
Now, once DB2 is installed on your system, go to "Control Center" from the Start Menu, and you will get to see the following program.
Now, click on "All Databases", and create a new "Standard Database" and mention the db name. After that you can see that a new Database that you created can be seen on the left, under "All Database".
Now, choose the create a new table option and create the table named " Student" with the fields as mentioned above. You can either use the GUI to create the table, or use the command editor, where you can type the SQL commands to create the table as follows:
CONNECT TO EXERCISE; CREATE TABLE STUDENT( USN VARCHAR(10) NOT NULL, NAME VARCHAR(20) NOT NULL, SEM INTEGER NOT NULL, BRANCH VARCHAR(10), DOB DATE, PRIMARY KEY(USN));
Now, we are done with the creating the database and a table in the db which we shall be using to insert data and retrieve from.
Step 2:
Once we are done creating a new database, now like we did in the first exercise, create a new Dynamic Web Application and call it, "Exercise2". Once you are done with creating a new web application, and setting the runtime server as WASCE (refer to the earlier post to know how to do it).
Now lets create the JSP with a name "AddStudentDetails.jsp", which has a form to enter the details of a student which will be entered into the database. Again, for which right click on your project in the Project Explorer and choose New->Web->Jsp, and enter the name and click ok. A new jsp with the mentioned name would be placed in the "Web Content" folder.
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>ExerciseTwo: Add Student Details</title> </head> <body> <h2 align="center">02 TGMC: Talking to the Database</h2> <fieldset><legend style="size: 10px;">Add Student Details</legend> <form action="SubmitStudentDetails.jsp"> <table align="center" width="50%" border="1" bordercolor="GRAY">
<tr> <td width="15%" style="padding: 3px">USN :</td> <td width="35%" style="padding: 3px"><input type="text" name="usn"/></td> </tr> <tr> <td style="padding: 3px">Name :</td> <td style="padding: 3px"><input type="text" name="name" /></td> </tr> <tr> <td style="padding: 3px">Semester :</td> <td style="padding: 3px"><input type="text" name="sem" /></td> </tr> <tr> <td style="padding: 3px">Branch :</td> <td style="padding: 3px"><select name="branch"> <option value="CSE">CSE</option> <option value="ISE">ISE</option> <option value="ECE">ECE</option> </select></td> </tr> <tr> <td style="padding: 3px"> Date of Birth :</td> <td style="padding: 3px"> <input type="text" name="dob"/>
MM-DD-YYYY </td>
</tr>
</table>
<br/>
<div align="center"> <input type="submit" value="Add Student Details"/>
<input type="reset" value="Clear"/>
</div>
</form>
</fieldset>
</body>
</html>With the above code in the "AddStudentDetails.jsp", when the server is run, we get the following page on the browser.
After the form is done, now we need to create another jsp, which gets the called on clicking the "Add Student Details Button". Lets call the second jsp as "SubmitStudentDetails.jsp", where we make the database connection and add the form values.
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1" import="java.sql.*;"%>
<%
Connection con = null; // Create an object of Connection class
String url = "jdbc:db2://localhost:50000/"; // The Database url
String db = "Exercise"; //Database Name
String driver = "com.ibm.db2.jcc.DB2Driver"; // Database driver for DB2
String user = "db2admin"; //DB2 Username
String pass = "admin"; //DB2 Password
try
{
//To Establish Database Connection
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url+db, user, pass);
// Obtain the student details from the form
String strUsn = request.getParameter("usn");
String strName = request.getParameter("name");
// Converting the String to Integer Type
int strSem = Integer.parseInt(request.getParameter("sem"));
String strBranch = request.getParameter("branch");
String strDob = request.getParameter("dob");
//Insert query to enter the details to database
String query="INSERT INTO DB2ADMIN.STUDENT (USN, NAME, SEM, BRANCH, DOB) VALUES('"+strUsn+"','"+strName+"','"+strSem+"','"+strBranch+"','"+strDob+"')";
con.setAutoCommit(false);
PreparedStatement ps = con.prepareStatement(query); int rowsUpdated = ps.executeUpdate(); // Condition to check if there was any data inserted to the db
if(rowsUpdated>0){%>
<h2>Student Details Inserted successfully! :)</h2>
<% }
else{%>
<h2>Operation Failed!! :( :( <br />
Check the Stack trace to identify the mistake!! :-)</h2>
<%}
// Commit the changes
con.commit();
// Close the connection
con.close();
}
// Handle the Exception
catch (Exception e)
{
e.printStackTrace();
System.out.print(e.getMessage());
}
%>Now, we are ready to test out our application. Just right click on the project and Run on Server.
.
.
.
.
.
I'm sure if you had run the project, you mostly encountered an error! Which is a good sign, coz it helps to learn things better! :)
And the error would be certainly, "Class Not Found Exception" pointing to the Database driver that you have mentioned in the SubmitStudentDetails.jsp.. Thats because we haven't added the database driver Jar file.
To add the driver jar files to the project all you need to do is, right click on the project ->Build Path -> Configure Build Path -> Add External Jars. Here browse to the location where you have installed IBM DB2 and find the following 2 jars and add them.
1. db2jcc.jar
2. db2jcc_ license_cu.jar
Once you've done this, I guess there shouldn't be any more problems. Once u enter the details in the form and hit "Submit" if everything goes right, you must be seeing the following screen..
Hope you could understand the various concepts involved, we have completed only a part of the exercise that we set out to do. We have entered the details into the database, the next thing we need to do, is retrieve the data from the data base and display it on a page. I'll be teaching you how we can do that in the next blog post. By then you can try it out yourself. Just in case you get lost, download the project from one of the below locations.
For any assistance you can leave a comment or send out a mail to [ voidpointers@gmail.com ].
Try out some more samples which you can find at http://roseindia.net. Happy coding.. We'll be back with the next exercise very soon.!
Cheers! :)
