package org.joonzis.ex;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class GreenDao {
// 필드
private Connection conn = null;
private PreparedStatement ps = null;
private ResultSet rs = null;
private String sql = "";
// 싱글톤
private GreenDao() {} // 기본 생성자(외부에서 접근할 수 없게 private 처리)
private static GreenDao dao = new GreenDao();
public static GreenDao getInstance() {
return dao;
}
// 1. DBCP설정
private static DataSource ds;
static {
try {
Context context = new InitialContext();
ds = (DataSource)context.lookup("java:comp/env/jdbc/oracle");
//java:comp/env/ : 톰캣(지정되지 않음)
//jdbc/oracle : Resource name(변경가능)을 찾아서 DataSource 객체에 전달
} catch (NamingException e) {
e.printStackTrace();
}
}
public List<GreenVO> getAllList() {
List<GreenVO> list = new ArrayList<>();
try {
//conn = getConnection(); ---이전 커넥션 방식
conn = ds.getConnection(); //생성이 아니라 얻어옴
sql = "select * from green order by idx";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
GreenVO dto = new GreenVO();
dto.setIdx(rs.getInt(1));
dto.setId(rs.getString(2));
dto.setPw(rs.getString(3));
dto.setName(rs.getString(4));
dto.setAge(rs.getInt(5));
dto.setAddr(rs.getString(6));
dto.setReg_date(rs.getDate(7));
list.add(dto);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
return list;
}
public int getInsert(GreenVO dto) {
int result = 0;
try {
conn = ds.getConnection();
String sql = "insert into green" + " values(green_seq.nextval, ?, ?, ?, ?, ?, sysdate)";
ps = conn.prepareStatement(sql);
conn.setAutoCommit(false); // 자동 커밋 방지
ps.setString(1, dto.getId());
ps.setString(2, dto.getPw());
ps.setString(3, dto.getName());
ps.setInt(4, dto.getAge());
ps.setString(5, dto.getAddr());
result = ps.executeUpdate();
if (result > 0) {
conn.commit(); // insert 잘 성공하면 커밋
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
return result;
}
// 데이터 삭제 메소드
// public int getRemove(String id, String pw) {}
public int getRemove(GreenVO dto) {
int result = 0; // try 안 타면 그냥 result 0 return
try {
conn = ds.getConnection();
String sql = "delete from green where id=? and pw=?";
ps = conn.prepareStatement(sql);
conn.setAutoCommit(false); // 자동 커밋 방지
ps.setString(1, dto.getId());
ps.setString(2, dto.getPw());
result = ps.executeUpdate();
if (result > 0) {
conn.commit();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
return result;
}
// 데이터 수정 메소드
public int getUpdate(GreenVO dto) {
int result = 0;
try {
conn = ds.getConnection();
String sql = "update green set pw=?, name=?, age=?, addr=? where idx=?";
ps = conn.prepareStatement(sql);
conn.setAutoCommit(false); // 자동 커밋 방지
ps.setString(1, dto.getPw());
ps.setString(2, dto.getName());
ps.setInt(3, dto.getAge());
ps.setString(4, dto.getAddr());
ps.setInt(5, dto.getIdx());
result = ps.executeUpdate();
if (result > 0) {
conn.commit();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
return result;
}
// 데이터 1개 출력
public GreenVO getSelectOne(String id) {
GreenVO dto = null; // 데이터 즉, rs가 없으면 null
try {
conn = ds.getConnection();
sql = "select * from green where id=?";
ps = conn.prepareStatement(sql);
ps.setString(1, id); // 쿼리 작동하기 전에 id=?에 전달받아온 매개변수 넣어줌
rs = ps.executeQuery(); // 실제 쿼리 작동
// if(rs.next()) : 데이터 있으면 담고 없으면 X
if(rs.next()) { // 행 기준, if문이나 while문 있어야 데이터 보여줌
dto = new GreenVO();
dto.setIdx(rs.getInt("idx"));
dto.setId(rs.getString(2));
dto.setPw(rs.getString(3));
dto.setName(rs.getString(4));
dto.setAge(rs.getInt(5));
dto.setAddr(rs.getString(6));
dto.setReg_date(rs.getDate(7));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
return dto;
}
// 업데이트를 위한 유저 정보 메소드
public GreenVO getUpdateView(GreenVO dto) {
GreenVO returnDto = null;;
try {
conn = ds.getConnection();
sql = "select * from green where id=? and pw=?";
ps = conn.prepareStatement(sql);
ps.setString(1, dto.getId());
ps.setString(2, dto.getPw());
rs = ps.executeQuery(); // 실제 쿼리 작동
if (rs.next()) {
returnDto = new GreenVO();
returnDto.setIdx(rs.getInt(1));
returnDto.setId(rs.getString(2));
returnDto.setPw(rs.getString(3));
returnDto.setName(rs.getString(4));
returnDto.setAge(rs.getInt(5));
returnDto.setAddr(rs.getString(6));
returnDto.setReg_date(rs.getDate(7));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
return returnDto;
}
}
