본문 바로가기
java,jsp,spring/Spring

Spring Jdbc Template

프루트 2022. 10. 14. 11:39

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

Spring Bean을 이용한 코드 간소화

설정 

  • pom.xml (version은 최신이 아닐 수 있음)
<!-- JDBC Template -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.1.4.RELEASE</version>
</dependency>
  • servlet-context.xml
<!-- Jdbc Template 설정 -->
<beans:bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">         <beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<beans:property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
<beans:property name="username" value="scott"/> <beans:property name="password" value="tiger"/> </beans:bean>
<beans:bean name="template" class="org.springframework.jdbc.core.JdbcTemplate">
<beans:property name="dataSource" ref="dataSource"/>
</beans:bean>
  • 패키지 추가 - JdbcTemplate 빈 생성 ex) util Packacge → constant.class
public static JdbcTemplate template;
  • Controller 단계 - JdbcTemplate 필드 및 setter(@Autowired) 설정
  • Dao 단계 - JdbcTemplate 참조 변수 추가 및 사용

게시판 코드 수정

1. Controller

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.*;
import com.javalec.spring_mvc_board_jdbc.util.Constant;

@Controller
public class BController {
	BService service;
	public JdbcTemplate template;
	
	// 실행되자마자 작동되게 하는 어노테이션
	@Autowired
	public void setTemplate(JdbcTemplate template) {
		this.template = template;
		Constant.template = this.template;
	}
	
	@RequestMapping("/list")
	public String list(Model model) {
		System.out.println("=======> list()");
	
		service = new BListService();
		service.execute(model);
		return "list";
	}
	@RequestMapping("/write_view") public String write_view() {
		System.out.println("=======> write_view()"); 
		return "write_view"; 
	}
	
	@RequestMapping("/write") 
	public String write(HttpServletRequest request, Model model) { 
		System.out.println("=======> write()");
	 
		model.addAttribute("request", request); 
		service = new BWriteService();
		service.execute(model);
	  
		return "redirect:list"; 
	}
	 
	@RequestMapping("/content_view") public String
	content_view(HttpServletRequest request, Model model) {
		System.out.println("=======> content_view()");
	  
		model.addAttribute("request", request); service = new BContentService();
		service.execute(model);
	 
		return "content_view"; 
	}
	  
	@RequestMapping("/modify") 
	public String modify(HttpServletRequest request,Model model) { 
		System.out.println("=======> modify()");
	
		model.addAttribute("request", request); 
		service = new BModifyService();
		service.execute(model);
		
		return "redirect:list"; 
	}
	  
	@RequestMapping("/delete") 
	public String delete(HttpServletRequest request,Model model) { 
		System.out.println("=======> delete()");
		  
		model.addAttribute("request", request); service = new BDeleteService();
		service.execute(model);
	 
		return "redirect:list"; 
	}
	
}

2. Dao

package com.javalec.spring_mvc_board_jdbc.dao;

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

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.BDto;
import com.javalec.spring_mvc_board_jdbc.util.Constant;

public class BDao {
	JdbcTemplate template = null;
	
	public BDao() {
		template = Constant.template;
	}
	
	public ArrayList<BDto> list(){
		String sql = "SELECT BID, BNAME, BTITLE, BCONTENT, BDATE, BHIT FROM MVC_BOARD";
		return (ArrayList<BDto>) template.query(sql, new BeanPropertyRowMapper(BDto.class));
	}
	
	public void write(final String bName, final String bTitle, final String bContent) {
		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); // 매개변수 final로 변경없이 사용하도록 수정 필요
				pstmt.setString(2, bTitle);
				pstmt.setString(3, bContent);
				
				return pstmt;
			}
		});
	}
	
	public BDto contentView (String strId) {
		upHit(strId);
		String sql = "SELECT BID, BNAME, BTITLE, BCONTENT, BDATE, BHIT FROM MVC_BOARD WHERE BID = "+Integer.parseInt(strId);
		return template.queryForObject(sql, new BeanPropertyRowMapper<BDto>(BDto.class));
	}
	
	private void upHit(final String strId) {
		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(strId));
			}
		});
	}
	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));
			}
		});
	}
}

3. util (추가된 패키지)

public class Constant {
	public static JdbcTemplate template;
}

4. Dao, Service, Jsp 파일은 기존과 동일

 

로그인 코드 수정

1. Controller

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.service.*;
import com.javalec.spring_test_member_jdbc.util.Constant;

@Controller
public class Memcontroller {
	MemService memService;
	public JdbcTemplate template;
	
	@Autowired
	public void setTemplate(JdbcTemplate 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);
		memService = new MemLoginService();
		int re = memService.execute(model);
		System.out.println(re);
		if (re == 1) {
			return "redirect:login_ok";
		} else {
			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("/register_ok")
	public String register_ok(HttpServletRequest request, Model model) {
		System.out.println("=======> register_ok()");
		
		model.addAttribute("request", request);
		memService = new MemRegisterService();
		int re = memService.execute(model);
		System.out.println(re);
		if (re == 1) {
			return "redirect:login";
		} else {
			return "redirect:register";
		}
	}
}

2. Dao

package com.javalec.spring_test_member_jdbc.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;

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

import com.javalec.spring_test_member_jdbc.util.Constant;

public class MemDao {
	JdbcTemplate template = null;
	
	public MemDao() {
		template = Constant.template;
	}
// 1번 방법 Dao 에서 로직 진행 후 return
	public int login(String mem_uid, String mem_pwd) {
		String sql = "SELECT MEM_PWD FROM MVC_MEMBER WHERE MEM_UID = '"+mem_uid+"'";
		int re = -1;
		try {
			String pwd = template.queryForObject(sql, String.class);
			// queryForObject를 사용할 때는 try-catch문 작성이 필요
			if (pwd != null) {
				if (pwd.equals(mem_pwd)) {
					re = 1;
				} else {
					re = 0;
				}
			} else {
				re = -1;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("아이디가 틀렸습니다.");
		}
		return re; // 쿼리 결과값과 비교되어 정해진 int 타입의 re 속성 return
	}

// 2번 방법 Service 단에서 로직 진행
public ArrayList<MemDto> login(String mem_uid, String mem_pwd) {
		String sql = "SELECT MEM_PWD FROM MVC_MEMBER WHERE MEM_UID = '"+mem_uid+"'";
		return (ArrayList<MemDto>) template.query(sql, new BeanPropertyRowMapper(MemDto.class));
	}

	public void register(final String mem_uid, final String mem_pwd, final String mem_name) {
		String sql = "INSERT INTO MVC_MEMBER VALUES(?,?,?)";
		template.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, mem_uid);
				ps.setString(2, mem_pwd);
				ps.setString(3, mem_name);
			}
		});
	}
}

3. Service (회원가입 추가)

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

// 1번 방법
public class MemLoginService implements MemService{
	@Override
	public int execute(Model model) {
		Map<String, Object> map = model.asMap();
		HttpServletRequest request = (HttpServletRequest) map.get("request");
		
		String uId = request.getParameter("mem_uid");
		String pwd = request.getParameter("mem_pwd");
		MemDao dao = new MemDao();
		int re = dao.login(uId, pwd);
		
		return re;
	}
}

// 2번 방법
public class MemLoginService implements MemService{
	@Override
	public int execute(Model model) {
		Map<String, Object> map = model.asMap();
		HttpServletRequest request = (HttpServletRequest) map.get("request");
		
		String uId = request.getParameter("mem_uid");
		String pwd = request.getParameter("mem_pwd");
		MemDao dao = new MemDao();
		int re;
		
		ArrayList<MemDto> dtos = new ArrayList<MemDto>();
		dtos = dao.login(uId, pwd);
		if (dtos.isEmpty()) {
			re = -1;
		} else {
			if (pwd.equals(dtos.get(0).getMem_pwd())) {
				re = 1;
			} else {
				re = 0;
			}
		}
		return re;
	}
}

public class MemRegisterService implements MemService {
	@Override
	public int execute(Model model) {
		Map<String, Object> map = model.asMap();
		HttpServletRequest request = (HttpServletRequest) map.get("request");
		
		String uId = request.getParameter("mem_uid");
		String pwd = request.getParameter("mem_pwd");
		String name = request.getParameter("mem_name");
		MemDao dao = new MemDao();
		dao.register(uId, pwd, name);
		return 1; // 리턴 타입이 인트인 메소드이기 때문에 임의로 값을 줌
	}
}

4. Dao, Service, Jsp 파일은 기존과 동일하고, Jsp 파일에 회원가입 추가

<body>
	<form method="post" action="register_ok">
		<table border="1">
			<tr>
				<td colspan="2">
					<h1>회원 가입 신청</h1>
				</td>
			</tr>
			<tr>
				<td>User ID</td>
				<td><input type="text" name="mem_uid" /></td>
			</tr>
			<tr>
				<td>암호</td>
				<td><input type="text" name="mem_pwd" /></td>
			</tr>
			<tr>
				<td>이름</td>
				<td><input type="text" name="mem_name" /></td>
			</tr>
			<tr>
				<td colspan="2">
					<input type="submit" value="등록" />
				</td>
			</tr>
		</table>
	</form>
</body>
저작자표시

'java,jsp,spring > Spring' 카테고리의 다른 글

Spring MyBatis로 전환  (0) 2022.10.14
MyBatis  (0) 2022.10.14
Spring 로그인 페이지 설계  (0) 2022.10.14
STS 한글 깨짐 현상 해결  (0) 2022.10.14
Spring 프로젝트 설계 - 기본 게시판  (0) 2022.10.11

'java,jsp,spring/Spring' Related Articles

티스토리툴바