본문 바로가기
Spring 3/jdbcTemplate

Spring jdbcTemplate example

Soul-Learner 2014. 9. 19. 22:48

Spring jdbcTemplate CRUD example


개 요

스프링에서 제공하는 jdbcTemplate  을 사용하여 Oracle 데이터베이스의 EMP 테이블에 입력, 출력, 수정, 삭제하는 기본적인 방법을 예제로 작성해본다


테스트환경

Spring 3 STS, Maven, Eclipse luna, Windows 7, JDK 1.7, Tomcat 8


http://www.beingjavaguys.com/2013/07/spring-jdbc-template-with-spring-mvc.html

http://examples.javacodegeeks.com/enterprise-java/spring/jdbc/spring-jdbctemplate-example/


Spring Web MVC를 위한 기본적인 라이브러리 외에 아래의 라이브러리를 추가로 설치한다.

오라클 데이터베이스에 접속하기 위한 JDBC 라이브러리(ojdbc6.jar 등) 로컬 시스템에 있고 그 라이브러리를 pom.xml  파일에 등록하고 사용하려는 경우에는 관련 페이지를 참조하세요

pom.xml

<dependency>

<groupId>org.springframework</groupId>

<artifactId>spring-jdbc</artifactId>

<version>${org.springframework-version}</version>

</dependency>


<dependency>

        <groupId>com.oracle</groupId>

        <artifactId>ojdbc6</artifactId>

        <version>11.2.0.3</version>

 </dependency>


<!--<dependency>

<groupId>cn.guoyukun.jdbc</groupId>

<artifactId>oracle-ojdbc6</artifactId>

<version>11.2.0.3.0</version>

</dependency>-->

위의 spring-jdbc 는 http://mavenrepository.com 에서 Spring jdbc 로 검색하면 찾을 수 있으나 ojdbc6은 찾을 수 없기 때문에 로컬 시스템에 있는 JDBC 드라이버를 Local Repository 에 복사하고 <dependency>를 pom.xml 에 추가해야 한다


DataSource 를 빈 객체로 스프링 프레임워크에 등록하고 나중에 DAO 클래스를 작성할 때  DAO 클래스로 DataSource객체를 주입한다

servlet-context.xml 파일에 설정해야 하는 내용은 DataSource  설정 뿐이고 그 외에는 Annotation을 사용하여 처리한다

servlet-context.xml

<?xml version="1.0" encoding="UTF-8"?>

<beans:beans xmlns="http://www.springframework.org/schema/mvc"

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xmlns:beans="http://www.springframework.org/schema/beans"

    xmlns:mvc="http://www.springframework.org/schema/mvc" 

    xmlns:context="http://www.springframework.org/schema/context"

    xsi:schemaLocation="http://www.springframework.org/schema/mvc 

    http://www.springframework.org/schema/mvc/spring-mvc.xsd

        http://www.springframework.org/schema/beans 

        http://www.springframework.org/schema/beans/spring-beans.xsd

        http://www.springframework.org/schema/context 

        http://www.springframework.org/schema/context/spring-context.xsd">


    <!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->

<context:component-scan base-package="org.kdea.java" />

<context:component-scan base-package="org.kdea.service" />

<!-- Process annotations on registered beans like @Autowired... -->

    <context:annotation-config/>


    <!-- Enables the Spring MVC @Controller programming model -->

    <mvc:annotation-driven/>

    

    <!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->

    <resources mapping="/resources/**" location="/resources/" />


    <!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->

    <beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">

        <beans:property name="prefix" value="/WEB-INF/views/" />

        <beans:property name="suffix" value=".jsp" />

        <beans:property name="contentType" value="text/html; charset=UTF-8"/>

    </beans:bean>


    <beans:bean id="messageSource" class="org.springframework.context.support.ReloadableResourceBundleMessageSource">

        <beans:property name="basename" value="/WEB-INF/messages/Messages"/>

        <beans:property name="defaultEncoding" value="UTF-8" />

        <beans:property name="cacheSeconds" value="180"/>

</beans:bean>

<beans:bean id="validator"

class="org.springframework.validation.beanvalidation.LocalValidatorFactoryBean">

<beans:property name="validationMessageSource" ref="messageSource"/>

</beans:bean>

 

    <beans:bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource"> 

   <beans:property name="dataSourceName" value="ds"/>

   <beans:property name="URL" value="jdbc:oracle:thin:@localhost:1521:xe"/>

   <beans:property name="user" value="scott"/>

   <beans:property name="password" value="tiger"/>

    </beans:bean>  

 

</beans:beans>


위의 서블릿 설정파일에 등록된 'dataSource' 는 DB접속이 필요한 곳에서 'dataSource' 라는 이름을 사용하여 빈 객체를 사용할 수가 있다. @Autowired나 @Resource 등의 Annotation을 사용해서 자동주입(Dependency Injection)이 가능하다. 즉, 다음과 같이 사용하면 된다

@Autowired 

private DataSource dataSource;


혹은, 아래처럼

@Resource(name="dataSource")

private DataSource dataSource;



EmpController.java

package org.kdea.java;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

@Controller
@RequestMapping("/emp/")
public class EmpController {

	@Autowired
	private EmpService empService;

	@RequestMapping("list")
	public String getList(Model model){
		model.addAttribute("list",empService.getEmpList());
		return "emp/empList";
	}
	
	@RequestMapping("info")
	public String getEmp(Model model, @RequestParam int empno){
		model.addAttribute("empInfo",empService.getEmp(empno));
		return "emp/empInfo";
	}
	
	@RequestMapping("insert")  
	public String inserEmp(@ModelAttribute Emp emp) {
		if(emp.getEmpno()==0) {
			return "emp/inputForm";
		}else{
			empService.insertEmp(emp);  
			return "redirect:/emp/list";  
		}
	}  
	
	@RequestMapping("edit")  
	public ModelAndView editEmp(@RequestParam int empno,  
	   @ModelAttribute Emp emp) {  
		emp = empService.getEmp(empno);  
		return new ModelAndView("/emp/empEdit", "empInfo", emp);
	}
	
	@RequestMapping("update")  
	public String updateEmp(@ModelAttribute Emp emp) {  
		empService.updateEmp(emp);  
		return "redirect:/emp/list";
	}
	
	@RequestMapping("delete")  
	public String deleteEmp(@RequestParam int empno) {  
		empService.deleteEmp(empno);  
		return "redirect:/emp/list";  
	}  
}



EmpService.java

package org.kdea.java;

import java.util.List;

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

@Service
public class EmpService {

	@Autowired
	private EmpDAO empDao;

	public List<Emp> getEmpList(){
		return empDao.getEmpList();
	}
	
	public Emp getEmp(int empno) {
		return empDao.getEmp(empno);
	}
	
	public boolean insertEmp(Emp emp) {
		return empDao.insertEmp(emp);
	}
	
	public boolean updateEmp(Emp emp) {
		return empDao.updateEmp(emp);
	}
	
	public boolean deleteEmp(int empno) {
		return empDao.deleteEmp(empno);
	}
}



EmpDAO.java

package org.kdea.java;

import java.util.*;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Repository
public class EmpDAO {

	@Autowired
	private DataSource dataSource; // 혹은 아래처럼

	//@Resource(name="dataSource")
	//private DataSource dataSource;

	public List<Emp> getEmpList() {  
		List<Emp> list = new ArrayList<Emp>();  
		String sql = "select * from emp2";  
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);  
		list = jdbcTemplate.query(sql, new EmpRowMapper());  
		return list;  
	}
	
	public boolean insertEmp(Emp emp) {  
		  
		String sql = "INSERT INTO emp2 "+
			"(empno,ename,deptno,sal,job ) VALUES (?,?,?,?,?)";  
		  
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);  
		  
		int rows = jdbcTemplate.update(  
			sql,  
			new Object[] { emp.getEmpno(),emp.getEname(),emp.getDeptno(),
				emp.getSal(),emp.getJob()}
		);
		
		return rows>0 ? true : false;
	}
	
	public boolean deleteEmp(int empno) {  
		String sql = "delete from emp2 where empno=?";  
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);  
		int rows = jdbcTemplate.update(sql,empno);
		return rows>0 ? true : false;
	}

	public boolean updateEmp(Emp emp) {  
		  
		String sql = "UPDATE emp2 set deptno=?, sal=? where empno=?";  
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);  
		  
		int rows = jdbcTemplate.update(  
			sql,emp.getDeptno(), emp.getSal(), emp.getEmpno());  
		return rows>0 ? true : false; 
	}
	
	public Emp getEmp(int empno) {  
		String sql = "select * from emp2 where empno=?";  
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);  
		Emp emp = jdbcTemplate.queryForObject(sql, new Object[]{empno}, new EmpRowMapper());  
		return emp;  
	}  

}



Spring JdbcTemplate 를 이용하여 특정 컬럼 1개만 가져오기


사번을 이용하여 사원의 이름을 가져오는 예

String query = "select name from employee where emp_id=?";

Object[] inputs = new Object[] {empId};

String empName = getJdbcTemplate().queryForObject(query, inputs, String.class);


사번을 이용하여 부서번호를 가져오는 예

String query = "select deptno from employee where emp_id=?";

Object[] inputs = new Object[] {empId};

int deptno = getJdbcTemplate().queryForObject(query, inputs, Integer.class);


총 행수를 가져오는 예

String sql = "SELECT COUNT(*) FROM CUSTOMER"; 

int total = getJdbcTemplate().queryForInt(sql);



Emp.java

package org.kdea.java;

public class Emp {
	
	private int empno;
	private String ename;
	private java.sql.Date hiredate;
	private int deptno;
	private int sal;
	private String job;
	
	public int getEmpno() {
		return empno;
	}
	public void setEmpno(int empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public java.sql.Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(java.sql.Date hiredate) {
		this.hiredate = hiredate;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	public int getSal() {
		return sal;
	}
	public void setSal(int sal) {
		this.sal = sal;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
}



EmpRowMapper.java

package org.kdea.java;

import java.sql.ResultSet;  
import java.sql.SQLException;  
import org.springframework.jdbc.core.RowMapper;  
  
public class EmpRowMapper implements RowMapper<Emp> {

	public Emp mapRow(ResultSet rs, int line) throws SQLException {
		EmpExtractor empExtractor = new EmpExtractor();  
		return empExtractor.extractData(rs);
	}  

}



EmpExtractor.java

package org.kdea.java;

import java.sql.ResultSet;  
import java.sql.SQLException;  
import org.springframework.dao.DataAccessException;  
import org.springframework.jdbc.core.ResultSetExtractor;  
  
public class EmpExtractor implements ResultSetExtractor<Emp> {  
  
 public Emp extractData(ResultSet resultSet) throws SQLException,  
   DataAccessException {  
    
  Emp emp = new Emp();  
    
  emp.setEmpno(resultSet.getInt("EMPNO"));  
  emp.setEname(resultSet.getString("ENAME"));  
  emp.setHiredate(resultSet.getDate("HIREDATE"));  
  emp.setDeptno(resultSet.getInt("DEPTNO"));  
  emp.setSal(resultSet.getInt("SAL"));  
  emp.setJob(resultSet.getString("JOB"));  
  
  return emp;  
 }  
  
}



WEB-INF/views/emp/empList.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE htm>
<html>
<head>
<meta charset="EUC-KR">
<title>사원정보 리스트</title>
</head>
<body>
<h2>사원정보 리스트</h2>
<c:forEach var="emp" items="${list}">
	${emp.empno } 
	<a href="info.do?empno=${emp.empno}">${emp.ename}</a> <br>
</c:forEach>
<p>
<a href="insert.do">사원정보 추가</a>
</body>
</html>



WEB-INF/views/emp/empInfo.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE htm>
<html>
<head>
<meta charset="EUC-KR">
<title>사원정보 상세 페이지</title>
</head>
<body>
<h2>사원정보 상세 페이지</h2>
사번 ${empInfo.empno } <br>
이름 ${empInfo.ename } <br>
부서 ${empInfo.deptno } <br>
입사 ${empInfo.hiredate } <br>
직무 ${empInfo.job } <br>
급여 ${empInfo.sal } <br>
<p>
<a href="edit.do?empno=${empInfo.empno}">사원정보 수정</a>
<a href="delete.do?empno=${empInfo.empno}">사원정보 삭제</a>
</body>
</html>



WEB-INF/views/emp/empEdit.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>  
<%@ taglib prefix="spring" uri="http://www.springframework.org/tags" %>
<!DOCTYPE htm>
<html>
<head>
<meta charset="UTF-8">
<title>사원정보 수정 페이지</title>
</head>
<body>
<h2>사원정보 수정 페이지</h2>
 <form:form method="post" action="update" modelAttribute="empInfo">  
 <form:hidden path="empno"/>
사번 ${empInfo.empno } <br>
이름 ${empInfo.ename } <br>
부서 <form:input path="deptno"/> <br>
입사 ${empInfo.hiredate } <br>
직무 ${empInfo.job } <br>
급여 <form:input path="sal"/> <br>
<p>
<input type="submit" value="변경사항 적용">
</form:form>  
</body>
</html>



WEB-INF/views/emp/inputForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>  
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> 
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원정보 입력 폼</title>
<style>  
body {  
 font-size: 20px;  
 color: teal;  
}  
  
td {  
 font-size: 15px;  
 color: black;  
 width: 100px;  
 height: 22px;  
 text-align: left;  
}  
  
.heading {  
 font-size: 18px;  
 color: white;  
 font: bold;  
 background-color: orange;  
 border: thick;  
}  
</style>  
</head>  
<body>  
 <h2>사원정보 입력 폼 </h2>   

  <div>  
   <form:form method="post" action="insert.do" modelAttribute="emp">  
    <table>  
     <tr>  
      <td>EMPNO :</td>  
      <td><form:input path="empno" /></td>  
     </tr>  
     <tr>  
      <td>ENAME :</td>  
      <td><form:input path="ename" /></td>  
     </tr>  
     <tr>  
      <td>DEPTNO :</td>  
      <td><form:input path="deptno"/></td>  
     </tr>  
     <tr>  
      <td>JOB :</td>  
      <td><form:input path="job" /></td>  
     </tr>  
     <tr>  
      <td> </td>  
      <td><input type="submit" value="Save" /></td>  
     </tr>  
     <tr>  
        
      <td colspan="2"><a href="list">사원정보 리스트 보기</a></td>  
     </tr>  
    </table>  
   </form:form>  
  </div>  

</body>  
</html>



저작자표시

티스토리툴바