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>"); %>
// 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"
No comments:
Post a Comment