Create a Table in Mysql database through SQL Query in JSP
This is detailed java code to connect a jsp page to
mysql database and create a table of given name in the specified database.
Create a database : First create a database named 'usermaster' in mysql and create table "user_master". Create an application directory named 'user' in the Tomcat. 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. welcome_to_database_query.jsp
2.create_table.jsp
1. welcome_to_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 "welcome_to_database_query.jsp" in the directory Tomcat-6.0.16/webapps/user/. You can run this jsp page typing http://localhost:8080/user/welcome_to_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="welcome_to_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.
