[Day 20 | JSP] 우편번호검색기.jsp

y♡ding·2024년 11월 8일
0

데브코스 TIL

목록 보기
135/163
//zipcode1.jsp 
<%--
  Created by IntelliJ IDEA.
  User: leehayeon
  Date: 2024. 11. 8.
  Time: 11:01
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <script type="text/javascript">
        const checkfrm = function () {
            //alert("checkfrm() 호출")

            // 입력값 검사
            if (document.frm.dong.value.trim().length < 2 ) {
                alert('2자 이상의 동이름을 입력하세요.');
                return false;
            }
            document.frm.submit();
        };
    </script>
</head>
<body>
<!-- zipcode1.jsp -->

<form action="zipcode1_ok.jsp" method="post" name="frm">
    동이름 입력 <input type="text" name="dong" maxlength="5" />
    <input type="button" value="동이름 검색" onclick="checkfrm()"/>
</form>
</body>
</html>
-------------------------------------
// zipcode1_ok.jsp
<%--
  Created by IntelliJ IDEA.
  User: leehayeon
  Date: 2024. 11. 8.
  Time: 11:02
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<%@ 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 strDong = request.getParameter("dong");

    String url = "jdbc:mariadb://localhost:3306/sample";
    String user = "root";
    String password = "exxyeon";

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

    StringBuilder sb = new StringBuilder();
    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 + "%");  // strDong으로 시작하는 동 검색

        rs = pstmt.executeQuery();

        sb.append("<table border='1' cellspacing='0'>");
        while (rs.next()) {
            sb.append(("<tr>"));
            sb.append(("<td>" + rs.getString("zipcode") + "</td>"));
            sb.append(("<td>" + rs.getString("sido") + "</td>"));
            sb.append(("<td>" + rs.getString("gugun") + "</td>"));
            sb.append(("<td>" + rs.getString("dong") + "</td>"));
            sb.append(("<td>" + rs.getString("ri") + "</td>"));
            sb.append(("<td>" + rs.getString("bunji") + "</td>"));
            sb.append(("</tr>"));
        }
        sb.append("</table>");
    } catch (ClassNotFoundException e) {
        System.out.println("[에러] " + e.getMessage());
    } catch (SQLException e) {
        System.out.println("[에러] " + e.getMessage());
    } finally {
        if (conn != null) conn.close();
        if (rs != null) conn.close();
        if (pstmt != null) conn.close();
    }
%>
<html>
<head>
    <title>Title</title>
</head>
<body>
<!-- zipcode1_ok -->
<%= sb.toString() %>
</body>
</html>
// -------------------------------------------------------
// 검색 시 다음 페이지에서 실행이 아닌, 한 페이지 아래에 결과 출력
// 디자인 / 결과 통합출력: zipcode.jsp
<%--
  Created by IntelliJ IDEA.
  User: kevin
  Date: 2024-11-08
  Time: 오전 11:02
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>

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

<%
    StringBuilder sbHtml = new StringBuilder();

    // 전송값 검사
    if (request.getParameter("dong") != null) {

        String strDong = request.getParameter("dong");

        String url = "jdbc:mariadb://localhost:3306/sample";
        String user = "root";
        String password = "exxyeon";

        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 border='1' cellspacing='0'>");
            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();
        }
    }
%>
<html>
<head>
    <title>Title</title>
    <script type="text/javascript">
        const checkfrm = function () {
            //alert( "checkfrm() 호출" );
            // 입력값 검사
            // 필수입력항목 검사
            //if ( document.frm.dong.value == '' ) {
            // 입력 길이 검사
            if (document.frm.dong.value.trim().length < 2) {
                alert('2자 이상의 동이름을 입력하셔야 합니다.');
                return false;
            }
            //alert( '정상' );
            document.frm.submit();
        };
    </script>
</head>
<body>

<!-- zipcode.jsp -->
<form action="zipcode.jsp" method="post" name="frm">
    동이름 입력 <input type="text" name="dong" maxlength="5"/>
    <input type="button" value="동이름 검색" onclick="checkfrm()"/>
</form>

<hr/>

<%=sbHtml.toString()%>

</body>
</html>

데이터베이스를 직접 연결에서 풀링을 통한 연결로 바꾸기 (zipcode.jsp)

<%--
  Created by IntelliJ IDEA.
  User: leehayeon
  Date: 2024. 11. 8.
  Time: 12:40
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.naming.NamingException" %>

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

<%@ page import="java.sql.*" %>

<%
  StringBuilder sbHtml = new StringBuilder();

  if (request.getParameter("dong") != null) {

    String strDong = request.getParameter("dong");

    String url = "jdbc:mariadb://localhost:3306/sample";
    String user = "root";
    String password = "exxyeon";

    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 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 border='1' cellspacing='0'>");
      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();
    }
  }
%>
<html>
<head>
  <title>Title</title>
  <script type="text/javascript">
    const checkfrm = function () {
      if (document.frm.dong.value.trim().length < 2) {
        alert('2자 이상의 동이름을 입력하셔야 합니다.');
        return false;
      }
      document.frm.submit();
    };
  </script>
</head>
<body>

<!-- zipcode.jsp -->
<form action="zipcode.jsp" method="post" name="frm">
  동이름 입력 <input type="text" name="dong" maxlength="5"/>
  <input type="button" value="동이름 검색" onclick="checkfrm()"/>
</form>

<hr/>

<%=sbHtml.toString()%>

</body>
</html>
profile
y♡ding
이전 포스트

[Day 20 | JSP]JSP 파일 배포와 데이터베이스 연결

다음 포스트

[Day 20] 게시판 구현

0개의 댓글

관련 채용 정보