Published 2022. 9. 27. 22:51

중고헌터 - DAO(Data Access Object) 작성

프로젝트/중고헌터

DAO(Data Access Object)

 

데이터(Data) 에 접근(Access) 하기 위한 객체(Object)로

Database 에 저장된 데이터를 읽기, 쓰기, 삭제, 변경을 수행한다.(CRUD)

DB테이블당 하나의  DAO를 작성

 

 

계층의 분리

 

UserDao.java

 

package com.jcy.usedhunter.dao;

import com.jcy.usedhunter.domain.User;

public interface UserDao {

	int deleteUser(String id);

	User selectUser(String id);

	// 사용자 정보를 user_info테이블에 저장하는 메서드
	int insertUser(User user);

	// 매개변수로 받은 사용자 정보로 user_info테이블을 update하는 메서드
	int updateUser(User user);
	
	void deleteAll() throws Exception;

}

 

 

UserDaoImpl.java

 

package com.jcy.usedhunter.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.jcy.usedhunter.domain.User;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    DataSource ds;
    final int FAIL = 0;

    @Override
	public int deleteUser(String id) {
        int rowCnt = FAIL; //  insert, delete, update

        Connection conn = null;
        PreparedStatement pstmt = null;

        String sql = "delete from user_info where id= ? ";

        try {
            conn = ds.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
//        int rowCnt = pstmt.executeUpdate(); //  insert, delete, update
//        return rowCnt;
            return pstmt.executeUpdate(); //  insert, delete, update
        } catch (SQLException e) {
            e.printStackTrace();
            return FAIL;
        } finally {
            // close()를 호출하다가 예외가 발생할 수 있으므로, try-catch로 감싸야함.
//            try { if(pstmt!=null) pstmt.close(); } catch (SQLException e) { e.printStackTrace();}
//            try { if(conn!=null)  conn.close();  } catch (SQLException e) { e.printStackTrace();}
            close(pstmt, conn); //     private void close(AutoCloseable... acs) {
        }
    }

    @Override
	public User selectUser(String id) {
        User user = null;

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        String sql = "select * from user_info where id= ? ";

        try {
            conn = ds.getConnection();
            pstmt = conn.prepareStatement(sql); // SQL Injection공격, 성능향상
            pstmt.setString(1, id);

            rs = pstmt.executeQuery(); //  select

            if (rs.next()) {
                user = new User();
                user.setId(rs.getString(1));
                user.setPwd(rs.getString(2));
                user.setName(rs.getString(3));
                user.setEmail(rs.getString(4));
                user.setBirth(new Date(rs.getDate(5).getTime()));
                user.setSns(rs.getString(6));
                user.setReg_date(new Date(rs.getTimestamp(7).getTime()));
            }
        } catch (SQLException e) {
            return null;
        } finally {
            // close()를 호출하다가 예외가 발생할 수 있으므로, try-catch로 감싸야함.
            // close()의 호출순서는 생성된 순서의 역순
//            try { if(rs!=null)    rs.close();    } catch (SQLException e) { e.printStackTrace();}
//            try { if(pstmt!=null) pstmt.close(); } catch (SQLException e) { e.printStackTrace();}
//            try { if(conn!=null)  conn.close();  } catch (SQLException e) { e.printStackTrace();}
            close(rs, pstmt, conn);  //     private void close(AutoCloseable... acs) {
        }

        return user;
    }

    // 사용자 정보를 user_info테이블에 저장하는 메서드
    @Override
	public int insertUser(User user) {
        int rowCnt = FAIL;

        Connection conn = null;
        PreparedStatement pstmt = null;

//        insert into user_info (id, pwd, name, email, birth, sns, reg_date)
//        values ('asdf22', '1234', 'smith', 'aaa@aaa.com', '2022-01-01', 'facebook', now());
        String sql = "insert into user_info values (?, ?, ?, ?,?,?, now()) ";

        try {
            conn = ds.getConnection();
            pstmt = conn.prepareStatement(sql); // SQL Injection공격, 성능향상
            pstmt.setString(1, user.getId());
            pstmt.setString(2, user.getPwd());
            pstmt.setString(3, user.getName());
            pstmt.setString(4, user.getEmail());
            pstmt.setDate(5, new java.sql.Date(user.getBirth().getTime()));
            pstmt.setString(6, user.getSns());

            return pstmt.executeUpdate(); //  insert, delete, update;
        } catch (SQLException e) {
            e.printStackTrace();
            return FAIL;
        } finally {
            close(pstmt, conn);  //     private void close(AutoCloseable... acs) {
        }
    }

    // 매개변수로 받은 사용자 정보로 user_info테이블을 update하는 메서드
    @Override
	public int updateUser(User user) {
        int rowCnt = FAIL; //  insert, delete, update

//        Connection conn = null;
//        PreparedStatement pstmt = null;

        String sql = "update user_info " +
                     "set pwd = ?, name=?, email=?, birth =?, sns=?, reg_date=? " +
                     "where id = ? ";

        // try-with-resources - since jdk7
        try (
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql); // SQL Injection공격, 성능향상
        ){
            pstmt.setString(1, user.getPwd());
            pstmt.setString(2, user.getName());
            pstmt.setString(3, user.getEmail());
            pstmt.setDate(4, new java.sql.Date(user.getBirth().getTime()));
            pstmt.setString(5, user.getSns());
            pstmt.setTimestamp(6, new java.sql.Timestamp(user.getReg_date().getTime()));
            pstmt.setString(7, user.getId());

            rowCnt = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return FAIL;
        }

        return rowCnt;
    }

    
    public void deleteAll() throws Exception{
        Connection conn = ds.getConnection();

        String sql = "delete from user_info ";

        PreparedStatement pstmt = conn.prepareStatement(sql); // SQL Injection공격, 성능향상
        pstmt.executeUpdate(); //  insert, delete, update
    }

    private void close(AutoCloseable... acs) {
        for(AutoCloseable ac :acs)
            try { if(ac!=null) ac.close(); } catch(Exception e) { e.printStackTrace(); }
    }
}

 

root-context.xml

 

context:component 추가

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:mvc="http://www.springframework.org/schema/mvc"
	   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	   xmlns="http://www.springframework.org/schema/beans"
	   xmlns:context="http://www.springframework.org/schema/context"
	   xmlns:tx="http://www.springframework.org/schema/tx"
	   xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd
		http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName"
			value="com.mysql.jdbc.Driver"></property>
		<property name="url" value="jdbc:mysql://localhost:3301/usedhunter?useUnicode=true&amp;characterEncoding=utf8"></property>
		<property name="username" value="jcy"></property>
		<property name="password" value="darknight24!"></property>
	</bean>
	 <context:component-scan base-package="com.jcy.usedhunter" />
	</beans>

 

 

UserDao 테스트 하기

 

UserDaoImplTest.java

 

package com.jcy.usedhunter;

import static org.junit.Assert.*;

import java.util.Calendar;
import java.util.Date;

import org.junit.Ignore;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.jcy.usedhunter.dao.UserDao;
import com.jcy.usedhunter.domain.User;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"file:src/main/webapp/WEB-INF/spring/**/root-context.xml"})
public class UserDaoImplTest {
	
	@Autowired
	UserDao userDao;
	
	@Test
	public void deleteUser() throws Exception  {
		Calendar cal = Calendar.getInstance();
		cal.clear();
		cal.set(2022, 1, 1);
		
		userDao.deleteAll();
		
		User user = new User("asdf", "1234", "adb", "aaa@aaa.com", new Date(cal.getTimeInMillis()), "facebook", new Date());
		userDao.insertUser(user);
		
		int rowCnt = userDao.deleteUser("asdf");
		assertTrue(rowCnt==1);
		assertTrue(userDao.selectUser(user.getId())==null);
	}
	
	@Ignore
	@Test
	public void selectUser()throws Exception {
		Calendar cal = Calendar.getInstance();
		cal.clear();
		cal.set(2022, 1, 1);
		
		userDao.deleteAll();
		User user = new User("asdf", "1234", "adb", "aaa@aaa.com", new Date(cal.getTimeInMillis()), "facebook", new Date());
		userDao.insertUser(user);
		User user2 = userDao.selectUser("asdf");
		
		assertTrue(user2.getId().equals("asdf"));
	}
	
	@Ignore
	@Test
	public void insertUser() throws Exception{
		Calendar cal = Calendar.getInstance();
		cal.clear();
		cal.set(2021, 1, 1);
		
		userDao.deleteAll();
		User user = new User("asdf2", "1234", "adb", "aaa@aaa.com", new Date(cal.getTimeInMillis()), "facebook", new Date());
		int rowCnt = userDao.insertUser(user);
		
		assertTrue(rowCnt==1);
	}
	
	@Ignore
	@Test
	public void updateUser() throws Exception {
		Calendar cal = Calendar.getInstance();
		cal.clear();
		cal.set(2021, 1, 1);
		
		userDao.deleteAll();
		User user = new User("asdf", "1234", "adb", "aaa@aaa.com", new Date(cal.getTimeInMillis()), "facebook", new Date());
		int rowCnt = userDao.insertUser(user);
		assertTrue(rowCnt==1);
		
		user.setPwd("bbb");
		user.setEmail("bbb@bbb.com");
		rowCnt = userDao.updateUser(user);
		
		assertTrue(rowCnt==1);
		
		User user2 = userDao.selectUser(user.getId());
		System.out.println("user = " + user);
		System.out.println("user2 = " + user2);
		assertTrue(user.equals(user2));
		
		
	}

}

 

 

저작자표시 비영리 변경금지

'프로젝트 > 중고헌터' 카테고리의 다른 글

중고헌터 - 데이터 변환과 검증 2  (0) 2022.09.28
중고헌터 - 데이터 변환과 검증  (0) 2022.09.28
중고헌터 - TDD(Test-Driven-Development)  (0) 2022.09.27
중고헌터 - STS, DB(MySQL ) 연결  (0) 2022.09.27
중고헌터 - 로그인 페이지 만들기3  (1) 2022.09.21
복사했습니다!

티스토리툴바