JDBC 2
2023. 12. 19. 10:30ㆍDaily Codig Reminder
import java.sql.SQLException;
import java.util.ArrayList;
import com.dto.Dept;
import com.exception.RecordNotFoundException;
import com.service.OracleTXService;
public class OralceTXMain {
public static void main(String[] args) {
OracleTXService service= new OracleTXService();
//4번 클릭
//service.delete(99);
//3번 클릭
Dept xx2 =new Dept(99, "경제", "서울");
try {
service.update(xx2);
} catch (RecordNotFoundException e1) {
System.out.println(e1.getMessage());
}
//2번클릭
int deptno = 99;
String dname="개발";
String loc ="제주";
Dept xx =new Dept(deptno, dname, loc);
//service.insert(xx);
//1번클릭
try {
ArrayList<Dept> list= service.select();
for (Dept dept : list) {
System.out.println(dept);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}import java.sql.SQLException;
import java.util.ArrayList;
import com.dto.Dept;
import com.exception.RecordNotFoundException;
import com.service.OracleTXService;
public class OralceTXMain2 {
public static void main(String[] args) {
OracleTXService service= new OracleTXService();
//99번부서 insert , 88번부서 delete
service.insertDelete(new Dept(99,"개발","제주"), 88);
//service.insert(new Dept(99,"개발","제주"));
//1번클릭
try {
ArrayList<Dept> list= service.select();
for (Dept dept : list) {
System.out.println(dept);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}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 com.dto.Dept;
import com.exception.RecordNotFoundException;
public class OracleTxDAO {
// 1. 4가지 정보
public OracleTxDAO() {
}
public ArrayList<Dept> select(Connection con) throws SQLException {
ArrayList<Dept> list = new ArrayList<Dept>();
PreparedStatement pstmt= null;
ResultSet rs= null;
String sql="select deptno x, dname, loc from dept";
pstmt= con.prepareStatement(sql);
rs= pstmt.executeQuery();
while (rs.next()) {
int deptno = rs.getInt("x");
String dname = rs.getString(2);
String loc = rs.getString("loc");
//System.out.println(deptno + "\t" + dname + "\t" + loc);
Dept dto = new Dept(deptno, dname, loc);
list.add(dto);
}
if(rs!= null) rs.close();
if(pstmt!= null) pstmt.close();
return list;
}
public void insert(Connection con, Dept xx) {
PreparedStatement pstmt= null;
try {
String sql="insert into dept(deptno, dname, loc)"
+"values (?,?,?)";
pstmt= con.prepareStatement(sql);
pstmt.setInt(1, xx.getDeptno());
pstmt.setString(2, xx.getDname());
pstmt.setString(3, xx.getLoc());
int num= pstmt.executeUpdate();
System.out.println("insert 실행 결과"+ num);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
// 7. 자원반납
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void update(Connection con, Dept xx) throws RecordNotFoundException {
PreparedStatement pstmt= null;
try {
String sql="update dept set dname=? , loc=? where deptno=?";
pstmt= con.prepareStatement(sql);
pstmt.setString(1, xx.getDname());
pstmt.setString(2, xx.getLoc());
pstmt.setInt(3, xx.getDeptno());
int num= pstmt.executeUpdate();
System.out.println("실행 결과"+ num);
if(num ==0 ) {
throw new RecordNotFoundException(xx.getDeptno()+"레코드가 없음");
}
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
// 7. 자원반납
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void delete(Connection con, int i) {
PreparedStatement pstmt= null;
try {
//String sql="delete from dept where deptno= ?";
String sql="dele from dept where deptno= ?"; //에러문장을 발생시킴
pstmt= con.prepareStatement(sql);
pstmt.setInt(1, i);
int num= pstmt.executeUpdate();
System.out.println("실행 결과"+ num);
}catch (SQLException e) {
// TODO Auto-generated catch block
try {
System.out.println("in dao.delete Rollback==================");
con.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}finally {
try {
// 7. 자원반납
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}package com.service;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import com.dao.OracleTxDAO;
import com.dto.Dept;
import com.exception.RecordNotFoundException;
public class OracleTXService {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String userid = "scott";
String passwd = "tiger";
OracleTxDAO dao;
public OracleTXService() {
super();
// TODO Auto-generated constructor stub
dao= new OracleTxDAO();
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//트랜젝션처리
public void insertDelete(Dept dept, int i ) {
Connection con= null;
ArrayList<Dept> list= null;
try {//con닫기
con= DriverManager.getConnection(url, userid, passwd);
con.setAutoCommit(false); //1. 자동 반영을 false로 변경, 수동으로 처리 (commit, rollback )
dao.insert(con, dept);//error 없어서 insert 되게 함
dao.delete(con, i); //delete시 exception 강제 발생
con.commit();//커밋
}catch(SQLException e) {
if(con !=null)
try {
System.out.println("service RollBack====================");
con.rollback();//롤백
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
System.out.println(e.getMessage());
}finally {
if(con!= null)
try {
con.setAutoCommit(true);// 다시 자동반영으로 변경
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public ArrayList<Dept> select() throws SQLException{
Connection con= null;
ArrayList<Dept> list= null;
try {//con닫기
con= DriverManager.getConnection(url, userid, passwd);
list = dao.select(con);
}finally {
if(con!= null) con.close();
}
return list;
}
public void insert(Dept xx) {
Connection con= null;
try {//con닫기
con= DriverManager.getConnection(url, userid, passwd);
dao.insert(con, xx);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(con!= null) con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void update(Dept xx) throws RecordNotFoundException {
Connection con= null;
try {//con닫기
con= DriverManager.getConnection(url, userid, passwd);
dao.update(con, xx);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(con!= null) con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void delete(int i) {
Connection con= null;
try {//con닫기
con= DriverManager.getConnection(url, userid, passwd);
dao.delete(con, i);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(con!= null) con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
jdbc 자동 커밋 → auto commit ⇒ connection이이 관리
tx 명시적 처리 순서
- con.setAutoCommit(false)
- dml sql executeUpdate
- 정상 실행 con.commit()
- 문제 발생 con.rollback();
- con.setAutoCommit(true)
import java.util.ArrayList;
import java.util.HashMap;
import com.biz.EmpBiz;
import com.entity.EmpDTO;
import com.exception.DataNotFoundException;
public class EmpTest {
public static void main(String[] args) throws DataNotFoundException {
EmpBiz empBiz = new EmpBiz();//생성자 작성 -
// empBiz.empInsert(new EmpDTO(30, "kim", "sales", 7902, null, 0.0, 0.1, 10));
// empBiz.empUpdate(new EmpDTO(30, "kim", "AAAAAA", 7902, null, 0.0, 0.1, 10));
//dao.empUpdate : empno를 기준으로 job, sal을 update 후 num출력
//update가 안된 경우 DataNotFoundEx발생
//EmpDTO dto= empBiz.selectDetailEmp("7369");
//검색 조건 job="SALES" , deptno=10 empBiz.select("SALES", 10);
HashMap<String, String> map = new HashMap<>();
map.put("job", "SALESMAN");
map.put("deptno", "30");
ArrayList<EmpDTO> list = empBiz.selectDetailEmp2(map);
//
//System.out.println(empBiz.selectDetailEmp("30"));
// ArrayList<EmpDTO> list = empBiz.selectAllEmp();
System.out.println(list);
for (EmpDTO dto : list) {
System.out.println(dto);
}
System.out.println("==============");
//System.out.println(empBiz.selectDetailEmp("30"));
//empBiz.empDelete("1");
}//end main
}//end classpackage com.biz;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import com.common.util.JdbcTemplate;
import com.dao.EmpDAO;
import com.entity.EmpDTO;
import com.exception.DataNotFoundException;
public class EmpBiz {
EmpDAO dao;
JdbcTemplate template;
public EmpBiz() {
dao = new EmpDAO();
template= new JdbcTemplate();
}
public ArrayList<EmpDTO> selectAllEmp(){
Connection con = JdbcTemplate.getConnection();
ArrayList<EmpDTO> list = dao.selectAllEmp(con);
JdbcTemplate.close(con);
return list;
}
public EmpDTO selectDetailEmp( String eno) throws DataNotFoundException{//////////사용자 정의 ex추가
Connection con = JdbcTemplate.getConnection();
EmpDTO dto = dao.selectDetailEmp(con, eno);
JdbcTemplate.close(con);
return dto;
}
public void empUpdate( EmpDTO empDTO) throws DataNotFoundException{
Connection con = JdbcTemplate.getConnection();
dao.empUpdate(con, empDTO);
JdbcTemplate.commit(con);
JdbcTemplate.close(con);
}
public void empDelete( String empno) throws DataNotFoundException{
Connection con = JdbcTemplate.getConnection();
dao.empDelete(con, empno);
JdbcTemplate.commit(con);
JdbcTemplate.close(con);
}
public void empInsert( EmpDTO empDTO) throws DataNotFoundException{
Connection con = JdbcTemplate.getConnection();
dao.insert(con, empDTO);//num 값 출력
JdbcTemplate.commit(con);
JdbcTemplate.close(con);
}
public ArrayList<EmpDTO> selectDetailEmp2(HashMap<String, String> map) {
Connection con = JdbcTemplate.getConnection();
ArrayList<EmpDTO> list= dao.selectDetailEmp2(con, map);
//컨넥션 닫기
return list;
}
}package com.common.util;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import java.util.Scanner;
public class CommonUtil {
public static String getCurrency(int data){
NumberFormat format = NumberFormat.getCurrencyInstance(Locale.KOREA);
return format.format(data);
}
public static String getUserInput() {
Scanner s = new Scanner(System.in);
return s.nextLine();
}
public static String getDate(Date d){
String formatData = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
formatData = sdf.format(d.getTime());
return formatData;
}
public static Date getDate(String cal){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date d = null;
try{
d = sdf.parse(cal);
}catch(ParseException e){
e.printStackTrace();
}
return d;
}
}
package com.common.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcTemplate {
public static String driver="oracle.jdbc.driver.OracleDriver";
public static String url = "jdbc:oracle:thin:@localhost:1521:xe";
public static String userid = "scott";
public static String passwd = "tiger";
public JdbcTemplate() {
try {
Class.forName(driver);
System.out.println("JdbcTemplate 드라이버 로딩 성공======");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* Connection을 연결한 후 멤버 attribute 인 conn 에 Connection 객체를 세팅한 후 리턴한다.
*
* @return Connection
*/
public static Connection getConnection() {//connection연결, 일반 멤버변수 사용 못함
Connection conn = null;
try {
conn = DriverManager.getConnection(url,userid,passwd);
conn.setAutoCommit(false);////////
} catch (Exception e) {
System.out.println("[JdbcTemplate.getConnection] : " + e.getMessage());
e.printStackTrace();
}
return conn;//db연결, autocommit(false)- 반드시 commit, rollback 실행해야 함.
}
/**
* DB와 Connect되었는지 여부를 Return 한다. *
* @return DB와 Connect 되었는지 여부.
*/
public static boolean isConnected(Connection conn) {
boolean validConnection = true;
try {
if (conn == null || conn.isClosed())
validConnection = false;
} catch (SQLException e) {
validConnection = false;
e.printStackTrace();
}
return validConnection;
}
/**
* Connection 객체를 시스템에 반환한다.
*/
public static void close(Connection conn) {
if (isConnected(conn)) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Statement를 Close 한다.
*
* @param stmt
* Statement 객체.
*/
public static void close(Statement stmt) {
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* ResultSet을 Close 한다.
*
* @param result
* ResultSet 객체.
*/
public static void close(ResultSet rset) {
try {
if (rset != null)
rset.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 지금까지의 트랜잭션을 Commit 처리한다.
*/
public static void commit(Connection conn) {
try {
if (isConnected(conn)) {
conn.commit();
System.out.println("[JdbcTemplate.commit] : DB Successfully Committed!");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 지금까지의 트랜잭션을 Rollback 처한다.
*/
public static void rollback(Connection conn) {
try {
if (isConnected(conn)) {
conn.rollback();
System.out.println("[JdbcTemplate.rollback] : DB Successfully Rollbacked!");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}package com.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import com.common.util.JdbcTemplate;
import com.entity.EmpDTO;
import com.exception.DataNotFoundException;
public class EmpDAO {
public ArrayList<EmpDTO> selectAllEmp(Connection con){
ArrayList<EmpDTO> list = new ArrayList<>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
String sql = "select empno,ename,job,mgr, to_char(hiredate,'YYYY-MM-DD') hiredate,sal,comm,deptno from emp "
+ "order by empno desc";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()){
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
String job = rs.getString("job");
int mgr = rs.getInt("mgr");
String hiredate = rs.getString("hiredate");
double sal = rs.getDouble("sal");
double comm = rs.getDouble("comm");
int deptno = rs.getInt("deptno");
EmpDTO notice = new EmpDTO(empno, ename, job, mgr, hiredate, sal, comm, deptno);
list.add(notice);
}
}catch(SQLException e){
e.printStackTrace();
}finally {
JdbcTemplate.close(rs);/////////////////////////////
JdbcTemplate.close(pstmt);//////////////////////////
}
return list;
}
//사원번호로 찾기
public EmpDTO selectDetailEmp(Connection con, String eno) throws DataNotFoundException{////ex throws
EmpDTO result = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
String sql = "select empno,ename,job,mgr, to_char(hiredate,'YYYY-MM-DD')hiredate,sal,comm,deptno "
+ "from emp where empno = ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, Integer.parseInt(eno));/////형변환
rs = pstmt.executeQuery();
if(rs.next()){///if사용
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
String job = rs.getString("job");
int mgr = rs.getInt("mgr");
String hiredate = rs.getString("hiredate");
double sal = rs.getDouble("sal");
double comm = rs.getDouble("comm");
int deptno = rs.getInt("deptno");
result = new EmpDTO(empno, ename, job, mgr, hiredate, sal, comm, deptno);
}else{//rs.next() false인경우
throw new DataNotFoundException(eno+"에 해당하는 사원정보가 없습니다. 확인후 다시 조회하세요.");
}
}catch(SQLException e){
e.printStackTrace();
}finally {
JdbcTemplate.close(rs);
JdbcTemplate.close(pstmt);
}
return result;//dto리턴
}
//수정하기
public void empUpdate(Connection con, EmpDTO empDTO) throws DataNotFoundException{
PreparedStatement pstmt = null;
try{
String sql = "update emp set job = ?, sal = ? where empno = ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(3, empDTO.getEmpno());
pstmt.setString(1, empDTO.getJob());
pstmt.setDouble(2, empDTO.getSal());
int n = pstmt.executeUpdate();
System.out.println("update 갯수 = "+ n);
if(n==0) throw new DataNotFoundException(empDTO.getEmpno()+"에 해당하는 사원정보가 없습니다. 확인후 다시 조회하세요.");
}catch(SQLException e){
e.printStackTrace();
}finally {
JdbcTemplate.close(pstmt);
}
}
public void empDelete(Connection con, String empno) throws DataNotFoundException{
PreparedStatement pstmt = null;
try{
String sql = "delete from emp where empno = ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, Integer.parseInt(empno));
int n = pstmt.executeUpdate();
if(n==0){
throw new DataNotFoundException(empno+"에 해당하는 사원정보가 없습니다. 확인후 다시 조회하세요.");
}
}catch(SQLException e){
e.printStackTrace();
}finally {
JdbcTemplate.close(pstmt);
}
}
public void insert(Connection con, EmpDTO empDTO) {
// TODO Auto-generated method stub
PreparedStatement pstmt = null;
try{
String sql = "insert into emp values (?,?,?,?,sysdate,?,?,?)";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, empDTO.getEmpno());
pstmt.setString(2, empDTO.getEname());
pstmt.setString(3, empDTO.getJob());
pstmt.setInt(4, empDTO.getMgr());
pstmt.setDouble(5, empDTO.getSal());
pstmt.setDouble(6, empDTO.getComm());
pstmt.setInt(7, empDTO.getDeptno());
int n = pstmt.executeUpdate();
System.out.println("insert : 된 갯수 "+ n);
}catch(SQLException e){
e.printStackTrace();
}finally {
JdbcTemplate.close(pstmt);
}
}
public ArrayList<EmpDTO> selectDetailEmp2(Connection con, HashMap<String, String> map) {
String job = map.get("job");
String no=map.get("deptno");
int deptno= Integer.parseInt(no);
System.out.println("dao map : " + job+ "\t"+ deptno);
ArrayList<EmpDTO> list = new ArrayList<>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
String sql = "select empno,ename,job,mgr, to_char(hiredate,'YYYY-MM-DD')hiredate,sal,comm,deptno "
+ "from emp where job = ? and deptno=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, job);
pstmt.setInt(2, deptno);
rs = pstmt.executeQuery();
while(rs.next()){
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
String ejob = rs.getString("job");
int mgr = rs.getInt("mgr");
String hiredate = rs.getString("hiredate");
double sal = rs.getDouble("sal");
double comm = rs.getDouble("comm");
int edeptno = rs.getInt("deptno");
EmpDTO notice = new EmpDTO(empno, ename, ejob, mgr, hiredate, sal, comm, edeptno);
list.add(notice);
}
}catch(SQLException e){
e.printStackTrace();
}finally {
JdbcTemplate.close(rs);/////////////////////////////
JdbcTemplate.close(pstmt);//////////////////////////
}
return list;
}
}package com.entity;
public class EmpDTO {
//모델클래스 작성시 멤버변수명과 테이블의 컬럼명과 일치
private int empno;
private String ename;
private String job;
private int mgr;//관리자번호
private String hiredate;//문자열
private double sal;
private double comm;
private int deptno;
public EmpDTO() {
super();
// TODO Auto-generated constructor stub
}
public EmpDTO(int empno, String ename, String job, int mgr, String hiredate, double sal, double comm, int deptno) {
super();
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public String getHiredate() {
return hiredate;
}
public void setHiredate(String hiredate) {
this.hiredate = hiredate;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
public double getComm() {
return comm;
}
public void setComm(double comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
@Override
public String toString() {
return "EmpDTO [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate="
+ hiredate + ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
}
}package com.exception;
public class DataNotFoundException extends Exception {
public DataNotFoundException(String mesg) {
super(mesg);
// TODO Auto-generated constructor stub
}
}