Sunday, December 27, 2020

Attempt to deploy "Employee Register" to Tomcat 9.0.40 (MySQL 8.0.22)

UPDATE 01/01/2021

See also    Refactoring Java CRUD App to be deployed to Tomcat 9.0.41 (MySQL 8.0.22)

END UPDATE

As far as I can see any Java CRUD App would require a hack kind  of  described below , no matter is it "update" or "insert" or "select" JDBC setup doesn't work as it did earlier . Code bellow - invoking  Connection "conn = DBConnection.createConnection();" along with setup JDBC encapsulated into DBConnection.java works for me. All updates to original code are marked bold.

Original code been refactored was taken from 

https://www.javaguides.net/2019/03/servlet-jsp-jdbc-mysql-example.html

However, inserts fails to commit on the most recent version of MySQL 8.0.22.

Following bellow are the only Java classes I was forced to update to get the job done.  Recreate table 

CREATE TABLE  employee (
   id int(3) NOT NULL  AUTO_INCREMENT,
   first_name varchar(20) DEFAULT NULL,
   last_name varchar(20) DEFAULT NULL,
   username varchar(250) DEFAULT NULL,
   password  varchar(20) DEFAULT NULL,
   address varchar(45) DEFAULT NULL,
   contact varchar(45) DEFAULT NULL,
   PRIMARY KEY (id)
)  ;

[tomcat@server33fedora 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/mysql_database?allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC";
     String username = "root"; 
     String password = "root"; 
          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@server33fedora src]$ cat  EmployeeDao.java
package net.javaguides.jsp.jdbc.database;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import net.javaguides.jsp.jdbc.bean.Employee;
public class EmployeeDao {
    public int registerEmployee(Employee employee) throws ClassNotFoundException {
    int result = 0 ; 
    String INSERT_USERS_SQL = "INSERT INTO employee" +
    " (first_name, last_name, username, password, address, contact) VALUES " + " (?,?,?,?,?,?);"
   try(Connection conn = DBConnection.createConnection(); preparedStatement =
conn.prepareStatement(INSERT_USERS_SQL))
      {
         preparedStatement.setString(1, employee.getFirstName());
         preparedStatement.setString(2, employee.getLastName());
         preparedStatement.setString(3, employee.getUsername());
         preparedStatement.setString(4, employee.getPassword());
         preparedStatement.setString(5, employee.getAddress());
         preparedStatement.setString(6, employee.getContact());
        
         result = preparedStatement.executeUpdate();

        } catch (SQLException e) {
            // process sql exception
            printSQLException(e);
        }
        return result;
    }
    private void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
Notice , that JSP containing :-
 Connection conn = DBConnection.createConnection();
would require import
<%@page import="net.javaguides.jsp.jdbc.database.DBConnection"%>

*******************
NO CHANGES HERE 
*******************
[tomcat@server33fedora src]$ cat EmployeeServlet.java
package net.javaguides.employeemanagement.web;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.javaguides.jsp.jdbc.database.EmployeeDao;
import net.javaguides.jsp.jdbc.bean.Employee;
@WebServlet("/register")
public class EmployeeServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    private EmployeeDao employeeDao;

    public void init() {
        employeeDao = new EmployeeDao();
    }
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {

        String firstName = request.getParameter("firstName");
        String lastName = request.getParameter("lastName");
        String username = request.getParameter("username");
        String password = request.getParameter("password");
        String address = request.getParameter("address");
        String contact = request.getParameter("contact");

        Employee employee = new Employee();
        employee.setFirstName(firstName);
        employee.setLastName(lastName);
        employee.setUsername(username);
        employee.setPassword(password);
        employee.setContact(contact);
        employee.setAddress(address);

        try {
            employeeDao.registerEmployee(employee);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        response.sendRedirect("employeedetails.jsp");
    }
}
************************
DIRECTORY STRUCTURE
************************
[tomcat@server33fedora WEB-INF]$ ls -CR
.:
classes  lib  src

./classes:
'\'   net

./classes/net:
javaguides

./classes/net/javaguides:
employeemanagement  jsp

./classes/net/javaguides/employeemanagement:
web

./classes/net/javaguides/employeemanagement/web:
EmployeeServlet.class

./classes/net/javaguides/jsp:
jdbc

./classes/net/javaguides/jsp/jdbc:
bean  database

./classes/net/javaguides/jsp/jdbc/bean:
Employee.class

./classes/net/javaguides/jsp/jdbc/database:
DBConnection.class  EmployeeDao.class
Just one jar under lib folder
./lib:
mysql-connector-java-8.0.22.jar

./src:
DBConnection.java  EmployeeDao.java  Employee.java  EmployeeServlet.java
Replaced page employeedetails.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="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"%>
<%
  
try(Connection conn = DBConnection.createConnection();
PreparedStatement preparedStatement = conn.prepareStatement("select * from employee")) { ResultSet resultSet = preparedStatement.executeQuery(); int rowCount = 0; out.println("<html>"); out.println("<body bgcolor=\"C0C0C0\">"); out.println("<table border=\"2\">"); while(resultSet.next()) { int id = resultSet.getInt("id"); String first_name = resultSet.getString("first_name"); String last_name = resultSet.getString("last_name"); String username = resultSet.getString("username"); String address = resultSet.getString("address"); out.println("<tr>"); out.println("<td>"); out.println(Integer.toString(id)+','+first_name+' '+last_name+", "+username+", "+address); 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>"); %>
Regarding the most recent version of employeedetails.jsp
// Original code  https://www.javaguides.net/2019/01/jsp-registration-form-jdbc-mysql-example.html
// net.javaguides.jsp.jdbc.database.DBConnection is imported as well
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
 pageEncoding="ISO-8859-1"%>
<%@page import="java.sql.*"%>
<%@page import="net.javaguides.jsp.jdbc.database.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
 <jsp:useBean id="employee"
  class="net.javaguides.jsp.jdbc.database.Employee" />
 <jsp:setProperty property="*" name="employee" />

 <%
  EmployeeDao employeeDao = new EmployeeDao();
  int status = employeeDao.registerEmployee(employee);
  if (status > 0) {
   out.println("You are successfully registered");
   out.println("<br>");
   out.println("<br>");
  }

// Added by B.D.

   String fName = request.getParameter("firstName");
   String lName = request.getParameter("lastName");

try(Connection conn = DBConnection.createConnection(); PreparedStatement preparedStatement =  conn.prepareStatement("select * from employee where  first_name = ? and last_name = ?"))
  {
        preparedStatement.setString(1, fName);
        preparedStatement.setString(2, lName);

      ResultSet resultSet = preparedStatement.executeQuery();

   int rowCount = 0;
   out.println("<table border=\"2\">");
   while(resultSet.next()) {
            int    id = resultSet.getInt("id");
            String first_name = resultSet.getString("first_name");
            String last_name = resultSet.getString("last_name");
            String username = resultSet.getString("username");
            String address = resultSet.getString("address");

            out.println("<tr>");
            out.println("<td>");
            out.println(Integer.toString(id)+','+first_name+' '+last_name+", "+username+", "+address);
            out.println("</td>");
            out.println("</tr>");

            ++rowCount;
     }
    }  catch (SQLException e)   {
              throw new ServletException("SQL failed!", e);
    }
   out.println("</table>");
 %>
</body>
</html>











































 











No comments:

Post a Comment