Implementation of DBlayer in Java (RDB, MySQL)

MySQL

DBConstants First, let's define the constants for DB connection! !!

DBConstants

public class Constants {

	public static final String DB_SERVER = "localhost";
	public static final String DB_PORT = "0000";
	public static final String DB_DRIVER = "com.mysql.jdbc.Driver";
	public static final String DB_USERNAME = "root";
	public static final String DB_PASSWORD = "root";
	public static final String DB_URL = "jdbc:mysql://localhost:0000/schema?autoReconnect=true&useSSL=false";
}

DBConection Next, let's create a class to connect to the database! !!

DBConection

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DBConn {

	//Connect to MySQL
	public Connection conn;
	//Create statement
	public Statement stmt;

	public DBConn() {
		try {
			Class.forName(Constants.DB_DRIVER);

			conn = DriverManager.getConnection(Constants.DB_URL, Constants.DB_USERNAME, Constants.DB_PASSWORD);
			stmt = conn.createStatement();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void finalize() {
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

DBEntity Next, let's create an Entity class! !!

DBEntity

public class UserEntity {

	private int id = 1;

	private String username = null;

	private String password = null;

	private Date lastlogin = null;

	private boolean deleteflg = false;

	private String name = null;

	public UserEntity() {
		this(0, null, null);
	}

	public UserEntity(int id, String username, String password) {
		this.setId(id);
		this.setUsername(username);
		this.setPassword(password);
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public Date getLastlogin() {
		return lastlogin;
	}

	public void setLastlogin(Date lastlogin) {
		this.lastlogin = lastlogin;
	}

	public boolean getDeleteflg() {
		return deleteflg;
	}

	public void setDeleteflg(boolean deleteflg) {
		this.deleteflg = deleteflg;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
}

DBDAO Finally, let's create a DAO class! !! (Let's inherit the DBConnection class created earlier)

DBDAO

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserDAO extends DBConn {

	public List<UserEntity> read(String wherestatement, String orderby, String ascdsc) throws SQLException {

		List<UserEntity> listuserEntity = new ArrayList<>();
		String sqlQuery = "SELECT * FROM user";

		if(wherestatement != "") {
			sqlQuery += " WHERE " + wherestatement;
		}

		if(orderby != "") {
			sqlQuery += " ORDER BY " + orderby;
		}

		if(ascdsc != "") {
			sqlQuery += " " + ascdsc;
		}

			//SELECT
			ResultSet rset = stmt.executeQuery(sqlQuery);
			while(rset.next()) {
				UserEntity userEntity = new UserEntity();
				userEntity.setId(rset.getInt("id"));
				userEntity.setUsername(rset.getString("username"));
				userEntity.setPassword(rset.getString("password"));
				userEntity.setLastlogin(rset.getDate("lastlogin"));
				userEntity.setDeleteflg(rset.getBoolean("deleteflg"));
				userEntity.setName(rset.getString("name"));
				listuserEntity.add(userEntity);
			}

			//Close result set
			rset.close();

		return listuserEntity;
	}

	public UserEntity readId(int id) throws SQLException {

		UserEntity userEntity = new UserEntity();

		//SELECT
		ResultSet rset = stmt.executeQuery("SELECT * FROM user WHERE id = "+ id);
		while(rset.next()) {
			userEntity.setId(rset.getInt("id"));
			userEntity.setUsername(rset.getString("username"));
			userEntity.setPassword(rset.getString("password"));
			userEntity.setLastlogin(rset.getDate("lastlogin"));
			userEntity.setDeleteflg(rset.getBoolean("deleteflg"));
			userEntity.setName(rset.getString("name"));
		}

		//Close result set
		rset.close();

		return userEntity;
	}

	public void add(UserEntity userEntity) throws SQLException {

		int id = userEntity.getId();
		String username = userEntity.getUsername();
		String password = userEntity.getPassword();
		Date lastlogin = userEntity.getLastlogin();
		boolean deleteflg = userEntity.getDeleteflg();
		String name = userEntity.getName();

		//INSERT
		int insert = stmt.executeUpdate("INSERT INTO user VALUES ("+ id +",'" +username +"','" + password +"'," + lastlogin + "," + deleteflg + ",'" + name  + ")");
	}

	public void update(int id, UserEntity userEntity) throws SQLException {

		int userid = userEntity.getId();
		String username = userEntity.getUsername();
		String password = userEntity.getPassword();
		Date lastlogin = userEntity.getLastlogin();
		boolean deleteflg = userEntity.getDeleteflg();
		String name = userEntity.getName();

		//UPDATE
		int rset = stmt.executeUpdate("UPDATE user SET id =" + userid +"," + "username = '" +username +"'," + "password = '" + password +"',"
				+ "lastlogin =" + lastlogin + "," + "deleteflg =" + deleteflg + "," + "name = '" + name + " WHERE id =" + id);
	}

	public void delete(int id) throws SQLException {

		//DELETE
		int rset = stmt.executeUpdate("DELETE FROM user WHERE id = "+ id);
	}

	public List<UserEntity> readwhere(String wherestatement, String orderby, String ascdsc) throws SQLException {

		List<UserEntity> listuserEntity = new ArrayList<>();

		//SELECT
		ResultSet rset = stmt.executeQuery("SELECT * FROM user WHERE " + wherestatement + " ORDER BY " + orderby +" " + ascdsc);
		while(rset.next()) {
			UserEntity userEntity = new UserEntity();
			userEntity.setId(rset.getInt("id"));
			userEntity.setUsername(rset.getString("username"));
			userEntity.setPassword(rset.getString("password"));
			userEntity.setLastlogin(rset.getDate("lastlogin"));
			userEntity.setDeleteflg(rset.getBoolean("deleteflg"));
			userEntity.setName(rset.getString("name"));
			listuserEntity.add(userEntity);
		}

		rset.close();

	   return listuserEntity;
    }

}