2022.08.24 JSP

sofia·2022년 8월 27일

jsp

JAVA

목록 보기

21/27

📚 데이터 베이스와 JDBC를 이용한 JSP 와의 연동(Chapter 13 ppt)

my SQL 먼저 실행(로그인)
show databases;입력

create database jspdb;입력

use jspdb;
하여 데이터 베이스 변경

show tables;
하였을때 아무런 테이블 없다고 나옴(새롭게 만든 데이터베이스에 아무것도 없기때문)

Create table member( id int not null auto_increment, name varchar (100) not null, passwd varchar(50) not null, primary key(id) );
실행 하여 테이블 생성 후 확인(show tables;)

데이터 상황 확인 후,
insert into member values(1,'홍길동','1234');실행하여 데이터 추가

이클립스로 돌아와서 Connection.jsp기반으로 실행하여 오라클 연결 확인 그후 Connection.jsp기반으로 connectionMySql.jsp생성

connectionMySql.jsp

<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
   <%
   	  Connection con = null;

   
      try {
         String url = "jdbc:mysql://localhost:3306/jspdb";
         String user = "ㅇ";
         String password = "ㅇㅇ";
         
         Class.forName("com.mysql.jdbc.Driver"); 
         con=DriverManager.getConnection(url, user, password);
         out.print("SQL 데이터베이스 연결이 성공했습니다.<br>");
      } catch(SQLException ex) {
         out.print("SQL 데이터베이스 연결이 실패했습니다.<br>");
         out.print("SQLException : "+ex.getMessage());
      } finally {
         if(con != null) {
            con.close();
         }
      }
   %>
</body>
</html>

그 후 C:\Tomcat 9.0\lib에 sql.jar 복사 붙여넣고 실행하면 데이터 베이스 연결 성공했다뜸

현재까지 2단계 Connection까지 완료.

executeQuery() 메소드와 select 문

*. 3단계
Statement stmt = con.createStatement( );
: 메소드를 호출하여 객체 생성

stmt.executeQuery()=>SQL 쿼리 작성
stmt.executeUpdate()=> SQL 쿼리 中 수정(삽입, 삭제 등)

*. 4단계
ResultSet
=> 결과 출력함.(이때 반복문을 사용하여 가지고 옴)

예시

먼저 쿼리 작성하여 테이블 생성

CREATE TABLE MEMBER2
(ID VARCHAR2(20) PRIMARY KEY
,PW VARCHAR2(20)
,NAME VARCHAR2(20)
,CLASS NUMBER(2)
,TEL VARCHAR2(20)
);

데이터 삽입

INSERT INTO MEMBER2 VALUES('trBird','abll','김태은',1,'010-2930-4874');
INSERT INTO MEMBER2 VALUES('yjWood','abcd','이은정',2,'010-9230-1245');
INSERT INTO MEMBER2 VALUES('jinBear','3333','조진이',2,'010-2022-7244');

COMMIT;을 실행하여야지만 추후 오류가 안남

viewMember.jsp 생성

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%! 
	Connection conn = null;
	Statement stmt = null;//3단계
	ResultSet rs = null; //4단계
	
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	String user = "scott";
	String password = "tiger";
	String selectQuery = "SELECT * FROM MEMBER2";
%>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<table width = "400" border = "1">
		<tr>
			<td>아이디</td>
			<td>이름</td>
			<td>등급</td>
			<td>전화번호</td>
		</tr>
		<%
			try{
				Class.forName("oracle.jdbc.driver.OracleDriver");
		        conn = DriverManager.getConnection(url, user, password);
		        stmt = conn.createStatement();
		        rs =  stmt.executeQuery(selectQuery);
		        
		        while(rs.next()){ //데이터가 있는 만큼 반복
		%>		
		<tr>
			<td><%= rs.getString("id") %></td>
			<td><%= rs.getString("name") %></td>
			<td><%= rs.getInt("class") %></td>
			<td><%= rs.getString("tel") %></td>
		</tr>	
		<%    	
		        }
			}catch(SQLException ex) {
		         ex.getMessage();
		    }  finally {
		    	try{
			        if(conn != null) rs.close();
			        if(conn != null) stmt.close();
			        if(conn != null) conn.close();		    		
		    	}catch(SQLException ex) {
			         ex.getMessage();
			    }
		    }
		%>
	</table>
	
</body>
</html>

실행하면

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%! 
	Connection conn = null;
	Statement stmt = null;//3단계
	ResultSet rs = null; //4단계
	
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	String user = "scott";
	String password = "tiger";
	String selectQuery = "SELECT * FROM MEMBER2";
%>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<table width = "400" border = "1">
		<tr>
			<td>아이디</td>
			<td>이름</td>
			<td>등급</td>
			<td>전화번호</td>
		</tr>
		<%
			try{
				Class.forName("oracle.jdbc.driver.OracleDriver");
		        conn = DriverManager.getConnection(url, user, password);
		        stmt = conn.createStatement();
		        rs =  stmt.executeQuery(selectQuery);
		        
		        while(rs.next()){ //데이터가 있는 만큼 반복
		%>		
		<tr>
			<td><%= rs.getString("id") %></td>
			<td><%= rs.getString("name") %></td>
			<%-- <td><%= rs.getInt("class") %></td> 주석 : Ctrl+Shift+/ --%>
         <td>
            <%
               int n_class = rs.getInt("class");
            
               if(n_class == 1) {
                  out.print("일반회원");
               } else {
                  out.print("교수님");
               }
            %>
         </td>
			<td><%= rs.getString("tel") %></td>
		</tr>	
		<%    	
		        }
			}catch(SQLException ex) {
		         ex.getMessage();
		    }  finally {
		    	try{
			        if(conn != null) rs.close();
			        if(conn != null) stmt.close();
			        if(conn != null) conn.close();		    		
		    	}catch(SQLException ex) {
			         ex.getMessage();
			    }
		              
		    }
		%>
	</table>
	
</body>
</html>

viewMemberMySql.jsp 생성

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%! 
	Connection con = null;
	Statement stmt = null;//3단계
	ResultSet rs = null; //4단계
	
	String url = "jdbc:mysql://localhost:3306/jspdb";
    String user = "root";
    String password = "1234";
	String selectQuery = "SELECT * FROM MEMBER2";
%>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<table width = "400" border = "1">
		<tr>
			<td>아이디</td>
			<td>이름</td>
			<td>등급</td>
			<td>전화번호</td>
		</tr>
		<%
			try{
				Class.forName("com.mysql.jdbc.Driver");
		        con = DriverManager.getConnection(url, user, password);
		        stmt = con.createStatement();
		        rs =  stmt.executeQuery(selectQuery);
		        
		        while(rs.next()){ //데이터가 있는 만큼 반복
		%>		
		<tr>
			<td><%= rs.getString("id") %></td>
			<td><%= rs.getString("name") %></td>
			<%-- <td><%= rs.getInt("class") %></td> 주석 : Ctrl+Shift+/ --%>
         <td>
            <%
               int n_class = rs.getInt("class");
            
               if(n_class == 1) {
                  out.print("일반회원");
               } else {
                  out.print("교수님");
               }
            %>
         </td>
			<td><%= rs.getString("tel") %></td>
		</tr>	
		<%    	
		        }
			}catch(SQLException ex) {
		         ex.getMessage();
		    }  finally {
		    	try{
			        if(con != null) rs.close();
			        if(con != null) stmt.close();
			        if(con != null) con.close();		    		
		    	}catch(SQLException ex) {
			         ex.getMessage();
			    }
		              
		    }
		%>
	</table>
	
</body>
</html>

예제 7.2

addForm.html생성

<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<form method="post" action="addMember.jsp">
			아 이 디 : <input type="text" name="id" size="20"><br>
			비밀번호 : <input type="password" name="pw" size="20"><br>
			회원이름 : <input type="text" name="name" size="20"><br>
			회원구분 : <input type="radio" name="mclass" value="1">일반회원
			        <input type="radio" name="mclass" value="2">교수님<br>
			전화번호 : <select name="phone1">
						<option value = "010">010</option>
						<option value = "011">011</option>
						<option value = "016">016</option>
						<option value = "017" selected>017</option>
						<option value = "018">018</option>
						<option value = "019">019</option>
					</select>
					-
			        <input type="text" name ="phone2" size="4" maxlength="4">
			        -
			        <input type="text" name ="phone3" size="4" maxlength="4"><br>
			        <input type="submit" value="전송">
	
	</form>
</body>
</html>

addMember.jsp

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%!
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;

	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	String user = "scott";
	String password = "tiger";
%>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<%
		String id="", pw="", name="", p1="", p2="", p3="", sclass=""; 
		id = request.getParameter("id");
		pw = request.getParameter("pw");
		name = request.getParameter("name");
		sclass = request.getParameter("mclass");
		p1 = request.getParameter("phone1");
		p2 = request.getParameter("phone2");
		p3 = request.getParameter("phone3");
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, user, password);
			stmt = conn.createStatement();
			StringBuffer insertQuery = new StringBuffer();
			insertQuery.append("INSERT INTO MEMBER2 VALUES('");
			insertQuery.append(id+"','");
			insertQuery.append(pw+"','");
			insertQuery.append(name+"','");
			insertQuery.append(sclass+"','");
			insertQuery.append(p1+"-"+p2+"-"+p3+"')");
			
			System.out.print(insertQuery.toString());
			int re = stmt.executeUpdate(insertQuery.toString());
			
			if(re == 1) {
		%>
				추가 성공
				<br>
				<a href="addForm.html">추가작업</a>
				<a href="viewMember.jsp">검색작업</a>
		<%
			} else {
		%>
				<font color="red">추가 실패 ㅠㅡㅠ</font>
		<%
			}
		} catch(SQLException ex) {
			ex.getMessage();
		%>
			서버불량 잠시 후 다시 시도
		<%
		} finally {
			try {
				if(rs != null) rs.close();
				if(stmt != null) stmt.close();
				if(conn != null) conn.close();
			} catch(SQLException ex) {
				ex.getMessage();
			}
		}
	%>
</body>
</html>

데이터가 추가됨을 확인 할 수 있음

근데 위의 코드로는 한글로 된 데이터를 추가하면 데이터가 깨질 수 있으므로 UTF-8와 <% request.setCharacterEncoding("UTF-8"); %> 추가

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.SQLException"%>
<%@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"%>
<% 
	request.setCharacterEncoding("UTF-8");  
%>  
   
<%!
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;

	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	String user = "scott";
	String password = "tiger";
%>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%
		String id="", pw="", name="", p1="", p2="", p3="", sclass=""; 
		id = request.getParameter("id");
		pw = request.getParameter("pw");
		name = request.getParameter("name");
		sclass = request.getParameter("mclass");
		p1 = request.getParameter("phone1");
		p2 = request.getParameter("phone2");
		p3 = request.getParameter("phone3");
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, user, password);
			stmt = conn.createStatement();
			StringBuffer insertQuery = new StringBuffer();
			insertQuery.append("INSERT INTO MEMBER2 VALUES('");
			insertQuery.append(id+"','");
			insertQuery.append(pw+"','");
			insertQuery.append(name+"','");
			insertQuery.append(sclass+"','");
			insertQuery.append(p1+"-"+p2+"-"+p3+"')");
			
			System.out.print(insertQuery.toString());
			int re = stmt.executeUpdate(insertQuery.toString());
			
			if(re == 1) {
		%>
				추가 성공
				<br>
				<a href="addForm.html">추가작업</a>
				<a href="viewMember.jsp">검색작업</a>
		<%
			} else {
		%>
				<font color="red">추가 실패 ㅠㅡㅠ</font>
		<%
			}
		} catch(SQLException ex) {
			ex.getMessage();
		%>
			서버불량 잠시 후 다시 시도
		<%
		} finally {
			try {
				if(rs != null) rs.close();
				if(stmt != null) stmt.close();
				if(conn != null) conn.close();
			} catch(SQLException ex) {
				ex.getMessage();
			}
		}
	%>
</body>
</html>

ddd,1,홍길동,교수님.010-1111-2222 입력

MYSQL의 경우

addFormMySql.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<form method="post" action="addMemberMySql.jsp">
		아 이 디 : <input type="text" name="id" size="20"><br>
		비밀번호 : <input type="password" name="pw" size="20"><br>
		회원이름 : <input type="text" name="name" size="20"><br>
		회원구분 : <input type="radio" name="mclass" value="1">일반회원
				 <input type="radio" name="mclass" value="2">교수님<br>
		전화번호 : <select name="phone1">
					<option value="010">010</option>
					<option value="011">011</option>
					<option value="016">016</option>
					<option value="017" selected>017</option>
					<option value="018">018</option>
					<option value="019">019</option>
				</select>
				-
				<input type="text" name="phone2" size="4" maxlength="4">
				-
				<input type="text" name="phone3" size="4" maxlength="4"><br>
				<input type="submit" value="전송">
	</form>
</body>
</html>

addMemberMySql.jsp

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.SQLException"%>
<%@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"%>
<% 
	request.setCharacterEncoding("UTF-8");  
%>  
   
<%!
	Connection con = null;
	Statement stmt = null;
	ResultSet rs = null;

	String url = "jdbc:mysql://localhost:3306/jspdb";
    String user = "root";
    String password = "1234";
%>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%
		String id="", pw="", name="", p1="", p2="", p3="", sclass=""; 
		id = request.getParameter("id");
		pw = request.getParameter("pw");
		name = request.getParameter("name");
		sclass = request.getParameter("mclass");
		p1 = request.getParameter("phone1");
		p2 = request.getParameter("phone2");
		p3 = request.getParameter("phone3");
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, user, password);
			stmt = con.createStatement();
			StringBuffer insertQuery = new StringBuffer();
			insertQuery.append("INSERT INTO MEMBER2 VALUES('");
			insertQuery.append(id+"','");
			insertQuery.append(pw+"','");
			insertQuery.append(name+"','");
			insertQuery.append(sclass+"','");
			insertQuery.append(p1+"-"+p2+"-"+p3+"')");
			
			System.out.print(insertQuery.toString());
			int re = stmt.executeUpdate(insertQuery.toString());
			
			if(re == 1) {
		%>
				추가 성공
				<br>
				<a href="addFormMySql.html">추가작업</a>
				<a href="viewMemberMySql.jsp">검색작업</a>
		<%
			} else {
		%>
				<font color="red">추가 실패 ㅠㅡㅠ</font>
		<%
			}
		} catch(SQLException ex) {
			ex.getMessage();
		%>
			서버불량 잠시 후 다시 시도
		<%
		} finally {
			try {
				if(rs != null) rs.close();
				if(stmt != null) stmt.close();
				if(con != null) con.close();
			} catch(SQLException ex) {
				ex.getMessage();
			}
		}
	%>
</body>
</html>

📚 CONNECTION POOL로 JSP와 데이터 베이스 연동

DBCP

JDBC보다 퍼포먼스적으로 성능이 좋음

TOMCAT의 server.xml에 추가 (Context태그 안에 삽입

<Resource auth="Container" driverClassName="oracle.jdbc.driver.OracleDriver" maxActive="100" maxIdle="30" maxWait="10000" name="jdbc/oracle" password="tiger" type="javax.sql.DataSource" url="jdbc:oracle:thin:@localhost:1521:xe" username="scott"/>

그 후
dbConn.jsp 생성

<%@page import="javax.naming.Context"%>
<%@page import="javax.naming.InitialContext"%>
<%@page import="java.sql.SQLException"%>
<%@page import="javax.naming.NamingException"%>
<%@page import="javax.sql.DataSource"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
	<%!
		Connection conn = null;
		DataSource ds = null;
	%>
	
	<%
		try{
			Context ctx =  new InitialContext();
			ds = (DataSource)ctx.lookup("java:comp/env/jdbc/oracle");//캐스팅
			conn = ds.getConnection();
			System.out.println("DBCP 연동 성공");
		}catch(NamingException ne){
			ne.printStackTrace();
		}catch(SQLException ex) {
	         out.print("데이터베이스 연결이 실패했습니다.<br>");
	         out.print("SQLException : "+ex.getMessage());
	      } finally {
	    	  try{
	    		  
	    	  }catch(Exception e){
	    		  e.printStackTrace();
	    	  }
	         if(conn != null) {
	            conn.close();
	         }
	      }
		
	%>
</body>
</html>

그 후 실행

예제 회원 정보 조회/수정

viewMember.jsp (chapter 14)

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%!
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;

	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	String user = "scott";
	String password = "tiger";
	String selectQuery = "SELECT * FROM MEMBER2";
%>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<table width="400" border="1">
		<tr>
			<td>아이디</td>
			<td>이름</td>
			<td>등급</td>
			<td>전화번호</td>
		</tr>
		<%
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection(url, user, password);
				stmt = conn.createStatement();
				rs = stmt.executeQuery(selectQuery); //쿼리 결과를 rs로 받음
				
				while(rs.next()) {
		%>
		<tr>
			<%-- <td><%= rs.getString("id") %></td> --%>
			<td>
				<a href = "updateMember.jsp?id = <%= rs.getString("id") %>">
					 <%= rs.getString("id") %>
				</a>
			</td>
			<td>
				<%= rs.getString("name") %>
			</td>
			<%-- <td><%= rs.getInt("class") %></td> 주석 : Ctrl+Shift+/ --%>
			<td>
				<%
					int n_class = rs.getInt("class");
				
					if(n_class == 1) {
						out.print("일반회원");
					} else {
						out.print("교수님");
					}
				%>
			</td>
			<td><%= rs.getString("tel") %></td>
		</tr>
		<%
				}
			} catch(SQLException ex) {
				ex.getMessage();
			} finally {
				try {
					if(rs != null) rs.close();
					if(stmt != null) stmt.close();
					if(conn != null) conn.close();
				} catch(SQLException ex) {
					ex.getMessage();
				}
			}
		%>
	</table>
</body>
</html>