17일차 6

spring jdbc

지금부터 Spring 내부에서 지원하는 jdbc,  즉 Spring JDBC를 사용해서 소스를 간략화 시켜볼거야..

 

먼저 java프로그램으로 만들어보고 web은 나중에 만들어보자..

 

우선 일반 jdbc를 사용하는 java프로그램을 만들어보자.

 

Java project로 springdev2만들어..

 

Spring Library 추가하자. springdev2에서 마우스 오른쪽 –> Properties

 

UTF-8설정

 

그리고 src밑에다 springweb3의 test.db package복사해와. 이걸 변형해서 만들자.

 

test.db밑에 Spring Bean Configuration File만들어.(beaninit.xml)

EmpListController에서 DB접속해 데이터 가져오는 부분을 EmpDao로 분리하자.

package test.db;

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


public class EmpDao {

	
	public List<EmpDto> list() {
		Connection cn = null;
		PreparedStatement st = null;
		ResultSet rs = null;		

		String sql = "SELECT e.empno, e.ename, e.sal, d.dname " + 
				 "FROM t_emp e, t_dept d " + 
				 "WHERE e.deptno =d.deptno ORDER BY 1 ASC";
		
		List<EmpDto> list = new ArrayList<EmpDto>();
		
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			cn = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521:xe", "oraclejava", "oraclejava");
			st = cn.prepareStatement(sql);
			rs = st.executeQuery();			
			
			while(rs.next()) {
				EmpDto dto = new EmpDto();
				dto.setEmpno(rs.getInt("empno"));
				dto.setEname(rs.getString("ename"));
				dto.setSal(rs.getInt("sal"));
				dto.setDname(rs.getString("dname"));
				list.add(dto);
			} 	
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {			
			if(rs != null ) try {rs.close();} catch(Exception e) {}
			if(st != null ) try {st.close();} catch(Exception e) {}
			if(cn != null ) try {cn.close();} catch(Exception e) {}
		}
		
		return list;
	}
}

 

EmpDao를 인터페이스로 만들어놓고, 실제구현은 EmpDaoImpl로 하자. 의존관계를 줄이기 위해서야.

EmpDao.java에 F2누르고 EmpDaoImpl로 변경.

package test.db;

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


public class EmpDaoImpl implements EmpDao {

	@Override
	public List<EmpDto> list() {
		Connection cn = null;
		PreparedStatement st = null;
		ResultSet rs = null;		

		String sql = "SELECT e.empno, e.ename, e.sal, d.dname " + 
				 "FROM t_emp e, t_dept d " + 
				 "WHERE e.deptno =d.deptno ORDER BY 1 ASC";
		
		List<EmpDto> list = new ArrayList<EmpDto>();
		
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			cn = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521:xe", "oraclejava", "oraclejava");
			st = cn.prepareStatement(sql);
			rs = st.executeQuery();			
			
			while(rs.next()) {
				EmpDto dto = new EmpDto();
				dto.setEmpno(rs.getInt("empno"));
				dto.setEname(rs.getString("ename"));
				dto.setSal(rs.getInt("sal"));
				dto.setDname(rs.getString("dname"));
				list.add(dto);
			} 	
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {			
			if(rs != null ) try {rs.close();} catch(Exception e) {}
			if(st != null ) try {st.close();} catch(Exception e) {}
			if(cn != null ) try {cn.close();} catch(Exception e) {}
		}
		
		return list;
	}
}

package test.db;

import java.util.List;

public interface EmpDao {
	public List<EmpDto> list();
}

 

EmpListController 수정

package test.db;

import java.util.List;

public class EmpListController {

	public void show() {
		
		EmpDao dao = new EmpDaoImpl();
		List<EmpDto> list = dao.list();		

		System.out.println("\t\t사원리스트");
		System.out.println("==================================");

		System.out.print("번호"+ "\t");
		System.out.print("이름"+ "\t");
		System.out.print("급여"+ "\t");
		System.out.print("부서"+ "\n");
		System.out.println("----------------------------------");
		
		for(EmpDto dto: list) {
			System.out.print(dto.getEmpno()+ "\t");
			System.out.print(dto.getEname()+ "\t");
			System.out.print(dto.getSal()+ "\t");
			System.out.print(dto.getDname()+ "\n");
		}
		System.out.println("==================================");
	}	
}

 

beaninit.xml(Spring Bean ConfigurationFile 생성)에 bean 등록

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

	<bean id="emplist" class="test.db.EmpListController" />

</beans>

 

실행위한 EmpMain구현

package test.db;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class EmpMain {

	public static void main(String[] args) {

		ApplicationContext context = 
				new ClassPathXmlApplicationContext("test/db/beaninit.xml");
		EmpListController el = (EmpListController)context.getBean("emplist");
		el.show();	
	}

}

 

EmpList를 인터페이스로 만들고, EmpListController에 implements 시키자.

package test.db;

public interface EmpList {
	public void show();
}

package test.db;

import java.util.List;

public class EmpListController implements EmpList{

	public void show() {
		
		EmpDao dao = new EmpDaoImpl();
		List<EmpDto> list = dao.list();		

		System.out.println("\t\t사원리스트");
		System.out.println("==================================");

		System.out.print("번호"+ "\t");
		System.out.print("이름"+ "\t");
		System.out.print("급여"+ "\t");
		System.out.print("부서"+ "\n");
		System.out.println("----------------------------------");
		
		for(EmpDto dto: list) {
			System.out.print(dto.getEmpno()+ "\t");
			System.out.print(dto.getEname()+ "\t");
			System.out.print(dto.getSal()+ "\t");
			System.out.print(dto.getDname()+ "\n");
		}
		System.out.println("==================================");
	}	
}

 

EmpMain에 적용

package test.db;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class EmpMain {

	public static void main(String[] args) {

		ApplicationContext context = 
				new ClassPathXmlApplicationContext("test/db/beaninit.xml");
		EmpList el = (EmpList)context.getBean("emplist");
		el.show();	
	}

}

 

 

여기까지가 일반jdbc로 구현한거야.. 이제 이걸 spring jdbc로 바꿔볼거라고..