This is detailed java code to connect a jsp page to mysql database
and create a table of given name in the specified database.
Steps to Follow:
1. create a database named 'usermaster' in mysql and create table "user_master".
2. Create an application directory named 'user' in the Tomcat.
3. Before running this java code you need mysql connector jar
in the Tomcat-6.0.16/webapps/user/WEB-INF/lib directory.
Creating JSP pages:
In this example, we have created two JSP pages.
First page is to provide link to create table and the next page for processing the table creation.
1. Hello_database_query.jsp
2.create_table.jsp
1. Hello_database_query.jsp
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<html>
<head>
<title>Create table in mysql database using jsp</title>
</head>
<body>
<TABLE style="background-color: #ffffcc;">
<TR>
<TD align="center"><h2>To create
table click on the link given below</h2></TD>
</TR>
<TR>
<TD align="center"><A HREF="create_table.jsp">
<font size="+2" color="blue">create table</font>
</TR>
</TABLE>
</body>
</html>
Save this code as a .jsp file named "Hello_database_query.jsp" in the directory Tomcat-6.0.16/webapps/user/.
You can run this jsp page typing http://localhost:8080/user/Hello_database_query.jsp url in address bar of the browser.
2.create_table.jsp
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<html>
<head>
<title>display data from the table using jsp</title>
</head>
<body>
<%
/* Create string of connection url within specified
format with machine name, port number
and database name. Here machine name id localhost
and database name is usermaster. */
String connectionURL = "jdbc:mysql://localhost:3306/usermaster";
// declare a connection by using Connection interface
Connection connection = null;
// declare object of Statement interface that is used for
// executing sql statements.
Statement statement = null;
try {
// Load JBBC driver "com.mysql.jdbc.Driver".
Class.forName("com.mysql.jdbc.Driver").newInstance();
/* Create a connection by using getConnection() method
that takes parameters of string type connection url, user
name and password to connect to database. */
connection =
DriverManager.getConnection(connectionURL, "root", "root");
/* createStatement() is used for create statement object
that is used for sending
sql statements to the specified database. */
statement = connection.createStatement();
// sql query to retrieve values from the secified table.
String QueryString =
"create table user_master(id int not null auto_increment,name " +
"varchar(25),city varchar(20), primary key(id));";
// execyteUpdate() mothod execute specified sql query.
statement.executeUpdate(QueryString);
%> <TABLE border="0" style="background-color: #ffffcc;">
<TR>
<TD align="center" ><font size="+3" color="green">Congratulations !
</font></TD>
<tr><TD align="center" ><font size="+2">Table of specified name is
created successfully.</font>
</TD></tr>
</TR>
<%
} catch (Exception ex) {%>
<TABLE border="0" style="background-color: #ffffcc;">
<TR>
<TD align="center" ><font size="+3" color="red">Some problems
to create table.</font></TD>
</TR>
<% } finally {
// close all the connections.
statement.close();
connection.close();
}
%><tr><td align="center" >
<A HREF="Hello_database_query.jsp">
<font size="5" color="blue">back to home page</font></A></td>
</tr>
</TABLE>
</body>
</html>
Save this code with name "create_table.jsp" in the directory Tomcat-6.0.16/webapps/user/.
This jsp code creates table of specified name in the mysql database and shows a response page.
If table exists already or found any error to make connection with database then shows an error page.
