Maven - Spring Project : DB연동하기(Oracle)

티스토리 뷰

Spring

Maven - Spring Project : DB연동하기(Oracle)

˙ᵕ˙ 2020. 9. 27. 03:49

DB 연동하기

spring jdbc 라이브러리 다운

<!-- spring jdbc -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.2.4.RELEASE</version>
</dependency>

was(tomcat)가 제공하는 connection pool 생성

< context.xml >

<Context>
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>
    <Resource name="jdbc/myspring" auth="Container"
              type="javax.sql.DataSource" 
              driverClassName="oracle.jdbc.driver.OracleDriver"
              url="jdbc:oracle:thin:@127.0.0.1:1521:xe"
              username="test" password="test" 
              maxTotal="20" maxIdle="10"
              maxWaitMillis="-1"/>
</Context>

connection pool type : javax.sql.DataSource

spring 설정

< spring-config.xml >

<!-- spring jdbc 관련 설정 -->
<beans:bean id="ds" class="org.springframework.jndi.JndiObjectFactoryBean">
	<beans:property name="jndiName" value="jdbc/myspring"/>
	<beans:property name="resourceRef" value="true"/>
</beans:bean>
<beans:bean id="template" class="org.springframework.jdbc.core.JdbcTemplate">
	<beans:property name="dataSource" ref="ds"/>
</beans:bean>

table 생성

Dto 생성

< EmpDTO >

package emp.dto;

public class EmpDTO {
	private String id;
	private String pass;
	private String name;
	private String addr;
	private String hiredate;
	private String grade;
	private int point;
	private String deptNo;
    
    //  setter/getter

< MyEmpRowMapper>

DB에서 가져온 데이터를 EmpDTO 리스트로 리턴

package emp.dto;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class MyEmpRowMapper implements RowMapper<EmpDTO>{
	@Override
	public EmpDTO mapRow(ResultSet rs, int rowNum) 
							throws SQLException {
		System.out.println("maprow=>"+rowNum);
		EmpDTO user = 
				new EmpDTO(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4),
						rs.getString(5), rs.getString(6)
						, rs.getInt(7), rs.getString(8));
		return user;
	}
}

Dao 생성

인터페이스

< MyEmpDAO >

package emp.dao;
import java.util.List;

import emp.dto.EmpDTO;

public interface MyEmpDAO {
	EmpDTO read(String id);
	int count();
	int insert(EmpDTO user);
	int update(EmpDTO userInfo);
	int delete(String id);
	EmpDTO login(String id,String pass);
	List<EmpDTO> getMemberList();
	List<EmpDTO> findByAddr(String addr);
}

< MyEmpDAOImpl >

package emp.dao;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import emp.dto.EmpDTO;
import emp.dto.MyEmpRowMapper;

@Repository("empdao")
public class MyEmpDAOImpl implements MyEmpDAO {
	@Autowired
	private JdbcTemplate template;

	@Override
	public int count() {
		return 
		 template.queryForObject("select count(id) from emp",
				 	Integer.class);
	}

	@Override
	public int insert(EmpDTO user) {
		String sql =
		"insert into emp values(?,?,?,?,sysdate,?,1000,?)";
		int result = 
		 template.update(sql, user.getId(),user.getPass(),
				 user.getName(),user.getAddr(),
				 user.getGrade(),user.getDeptNo());
		return result;

	}

	@Override
	public int update(EmpDTO userInfo) {
		String sql = "update emp ";
		sql = sql+"set pass=?, addr=?,grade=? ";
		sql = sql+"where id=?";
		return template.update(sql, userInfo.getPass(),
				userInfo.getAddr(),userInfo.getGrade(),
				userInfo.getId());
	}

	@Override
	public int delete(String id) {
		return template.update("delete from emp where id=?",
								id);
	
	}
	@Override
	public EmpDTO read(String id) {
		EmpDTO userInfo = null;
		try{
			userInfo = template.queryForObject(
				"select * from emp where id=?",
				new Object[]{id},new MyEmpRowMapper());	
		}catch(EmptyResultDataAccessException e){
			
		}
		return userInfo;
	}
	
	@Override
	public EmpDTO login(String id, String pass) {
		EmpDTO loginUser = null;
		try{
			loginUser = template.queryForObject(
				"select * from emp where id=? and pass=?",
				new Object[]{id,pass},new MyEmpRowMapper());	
		}catch(EmptyResultDataAccessException e){
			
		}
		return loginUser;
	}

	@Override
	public List<EmpDTO> getMemberList() {
		return template.query("select * from emp",
						new MyEmpRowMapper());
	}

	@Override
	public List<EmpDTO> findByAddr(String addr) {
		return 
		 template.query("select * from emp where addr like ?",
				 new Object[]{"%"+addr+"%"},new MyEmpRowMapper());
	}	
}

회원가입

회원가입 폼

< register.jsp >

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>

<meta charset="UTF-8">
<title>Insert title here</title>
 <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>
  <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  
<body>
	<div class="container-fluid">
			<form role="form" class="form-horizontal"
				action="/firstPro/insert.do" method="POST" 
				name="myform">
				<fieldset>
					<div id="legend">
						<legend>아래 양식을 작성해주세요.</legend>
					</div>
					<div class="form-group">
						<!-- 부서코드 -->
						<label class="control-label col-sm-2" for="orgcode">부서코드</label>
						<div class="col-sm-3">
							<input type="text" id="orgcode" name="deptNo"
								placeholder="부서코드" class="form-control"
								 required>
						</div>
					</div>

					
					
					<div class="form-group">
						<!-- 성명-->
						<label class="control-label col-sm-2" for="orgname">성명</label>
						<div class="col-sm-3">
							<input type="text" id="orgname" name="name"
								placeholder="성명" class="form-control" required>

						</div>
					</div>
					<div class="form-group">
						<!-- 아이디-->
						<label class="control-label col-sm-2" for="id">아이디</label>
						<div class="col-sm-3">
							<input type="text" id="id" name="id"
								placeholder="아이디" class="form-control" 
								minlength="4" required onkeyup="runAjax()">
							
						</div>
						<input type="button" value="아이디중복체크" class="btn btn-success"
							onclick="openidcheck()"/>
						<span id="checkVal"></span>
					</div>
			
					<div class="form-group">
						<!-- 패스워드-->
						<label class="control-label col-sm-2" for="pass">패스워드</label>
						<div class="col-sm-3">
							<input type="text" id="pass" name="pass"
								placeholder="패스워드" class="form-control" minlength="4" >

						</div>
					</div>
					<div class="form-group">
						<!-- 입사날짜-->
						<label class="control-label col-sm-2" for="addr">주소</label>
						<div class="col-sm-3">
							<input type="text" id="addr" name="addr" 
							placeholder="주소"
								class="form-control" minlength="4" required>

						</div>
					</div>
					<div class="form-group">
						<!-- 포인트-->
						<label class="control-label col-sm-2" for="point">포인트</label>
						<div class="col-sm-3">
							<input type="text" id="point" name="point" 
							placeholder="포인트"
								class="form-control" minlength="4" required>

						</div>
					</div>
					<div class="form-group">
						<!-- 등급-->
						<label class="control-label col-sm-2" for="grade">등급</label>
						<div class="col-sm-3">
							<input type="text" id="grade" name="grade" 
							placeholder="등급"
								class="form-control" minlength="4" required>

						</div>
					</div>
					<div class="form-group">
						<!-- Button -->
						<div class="col-sm-3 col-sm-offset-2">
							<input type="submit" value="가입하기" class="btn btn-success"/>
						</div>
					</div>
				</fieldset>
			</form>
	</div>
</body>
</html>

controller 생성

< InsertController.java >

package emp.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import emp.dao.MyEmpDAO;
import emp.dto.EmpDTO;


@Controller
public class InsertController {
	@Autowired
	@Qualifier("empdao")
	MyEmpDAO dao;
	
	// insert폼을 reponse하는 메소드 - get 
	@RequestMapping(value = "/insert.do", method = RequestMethod.GET)
	public String showPage() {
		return "emp/register";
	}
	
	// DB에 insert를 실행하는 메소드 - post
	// 파라미터를 String 매개변수로 정의하거나 command객체(dto, vo) 사용 할 수 있다.
	// 파라미터 : @RequestParam String id, String pass)
	@RequestMapping(value = "/insert.do", method = RequestMethod.POST)
	public String insert(EmpDTO user) {
		dao.insert(user);
		System.out.println("성공");
		return "redirect:/list.do";
	}
}

실행

회원목록보기

리스트 화면

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="java.util.*,emp.dto.*"%>
<!DOCTYPE html>
<html>
<head>
<title>Bootstrap Example</title>
<meta charset="UTF-8">
 <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>
  <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  
</head>
<body>
	<div class="container">
	<%ArrayList<EmpDTO> deptlist =
			(ArrayList<EmpDTO>) request.getAttribute("emplist"); 
	 	 int size = deptlist.size();
	  %>
		<table class="table">
			<thead>
				<tr>
					<th>사원코드</th>
					<th>사원명</th>
					<th>입사일</th>
					<th>등급</th>
					<th>주소</th>
					<th>부서코드</th>
					<th>삭제</th>
				</tr>
			</thead>
			<tbody>
			<%for(int i=0;i<size;i++){ 
				EmpDTO emp =  deptlist.get(i);%>
				<tr>
					<td><a
					 href="/firstPro/read.do?id=<%= emp.getId()%>"><%= emp.getId() %></a></td>
					<td><%= emp.getName() %></td>
					<td><%= emp.getHiredate() %></td>
					<td><%= emp.getGrade() %></td>
					<td><%= emp.getAddr() %></td>
					<td><%= emp.getDeptNo()%></td>
					<td><a 
					href="/firstPro/delete.do?id=<%= emp.getId()%>">삭제</a></td>
				</tr>
			<%}%>
			</tbody>
		</table>
	</div>

</body>
</html>

controller 생성

< ListController >

package emp.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import emp.dao.MyEmpDAO;
import emp.dto.EmpDTO;

@Controller
public class ListController {
	@Autowired
	MyEmpDAO dao;
	
	@RequestMapping("/list.do")
	public ModelAndView list() {
		List<EmpDTO> list = dao.getMemberList();
		ModelAndView mav = new ModelAndView();
		//request.setAttribute와 동일하지만 controller는 ModelAndView를 만들어야함
		mav.addObject("emplist", list); 
		mav.setViewName("emp/emp_list");
		return mav;
	}
}

실행

회원검색

회원검색 폼

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원 검색</title>
</head>

<body>
	<div align="center">
		<form method="post" action="/firstPro/search.do">
			<input type="hidden" name="test" value="paramtest">
			<h1>회원 검색</h1>
			<h3>
				주소:<input type="text" name="addr" />
				 <input type="submit"
					value="검색">
			</h3>
		</form>
	</div>

</body>
</html>

controller 생성

< SearchController >

package emp.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
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;

import emp.dao.MyEmpDAO;
import emp.dto.EmpDTO;

@Controller
public class SearchController {
	@Autowired
	MyEmpDAO dao;
	
	@RequestMapping(value = "/search.do", method = RequestMethod.GET)
	public String showPage() {
		return "emp/search";
	}
	
	@RequestMapping(value = "/search.do", method = RequestMethod.POST)
	public ModelAndView search(@RequestParam String addr) {
		List<EmpDTO> list = dao.findByAddr(addr);
		ModelAndView mav = new ModelAndView();
		mav.addObject("emplist", list);
		mav.setViewName("emp/emp_list");
		return mav;
	}
}

회원 상세보기

페이지

< emp_read.jsp >

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR" import="emp.dto.*"%>

<!DOCTYPE html>
<html>
<head>
 <title>인사관리시스템</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <style>
    /* Remove the navbar's default margin-bottom and rounded borders */ 
    .navbar {
      margin-bottom: 0;
      border-radius: 0;
    }
    
    /* Set height of the grid so .sidenav can be 100% (adjust as needed) */
    .row.content {height: 450px}
    
    /* Set gray background color and 100% height */
    .sidenav {
      padding-top: 20px;
      background-color: #f1f1f1;
      height: 100%;
    }
    
    /* Set black background color, white text and some padding */
    footer {
      background-color: #555;
      color: white;
      padding: 15px;
    }
    
    /* On small screens, set height to 'auto' for sidenav and grid */
    @media screen and (max-width: 767px) {
      .sidenav {
        height: auto;
        padding: 15px;
      }
      .row.content {height:auto;} 
    }
  </style>
</head>
<body>
<!-- top에 보여질 메뉴 -->
<%-- <jsp:include page="../layout/top.jsp"></jsp:include> --%>
  
<div class="container-fluid text-center">    
  <div class="row content">
    <div class="col-sm-2 sidenav">
    <%-- 	<jsp:include page="../layout/emp_menu.jsp"></jsp:include> --%>
    </div>
	<div class="col-sm-10">
	
			<form role="form" class="form-horizontal"
		action="/insaPro/read.do?id=${user.id}&action=UPDATE" 
		method="post">
				<fieldset>
					<div id="legend">
						<legend>아래 양식을 작성해주세요.</legend>
					</div>
					
					<div class="form-group">
						<!-- 부서코드-->
						<label class="control-label col-sm-2" for="orgtel">부서코드</label>
						<div class="col-sm-3">
							<!-- 부서코드 -->
							${user.deptNo }
						</div>
					</div>
					
					<div class="form-group">
						<!-- 사원코드 -->
						<label class="control-label col-sm-2" for="orgcode">사원코드</label>
						<div class="col-sm-3">
							<!-- 부서코드를 이곳에 출력하세요 -->
							${user.id}
						</div>
					</div>

					
					
					<div class="form-group">
						<!-- 사원명-->
						<label class="control-label col-sm-2" for="orgname">사원명</label>
						<div class="col-sm-3">
							<!-- 사원명을 이곳에 출력하세요 -->
							${user.name }
						</div>
					</div>



				


					
					<div class="form-group">
						<!-- 패스워드-->
						<label class="control-label col-sm-2" for="orgloc">패스워드</label>
						<div class="col-sm-3">
							<!-- 부서위치를 이곳에 출력하세요 -->
								${user.pass }
						</div>
					</div>
					<div class="form-group">
						<!-- 입사일-->
						<label class="control-label col-sm-2" for="orgtel">입사일</label>
						<div class="col-sm-3">
							<!-- 부서전화번호를 이곳에 출력하세요 -->
								${user.hiredate }
						</div>
					</div>

					<div class="form-group">
						<!-- 등급과 급여-->
						<label class="control-label col-sm-2" for="orgtel">등급 및 급여</label>
						<div class="col-sm-3">
							<!-- 관리자를 이곳에 출력하세요 -->
								${user.grade }(${user.point })
						</div>
					</div>
					
					<div class="form-group">
						<!-- Button -->
						<div class="col-sm-3 col-sm-offset-2">
							<input type="button" value="목록" class="btn btn-success" onclick="location.href='list.do'"/>
							<input type="submit" value="수정" class="btn btn-success"/>
						</div>
					</div>
				</fieldset>
			</form>
	</div>
	  </div>
</div>

<footer class="container-fluid text-center">
  <p>Footer Text</p>
</footer>
	
</body>
</html>

controller 생성

< ReadController.java >

package emp.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import emp.dao.MyEmpDAO;
import emp.dto.EmpDTO;

@Controller
public class ReadController {
	@Autowired
	MyEmpDAO dao;
	
	@RequestMapping("/read.do")
	public ModelAndView read(String id) {
		EmpDTO user = dao.read(id);
		ModelAndView mav = new ModelAndView();
		mav.addObject("user", user);
		mav.setViewName("emp/emp_read");
		return mav;
	}
}

실행

회원 삭제하기

controller 생성

package emp.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import emp.dao.MyEmpDAO;

@Controller
public class DeleteController {
	@Autowired
	MyEmpDAO dao;
	
	@RequestMapping("/delete.do")
	public String delete(String id) {
		dao.delete(id);
		return "redirect:/list.do";
	}
}

실행

프로젝트 구조

'Spring' 카테고리의 다른 글

Maven - Spring Project : Ajax 사용하기 (0)	2020.10.01
Maven - Spring Project : Mybatis 연동 (0)	2020.09.27
Maven - Spring Project : Eclipse data tool(DB연동 tool) (0)	2020.09.27
Maven - Spring Project : Tiles 사용 (0)	2020.09.27
Maven - Spring Project : STS설치, Tomcat서버 설정 (0)	2020.09.24

공지사항

최근에 올라온 글

최근에 달린 댓글

Total

Today

Yesterday

링크

TAG more

« 2025/01 »
일	월	화	수	목	금	토
			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	31

글 보관함

물흐르듯 개발하다 대박나기

티스토리 뷰

Maven - Spring Project : DB연동하기(Oracle)

'Spring' 카테고리의 다른 글

티스토리툴바