Bright Java Tutorial

is brought to you by DoITBright LLC

JSP

Connect JSP to MySQL

One of the top reasons why you want to do server side programming such as Java Server Pages is to be able to access server side resources such as a relational database management system like MySQL. In this tutorial, we will look at an example JSP that establishes database connection to MySQL. Before anything else, it is assumed that you have prior knowledge to MySQL before going through this tutorial. If not, it is advisable for you to go through the MySQL Tutorial that can also be found in this website. Before we start coding our JSP, we need to do some preparations. It is now time to create our JSP in /jspLessons directory. We can name it as 'connectJspToMySQL.jsp'. Here are the contents of the JSP file...

  <%@ page import="java.sql.*,java.util.Date,
    java.text.SimpleDateFormat" %>
  <html>
    <head>
      <title>Connect JSP to MySQL</title>
    </head>
    <body>
  <%
    Connection connection = null;
    Statement statement = null;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      connection = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/MY_FIRST_DB?" + 
        "user=root&password=root");
      statement = connection.createStatement();
      String email = "customerAbc@testmail.com";
      Date now = new Date();
      SimpleDateFormat sdf 
        = new SimpleDateFormat("yyyy-MM-dd");
      String strNow = sdf.format(now);
      String sql 
        = "INSERT INTO ACCOUNT(EMAIL,CREATE_DATE)" 
        + " VALUES ('" + email + "', '" + strNow + "')";
      statement.executeUpdate(sql);
    } catch (SQLException sqle) {
      out.println(sqle.getMessage());
    } catch (Exception e) {
      out.println(e.getMessage());
    } finally {
      try {
        statement.close();
        connection.close();
      } catch (Exception ex) {
        out.println(ex.getMessage());
      }
    }
  %>
    </body>
  </html>
                            

It is assumed in the above code that you are using 'root' as user and 'root' as password in your MySQL. Deploy the jspLessons application in your tomcat webapps directory and start the server. Invoke the URL http://localhost:8080/jspLessons/connectJspToMySQL.jsp and it is expected that you will get an empty page. Validate your effort by opening your MySQL Workbench and performing a SELECT statement against your ACCOUNT table. You should be able to see the data that you inserted.


Back    Next