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