JAVA 자바 JDBC 연습 - 상품정보 조회하기

🎯 자바 JDBC 연습문제를 풀어본다.

 

 

 

 

JDBC 상품정보 조회하기

DB 구성

 

결과는 위와같이 출력되어야 한다.

 

JDBC를 이용하여 매출일자순, 상품별 매출순 데이터 조회를 하도록 구현한다.

 

 

1. SQL Developer에서 test 계정 만들기

create user test IDENTIFIED by test;
alter user test account unlock;
grant connect, resource to test;

sys에서 명령문을 실행하고 새 계정을 생성하고 접속한다.

 

2. 테이블 만들기

DROP TABLE D7_ORDER;
DROP TABLE D7_PRODUCT;

CREATE TABLE D7_PRODUCT(
	PID VARCHAR(20) PRIMARY KEY,
	PNAME VARCHAR(50) NOT NULL,
	PRICE NUMBER NOT NULL
);

CREATE TABLE D7_ORDER(
	OID VARCHAR(10) PRIMARY KEY,
	PID VARCHAR(20) NOT NULL,
	QUANTITY NUMBER NOT NULL,
	ODATE DATE NOT NULL,
	CONSTRAINT FK_ORDER_PRODUCT FOREIGN KEY (PID) REFERENCES D7_PRODUCT (PID)
);

INSERT INTO D7_PRODUCT VALUES('P001', '이자녹스', 23000);
INSERT INTO D7_PRODUCT VALUES('P002', '리엔', 8800);
INSERT INTO D7_PRODUCT VALUES('P003', '죽염치약', 5500);
INSERT INTO D7_PRODUCT VALUES('P004', '페리오', 3400);
INSERT INTO D7_PRODUCT VALUES('P005', '온더바디', 9900);
INSERT INTO D7_PRODUCT VALUES('P006', '샤프란', 11500);
INSERT INTO D7_PRODUCT VALUES('P007', '테크', 9500);
INSERT INTO D7_PRODUCT VALUES('P008', '드봉', 2000);
INSERT INTO D7_PRODUCT VALUES('P009', '엘라스틴', 15000);
INSERT INTO D7_PRODUCT VALUES('P010', '조지아', 1000);
INSERT INTO D7_PRODUCT VALUES('P011', '조지아-블루', 1500);
INSERT INTO D7_PRODUCT VALUES('P012', '킨사이다', 1000);
INSERT INTO D7_PRODUCT VALUES('P013', '코카콜라', 1500);
INSERT INTO D7_PRODUCT VALUES('P014', '스프라이트', 2000);
INSERT INTO D7_PRODUCT VALUES('P015', '환타', 1000);
INSERT INTO D7_PRODUCT VALUES('P016', '숨', 25000);
INSERT INTO D7_PRODUCT VALUES('P017', '수려한', 55000);

INSERT INTO D7_ORDER VALUES('O-001', 'P001', 3, '2014/01/04');
INSERT INTO D7_ORDER VALUES('O-002', 'P004', 1, '2014/01/04');
INSERT INTO D7_ORDER VALUES('O-003', 'P001', 10, '2014/01/04');
INSERT INTO D7_ORDER VALUES('O-004', 'P001', 30, '2014/01/05');
INSERT INTO D7_ORDER VALUES('O-005', 'P011', 2, '2014/01/05');
INSERT INTO D7_ORDER VALUES('O-006', 'P012', 1, '2014/01/05');
INSERT INTO D7_ORDER VALUES('O-007', 'P011', 3, '2014/01/06');
INSERT INTO D7_ORDER VALUES('O-008', 'P012', 13, '2014/01/07');
INSERT INTO D7_ORDER VALUES('O-009', 'P013', 3, '2014/01/07');
INSERT INTO D7_ORDER VALUES('O-010', 'P014', 2, '2014/01/07');
INSERT INTO D7_ORDER VALUES('O-011', 'P014', 2, '2014/01/08');
INSERT INTO D7_ORDER VALUES('O-012', 'P001', 1, '2014/01/08');
INSERT INTO D7_ORDER VALUES('O-013', 'P007', 4, '2014/01/08');
INSERT INTO D7_ORDER VALUES('O-014', 'P008', 5, '2014/01/08');
INSERT INTO D7_ORDER VALUES('O-015', 'P001', 10, '2014/01/20');
INSERT INTO D7_ORDER VALUES('O-016', 'P007', 20, '2014/01/20');
INSERT INTO D7_ORDER VALUES('O-017', 'P014', 5, '2014/01/20');
INSERT INTO D7_ORDER VALUES('O-018', 'P013', 3, '2014/01/21');
INSERT INTO D7_ORDER VALUES('O-019', 'P007', 2, '2014/02/02');
INSERT INTO D7_ORDER VALUES('O-020', 'P008', 1, '2014/02/02');
INSERT INTO D7_ORDER VALUES('O-021', 'P008', 1, '2014/02/04');
INSERT INTO D7_ORDER VALUES('O-022', 'P009', 1, '2014/02/04');
INSERT INTO D7_ORDER VALUES('O-023', 'P001', 5, '2014/02/04');
INSERT INTO D7_ORDER VALUES('O-024', 'P012', 5, '2014/03/04');
INSERT INTO D7_ORDER VALUES('O-025', 'P001', 2, '2014/03/04');

COMMIT;

SELECT TO_CHAR(ODATE, 'YYYY-MM-DD') 영업일, SUM(O.QUANTITY*P.PRICE) 매출 FROM D7_ORDER O, D7_PRODUCT P
GROUP BY ODATE
ORDER BY 1
;

SELECT PNAME 상품명, SUM(O.QUANTITY*P.PRICE) 매출 FROM D7_ORDER O, D7_PRODUCT P
GROUP BY PNAME
ORDER BY 2 DESC
;

test 계정에 위 코드를 붙여넣고 실행한다. 여러개의 테이블이 생성된다.

 

3. 이클립스에서 코드를 작성한다.

String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String userid = "test";
String passwd = "test";
		
Connection con= null;
ResultSet rs= null;
PreparedStatement pstmt= null;

기본세팅

try {
	Class.forName(driver);
	con = DriverManager.getConnection(url, userid, passwd);
	String sql = "";

try문 시작하고 드라이버 로딩한 다음 sql 변수를 선언한다.

Scanner scan = new Scanner(System.in);
System.out.print("매출 조회 메뉴 - [ 매출 일자순 : 1, 상품별 매출순 : 2 ]: ");
int menu = scan.nextInt(); 

두가지의 매출 조회 방법을 선택할 수 있도록 스캐너를 생성해 숫자를 입력받는다.

if(menu ==1) {
	sql = "SELECT TO_CHAR(ODATE, 'YYYY-MM-DD') 영업일, SUM(O.QUANTITY*P.PRICE) 매출 "
		+ "FROM D7_ORDER O JOIN D7_PRODUCT P USING(PID) GROUP BY ODATE ORDER BY 1";
}

숫자가 1번일 경우 매출 일자순으로 영업일과 매출을 조회할 수 있도록 쿼리문을 작성한다. ANSI 네츄럴 조인방법에 USING절을 사용해 공통컬럼을 명시한다.

숫자를 char로 형변환할 수 있는 TO_CHAR(사용할 데이터, 원하는 출력 형태) 함수를 사용해 ODATE 정보를 입력하고 별칭은 영업일로 지정한다. 매출정보는 주문수량과 상품가격을 곱해 SUM함수로 합계를 구한다.

else if(menu ==2) {
	sql = "SELECT PNAME 상품명, SUM(O.QUANTITY*P.PRICE) 매출 "
		+ "FROM D7_ORDER O JOIN D7_PRODUCT P USING(PID) GROUP BY PNAME ORDER BY 2 DESC";
}

숫자가 2번일 경우 상품별 매출순으로 상품명과 매출을 조회할 수 있도록 쿼리문을 작성한다.

pstmt = con.prepareStatement(sql);
	rs = pstmt.executeQuery();
	System.out.println("--------------------------------------");
	System.out.println(((menu==1)?"영업일":"상품명")+"\t\t\t매출");
	System.out.println("--------------------------------------");

while (rs.next()) {
	System.out.println(rs.getString(1) + "\t\t" + rs.getString(2));
}

(menu==1)?"영업일":"상품명"

조건문 대신 간단한 삼항연산자를 사용했다. 괄호 안 조건이 참일 경우 ?기호 다음 첫자리 값을 실행하고 거짓일 경우 두번째 자리 값을 실행한다. 따라서 menu값이 2일 경우엔 상품명을 출력하게 된다.

} catch (ClassNotFoundException e) {
	e.printStackTrace();
}catch (SQLException 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();
	}
}

예외처리하기

#전체 코드 보기


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class workshop08 {
	
	public static void main(String[] args) throws SQLException {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@localhost:1521:orcl";
		String userid = "test";
		String passwd = "test";
		
		Connection con= null;
		ResultSet rs= null;
		PreparedStatement pstmt= null;
		
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, userid, passwd);
			String sql = "";
			Scanner scan = new Scanner(System.in);
			System.out.print("매출 조회 메뉴 - [ 매출 일자순 : 1, 상품별 매출순 : 2 ]: ");
			int menu = scan.nextInt();
			if(menu ==1) {
				sql = "SELECT TO_CHAR(ODATE, 'YYYY-MM-DD') 영업일, SUM(O.QUANTITY*P.PRICE) 매출 "
						+ "FROM D7_ORDER O JOIN D7_PRODUCT P USING(PID) GROUP BY ODATE ORDER BY 1";
			}else if(menu ==2) {
				sql = "SELECT PNAME 상품명, SUM(O.QUANTITY*P.PRICE) 매출 "
						+ "FROM D7_ORDER O JOIN D7_PRODUCT P USING(PID) GROUP BY PNAME ORDER BY 2 DESC";
			}
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			System.out.println("--------------------------------------");
			System.out.println(((menu==1)?"영업일":"상품명")+"\t\t\t매출");
			System.out.println("--------------------------------------");
			while (rs.next()) {
				System.out.println(rs.getString(1) + "\t\t" + rs.getString(2));
			}
			System.out.println("--------------------------------------");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException 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();
			}
		}
	}

 

 

 

EMP_JDBC 프로젝트

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() {}
	public EmpDTO(int empno, String ename, String job, int mgr, String hiredate, double sal, double comm, int deptno) {
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.mgr = mgr;
		this.hiredate = hiredate;
		this.sal = sal;
		this.comm = comm;
		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 + "]";
	}
	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;
	}

}

이번엔 scott계정의 emp테이블로 연습해본다. emp테이블의 모델 클래스로 사용할 DTO 클래스를 만든다.

package com.exception;

public class DataNotFoundException extends Exception {
	public DataNotFoundException(String mesg) {
		super(mesg);
	}

}

예외를 처리할 사용자정의 예외클래스를 만든다.

package com.common.util;

import java.sql.*;

public class JdbcTemplate { // 드라이버 로딩 및 db연결, con리턴, con.close()

	public static String driver = "oracle.jdbc.driver.OracleDriver";
	public static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
	public static String userid = "scott";
	public static String passwd = "tiger";

	public JdbcTemplate() {
		// 드라이버로딩
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static Connection getConnection() {
		// connect연결 후 tx(트랜잭션)를 false로 설정 리턴 con.setAutoCommit(false);
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, userid, passwd);
			conn.setAutoCommit(false); // 수정후 리턴
		} catch (Exception e) {
			System.out.println("[JdbcTemplate.getConnection] : " + e.getMessage());
			e.getStackTrace();
		}
		return conn; // connection 리턴
	}

	public static void close(Connection conn) {
		if (isConnected(conn))
			try {
				if (isConnected(conn))
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}

	public static boolean isConnected(Connection conn) {
		boolean validConnection = true;
		// conn 이 null 이거나 conn.isClose()가 참이면 validConnection을 false 로 설정
		try {
			if (conn == null || conn.isClosed())
				validConnection = false;
		} catch (SQLException e) {
			validConnection = false;
			e.printStackTrace();
		}
		return validConnection;
	}

	public static void close(PreparedStatement pstmt) {
		// dao에서 pstmt를 넘겨줘서 PreparedStatement를 close() //try-catch 처리
		try {
			if (pstmt != null) {
				pstmt.close();
			}
		} catch (SQLException e) {
			e.getStackTrace();
		}
	}

	public static void close(ResultSet rset) {
		//// dao에서 pstmt를 넘겨줘서 PreparedStatement를 close() close - try - catch 처리
		try {
			if (rset != null)
				rset.close();
		} catch (SQLException e) {
			e.getStackTrace();
		}
	}

	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();
		}
	}

	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.biz;

import java.sql.Connection;
import java.util.ArrayList;

import com.common.util.JdbcTemplate;
import com.dao.EmpDAO;
import com.entity.EmpDTO;
import com.exception.DataNotFoundException;

public class EmpBiz {

	EmpDAO dao;

	public EmpBiz() {
		dao = new EmpDAO(); // 클래스 타입 지정x 지정하게 되면 이 안에서만 사용하게된다
	}

	public ArrayList<EmpDTO> selectAllEmp() {
		// JdbcTemplate.getConnection()에서 con 얻기
		Connection con = JdbcTemplate.getConnection();
		// dao.selectAllEmp(con); 호출 con 전달
		ArrayList<EmpDTO> list = dao.selectAllEmp(con);
		// dbcTemplate 이용 close(con)으로 커넥션 끊기
		JdbcTemplate.close(con);
		return list;
	}

	public EmpDTO selectDetailEmp(String eno) throws DataNotFoundException {
		// JdbcTemplate.getConnection()에서 con 얻기
		Connection con = JdbcTemplate.getConnection();
		// dao.selectDetailEmp(con); 호출
		EmpDTO dto = dao.selectDetailEmp(con, eno);
		// dbcTemplate 이용 close
		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);
	}
}

비즈 클래스는 템플릿 클래스에서 커넥션을 얻어오고 커넥션을 DAO 클래스에 넘겨주는 역할을 한다.

package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.common.util.JdbcTemplate;
import com.entity.EmpDTO;
import com.exception.DataNotFoundException;

public class EmpDAO {
	public ArrayList<EmpDTO> selectAllEmp(Connection con) {
		// select 후 jdbcTemplate 이용 close
		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 DORDER 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 {
		// select 후 jdbcTemplate 이용 close
		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()) {
				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);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcTemplate.close(rs);
			JdbcTemplate.close(pstmt);
		}
		return result;
	}

	public void empUpdate(Connection con, EmpDTO empDTO) {
		PreparedStatement pstmt = null;
		try {
			String sql = "update emp set job = ?, sal=? where empno =?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, empDTO.getJob());
			pstmt.setDouble(2, empDTO.getSal());
			pstmt.setInt(3, empDTO.getEmpno());
			int n = pstmt.executeUpdate();
			System.out.println("실행된 레코드 갯수:" + n);
		} 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();
			System.out.println("실행된 레코드 갯수:" + n);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcTemplate.close(pstmt);
		}
	}
}

DAO 클래스는 sql문을 실행하는 역할을 담당한다.

public class EmpTest {
	public static void main(String[] args) {
		EmpBiz empBiz = new EmpBiz();

		ArrayList<EmpDTO> list = empBiz.selectAllEmp();
		for (EmpDTO dto : list) {
			System.out.println(dto);
		}

		try {
			EmpDTO emp = empBiz.selectDetailEmp("7369");
			System.out.println(emp);
		} catch (DataNotFoundException e) {
			e.printStackTrace();
		}

		try {
			empBiz.empDelete("107");
		} catch (DataNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}// end main
}// end class

메인에서는 비즈 클래스를 호출한다.

이 패턴은 마이바티스(mybatis)에서 사용할 패턴과 비슷하다.

 

 

 

😜 NEXT : 마이바티스 연습하기