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;
    }

}

Recommended Posts

Implementation of DBlayer in Java (RDB, MySQL)
Implementation of gzip in java
Implementation of tri-tree in Java
Implementation of like function in Java
Interpreter implementation in Java
Boyer-Moore implementation in Java
Heapsort implementation (in java)
[Java] Implementation of Faistel Network
Implementation of HashMap in kotlin
Implementation of ls command in Ruby
Implementation of asynchronous processing in Tomcat
List of members added in Java 9
List of types added in Java 9
Let's create a TODO application in Java 4 Implementation of posting function
Let's create a TODO application in Java 6 Implementation of search function
Let's create a TODO application in Java 8 Implementation of editing function
Mechanism and characteristics of Collection implementation class often used in Java
[Java] I participated in ABC-188 of Atcorder.
Get the result of POST in Java
Implementation of multi-tenant asynchronous processing in Tomcat
Japanese setting of mysql in Docker container
The story of writing Java in Emacs
Role of JSP in Web application [Java]
Discrimination of Enums in Java 7 and above
This and that of the implementation of date judgment within the period in Java
Comparison of thread implementation methods in Java and lambda expression description method
The story of low-level string comparison in Java
[Java] Handling of JavaBeans in the method chain
The story of making ordinary Othello in Java
About the idea of anonymous classes in Java
Do you need a memory-aware implementation of Java?
Partization in Java
Implementation of digit grouping in flea market apps
The story of learning Java in the first programming
Measure the size of a folder in Java
SKStoreReviewController implementation memo in Swift UI of iOS14
Procedure to change lower_case_table_names = 1 in MySQL 8.0 of CentOS 8.3
Changes in Java 11
[Java] Use of final in local variable declaration
Feel the passage of time even in Java
Rock-paper-scissors in Java
[Rails] Implementation of retweet function in SNS application
[JQuery] Implementation procedure of AutoComplete function [Java / Spring]
[Java / Spring Boot] Spring security ④ --Implementation of login process
Implementation of GKAccessPoint
[Rails] Implementation of "notify notification in some way"
Basics of threads and Callable in Java [Beginner]
[Java] Where is the implementation class of annotation that exists in Bean Validation?
[Java / Spring Boot] Spring security ⑤ --Implementation of logout processing
A quick review of Java learned in class
[Java] Is it unnecessary to check "identity" in the implementation of the equals () method?
Stumble in MySQL 5.5-> 5.7
Method name of method chain in Java Builder + α
Pi in Java
Import files of the same hierarchy in Java
[Java] Overview of Java
Why use setters/getters instead of public/private in Java
FizzBuzz in Java
Sample code to get the values of major SQL types in Java + MySQL 8.0
Review and implementation of CSV library for loading large amounts of data into MySQL (Java)
[Java] Implementation method memo to set WS-Security Username Token in SOAP Stub of axis2