1. Simple Jdbc

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

1. Simple Jdbc

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

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

1. 패키지 구조, 디렉토리 구조 추가
- jdbc 패키지 아래로 controller, service, dao, vo 패키지 추가
- WEB-INF > view > list.jsp , list_id.jsp
- webapp > html > regist_jdbc.html

2. 소스코드 - jdbc 패키지 하위

src/main/java/com/model2/spring/jdbc/comm/DbConn.java

package com.model2.spring.jdbc.comm;

import java.sql.Connection;
import java.sql.DriverManager;

public class DbConn {

	
	String driver = "oracle.jdbc.driver.OracleDriver";
	String dbURL="jdbc:oracle:thin:@localhost:1521:xe";
	String user_id="scott";        
	String user_pw="tiger";
	String qry="";

	private Connection conn = null; // DB 에 connection 된 객체를 저장 
	
	public Connection getConn(){
		
		try{
			/* Driver Loading */
		    Class.forName(driver);
		    conn = DriverManager.getConnection(dbURL, user_id, user_pw);
		    
		    System.out.println("OK Connection");
			
		}catch (Exception e) {
			// TODO: handle exception
		}
		
		return conn;
	}
	
	
}

src/main/java/com/model2/spring/jdbc/controller/Con_People.java

package com.model2.spring.jdbc.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.jdbc.service.Sv_People;
import com.model2.spring.jdbc.vo.Vo_People;

@Controller
@RequestMapping("/jdbc")
public class Con_People {
	
	Sv_People sv_People;
	
	/* 생성자를 통한 Di */
	@Autowired
	public Con_People(Sv_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);
		
		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);
		
		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:/jdbc/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:/jdbc/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:/jdbc/PeopleList";
	}	
}

src/main/java/com/model2/spring/jdbc/dao/Dao_People.java

package com.model2.spring.jdbc.dao;

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

import org.springframework.stereotype.Repository;

import com.model2.spring.jdbc.comm.DbConn;
import com.model2.spring.jdbc.vo.Vo_People;

import lombok.extern.slf4j.Slf4j;

@Repository
@Slf4j
public class Dao_People {
	
	Connection conn = null; // DB 에 connection 된 객체를 저장 
    PreparedStatement ps = null;  // connection 객체에 실행문을 던지는 역할(창구)
    ResultSet rs = null;     // select 결과값을 받아옮
    
	

	/* People 의 값을 전부 가져오는 메서드 */
	public List<Vo_People> doReadList() {
		
		String qry="";
		DbConn dbConn = new DbConn();	    
		conn = dbConn.getConn();
		
		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 ";
			
			ps = conn.prepareStatement(qry);
			log.info(qry);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				// Poeple 생성자를 이용하여 값을 입력 				
				Vo_People vo_People = new Vo_People(rs.getString("id"), rs.getString("name"), rs.getString("age"), rs.getString("dati"));
				list.add(vo_People);	
				
			}
			
			log.info("doReadList"+list.size());
			
		
	   }catch (Exception e) {
			System.out.println("Error =>"+e);			
		 }finally {
			 /* Close */ 
			try {
				if(rs != null) rs.close();
				if(ps != null) ps.close();
				if(conn != null) conn.close();			
			}catch (Exception e2) {			
			}
		}		
		
		return list;
	}

	/* 특정 ID 값을 져오는 메서드 */	
	public Vo_People doReadId(String id) {
		
		String qry="";
		DbConn dbConn = new DbConn();	    
		conn = dbConn.getConn();
	
		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 = ? "
				;
			
			ps = conn.prepareStatement(qry);
			ps.setString(1, id);
			rs = ps.executeQuery();
			log.info(qry);
			if(rs.next()) {
				// Poeple 생성자를 이용하여 값을 입력 				
				vo_People = new Vo_People(rs.getString("id"), rs.getString("name"), rs.getString("age"), rs.getString("dati"));
				
			}
			
			log.info("doReadId"+id);
			
		
	   }catch (Exception e) {
			System.out.println("Error =>"+e);			
		 }finally {
			 /* Close */ 
			try {
				if(rs != null) rs.close();
				if(ps != null) ps.close();
				if(conn != null) conn.close();			
			}catch (Exception e2) {			
			}
		}		
		
		return vo_People;
	}

	/* ID 를 생성하는 메서드 */
	public int doCreate(Vo_People vo_People) {
		
		String qry="";
		DbConn dbConn = new DbConn();	    
		conn = dbConn.getConn();
		
		int intI =0;
				
		 try{ 
			   
			 /* Result Set , Print */	
				qry = " Insert into people(id, name, age) "
					 +" values (? , ?, to_number(?))";
			     
				ps = conn.prepareStatement(qry);			
				ps.setString(1, vo_People.getId());
				ps.setString(2, vo_People.getName());
				ps.setString(3, vo_People.getAge());			
				log.info(qry);
				
				intI = ps.executeUpdate();
				
		   }catch (Exception e) {
			    intI = 1;
				System.out.println("Error =>"+e);			
			 }finally {
				 /* Close */ 
				try {
					if(rs != null) rs.close();
					if(ps != null) ps.close();
					if(conn != null) conn.close();			
				}catch (Exception e2) {			
				}
			}	
		 
		 log.info("doCreate"); 
		 
		return intI;
	}

	/* ID 를 수정하는 메서드 */
	public int doUpdate(Vo_People vo_People) {
		
		String qry="";
		DbConn dbConn = new DbConn();	    
		conn = dbConn.getConn();
		
		int intI =0;
				
		 try{ 
			   
			 /* Result Set , Print */	
				qry = " update people "
					 +" SET "
					 +" name =?, age =? "
					 +" WHERE id = ?"
					 ;					 
			     
				ps = conn.prepareStatement(qry);			
				ps.setString(1, vo_People.getName());
				ps.setString(2, vo_People.getAge());			
				ps.setString(3, vo_People.getId());
				
				log.info(qry);
				intI = ps.executeUpdate();
				
		   }catch (Exception e) {
			    intI = 1;
				System.out.println("Error =>"+e);			
			 }finally {
				 /* Close */ 
				try {
					if(rs != null) rs.close();
					if(ps != null) ps.close();
					if(conn != null) conn.close();			
				}catch (Exception e2) {			
				}
			}	
		 
		 log.info("doCreate"); 
		 
		return intI;
	}

	/* ID 를 삭제하는 메서드 */
	public int doDelete(String id) {
		
		String qry="";
		DbConn dbConn = new DbConn();	    
		conn = dbConn.getConn();
		
		int intI =0;		
				
		 try{ 
			   
			 /* Result Set , Print */	
				qry = " Delete from people "
					 +" WHERE id = ?"
					 ;					 
			     
				ps = conn.prepareStatement(qry);			
				ps.setString(1, id);
				log.info(qry);
				
				intI = ps.executeUpdate();
				
		   }catch (Exception e) {
			    intI = 1;
				System.out.println("Error =>"+e);			
			 }finally {
				 /* Close */ 
				try {
					if(rs != null) rs.close();
					if(ps != null) ps.close();
					if(conn != null) conn.close();			
				}catch (Exception e2) {			
				}
			}	
		 
		 log.info("doCreate"); 
		 
		return intI;
		
	}

}

/src/main/java/com/model2/spring/jdbc/service/Sv_IfPeople.java

package com.model2.spring.jdbc.service;

import java.util.List;

import com.model2.spring.jdbc.vo.Vo_People;

public interface Sv_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/jdbc/service/Sv_People.java

package com.model2.spring.jdbc.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.jdbc.dao.Dao_People;
import com.model2.spring.jdbc.vo.Vo_People;

@Service
public class Sv_People implements Sv_IfPeople {
	
	Dao_People dao_People;
	
	@Autowired
	public Sv_People(Dao_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/jdbc/vo/Vo_People.java

package com.model2.spring.jdbc.vo;

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

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

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

	}

3. 소스코드 - html, jsp

src/main/webapp/index_jdbc.html

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

<h1> ■모델 2 Spring boot - jdbc </h1>

 <a href="./html/regist_jdbc.html">● 등록 </a>
 <p>
 <a href="/jdbc/PeopleList">● 조회 (삭제)</a>
   
</body>
</html>

/src/main/webapp/html/regist_jdbc.html

<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1> ■모델 2 Spring boot - jdbc </h1>

 <a href="/index_jdbc.html">● Home </a>
 <p>
  
 <form name="regist" action="/jdbc/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_id.jsp

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


<%		
    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_jdbc.html">● Home </a>
 <p>

 <form name="regist" action="/jdbc/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>

/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.jdbc.vo.*" %>


<%		
	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_jdbc.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="/jdbc/PeopleId?id=<%=strId%>"><%=strId%></a></td>
				<td align="center"><%=p.getName()%></td>
				<td align="center"><%=p.getAge()%></td>
				<td align="center"><%=p.getDate()%></td>
				<td><a href="/jdbc/PeopleDelete?id=<%=strId%>">삭제 </a></td>
			</tr>	
			<%			
			}
				
	  }catch (Exception e) {
			System.out.println("Error =>"+e);			
		 }finally {			
		}	
		
	%>	
		
		
	</table>

</body>
</html>

Spring lib 를 사용하지 않고, 순수 jdbc 를 이용.

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

3. Spring boot + mybatis + logback-spring (0)	2021.08.29
2. Spring boot + JdbcTemplate (0)	2021.08.25

현재글1. Simple Jdbc

고인돌 개발자

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

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

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

고인돌 개발자

1. Simple Jdbc

1. 패키지 구조, 디렉토리 구조 추가
- jdbc 패키지 아래로 controller, service, dao, vo 패키지 추가
- WEB-INF > view > list.jsp , list_id.jsp
- webapp > html > regist_jdbc.html

2. 소스코드 - jdbc 패키지 하위

3. 소스코드 - html, jsp

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

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

티스토리툴바

1. Simple Jdbc

1. 패키지 구조, 디렉토리 구조 추가 - jdbc 패키지 아래로 controller, service, dao, vo 패키지 추가 - WEB-INF > view > list.jsp , list_id.jsp - webapp > html > regist_jdbc.html

2. 소스코드 - jdbc 패키지 하위

3. 소스코드 - html, jsp

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

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

관련글

티스토리툴바

1. 패키지 구조, 디렉토리 구조 추가
- jdbc 패키지 아래로 controller, service, dao, vo 패키지 추가
- WEB-INF > view > list.jsp , list_id.jsp
- webapp > html > regist_jdbc.html