Now we are trying to connect our web application to a database. I use Northwind database here as an example database.
For you who don’t know what Northwind is, it is a sample database given if you install MS Access. I am sorry for non Windows user, but if you follow below tutorial you should also do it in another database like MySQL or PostgreSQL.
Open your previous SimpleJSP project, create a new file under simplejsp folder and named it employees.jsp.
What we are trying to do is to display all data from Employees table in Northwind database in our new employees.jsp page.
Copy and paste below code,
<%@ page import=”java.sql.*”%>
<%@ page import=”java.util.*”%>
<%
List empList = new ArrayList();
Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”);
Connection conn = DriverManager.getConnection(”jdbc:odbc:northwind”,”",”");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(”select EmployeeId,LastName,FirstName,Title from Employees”);
while(rs.next()){
Map emp = new HashMap();
emp.put(”id”,rs.getInt(1));
emp.put(”firstname”,rs.getString(2));
emp.put(”lastname”,rs.getString(3));
emp.put(”title”,rs.getString(4));
empList.add(emp);
}
rs.close();
stmt.close();
conn.close();
%>
<html>
<head>
<title>Employees</title>
</head>
<body>
<h1>Employees</h1>
<table border=”1″>
<%
for(int i=0;i<empList.size();i++){
Map emp = (Map)empList.get(i);
%>
<tr>
<td align=”right”><%=emp.get(”id”)%></td><td><%=emp.get(”lastname”)%></td><td><%=emp.get(”firstname”)%></td><td><%=emp.get(”title”)%></td>
</tr>
<%
}
%>
</table>
</body>
</html>
Before you try our new page, make sure to check your odbc whether it’s already had northwind DSN. Check it in ODBC Administrator.
If you doesn’t have it yet, create a new DSN, name it northwind and select Northwind.mdb file. If DSN already configured then start your jetty and go to http://localhost:8080/employees.jsp
You should get something like this,
Employees
| 1 | Nancy | Davolio | Sales Representative |
| 2 | Andrew | Fuller | Vice President, Sales |
| 3 | Janet | Leverling | Sales Representative |
| 4 | Margaret | Peacock | Sales Representative |
| 5 | Steven | Buchanan | Sales Manager |
| 6 | Michael | Suyama | Sales Representative |
| 7 | Robert | King | Sales Representative |
| 8 | Laura | Callahan | Inside Sales Coordinator |
| 9 | Anne | Dodsworth | Sales Representative |
Let’s break down our code to figure out what is all about..
1. Import package
<%@ page import=”java.sql.*”%>
<%@ page import=”java.util.*”%>
Above code means that we are importing all Classes that are under java.sql and java.util.
java.sql package contains classes that are used to connect to database while java.util package contains class that are usefull as data holder like List and Vector.
2. Create Connection
Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”);
Because we are using MS Access, so we are using sun.jdbc.odbc.JdbcOdbcDriver class as java driver for ODBC database.
If you are using other type of database, you must change it to appropriate driver class.
Connection conn = DriverManager.getConnection(”jdbc:odbc:northwind”,”",”");
First parameter in DriverManager.getConnection is our database URL, the second one is user that is allowed to access the database, and the third is the password.
3. Fetch Data
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(”select EmployeeId,LastName,FirstName,Title from Employees”);
while(rs.next()){
Map emp = new HashMap();
emp.put(”id”,rs.getInt(1));
emp.put(”firstname”,rs.getString(2));
emp.put(”lastname”,rs.getString(3));
emp.put(”title”,rs.getString(4));
empList.add(emp);
}
To execute the SQL query, we must create Statement object first. From Statement object we can create ResultSet object that will hold our query result. As you can see that ResultSet rs hold “select EmployeeId,LastName,FirstName,Title from Employees” SQL query result. The rest of the code is just to set result data in a map for s single row and List for the whole data.
4. Close connection
rs.close();
stmt.close();
conn.close();
Don’t forget to always close all of your connection object after you done with your database operation.
5. Display fetched Data
<html>
<head>
<title>Employees</title>
</head>
<body>
<h1>Employees</h1>
<table border=”1″>
<%
for(int i=0;i<empList.size();i++){
Map emp = (Map)empList.get(i);
%>
<tr>
<td align=”right”><%=emp.get(”id”)%></td><td><%=emp.get(”lastname”)%></td><td><%=emp.get(”firstname”)%></td><td><%=emp.get(”title”)%></td>
</tr>
<%
}
%>
</table>
</body>
</html>
Here we just use a simple table component from HTML to display our fetched data.
That’s all for now. Next tutorial will be JSP + Beans.
Thanks
