Friday, August 27, 2010

JSP JDBC tutorial (Hello World)

I assume you already had knowledge of JSP. If you don’t know you can go to http://chackboom.blogspot.com/ to learn it.

JDBC is Java Database Connectivity. We can use it to connect database. For this tutorial, we are going to use MySQL database. The database is installed already (at EATJ hosting http://www.eatj.com/. Thank EATJ for providing database) . To run this JDBC JSP page, you don’t need install MySQL database.

Pre-installed MySQL database has one table named employees. There is one record:

FIRST

LAST

ADDRESS

POSITION

John

Herb

123 ABC DR

Engineer

This tutorial will show you how to retrieve first name from MySQL database and display in web page.

Please follow step by step:

1. Download Eclipse IDE for Java EE Developers from http://www.eclipse.org/downloads/

2. Unzip it and Install Eclipse IDE.

3. Launch Eclipse by clicking eclipse.exe

4. Click on File=>New=>Project=>Web=>Dynamic Web Project

5. Click on Next button

6. Put db as Project name and select 2.3 in Dynamic web module version

7. Click on Finish button

8. Expend WebContent and create context.xml inside META-INF

9. Put following code into your context.xml

<?xml version="1.0" encoding="UTF-8"?>

<Context>

<!-- Specify a JDBC datasource -->

<Resource name="jdbc/mydatabase"

auth="Container"

type="javax.sql.DataSource"

username="demo"

password="demo"

driverClassName="com.mysql.jdbc.Driver"

url="jdbc:mysql://www.coincan.net:3306/demo?autoReconnect=true"

validationQuery="select 1"

maxActive="2"

maxIdle="1"/>

</Context>

10. Click on File=>Save

11. In Java Resources: src, create com.atj.db package and DBConnector.java and put following code into DBConnectior.java

//Code:

package com.atj.db;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.sql.DataSource;

public class DBConnector {

private static DBConnector connector_ = null;

public static DBConnector getInstance() throws Exception {

if(connector_ == null) {

connector_ = new DBConnector();

}

return connector_;

}

public Connection getConnection() throws Exception {

// Get DataSource

Context ctx = new InitialContext();

DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mydatabase");

Connection c = ds.getConnection();

return c;

}

public String getFirstName() throws Exception {

String first = "";

try{

Connection con = getConnection();

Statement st = con.createStatement();

ResultSet res = st.executeQuery("SELECT * FROM employees");

while (res.next()) {

first = res.getString("first");

System.out.println(first);

}

con.close();

}

catch (SQLException s){

System.out.println("SQL code does not execute.");

}

return first;

}

}

12. In WebContent, create db.jsp and put following code into db.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

pageEncoding="ISO-8859-1"%>

<%@ page import="com.atj.db.*" %>

<%

DBConnector dbConn = DBConnector.getInstance();

String firstName = dbConn.getFirstName();

%>

<!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>My DB</title>

</head>

<body>

Hello <%=firstName%>!

</body>

</html>

13. Right-click on your db project.

14. Click on Export=>war file

15. In Destination, put c:\db.war and click Finish button

Now you need publish your Hello World DB jsp on internet. I found a good JPS hosting company EATJ.com http://www.eatj.com/ . They have free trial account. You can get your hosting space immediately, once you register on EATJ.

16. Once your EATJ account is activated, login to your EATJ account. Scroll down, you will find UPLOAD WAR FILES section. Click Browser button and go to c:\ and find your db.war and click Upload button.

17. Click on Restart link. You will see something like http://<username>.<hostname>.eatj.com/

18. Click on this link. You will see Tomcat page.

19. Append hello/hello.jsp in your URL such as http://<username>.<hostname>.eatj.com/db/db.jsp

20. You should see Hello John on your web page. “John” is retrieved from database.

For this tutorial source code, you can download it at http://www.postpart.com/db.war




More tutorials: http://jsptutorblog.blogspot.com/