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.