#1 JDBCTemplate
package common;
import java.sql.*;
import java.util.Properties;
/* NAME : JDBCTemplate
* DESC : DB를 읽어오고 반환하는 등 기능을 수행하는 Utility 클래스
* DB 연결 / DB 종료
*/
public class JDBCTemplate {
public static Connection getConnection() {
// DB 연결
// 위임하면 안됨 try catch로 할 것
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
String id = "user";
String pw = "password";
Properties pro = new Properties();
pro.put(id, "bigdata");
pro.put(pw, "admin1234");
Connection conn = null;
// 위임하면 안됨
try {
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", pro);
conn.setAutoCommit(false); // AutoCommit 해제
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
// DB 종료
public static void Close(Connection con) {
// DB와 Connect 되었는지 여부를 Return
if (isConnection(con)) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static boolean isConnection(Connection conn) {
boolean valid = true;
try {
if (conn == null || conn.isClosed()) {
valid = false;
}
} catch (SQLException e) {
valid = true;
e.printStackTrace();
}
return valid;
// Statement Close
}
public static void Close(Statement stmt) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// ResultSet Close
public static void Close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void Commit(Connection conn) {
if (isConnection(conn)) {
try {
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 트렌젝션처리 (rollback)
public static void Rollback(Connection conn) {
if (isConnection(conn)) {
try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
#2
A.sql
create table userinfo(
idx varchar2(10) primary key,
sname varchar2(10) not null,
age number,
birth date);
create sequence idx_seq
increment by 1
start with 1
nocycle nocache;
insert into userinfo(idx, sname, age, birth) values(idx_seq.nextval, '강호동', 20, '1997-05-20');
insert into userinfo(idx, sname, age, birth) values(idx_seq.nextval, '유재석', 21, '1980-04-01');
insert into userinfo(idx, sname, age, birth) values(idx_seq.nextval, '이경규', 19, '1988-07-10');
select * from userinfo;
#3 UserInfoBiz
package play.biz;
import java.sql.*;
import java.util.*;
import play.dao.UserInfoDAO;
import play.entity.*;
import static common.JDBCTemplate.*;
//Controller <===== biz =====> DAO
public class UserInfoBiz {
public ArrayList<UserInfoEntity> getAll_UserInfo() {
Connection con = getConnection();
ArrayList<UserInfoEntity> all = new UserInfoDAO(con).getAll_UserInfo();
Close(con);
return all;
}
public int getDelete_UserInfo(String idx) {
Connection con = getConnection();
int all = new UserInfoDAO(con).getDelete_UserInfo(idx);
Close(con);
return all;
}
public int getInsert_UserInfo(UserInfoEntity u) {
Connection con = getConnection();
int all = new UserInfoDAO(con).getInsert_UserInfo(u);
Close(con);
return all;
}
public int getUpdate_UserInfo(UserInfoEntity u) {
Connection con = getConnection();
int all = new UserInfoDAO(con).getUpdate_UserInfo(u);
Close(con);
return all;
}
public UserInfoEntity getFind_UserInfo(String sname) {
Connection conn = getConnection();
UserInfoEntity res = new UserInfoDAO(conn).getFind_UserInfo(sname);
Close(conn);
return res;
}
}
#4 UserInfo
package play.dao;
//insert into userinfo(idx, name, age, birth)
// values (idx_seq.nextval, '강호동', 20, '1997-05-20');
public interface UserInfo {
String UI_SELECT = "SELECT * FROM USERINFO ORDER BY 1";
String UI_INSERT = "INSERT INTO USERINFO(IDX, SNAME, AGE, BIRTH) VALUES(idx_seq.nextval,?,?,?)";
String UI_DELETE = "DELETE FROM USERINFO WHERE IDX = ?";
// IDX를 찾아 나이를 변경
String UI_UPDATE = "UPDATE USERINFO SET AGE = ? WHERE IDX=?";
// 이름으로 레코드를 리턴
String UI_FIND = "SELECT * FROM USERINFO WHERE SNAME = ?";
//물음표는 대용량 Transaction이 자동으로 이루어짐
}
#5 UserInfoDAO
package play.dao;
//CRUD + Commit + Rollback + biz값을 받아 결과를 biz로 리턴.
import java.sql.*;
import java.util.*;
import play.entity.*;
import static common.JDBCTemplate.*;
public class UserInfoDAO implements UserInfo {
private Connection con;
public UserInfoDAO(Connection con) { // biz 호출
this.con = con;
}
// Insert
// String UI_INSERT = "INSERT INTO USERINFO(IDX, SNAME, AGE, BIRTH)
// VALUES(idx_seq.nextval,?,?,?)";
public int getInsert_UserInfo(UserInfoEntity user_info) {
PreparedStatement pstm = null;
int res = 0;
try {
pstm = con.prepareStatement(UI_INSERT); // sql쿼리로 인식
pstm.setString(1, user_info.getSname());
pstm.setInt(2, user_info.getAge());
pstm.setString(3, user_info.getDate());
res = pstm.executeUpdate();
if (res > 0) {
Commit(con);
} else {
Rollback(con);
} // 왜 이런 코드를 하면 안되는가 : 오라클은 갱신하지 않으면 음수 = 자바에서는 Exception
} catch (SQLException e) {
e.printStackTrace();
Rollback(con);
} finally {
Close(pstm);
}
return res;
}
// Delete
// String UI_DELETE = "DELETE FROM USERINFO WHERE IDX = ?";
public int getDelete_UserInfo(String idx) {
PreparedStatement pstm = null;
int res = 0;
try {
pstm = con.prepareStatement(UI_DELETE);
pstm.setString(1, idx);
res = pstm.executeUpdate();
if (res > 0) {
Commit(con);
}
} catch (SQLException e) {
e.printStackTrace();
Rollback(con);
} finally {
Close(pstm);
}
return res;
}
// Update
// String UI_UPDATE = "UPDATE USERINFO SET AGE = ? WHERE IDX=?";
public int getUpdate_UserInfo(UserInfoEntity user_info) {
PreparedStatement pstm = null;
int res = 0;
try {
pstm = con.prepareStatement(UI_UPDATE);
pstm.setInt(1, user_info.getAge());
pstm.setString(2, user_info.getIdx());
res = pstm.executeUpdate();
if (res > 0) {
Commit(con);
}
} catch (Exception e) {
e.printStackTrace();
Rollback(con);
} finally {
Close(pstm);
}
return res;
}
// SELECT
// String UI_SELECT = "SELECT * FROM USERINFO";
public ArrayList<UserInfoEntity> getAll_UserInfo() {
ArrayList<UserInfoEntity> all = new ArrayList<>();
UserInfoEntity vo = null;
Statement stmt = null;
ResultSet rs = null;
try {
stmt = con.createStatement();
rs = stmt.executeQuery(UI_SELECT);
while (rs.next()) {
// vo = new UserInfoEntity(rs.getString(1), rs.getString(2), rs.getInt(3),
// rs.getString(4));
vo = new UserInfoEntity();
vo.setAge(rs.getInt(3));
vo.setDate(rs.getString("birth"));
vo.setIdx(rs.getString("idx"));
vo.setSname(rs.getString("sname"));
all.add(vo);
}
} catch (SQLException s) {
s.printStackTrace();
} finally {
Close(rs);
Close(stmt);
}
return all;
}
public UserInfoEntity getFind_UserInfo(String sname) {
PreparedStatement pstm = null;
UserInfoEntity vo = null;
ResultSet rs = null;
try {
pstm = con.prepareStatement(UI_FIND);
pstm.setString(1, sname);
rs = pstm.executeQuery();
if (rs.next()) {
vo = new UserInfoEntity(rs.getString(1), rs.getString("sname"), rs.getInt(3), rs.getString(4));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
Close(rs);
Close(pstm);
}
return vo;
}
}
#6 UserInfoEntity
package play.entity;
public class UserInfoEntity {
private String idx;
private String sname;
private int age;
private String date;
public UserInfoEntity() {
super();
}
public UserInfoEntity(String idx, String sname, int age, String date) {
super();
this.idx = idx;
this.sname = sname;
this.age = age;
this.date = date;
}
public String getIdx() {
return idx;
}
public void setIdx(String idx) {
this.idx = idx;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
}
#7 UserInfoView
package play.view;
import play.entity.*;
import java.util.ArrayList;
import java.util.Scanner;
import play.biz.*;
public class UserInfoView {
Scanner sc = new Scanner(System.in);
public int menu() /* 화면 설계 _VIEW */
{
StringBuffer sb = new StringBuffer();
sb.append("1. 전체 ");
sb.append("2. 추가 ");
sb.append("3. 삭제 ");
sb.append("4. 수정 ");
sb.append("5. 검색 ");
sb.append("6. 종료 ");
sb.append("선택(1-6) : ");
System.out.print(sb.toString());
int n = sc.nextInt();
return n;
}
public void process(int n) /* 결과값을 화면에 출력 VIEW + Controller */
{
String sname = null;
int age = 0;
String birth;
int k = 0;
UserInfoBiz biz = new UserInfoBiz(); // UserInfoBiz는 non-static이기 때문에 이렇게 호출해서 case 1,2
// 3,4,5,6에 다 써준다.
UserInfoEntity entity = new UserInfoEntity();
switch (n) {
case 1:
System.out.println(" 전체 출력 ");
ArrayList<UserInfoEntity> all = biz.getAll_UserInfo();
for (UserInfoEntity r : all) {
System.out.printf("%10s %10s %5d %10s \n", r.getIdx(), r.getSname(), r.getAge(), r.getDate());
}
break;
case 2:
// 이름 나이 생일 추가
entity = new UserInfoEntity();
System.out.println(" 입력화면 ");
System.out.println(" 입력할 나이는? ");
age = sc.nextInt();
entity.setAge(age);
System.out.println("입력할 이름은?");
sname = sc.next();
entity.setSname(sname);
System.out.println("입력할 생일은 YYYY/MM/DD?");
birth = sc.next();
entity.setDate(birth);
int res = biz.getInsert_UserInfo(entity);
if (res > 0) {
System.out.println("입력성공했어!");
}
entity = null;
break;
case 3:
System.out.print(" 삭제할 번호는? ");
String idx = sc.next();
int r = biz.getDelete_UserInfo(idx.trim());
if (r > 0) {
System.out.println("삭제 되었어");
}
break;
case 4:
// 이경규의 나이를 20살로 바꿔보기
System.out.print(" 수정할 번호는? ");
idx = sc.next();
System.out.print(" 몇 살로 바꿔볼까? ");
age = sc.nextInt();
entity.setIdx(idx);
entity.setAge(age);
r = biz.getUpdate_UserInfo(entity);
if (r > 0) {
System.out.println("수정되었어!");
}
entity = null;
break;
case 5:
System.out.print("검색할 이름 : ");
sname = sc.next();
// 이름 검색
entity = biz.getFind_UserInfo(sname);
System.out.println("검색한 레코드");
System.out.printf("%10s %10s %5d %10s \n", entity.getIdx(), entity.getSname(), entity.getAge(),
entity.getDate());
break;
case 6:
System.out.println("** 프로그램을 종료합니다 **");
System.exit(0);
}
}
public static void main(String[] args) {
UserInfoView uiv = new UserInfoView();
while (true) {
int n = uiv.menu();
uiv.process(n); /* 전체, 추가, 삭제, 수정, 조회, 메소드 구현 */
}
}
}
댓글