본문 바로가기
Kosta DevOps 과정 280기/Java

jsp 로 DB 연동하기

by 롯슈83 2024. 7. 4.
  • JDBC 처럼 사전 등록이 필요하다
    • WEB-INF  폴더 lib 에 "C:\app\WD\product\21c\dbhomeXE\jdbc\lib" 에 있는 ojdbc8.jar 복사해서 넣기
  • server.xml 에 한글 받기 다음과 같이 바꾸기
    • project 에 Servers 폴더의 server.xml 에 해당 태그에 URLEncoding 속성을 다음과 같이 추가한다.
    • 그 이후 html 과 jsp 각각에 encoding 을 UTF-8 로 변경해준다.
  • 또한 프로그램 내에서 계속 UTF-8  로 자동으로 삭제하고 싶을 경우 
    • window > preference > web > html의 인코딩을 UTF -8 로 변경한 뒤, apply  한다.
    • jsp 도 똑같이 uif-8 로 변경하고 apply and close 한다.
<Connector connectionTimeout="20000" 
    maxParameterCount="1000" 
    port="8088" 
    protocol="HTTP/1.1" 
    redirectPort="8443"
    URLEncoding = "UTF-8"
    />
  • 예시1
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@ 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>
	<!-- DB 랑 연동하여 jsp 작업 -->
	<h2>도서목록</h2>
	<hr>
	<ul>
		
		<%
			try{
				String driver = "oracle.jdbc.driver.OracleDriver";
				String url = "jdbc:oracle:thin:@localhost:1521:XE";
				String username = "c##madang";
				String password = "madang";
				String sql = "select bookname from book";
				
				//1. JDBC 드라이버를 메모리로 로드한다.
				Class.forName(driver);
				
				//2. DB 서버에 연결한다.
				Connection conn = DriverManager.getConnection(url, username, password);
				
				//3. Statement 객체를 생성한다.
				Statement stmt = conn.createStatement();
				
				//4. sql 명령을 실행한다.
				ResultSet rs = stmt.executeQuery(sql);
				
				while(rs.next()){
					%>
					<li><%= rs.getString(1) %></li>
					<%
				}
				
				rs.close();
				stmt.close();
				conn.close();
			}catch(Exception e){
				System.out.println("예외발생 : "+e.getMessage());
			}
		%>
	</ul>
</body>
</html>
  • 예시2
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@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>
	<table border = "1" width ="80%">
		<thead>
			<tr>
				<th>
					부서번호
				</th>
				<th>
					부서명
				</th>
				<th>
					부서위치
				</th>
			</tr>
		</thead>
		<%
			try{
				String driver = "oracle.jdbc.driver.OracleDriver";
				String url = "jdbc:oracle:thin:@localhost:1521:XE";
				String username = "c##madang";
				String password = "madang";
				String sql = "select * from dept";
				
				Class.forName(driver);
				Connection conn = DriverManager.getConnection(url, username, password);
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while(rs.next()){
					%>
						<tbody>
							<tr>
							<td>
								<%= rs.getInt(1) %>
							</td>
							<td>
								<%= rs.getString(2) %>
							</td>
							<td>
								<%= rs.getString(3) %>
							</td>
							</tr>
						</tbody>
					<%
				}
			}catch(Exception e){
				System.out.println(e.getMessage()); 
			}
		%>
	</table>
</body>
</html>

 

form 태그로 데이터를 입력받고 DB 와 연결하기 

  • method - get 방식 : 입력한 데이터가 주소 표시줄에 나타남
    • 단, server.xml 을 URIEncoding ="UTF-8" 만 설정해주면 따로 request 객체에 알려주지 않아도 사용 가능하다.
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h2>get방식으로 데이터 전달하기</h2>
    <form action="ex06_getOK.jsp" method="get">
        이름 : <input type="text" name="username" id="">
        <input type="submit" value="확인">
    </form>
</body>
</html>
<%@ 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>
</head>
<body>
	<%
		String name = request.getParameter("username");
	%>
	반갑습니다. <b><%=name%></b>님
</body>
</html>

  • method - post 방식 : 입력한 데이터가 주소 표시줄에 나타나지 않음
    • 단, 한글 데이터를 받기 위하여 request.setCharacterEncoding("UTF-8") 을 첫 줄에 적어줘야 한다.
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h2>post방식으로 데이터 전달하기</h2>
    <form action="ex07_postOK.jsp" method="post">
        이름 : <input type="text" name="username" id="">
        <input type="submit" value="확인">
    </form>
</body>
</html>
<%@ 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>
</head>
<body>
	<%
		request.setCharacterEncoding("UTF-8");
		String name = request.getParameter("username");
	%>
	반갑습니다. <b><%=name%></b>님
</body>
</html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h2>주문 도서 조회하기</h2>
    <hr>
    <!-- 사용자에게 입력받기 == form -->
    <form action="ex05_orderOK.jsp" method="post">
        고객명: <input type="text" name="name">
        <input type="submit" value="조회">
    </form>
</body>
</html>
<%@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>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%
	//사용자가 요청한 문자셋은 한글이다.
			request.setCharacterEncoding("UTF-8");
			
			//사용자가 요청한 고객의 이름을 받아온다.
			//매개변수에 input name 속성과 일치시킨다.
			String name = request.getParameter("name");
			%>
				<h2><%= name%> 고객의 주문도서 목록</h2>		
			<%
	%>
	
	<hr>
	<table border = "1">
        <thead>
            <tr>
                <th>도서번호</th>
                <th>도서명</th>
                <th>가격</th>
                <th>출판사</th>
            </tr>
        </thead>
        <tbody>
    
	<%
		
		
		String sql = "SELECT * FROM BOOK WHERE BOOKID IN (SELECT BOOKID FROM ORDERS WHERE CUSTID = (SELECT CUSTID FROM CUSTOMER WHERE NAME ='"+name+"'))";
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url ="jdbc:oracle:thin:@localhost:1521:XE";
		String username= "c##madang";
		String password = "madang";
		
		try{
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, username, password);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				%>
				<tr>
					<td><%=rs.getInt(1)%></td>
					<td><%=rs.getString(2) %></td>
					<td><%=rs.getInt(3)%></td>
					<td><%=rs.getString(4) %></td>
				</tr>
				<%
			}
			rs.close();
			stmt.close();
			conn.close();
		}
		catch(Exception e){
			System.out.println(e);
		}

	%>
	</tbody>
	</table>
</body>
</html>


다양한 입력 양식

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h2>다양한 입력 양식</h2>
    <form action="ex08_inputOK.jsp" method="post">
        이름 : <input type="text" name="name" id=""> <br>
        주소 : <input type="text" name="addr" id="" value="서울시 종로구 관철동 1204"> <br>
        나이 : <input type="number" name="age" id="" min="10" max="100" value="20"> <br>
        이메일 : <input type="email" name="email" id=""> <br>
        <!-- 단순히 모양만 지정하는 것으로 업로드 하려면 별도의 프로그램 코드 필요 -->
        첨부파일 : <input type="file" name="fname" id=""> <br>
        비밀번호 : <input type="password" name="pwd" id=""> <br>
        취미 : 
        <input type="checkbox" name="hobby" id="" value = "마라톤"> 마라톤
        <input type="checkbox" name="hobby" id="" value = "수영"> 수영
        <input type="checkbox" name="hobby" id="" value = "게임"> 게임
        <input type="checkbox" name="hobby" id="" value = "축구"> 축구
        <br>
        성별 : 
        <input type="radio" name="gender" id="" value = "남"> 남
        <input type="radio" name="gender" id="" value = "여"> 여
        <br>
        혈액형 : <br> 
        <input type="radio" name="bloodType" value="A"> A
        <input type="radio" name="bloodType" value="B"> B
        <input type="radio" name="bloodType" value="O"> O
        <input type="radio" name="bloodType" value="AB"> AB
        <br>
        직업 :
        <select size = "1" name = "job">
        	<option value="회사원">회사원</option>
        	<option value="공무원">공무원</option>
        	<option value="학생">학생</option>
        	<option value="주부">주부</option>
        </select>
        <br>
        좋아하는 음식<br>
        <select size = "3" name = "food">
        	<option value="한식">한식</option>
        	<option value="중식">중식</option>
        	<option value="일식">일식</option>
        	<option value="양식">양식</option>
        </select>
        
        <br>
        자기소개 : <br>
        <textarea name="pro" id="" rows="10" cols="60"></textarea>
        <br>
        <input type="submit" value="확인">
        <input type="reset" value="다시입력">
    </form>
</body>
</html>
<%@ 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>
</head>
<body>
	<%
	request.setCharacterEncoding("UTF-8");
	String name = request.getParameter("name");
	String addr = request.getParameter("addr");
	int age = Integer.parseInt(request.getParameter("age"));
	String email = request.getParameter("email");
	String pwd = request.getParameter("pwd");
	String []hobby = request.getParameterValues("hobby");
	String gender = request.getParameter("gender");
	String bloodType= request.getParameter("bloodType");
	String food = request.getParameter("food");
	String pro  = request.getParameter("pro");
	
	%>
	이름 : <%= name %><br>
	주소 : <%= addr %><br>
	나이 : <%= age %><br>
	메일 : <%= email %><br>
	비번 : <%= pwd %><br>
	취미 : 
	<%
		for(String h : hobby){
			%>
				<%= h %>&nbsp;
			<%
		}
	%><br>
	성별 : <%= gender %><br>
	혈액 : <%= bloodType %><br>
	음식 : <%= food %><br>
	소개 : <%= pro %><br>
	
</body>
</html>

 

실제로 DB에 INSERT 해보기

<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
</head>
<body>
	<%
	request.setCharacterEncoding("UTF-8");
	String name = request.getParameter("name");
	String addr = request.getParameter("addr");
	int age = Integer.parseInt(request.getParameter("age"));
	String email = request.getParameter("email");
	String pwd = request.getParameter("pwd");
	String []hobby = request.getParameterValues("hobby");
	String hobby2 = String.join(", ", hobby);
	String gender = request.getParameter("gender");
	String bloodType= request.getParameter("bloodType");
	String food = request.getParameter("food");
	String pro  = request.getParameter("pro");
	String job = request.getParameter("job");
	%>
	<%
	//String sql ="INSERT INTO MEMBER(no, name, addr, age, email, pwd, hobby, gender, bloodtype, job, food, info) VALUES(seq_member.nextval, '홍길동', '지구', 20, 'aaa@naver.com', '1234', '마라톤', '여', 'A', '대도', '포도', '대도둑~!')";
	String sql ="INSERT INTO MEMBER(no, name, addr, age, email, pwd, hobby, gender, bloodtype, job, food, info) VALUES(seq_member.nextval, '"+name+"', '"+addr+"', "+age+", '"+email+"', '"+pwd+"', '"+hobby2+"', '"+gender+"', '"+bloodType+"', '"+job+"', '"+food+"', '"+pro+"')";
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:XE";
	String username = "c##madang";
	String password = "madang";
	
	try{
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url, username, password);
		Statement stmt = conn.createStatement();
		int re = stmt.executeUpdate(sql);
		if(re == 1){
			%>
			고객 정보 추가됨
			<%
		}else{
			%>
			고객 정보 추가 안됨
			<%
		}
		
		stmt.close();
		conn.close();
	}catch(Exception e){
		System.out.println(e);
	}
	
	%>
	
</body>
</html>
  • 예제2 : table 과 prepareStatement 이용
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h2>다양한 입력 양식</h2>
    <form action="ex08_inputOK2.jsp" method="post">
    	<table width ="80%">
    		<tr>
    			<th width ="200px">이름</th>
    			<td>
    				<input type="text" name="name" id="">
    			</td>
   			</tr>
   			<tr>
    			<th>주소</th>
    			<td>
    				<input type="text" name="addr" id="" value="서울시 종로구 관철동 1204">
    			</td>
   			</tr>
   			<tr>
    			<th>나이</th>
    			<td>
    				<input type="number" name="age" id="" min="10" max="100" value="20">
    			</td>
   			</tr>
   			<tr>
    			<th>이메일</th>
    			<td>
    				<input type="email" name="email" id="">
    			</td>
   			</tr>
   			<tr>
    			<th>첨부파일</th>
    			<td>
    				<input type="file" name="fname" id="">
    			</td>
   			</tr>
   			<tr>
    			<th>비밀번호</th>
    			<td>
    				<input type="password" name="pwd" id="">
    			</td>
   			</tr>
   			<tr>
    			<th>취미</th>
    			<td>
    				<input type="checkbox" name="hobby" id="" value = "마라톤"> 마라톤
			      	<input type="checkbox" name="hobby" id="" value = "수영"> 수영
			      	<input type="checkbox" name="hobby" id="" value = "게임"> 게임
			      	<input type="checkbox" name="hobby" id="" value = "축구"> 축구
    			</td>
   			</tr>
   			<tr>
    			<th>성별</th>
    			<td>
    				<input type="radio" name="gender" id="" value = "남"> 남
        			<input type="radio" name="gender" id="" value = "여"> 여
    			</td>
   			</tr>
   			<tr>
    			<th>혈액형</th>
    			<td>
    				<input type="radio" name="bloodType" value="A"> A
			        <input type="radio" name="bloodType" value="B"> B
			        <input type="radio" name="bloodType" value="O"> O
			        <input type="radio" name="bloodType" value="AB"> AB
    			</td>
   			</tr>
   			<tr>
    			<th>직업</th>
    			<td>
    				<select size = "1" name = "job">
			        	<option value="회사원">회사원</option>
			        	<option value="공무원">공무원</option>
			        	<option value="학생">학생</option>
			        	<option value="주부">주부</option>
			        </select>
    			</td>
   			</tr>
   			<tr>
    			<th>좋아하는 음식</th>
    			<td>
    				<select size = "3" name = "food">
			        	<option value="한식">한식</option>
			        	<option value="중식">중식</option>
			        	<option value="일식">일식</option>
			        	<option value="양식">양식</option>
			        </select>
    			</td>
   			</tr>
   			<tr>
    			<th>자기소개</th>
    			<td>
    				<textarea name="pro" id="" rows="10" cols="60"></textarea>
    			</td>
   			</tr>
   			<tr>
   				<td colspan = "2">
   					<input type="submit" value="확인">
        			<input type="reset" value="다시입력">
   				</td>
   			</tr>
    	</table>
    </form>
</body>
</html>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
</head>
<body>
	<%
	request.setCharacterEncoding("UTF-8");
	String name = request.getParameter("name");
	String addr = request.getParameter("addr");
	int age = Integer.parseInt(request.getParameter("age"));
	String email = request.getParameter("email");
	String pwd = request.getParameter("pwd");
	String []hobby = request.getParameterValues("hobby");
	String hobby2 = String.join(", ", hobby);
	String gender = request.getParameter("gender");
	String bloodType= request.getParameter("bloodType");
	String food = request.getParameter("food");
	String pro  = request.getParameter("pro");
	String job = request.getParameter("job");
	%>
	<%
	//String sql ="INSERT INTO MEMBER(no, name, addr, age, email, pwd, hobby, gender, bloodtype, job, food, info) VALUES(seq_member.nextval, '홍길동', '지구', 20, 'aaa@naver.com', '1234', '마라톤', '여', 'A', '대도', '포도', '대도둑~!')";
	String sql ="INSERT INTO MEMBER(no, name, addr, age, email, pwd, hobby, gender, bloodtype, job, food, info) VALUES(seq_member.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:XE";
	String username = "c##madang";
	String password = "madang";
	
	try{
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url, username, password);
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, name);
		pstmt.setString(2, addr);
		pstmt.setInt(3, age);
		pstmt.setString(4, email);
		pstmt.setString(5, pwd);
		pstmt.setString(6, hobby2);
		pstmt.setString(7, gender);
		pstmt.setString(8, bloodType);
		pstmt.setString(9, job);
		pstmt.setString(10, food);
		pstmt.setString(11, pro);
		int re = pstmt.executeUpdate();
		if(re == 1){
			%>
			고객 정보 추가됨
			<%
		}else{
			%>
			고객 정보 추가 안됨
			<%
		}
		
		pstmt.close();
		conn.close();
	}catch(Exception e){
		System.out.println(e);
	}
	
	%>
	
</body>
</html>

 

공간분할(영역지정)을 위한 태그

  • 블록형식
    • div
    • 세로배치
    • 크기를 가질 수 있음
  • 인라인 형식
    • span
    • 가로배치
    • 크기를 가질 수 없음
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
    div{
        width: 100px;
        height: 100px;
        margin : 10px;
        background:orange;
    }
</style>
</head>
<body>
	<div>1</div>
    <div>2</div>
    <div>3</div>
    <div>4</div>
    <div>5</div>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
    span{
        width: 100px;
        height: 100px;
        margin : 10px;
        background:orange;
    }
</style>
</head>
<body>
	<span>1</span>
    <span>2</span>
    <span>3</span>
    <span>4</span>
    <span>5</span>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
    span{
        display: inline-block;
        width: 100px;
        height: 100px;
        margin : 10px;
        background:orange;
    }
</style>
</head>
<body>
	<span>1</span>
    <span>2</span>
    <span>3</span>
    <span>4</span>
    <span>5</span>
</body>
</html>
저작자표시 비영리 변경금지

'Kosta DevOps 과정 280기 > Java' 카테고리의 다른 글

색상 설정하기  (0) 2024.07.05
선택자  (0) 2024.07.05
웹서버  (0) 2024.07.03
웹 공부 사전 준비  (0) 2024.07.03
사용자 정의 함수(function)만드는 방법  (0) 2024.06.25

관련글

티스토리툴바