고인돌 프로젝트/[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>