[tutorial] Database Connection in JSP
have I told you that I am both a Programming teacher in Vocational High School and Lecturer in one of college in town. So, now in vocational high school we are in the second semester this year and based on the teaching subject that shared among the teachers, I -again- assigned to teach Java Server Page. I am a Web-Based-Application freak and I love web Programming. Web Programming allow me make the Program and designed the page at the same time. but I am more like a PHP Girl rather than a Java Woman, and I don’t want to cheat him… I don’t know, it isn’t like I hate Java, I just not so get along with him…. sorry Mr. Java.
But if the boss said that I have to teach it, so be it. When the First semester of the third class was learn about the basic of JSP, now this semester I will connect their application with the Database.
Application you might need :
- Netbeans ( I used Netbeans IDE 7.2.1 )
- XAMPP ( I used XAMPP 1.7.3 )
note here that, if you found tomcat in this version of XAMPP, just leave it alone. we won’t disturb him. we need this XAMPP just for their MySQL Database. maybe you wondering why didn’t I install another version of GUI MySQL. The reason is, this XAMPP and every application inside is package that I used for all of my PHP application. - Browser ( it’s obvious )
- Mysql Connector for Java you can download here
I assume you already started the mysql database and already installed all of the application above. Fasten your seat belt Ladies and Gentlemen, we’ll go for a ride….
- Start your netbeans application
- Make your new Project, Click File-New ProjectPict 1
- Choose Java Web-Web Application when ‘New Project’ Windows appear then click Next.Pict 2
- Fill the name of your new Project and define the place where you want to place your project. Click NextPict 3
- the Next Page is Server setting, just leave them alone if you don’t have another web server you want to use. I use the default setting and just click next to the next page.Pict 4
- Just Click finish in this PagePict 5
- When it’s done, Look at the up left panel of your workspace, click the ‘+’ symbol near your project name, then right click on a library tree, Click ‘Add JAR/Folder…’ buttonPict 6
- When the windows is open, locate your ‘mysql connector’ Library you donwload and click ‘Open’Pict 7Your left panel will be like like this, after you add the mysql connectorPict 8
- Then you need to make the connection in order to connect to the database you wish. so, Click ‘Service’ tab menuPict 9
- Right Click on ‘Database’ tree menu and choose ‘New Connection…’Pict 10
- In the first Page of ‘New Connection Wizard’ you have to define the driver you will use. I choose ‘Mysql (Connector / J driver)’ then click nextPict 11
- Fill the Host name, username and password of your database then click ‘Test Connection’ button.Pict 12when the ‘connection succeeded’ information has been appear, means that you can click the Finish buttonPict 13
- back to your Database tree menu, several database name will appear according to your current database. Right click on the database you will use, choose ‘Connect’Pict 15
- and the last part is, Write the code. I used the following syntax to view the record from my ‘siswa’ table from database ‘osis’
<%@page contentType=”text/html” pageEncoding=”UTF-8″%>
<%@ page import=”java.sql.*” %>
<%@ page import=”java.io.*” %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=UTF-8″>
<title>JSP Page</title>
</head>
<body>
<%
try {
String Host = “jdbc:mysql://localhost:3306/osis”;
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
Class.forName(“com.mysql.jdbc.Driver”);
connection = DriverManager.getConnection(Host, “root”, “”);
statement = connection.createStatement();
String Data = “select * from pil”;
rs = statement.executeQuery(Data);
%>
<TABLE border=”1″>
<tr width=”10″ bgcolor=”#9979″>
<td>Kode</td>
<td>Nama</td>
<td>Count</td></tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString(“kd”)%></TD>
<TD><%=rs.getString(“nama”)%></TD>
<TD><%=rs.getString(“count”)%></TD>
</TR>
<% } %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println(“Can’t connect to database.”);
}
%>
<h1>Hello World!</h1></body>
</html>
<%@ page import=”java.sql.*” %>
<%@ page import=”java.io.*” %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=UTF-8″>
<title>JSP Page</title>
</head>
<body>
<%
try {
String Host = “jdbc:mysql://localhost:3306/osis”;
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
Class.forName(“com.mysql.jdbc.Driver”);
connection = DriverManager.getConnection(Host, “root”, “”);
statement = connection.createStatement();
String Data = “select * from pil”;
rs = statement.executeQuery(Data);
%>
<TABLE border=”1″>
<tr width=”10″ bgcolor=”#9979″>
<td>Kode</td>
<td>Nama</td>
<td>Count</td></tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString(“kd”)%></TD>
<TD><%=rs.getString(“nama”)%></TD>
<TD><%=rs.getString(“count”)%></TD>
</TR>
<% } %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println(“Can’t connect to database.”);
}
%>
<h1>Hello World!</h1></body>
</html>
