Last updated on November 22nd, 2022 at 05:16 pm
This can be deployed in Apache tomcat and we will connect to MySQL to create table.
Some prerequisite before we start
- Create a folder named ‘user‘ under ‘webapps‘
- Create a database in MySQL named user
- Before running this JSP, you need mysql connector jar in the /webapps/user/WEB-INF/lib directory.
- MySQL Connector I am using mysql-connector-java-8.0.28.jar, Download connector from https://dev.mysql.com/downloads/connector/j/ . I selected “Platform Independent (Architecture Independent), ZIP Archive”
- I deployed this JSP code in Apache Tomcat/10.0.20
Write JSP code
We are going to have 2 JSP files
1. Hello_database_query.jsp just an plain HTML file with a create table hyperlink (not really required but just for fun)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<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 the above html code under <TOMCAT_INSTALL>/webapps/user/ directory.
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 all the database action happens in this jsp file. This will create a table named user_details with columns id,name and city
Before running the code make sure you update
Database name: String connectionURL = “jdbc:mysql://localhost:3306/user”;
Credentials : DriverManager.getConnection(connectionURL, “root”, “”);
<!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.*" %>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<html>
<head>
<title>display data from the table using jsp</title>
</head>
<body>
<%
String connectionURL = "jdbc:mysql://localhost:3306/user";
// 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", "");
statement = connection.createStatement();
// sql query to retrieve values from the specified table.
String QueryString =
"create table user_details(id int not null auto_increment,name " +
"varchar(25),city varchar(20), primary key(id));";
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 /webapps/user/
This code creates the table inside mysql database and shows a response page like below
If you login to your MySQL database you should see the details of the table created like I have shown below
mysql> use user;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from user_details;
Empty set (0.01 sec)
mysql> describe user_details;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(25) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql>
If table exists already or found any error to make connection with database then shows an error page.
admin