JSP - eclipse/oracle

최성현·2023년 8월 7일
@NumberFormatSimpleDateFormatdetailPagerequest.setCharacterEncoding("utf-8");select valueselection
0

JSP

목록 보기
8/13

CRUD - eclipse/oracle 연동 (복습,mymall)

추가학습

selection 선택시 미리보기(list)

<img src="" id="selphoto">

<script>
	$("#selphoto").attr("src", $("#photo").val());
	
	$("#photo").change(function(){
		var img=$(this).val();
			
		$("#selphoto").attr("src",img);
	})
</script>

NumberFormat nf= NumberFormat.getCurrencyInstance();

통화단위 표시

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm");

보이는 시간대 설정가능

request.setCharacterEncoding("utf-8");

tomcat server 9.0에서 한글깨짐 방지
action페이지에만 들어가야함

select value(updateForm)

select의 value값은 삼항연산자로 확인해주면 좋다

<tr>
	<th>상품사진</th>
	<td>
		<select name="photo" id="photo">
			<option value="../image/1.jpg"
			<%=dto.getPhoto().equals("../image/1.jpg")?"selected":""%>>머플러</option>
			<option value="../image/3.jpg"
			<%=dto.getPhoto().equals("../image/3.jpg")?"selected":""%>>모자</option>
			<option value="../image/11.jpg"
			<%=dto.getPhoto().equals("../image/11.jpg")?"selected":""%>>로퍼</option>
			<option value="../image/20.jpg"
			<%=dto.getPhoto().equals("../image/20.jpg")?"selected":""%>>시계</option>
			<option value="../image/31.jpg"
			<%=dto.getPhoto().equals("../image/31.jpg")?"selected":""%>>자켓</option>
		</select>
	</td>
</tr>

detailpage

특정 문구를 누르면 상세 정보창으로 이동해 해당 제품의 상세정보가 적혀있도록 폼을 짜 구성한다

update와 마찬가지로 시퀀스값을 가져와 해당 행의 정보를 모두 가져온다

<%
	String no=request.getParameter("no");
	MallDao dao=new MallDao();
	MallDto dto=dao.getData(no);
%>

sql문

--mymall
create table mymall(no number(3) primary key,
sangpum varchar2(30),
photo varchar2(50),
price number(5),
ipgoday varchar2(20),
writeday date);

select * from mymall;

DBConnect

package oracle.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBConnect {

static final String ORACLE_URL="jdbc:oracle:thin:@localhost:1521:XE";
	
	//driver
	String driver="oracle.jdbc.driver.OracleDriver";
	
	public DBConnect() {
		try {
			Class.forName(driver);
			System.out.println("오라클 드라이버 성공!!!");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("오라클 드라이버 실패!!!");
		}
	}
	
	//Connection
	public Connection getConnection()
	{
		
		Connection conn=null;
		
		try {
			conn=DriverManager.getConnection(ORACLE_URL, "tjdgus", "1234");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("오라클 연결 실패: url,계정,비밀번호 확인 요함! "+e.getMessage());
		}
		return conn;
	}
	
	//close메서드..총 4개
	//완성구문 2개
	public void dbClose(ResultSet rs,Statement stmt,Connection conn)
	{
		try {
			if(rs!=null) rs.close();
			if(stmt!=null) stmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void dbClose(Statement stmt,Connection conn)
	{
		try {
			if(stmt!=null) stmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//미완구문 2개 //preparedstatement 나중에 정의해줌
	public void dbClose(ResultSet rs,PreparedStatement pstmt,Connection conn)
	{
		try {
			if(rs!=null) rs.close();
			if(pstmt!=null) pstmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void dbClose(PreparedStatement pstmt,Connection conn)
	{
		try {
			if(pstmt!=null) pstmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

MallDto.java

package model.mymall;

import java.sql.Timestamp;

public class MallDto {
	
	private String no;
	private String sangpum;
	private String photo;
	private String price;
	private String ipgoday;
	private Timestamp writeday;
	
	
	public String getNo() {
		return no;
	}
	public void setNo(String no) {
		this.no = no;
	}
	public String getSangpum() {
		return sangpum;
	}
	public void setSangpum(String sangpum) {
		this.sangpum = sangpum;
	}
	public String getPhoto() {
		return photo;
	}
	public void setPhoto(String photo) {
		this.photo = photo;
	}
	public String getPrice() {
		return price;
	}
	public void setPrice(String price) {
		this.price = price;
	}
	public String getIpgoday() {
		return ipgoday;
	}
	public void setIpgoday(String ipgoday) {
		this.ipgoday = ipgoday;
	}
	public Timestamp getWriteday() {
		return writeday;
	}
	public void setWriteday(Timestamp writeday) {
		this.writeday = writeday;
	}
	
	
}

MallDao.java

package model.mymall;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import model.myteam.TeamDto;
import oracle.db.DBConnect;

public class MallDao {
	
	DBConnect db=new DBConnect();
	
	public void insertMall(MallDto dto)
	{
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		
		String sql="insert into mymall values(seq_1.nextval,?,?,?,?,sysdate)";
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, dto.getSangpum());
			pstmt.setString(2, dto.getPhoto());
			pstmt.setString(3, dto.getPrice());
			pstmt.setString(4, dto.getIpgoday());
			
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.dbClose(pstmt, conn);
		}
	}
	
	public Vector<MallDto> getAllMyMalls()
	{
		Vector<MallDto> list=new Vector<MallDto>();
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		String sql="select * from mymall order by no";
		
		try {
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			while(rs.next())
			{
				MallDto dto=new MallDto();
				
				dto.setNo(rs.getString("no"));
				dto.setSangpum(rs.getString("sangpum"));
				dto.setPhoto(rs.getString("photo"));
				dto.setPrice(rs.getString("price"));
				dto.setIpgoday(rs.getString("ipgoday"));
				dto.setWriteday(rs.getTimestamp("writeday"));
				
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	public void deleteMyMall(String no)
	{
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		
		String sql="delete from mymall where no=?";
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, no);
			
			pstmt.execute();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.dbClose(pstmt, conn);
		}
	}
	
	public MallDto getData(String no)
	{
		MallDto dto=new MallDto();
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		String sql="select * from mymall where no=?";
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, no);
			rs=pstmt.executeQuery();
			
			if(rs.next())
			{
				dto.setNo(rs.getString("no"));
				dto.setSangpum(rs.getString("sangpum"));
				dto.setPhoto(rs.getString("photo"));
				dto.setPrice(rs.getString("price"));
				dto.setIpgoday(rs.getString("ipgoday"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, pstmt, conn);
		}
		
		return dto;
	}
	
	public void updateMall(MallDto dto)
	{
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		
		String sql="update mymall set sangpum=?,photo=?,price=?,ipgoday=? where no=?";
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, dto.getSangpum());
			pstmt.setString(2, dto.getPhoto());
			pstmt.setString(3, dto.getPrice());
			pstmt.setString(4, dto.getIpgoday());
			pstmt.setString(5, dto.getNo());
			
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(pstmt, conn);
		}
	}
}

addForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Cute+Font&family=Diphylleia&family=Dokdo&family=Nanum+Brush+Script&family=Nanum+Gothic+Coding&family=Noto+Sans+KR&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<title>Insert title here</title>
</head>
<body>
	<form action="addAction.jsp" method="post">
		<table class="table table-bordered" style="width: 400px;">
			<tr>
				<th>상품명</th>
				<td>
					<input type="text" name="name" class="form-control"
					required="required" style="width: 120px;" placeholder="상품명">
				</td>
			</tr>
			
			<tr>
				<th>상품사진</th>
				<td>
					<select name="photo" id="photo">
						<option value="../image/1.jpg">머플러</option>
						<option value="../image/3.jpg">모자</option>
						<option value="../image/11.jpg">로퍼</option>
						<option value="../image/20.jpg">시계</option>
						<option value="../image/31.jpg">자켓</option>
					</select>
				</td>
			</tr>
			
			<tr>
				<th>가격</th>
				<td>
					<input type="text" name="price" class="form=control"
					required="required" style="width: 120px;" placeholder="가격">
				</td>
			</tr>
			
			<tr>
				<th>입고일</th>
				<td>
					<input type="date" name="ipgoday" class="form=control"
					required="required" style="width: 120px;" placeholder="입고일">
				</td>
			</tr>
			
			<tr>
				<td colspan="2" align="center">
					<button type="submit" class="btn btn-info">전송</button>
					
					<button type="button" class="btn btn-success"
					onclick="location.href='list.jsp'">목록</button>
				</td>
			</tr>
		</table>
	</form>
	
	<%-- selection 선택했을때 옆에 미리 보기 보이기 --%>
	<img src="" id="selphoto">
	
	<script>
		$("#selphoto").attr("src", $("#photo").val());
	
		$("#photo").change(function(){
			var img=$(this).val();
			
			$("#selphoto").attr("src",img);
		})
		
	</script>
</body>
</html>

list.jsp

<%@page import="java.text.SimpleDateFormat"%>
<%@page import="java.text.NumberFormat"%>
<%@page import="model.mymall.MallDto"%>
<%@page import="java.util.Vector"%>
<%@page import="model.mymall.MallDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Cute+Font&family=Diphylleia&family=Dokdo&family=Nanum+Brush+Script&family=Nanum+Gothic+Coding&family=Noto+Sans+KR&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<title>Insert title here</title>
</head>
<%
	MallDao dao=new MallDao();
	Vector<MallDto> list=dao.getAllMyMalls();
%>
<body>
<button type="button" class="btn btn-info"
onclick="location.href='addForm.jsp'">데이터 추가</button>
<br><br>
<table class="table table-bordered" style="width: 800px;">
	<tr class="table-warning" style="text-align: center;">
		<th width="60">번호</th>
		<th width="120">상품명</th>
		<th width="150">상품사진</th>
		<th width="120">가격</th>
		<th width="250">입고일</th>
		<th width="250">작성일</th>
		<th	width="200">수정 삭제</th>
	</tr>
	
	<%
	
	if(list.size()==0){
		%>
		<tr>
			<td colspan="7" align="center">
				<h3>데이터가 없습니다</h3>
			</td>
		</tr>
	<%}
	
	else
	{
		NumberFormat nf= NumberFormat.getCurrencyInstance();
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm");
		
		for(int i=0;i<list.size();i++)
		{
			MallDto dto=list.get(i);
			%>
			<tr align="center" valign="middle">
				<td align="center" valign="middle"><%=i+1 %></td>
				<td align="center" valign="middle"><%=dto.getSangpum() %></td>
				<td align="center" valign="middle" id="photo"
				onclick="location.href='detailPage.jsp?no=<%=dto.getNo()%>'"><img src='<%=dto.getPhoto() %>' width="60" height="60"></td>
				<td align="center" valign="middle"><%=dto.getPrice() %>원</td>
				<td align="center" valign="middle"><%=dto.getIpgoday() %></td>
				<td align="center" valign="middle"><%=dto.getWriteday() %></td>
				<td>
				<button type="button" class="btn btn-warning btn-sm"
				onclick="location.href='updateForm.jsp?no=<%=dto.getNo()%>'">수정</button>
				<button type="button" class="btn btn-danger btn-sm"
				onclick="confirm('삭제하시겠습니까?')?location.href='delete.jsp?no=<%=dto.getNo()%>'
				:alert('취소되었습니다')">삭제</button>
				</td>
			</tr>
		<%}
	}
	%>
</table>
</body>
</html>

addAction.jsp

<%@page import="model.mymall.MallDao"%>
<%@page import="model.mymall.MallDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Cute+Font&family=Diphylleia&family=Dokdo&family=Nanum+Brush+Script&family=Nanum+Gothic+Coding&family=Noto+Sans+KR&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<title>Insert title here</title>
</head>
<body>
<%
	//한글엔코딩
	//insertAction에만 추가 하면됨
	request.setCharacterEncoding("utf-8");


	
	String name=request.getParameter("name");
	String photo=request.getParameter("photo");
	String price=request.getParameter("price");
	String ipgoday=request.getParameter("ipgoday");
	
	MallDto dto=new MallDto();
	dto.setSangpum(name);
	dto.setPhoto(photo);
	dto.setPrice(price);
	dto.setIpgoday(ipgoday);
	
	MallDao dao=new MallDao();
	dao.insertMall(dto);
	
	response.sendRedirect("list.jsp");
%>
</body>
</html>

deletePage.jsp

<%@page import="model.mymall.MallDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Cute+Font&family=Diphylleia&family=Dokdo&family=Nanum+Brush+Script&family=Nanum+Gothic+Coding&family=Noto+Sans+KR&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<title>Insert title here</title>
</head>
<body>
<%
	String no=request.getParameter("no");
	
	MallDao dao=new MallDao();
	dao.deleteMyMall(no);
	
	response.sendRedirect("list.jsp");
%>
</body>
</html>

updateForm.jsp

<%@page import="model.mymall.MallDto"%>
<%@page import="model.mymall.MallDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Cute+Font&family=Diphylleia&family=Dokdo&family=Nanum+Brush+Script&family=Nanum+Gothic+Coding&family=Noto+Sans+KR&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<title>Insert title here</title>
</head>
<%
	String no=request.getParameter("no");
	MallDao dao=new MallDao();
	MallDto dto=dao.getData(no);
%>
<body>
	<form action="updateAction.jsp" method="post">
	<input type="hidden" name="no" value="<%=no%>">
		<table class="table table-bordered" style="width: 400px;">
			<tr>
				<th>상품명</th>
				<td>
					<input type="text" name="name" class="form=control"
					required="required" style="width: 120px;" placeholder="상품명"
					value="<%=dto.getSangpum()%>">
				</td>
			</tr>
			
			<tr>
				<th>상품사진</th>
				<td>
					<select name="photo" id="photo">
						<option value="../image/1.jpg"
						<%=dto.getPhoto().equals("../image/1.jpg")?"selected":""%>>머플러</option>
						<option value="../image/3.jpg"
						<%=dto.getPhoto().equals("../image/3.jpg")?"selected":""%>>모자</option>
						<option value="../image/11.jpg"
						<%=dto.getPhoto().equals("../image/11.jpg")?"selected":""%>>로퍼</option>
						<option value="../image/20.jpg"
						<%=dto.getPhoto().equals("../image/20.jpg")?"selected":""%>>시계</option>
						<option value="../image/31.jpg"
						<%=dto.getPhoto().equals("../image/31.jpg")?"selected":""%>>자켓</option>
					</select>
				</td>
			</tr>
			
			<tr>
				<th>가격</th>
				<td>
					<input type="text" name="price" class="form=control"
					required="required" style="width: 120px;" placeholder="가격"
					value="<%=dto.getPrice()%>">
				</td>
			</tr>
			
			<tr>
				<th>입고일</th>
				<td>
					<input type="date" name="ipgoday" class="form=control"
					required="required" style="width: 120px;" placeholder="입고일"
					value=<%=dto.getIpgoday() %>>
				</td>
			</tr>
			
			<tr>
				<td colspan="2" align="center">
					<button type="submit" class="btn btn-warning">상품수정</button>
					
					<button type="button" class="btn btn-success"
					onclick="location.href='list.jsp'">목록</button>
				</td>
			</tr>
		</table>
	</form>
	
	<img src="" id="selphoto">
	
	<script>
		$("#selphoto").attr("src", $("#photo").val());
	
		$("#photo").change(function(){
			var img=$(this).val();
			
			$("#selphoto").attr("src",img);
		})
		
	</script>
</body>
</html>

updateAction.jsp

<%@page import="model.mymall.MallDao"%>
<%@page import="model.mymall.MallDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Cute+Font&family=Diphylleia&family=Dokdo&family=Nanum+Brush+Script&family=Nanum+Gothic+Coding&family=Noto+Sans+KR&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<title>Insert title here</title>
</head>
<body>
<%
	String no=request.getParameter("no");
	String sangpum=request.getParameter("name");
	String photo=request.getParameter("photo");
	String price=request.getParameter("price");
	String ipgoday=request.getParameter("ipgoday");
	
	MallDto dto=new MallDto();
	MallDao dao=new MallDao();
	
	dto.setNo(no);
	dto.setSangpum(sangpum);
	dto.setPhoto(photo);
	dto.setPrice(price);
	dto.setIpgoday(ipgoday);
	
	dao.updateMall(dto);
	
	response.sendRedirect("list.jsp");
%>
</body>
</html>

detailpage.jsp

<%@page import="model.mymall.MallDao"%>
<%@page import="model.mymall.MallDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Cute+Font&family=Diphylleia&family=Dokdo&family=Nanum+Brush+Script&family=Nanum+Gothic+Coding&family=Noto+Sans+KR&display=swap" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<title>Insert title here</title>
<style>
 	#box{
 		position: absolute;
 		top: 200px;
 		left: 200px;
		width: 500px;
		height: 600px;
	}
	
	#box>img{
		border: 2px groove brown;
	}
	
	#box2{
		margin-top: 200px;
		margin-left: 500px;
	}
	
	#btn{
		position: absolute;
		top: 450px;
		left: 500px;
	}
	
	
 </style>
</head>
<body>
<%
	String no=request.getParameter("no");
	MallDao dao=new MallDao();
	MallDto dto=dao.getData(no);
%>
<div id="box"><img src='<%=dto.getPhoto() %>'></div>

<div id="box2">
	<h1>상품명: <%=dto.getSangpum() %></h1><br>
	<h1>가격: <%=dto.getPrice() %>원</h1><br>
	<h1>입고일: <%=dto.getIpgoday() %></h1>
	<br><br><br><br><br>
</div>

	<button type="button" id="btn" class="btn btn-success" style="width: 100px; height: 50px;"
	onclick="location.href='list.jsp'">목록</button>

</body>
</html>
profile
최성현
백엔드 개발자로서 성장해 나가는 성현이의 블로그~
이전 포스트

JSP - eclipse/oracle

다음 포스트

JSP - eclipse/oracle

0개의 댓글