[프레임워크] <Spring>〈Maven〉JDBCTempalete을 활용한 DAO

JDBCTempalete 

◎ 템플릿 패턴을 활용한 클래스이다.

◎ 반복되는 JDBC의 로직을 캡슐화한 클래스이다.

◎ 가독성이 증가한다.

---

템플릿 패턴

◎ 반복되는 로직 또는 복잡한 로직을 캡슐화한 것이다.

 

---

기존 코드

package com.spring.biz.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCUtil {
	
	static final String driverName="com.mysql.cj.jdbc.Driver";
	static final String url="jdbc:mysql://localhost:3306/kimdb";
	static final String user="root";
	static final String passwd="1234";
	
	public static Connection connect() {
		Connection conn=null;
		
		try {
			Class.forName(driverName);
			
			conn=DriverManager.getConnection(url,user,passwd);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		
		return conn;
	}
	
	public static void disconnect(PreparedStatement pstmt, Connection conn) {
		try {
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}

 

 

package com.spring.biz.member;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
@Repository("memberDAO")
public class MemberDAO {
	private Connection conn;
	private PreparedStatement pstmt;

	private static final String SELECTALL="SELECT * FROM MEMBER";
	private static final String SELECTONE="SELECT * FROM MEMBER WHERE MID=? AND PASSWORD=?";

	private static final String INSERT="INSERT INTO MEMBER VALUES(?,?,?,?)";
	private static final String UPDATE="";
	private static final String DELETE="";

	public ArrayList<MemberDTO> selectAll(MemberDTO mDTO) {
		ArrayList<MemberDTO> datas=new ArrayList<MemberDTO>();

		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(SELECTALL);

			ResultSet rs=pstmt.executeQuery();

			while(rs.next()) {
				MemberDTO data=new MemberDTO();
				data.setMid(rs.getString("MID"));
				data.setPassword(rs.getString("PASSWORD"));
				data.setName(rs.getString("NAME"));
				data.setRole(rs.getString("ROLE"));
				datas.add(data);
			}

			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}

		return datas;
	}
	public MemberDTO selectOne(MemberDTO mDTO) {
		MemberDTO data=null;

		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(SELECTONE);
			pstmt.setString(1, mDTO.getMid());
			pstmt.setString(2, mDTO.getPassword());

			ResultSet rs=pstmt.executeQuery();

			if(rs.next()) {
				data=new MemberDTO();
				data.setMid(rs.getString("MID"));
				data.setPassword(rs.getString("PASSWORD"));
				data.setName(rs.getString("NAME"));
				data.setRole(rs.getString("ROLE"));
			}

			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}

		return data;
	}

	public boolean insert(MemberDTO mDTO) {
		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(INSERT);
			pstmt.setString(1, mDTO.getMid());
			pstmt.setString(2, mDTO.getPassword());
			pstmt.setString(3, mDTO.getName());
			pstmt.setString(4, mDTO.getRole());
			int rs=pstmt.executeUpdate();
			if(rs<=0) {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return true;
	}
	public boolean update(MemberDTO mDTO) {
		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(UPDATE);
			int rs=pstmt.executeUpdate();
			if(rs<=0) {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return true;
	}
	public boolean delete(MemberDTO mDTO) {
		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(DELETE);
			int rs=pstmt.executeUpdate();
			if(rs<=0) {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return true;
	}
}

---

템플릿 패턴 적용 코드

public class MemberDAO {
   
   @Autowired 
   private JdbcTemplate jdbcTemplate;

   private static final String SELECTALL="SELECT * FROM MEMBER";
   private static final String SELECTONE="SELECT * FROM MEMBER WHERE MID=? AND PASSWORD=?";
   private static final String INSERT="INSERT INTO MEMBER (MID,PASSWORD,NAME,ROLE) VALUES(?,?,?,?)";
   private static final String UPDATE="";
   private static final String DELETE="";

   public ArrayList<MemberDTO> selectAll(MemberDTO mDTO) {
	   return (ArrayList<MemberDTO>)jdbcTemplate.query(SELECTALL, new MemberRowMapper());
   }
   
   public MemberDTO selectOne(MemberDTO mDTO) {
      return jdbcTemplate.queryForObject(SELECTONE, new MemberRowMapper());
   }

   public boolean insert(MemberDTO mDTO) {
      int result=jdbcTemplate.update(INSERT, mDTO.getMid(), mDTO.getPassword(), mDTO.getName(), mDTO.getRole());
      if(result<=0) {
         return false;
      }
      return true;
   }
   
   public boolean update(MemberDTO mDTO) {
      int result=jdbcTemplate.update(UPDATE, mDTO.getMid(), mDTO.getPassword(), mDTO.getName(), mDTO.getRole());
      if(result<=0) {
         return false;
      }
      return true;
   }
   
   public boolean delete(MemberDTO mDTO) {
      int result=jdbcTemplate.update(DELETE, mDTO.getMid(), mDTO.getPassword(), mDTO.getName(), mDTO.getRole());
      if(result<=0) {
         return false;
      }
      return true;
   }
}

// 개발자의 편의를 위해 RowMapper 인터페이스를 사용
class MemberRowMapper implements RowMapper<MemberDTO> {
   @Override
   public MemberDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
      MemberDTO data=new MemberDTO();
      data.setMid(rs.getString("MID"));
      data.setPassword(rs.getString("PASSWORD"));
      data.setName(rs.getString("NAME"));
      data.setRole(rs.getString("ROLE"));
      return data;
   }
}

 

---

JdbcTemplate 의존 주입을 위한 bean

// Util 역할
// DBCP
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
	  
	<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
      
	<property name="url" value="jdbc:mysql://localhost:3306/lee" />
      
	<property name="username" value="root" />
      
	<property name="password" value="1234" />
      
</bean>

// 템플릿을 사용하기 위한 선언
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	   
	<property name="dataSource" ref="dataSource" />
      
</bean>