자바스크립트, DB 이용하여 주소록 생성하기

이름을 눌렀을때 수정, 삭제 할 수 있는 선택지가 주어짐 (detail.jsp)

우편번호 검색 버튼을 눌렀을 경우 (detail.jsp에서 zipCheck.jsp로 넘어감)

Address.java : 게터 세터, 생성자

package com.address;

public class Address {

    private long num;

    private String name;

    private String zipcode;

    private String tel;

    private String addr;

    

    public long getNum() {

        return num;

    }

    public void setNum(long num) {

        this.num = num;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public String getZipcode() {

        return zipcode;

    }

    public void setZipcode(String zipcode) {

        this.zipcode = zipcode;

    }

    public String getTel() {

        return tel;

    }

    public void setTel(String tel) {

        this.tel = tel;

    }

    public String getAddr() {

        return addr;

    }

    public void setAddr(String addr) {

        this.addr = addr;

    }

}

AddressDAO.java : 입력 수정 삭제 등 DB와 연결

package com.address;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.sql.DataSource;

public class AddressDAO {

    //싱글톤 패턴. 최초 한번만 메모리 할당 (static)

    //AddressDAO클래스에 인스턴스를 만들어 사용

    private static AddressDAO instance = new AddressDAO();

    public static AddressDAO getInstance() {

        return instance;

    }

    //디비연결 (scott의 address 테이블)

    private Connection getConnection() throws Exception{

        Context initCtx=new InitialContext();

        Context envCtx=(Context)initCtx.lookup("java:comp/env");

        DataSource ds=(DataSource)envCtx.lookup("jdbc/jsp");

        //톰캣에서 jdbc/jsp라는 이름을 찾아서 커넥션 시킨다.

        return ds.getConnection();

    }

    

    //추가

    public void addrInsert(Address ad) {

        Connection con=null;

        PreparedStatement ps=null;

        try {

            con=getConnection();

            String sql="INSERT INTO address "

                    + "VALUES (address_seq.nextval,?,?,?,?)";

            ps=con.prepareStatement(sql);

            ps.setString(1, ad.getName());

            ps.setString(2, ad.getZipcode());

            ps.setString(3, ad.getAddr());

            ps.setString(4, ad.getTel());

            ps.executeUpdate();

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            closeConnection(con,ps);

        }

    }

    

    //수정

    public void addrUpdate(Address ad) {

        Connection con=null;

        PreparedStatement ps=null;

        try {

            con=getConnection();

            String sql="UPDATE address SET name=?, zipcode=?, "

                    + "addr=?, tel=? WHERE num=?";

            ps=con.prepareStatement(sql);

            ps.setString(1, ad.getName());

            ps.setString(2, ad.getZipcode());

            ps.setString(3, ad.getAddr());

            ps.setString(4, ad.getTel());

            ps.setLong(5, ad.getNum());

            ps.executeUpdate();

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            closeConnection(con, ps);

        }

    }

    

    //전체보기

    public ArrayList<Address> addrList(String field, String word){

        Connection con=null;

        Statement st=null;

        ResultSet rs=null;

        String sql="";

        ArrayList<Address> arr=new ArrayList<Address>();

        try {

            con=getConnection();

            if(word.equals("")) { //검색이 아닐떄

                sql="SELECT * FROM address";

            }else { //검색일때

                sql="SELECT * FROM address WHERE "+field+" LIKE '%"+word+"%'";

                //sql="select * from address where tel like '%010%'";

            }

            st=con.createStatement();

            rs=st.executeQuery(sql);

            while(rs.next()) {

                Address ad=new Address();

                ad.setNum(rs.getInt("num"));

                ad.setName(rs.getString("name"));

                ad.setAddr(rs.getString("addr"));

                ad.setZipcode(rs.getString("zipcode"));

                ad.setTel(rs.getString("tel"));

                arr.add(ad);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            closeConnection(con,st,rs);

        }

        return arr;

    }

    

    //개수출력

    public int getCount(String field, String word) {

        Connection con=null;

        Statement st=null;

        ResultSet rs=null;

        String sql="";

        int count=0;

        try {

            con=getConnection();

            if(word.equals("")) { //검색아니고 전체

                sql="SELECT COUNT(*) FROM address";

            }else { //검색일때

                sql="SELECT COUNT(*) FROM address WHERE "+field+" LIKE '%"+word+"%'";

            }

            st=con.createStatement();

            rs=st.executeQuery(sql);

            if(rs.next()) {

                count=rs.getInt(1); //address테이블의 첫번째 컬럼의 개수

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            closeConnection(con, st, rs);

        }

        return count;

    }

    

    //상세보기

    public Address addrDetail(int num) {

        Connection con=null;

        Statement st=null;

        ResultSet rs=null;

        Address ad=null;

        try {

            con=getConnection();

            String sql="SELECT * FROM address where num="+num;

            st=con.createStatement();

            rs=st.executeQuery(sql);

            if(rs.next()) {

                ad=new Address();

                ad.setAddr(rs.getString("addr"));

                ad.setName(rs.getString("name"));

                ad.setNum(rs.getLong("num"));

                ad.setTel(rs.getString("tel"));

                ad.setZipcode(rs.getString("zipcode"));

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            closeConnection(con,st,rs);

        }

        return ad;

    }

    

    //삭제

    public void addrDelete(int num) {

        Connection con=null;

        Statement st=null;

        try {

            con=getConnection();

            String sql="DELETE FROM address WHERE num="+num;

            st=con.createStatement();

            st.executeUpdate(sql);

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            closeConnection(con, st, null);

        }

    }

    

    

    //우편번호 검색

    public ArrayList<ZipCodeBean> zipcodeRead(String dong) {

        Connection con=null;

        Statement st=null;

        ResultSet rs=null;

        ArrayList<ZipCodeBean> zipArr=new ArrayList<>();

        //결과값이 여러개라서 ArrayList사용

        

        try {

            con=getConnection();

            //select * from zipcode where dong like '%서면%';

            String sql="SELECT * FROM zipcode WHERE dong like '%"+dong+"%'";

            st=con.createStatement();

            rs=st.executeQuery(sql);

            while(rs.next()) {

                ZipCodeBean zip=new ZipCodeBean();

                zip.setBunji(rs.getString("bunji"));

                zip.setDong(rs.getString("dong"));

                zip.setGugun(rs.getString("gugun"));

                zip.setSeq(rs.getInt("seq"));

                zip.setSido(rs.getString("sido"));

                zip.setZipcode(rs.getString("zipcode"));

                zipArr.add(zip);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            closeConnection(con, st, rs);

        }

        return zipArr;

    }

    

    

    //closeConnection

    private void closeConnection(Connection con, PreparedStatement ps) {

        try {

            if(ps!=null) ps.close();

            if(con!=null) con.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

    private void closeConnection(Connection con, Statement st, ResultSet rs) {

        try {

            if(st!=null) st.close();

            if(con!=null) con.close();

            if(rs!=null) rs.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}

list : 주소록 목록을 보여주는 jsp

<%@page import="com.address.Address"%>

<%@page import="com.address.AddressDAO"%>

<%@page import="java.util.ArrayList"%>

<%@ 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>

<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css" integrity="sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc+NcPb1dKGj7Sk" crossorigin="anonymous">

<style>

div.divCss{

    text-align: right;

    background-color: darkgray;

    padding-right: 20px;

}

a:hover{text-decoration: none;}

a:link{text-decoration: none;}

a:visited{text-decoration: none;}

</style>

</head>

<script src="../js/jquery-3.5.1.min.js"></script>

<script>

    function searchCheck(){

        if($("#word").val()==""){

            alert("검색어를 입력하세요");

            $("#word").focus();

            return false;

        }

        $("#searchFrm").submit();

    }

    

    function delFunc(no){

        if(confirm("정말 삭제할까요?")){

            location.href="deletePro.jsp?num="+no;

        }

    }

</script>

<%

    request.setCharacterEncoding("utf-8");

    String word ="";

    String field="";

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

        field=request.getParameter("field");

        word=request.getParameter("word");

    }

    AddressDAO dao=AddressDAO.getInstance();

    ArrayList<Address> arr=dao.addrList(field, word);

    int count=dao.getCount(field, word); //전체 개수 혹은 검색한 개수 출력

%>

<body>

<div class="divCss"> <!-- 문단태그 -->

    주소록 갯수: <%=count %><br>

    <a href="insert.jsp">추가하기 /</a>

    <a href="list.jsp">전체보기</a>

</div>

<table class="table">

        <thead class="thead-dark">              <!-- 이미 정의되어 있는 테이블 스타일 -->

          <tr>

            <th scope="col">번호</th>

            <th scope="col">이름</th>

            <th scope="col">우편번호</th>

            <th scope="col">주소</th>

            <th scope="col">전화번호</th>

            <th scope="col">삭제</th>

          </tr>

        </thead>

    <tbody>

    <%

        for(int i=0;i<arr.size();i++){

    %> <!-- 태그는 스크립틀릿안에 들어가면 안되므로 따로 빼줌 -->

        <tr>

            <th scope="row"><%=arr.get(i).getNum() %></th>

            <td>

            <a href="detail.jsp?num=<%=arr.get(i).getNum() %>">

            <%=arr.get(i).getName() %></a></td>

            <td><%=arr.get(i).getZipcode() %></td>

            <td><%=arr.get(i).getAddr() %></td>

            <td><%=arr.get(i).getTel() %></td>

            <td onclick="delFunc(<%=arr.get(i).getNum()%>)">삭제</td>

        </tr>

    <%} //end for문

    %>

    </tbody>

</table>

<form action="list.jsp" name="searchFrm" id="searchFrm">

    <select name="field">

        <option value="name">이름</option>

        <option value="tel">전화번호</option>

    </select>

    <input type="text" name="word" id="word">

    <input type="button" value="검색" class="btn btn-primary" onclick="searchCheck()">

</form>

</body>

</html>

detail.jsp: 주소록 수정 및 삭제 jsp

<%@page import="com.address.Address"%>

<%@page import="com.address.AddressDAO"%>

<%@ 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>

<script src="../js/jquery-3.5.1.min.js"> </script> <!-- cdn방식 -->

<%

    request.setCharacterEncoding("utf-8");

    int num=Integer.parseInt(request.getParameter("num"));

    AddressDAO dao=AddressDAO.getInstance();

    Address address=dao.addrDetail(num);

%>

<script>

//query 이용

$(document).ready(function(){ //document.ready를 통해 메소드를 로드해야함.

    $("#deleteBtn").click(function(){

        if(confirm("정말 삭제할까요?")){

            //location.href="deletePro.jsp?num=<%=num%>";

            $(location).attr("href","deletePro.jsp?num=<%=num%>");

        }

    });

})

function del(){

    if(confirm("정말 삭제할까요?")){

        location.href="deletePro.jsp?num=<%=num%>";

    }

}

//매개변수 있는 함수

function dels(no){

    if(confirm("정말 삭제할까요?")){

        location.href="deletePro.jsp?num="+no;

    }

}

</script>

</head>

<body>

<form action="updatePro.jsp" method="post">

<input type="hidden" name="num" value=<%=num %>> <!-- hidden: 사용자에겐 보이지 않지만 서버한테 전달됨 -->>

<table>

    <tr>

        <td colspan="2">주소록 수정하기</td>

    </tr>

    <tr>

        <td>이름</td>

        <td><input type="text" name="name"

        value="<%=address.getName()%>"></td>

    </tr>

    <tr>

        <td>우편번호 </td>

        <td><input type="text" name="zipcode" size=10

        value="<%=address.getZipcode()%>">

        <input type="button" name="search" value="검색"></td>

    <tr>

        <td>주소

        <td><input type="text" name="addr" size=30

        value="<%=address.getAddr()%>"></td>

    </tr>

    </tr>

        <td>전화번호</td>

        <td><input type="text" name="tel"

        value="<%=address.getTel()%>"></td>

    <tr>

        <td colspan="2">

        <input type="submit" value="수정">

        <input type="button" value="삭제" onclick="del()"> <!-- del()이라는 함수를 부른다. -->

        <input type="button" value="매개변수삭제" onclick="dels(<%=num%>)"> <!-- 인자가 있는 함수를 부른다.파라미터값이 하나-->

        <input type="button" value="jquery삭제" id="deleteBtn"> <!-- deleteBtn이라는 아이디 값을 하나 준다. -->

        <input type="reset" value="취소">

        </td>

    </tr>

</table>

</form>

</body>

</html>

insert.jsp : 주소록 입력 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>

<script src="../js/jquery-3.5.1.min.js"></script>

<script>

$(function(){

    $("#btn").on("click",function(){

        if($("#name").val()==""){

            alert("이름을 입력하세요");

            return false;

        }

        if($("#zipcode").val()==""){

            alert("우편번호을 입력하세요");

            return false;

        }

        if($("#addr").val()==""){

            alert("주소를 입력하세요");

            return false;

        }

        if($("#tel").val()==""){

            alert("전화번호를 입력하세요");

            return false;

        }

        frm.submit();

    });

})

function zipfinder(){

    window.open("zipCheck.jsp","","width=700 height=400");   

}

</script>

</head>

<body>

<a href="list.jsp">전체보기</a> <br>

<form action="insertPro.jsp" method="post" name="frm" id="frm">

<table>

    <tr>

        <td colspan="2">주소록 등록하기</td>

    </tr>

    <tr>

        <td>이름</td>

        <td><input type="text" name="name" id="name" size=15></td>

    </tr>

    <tr>

        <td>우편번호 </td>

        <td><input type="text" name="zipcode" id="zipcode" size=10>

        <input type="button" name="search" value="검색" onclick="zipfinder()"></td>

    <tr>

        <td>주소

        <td colspan="2"><input type="text" name="addr" id="addr" size=30>

        </td>

    </tr>

    </tr>

        <td>전화번호</td>

        <td colspan="2"><input type="text" name="tel" id="tel" size=15></td>

    <tr>

        <td colspan="2">

        <input type="submit" value="등록">

        <input type="reset" value="취소"></td>

    </tr>

</table>

</form>

</body>

</html>

insertPro.jsp: java class를 부르는 자바빈 액션태그들로 구성되어 있음. list.jsp로 가게함

<%@page import="com.address.AddressDAO"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<!-- 화면에 보여줄 내용이 없음 -->

<%

    request.setCharacterEncoding("utf-8"); //한글처리

%>

<jsp:useBean id="ad" class="com.address.Address"/>

<jsp:setProperty property="*" name="ad"/>

<%

    AddressDAO dao=AddressDAO.getInstance(); //생성된 객체를 반환하기 위해 getInstance()메소드 이용

    dao.addrInsert(ad);

    response.sendRedirect("list.jsp"); //화면의 내용을 받아넣고 list.jsp로 가라는 뜻

%>

deletePro.jsp: java class를 부르는 자바빈 액션태그들로 구성. 데이터를 삭제하기 위한 jsp. list.jsp로 가게함

<%@page import="com.address.AddressDAO"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<%

    request.setCharacterEncoding("utf-8");

%>

<jsp:useBean id="ad" class="com.address.Address"/>

<jsp:setProperty property="*" name="ad"/>

<%

    int num=Integer.parseInt(request.getParameter("num"));

    AddressDAO dao=AddressDAO.getInstance();

    dao.addrDelete(num);

    response.sendRedirect("list.jsp");

%>

zipCheck: 우편번호 검색하는 새창 jsp

<%@page import="com.address.ZipCodeBean"%>

<%@page import="java.util.ArrayList"%>

<%@page import="com.address.AddressDAO"%>

<%@ 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>

<style>

a:hover{

    text-decoration: none; color:#000;

}

a:link{

    text-decoration: none; color:#000;

}

a:visited{

    text-decoration: none; color:#000;

}

</style>

<script src="../js/jquery-3.5.1.min.js"></script>

<%

    request.setCharacterEncoding("utf-8");

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

    

    AddressDAO dao=AddressDAO.getInstance();

    ArrayList<ZipCodeBean>zarr=dao.zipcodeRead(dong);

%>

<script>

function dongCheck(){

    if($("#dong").val()==""){

        alert("동이름을 입력하세요");

        $("#dong").focus();

        return false;

    }

    $("#frm").submit();

}

function send(code,sido,gugun,dong,bunji){

    var address=sido+" "+gugun+" "+dong+" "+bunji;

    opener.document.frm.zipcode.value=code; //insert.jsp에 있는 form의 이름이 frm.그 frm의 zipcode의 값을 code로 바꾸겠다.

    opener.document.frm.addr.value=address;

    self.close();   

}

</script>

</head>

<body>

<form action="zipCheck.jsp" id="frm" name="frm">

<table>

    <tr>

        <td>동이름 입력: <input type="text" name="dong" id="dong">

        <input type="button" value="검색" onclick="dongCheck()">

        </td>

    </tr>

    <tr>

    <%

        if(zarr.isEmpty()){

    %>

            <tr>

             <td>검색된 결과가 없습니다.</td>

            </tr>

    <%  

        }else{

    %>

        <tr>

            <td>검색 후, 아래 우편번호를 클릭하면 자동으로 입력됩니다.</td>

        </tr>

    <%

        for(ZipCodeBean z:zarr){

            String zip =z.getZipcode();

            String sido =z.getSido();

            String bunji =z.getBunji();

            String gugun =z.getGugun();

            String d =z.getDong();

    %>

            <tr>    

                <td><a href="javascript:send('<%=zip%>','<%=sido%>','<%=gugun%>','<%=d%>','<%=bunji%>')">

                <!-- javascript:자바스크립트임을 알려줌. 문자열이라서 홑따옴표 -->

                <%=zip%><%=sido%><%=gugun%><%=d%><%=bunji%>

                </a></td>

            </tr>

    <%

            } //for

       }//else

    %>

    </tr>

</table>

</form>

</body>

</html>

ZipCodeBean.java : 도시, 구, 동, 우편번호가 저장되어있는 DB와 관련된 게터 세터, 생성자

package com.address;

public class ZipCodeBean {

    private String zipcode;

    private String sido;

    private String gugun;

    private String dong;

    private String bunji;

    private int seq;

    public String getZipcode() {

        return zipcode;

    }

    public void setZipcode(String zipcode) {

        this.zipcode = zipcode;

    }

    public String getSido() {

        return sido;

    }

    public void setSido(String sido) {

        this.sido = sido;

    }

    public String getGugun() {

        return gugun==null? "":gugun.trim();

    }

    public void setGugun(String gugun) {

        this.gugun = gugun;

    }

    public String getDong() {

        return dong;

    }

    public void setDong(String dong) {

        this.dong = dong;

    }

    public String getBunji() {

        return bunji==null? "":bunji.trim(); //null이면 공백 아니면 bunji값 (삼항연산자)

    }

    public void setBunji(String bunji) {

        this.bunji = bunji;

    }

    public int getSeq() {

        return seq;

    }

    public void setSeq(int seq) {

        this.seq = seq;

    }

}

저작자표시 비영리 변경금지

'Learning > JSP' 카테고리의 다른 글

JSON 형식의 데이터값 읽어오기 (0)	2020.07.15
ajax방식을 이용하여 id, pw 입력하고 하단부에 출력 (0)	2020.07.15
jQuery 이용 (0)	2020.07.10
JSP액션 태그, 객체와 getter setter이용 (0)	2020.07.09
객체와 메소드를 이용하여 JSP 성적 출력하기 (자바 파일: 생성자를 이용하여 값 할당) (0)	2020.07.09

cozy coding

자바스크립트, DB 이용하여 주소록 생성하기

'Learning > JSP' 카테고리의 다른 글

티스토리툴바

자바스크립트, DB 이용하여 주소록 생성하기

'Learning > JSP' 카테고리의 다른 글

'Learning/JSP' Related Articles

티스토리툴바