Programming/Java
[JDBC] Service-DAO-DTO 구축
seandoesdev
2023. 8. 4. 01:26
Service
package com.service;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import com.dao.StudentDAO;
import com.dto.StudentDTO;
public class StudentService {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String userid = "workshop";
String passwd = "workshop";
public StudentService() {
try {
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
public List<StudentDTO> selectAllStudent(){
List<StudentDTO> list = null;
Connection conn = null;
try {
conn = DriverManager.getConnection(url, userid, passwd);
StudentDAO dao = new StudentDAO();
list = dao.selectAllStudent(conn);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public List<StudentDTO> selectByName(String name){
List<StudentDTO> list = null;
Connection conn = null;
try {
conn = DriverManager.getConnection(url, userid, passwd);
StudentDAO dao = new StudentDAO();
list = dao.selectByName(conn, name);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public List<StudentDTO> selectByEntranceDate(HashMap<Integer, String> map){
List<StudentDTO> list = null;
Connection conn = null;
try {
conn = DriverManager.getConnection(url, userid, passwd);
StudentDAO dao = new StudentDAO();
list = dao.selectByEntranceDate(conn, map);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public List<StudentDTO> selectBySearchNo(String searchNo) {
List<StudentDTO> list = null;
Connection conn = null;
try {
conn = DriverManager.getConnection(url, userid, passwd);
StudentDAO dao = new StudentDAO();
list = dao.selectBySearchNo(conn, searchNo);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
}
DAO
package com.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.HashMap;
import java.util.List;
import com.dto.StudentDTO;
public class StudentDAO {
public List<StudentDTO> selectAllStudent(Connection conn) {
List<StudentDTO> list = new ArrayList<>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
String sql = "select student_no 학번, student_name 이름, rpad(substr(student_ssn, 0, 8), 14, '*') 주민번호, substr(student_address, 0, 10)||'...' 주소, to_char(entrance_date, 'yyyy/mm/dd') 입학년도, absence_yn 휴학여부";
sql += " from tb_student order by 1";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
String stuNo = rs.getNString("학번");
String stuName = rs.getNString("이름");
String stuSsn = rs.getNString("주민번호");
String stuAddress = rs.getNString("주소");
String entDate = rs.getNString("입학년도");
String absYn = rs.getNString("휴학여부");
StudentDTO s = new StudentDTO(stuNo, stuName, stuSsn, stuAddress, entDate, absYn);
list.add(s);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public List<StudentDTO> selectByName(Connection conn, String name) {
List<StudentDTO> list = new ArrayList<>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
String sql = "select student_no 학번, student_name 이름, rpad(substr(student_ssn, 0, 8), 14, '*') 주민번호,";
sql += " case when student_address is null then '**주소미상**' else substr(student_address, 0, 10)||'...' end 주소,";
sql += " to_char(entrance_date, 'yyyy/mm/dd') 입학년도, absence_yn 휴학여부";
sql += " from tb_student where student_name like ? order by 1";
pstmt = conn.prepareStatement(sql);
pstmt.setNString(1, "%" + name + "%");
rs = pstmt.executeQuery();
while (rs.next()) {
String stuNo = rs.getNString("학번");
String stuName = rs.getNString("이름");
String stuSsn = rs.getNString("주민번호");
String stuAddress = rs.getNString("주소");
String entDate = rs.getNString("입학년도");
String absYn = rs.getNString("휴학여부");
StudentDTO s = new StudentDTO(stuNo, stuName, stuSsn, stuAddress, entDate, absYn);
list.add(s);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null)
pstmt.close();
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public List<StudentDTO> selectByEntranceDate(Connection conn, HashMap<Integer, String> map) {
List<StudentDTO> list = new ArrayList<>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
String sql = "select student_no 학번, student_name 이름, rpad(substr(student_ssn, 0, 8), 14, '*') 주민번호,";
sql += " case when student_address is null then '**주소미상**' else substr(student_address, 0, 10)||'...' end 주소,";
sql += " to_char(entrance_date, 'yyyy/mm/dd') 입학년도, absence_yn 휴학여부";
sql += " from tb_student where to_char(entrance_date, 'YYYY') between ? and ? order by 1";
pstmt = conn.prepareStatement(sql);
pstmt.setNString(1, map.get(0));
pstmt.setNString(2, map.get(1));
rs = pstmt.executeQuery();
while (rs.next()) {
String stuNo = rs.getNString("학번");
String stuName = rs.getNString("이름");
String stuSsn = rs.getNString("주민번호");
String stuAddress = rs.getNString("주소");
String entDate = rs.getNString("입학년도");
String absYn = rs.getNString("휴학여부");
StudentDTO s = new StudentDTO(stuNo, stuName, stuSsn, stuAddress, entDate, absYn);
list.add(s);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public List<StudentDTO> selectBySearchNo(Connection conn, String searchNo) {
List<StudentDTO> list = new ArrayList<>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
String sql = "select student_no 학번, student_name 이름, rpad(substr(student_ssn, 0, 8), 14, '*') 주민번호,";
sql += " case when student_address is null then '**주소미상**' else substr(student_address, 0, 10)||'...' end 주소,";
sql += " to_char(entrance_date, 'yyyy/mm/dd') 입학년도, absence_yn 휴학여부";
sql += " from tb_student where student_no in ( ?, ?)";
String[] str = searchNo.split(",");
pstmt = conn.prepareStatement(sql);
pstmt.setNString(1, str[0]);
pstmt.setNString(2, str[1]);
rs = pstmt.executeQuery();
while (rs.next()) {
String stuNo = rs.getNString("학번");
String stuName = rs.getNString("이름");
String stuSsn = rs.getNString("주민번호");
String stuAddress = rs.getNString("주소");
String entDate = rs.getNString("입학년도");
String absYn = rs.getNString("휴학여부");
StudentDTO s = new StudentDTO(stuNo, stuName, stuSsn, stuAddress, entDate, absYn);
list.add(s);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
}
DTO
package com.dto;
public class StudentDTO {
private String stuNo;
private String departNo;
private String stuName;
private String stuSsn;
private String stuAddress;
private String entDate;
private String absYn;
private String coachProfessorNo;
public StudentDTO() {
}
public StudentDTO(String stuNo, String stuName, String stuSsn, String stuAddress, String entDate, String absYn) {
this.stuNo = stuNo;
this.stuName = stuName;
this.stuSsn = stuSsn;
this.stuAddress = stuAddress;
this.entDate = entDate;
this.absYn = absYn;
}
public StudentDTO(String stuNo, String departNo, String stuName, String stuSsn, String stuAddress, String entDate,
String absYn, String coachProfessorNo) {
this.stuNo = stuNo;
this.departNo = departNo;
this.stuName = stuName;
this.stuSsn = stuSsn;
this.stuAddress = stuAddress;
this.entDate = entDate;
this.absYn = absYn;
this.coachProfessorNo = coachProfessorNo;
}
@Override
public String toString() {
return "StudentDTO [stuNo=" + stuNo + ", stuName=" + stuName + ", stuSsn=" + stuSsn + ", stuAddress="
+ stuAddress + ", entDate=" + entDate + ", absYn=" + absYn + "]";
}
public String getStuNo() {
return stuNo;
}
public void setStuNo(String stuNo) {
this.stuNo = stuNo;
}
public String getDepartNo() {
return departNo;
}
public void setDepartNo(String departNo) {
this.departNo = departNo;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public String getStuSsn() {
return stuSsn;
}
public void setStuSsn(String stuSsn) {
this.stuSsn = stuSsn;
}
public String getStuAddress() {
return stuAddress;
}
public void setStuAddress(String stuAddress) {
this.stuAddress = stuAddress;
}
public String getEntDate() {
return entDate;
}
public void setEntDate(String entDate) {
this.entDate = entDate;
}
public String getAbsYn() {
return absYn;
}
public void setAbsYn(String absYn) {
this.absYn = absYn;
}
public String getCoachProfessorNo() {
return coachProfessorNo;
}
public void setCoachProfessorNo(String coachProfessorNo) {
this.coachProfessorNo = coachProfessorNo;
}
}
Main
import java.util.HashMap;
import java.util.List;
import java.util.Scanner;
import com.dto.StudentDTO;
import com.service.StudentService;
public class StudentTest {
public static void main(String[] args) {
System.out.println("********************************");
System.out.println("\t [학생 정보 관리 메뉴]");
System.out.println("********************************");
System.out.println("1.전체 학생 목록");
System.out.println("2.학생 이름 검색");
System.out.println("3.학생 입학년도 범위 검색(예> 2000부터 2003년까지)");
System.out.println("4.학생 학번으로 다중 검색");
System.out.println("0.종료");
System.out.println("********************************");
System.out.println("메뉴 입력 => ");
int input = 0;
Scanner scan = new Scanner(System.in);
input = scan.nextInt();
if (input == 1) {
StudentService service = new StudentService();
List<StudentDTO> list = service.selectAllStudent();
System.out.println("==========================================================");
System.out.println("학번\t이름\t주민번호\t주소\t입학년도\t휴학여부");
System.out.println("----------------------------------------------------------");
for (StudentDTO s : list) {
System.out.println(s.getStuNo() + "\t" + s.getStuName() + "\t" + s.getStuSsn() + "\t"
+ s.getStuAddress() + "\t" + s.getEntDate() + "\t" + s.getAbsYn());
}
System.out.println("총 학생 수 : " + list.size() + "명");
} else if (input == 2) {
System.out.println("검색할 학생명을 입력하시오 =>");
String name = scan.next();
StudentService service = new StudentService();
List<StudentDTO> list = service.selectByName(name);
System.out.println("==========================================================");
System.out.println("학번\t이름\t주민번호\t주소\t입학년도\t휴학여부");
System.out.println("----------------------------------------------------------");
for (StudentDTO s : list) {
System.out.println(s.getStuNo() + "\t" + s.getStuName() + "\t" + s.getStuSsn() + "\t"
+ s.getStuAddress() + "\t" + s.getEntDate() + "\t" + s.getAbsYn());
}
System.out.println("총 학생 수 : " + list.size() + "명");
} else if (input == 3) {
HashMap<Integer, String> map = new HashMap<>();
System.out.println("시작 입학년도 입력하시오 =>");
map.put(0, scan.next());
map.put(1, scan.next());
StudentService service = new StudentService();
List<StudentDTO> list = service.selectByEntranceDate(map);
System.out.println("==========================================================");
System.out.println("학번\t이름\t주민번호\t주소\t입학년도\t휴학여부");
System.out.println("----------------------------------------------------------");
for (StudentDTO s : list) {
System.out.println(s.getStuNo() + "\t" + s.getStuName() + "\t" + s.getStuSsn() + "\t"
+ s.getStuAddress() + "\t" + s.getEntDate() + "\t" + s.getAbsYn());
}
System.out.println("총 학생 수 : " + list.size() + "명");
} else if (input == 4) {
System.out.println("검색할 학번을 입력하시오 =>");
String name = scan.next();
StudentService service = new StudentService();
List<StudentDTO> list = service.selectBySearchNo(name);
System.out.println("==========================================================");
System.out.println("학번\t이름\t주민번호\t주소\t입학년도\t휴학여부");
System.out.println("----------------------------------------------------------");
for (StudentDTO s : list) {
System.out.println(s.getStuNo() + "\t" + s.getStuName() + "\t" + s.getStuSsn() + "\t"
+ s.getStuAddress() + "\t" + s.getEntDate() + "\t" + s.getAbsYn());
}
System.out.println("총 학생 수 : " + list.size() + "명");
} else if (input == 5) {
} else if (input == 6) {
} else if (input == 7) {
} else {
System.out.println("Exit..");
System.exit(0);
}
}
}