JSP 데이터베이스 프로그래밍

JDBC

-Java Database Connectivity

-자바에서 DB 프로그래밍을 하기 위해 사용되는 API

 

JDBC API 사용 어플리케이션의 기본 구성

 

 

 

JDBC 드라이버

- JDBC 드라이버 : 각 DBMS에 알맞는 클라이언트

- DBMS와 통신을 담당하는 자바 클래스

- DMBS 별로 알맞은 JDBC 드라이버 필요 (jar)

- 로딩코드 : Class.forName(“JDBC드라이버 이름”);

• MySQL : com.mysql.jdbc.Driver

• 오라클 : oracle.jdbc.driver.OracleDriver

• MSSQL : com.microsoft.sqlserver.jdbc.SQLServerDriver

 

 

자바는 어떤 종류의 DB든 해당 드라이버만 있으면

그 DB에 접근 할 수 있는 방법을 제공해준다.

즉 우리는 해당 드라이버만 Library에 추가해주면 된다.

나는 데이터베이스를 오라클을 사용할 거기 때문에 ojdbc를 사용하겠다.

 

 

 

예제 세팅

테이블만들기

https://github.com/gks930620/study_filezip의 study-DB부분에서 

jar파일을 다운받아 lib에 추가하자.

 

자, 자바에서 오라클 DB에 접근할 준비는 끝났다.

이제 DB에서 JSP관련 실습을 위한 오라클 유저와 실습에 사용할 테이블을 만들어보자.

다운받은 free_board.sql, member.sql을 DB에서 실행하자

 

 

테이블을 만들었으면 임시로 데이터를 넣어주자. 

 INSERT INTO free_board (
     bo_no,bo_title,bo_category,bo_writer
    ,bo_pass,bo_content, bo_hit
    ,bo_reg_date,bo_mod_date,bo_del_yn
) VALUES (
  SEQ_FREE_BOARD.nextval, '제목1', 'BC01','한창희'
  ,'1004','내용은 길게',0
  ,sysdate,null,'N'
);

INSERT INTO member (
     mem_id,mem_pass,mem_name,mem_bir
    ,mem_zip,mem_add1,mem_add2,mem_hp
    ,mem_mail,mem_job,mem_hobby,mem_mileage
    ,mem_del_yn
) VALUES (
    'a00'||seq_member.nextval, '1004','한창희',sysdate
    ,'34502','우리집 아파트','201호','010-8000-8000'
    ,'gksbir@naver.com','JB01','HB01',0
    ,'N'
);

대충 50번 정도 실행해보자.

이 때 DB에서 commit을 하지않으면 jsp에서  제대로 실행해도 데이터가 보이지않으므로

DB에 변경사항이 잇을 경우 꼭 commit하자.

그리고 테이블 만들 때 char(4)로 만들어서 4byte의 데이터만 저장가능하고

나중에 code 테이블과 연계해서 쓰일예정이니

mem_job,mem_hobby,  bo_category는 반드시 'JB01','HB01','BC01'로 저장하자. 

이와같이 데이터가 DB에 들어갔으면 됐다.(free_board도 데이터가 저장됐을 것이다.)

 

자, 이제 DB에 데이터도 저장되었으니

이제 JSP에서 DB에 접근해 데이터를 가져와 화면에 출력하거나

DB를 update,insert 등을 해보자.

 

 

VO만들기

DB에서 가져온 데이터를 담을 객체를 VO(Value Object)라한다.

 

먼저 다음에 쿼리를 실행하자. 

SELECT RPAD( 'private '
             || DECODE(a.data_type , 'NUMBER', 'int ', 'String ')
             || SUBSTR(LOWER(a.column_name),1,1) 
             || SUBSTR(REPLACE(INITCAP(a.column_name),'_',''),2)
               || ';'
              , 40)
       || NVL2(b.comments, '/* ' || b.comments || ' */', '')
  FROM user_tab_cols a, user_col_comments b
 WHERE a.table_name  = b.table_name
   AND a.column_name = b.column_name
   AND a.table_name = UPPER(:TB)
 ORDER BY column_id;

 

 

 

값부분에 테이블이름을 적어준다.

 

 

적용을 누르면  다음과 같이 member테이블의 칼럼을 javaBean형식에 맞도록 생성해준다. 

 

이 쿼리를 이용해 자바의 VO를 쉽게 생성 할 수 있다.

프로젝트에 FreeBoardVO, MemberVO를 만들어 두자.

 

 

※VO생성 코드는 VO만들때마다 사용하므로  상단 보기-코드조각에

가서 다음과 같이 저장해놓고 사용하자.

 

 

이렇게 VO까지 만들었다면 JDBC를 활용해 DB와의 연동할 준비가 되었다.

 

 

 

 

 

 

JDBC 프로그래밍 코딩 흐름

1)JDBC 드라이버 로드

2)DB 연결

3)쿼리문 수행

4)DB 연결 종료

 

1) 드라이버로드 

https://brilliantdevelop.tistory.com/54 Class.forName 참고 

try{
	Class.forName("oracle.jdbc.driver.OracleDriver");
}catch (ClassNotFoundException e){
	System.out.println("classNotFound 떳다");
	e.printStackTrace();
	}

 

 

 

 

2)DB 연결

  JDBC URL

- DBMS와의 연결을 위한 식별 값

- JDBC 드라이버에 따라 형식이 다름

- 구성 :          jdbc:[DBMS]:[데이터베이스식별자]

• MySQL :     jdbc:mysql://HOST[:PORT]/DBNAME[?param=value&param1=value2&..]

• Oracle:       jdbc:oracle:thin:@HOST:PORT:SID

• MS SQL :   jdbc:sqlserver://HOST[:PORT];databaseName=DB

앞으로도 Oracle을 사용할 것이다.

conn=DriverManager.getConnection(
				"jdbc:oracle:thin:@127.0.0.1:1521:xe", oracle아이디,비밀번호);//DB연결

 

 

 

3) 쿼리문 수행 

JAVA에서 쿼리문을 수행할 수 있게 해주는

객체는 당연하게도 Connection객체로 부터 얻는다.

이렇게 얻은 객체가 Statement 인데 Statement종류는 3개가 있다.

1. Statement

- 기본 객체

- 텍스트 SQL 호출

2. PreparedStatement

- Statement 객체의 기능 향상

- 인자와 관련된 작업이 특화(매개변수)

- 코드 안정성 높음. 가독성 높음.

- 코드량이 증가

- 텍스트 SQL 호출

3. CallableStatement

- 프로시저 호출 전용, DB내에 프로시저를 사용할 수 있게 해준다.

 

Statement 주요 메소드

cancel()	SQL 문 실행을 취소합니다. 단, 해당 DBMS 에서 이러한 기능을 제공해주어야 합니다.
close()	Statement 객체를 제거합니다.
execute(String sql)	주어진 SQL을 데이터베이스에 전송시켜 주고, 그 결과를 가져옵니다. 이 메소드는 여러 개의 결과 데이터를 리턴합니다.
executeQuery(String sql)	주어진 SQL을 데이터베이스에 전송시켜주고 결과를 가져옵니다. 데이터베이스에 변경을 가하지 않는 SQL을 전송할 때 사용되며, 주로 SELECT 구문에 이용됩니다. 결과값으로 ResultSet 객체가 리턴됩니다.
executeUpdate(String sql)	주어진 SQL을 데이터베이스에 전송시켜 주고 그 결과를 가져옵니다. 데이터베이스에 변경을 가하게 되는 SQL을 전송할 때 사용되며, 주로 INSERT, UPDATE, CREATE 등의 SQL에 사용됩니다. 결과값으로 SQL에 의해서 영향을 받은 레코드 수를 가져옵니다.
getMaxFieldSize()	ResutlSet 객체에 포함시킬 수 있는 필드들의 최대 크기를 얻어옵니다.
getMaxRows()	ResultSet 객체에 포함시킬 수 있는 최대 레코드 수를 얻어옵니다.
getMoreResults()	하나 이상의 결과를 반환하는 SQL 문을 작성하였을 때 다음 결과 집합으로 이동시킵니다.
getResultSet()	SQL문 조회 결과를 ResultSet 객체 형태로 얻어옵니다.
getUpdateCount()	SQL문 처리로 인해 영향을 받은 레코드 수를 얻어옵니다.
setMaxFieldSize()	ResultSet 객체에 포함시킬 수 있는 필드들의 최대 크기를 설정합니다.
setMaxRows()	ResultSet 객체에 포함시킬 수 있는 최대 레코드 수를 설정합니다.

 

 

 

 

 

ResultSet

쿼리문을 수행하고 나면 insert,update,delete행은 int를 반환한다.

('n개 행이 업데이트 되었습니다'의 n을 반환)

select의 경우 쿼리 결과를 받아야하는데 이 객체가 바로 ResultSet이다.

	rs=stmt.executeQuery("SELECT mem_id,mem_name,mem_zip FROM member");

위 코드에서는 member테이블의 mem_id,mem_name,mem_zip의 대한 결과를 rs가 갖고있다.

 

ResultSet 주요 메소드

메소드	Return Type	Description
getString(String name) getString(int index)	String	지정한 값을 String으로
getCharacterStream(String name) getCharacterStream(int index)	java.io.Reader	지정한 값을 Stream 형태로 Long varchar 타입 읽어 올 때
getInt(String name) getInt(int index)	int	Int 타입으로 읽어 올 때
getLong(String name) getLong(int index)	long	Long 타입으로
getDouble(String name) getDouble(int index)	double	Double 타입으로
getFloat(String name) getFloat(int index)	float	Float 타입으로
getTimestamp(String name) getTimestamp(int index)	java.sql.Timestamp	Timestamp 타입으로… SQL TIMESTAMP 타입을 읽어 올 때
getDate(String name) getDate(int index)	java.sql.Date	Date 타입으로 SQL DATE 타입을 읽어 올 때
getTime(String name) getTime(int index)	java.sql.Time	Time 타입으로 SQL TIME 타입으로 읽어 올 때

4) 연결종료

Connection.close()메소드를 이용해 연결을 끊어줘야한다.

보통 한번에 DB에 연결할 수 있는 수는 정해져있고 

DB와의 연결을 끊지않으면 Too many Connection 에러가 발생하거나

다른곳의 연결이 끊어질때까지 무한대기하는 상황이 발생한다. 

※DB입장에서는  Connection conn변수를 DriverManger.getConnection()해서

얻은 순간 '아, 연결1개 추가되었군'이라고 인식한다. 

근데 JAVA에서 conn.close()를 안해주고 conn변수에 다른 값을 할당하면 

DB는 연결이 끊어진 줄 모른다.

즉, close를 제대로 안해주면 DB는 연결을 추가만 해서 한계에 도달하는 상황이 발생한다.

 

 

실습

00memberList.jsp 

<%@page import="java.sql.Array"%>
<%@page import="oracle.jdbc.driver.OracleDriver"%>
<%@page import="com.study.member.vo.MemberVO"%>
<%@page import="java.util.ArrayList"%> 
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<%request.setCharacterEncoding("utf-8"); %>
<html>
<head>
<%@include file="/WEB-INF/inc/header.jsp" %>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%@include file="/WEB-INF/inc/top.jsp" %>

<%
	
	
	try{
		Class.forName("oracle.jdbc.driver.OracleDriver");
	}catch (ClassNotFoundException e){
		System.out.println("classNotFound 떳다");
		e.printStackTrace();
	}
	Connection conn=null;   //DB연결,  user까지 연결합니다
	Statement stmt=null;    //쿼리문 작성
	ResultSet rs=null;      //쿼리문의 결과 저장
	
	try{
		conn=DriverManager.getConnection(
				"jdbc:oracle:thin:@127.0.0.1:1521:xe","jsp","oracle");//DB연결 
		
		stmt=conn.createStatement(); //쿼리문 작성 객체를 Connection으로부터얻음
		
		rs=stmt.executeQuery(
		"SELECT mem_id,mem_name,mem_zip FROM member"
				); //쿼리실행 + 결과값 ResultSet에 리턴  
		 //쿼리문에 ; 안 씁니다.
		 //쿼리문에 ; 안 씁니다.
		 //쿼리문에 ; 안 씁니다.
		 //쿼리문에 ; 안 씁니다.
		

		 
		 List<MemberVO> memberList=new ArrayList<MemberVO>();
		 while(rs.next()){  //select 결과가 더 남아있는지 검사
			 MemberVO member=new MemberVO();
		 	member.setMemId(rs.getString("mem_id"));
		 	member.setMemName(rs.getString("mem_name"));
		 	member.setMemZip(rs.getString("mem_zip"));
		 	memberList.add(member);
		 }
		request.setAttribute("memberList", memberList);
		 
	}catch (SQLException e){
		e.printStackTrace();
	}finally {
		if(rs!=null){try{rs.close();} catch(Exception e){} }
		if(stmt!=null){try{stmt.close();} catch(Exception e){} }
		if(conn!=null){try{conn.close();} catch(Exception e){} }
	}
	
%>


<!-- prodList보면서  c:forEach로 작성  -->
<table border="1" class="table table-striped table-bordered">
<c:forEach items="${memberList }" var="mem">
	<tr>
		<td>${mem.memId }</td>
		<td>
		<a  href="00memberView.jsp?memId=${mem.memId }">${mem.memName }</a>
		</td>
		<td>${mem.memZip }</td>
	</tr>
	
</c:forEach>
</table>








</body>
</html>

 

 

 

 

 

memName에다가 <a>태그를 지정해서 이름 클릭시 00memberView.jsp로 이동한다.

00memberView.jsp

<%@page import="java.util.ArrayList"%>
<%@page import="com.study.member.vo.MemberVO"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html lang="ko">
<head>
<%@ include file="/WEB-INF/inc/header.jsp"%>
<title>memberView.jsp</title>
</head>
<body>
	<%@include file="/WEB-INF/inc/top.jsp"%>

	<%
	String memId = request.getParameter("memId");
	try {
		Class.forName("oracle.jdbc.driver.OracleDriver");
	} catch (ClassNotFoundException e) {
		System.out.println("classNotFound 떳다");
		e.printStackTrace();
	}
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;

	try {
		conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "jsp", "oracle");
		stmt = conn.createStatement();
		//항상 쿼리문 띄어쓰기 
		StringBuffer  sb=new StringBuffer();
		sb.append("SELECT                                   "    );
		sb.append(" mem_id     ,mem_pass    ,mem_name       "    );
		sb.append(",mem_bir    ,mem_zip     ,mem_add1       "    );
		sb.append(",mem_add2   ,mem_hp      ,mem_mail       "    );
		sb.append(",mem_job    ,mem_hobby   ,mem_mileage    "    );
		sb.append(",mem_del_yn                              "    );
		sb.append(" FROM member                             "    );
		sb.append(" WHERE mem_id='"+memId+"'                "    ); 
		
		rs = stmt.executeQuery(sb.toString());
		if(rs.next()){
			MemberVO member=new MemberVO();
			member.setMemId(rs.getString("mem_id"));
			member.setMemPass(rs.getString("mem_pass"));
			member.setMemName(rs.getString("mem_name"));
			member.setMemBir(rs.getString("mem_bir"));
			member.setMemZip(rs.getString("mem_zip"));
			member.setMemAdd1(rs.getString("mem_add1"));
			member.setMemAdd2(rs.getString("mem_add2"));
			member.setMemHp(rs.getString("mem_hp"));
			member.setMemMail(rs.getString("mem_mail"));
			member.setMemJob(rs.getString("mem_job"));
			member.setMemHobby(rs.getString("mem_hobby"));
			member.setMemMileage(rs.getInt("mem_mileage"));
			member.setMemDelYn(rs.getString("mem_del_yn"));
			request.setAttribute("member", member);
		}
		
	

	} catch (SQLException e) {
		e.printStackTrace();
	}
	finally {
		if(rs!=null){try{rs.close();} catch(Exception e){} }
		if(stmt!=null){try{stmt.close();} catch(Exception e){} }
		if(conn!=null){try{conn.close();} catch(Exception e){} }
	}
	%>



	<div class="container">
		<h3>회원조회</h3>
		<table class="table table-striped table-bordered">
			<tbody>
				<tr>
					<th>아이디</th>
					<td>${member.memId }</td>
				</tr>
				
				<tr>
					<th>회원명</th>
					<td>${member.memName }</td>
				</tr>
				<tr>
					<th>우편번호</th>
					<td>${member.memZip }</td>
				</tr>
				<tr>
					<th>주소</th>
					<td>${member.memAdd1 }
						${member.memAdd2 }
					</td>
				</tr>
				<tr>
					<th>생일</th>
					<td>${member.memBir }</td>
				</tr>
				<tr>
					<th>핸드폰</th>
					<td>${member.memHp }</td>
				</tr>
				<tr>
					<th>직업</th>
					<td>${member.memJob }</td>
				</tr>
				<tr>
					<th>취미</th>
					<td>${member.memHobby }</td>
				</tr>
				<tr>
					<th>마일리지</th>
					<td>${member.memMileage }</td>
				</tr>
				<tr>
					<th>탈퇴여부</th>
					<td>${member.memDelYn }</td>
				</tr>
				
			</tbody>
		</table>
	</div>

</body>
</html>

 

 

※Statement를 이용해서 쿼리실행할 때 

?memId='a001'을 예상하고 코딩을 했다.

만약 사용자가 a001이라는 파라미터 대신 a000 OR 1=1이라는 파라미터를 준다면

memId와 상관없이 화면을 볼 수 있게 된다.

이런 이유 때문에 StateMent대신 PrepardStatement사용을 권장한다.