Crescendo Code

4월 24일 (월) - [] 본문

KIC 백엔드 수업

4월 24일 (월) - []

Crecok 2023. 4. 25. 09:10
반응형

◆ 이전 수업 개념

 

▶ JSP 환경설정

 

1. 개발 환경

- Window (Mac) + JDK + apache-tomcat + eclipse (intellij)

2. 서비스 환경

- Linux (Unix)+ JDK + apache-tomcat

* 배포 ( 수동배포 / 자동배포 ) : 개발환경 → 서비스환경 

 

▶ Java 웹 프로그램을 할 수 있는 방법

1. Servlet - Class 파일

2. JSP - HTML 유사

 

▶ 웹 프로그램 실행 환경

브라우저 (요청, 렌더링) / (Client) → 요청 → (웹서버) + WAS / (Server) <-> JSP / Servlet

- apache-tomcat은 WAS 중 하나

 

JSP

1. 확장자 : *.jsp

2. 4가지의 특수기호를 사용해서 코딩

  - <%@ : directive - JSP 페이지에 대한 설정

    ▶ page ( language / contentType / pageEncoding / import )

    ▶ include

    ▶ taglib

  - <%!    : declaration - 멤버 필드, 메서드 선언 (X)

  - <%     : scriptlet - java 소스 (지역변수, 제어문)

  - <%=   : expression - out.println

 

* JSP / Servlet / JDBC : Java EE 환경

 

 

Q. 만년력 만들기 : 년도와 월을 입력하면 그 달에 대한 달력 출력

- 파일 이름 : calendar.jsp / calendar_ok1.jsp

 

calendar.jsp
calendar_ok1.jsp

1. calendar.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>
</head>
<body>

<form action="calendar_ok1.jsp" method="post">
년도 <input type="text" name="year" />
월  <input type="text" name="month" />
<input type="submit" value="달력보기" />
</form>

</body>
</html>

 

2. calendar_ok1.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.Calendar" %>
<%
	request.setCharacterEncoding( "utf-8" );
	String strYear = request.getParameter( "year" );
	String strMonth = request.getParameter( "month" );
	
	// 설정 날짜
	int year = Integer.parseInt( strYear );
	int month = Integer.parseInt( strMonth );
	
	// 현재 날짜
	// Calendar nDay = Calendar.getInstance();
    
	// int year = nDay.get( Calendar.YEAR );
	// int month = nDay.get( Calendar.MONTH ) + 1;

	Calendar sDay = Calendar.getInstance();
	Calendar eDay = Calendar.getInstance();
    
	// 시작 날, 마지막 날 세팅
	sDay.set( year, month-1, 1 );
	eDay.set( year, month, 1-1 );
     
	// 1일의 요일
	int startDayOfWeek = sDay.get( Calendar.DAY_OF_WEEK );
	int endDayOfWeek = eDay.get( Calendar.DAY_OF_WEEK );
	int endDay = eDay.get( Calendar.DATE );
    
	StringBuilder sbHtml = new StringBuilder();
	
	sbHtml.append( "<table width='800' border='1'>" );
	sbHtml.append( "<tr>" );
	sbHtml.append( "<td colspan='7'>" + year + "년" + month + "월</td>" );
	sbHtml.append( "</tr>" );
	sbHtml.append( "<tr>" );
	sbHtml.append( "<td>SU</td><td>MO</td><td>TU</td><td>WE</td><td>TH</td><td>FR</td><td>SA</td>" );
	sbHtml.append( "</tr>" );
     
	sbHtml.append( "<tr>" );
	for( int i=1 ; i<startDayOfWeek ; i++ ) {
		sbHtml.append( "<td></td>" );
	}
     
	for( int i=1, n=startDayOfWeek ; i<=endDay ; i++, n++ ) {
		if( n % 7 == 1 ) sbHtml.append( "<tr>" );

		sbHtml.append( "<td>" + i + "</td>" );
          
		if( n % 7 == 0 ) sbHtml.append( "</tr>" );
	}
     
	for( int i=endDayOfWeek ; i<=6 ; i++ ) {
		sbHtml.append( "<td></td>" );
	}
	
	sbHtml.append( "</tr>" );
	sbHtml.append( "</table>" );
	
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%= sbHtml.toString() %>
</body>
</html>

 

 

Q. 위 예제에서 입력 검사 코드 추가하기 (calendar.jsp / calendar_ok1.jsp)

- 년, 월 미입력시 메시지 출력

- 월의 범위가 1~12가 아니면 메시지 출력

- calendar.jps <head> 태그 안에 자바스크립트 생성

<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
	window.onload = function() {
		document.getElementById( 'btn' ).onclick = function() {
			// alert( '버튼 클릭' );
			if( document.frm.year.value.trim() == '' ) {
				alert( '년도를 입력하셔야 합니다.' );
				return;
			}
			if( document.frm.month.value.trim() == '' ) {
				alert( '월을 입력하셔야 합니다.' );
				return;
			} else {
				let month = parseInt( document.frm.month.value.trim() );
				if( month <= 0 || month >= 13 ) {
					alert( '월을 정확하게 입력하셔야 합니다.' );
					return;
				}
			}
			document.frm.submit();
		};
	};
</script>
</head>

 

 

Q. Select 박스를 이용해 만년력 만들기 (calendar3.jsp / calendar_ok1.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>
</head>
<body>
<form action="calendar_ok1.jsp" method="post">
년도
<select name="year">
	<option value="2022">2022년</option>
	<option value="2023" selected="selected">2023년</option>
	<option value="2024">2024년</option>
</select>
월
<select name="month">
	<option value="1">1월</option>
	<option value="2">2월</option>
	<option value="3">3월</option>
	<option value="4" selected="selected">4월</option>
	<option value="5">5월</option>
	<option value="6">6월</option>
	<option value="7">7월</option>
	<option value="8">8월</option>
	<option value="9">9월</option>
	<option value="10">10월</option>
	<option value="11">11월</option>
	<option value="12">12월</option>
</select>
<input type="submit" value="달력보기" />
</form>
</body>
</html>

 

 

Q. 하나의 페이지에 select 박스로 년, 월을 입력하고 그 입력 데이터로 달력을 출력 (calendar_ok2.jsp)

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.Calendar" %>
<%
	request.setCharacterEncoding( "utf-8" );

	StringBuilder sbHtml = new StringBuilder();

	if ( request.getParameter( "year" ) == null || request.getParameter( "month" ) == null )	{
		// 입력 데이터가 없는 경우
		
	} else {
		
		// 입력 데이터가 있는 경우
		String strYear = request.getParameter( "year" );
		String strMonth = request.getParameter( "month" );
		
		int year = Integer.parseInt( strYear );
		int month = Integer.parseInt( strMonth );
	
		Calendar sDay = Calendar.getInstance();
		Calendar eDay = Calendar.getInstance();
	    
		sDay.set( year, month-1, 1 );
		eDay.set( year, month, 1-1 );
	     
		int startDayOfWeek = sDay.get( Calendar.DAY_OF_WEEK );
		int endDayOfWeek = eDay.get( Calendar.DAY_OF_WEEK );
		int endDay = eDay.get( Calendar.DATE );
		
		sbHtml.append( "<table width='800' border='1'>" );
		sbHtml.append( "<tr>" );
		sbHtml.append( "<td colspan='7'>" + year + "년" + month + "월</td>" );
		sbHtml.append( "</tr>" );
		sbHtml.append( "<tr>" );
		sbHtml.append( "<td>SU</td><td>MO</td><td>TU</td><td>WE</td><td>TH</td><td>FR</td><td>SA</td>" );
		sbHtml.append( "</tr>" );
	     
		sbHtml.append( "<tr>" );
		for( int i=1 ; i<startDayOfWeek ; i++ ) {
			sbHtml.append( "<td></td>" );
		}
	     
		for( int i=1, n=startDayOfWeek ; i<=endDay ; i++, n++ ) {
			if( n % 7 == 1 ) sbHtml.append( "<tr>" );
	
			sbHtml.append( "<td>" + i + "</td>" );
	          
			if( n % 7 == 0 ) sbHtml.append( "</tr>" );
		}
	     
		for( int i=endDayOfWeek ; i<=6 ; i++ ) {
			sbHtml.append( "<td></td>" );
		}
		
		sbHtml.append( "</tr>" );
		sbHtml.append( "</table>" );
		
	}
	
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<!-- calendar_ok2.jsp -->

<form action="calendar_ok2.jsp" method="post">
년도
<select name="year">
	<option value="2022">2022년</option>
	<option value="2023" selected="selected">2023년</option>
	<option value="2024">2024년</option>
</select>
월
<select name="month">
	<option value="1">1월</option>
	<option value="2">2월</option>
	<option value="3">3월</option>
	<option value="4" selected="selected">4월</option>
	<option value="5">5월</option>
	<option value="6">6월</option>
	<option value="7">7월</option>
	<option value="8">8월</option>
	<option value="9">9월</option>
	<option value="10">10월</option>
	<option value="11">11월</option>
	<option value="12">12월</option>
</select>
<input type="submit" value="달력보기" />
</form>

<%= sbHtml.toString() %>
</body>
</html>

 

 

▶ JDBCEx01 프로젝트 생성 후 JDBC 드라이버 넣기

- 경로를 확인한다. ( lib 폴더 )

 

 

Q. 드라이버를 로딩하고 데이터베이스를 연결 ( jdbc01.jsp )

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.SQLException" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%

	String url = "jdbc:mariadb://localhost:3306/sample";
	String user = "root";
	String password = "123456";
	
	Connection conn = null;
	
	try {
		
		Class.forName( "org.mariadb.jdbc.Driver" );
		out.println( "드라이버 로딩 성공<br />" );
		
		conn = DriverManager.getConnection( url, user, password );
		out.println( "데이터베이스 연결 성공<br />" );
		
	} catch( ClassNotFoundException e ) {
		System.out.println( "[에러] : " + e.getMessage() );
	} catch( SQLException e ) {
		System.out.println( "[에러] : " + e.getMessage() );
	} finally {
		if ( conn != null ) conn.close();
	}
	
%>
</body>
</html>

 

 

Q. 데이터베이스의 dept 테이블을 출력 ( jdbc02.jsp )

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.PreparedStatement"%>
<%@ page import="java.sql.ResultSet"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%

	String url = "jdbc:mariadb://localhost:3306/sample";
	String user = "root";
	String password = "123456";
	
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	try {
		
		Class.forName( "org.mariadb.jdbc.Driver" );
		
		conn = DriverManager.getConnection( url, user, password );
		
		String sql = "select * from dept";
		pstmt = conn.prepareStatement( sql );
		
		rs = pstmt.executeQuery();
		
		out.println( "<table width='800' border='1'>" );
		while( rs.next() ) {
			out.println( "<tr>" );
			out.println( "<td>" + rs.getString("deptno") + "</td>" );
			out.println( "<td>" + rs.getString("dname") + "</td>" );
			out.println( "<td>" + rs.getString("loc") + "</td>" );
			out.println( "</tr>" );
		}
		out.println( "</table>" );
		
	} catch( ClassNotFoundException e ) {
		System.out.println( "[에러] : " + e.getMessage() );
	} catch( SQLException e ) {
		System.out.println( "[에러] : " + e.getMessage() );
	} finally {
		if ( rs != null ) rs.close();
		if ( pstmt != null ) pstmt.close();
		if ( conn != null ) conn.close();
	}
	
%>
</body>
</html>

 

 

Q. 위 코드를 자바 부분과 HTML 부분으로 분리

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.PreparedStatement"%>
<%@ page import="java.sql.ResultSet"%>
<%

	String url = "jdbc:mariadb://localhost:3306/sample";
	String user = "root";
	String password = "123456";
	
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
		StringBuilder sbHtml = new StringBuilder();
		
	try {
		
		Class.forName( "org.mariadb.jdbc.Driver" );
		
		conn = DriverManager.getConnection( url, user, password );
		
		String sql = "select * from dept";
		pstmt = conn.prepareStatement( sql );
		
		rs = pstmt.executeQuery();
		
		sbHtml.append( "<table width='800' border='1'>" );
		
		while( rs.next() ) {
			sbHtml.append( "<tr>" );
			sbHtml.append( "<td>" + rs.getString("deptno") + "</td>" );
			sbHtml.append( "<td>" + rs.getString("dname") + "</td>" );
			sbHtml.append( "<td>" + rs.getString("loc") + "</td>" );
			sbHtml.append( "</tr>" );
		}
		sbHtml.append( "</table>" );
		
	} catch( ClassNotFoundException e ) {
		System.out.println( "[에러] : " + e.getMessage() );
	} catch( SQLException e ) {
		System.out.println( "[에러] : " + e.getMessage() );
	} finally {
		if ( rs != null ) rs.close();
		if ( pstmt != null ) pstmt.close();
		if ( conn != null ) conn.close();
	}
	
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%= sbHtml %>
</body>
</html>

 

 

 

Q. 우편번호 검색기 만들기 ( zipcode.jsp )

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.PreparedStatement"%>
<%@ page import="java.sql.ResultSet"%>
<%

	request.setCharacterEncoding( "utf-8" );
	
	StringBuilder sbHtml = new StringBuilder();
	
	if ( request.getParameter( "dong" ) != null ) {
		
		String strDong = request.getParameter( "dong" );
		
		String url = "jdbc:mariadb://localhost:3306/project";
		String user = "root";
		String password = "123456";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			
			Class.forName( "org.mariadb.jdbc.Driver" );
			conn = DriverManager.getConnection( url, user, password );
			
			String sql = "select zipcode, sido, gugun, dong, ri, bunji from zipcode where dong like ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setString( 1, strDong + "%" );
			
			rs = pstmt.executeQuery();
			
			sbHtml.append( "<table width='800' border='1'>" );
			while( rs.next() ) {
				sbHtml.append( "<tr>" );
				sbHtml.append( "<td>" + rs.getString( "zipcode" ) + "</td>" );
				sbHtml.append( "<td>" + rs.getString( "sido" ) + "</td>" );
				sbHtml.append( "<td>" + rs.getString( "gugun" ) + "</td>" );
				sbHtml.append( "<td>" + rs.getString( "dong" ) + "</td>" );
				sbHtml.append( "<td>" + rs.getString( "ri" ) + "</td>" );
				sbHtml.append( "<td>" + rs.getString( "bunji" ) + "</td>" );
				sbHtml.append( "</tr>" );
			}
			sbHtml.append( "</table>" );
			
		} catch( ClassNotFoundException e ) {
			System.out.println( "[에러] : " + e.getMessage() );
		} catch( SQLException e ) {
			System.out.println( "[에러] : " + e.getMessage() );
		} finally {
			if ( rs != null ) rs.close();
			if ( pstmt != null ) pstmt.close();
			if ( conn != null ) conn.close();
		}
		
	}

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

<form action="./zipcode.jsp" method="post" name="frm">
	<fieldset>
		<legend>우편번호 검색</legend>
		<label for="dong">동이름 입력</label>
		<input type="text" id="dong" name="dong" size="40" placeholder="동이름 입력" />
		<input type="submit" id="btn" value="우편번호 검색" />
	</fieldset>
</form>

<%= sbHtml %>

</body>
</html>

 

 

데이터베이스 풀링

- 미리 만들어놓은 접속

- tomcat 시작 → 풀링 → JNDI → 프로그램 접근

 

 

Q. JDBCEx02 프로젝트 생성 후 JDBC 드라이버 등록. context.xml 파일 생성 및 Resource 내용 작성

- 실행 시 tomcat을 완전히 종료 후 다시 켜서 연결해야 한다.

 

1. context.xml

<?xml version="1.0" encoding="utf-8" ?>
<Context>

	<Resource
		name="jdbc/mariadb1"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="org.mariadb.jdbc.Driver"
		url="jdbc:mariadb://localhost:3306/sample"
		username="root"
		password="123456" />
		
	<Resource
		name="jdbc/mariadb2"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="org.mariadb.jdbc.Driver"
		url="jdbc:mariadb://localhost:3306/project"
		username="project"
		password="1234" />
        
</Context>

 

 

2. jdbc01.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.naming.NamingException" %>

<%@ page import="javax.sql.DataSource" %>

<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.SQLException" %>

<%
	Connection conn = null;

	Context initCtx = new InitialContext();
	Context envCtx = (Context)initCtx.lookup( "java:comp/env" );
	DataSource dataSource = (DataSource)envCtx.lookup( "jdbc/mariadb1" );

	conn = dataSource.getConnection();
	System.out.println( "데이터베이스 연결 성공" );
	
	conn.close();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

</body>
</html>

 

 

 

Q1. jdbc02를 만들고 데이터베이스 풀링으로부터 dept 테이블의 deptno 목록 가져오기

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.naming.NamingException" %>

<%@ page import="javax.sql.DataSource" %>

<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.SQLException" %>

<%
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;

	try {
		Context initCtx = new InitialContext();
		Context envCtx = (Context)initCtx.lookup( "java:comp/env" );
		DataSource dataSource = (DataSource)envCtx.lookup( "jdbc/mariadb1" );
	
		conn = dataSource.getConnection();
		
		String sql = "select * from dept";
		pstmt = conn.prepareStatement( sql );
		
		rs = pstmt.executeQuery();
		
		while( rs.next() ) {
			System.out.println( rs.getString( "deptno" ) );
		}
		
	} catch ( NamingException e ) {
		System.out.println( "[에러] " + e.getMessage() );
	} catch ( SQLException e ) {
		System.out.println( "[에러] " + e.getMessage() );
	} finally {
		if( rs != null ) rs.close();
		if( pstmt != null ) pstmt.close();
		if( conn != null ) conn.close();
	}
	
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

</body>
</html>

 

 

Q2. 새 프로젝트 ZipcodeEx01을 생성하고 lib 폴더에 JDBC 드라이버를 등록한다.

- context.xml, zipcode.jsp 파일 생성 후 데이터베이스 풀링을 이용하여 우편번호 검색기 만들기

 

1. context.xml

<?xml version="1.0" encoding="utf-8" ?>
<Context>
	<Resource
		name="jdbc/mariadb1"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="org.mariadb.jdbc.Driver"
		url="jdbc:mariadb://localhost:3306/sample"
		username="root"
		password="123456" />
		
	<Resource
		name="jdbc/mariadb2"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="org.mariadb.jdbc.Driver"
		url="jdbc:mariadb://localhost:3306/project"
		username="project"
		password="1234" />
</Context>

 

2. zipcode.jsp

<!-- (동이름 입력) 우편번호 검색기 -->

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
	<%@page import="java.sql.ResultSet"%>
	<%@page import="java.sql.PreparedStatement"%>
	<%@ page import="javax.naming.Context" %>
	<%@ page import="javax.naming.InitialContext" %>
	<%@ page import="javax.naming.NamingException" %>
	
	<%@ page import="javax.sql.DataSource" %>
	
	<%@ page import="java.sql.Connection" %>
	<%@ page import="java.sql.SQLException" %>
<%

	request.setCharacterEncoding( "utf-8" );
	
	StringBuilder sbHtml = new StringBuilder();
	
	if ( request.getParameter( "dong" ) != null ) {
		
		String strDong = request.getParameter( "dong" );;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			
			Context initCtx = new InitialContext();
			Context envCtx = (Context)initCtx.lookup( "java:comp/env" );
			DataSource dataSource = (DataSource)envCtx.lookup( "jdbc/mariadb2" );
		
			conn = dataSource.getConnection();
			
			String sql = "select zipcode, sido, gugun, dong, ri, bunji from zipcode where dong like ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setString( 1, strDong + "%" );
			
			rs = pstmt.executeQuery();
			
			sbHtml.append( "<table width='800' border='1'>" );
			while( rs.next() ) {
				sbHtml.append( "<tr>" );
				sbHtml.append( "<td>" + rs.getString( "zipcode" ) + "</td>" );
				sbHtml.append( "<td>" + rs.getString( "sido" ) + "</td>" );
				sbHtml.append( "<td>" + rs.getString( "gugun" ) + "</td>" );
				sbHtml.append( "<td>" + rs.getString( "dong" ) + "</td>" );
				sbHtml.append( "<td>" + rs.getString( "ri" ) + "</td>" );
				sbHtml.append( "<td>" + rs.getString( "bunji" ) + "</td>" );
				sbHtml.append( "</tr>" );
			}
			sbHtml.append( "</table>" );
			
		} catch( NamingException e ) {
			System.out.println( "[에러] : " + e.getMessage() );
		} catch( SQLException e ) {
			System.out.println( "[에러] : " + e.getMessage() );
		} finally {
			if ( rs != null ) rs.close();
			if ( pstmt != null ) pstmt.close();
			if ( conn != null ) conn.close();
		}
		
	}

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

<form action="./zipcode.jsp" method="post" name="frm">
	<fieldset>
		<legend>우편번호 검색</legend>
		<label for="dong">동이름 입력</label>
		<input type="text" id="dong" name="dong" size="40" placeholder="동이름 입력" />
		<input type="submit" id="btn" value="우편번호 검색" />
	</fieldset>
</form>

<%= sbHtml %>

</body>
</html>

 

 

 

Q. 데이터베이스 풀링을 기반으로 시도 → 구군 순으로 선택 리스트가 발생하는 우편번호 검색기 만들기 ( 미완성 )

<!-- 우편번호 검색기. 시도 → 구군 순으로 선택 리스트 발생 -->
<!-- 미완성 -->

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.naming.NamingException" %>

<%@ page import="javax.sql.DataSource" %>

<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>

<%
	request.setCharacterEncoding( "utf-8" );
	
	String strSido = request.getParameter( "sido" );
	String strGugun = request.getParameter( "gugun" );
	String strDong = request.getParameter( "dong" );

	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	StringBuffer sbSido = new StringBuffer();
	StringBuffer sbGugun = new StringBuffer();
	StringBuilder sbAddress = new StringBuilder();
	
	try {
		
		Context initCtx = new InitialContext();
		Context envCtx = (Context)initCtx.lookup( "java:comp/env" );
		DataSource dataSource = (DataSource)envCtx.lookup( "jdbc/mariadb2" );
		
		conn = dataSource.getConnection();
	
		String sql = "select distinct sido from zipcode";
		
		pstmt = conn.prepareStatement( sql );
		
		rs = pstmt.executeQuery();
		
		while( rs.next() ) {
			sbSido.append( "<option value='" + rs.getString( "sido" ) + "'>" + rs.getString( "sido" ) + "</option>" );
		}
		
		if ( request.getParameter( "sido" ) != null ) {
			
			sql = "select distinct gugun from zipcode where sido=?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setString( 1, strSido );
			
			rs = pstmt.executeQuery();
			
			while( rs.next() ) {
				sbGugun.append( "<option value='" + rs.getString( "gugun" ) + "'>" + rs.getString( "gugun" ) + "</option>" );
			}
			
		}
		
		if ( request.getParameter( "gugun" ) != null ) {
			
			sql = "select zipcode, dong, ri, bunji from zipcode where sido='서울' and gugun=?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setString( 1, strGugun );
			
			rs = pstmt.executeQuery();
			
			sbAddress.append( "<table width='800' border='1'>" );
			while( rs.next() ) {
				sbAddress.append( "<tr>" );
				sbAddress.append( "<td>" + rs.getString( "zipcode" ) + "</td>" );
				sbAddress.append( "<td>" + rs.getString( "dong" ) + "</td>" );
				sbAddress.append( "<td>" + rs.getString( "ri" ) + "</td>" );
				sbAddress.append( "<td>" + rs.getString( "bunji" ) + "</td>" );
				sbAddress.append( "</tr>" );
			}
			sbAddress.append( "</table>" );
		}
		
	} catch( NamingException e ) {
		System.out.println( "[에러] " + e.getMessage() );
	} catch( SQLException e ) {
		System.out.println( "[에러] " + e.getMessage() );
	} finally {
		if( rs != null ) rs.close();
		if( pstmt != null ) pstmt.close();
		if( conn != null ) conn.close();
	}
%>


<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style type="text/css">
	table	{ border: 1px solid black; font-size: 15px; border-collapse: collapse; }
	td		{ width: 800px; padding: 10px; text-align: center; border: 1px solid black; }
	#sido	{ width: 100px; }
	#gugun	{ width: 100px; }
</style>
<script type="text/javascript">
	window.onload = function() {
		document.getElementById( 'sido' ).onchange = function() {
			
			// const box = document.getElementById( 'sido' );  //select box
			// const length = box.options.length; //select box의 option 갯수
			const strValue = document.getElementById( 'sido' ).value; //입력 받은 value 값
			
			//select box의 option 갯수만큼 for문 돌림
			// for (let i=0; i<length; i++){  
				//select box의 option value가 입력 받은 value의 값과 일치할 경우 selected
				// if ( box.options[i].value == strValue ){
					// box.options[i].selected = true;
				// }
			// }  
			
			// const sido = document.getElementById( 'sido' ).value;
			// alert( sido );
			document.frm.submit();
			
		};
		
		// for (let i=0; i<length; i++){  
			// select box의 option value가 입력 받은 value의 값과 일치할 경우 selected
			// if ( box.options[i].value == strValue ){
				// box.options[i].selected = true;
			// }
		// }
		
		
		document.getElementById( 'gugun' ).onchange = function() {
			const gugun = document.getElementById( 'gugun' ).value;
			document.frm.submit();
		};

	};
</script>
</head>
<body>

<form action="zipcode2.jsp" method="post" name="frm">
<table>
<tr>
	<td>
		<select name="sido" id="sido">
			<option value="시도"></option>
			<%=sbSido %>
		</select>
		<select name="gugun" id="gugun">
			<option value="구군"></option>
			<%=sbGugun %>
		</select>
		<input type="text" name="dong" id="dong" size="40" />
		<input type="submit" value="우편번호검색" />
	</td>
</tr>
</table>
</form>

<%= sbAddress %>

</body>
</html>

 

 

 

 

반응형
저작자표시 비영리 변경금지 (새창열림)

'KIC 백엔드 수업' 카테고리의 다른 글

4월 27일 (목) - []  (0) 2023.04.27
4월 26일 (수) - [ 게시판 : 최신글 new, 조회수 증가, 이모티콘 게시판, 페이징 기법 ]  (0) 2023.04.26
4월 21일 (금) - [ 이클립스와 JSP 환경 설정 ]  (0) 2023.04.21
4월 20일 (목) - [ Lombok, Chart ]  (0) 2023.04.20
4월 19일 (수) - [ 메시지 / 이메일 전송 ]  (0) 2023.04.19
'KIC 백엔드 수업' Related Articles more
Comments