Wednesday, December 30, 2020

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

Consider code provided by https://www.javaguides.net/2019/03/jsp-servlet-jdbc-mysql-crud-example-tutorial.html 

Upgrade MySQL up to 8.0.22 would affect JDBC setup mentioned in post above utilizing mysql-connector-java-8.0.13.jar. Refactoring this code for the most recent version of MySQL follows below.  We also intend to add DBConnection.java and update correspondingly UserDAO.java, encapsulating JDBC setup into public DBConnection.java  as public static method createConnection(). The only jars been placed under WEB-INF/lib are jstl-1.2.jar  and    mysql-connector-java-8.0.22.jar

[tomcat@sever33fedora classes]$ cat ../src/DBConnection.java
package net.javaguides.usermanagement.model;
 
import java.sql.Connection;
import java.sql.DriverManager;
 
public class DBConnection {
 public static Connection createConnection()
 {
     String url = "jdbc:mysql://localhost/demo?allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC";

     String username = "root"; 
     String password = "root"; 
     System.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 classes]$ cat  ../src/UserDAO.java                       
package net.javaguides.usermanagement.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.javaguides.usermanagement.model.User;
import net.javaguides.usermanagement.model.DBConnection ;

public class UserDAO {

  private static final String INSERT_USERS_SQL = "INSERT INTO users" + "  (name, email, country) VALUES " +
        " (?, ?, ?);";

 private static final String SELECT_USER_BY_ID = "select id,name,email,country from users where id =?";
 private static final String SELECT_ALL_USERS = "select * from users";
 private static final String DELETE_USERS_SQL = "delete from users where id = ?;";
 private static final String UPDATE_USERS_SQL = "update users set name = ?,email= ?, country =? where id = ?;";


  public void insertUser(User user) throws SQLException {
       System.out.println(INSERT_USERS_SQL);
       try(Connection connection = DBConnection.createConnection();PreparedStatement preparedStatement = 
connection.prepareStatement(INSERT_USERS_SQL))
{ preparedStatement.setString(1, user.getName()); preparedStatement.setString(2, user.getEmail()); preparedStatement.setString(3, user.getCountry()); System.out.println(preparedStatement); preparedStatement.executeUpdate(); } catch (SQLException e) { printSQLException(e); } } public User selectUser(int id) { User user = null; try(Connection connection = DBConnection.createConnection();PreparedStatement preparedStatement =
connection.prepareStatement(SELECT_USER_BY_ID))
{ preparedStatement.setInt(1, id); System.out.println(preparedStatement); ResultSet rs = preparedStatement.executeQuery(); while (rs.next()) { String name = rs.getString("name"); String email = rs.getString("email"); String country = rs.getString("country"); user = new User(id, name, email, country); } } catch (SQLException e) { printSQLException(e); } return user; } public List < User > selectAllUsers() { List < User > users = new ArrayList < > (); try(Connection connection = DBConnection.createConnection();PreparedStatement preparedStatement =
connection.prepareStatement(SELECT_ALL_USERS))
{ System.out.println(preparedStatement); ResultSet rs = preparedStatement.executeQuery(); while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); String email = rs.getString("email"); String country = rs.getString("country"); users.add(new User(id, name, email, country)); } } catch (SQLException e) { printSQLException(e); } return users; } public boolean deleteUser(int id) throws SQLException { boolean rowDeleted = true ; try(Connection connection = DBConnection.createConnection();PreparedStatement statement =
connection.prepareStatement(DELETE_USERS_SQL))
{ statement.setInt(1, id); rowDeleted = statement.executeUpdate() > 0; } catch (SQLException e) { printSQLException(e); } return rowDeleted; } public boolean updateUser(User user) throws SQLException { boolean rowUpdated = true ; try(Connection connection = DBConnection.createConnection();PreparedStatement statement =
connection.prepareStatement(UPDATE_USERS_SQL))
{ statement.setString(1, user.getName()); statement.setString(2, user.getEmail()); statement.setString(3, user.getCountry()); statement.setInt(4, user.getId()); rowUpdated = statement.executeUpdate() > 0; } catch (SQLException e) { printSQLException(e); } return rowUpdated; } 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(); } } } } }
Compile source files
 $  javac -cp .:$CLASSPATH ../src/DBConnection.java -d  .  
 $  javac -cp .:$CLASSPATH ../src/User.java -d  . 
 $  javac -cp .:$CLASSPATH ../src/UserDAO.java -d  .
 $  javac -cp .:$CLASSPATH ../src/UserServlet.java -d  .
FILES LAYOUT

[tomcat@server33fedora WEB-INF]$ ls -CR
.:
classes  lib  src

./classes:
net

./classes/net:
javaguides

./classes/net/javaguides:
usermanagement

./classes/net/javaguides/usermanagement:
dao  model  web

./classes/net/javaguides/usermanagement/dao:
UserDAO.class

./classes/net/javaguides/usermanagement/model:
DBConnection.class  User.class

./classes/net/javaguides/usermanagement/web:
UserServlet.class

./lib:
jstl-1.2.jar  mysql-connector-java-8.0.22.jar

./src:
DBConnection.java  UserDAO.java  User.java  UserServlet.java














































Definition of User.java been proposed in original link
package net.javaguides.usermanagement.model;

/**
 * User.java
 * This is a model class represents a User entity
 * @author Ramesh Fadatare
 *
 */
public class User {
    protected int id;
    protected String name;
    protected String email;
    protected String country;

    public User() {}

    public User(String name, String email, String country) {
        super();
        this.name = name;
        this.email = email;
        this.country = country;
    }
    public User(int id, String name, String email, String country) {
        super();
        this.id = id;
        this.name = name;
        this.email = email;
        this.country = country;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getCountry() {
        return country;
    }
    public void setCountry(String country) {
        this.country = country;
    }
}

No comments:

Post a Comment