JavaでDBlayerの実装(RDB、MySQL)

MySQL

DBConstants

まず、DB接続用の定数を定義しよう！！

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

つぎに、データベースに接続するためのクラスをつくろう！！

DBConection

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

public class DBConn {

    //MySQLに接続
    public Connection conn;
    //ステートメントを作成
    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

お次は、Entityクラスをつくろう！！

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

最後に、DAOクラスをつくろう！！
(先につくったDBConnectionクラスを継承させよう)

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

            //結果セットをクローズ
            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"));
        }

        //結果セットをクローズ
        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;
    }

}