11. DAO

DAO

Data Access Object
데이터베이스 접근 부분을 캡슐화하여 메소드를 이용하여 데이터베이스에 접근한다. 이때 결과는 VO객체를 통해 얻는다.

AuthorDao.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.bigdata2017.jdbc.bookshop.vo.AuthorVo;

public class AuthorDao {

	private Connection getConnection() throws SQLException {

		Connection conn = null ;
		//1. JDBC 드라이버 로딩

		try {
			Class.forName( "oracle.jdbc.driver.OracleDriver" );

			//2. Connection 얻어오기
			String url = "jdbc:oracle:thin:@localhost:1521:xe";
			conn = DriverManager.getConnection(url, "hr", "hr");

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		return conn;
	}

	public int delete(  ) {
		int count = 0 ;

		Connection conn = null ;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();

			//3. Statement 객체 생성
			stmt = conn.createStatement();

			//4. SQL문 실행
			String sql ="DELETE FROM AUTHOR";
			count = stmt.executeUpdate(sql);




		} catch (SQLException e) {
			System.out.println( "error" + e );
		} finally {
			//3. 자원정리
			try {
				if( rs != null ) {
					rs.close();
				}
				if( stmt != null ) {
					stmt.close();
				}
				if( conn != null ) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return count;

	}

	public int insert( AuthorVo vo ) {
		int count = 0;

		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();

			//3. Statement 준비
			String sql =
				" insert" +
				"   into author" +
				" values (seq_author.nextval, ?, ?)";			
			pstmt = conn.prepareStatement( sql );

			//4. binding
			pstmt.setString( 1, vo.getName() );
			pstmt.setString( 2, vo.getProfile() );

			//5. SQL문 실행
			count = pstmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println( "error :" + e );
		} finally {
			//3.자원 정리
			try {
				if( pstmt != null ) {
					pstmt.close();
				}
				if( conn != null ) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		

		return count;
	}
	public List<AuthorVo> getList(){
		List<AuthorVo> list = new ArrayList<AuthorVo>();

		Connection conn = null ;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();

			//3. Statement 객체 생성
			stmt = conn.createStatement();

			//4. SQL문 실행
			String sql ="SELECT NO, NAME, PROFILE FROM AUTHOR ORDER BY NO";
			rs = stmt.executeQuery(sql);

			System.out.println("connection 성공");

			//5. 결과 사용하기
			while( rs.next() ) {
				Long no = rs.getLong( 1 );
				String name = rs.getString( 2 );
				String profile = rs.getString( 3 );

				AuthorVo vo = new AuthorVo();
				vo.setNo(no);
				vo.setName(name);
				vo.setProfile(profile);
				list.add(vo);
			}

		} catch (SQLException e) {
			System.out.println( "error" + e );
		} finally {
			//3. 자원정리
			try {
				if( rs != null ) {
					rs.close();
				}
				if( stmt != null ) {
					stmt.close();
				}
				if( conn != null ) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
}

AuthorDaoTest.java

package com.bigdata2017.jdbc.test;

import java.util.List;

import com.bigdata2017.jdbc.bookshop.dao.AuthorDao;
import com.bigdata2017.jdbc.bookshop.vo.AuthorVo;

public class AuthorDaoTest {

	public static void main(String[] args) {
		testInsert();
		testGetList();
	}
	public static void testGetList() {
		AuthorDao dao = new AuthorDao();
		List<AuthorVo> list = dao.getList();
		for(AuthorVo vo: list) {
			System.out.println(vo);

		}
	}
		public static void testInsert() {
			AuthorDao dao = new AuthorDao();

			AuthorVo vo = new AuthorVo();

			vo.setName( "공자" );
			vo.setProfile( "...." );

			new AuthorDao().insert(vo);

	}

}