2022.10.13 Spring

sofia·2022년 11월 16일

Spring

목록 보기

8/11

Char16. Spring JDBC

1. JDBC Template를 이용한 반복 코드 줄이기

2. Spring Bean을 이용한 코드 간소화

3. JDBC Template를 이용하기 위한 설정

com.javalec.spring_test_item_jdbc.util (패키지 추가)
- Constant.java(JdbcTemplate 빈 생성)
Controller 단계
- JdbcTemplate 필드 및 setter(@Autowired) 설정
Dao 단계
- JdbcTemplate 참조변수 추가 및 사용

예제 1

pom.xm

<!-- JDBC Template -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>4.1.4.RELEASE</version>
		</dependency>

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:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd
		http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">

	<!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->
	
	<!-- Enables the Spring MVC @Controller programming model -->
	<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:bean>
	
	<context:component-scan base-package="com.javalec.spring_mvc_board_jdbc" />
	
	<!-- 추가된 부분 -->
	<beans:bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></beans:property>
		<beans:property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"></beans:property>
		<beans:property name="username" value="scott"></beans:property>
		<beans:property name="password" value="tiger"></beans:property>
	</beans:bean>

	<beans:bean name="template" class="org.springframework.jdbc.core.JdbcTemplate">
		<beans:property name="dataSource" ref="dataSource"></beans:property>
	</beans:bean>	
	<!-- 추가된 부분  끝!-->
		
</beans:beans>

Constant.java

package com.javalec.spring_mvc_board_jdbc.util;

import org.springframework.jdbc.core.JdbcTemplate;

public class Constant {
	public static JdbcTemplate template;
}

BDto,java

package com.javalec.spring_mvc_board_jdbc.dto;

import java.sql.Timestamp;

public class BDto {
	int bId;
	String bName;
	String bTitle;
	String bContent;
	Timestamp bDate;
	int bHit;
	
	
	public BDto() {//기본 생성자
		
	}
	
	//필드를 사용한 생성자
	public BDto(int bId, String bName, String bTitle, String bContent, Timestamp bDate, int bHit) {
		super();
		this.bId = bId;
		this.bName = bName;
		this.bTitle = bTitle;
		this.bContent = bContent;
		this.bDate = bDate;
		this.bHit = bHit;
	}

	public int getbId() {
		return bId;
	}
	public void setbId(int bId) {
		this.bId = bId;
	}
	public String getbName() {
		return bName;
	}
	public void setbName(String bName) {
		this.bName = bName;
	}
	public String getbTitle() {
		return bTitle;
	}
	public void setbTitle(String bTitle) {
		this.bTitle = bTitle;
	}
	public String getbContent() {
		return bContent;
	}
	public void setbContent(String bContent) {
		this.bContent = bContent;
	}
	public Timestamp getbDate() {
		return bDate;
	}
	public void setbDate(Timestamp bDate) {
		this.bDate = bDate;
	}
	public int getbHit() {
		return bHit;
	}
	public void setbHit(int bHit) {
		this.bHit = bHit;
	}
	
	
}

BDao java

package com.javalec.spring_mvc_board_jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;

import com.javalec.spring_mvc_board_jdbc.dto.*;
//jdbc.dto.*;로됨
import com.javalec.spring_mvc_board_jdbc.util.Constant;

public class BDao {
//	DataSource dataSource;
	//이미 dataSource에 대한 정보는 template에 담았음
	JdbcTemplate template = null;

	
	public BDao() {//기본 생성자 생성
		template = Constant.template;
	}
		
	
		//list.jsp 조회하는 메소드
		public ArrayList<BDto> list(){
			//1번째 방법
//			ArrayList<BDto> dtos =null;
//			String sql = "SELECT bId, bName, bTitle, bContent, bDate, bHit FROM MVC_BOARD";
//			dtos = (ArrayList<BDto>)template.query(sql, new BeanPropertyRowMapper(BDto.class));
//			return dtos;
			
			//2번째 방법
//			String sql = "SELECT bId, bName, bTitle, bContent, bDate, bHit FROM MVC_BOARD";
//			return (ArrayList<BDto>)template.query(sql, new BeanPropertyRowMapper(BDto.class));
			
			//3번째 방법
			return (ArrayList<BDto>)template.query("SELECT bId, bName, bTitle, bContent, bDate, bHit FROM MVC_BOARD", new BeanPropertyRowMapper(BDto.class));
			
			// 세 방법 전부 결과는 동일함

		}
		
		//글작성 write 메소드?
		public void write(final String bName, final String bTitle,final String bContent) {
			//temple에서 변경 없이 쓰기 위해서 final을 붙임
			template.update(new PreparedStatementCreator() {
				
				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					String sql = "INSERT INTO MVC_BOARD(bId, bName, bTitle, bContent, bHit)\r\n" + 
				               " VALUES(MVC_BOARD_SEQ.NEXTVAL,?,?,?,0)";
					PreparedStatement pstmt = con.prepareStatement(sql);
					pstmt.setString(1, bName);
					pstmt.setString(2, bTitle);
					pstmt.setString(3, bContent);
					
					return pstmt;
				}
			});
		}
//		
//		//글 내용을 보는 메소드
//		//글번호를 매개변수로 받음
		public BDto contentView(String strID) {
			upHit(strID);
			//pstmt.setInt(1, Integer.parseInt(strID))하지 않고 bId에 넣음
			String sql = "SELECT bId, bName, bTitle, bContent, bDate, bHit FROM MVC_BOARD where bId="+strID;
			return template.queryForObject(sql, new BeanPropertyRowMapper<BDto>(BDto.class))
		}
//		
//		//조회수 증가 메소드
//		//어짜피 내부에서만 돌아가면 되는거라서 private로 메소드 생성함
		private void upHit(final String bId) {
			String sql = "update MVC_BOARD set bHit = bHit + 1 where bId=?";
			template.update(sql,new PreparedStatementSetter() {
				
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setInt(1, Integer.parseInt(bId));//글번호 세팅, 캐스팅
					
				}
			});
		}
//		
//		//수정 메소드
//		//매개변수로 content_view에서 보낸 데이터들을 다 받는다.
		public void modify(final String bId, final String bName, final String bTitle, final String bContent) {
			String sql = "update MVC_BOARD set bName = ?, bTitle = ?, bContent = ? where bId=?";
			template.update(sql, new PreparedStatementSetter() {
				
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, bName);//작성자 세팅
					ps.setString(2, bTitle);//글제목 세팅
					ps.setString(3, bContent);//글내용 세팅
					ps.setInt(4, Integer.parseInt(bId));//글번호 세팅, 캐스팅
					
				}
			});
		}
//		
//		
		public void delete(final String strID) {
			String sql = "delete from MVC_BOARD where bId=?";
			template.update(sql, new PreparedStatementSetter() {
				
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setInt(1, Integer.parseInt(strID));//글번호 세팅, 캐스팅
					
				}
			});
		}
}

BService.java(인터페이스)

package com.javalec.spring_mvc_board_jdbc.service;

import org.springframework.ui.Model;

public interface BService {
	public void execute(Model model); //선언
}

BwriteService.java

package com.javalec.spring_mvc_board_jdbc.service;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.ui.Model;

import com.javalec.spring_mvc_board_jdbc.dao.*;

public class BwriteService implements BService {
	@Override
	//이 메소드는 BController의 public String write(HttpServletRequest request, Model model) 로 보냄
	public void execute(Model model) {
		//Model에서 끌어와서 dao로 끌어보내야함
		Map<String, Object> map = model.asMap();
		HttpServletRequest request = (HttpServletRequest) map.get("request");
		//모델에 리퀘스트를 담아서 커맨드로 보내면 됨
		
		//꺼내서 변수로 담음
		String bName = request.getParameter("bName");
		String bTitle = request.getParameter("bTitle");
		String bContent = request.getParameter("bContent");
		
		//그후 BDao로 받게 함
		BDao dao = new BDao();
		dao.write(bName, bTitle, bContent);
		
	}
}

BModifyService.java

package com.javalec.spring_mvc_board_jdbc.service;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.ui.Model;

import com.javalec.spring_mvc_board_jdbc.dao.*;

public class BModifyService implements BService{

	@Override
	public void execute(Model model) {
		Map<String, Object> map = model.asMap();
		HttpServletRequest request =(HttpServletRequest) map.get("request");
		
		//request.getParameter로 데이터 값들을 받아옴
		String bId = request.getParameter("bId");
		String bName = request.getParameter("bName");
		String bTitle = request.getParameter("bTitle");
		String bContent = request.getParameter("bContent");
		
		//DAO객체 생성하여 COMMAND와 연결
		BDao dao =new BDao();
		dao.modify(bId, bName, bTitle, bContent);
	}

}

BListService.java

package com.javalec.spring_mvc_board_jdbc.service;

import java.util.ArrayList;

import org.springframework.ui.Model;

import com.javalec.spring_mvc_board_jdbc.dao.*;
import com.javalec.spring_mvc_board_jdbc.dto.*;
//jdbc.dto.*;, jdbc.dao.*로됨

public class BListService implements BService{
	@Override
	public void execute(Model model) {
		//DAO 단 호출(패키치 DAO쪽 호출)
		BDao dao = new BDao();
		ArrayList<BDto> dtos = dao.list();//list 호출
		//모델 객체에 삽입
		model.addAttribute("list",dtos);
	}

}

BDeleteService.java

package com.javalec.spring_mvc_board_jdbc.service;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.ui.Model;

import com.javalec.spring_mvc_board_jdbc.dao.*;

public class BDeleteService implements BService{
	@Override
	public void execute(Model model) {
		Map<String, Object> map = model.asMap();
		
		HttpServletRequest request =(HttpServletRequest) map.get("request");
		String bId = request.getParameter("bId");
		//동일하게 content_view의 삭제 쿼리스트링의 bid를 가지고 와야함
		
		BDao dao = new BDao();
		dao.delete(bId);//18번 줄의 String bId
	}

}

BContentService.java

package com.javalec.spring_mvc_board_jdbc.service;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.ui.Model;

import com.javalec.spring_mvc_board_jdbc.dao.*;
import com.javalec.spring_mvc_board_jdbc.dto.*;

public class BContentService implements BService{

	@Override
	public void execute(Model model) {
		//MAP 선언 후에 이  map 안에다가 모델 객체를 담음(요청처리
		Map<String, Object> map = model.asMap();
		//get으로 꺼냄
		HttpServletRequest request =(HttpServletRequest) map.get("request");
		
		//글번호를 가지고 옴
		//list.jsp의 bId로 가지고 와야함
		String bId = request.getParameter("bId");
		//list.jsp에서 제목 클릭시 글내용을 조회할 수있는 <a href></a>에서의 쿼리스트링에서 받은 bId를 가지고 와야함
		
		//BDao 호출하여 데이터 처리
		BDao dao =new BDao();
		//BAO에서 처리한 데이터를 객체화?
		BDto dto = dao.contentView(bId);//내용을 보여줌
//		//dao의 contentView메소드의 리턴값을 받아옴
//		
//		//결과값을 넘겨줘야하므로 model에 집어넣음
		model.addAttribute("content_view",dto);
	}

}

BController,java

package com.javalec.spring_mvc_board_jdbc.controller;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import com.javalec.spring_mvc_board_jdbc.service.BContentService;
import com.javalec.spring_mvc_board_jdbc.service.BDeleteService;
//import com.javalec.spring_mvc_board.command.BCommand;
//import com.javalec.spring_mvc_board.command.BContentCommand;
//import com.javalec.spring_mvc_board.command.BDeleteCommand;
//import com.javalec.spring_mvc_board.command.BListCommand;
//import com.javalec.spring_mvc_board.command.BModifyCommand;
//import com.javalec.spring_mvc_board.command.BWriteCommand;
import com.javalec.spring_mvc_board_jdbc.service.BListService;
import com.javalec.spring_mvc_board_jdbc.service.BModifyService;
import com.javalec.spring_mvc_board_jdbc.service.BService;
import com.javalec.spring_mvc_board_jdbc.service.BwriteService;
import com.javalec.spring_mvc_board_jdbc.util.Constant;

@Controller //어노테이션 -> 컨트롤러  생성
public class BController {
//	BCommand command;//인터페이스? 생성?
	BService service;
	public JdbcTemplate template;
	
	
	@Autowired
	public void setTemplate(JdbcTemplate template) {
		//메소드의 매개변수인 template는 servlet-context.xml의  name의 template
		this.template = template;
		Constant.template = this.template;
	}

	//목록 조회
	@RequestMapping("/list")//url 받음
	public String list(Model model) {
		System.out.println("@@@### list()");

//		command = new BListCommand();
//		command.execute(model);//호출
		
		//service 단 호출(패키지 service 쪽 호출)
		service = new BListService();
		//service로(인터페이스) 수정 삭제 삽입등을 받으면 됨. 왜냐 인터페이스로 상속 받을 예정이기 때문
		service.execute(model);//호출
		
		return "list";//결과값 받아서 화면 출력해야하기 때문에 list생성해야함
		
	}
	
	//글작성 페이지
	@RequestMapping("/write_view")//url 받음
	public String write_view() {
		System.out.println("@@@### write_view()");

		return "write_view";//글쓰기 폼으로 이동
		
	}
	
	//글작성
	@RequestMapping("/write")//url 받음
	public String write(HttpServletRequest request, Model model) {
		System.out.println("@@@### write()");
		
		//Model에 데이터 삽입
		model.addAttribute("request",request);
//		command = new BwriteService();
//		command.execute(model);
		
		service = new BwriteService();
		service.execute(model);
		
		
		return "redirect:list";//글 목록 폼으로 이동
	}

	
	
//	//글 조회
	@RequestMapping("/content_view")//url 받음
	public String content_view(HttpServletRequest request, Model model) {
		System.out.println("@@@### content_view()");
		
		//Model에 데이터(request)를 담아서 보냄.(BContentCommand로)
		model.addAttribute("request",request);
		//"request"는 BContentCommand.java의 20번째 줄의 "request"(글번호 가지고 오는 부분)
		//뒤의 request는 content_view 메소드의 매개변수인 HttpServletRequest request부분
		
//		command = new BContentCommand();
//		command.execute(model);
		
		service = new BContentService();
		service.execute(model);
		//커맨드 호출필요
		
		return "content_view";//내용보는 페이지로 이동
	}
//	
//	
//	//글수정
	@RequestMapping("/modify")//url 받음
	public String modify(HttpServletRequest request, Model model) {
		System.out.println("@@@### modify()");
		
		model.addAttribute("request",request);
		//command단 호출
		//Interface command 사용
//		command = new BModifyCommand();
//		command.execute(model);//호출
		
		service = new BModifyService();
		service.execute(model);
		
		return "redirect:list";//글 목록 폼으로 이동
	}
//	
//	
//	
//	//글 삭제
	@RequestMapping("/delete")//url 받음
	public String delete(HttpServletRequest request, Model model) {
		//여기 매개변수의 request는 bId가 들어있음
		System.out.println("@@@### delete()");
		
		model.addAttribute("request",request);
		
//		command = new BDeleteCommand();
//		command.execute(model);//호출
		
		service = new BDeleteService();
		service.execute(model);
		
		return "redirect:list";//글 목록 폼으로 이동
	}
}

content_view.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<table width="500" border="1">
		<form method="post" action="write"><!-- BController에서 write로 호출 해야함 -->
			<tr>
				<td>이름</td>
				<td>
					<input type="text" name="bName" size="50">
				</td>
			</tr>
			<tr>
				<td>제목</td>
				<td>
					<input type="text" name="bTitle" size="50">
				</td>
			</tr>
			<tr>
				<td>내용</td>
				<td>
					<textarea rows="10" name="bContent"></textarea>
				</td>
			</tr>
			<tr>
				<td colspan="2">
					<input type="submit" value="입력">
				</td>
			</tr>
		</form>
	</table>
</body>
</html>

list.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<table width="500" border="1">
		<form method="post" action="write"><!-- BController에서 write로 호출 해야함 -->
			<tr>
				<td>이름</td>
				<td>
					<input type="text" name="bName" size="50">
				</td>
			</tr>
			<tr>
				<td>제목</td>
				<td>
					<input type="text" name="bTitle" size="50">
				</td>
			</tr>
			<tr>
				<td>내용</td>
				<td>
					<textarea rows="10" name="bContent"></textarea>
				</td>
			</tr>
			<tr>
				<td colspan="2">
					<input type="submit" value="입력">
				</td>
			</tr>
		</form>
	</table>
</body>
</html>

write_view.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<table width="500" border="1">
		<form method="post" action="write"><!-- BController에서 write로 호출 해야함 -->
			<tr>
				<td>이름</td>
				<td>
					<input type="text" name="bName" size="50">
				</td>
			</tr>
			<tr>
				<td>제목</td>
				<td>
					<input type="text" name="bTitle" size="50">
				</td>
			</tr>
			<tr>
				<td>내용</td>
				<td>
					<textarea rows="10" name="bContent"></textarea>
				</td>
			</tr>
			<tr>
				<td colspan="2">
					<input type="submit" value="입력">
				</td>
			</tr>
		</form>
	</table>
</body>
</html>

예제 2

pom.xm

<!-- JDBC Template -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>4.1.4.RELEASE</version>
		</dependency>

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:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd
		http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">

	<!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->
	
	<!-- Enables the Spring MVC @Controller programming model -->
	<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:bean>
	
	<context:component-scan base-package="com.javalec.spring_mvc_board_jdbc" />
	
	<!-- 추가된 부분 -->
	<beans:bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></beans:property>
		<beans:property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"></beans:property>
		<beans:property name="username" value="scott"></beans:property>
		<beans:property name="password" value="tiger"></beans:property>
	</beans:bean>

	<beans:bean name="template" class="org.springframework.jdbc.core.JdbcTemplate">
		<beans:property name="dataSource" ref="dataSource"></beans:property>
	</beans:bean>	
	<!-- 추가된 부분  끝!-->
		
</beans:beans>

Constant.java

package com.javalec.spring_test_item_jdbc.util;

import org.springframework.jdbc.core.JdbcTemplate;

public class Constant {
	public static JdbcTemplate template;
	
	
}

ItemDto.java

package com.javalec.spring_test_item_jdbc.dto;

public class ItemDto {
	String name;
	int price;
	String description;
	
	public ItemDto() {
		// TODO Auto-generated constructor stub
	}
	
	public ItemDto(String name, int price, String description) {
		this.name = name;
		this.price = price;
		this.description = description;
	}

	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	
}

ItemDao.java

package com.javalec.spring_test_item_jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;

import com.javalec.spring_test_item_jdbc.util.Constant;
import com.javalec.spring_test_item_jdbc.dto.*;

public class ItemDao {
	JdbcTemplate template = null;
	
	public ItemDao() {
		template = Constant.template;
	}
	
	//작성
	public void write(final String name, final int price, final String description) {
		template.update(new PreparedStatementCreator() {
			
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				String sql="insert into item(name, price, description) values(?,?,?)";
				PreparedStatement pstmt = con.prepareStatement(sql);
				pstmt.setString(1, name);
				pstmt.setInt(2, price);
				pstmt.setString(3, description);
				return pstmt;
			}
		});
	}

	public ArrayList<ItemDto> contentView(){
		//내가 한 방법
//		ArrayList<ItemDto> dtos =null;
//		String sql="select name, price, description from item";
//		dtos =(ArrayList<ItemDto>)template.query(sql, new BeanPropertyRowMapper(ItemDto.class));
//		
//		return dtos;

		//강사님이 한 방법
		String sql="select name, price, description from item";
		return (ArrayList<ItemDto>)template.query(sql, new BeanPropertyRowMapper(ItemDto.class));
	}
}

ItemController.java

package com.javalec.spring_test_item_jdbc.controller;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import com.javalec.spring_test_item_jdbc.util.Constant;
import com.javalec.spring_test_item_jdbc.service.ItemContentService;
import com.javalec.spring_test_item_jdbc.service.ItemService;
import com.javalec.spring_test_item_jdbc.service.ItemWriteService;

@Controller
public class ItemController {
	ItemService service;
	
	public JdbcTemplate template;
	
	
	@Autowired
	public void setTemplate(JdbcTemplate template) {
		//메소드의 매개변수인 template는 servlet-context.xml의  name의 template
		this.template = template;
		Constant.template = this.template;
	}
	
	@RequestMapping("/write")
	public String write() {
		System.out.println("@@@### write()");
		
		return "itemWrite";
	}
	/////////////////////////////////////////////////////////////
	
	
	@RequestMapping("/writeResult")
	public String writeResult(HttpServletRequest request, Model model) {
		System.out.println("@@@### writeResult()");
		
		model.addAttribute("request", request);
		service = new ItemWriteService();
		service.execute(model);
		
		return "writeResult";
	}
	
	@RequestMapping("/content_view")
	public String content_view(Model model) {
		System.out.println("@@@### content_view()");

		service = new ItemContentService();
		service.execute(model);
		
		return "content_view";
	}
	
}

ItemService.java(인터페이스)

package com.javalec.spring_test_item_jdbc.service;

import org.springframework.ui.Model;

public interface ItemService {
	public void execute(Model model);
}

ItemContentService.java

package com.javalec.spring_test_item_jdbc.service;

import java.util.ArrayList;

import org.springframework.ui.Model;

import com.javalec.spring_test_item_jdbc.dao.*;
import com.javalec.spring_test_item_jdbc.dto.*;

public class ItemContentService implements ItemService{

	@Override
	public void execute(Model model) {
		ItemDao dao = new ItemDao();
		ArrayList<ItemDto> dtos = dao.contentView();
		model.addAttribute("content_view", dtos);
	}

}

ItemWriteService.java

package com.javalec.spring_test_item_jdbc.service;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.ui.Model;

import com.javalec.spring_test_item_jdbc.dao.*;

public class ItemWriteService implements ItemService{

	@Override
	public void execute(Model model) {
		Map<String, Object> map = model.asMap();
		HttpServletRequest request = (HttpServletRequest) map.get("request");
		
		String name = request.getParameter("name");
		int price = Integer.parseInt(request.getParameter("price"));
		String description = request.getParameter("description");
		
		ItemDao dao = new ItemDao();
		dao.write(name, price, description);
	}

}

content_view.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 html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<table width="500" border="1">
		<tr>
			<td>상품명</td>
			<td>가격</td>
			<td>설명</td>
		</tr>
		<c:forEach items="${content_view}" var="dto">
			<tr>
				<td>${dto.name}</td>
				<td>${dto.price}</td>
				<td>${dto.description}</td>
			</tr>
		</c:forEach>
	</table>
</body>
</html>

itemWrite.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<form method="post" action="writeResult">
		<table>
			<tr>
				<td>상품명</td>
				<td>
					<input type="text" name="name">
				</td>
			</tr>
			<tr>
				<td>가격</td>
				<td>
					<input type="text" name="price">
				</td>
			</tr>
			<tr>
				<td>설명</td>
				<td>
					<input type="text" name="description">
				</td>
			</tr>
			<tr>
				<td colspan="2" align="center">
					<input type="submit" value="전송">
					<input type="reset" value="취소">
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

writeResult.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<a href="content_view">결과 보기</a>
</body>
</html>

예제 3

pom.xm

<!-- JDBC Template -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>4.1.4.RELEASE</version>
		</dependency>

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:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd
		http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">

	<!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->
	
	<!-- Enables the Spring MVC @Controller programming model -->
	<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:bean>
	
	<context:component-scan base-package="com.javalec.spring_mvc_board_jdbc" />
	
	<!-- 추가된 부분 -->
	<beans:bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></beans:property>
		<beans:property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"></beans:property>
		<beans:property name="username" value="scott"></beans:property>
		<beans:property name="password" value="tiger"></beans:property>
	</beans:bean>

	<beans:bean name="template" class="org.springframework.jdbc.core.JdbcTemplate">
		<beans:property name="dataSource" ref="dataSource"></beans:property>
	</beans:bean>	
	<!-- 추가된 부분  끝!-->
		
</beans:beans>

Constant.java

package com.javalec.spring_test_member_jdbc.util;

import org.springframework.jdbc.core.JdbcTemplate;

public class Constant {
	public static JdbcTemplate template;
}

MemDto.java

package com.javalec.spring_test_member_jdbc.dto;

public class MemDto {
	String mem_uid;
	String mem_pwd;
	String mem_name;
	
	
	public MemDto(String mem_uid, String mem_pwd, String mem_name) {
		super();
		this.mem_uid = mem_uid;
		this.mem_pwd = mem_pwd;
		this.mem_name = mem_name;
	}

	public String getMem_uid() {
		return mem_uid;
	}

	public void setMem_uid(String mem_uid) {
		this.mem_uid = mem_uid;
	}

	public String getMem_pwd() {
		return mem_pwd;
	}

	public void setMem_pwd(String mem_pwd) {
		this.mem_pwd = mem_pwd;
	}

	public String getMem_name() {
		return mem_name;
	}

	public void setMem_name(String mem_name) {
		this.mem_name = mem_name;
	}

	public MemDto() {
		
	}
	
	
}

MemDao.java

package com.javalec.spring_test_member_jdbc.dao;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;

import com.javalec.spring_test_member_jdbc.dto.MemDto;
import com.javalec.spring_test_member_jdbc.util.Constant;

public class MemDao {
	JdbcTemplate template = null;
	
	public MemDao() {
		template = Constant.template;
	}
	
	public int loginYn( String id, String pw) {

		
		String sql = "SELECT mem_pwd FROM MVC_MEMBER WHERE mem_uid='"+id+"'";
		int re = -1;
		
		
		try {
			
			String db_pwd = template.queryForObject(sql, String.class);//String 변수로 받을 class(
			if (db_pwd.equals(pw)) {//패스워드도  일치
				re = 1;
			}else {//패스워드가 불일치
				re=0  ;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return re;
	}
	
	
	   public void write(final String mem_uid, final String mem_pwd, final String mem_name) {
		      template.update(new PreparedStatementCreator() {
		         
		         @Override
		         public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
		            String sql = "INSERT INTO MVC_MEMBER(mem_uid, mem_pwd, mem_name)\r\n" + 
		                     " VALUES(?,?,?)";
		            PreparedStatement pstmt = con.prepareStatement(sql);
		            pstmt.setString(1, mem_uid);
		            pstmt.setString(2, mem_pwd);
		            pstmt.setString(3, mem_name);
		            
		            return pstmt;
		         }
		      });
		   }
	
}

MemController.java

package com.javalec.spring_test_member_jdbc.controller;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import com.javalec.spring_test_member_jdbc.util.Constant;
import com.javalec.spring_test_member_jdbc.service.*;

@Controller
public class MemController {
	MemService service;
	public JdbcTemplate template;
	
	@Autowired
	public void setTemplate(JdbcTemplate template) {
		//메소드의 매개변수인 template는 servlet-context.xml의  name의 template
		this.template = template;
		Constant.template = this.template;
	}
	
	
	@RequestMapping("/login")
	public String login() {
		System.out.println("###@@@ login()");
		
		return "login";
	}
	
	@RequestMapping("/login_yn")
	public String login_yn(HttpServletRequest request, Model model) {
		System.out.println("###@@@ login_yn()");
		
		model.addAttribute("request",request);
		service =  new MemLoginService();
		int result = service.execute(model);
		
		//분기처리
		if (result == 1) {
			return "redirect:login_ok";//로그인 성공시 이동
		}
		return "redirect:login";//로그인 실패시 그자리에 그대로 있음
	}
	
	@RequestMapping("/login_ok")
	public String login_ok() {
		System.out.println("###@@@ login_ok()");
		
		return "login_ok";
	}
	
	
	@RequestMapping("/register")
	public String register() {
		System.out.println("###@@@ register()");
		
		return "register";
	}

	
	@RequestMapping("/registerOk")
	public String registerOk(HttpServletRequest request, Model model) {// 값을 받기위해 파라미터 넣음
		System.out.println("###@@@ registerOk");
		
		model.addAttribute("request",request);
		service = new MWriteService();
		service.execute(model);
		
		return "redirect:login";
	}
		
	
}

MemService.java(인터페이스)

package com.javalec.spring_test_member_jdbc.service;

import org.springframework.ui.Model;

public interface MemService {
	public int execute(Model model);
}

MWriteService.java

package com.javalec.spring_test_member_jdbc.service;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.ui.Model;

import com.javalec.spring_test_member_jdbc.dao.MemDao;

public class MWriteService implements MemService{

	@Override
	public int execute(Model model) {
		Map<String, Object> map = model.asMap();
		HttpServletRequest request = (HttpServletRequest) map.get("request");
		
		String mem_uid = request.getParameter("mem_uid");
		String mem_pwd = request.getParameter("mem_pwd");
		String mem_name = request.getParameter("mem_name");
		
		
		MemDao dao = new MemDao();
		dao.write(mem_uid, mem_pwd, mem_name);
		
		return 0;
	}

}

MemLoginService.java

package com.javalec.spring_test_member_jdbc.service;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.ui.Model;

import com.javalec.spring_test_member_jdbc.dao.*;

public class MemLoginService implements MemService {

	@Override
	public int execute(Model model) {
		Map<String, Object> map = model.asMap();
		HttpServletRequest request = (HttpServletRequest) map.get("request");
		
		String mId = request.getParameter("mem_uid");
		String mPw = request.getParameter("mem_pwd");
		
		MemDao dao = new MemDao();//객체 생성
		int re = dao.loginYn(mId, mPw);
		
		return re;
	}

}

login_ok.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<h1>login ok~~!!!</h1>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<table border="1"  align="center">
		<form method="post" action="login_yn" >
			<tr height="30">
         		<td width="100">
         			사용자 ID 
         		</td>
         		<td width="100">
         			<input type="text" name="mem_uid">
         		</td>
     		</tr>
     		<tr>
         		<td width="100">
         		 	비밀번호 
         		</td>
         		<td width="100">
         			 <input type="text" name="mem_pwd" >
         		</td>
     		</tr>
     		<tr>
     			<td colspan = "2" align="center">
         			<input type="submit" value="로그인">
         			 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
         			 <!-- <input type="button" value="회원가입"> -->
         			 <a href="register">회원 가입</a>
         		</td>
     		</tr>
		</form>
	</table>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<table  border="1" align ="center">
		<form method="post" action="registerOk">
			<tr height="50">
         		<td colspan = "2">
         			<h1>회원 가입 신청</h1>
         		</td>
     		</tr>
     		
     		<tr height="30">
         		<td width="80">User ID</td >
     			<td><input type="text" size ="20" name="mem_uid" ></td>
     		</tr>
     		
     		<tr height="30">
         		<td width="80">암호</td>
     			<td><input type="password" size ="20" name="mem_pwd"></td>
     		</tr>
     		<tr height="30">
         		<td width="80">이     름</td>
     			<td><input type="text" size ="20" name= "mem_name"></td>
     		</tr>
     			<td colspan = "2">
         		 	<input type="submit" value="등록">
         		</td>
     		</tr>
		</form>
	</table>
</body>
</html>

또는

MemLoginService.java

package com.javalec.spring_test_member_jdbc.service;

import java.util.ArrayList;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.ui.Model;

import com.javalec.spring_test_member_jdbc.dao.*;
import com.javalec.spring_test_member_jdbc.dto.MemDto;

public class MemLoginService implements MemService {

	@Override
	public int execute(Model model) {
		Map<String, Object> map = model.asMap();
		HttpServletRequest request = (HttpServletRequest) map.get("request");
		
		String mId = request.getParameter("mem_uid");
		String mPw = request.getParameter("mem_pwd");
		int re;
		
		MemDao dao = new MemDao();//객체 생성
//		int re = dao.loginYn(mId, mPw);
		
		
		//MemDao.java의 loginYn 메소드 위의 부분(검증 안하는 부분)에서 넘어온 결과를 서비스단인 MemLoginService에서 확인
		ArrayList<MemDto> dtos = dao.loginYn(mId, mPw);
		//비밀번호 검증
		if (dtos.isEmpty()) {
			re=-1;
		}else {
			if (mPw.equals(dtos.get(0).getMem_pwd())) {
				re = 1;
			}else {
				re =0;
			}
		}
		
		return re;
	}

}

MemDao.java

package com.javalec.spring_test_member_jdbc.dao;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;

import com.javalec.spring_test_member_jdbc.dto.MemDto;
import com.javalec.spring_test_member_jdbc.util.Constant;

public class MemDao {
	JdbcTemplate template = null;
	
	public MemDao() {
		template = Constant.template;
	}
	
//	public int loginYn( String id, String pw) {
	public ArrayList<MemDto> loginYn( String id, String pw) {
	String sql = "SELECT mem_pwd FROM MVC_MEMBER WHERE mem_uid='"+id+"'";
	return (ArrayList<MemDto>) template.query(sql, new BeanPropertyRowMapper(MemDto.class));
	//캐스팅
	// 위의 경우 받는 쪽인(서비스단)에서 처리되도록 함.
	// 비밀번호 검증 안함 
	
	
	//비밀번호검증까지 함
//	String sql = "SELECT mem_pwd FROM MVC_MEMBER WHERE mem_uid='"+id+"'";
//		int re = -1;
//		
//		try {
//			String db_pwd = template.queryForObject(sql, String.class);//String 변수로 받을 class(
//			if (db_pwd.equals(pw)) {//패스워드도  일치
//				re = 1;
//			}else {//패스워드가 불일치
//				re=0  ;
//			}
//			
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//		return re;
	}
	
	
	   public void write(final String mem_uid, final String mem_pwd, final String mem_name) {
		      template.update(new PreparedStatementCreator() {
		         
		         @Override
		         public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
		            String sql = "INSERT INTO MVC_MEMBER(mem_uid, mem_pwd, mem_name)\r\n" + 
		                     " VALUES(?,?,?)";
		            PreparedStatement pstmt = con.prepareStatement(sql);
		            pstmt.setString(1, mem_uid);
		            pstmt.setString(2, mem_pwd);
		            pstmt.setString(3, mem_name);
		            
		            return pstmt;
		         }
		      });
		   }
	
}

sofia

이전 포스트

2022.10.12 Spring

다음 포스트

2022.10.13 Spring

Spring

Char16. Spring JDBC

1. JDBC Template를 이용한 반복 코드 줄이기

2. Spring Bean을 이용한 코드 간소화

3. JDBC Template를 이용하기 위한 설정

예제 1

예제 2

예제 3

2022.10.12 Spring

2022.10.14 Spring

0개의 댓글