Thursday, December 31, 2020

How to catch SQLException in JSP working via JDBC with MySQL 8.0.22 on Tomcat 9.0.41

 Encapsulate JDBC setup into public DBConnection.java and afterwards in any JSP working with database we should be able import this class and invoke :-

 Connection conn = DBConnection.createConnection();

Define public class DBConnection.java and static method inside named createConnection()

 [tomcat@sever33fedora src]$ cat DBConnection.java

package net.javaguides.jsp.jdbc.database;

import java.sql.Connection;

import java.sql.DriverManager;

public class DBConnection {

 public static Connection createConnection()

 {

     String url = "jdbc:mysql://localhost/ebookshop?allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC";

     String username = "root"; 

     String password = "root"; 

     out.println("In DBConnection.java class ");

     Connection conn = null;

try  {

 try  {  

Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance();

 }   catch (ClassNotFoundException e)    {  

                e.printStackTrace();  

        }   

           conn = DriverManager.getConnection(url, username, password); 

      }   catch (Exception e)   {  

        e.printStackTrace(); 

     }   

   return conn ; 

   }

}

[tomcat@sever33fedora GuruVerify]$ cat  WebUpdateExt.jsp         
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.SQLException"%>
<%@page import="net.javaguides.jsp.jdbc.database.DBConnection"%>
<%@page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>

<%
String query = "update books set author = ?, title = ?  where id = ?";  
try(Connection conn
=DBConnection.createConnection(); PreparedStatement Stat = conn.prepareStatement(query)) 

  {  
        Statement stmt = conn.createStatement();  
        String avtor = request.getParameter("param1") ;
        String nazv =  request.getParameter("param2") ;
        String code =  request.getParameter("param3") ;
        int key = Integer.parseInt(code);

        Stat.setString(1, avtor);
        Stat.setString(2, nazv);
        Stat.setInt(3, key);
        Stat.executeUpdate();


   String strSelect = "select id, author, title, price, qty from books";
   out.println("The SQL statement is: " + strSelect + "\n");

   out.println("<br>");
   out.println("<br>");

        ResultSet rset = stmt.executeQuery(strSelect);

  out.println("The records selected are:");
  int rowCount = 0;
  out.println("<html>");
  out.println("<body bgcolor=\"C0C0C0\">");
  out.println("<table border=\"2\">");
  while(rset.next()) {
           int id = rset.getInt("id");
           String author = rset.getString("author");
           String title = rset.getString("title");
           double price = rset.getDouble("price");
           int    qty   = rset.getInt("qty");
           out.println("<tr>");
           out.println("<td>");
           out.println(Integer.toString(id)+','+author+','+title + ", " + price + ", " + qty);
           out.println("</td>");
           out.println("</tr>");
           ++rowCount;
        }   
 }  catch (SQLException e) {

      throw new ServletException("SQL failed!", e);
 }

  out.println("</table>");  
  out.println("</body>");
  out.println("</html>");
%>
[tomcat@sever33fedora GuruVerify]$ cat  WebInsertExt.jsp
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.SQLException"%>

<%@page import="net.javaguides.jsp.jdbc.database.DBConnection"%>
<%@page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<%
String query = "INSERT INTO books(id,title,author,price,qty) VALUES(?,?,?,?,?)";
try(Connection conn = DBConnection.createConnection(); PreparedStatement Stat = conn.prepareStatement(query))
  {   

        Statement stmt = conn.createStatement();  
        String code =  request.getParameter("param1") ;
        String nazv =  request.getParameter("param2") ;
        String avtor = request.getParameter("param3") ;
        String price  =  request.getParameter("param4") ;
        String quantity = request.getParameter("param5") ;

        int key = Integer.parseInt(code);
        double dprice  = Double.parseDouble(price+"d");
        int qntity = Integer.parseInt(quantity);

        Stat.setInt(1, key);
        Stat.setString(2, nazv);
        Stat.setString(3, avtor);
        Stat.setDouble(4, dprice);
        Stat.setInt(5, qntity);

        Stat.executeUpdate();


   String strSelect = "select id, author, title, price, qty from books";
   out.println("The SQL statement is: " + strSelect + "\n");

   out.println("<br>");
   out.println("<br>");

        ResultSet rset = stmt.executeQuery(strSelect);

  out.println("The records selected are:");
  int rowCount = 0;
  out.println("<html>");
  out.println("<body bgcolor=\"C0C0C0\">");
  out.println("<table border=\"2\">");
  while(rset.next()) {
           int id = rset.getInt("id");
           String author = rset.getString("author");
           String title = rset.getString("title");
           double price1 = rset.getDouble("price");
           int    qty   = rset.getInt("qty");
           out.println("<tr>");
           out.println("<td>");
           out.println(Integer.toString(id)+','+author+','+title + ", " + price1 + ", " + qty);
           out.println("</td>");
           out.println("</tr>");
           ++rowCount;
        }
 }  catch (SQLException e) {

   throw new ServletException("SQL failed!", e);
 }

  out.println("</table>");  
  out.println("</body>");
  out.println("</html>");
%>
[tomcat@sever33fedora GuruVerify]$ cat WebWorkExt.jsp            
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.SQLException"%>
<%@page import="net.javaguides.jsp.jdbc.database.DBConnection"%>
<%@page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<%
try(Connection conn = DBConnection.createConnection(); Statement stmt = conn.createStatement())
 {

   String strSelect = "select id, author, title, price, qty from books";
   out.println("The SQL statement is: " + strSelect + "\n");
   out.println("<br>");
   out.println("<br>");

        ResultSet rset = stmt.executeQuery(strSelect);

   out.println("The records selected are:");
   int rowCount = 0;
   out.println("<html>");
   out.println("<body bgcolor=\"C0C0C0\">");
   out.println("<table border=\"2\">");
    while(rset.next()) {
           int    id = rset.getInt("id");
           String author = rset.getString("author");
           String title = rset.getString("title");
           double price = rset.getDouble("price");
           int    qty   = rset.getInt("qty");
           out.println("<tr>");
           out.println("<td>");
           out.println(Integer.toString(id)+','+author+','+title + ", " + price + ", " + qty);
           out.println("</td>");
           out.println("</tr>");
           ++rowCount;
     } 
 } catch (SQLException e) {

      throw new ServletException("SQL failed!", e);
 }

  out.println("</table>");  
  out.println("</body>");
  out.println("</html>");
%>

A sample of html form accepting data and invoking JSP

[tomcat@sever33fedora GuruVerify]$ cat ThreeParamsForm.html 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> 
<HTML><HEAD><TITLE>Collecting Three Parameters</TITLE></HEAD> 
<BODY BGCOLOR="#C0C0C0"> 
<H1 ALIGN="CENTER">Updating MySQL Table via JDBC</H1> 
<FORM ACTION="WebUpdateExt.jsp"> 
<TABLE BORDER="2"> 
<tr> 
<td> 
Update Author: <INPUT TYPE="TEXT" NAME="param1"> 
</td> 
</tr> 
<tr> 
<td> 
Update Title: <INPUT TYPE="TEXT" NAME="param2"> 
</td> 
</tr> 
<tr> 
<td> 
Key Record's Code : <INPUT TYPE="TEXT" NAME="param3"> 
</td> 
</tr> 
<tr> 
<td> 
<CENTER><INPUT TYPE="SUBMIT"></CENTER> 
</td> 
</tr> 
</TABLE> 
</FORM> 
</BODY></HTML>

FILE LAYOUT

.:
FiveParamsForm.html  images  WEB-INF  WebUpdateExt.jsp
index.jsp  ThreeParamsForm.html  WebInsertExt.jsp  WebWorkExt.jsp
./images:
tomcat.gif
./WEB-INF:
classes  lib  src  web.xml
./WEB-INF/classes:
net
./WEB-INF/classes/net:
javaguides
./WEB-INF/classes/net/javaguides:
jsp
./WEB-INF/classes/net/javaguides/jsp:
jdbc
./WEB-INF/classes/net/javaguides/jsp/jdbc:
database
./WEB-INF/classes/net/javaguides/jsp/jdbc/database:
DBConnection.class
./WEB-INF/lib:
 mysql-connector-java-8.0.22.jar
./WEB-INF/src:
DBConnection.java



No comments:

Post a Comment