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 . 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"
        <title>Create table in mysql database using jsp</title>
        <TABLE style="background-color: #ffffcc;">
                <TD align="center"><h2>To create
                    table click on the link given below</h2></TD>
                <TD align="center"><A HREF="create_table.jsp">
                <font size="+2" color="blue">create table</font>

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"
<%@ page import="java.sql.*" %>
<%@ page import="*" %>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
        <title>display data from the table using jsp</title>
            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".
                /* 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));";
        %> <TABLE border="0" style="background-color: #ffffcc;">
            <TD align="center" ><font size="+3" color="green">Congratulations !
            <tr><TD align="center" ><font size="+2">Table of specified name is
                created successfully.</font>
        <% } catch (Exception ex) {%>
        <TABLE border="0" style="background-color: #ffffcc;">
                <TD align="center" ><font size="+3" color="red">Some problems
               to create table.</font></TD>
            <% } finally {
                // close all the connections.
            %><tr><td align="center" >
                    <A HREF="Hello_database_query.jsp">
                <font size="5" color="blue">back to home page</font></A></td>
        </tr>  </TABLE>

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)


If table exists already or found any error to make connection with database then shows an error page.

Leave a Reply

Your email address will not be published. Required fields are marked *