2. Spring boot + JdbcTemplate

고인돌 프로젝트/[Step 4] Spring boot + Oracle

2. Spring boot + JdbcTemplate

고인돌개발자 2021. 8. 25. 10:32

참고, Step 1,2,3 진행 후 , DB 연결부분에 대한 학습입니다.

※ JdbcTemplate Spring 문서
- https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html

※ JdbcTemplate Spring Giude 문서 - https://spring.io/guides/gs/relational-data-access/

1. 패키지 구조, 디렉토리 구조 추가

- jdbctemplate 패키지 아래로 controller, service, dao 패키지 추가
- vo 패키지는 com.model2.spring 바로 아래 추가
- WEB-INF > view > list.jsp , list_id.jsp (기존 소스 일부 수정하여 사용)
- webapp > index.html | webapp > jsp> regist.jsp ( mapping 위치를 파라메터로 받아서 처리 하도록 )

2. 환경세팅 (pom.xml , application.properties )

src/main/resources/application.properties

# change port
# server.port = 8084

spring.mvc.view.prefix=/WEB-INF/view/
spring.mvc.view.suffix=.jsp

#oracle settings for jdbctemplate
#spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.username=scott
spring.datasource.password=tiger

/pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.4</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.model2</groupId>
	<artifactId>Model2_Spring_War</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>
	<name>Model2_Spring_War</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>com.oracle.database.jdbc</groupId>
			<artifactId>ojdbc8</artifactId>
			<scope>runtime</scope>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/org.apache.tomcat.embed/tomcat-embed-jasper -->
		<dependency>
		    <groupId>org.apache.tomcat.embed</groupId>
		    <artifactId>tomcat-embed-jasper</artifactId>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
		<dependency>
		    <groupId>org.projectlombok</groupId>
		    <artifactId>lombok</artifactId>
		    <scope>provided</scope>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-jdbc -->
		<dependency>
		    <groupId>org.springframework.boot</groupId>
		    <artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
				
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-tomcat</artifactId>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

3. 소스코드 - jdbcTemplate 패키지 하위

src/main/java/com/model2/spring/jdbctemplate/controller

package com.model2.spring.jdbctemplate.controller;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

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 com.model2.spring.jdbctemplate.service.Sv_jt_People;
import com.model2.spring.vo.Vo_People;

@Controller
@RequestMapping("/jdbctemplate")
public class Con_jt_People {
	
	Sv_jt_People sv_People;
	
	/* 생성자를 통한 Di */
	@Autowired
	public Con_jt_People(Sv_jt_People sv_People) {
		this.sv_People = sv_People;
	}
	
	
	/* People 의 값을 전부 가져오는 메서드 */
	@RequestMapping("/PeopleList")
	public String getPeopleList(Model model) {
		
		System.out.println("Hi Spring - getPeopleList - Model2_Spring_War");	
		
		List<Vo_People> list = new ArrayList<>();
	    
		list = sv_People.doReadList();
	   	
		System.out.println("2. list size"+ list.size());	
		// 리스트의 값을 Attribute 를 통해 주고 받는다.
		//request.setAttribute("people", list);
		model.addAttribute("people",list);
		model.addAttribute("path","jdbctemplate");
		
		return "jdbc/list";
		//return "/WEB-INF/view/list.jsp";	
	}
	
	/* 특정 ID 값을 져오는 메서드 */
	@RequestMapping("/PeopleId")
	public String getPeopleId(HttpServletRequest request, Model model) {
		
		System.out.println("Hi Spring - getPeopleId - Model2_Spring_War");	
		
	    String strId = request.getParameter("id");		
		Vo_People vo_People = sv_People.doReadId(strId);
	    
		model.addAttribute("vo_people",vo_People);
		model.addAttribute("path","jdbctemplate");
		
		return "jdbc/list_id";
		//return "/WEB-INF/view/list.jsp";	
	}	
	
	/* ID 를 생성하는 메서드 */
	@RequestMapping("/PeopleCreate")
	public String getPeopleCreate(@ModelAttribute Vo_People vo_People) {
		
		System.out.println("Hi Spring - getPeopleCreate - Model2_Spring_War");	
		
		int int_result = sv_People.doCreate(vo_People);
	    
		return "redirect:/jdbctemplate/PeopleList";
		//return "/WEB-INF/view/list.jsp";	
	}	

	
	/* ID 를 수정하는 메서드 */
	@RequestMapping("/PeopleUpdate")
	public String getPeopleUpdate(@ModelAttribute Vo_People vo_People) {
		
		System.out.println("Hi Spring - getPeopleUpdate - Model2_Spring_War");	
				
		int int_result = sv_People.doUpdate(vo_People);
		
		return "redirect:/jdbctemplate/PeopleList";
		//return "/WEB-INF/view/list.jsp";	
	}
	
	/* ID 를 삭제하는 메서드 */
	@RequestMapping("/PeopleDelete")
	public String getPeopleDelete(HttpServletRequest request) {
		
		System.out.println("Hi Spring - getPeopleDelete - Model2_Spring_War");	
		
		String strId = request.getParameter("id");	
		int int_result = sv_People.doDelete(strId);
		
		return "redirect:/jdbctemplate/PeopleList";
	}	
}

src/main/java/com/model2/spring/jdbctemplate/dao/Dao_jt_People.java

package com.model2.spring.jdbctemplate.dao;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import com.model2.spring.vo.Vo_People;

import lombok.extern.slf4j.Slf4j;

@Repository
@Slf4j
public class Dao_jt_People {
	
	@Autowired
	JdbcTemplate template;

	/* People 의 값을 전부 가져오는 메서드 */
	public List<Vo_People> doReadList() {
		
		String qry="";
			
		List<Vo_People> list = new ArrayList<>();
		
		try{ 
		    /* Result Set , Print */	
			qry = " select id, name, age, to_char(reg_date,'yyyy.mm.dd') as dati " 
				+" from people "
				+ " order by id ";
			
			log.info(qry);
			
			list = template.query(qry,  new BeanPropertyRowMapper<Vo_People>(Vo_People.class));
			
			log.info("doReadList"+list.size());
			
		
	   }catch (Exception e) {
			System.out.println("Error =>"+e);			
		 }finally {
		}		
		
		return list;
	}

	/* 특정 ID 값을 져오는 메서드 */	
	public Vo_People doReadId(String id) {
		
		String qry="";
			
		Vo_People vo_People=null;
		
		try{ 
		    /* Result Set , Print */	
			qry = " select id, name, age, to_char(reg_date,'yyyy.mm.dd') as dati " 
				+" from people "
				+" where id = ? "
				;
			
			log.info(qry);
			
			vo_People = template.queryForObject(qry, new BeanPropertyRowMapper<Vo_People>(Vo_People.class),id);
			
			
			log.info("doReadId"+id);
			
		
	   }catch (Exception e) {
			System.out.println("Error =>"+e);			
		 }finally {
		}		
		
		return vo_People;
	}

	/* ID 를 생성하는 메서드 */
	public int doCreate(Vo_People vo_People) {
		
		String qry="";
	
		int intI =0;
				
		 try{ 
			   
			 /* Result Set , Print */	
				qry = " Insert into people(id, name, age) "
					 +" values (? , ?, to_number(?))";
			   
				log.info(qry);
				
				intI = template.update(qry,vo_People.getId(), vo_People.getName(), vo_People.getAge());
				
		   }catch (Exception e) {
			    intI = 1;
				System.out.println("Error =>"+e);			
			}finally {			
			}	
		 
		 log.info("doCreate"); 
		 
		return intI;
	}

	/* ID 를 수정하는 메서드 */
	public int doUpdate(Vo_People vo_People) {
		
		String qry="";
				
		int intI =0;
				
		 try{ 
			   
			 /* Result Set , Print */	
				qry = " update people "
					 +" SET "
					 +" name =?, age =? "
					 +" WHERE id = ?"
					 ;					 
			     
				log.info(qry);
				
				intI = template.update(qry, vo_People.getName(), vo_People.getAge(), vo_People.getId());
				
		   }catch (Exception e) {
			    intI = 1;
				System.out.println("Error =>"+e);			
			 }finally {				
			}	
		 
		 log.info("doCreate"); 
		 
		return intI;
	}

	/* ID 를 삭제하는 메서드 */
	public int doDelete(String id) {
		
		String qry="";
		
		int intI =0;		
				
		 try{ 
			   
			 /* Result Set , Print */	
				qry = " Delete from people "
					 +" WHERE id = ?"
					 ;					 
			     
				log.info(qry);
				
				intI = template.update(qry, id);
				
		   }catch (Exception e) {
			    intI = 1;
				System.out.println("Error =>"+e);			
			 }finally {				
			}	
		 
		 log.info("doCreate"); 
		 
		return intI;
		
	}

}

src/main/java/com/model2/spring/jdbctemplate/service/Sv_jt_IfPeople.java

package com.model2.spring.jdbctemplate.service;

import java.util.List;

import com.model2.spring.vo.Vo_People;


public interface Sv_jt_IfPeople {
	
	// 전체 리스트 가져오기 
	public List<Vo_People> doReadList();
	
	// 특정 Id 리스트만 가져오기 
	public Vo_People doReadId(String id);
	
	// 데이타 로우 생성하기
	public int doCreate(Vo_People vo_People);
	
	// 특정 ID 정보 수정하기 
	public int doUpdate(Vo_People vo_People);
	
	// 특정 ID 정보 삭제하기 
	public int doDelete(String id);	

}

src/main/java/com/model2/spring/jdbctemplate/service/Sv_jt_People.java

package com.model2.spring.jdbctemplate.service;

import java.util.ArrayList;
import java.util.List;

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

import com.model2.spring.jdbctemplate.dao.Dao_jt_People;
import com.model2.spring.vo.Vo_People;

@Service
public class Sv_jt_People implements Sv_jt_IfPeople {
	
	Dao_jt_People dao_People;
	
	@Autowired
	public Sv_jt_People(Dao_jt_People dao_People) {
		this.dao_People = dao_People;
	}

	/* People 의 값을 전부 가져오는 메서드 */
	@Override
	public List<Vo_People> doReadList() {
		
		List<Vo_People> list = dao_People.doReadList();
		
		return list;
	}

	/* 특정 ID 값을 져오는 메서드 */
	@Override
	public Vo_People doReadId(String id) {
		
	  Vo_People vo_People =null;	
	  
	  /* data 유효성 검증 */
	  if(id != null) {	
		vo_People = dao_People.doReadId(id);
	  }else {
		vo_People = null;  
	  }
	  
		return vo_People;
	}

	/* ID 를 생성하는 메서드 */
	@Override
	public int doCreate(Vo_People vo_People) {
		
		int intI = dao_People.doCreate(vo_People);
		
		return intI;
	}

	/* ID 를 수정하는 메서드 */
	@Override
	public int doUpdate(Vo_People vo_People) {
		
		int intI = dao_People.doUpdate(vo_People);
		
		return intI;
	}

	/* ID 를 삭제하는 메서드 */
	@Override
	public int doDelete(String id) {

		int intI = dao_People.doDelete(id);
		
		return intI;
	}

}

/src/main/java/com/model2/spring/vo/Vo_People.java

package com.model2.spring.vo;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

/* People DB 에서 값을 받아 저장하고, View 에 던져주는 매개체 역할을 합니다. */

@Getter
@Setter
@ToString
@AllArgsConstructor // 멤버변수를 사용하는 생성자
@NoArgsConstructor  // 기본 생성자 
public class Vo_People {
	
	private String id;
	private String name;
	private String age;
	private String dati;

	}

4. 소스코드 - html, jsp

/src/main/webapp/index.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

<h1> ■모델 2 Spring </h1>

 <a href="./jsp/regist.jsp?path=jdbctemplate">● 등록 </a>
 <p>
 <a href="/jdbctemplate/PeopleList">● 조회 (삭제)</a>
   
</body>
</html>

/src/main/webapp/jsp/regist.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%
	String strPath = request.getParameter("path");
	
%>
    
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1> ■모델 2 Spring boot - <%=strPath %> </h1>

 <a href="/index.html">● Home </a>
 <p>
  
 <form name="regist" action="/<%=strPath %>/PeopleCreate" method="post"> 
	<table style="width: 200px;">
		<tr>
			<td> 구분 </td> <td align="center">입력</td> 
		</tr>
		<tr>	
			<td> ID </td>   <td><input type="text" name="id" value="" />   </td> 
		</tr>
		<tr>
			<td> Name </td> <td><input type="text" name="name" value="" /></td> 
		</tr>
		<tr>	
			<td> Age </td>  <td><input type="text" name="age" value="" />  </td> 
		</tr> 
	</table>
	<p>
	<input type="submit"  value="전송" style="width:200px;"> 

 </form>
</body>
</html>

src/main/webapp/WEB-INF/view/jdbc/list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%@ page import="java.util.*, com.model2.spring.vo.*" %>


<%		
    String strPath = (String)request.getAttribute("path");
	out.println("strPath =>"+strPath);
	List<Vo_People> list2 = (ArrayList<Vo_People>)request.getAttribute("people");	
%>


<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Select </title>
</head>
<body>


<h1> ■모델 2 , Spring boot - jdbc/list.jsp</h1>

 <a href="/index.html">● Home </a>
 <p>
 
 <table style="width: 400px;" border="0">
		<tr style="height: 40px">
			<td>ID</td>
			<td align="center">이름</td>
			<td align="center">나이</td>
			<td align="center">등록일자</td>			
			<td> 삭제 </td>
		</tr>
		
	<%
			String strId;
	
	try{
			for(Vo_People p : list2){
				strId = p.getId();	
			%>	
				<tr style="height: 40px">
				<td><a href="/<%=strPath %>/PeopleId?id=<%=strId%>"><%=strId%></a></td>
				<td align="center"><%=p.getName()%></td>
				<td align="center"><%=p.getAge()%></td>
				<td align="center"><%=p.getDati()%></td>
				<td><a href="/<%=strPath %>/PeopleDelete?id=<%=strId%>">삭제 </a></td>
			</tr>	
			<%			
			}
				
	  }catch (Exception e) {
			System.out.println("Error =>"+e);			
		 }finally {			
		}	
		
	%>	
		
		
	</table>

</body>
</html>

/src/main/webapp/WEB-INF/view/jdbc/list_id.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%@ page import="java.util.*, com.model2.spring.vo.*" %>


<%	
	String strPath = (String)request.getAttribute("path");	
    Vo_People vo_People = (Vo_People)request.getAttribute("vo_people");	
%>


<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Select </title>
</head>
<body>


<h1> ■모델 2 , Spring boot - jdbc/list_id.jsp</h1>

 <a href="/index.html">● Home </a>
 <p>

 <form name="regist" action="/<%=strPath %>/PeopleUpdate" method="post">  
 <table style="width: 400px;" border="0">
		<tr style="height: 40px">
			<td> ID </td>   <td><%=vo_People.getId() %><input type="hidden" name="id" value="<%=vo_People.getId() %>" />   </td> 
		</tr>
		<tr style="height: 40px">
			<td> Name </td> <td><input type="text" name="name" value="<%=vo_People.getName() %>" /></td> 
		</tr>
		<tr style="height: 40px">
			<td> Age </td>  <td><input type="text" name="age" value="<%=vo_People.getAge() %>" />  </td> 
		</tr>		
	</table>
<input type="submit"  value="수정" style="width:200px;"> 	
</form>
</body>
</html>

'고인돌 프로젝트 > [Step 4] Spring boot + Oracle' 카테고리의 다른 글

3. Spring boot + mybatis + logback-spring (0)	2021.08.29
1. Simple Jdbc (0)	2021.08.25

현재글2. Spring boot + JdbcTemplate

고인돌 개발자

20년 개발자로 살아가면서 모르는것도 너무 많고, 새로운것에 대한 배움도 없던 모습을 반성하며..

springboot, MVC, lamda, abstract, 모델2, foreach, Oracle, 자바, Spring, model2, Spring boot, ENUM, JSP, JDBC, 객체지향설계, 모델1, Model1, Spring Framework, java, stream,

Today :
Yesterday :

일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

고인돌 개발자