본문 바로가기
Dev/[Java]

[DBMS] 4. Dynamic Web Project 에서 SQL테스트(Junit)

랑섭(Rangsub) 2021. 1. 26. 17:36
반응형

 

 

DAO.java

package jdbc.user.dao;

import java.util.*;

import jdbc.user.vo.UserVO;

import java.sql.*;

/**
 * DAO(Data Access Object) jdbc 를 쓰거나 Mybatis 를 사용한 객체가 되겠죠? 
 * 왜냐? Data에 접근하는 객체이기
 * 때문에!!
 */
public class UserDAO {
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String user = "scott";
	String pw = "tiger";

	/**
	 * Default constructor
	 */
	public UserDAO() {
		// 1. Driver Class loading
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, user, pw);
	}

	public void close(Statement stmt, Connection con) throws SQLException {
		if (stmt != null)
			stmt.close();
		if (con != null)
			con.close();
	}

	/**
	 * @return
	 */
	public List<UserVO> getUsers() {
		// TODO implement here
		return null;
	}

	/**
	 * @param userid
	 * @return
	 */
	public UserVO getUser(String userid) {
		String sql = "select * from users where userid = ?";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		UserVO userVO = null;
		try {
			con = getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, userid);
			rs = stmt.executeQuery();
			if(rs.next()) {
				userVO = new UserVO(
						rs.getInt("id"),
						rs.getString("userid"),
						rs.getString("name"),
						rs.getString("gencer"),
						rs.getString("city"),
						rs.getDate("regdate")
						);
			}
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}

		return userVO;
	}

	/**
	 * @param userVO
	 */
	public void insertUser(UserVO userVO) {
		// TODO implement here
	}

	/**
	 * @param UserVO
	 */
	public void updateUser(UserVO UserVO) {
		// TODO implement here
	}

}

 

VO.java

package jdbc.user.vo;

import java.sql.*;
/**
 * VO(Value Object)
 * DTO(Data Transfer Object), JavaBeans, Entity, 
 */
public class UserVO {

   
    /**
     * id
     */
    private int id;

    /**
     * userid
     */
    private String userid;

    /**
     * name
     */
    private String name;

    /**
     * gender
     */
    private String gender;

    /**
     * city
     */
    private String city;

    /**
     * date
     */
    private Date regdate;

    /**
     * Default constructor
     */
    public UserVO() {
    }

	public UserVO(String userid, String name, String gender, String city) {
		super();
		this.userid = userid;
		this.name = name;
		this.gender = gender;
		this.city = city;
	}

	public UserVO(int id, String userid, String name, String gender, String city) {
		this(userid,name,gender,city);
		this.id = id;
	}
	
	public UserVO(int id, String userid, String name, String gender, String city, Date regdate) {
		this(id,userid,name,gender,city);
		this.regdate = regdate;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getUserid() {
		return userid;
	}

	public void setUserid(String userid) {
		this.userid = userid;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

	public String getCity() {
		return city;
	}

	public void setCity(String city) {
		this.city = city;
	}

	public Date getRegdate() {
		return regdate;
	}

	public void setRegdate(Date regdate) {
		this.regdate = regdate;
	}

	@Override
	public String toString() {
		return "UserVO [id=" + id + ", userid=" + userid + ", name=" + name + ", gender=" + gender + ", city=" + city
				+ ", regdate=" + regdate + "]";
	}
	
	
    
	
	
    
    

}

 

이제 이렇게 하고 View 단을 붙여보고자 한다... 그런데 뷰단 만들기 전에 단위테스트를 보통 먼저 하고 진행한다고 한다..

 

이걸 지원하는 JUNIT 실습해볼것이다...

 

5. 단위테스트 작성..

Junit(java unit) Junit라이브러리 받는 방법은

1. 메이븐에서 가져와도되고,,

2. 이클립스에 내장되어있다..

 

우리는 2번 방법으로할것이다...

 

 

 

 

 

프로젝트에 이렇게 생긴다

 

 

 

이후 테스트용 패키지와 클래스를 하나 더 만들어준다...

 

이렇게 이클립스에 포함된 JUNIT을 추가한 뒤....

 

@Test

위와 같은 테스트 어노테이션을 반드시 붙여줘야한다.

테스트 어노테이션 안붙이면 실행 안된다..

그리고 메서드는 무조건 public void 로 해야댄다...

메서드 명은 상관없음...

 

 

@Before 라는 어노테이션도있다...

 

테스트 진행 전에 반복되는 코드들을 별도의 다른 메서드를 만들어서 거기다가넣어준다.

 

 

 

 

 

 

 

 

 

자 다시 돌아와서... Insert문을 구현한다.

 

 

package jdbc.user.dao;

import java.util.*;

import jdbc.user.vo.UserVO;

import java.sql.*;

/**
 * DAO(Data Access Object) jdbc 를 쓰거나 Mybatis 를 사용한 객체가 되겠죠? 왜냐? Data에 접근하는 객체이기
 * 때문에!!
 */
public class UserDAO {
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String user = "scott";
	String pw = "tiger";

	/**
	 * Default constructor
	 */
	public UserDAO() {
		// 1. Driver Class loading
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, user, pw);
	}

	public void close(Statement stmt, Connection con) throws SQLException {
		if (stmt != null)
			stmt.close();
		if (con != null)
			con.close();
	}

	/**
	 * @return
	 */
	public List<UserVO> getUsers() {
		// TODO implement here
		return null;
	}

	/**
	 * @param userid
	 * @return
	 */
	public UserVO getUser(String userid) {
		String sql = "select * from users where userid =?";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		UserVO userVO = null;
		try {
			con = getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, userid);
			rs = stmt.executeQuery();
			if(rs.next()) {
				userVO = new UserVO(
						rs.getInt("id"),
						rs.getString("userid"),
						rs.getString("name"),
						rs.getString("gender"),
						rs.getString("city"),
						rs.getDate("regdate")
						);
			}
			
			close(stmt,con);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}

		return userVO;
	}

	/**
	 * @param userVO
	 */
	public int insertUser(UserVO userVO) {
		String sql = "insert into users values(user_seq.NEXTVAL, ?, ?, ?, ?, sysdate)";
		Connection con = null;
		PreparedStatement stmt = null;
		int insertCount = 0;
		try {
			con = getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, userVO.getUserid());
			stmt.setString(2, userVO.getName());
			stmt.setString(3, userVO.getGender());
			stmt.setString(4, userVO.getCity());
			insertCount = stmt.executeUpdate();
		} catch(SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally{
			try {
				close(stmt,con);
			} catch(SQLException e) {
				System.out.println(e.getMessage());
				e.printStackTrace();
			}
			
		}
		
		return insertCount;
	}

	/**
	 * @param UserVO
	 */
	public void updateUser(UserVO UserVO) {
		// TODO implement here
	}

}

 

insert 문을 구현하고 다시 테스트 돌려본다..

 

insert 한번 했는데 또하면 에러나기때문에,,

 

@Ignore 라는 어노테이션을 써주면 그 테스트는 진행안한다.

 

 

자 이제 Update와  Delete 까지 구현한다

 

package jdbc.user.dao;

import java.util.*;

import jdbc.user.vo.UserVO;

import java.sql.*;

/**
 * DAO(Data Access Object) jdbc 를 쓰거나 Mybatis 를 사용한 객체가 되겠죠? 왜냐? Data에 접근하는 객체이기
 * 때문에!!
 */
public class UserDAO {
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String user = "scott";
	String pw = "tiger";

	/**
	 * Default constructor
	 */
	public UserDAO() {
		// 1. Driver Class loading
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, user, pw);
	}

	public void close(Statement stmt, Connection con) throws SQLException {
		if (stmt != null)
			stmt.close();
		if (con != null)
			con.close();
	}

	/**
	 * @return
	 */
	public List<UserVO> getUsers() {
		// TODO implement here
		return null;
	}

	/**
	 * @param userid
	 * @return
	 */
	public UserVO getUser(String userid) {
		String sql = "select * from users where userid =?";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		UserVO userVO = null;
		try {
			con = getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, userid);
			rs = stmt.executeQuery();
			if(rs.next()) {
				userVO = new UserVO(
						rs.getInt("id"),
						rs.getString("userid"),
						rs.getString("name"),
						rs.getString("gender"),
						rs.getString("city"),
						rs.getDate("regdate")
						);
			}
			
			close(stmt,con);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}

		return userVO;
	}

	/**
	 * @param userVO
	 */
	public int insertUser(UserVO userVO) {
		String sql = "insert into users values(user_seq.NEXTVAL, ?, ?, ?, ?, sysdate)";
		Connection con = null;
		PreparedStatement stmt = null;
		int insertCount = 0;
		try {
			con = getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, userVO.getUserid());
			stmt.setString(2, userVO.getName());
			stmt.setString(3, userVO.getGender());
			stmt.setString(4, userVO.getCity());
			insertCount = stmt.executeUpdate();
		} catch(SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally{
			try {
				close(stmt,con);
			} catch(SQLException e) {
				System.out.println(e.getMessage());
				e.printStackTrace();
			}
			
		}
		
		return insertCount;
	}

	/**
	 * @param UserVO
	 */
	public int updateUser(UserVO userVO, String userid) {
		// update 할 때 where 조건에 user id 를 사용하세요.
		String sql = "update users set userid =?, name = ?, gender = ?, city = ? where userid = ?";
		Connection con = null;
		PreparedStatement stmt = null;
		int updateCount = 0;
		try {
			con = getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, userVO.getUserid());
			stmt.setString(2, userVO.getName());
			stmt.setString(3, userVO.getGender());
			stmt.setString(4, userVO.getCity());
			stmt.setString(5, userid);
			updateCount = stmt.executeUpdate();
		} catch(SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally{
			try {
				close(stmt,con);
			} catch(SQLException e) {
				System.out.println(e.getMessage());
				e.printStackTrace();
			}
			
		}
		return updateCount;
		
	}
	
	/**
	 * @param UserVO
	 */
	public int deleteUser(int  id) {
		// TODO implement here
			String sql = "delete from users where id = ?";
			Connection con = null;
			PreparedStatement stmt = null;
			int deleteCount = 0;
			try {
				con = getConnection();
				stmt = con.prepareStatement(sql);
				stmt.setInt(1, id);
				deleteCount = stmt.executeUpdate();
			} catch(SQLException e) {
				System.out.println(e.getMessage());
				e.printStackTrace();
			} finally{
				try {
					close(stmt,con);
				} catch(SQLException e) {
					System.out.println(e.getMessage());
					e.printStackTrace();
				}
				
			}
			return deleteCount;
			
	}

}

 

 

	@Test @Ignore
	public void deleteTest() {
		int deleteCount=0; 
		deleteCount = dao.deleteUser(1);
		assertEquals(1, deleteCount);
	}
	
	@Test @Ignore
	public void UpdateTest() {
		UserVO user = new UserVO("toly","톨리","남","청주");
		int count = dao.updateUser(user, "dooly");
		assertEquals(1, count);
	}

 

 

 

 

 

강사님의 소스코드..

여기서 참고할것은 에러나면 롤백하는 것을 잘 봐두자...

 

UserDAO.java

package jdbc.user.dao;

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 jdbc.user.vo.UserVO;

public class UserDAO {
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	String user = "scott";
	String pass = "tiger";

	public UserDAO() {
		//1. Driver class loading
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			System.out.println("Driver loading OK!!");
		} catch (ClassNotFoundException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}		
	}
	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, user, pass);
	}
	
	public void close(Statement stmt, Connection con) throws SQLException {
		if (stmt != null) stmt.close();
		if (con != null) con.close();
	}
	
	//update 하는 메서드
	public int updateUser(UserVO user) {
		String sql = "update users set name = ?, gender = ?, city = ? where userid = ?";
		Connection con = null;
		PreparedStatement stmt = null;
		int updateCnt = 0;
		try {
			con = getConnection();
			//auto commit 해제
			con.setAutoCommit(false);
			stmt = con.prepareStatement(sql);
			stmt.setString(1, user.getName());
			stmt.setString(2, Character.toString(user.getGender()));
			stmt.setString(3, user.getCity());
			stmt.setString(4, user.getUserid());
			updateCnt = stmt.executeUpdate();
			//커밋
			con.commit();
		}catch(SQLException e) {
			//롤백
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			try {
				close(stmt,con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return updateCnt;
	}
	
	//userid를 입력 받아서 1개의 row를 반환하는 메서드
	public UserVO getUser(String userid) {
		String sql = "select * from users where userid = ?";
		Connection con = null;
		PreparedStatement stmt = null;
		UserVO user = null;
		try {
			con = getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, userid);
			ResultSet rs =  stmt.executeQuery();
			if(rs.next()) {
				user = new UserVO(rs.getString("userid"), 
						          rs.getString("name"), 
						          rs.getString("gender").charAt(0), 
						          rs.getString("city"));
				
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				close(stmt,con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return user;
	}
	//전체 row를 반환하는 메서드
	public List<UserVO> getUsers() {
		String sql = "select * from users order by userid";
		Connection con = null;
		PreparedStatement stmt = null;
		UserVO user = null;
		List<UserVO> userList = new ArrayList<>();
		try {
			con = getConnection();
			stmt = con.prepareStatement(sql);
			ResultSet rs =  stmt.executeQuery();
			while(rs.next()) {
				user = new UserVO(rs.getString("userid"), 
						          rs.getString("name"), 
						          rs.getString("gender").charAt(0), 
						          rs.getString("city"));
				userList.add(user);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				close(stmt,con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return userList;
	}//getUserList
	
	
}

 

 

오라클로 여태 실습을 했는데 회사에서 최종플젝에는 마리아디비를 쓰라고 한다...

 

그래서 디비를 다시 설치한다..

rootid와 PW 는

mysql.mysql 로 한다..

 

 

#MySQL Database 생성
mysql -u root –p
show databases;
use mysql;(mysql db를 쓰겠다)
create user scott@localhost identified by 'tiger';
grant all on *.* to scott@localhost;
flush privileges;
exit;

mysql -u scott -p
create database java_db;
show databases;
use java_db;

 

 

 

 

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

'Dev > [Java]' 카테고리의 다른 글

[DBMS] 6. Mybatis 연동후 MariaDB와 붙여서 CRUD작성  (0) 2021.01.27
[DBMS] 5. Mybatis 개요, MariaDB 설정 및 테이블 생성  (0) 2021.01.27
[JDBC] 3. Dynamic Web Project 생성 + Maven연동  (0) 2021.01.26
[JDBC] 2. Servlet + JSP + JDBC ( Tomcat 설치)  (0) 2021.01.26
[JDBC] 1. 설치 및 환경설정, 이클립스와 연동 방법  (0) 2021.01.21

'Dev/[Java]' Related Articles

티스토리툴바