##DAO DTO 패턴과 Connection Pool을 이용
DTO(Database Transfer Object) - Model
package exam05;
public class PoolDTO {
private int deptno;
private String dname;
private String loc;
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public void setDname(String dname) {
this.dname = dname;
}
public void setLoc(String loc) {
this.loc = loc;
}
public int getDeptno() {
return deptno;
}
public String getDname() {
return dname;
}
public String getLoc() {
return loc;
}
}
main - View
package exam05;
import java.util.ArrayList;
public class PoolTest{
public static void main( String [] args ) {
PoolDAO dao = new PoolDAO();
ArrayList<PoolDTO> list = dao.select();
for( PoolDTO dto : list ){
int deptno = dto.getDeptno();
String dname = dto.getDname();
String loc = dto.getLoc();
System.out.println(deptno +" " +dname + " ");
}
}
}
DAO(Database Access Object) - Controller
package exam05;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.commons.dbcp2.BasicDataSource;
public class PoolDAO{
BasicDataSource ds;
public PoolDAO(){
ds = new BasicDataSource();
ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
ds.setUrl( "jdbc:oracle:thin:@localhost:1521:orcl" );
ds.setUsername( "scott");
ds.setPassword( "tiger");
ds.setInitialSize( 4 ); //미리 4개의 객체를 만들겠다.
}
public ArrayList<PoolDTO> select(){
ArrayList<PoolDTO> list = new ArrayList<PoolDTO>();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
con = ds.getConnection();
String query = "SELECT deptno,dname,loc FROM dept";
pstmt = con.prepareStatement(query);
rs = pstmt.executeQuery();
while(rs.next()){
PoolDTO dto = new PoolDTO();
dto.setDeptno(rs.getInt("deptno"));
dto.setDname(rs.getString("dname"));
dto.setLoc(rs.getString("loc"));
list.add(dto);
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(rs != null)rs.close();
if(pstmt != null )pstmt.close();
if(con != null )con.close();
}catch(SQLException e){ e.printStackTrace(); }
}
return list;
}
}
캐쉬 다이어리
Main
package twotwo;
import java.util.Scanner;
public class CashMain {
public static void main(String[] args) {
CashView ui = new CashView();
ui.intro();
}
}
View
package twotwo;
import java.util.ArrayList;
import java.util.Scanner;
public class CashView {
CashController ctl;
CashView(){
ctl = new CashController();
}
public void intro(){
while(true){
System.out.println("\n");
System.out.println("\t┌─────────────────────────────────────────┐");
System.out.println("\t│ │");
System.out.println("\t│ CashDiary!!! │");
System.out.println("\t│ │");
System.out.println("\t├─────────────────────────────────────────┤");
System.out.println("\t│ │");
System.out.println("\t│ │");
System.out.println("\t│ 1. Login │");
System.out.println("\t│ 2. Signup │");
System.out.println("\t│ 3. Exit │");
System.out.println("\t│ │");
System.out.println("\t└─────────────────────────────────────────┘");
Scanner scan = new Scanner(System.in);
int num = scan.nextInt();
switch(num){
case 1:
this.login();
break;
case 2:
this.register();
break;
case 3:
System.out.println("프로그램 종료");
System.exit(0);
default:
System.out.println("잘못 입력하셨습니다.");
continue;
}
break;
}
}
public void login(){
System.out.println("\t┌─────────────────────────────────────────┐");
System.out.println("\t│ │");
System.out.println("\t│ CashDiary!!! │");
System.out.println("\t│ │");
System.out.println("\t├─────────────────────────────────────────┤");
System.out.println("\t│ │");
System.out.println("\t│ │");
System.out.println("\t│ │");
System.out.println("\t│ Login please │");
System.out.println("\t│ ex) id/password │");
System.out.println("\t│ │");
System.out.println("\t└─────────────────────────────────────────┘");
if(ctl.signin()){
this.mainMenu();
} else {
this.login();
}
}
public void register(){
Scanner scan = new Scanner(System.in);
System.out.print("아이디를 입력해주세요 > ");
String id = scan.nextLine();
System.out.print("비밀번호를 입력해주세요 > ");
String pw = scan.nextLine();
boolean result = ctl.insertResister(id, pw);
if(result){
this.mainMenu();
} else {
this.register();
}
}
public void mainMenu(){
while(true){
System.out.println("\t┌─────────────────────────────────────────┐");
System.out.println("\t│ │");
System.out.println("\t│ CashDiary!!! │");
System.out.println("\t│ │");
System.out.println("\t├─────────────────────────────────────────┤");
System.out.printf("\t│ Welcome %7s !! Money: %6s │\n",ctl.getId(),ctl.getMoney());
System.out.println("\t├─────────────────────────────────────────┤");
System.out.println("\t│ │");
System.out.println("\t│ │");
System.out.println("\t│ 1. Input Data │");
System.out.println("\t│ 2. Search │");
System.out.println("\t│ 3. Modify │");
System.out.println("\t│ 4. Delete │");
System.out.println("\t│ 5. Exit │");
System.out.println("\t│ │");
System.out.println("\t└─────────────────────────────────────────┘");
Scanner scan = new Scanner(System.in);
switch(scan.nextInt()){
case 1:
this.mainInsert();
break;
case 2:
this.mainSearch();
break;
case 3:
ctl.dataUpdate();
break;
case 4:
ctl.dataDelete();
break;
case 5:
System.out.println("프로그램 종료");
System.exit(0);
default:
System.out.println("잘못 입력하셨습니다.");
continue;
}
}
}
public void mainInsert(){
loop:
while(true){
System.out.println("\t┌─────────────────────────────────────────┐");
System.out.println("\t│ │");
System.out.println("\t│ CashDiary!!! │");
System.out.println("\t│ │");
System.out.println("\t├─────────────────────────────────────────┤");
System.out.printf("\t│ Welcome %7s !! Money: %6s │\n",ctl.getId(),ctl.getMoney());
System.out.println("\t├─────────────────────────────────────────┤");
System.out.println("\t│ │");
System.out.println("\t│ │");
System.out.println("\t│ 1. Save Money │");
System.out.println("\t│ 2. Spend Money │");
System.out.println("\t│ 3. Before │");
System.out.println("\t│ 4. Exit │");
System.out.println("\t│ │");
System.out.println("\t└─────────────────────────────────────────┘");
Scanner scan = new Scanner(System.in);
switch(scan.nextInt()){
case 1:
System.out.println("수입 내용을 입력하세요");
System.out.println("날짜/내용/금액");
System.out.println("ex) 20150504/월급/10000");
ctl.dataInput(1);
break;
case 2:
System.out.println("지출 내용을 입력하세요");
System.out.println("날짜/분류/내용/금액");
System.out.println("분류> 식비, 교통비, 기타");
System.out.println("ex) 20150504/식비/죠스바/900");
ctl.dataInput(2);
break;
case 3: break loop;
case 4:
System.out.println("프로그램 종료");
System.exit(0);
default:
System.out.println("잘못 입력하셨습니다.");
continue;
}
}
}
public void mainSearch(){
loop:
while(true){
System.out.println("\t┌─────────────────────────────────────────┐");
System.out.println("\t│ │");
System.out.println("\t│ CashDiary!!! │");
System.out.println("\t│ │");
System.out.println("\t├─────────────────────────────────────────┤");
System.out.printf("\t│ Welcome %7s !! Money: %6s │\n",ctl.getId(),ctl.getMoney());
System.out.println("\t├─────────────────────────────────────────┤");
System.out.println("\t│ │");
System.out.println("\t│ │");
System.out.println("\t│ 1. Input Period │");
System.out.println("\t│ 2. Day Data │");
System.out.println("\t│ 3. Week Data │");
System.out.println("\t│ 4. Month Data │");
System.out.println("\t│ 5. Before │");
System.out.println("\t│ 6. Exit │");
System.out.println("\t│ │");
System.out.println("\t└─────────────────────────────────────────┘");
Scanner scan = new Scanner(System.in);
int num = scan.nextInt();
switch(num){
case 1:
System.out.println("기간을 입력해주세요");
System.out.println("ex) 20150502/20150602");
ctl.dataSearch(num);
break;
case 2:case 3:case 4:
ctl.dataSearch(num);
break;
case 5: break loop;
case 6:
System.out.println("프로그램 종료");
System.exit(0);
default:
System.out.println("잘못 입력하셨습니다.");
continue;
}
}
}
}
Controller
package twotwo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;
import org.apache.commons.dbcp2.BasicDataSource;
public class CashController {
BasicDataSource ds;
private String id;
private int money=0;
public String getId() {
return id;
}
public int getMoney() {
return money;
}
public CashController(){
ds = new BasicDataSource();
ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
ds.setUrl( "jdbc:oracle:thin:@localhost:1521:orcl" );
ds.setUsername( "scott");
ds.setPassword( "tiger");
ds.setInitialSize( 50 ); //미리 4개의 객체를 만들겠다.
this.money = 0;
}
public boolean insertResister(String id, String pw){
Connection con = null;
PreparedStatement pstmt = null;
try{
con = ds.getConnection();
String sql = "insert into user_info values(user_seq.nextval,?,?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
pstmt.setString(2, pw);
pstmt.executeUpdate();
pstmt.close();
String sql2 = String.format("create table cashdiary_%s ("
+ "num number(10) primary key,"
+ "day date,"
+ "category varchar2(10),"
+ "memo varchar2(20),"
+ "money number(10) "
+ ")", id);
pstmt = con.prepareStatement(sql2);
pstmt.executeUpdate(sql2) ;
System.out.println("회원가입 성공");
this.id=id;
return true;
} catch(Exception e) {
e.printStackTrace();
System.out.println("회원가입 실패");
return false;
} finally {
try{
if(pstmt != null )pstmt.close();
if(con != null )con.close();
}catch(SQLException e){ e.printStackTrace(); }
}
}
public boolean signin(){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
con = ds.getConnection();
String sql = "select * from user_info";
String[] result = this.scanData();
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()){
String id = rs.getString("id");
String pw = rs.getString("pw");
if(id.compareTo(result[0]) == 0){
if(pw.compareTo(result[1]) == 0){
System.out.println("로그인 성공!!");
this.id = id;
this.setMoney();
return true;
} else {
System.out.println("비밀번호가 맞지 않습니다.");
return false;
}
}
}
System.out.println("아이디가 존재하지 않습니다.");
return false;
}catch(Exception e){
e.printStackTrace();
return false;
}finally{
try{
if(rs != null)rs.close();
if(pstmt != null )pstmt.close();
if(con != null )con.close();
}catch(SQLException e){ e.printStackTrace(); }
}
}
public void dataInput(int num) {
Connection con = null;
PreparedStatement pstmt = null;
try{
con = ds.getConnection();
String[] result = this.scanData();
String sql = String.format("insert into cashdiary_%s ", id);
int len = result.length;
if(num==1){
if(len != 3){
System.out.println("양식에 맞춰 입력해주세요.");
return;
}
sql += "values(cash_seq.nextval,?,'입금',?,?)";
}else if (num==2){
if(len != 4){
System.out.println("양식에 맞춰 입력해주세요.");
return;
}
sql += "values(cash_seq.nextval,?,?,?,?)";
result[3] = "-"+result[3];
}
int money = Integer.parseInt(result[len-1]); //금액 문자열을 정수형으로 변환
this.money += money;
pstmt = con.prepareStatement(sql);
for(int i=0; i<len;i++){
if(i == len-1){
pstmt.setInt(i+1, money);
} else {
pstmt.setString(i+1, result[i]);
}
}
if(pstmt.executeUpdate() ==1){
System.out.println("입력 성공");
}else{
System.out.println("입력 실패");
};
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(pstmt != null )pstmt.close();
if(con != null )con.close();
}catch(SQLException e){ e.printStackTrace(); }
}
}
public void setMoney(){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
con = ds.getConnection();
String sql = "select sum(money) sum from cashdiary_"+id;
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()){
this.money = rs.getInt("sum");
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(rs != null)rs.close();
if(pstmt != null )pstmt.close();
if(con != null )con.close();
}catch(SQLException e){ e.printStackTrace(); }
}
}
public void dataSearch(int num){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
con = ds.getConnection();
String sql = "select * from cashdiary_"+id+" where day";
if(num == 1 || num == 5){
String[] result = this.scanData();
if(result.length == 1){
sql += String.format("='%s'", result[0]);
}else{
sql += String.format(" between '%s' and '%s'", result[0], result[1]);
}
} else {
switch(num){
case 2:
sql += "=to_char(sysdate, 'yyyymmdd')";
break;
case 3:
sql += " between sysdate-7 and sysdate";
break;
case 4:
sql += " between sysdate-30 and sysdate";
break;
}
}
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
if(num == 5){
System.out.printf("\t번호 날짜\t\t분류\t내용\t금액\n");
while(rs.next()){
System.out.printf("\t[%s] %s\t%s\t%s\t%s\n",rs.getString("num"),rs.getString("day").substring(0, 10),
rs.getString("category"),rs.getString("memo"),rs.getString("money"));
}
} else {
System.out.printf("\t날짜\t\t분류\t내용\t금액\n");
while(rs.next()){
System.out.printf("\t%s\t%s\t%s\t%s\n",rs.getString("day").substring(0, 10),
rs.getString("category"),rs.getString("memo"),rs.getString("money"));
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(rs != null)rs.close();
if(pstmt != null )pstmt.close();
if(con != null )con.close();
}catch(SQLException e){ e.printStackTrace(); }
}
}
public String[] scanData(){
Scanner scan = new Scanner(System.in);
String data = scan.nextLine();
String[] result = data.replaceAll("\\s", "").split("/");
return result;
}
public void dataDelete(){
Connection con = null;
PreparedStatement pstmt = null;
System.out.println("삭제할 날짜를 입력해주세요");
this.dataSearch(5);
try{
con = ds.getConnection();
System.out.println("삭제할 데이터 번호를 입력해주세요");
Scanner scan = new Scanner(System.in);
String sql = "delete from CASHDIARY_"+id+" where num="+scan.nextLine();
pstmt = con.prepareStatement(sql);
System.out.println(sql);
if(pstmt.executeUpdate() ==1){
System.out.println("삭제 성공");
}else{
System.out.println("삭제 실패");
};
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(pstmt != null )pstmt.close();
if(con != null )con.close();
}catch(SQLException e){ e.printStackTrace(); }
}
}
public void dataUpdate(){
Connection con = null;
PreparedStatement pstmt = null;
System.out.println("수정할 날짜를 입력해주세요");
this.dataSearch(5);
try{
con = ds.getConnection();
System.out.println("수정할 데이터 번호를 입력해주세요");
Scanner scan = new Scanner(System.in);
int num = scan.nextInt();
System.out.println("양식에 맞게 수정할 데이터를 입력하세요");
System.out.println("분류/내용/금액");
String[] result = this.scanData();
String sql = "update CASHDIARY_"+id+" set ";
if(!result[0].equals("")){
sql += String.format("category='%s'", result[0]);
}else if(!result[1].equals("")){
sql += String.format("memo='%s'", result[1]);
}else if(!result[2].equals("")){
sql += String.format("money='%s'", result[2]);
}
sql += " where num="+num;
pstmt = con.prepareStatement(sql);
System.out.println(sql);
if(pstmt.executeUpdate() ==1){
System.out.println("수정 성공");
}else{
System.out.println("수정 실패");
};
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(pstmt != null )pstmt.close();
if(con != null )con.close();
}catch(SQLException e){ e.printStackTrace(); }
}
}
}