오라클에서는 어떤값을 자동증가 시키려면 시퀀스를 생성해야함
create sequence 시퀀스명..(필드명_seq)
insert into 테이블명 values (member_seq.nextval,
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="top.jsp" %>
<%@include file="dbcon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<script type="text/javascript" src="cs/info.js"></script>
<section>
<%!
int custno;
%>
<%
String sql="select max(custno)+1 from member_tbl_02";
psmt = con.prepareStatement(sql);
rs = psmt.executeQuery();
if(rs.next()) {
custno = rs.getInt(1);
}
%>
<h2>홈쇼핑 회원 등록</h2>
<form name="form1" action="inputsave.jsp" method="post">
<table border="1" width="400" align="center">
<tr><td>회원번호(자동생성)</td><td><input type="text" name="custno" value="<%=custno%>" readonly> </td></tr>
<tr><td>회원이름</td><td><input type="text" name="custname"></td></tr>
<tr><td>회원전화</td><td><input type="text" name="phone"></td></tr>
<tr><td>회원주소</td><td><input type="text" name="address"></td></tr>
<tr><td>가입일자</td><td><input type="date" name="joindate"></td></tr>
<tr><td>고객등급[A:VIP,B:일반,C:직원]</td><td><input type="text" name="grade" list="koko">
<datalist id="koko">
<option value="A"></option>
<option value="B"></option>
<option value="C"></option>
</datalist>
</td></tr>
<tr><td>거주도시</td><td><input type="text" name="city"></td></tr>
<tr><td colspan="2" align="center">
<input type="button" value="등록" onclick="return check()">
<a href="listmodify.jsp"><input type="button" value="조회"></a>
</td></tr>
</table>
</form>
</section>
<jsp:include page="footer.jsp"/>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="dbcon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%!
int custno;
String custname,phone,address,joindate,grade,city;
%>
<%
request.setCharacterEncoding("utf-8");
custno = Integer.parseInt(request.getParameter("custno"));
custname = request.getParameter("custname");
phone = request.getParameter("phone");
address = request.getParameter("address");
joindate = request.getParameter("joindate");
grade = request.getParameter("grade");
city = request.getParameter("city");
String sql = "insert into MEMBER_TBL_02 values (?,?,?,?,?,?,?)";
psmt = con.prepareStatement(sql);
psmt = con.prepareStatement(sql);
psmt.setInt(1, custno);
psmt.setString(2, custname);
psmt.setString(3, phone);
psmt.setString(4, address);
psmt.setString(5, joindate);
psmt.setString(6, grade);
psmt.setString(7, city);
psmt.executeUpdate();
response.sendRedirect("index.jsp");
%>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="top.jsp" %>
<%@include file="dbcon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
<%!
int custno;
String custname,phone,address,joindate,grade,city;
%>
<%
String sql = "select * from MEMBER_TBL_02";
psmt = con.prepareStatement(sql);
rs = psmt.executeQuery();
%>
<h2>회원목록조회/수정</h2>
<table border="1" width="1000px" align="center">
<tr>
<th>회원번호</th><th>회원성명</th><th>연락처</th><th>주소</th>
<th>가입일자</th><th>고객등급</th><th>도시코드</th>
</tr>
<%
while(rs.next()){
int custno = rs.getInt(1);
String custname = rs.getString(2);
String phone = rs.getString(3);
String address = rs.getString(4);
String joindate = rs.getString(5);
joindate = joindate.substring(0, 10);
String grade = rs.getString(6);
if(grade.equals("A")) {
grade = "VIP";
}
else if(grade.equals("B")) {
grade = "일반";
}
else {
grade = "직원";
}
String city = rs.getString(7);
%>
<tr>
<td><a href="modify1.jsp?custno=<%=custno%>"><%=custno%></a></td>
<td><%=custname%></td><td><%=phone%></td>
<td><%=address%></td><td><%=joindate%></td><td><%=grade%></td>
<td><%=city%></td>
</tr>
<%
}
%>
</table>
</section>
<jsp:include page="footer.jsp"/>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="top.jsp" %>
<%@include file="dbcon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
<%!
int custno;
String custname,phone,address,joindate,grade,city;
%>
<%
String sql = "select m.custno,m.custname,m.grade,sum(o.price) "
+ "from MEMBER_TBL_02 m, money_tbl_02 o "
+ "where m.custno=o.custno group by m.custno,m.custname,m.grade order by sum(o.price) desc";
psmt = con.prepareStatement(sql);
rs = psmt.executeQuery();
%>
<h2>회원매출조회</h2>
<table border="1" width="1000px" align="center">
<tr>
<th>회원번호</th><th>회원성명</th><th>고객등급</th><th>매출</th>
</tr>
<%
while(rs.next()){
int custno = rs.getInt(1);
String custname = rs.getString(2);
String grade = rs.getString(3);
if(grade.equals("A")) {
grade = "VIP";
}
else if(grade.equals("B")) {
grade = "일반";
}
else {
grade = "직원";
}
int price = rs.getInt(4);
%>
<tr>
<td><%=custno%></td><td><%=custname%></td><td><%=grade%></td>
<td><%=price%></td>
</tr>
<%
}
%>
</table>
</section>
<jsp:include page="footer.jsp"/>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="top.jsp" %>
<%@include file="dbcon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
<%!
int custno;
String custname,phone,address,joindate,grade,city;
%>
<%
request.setCharacterEncoding("utf-8");
custno = Integer.parseInt(request.getParameter("custno"));
String sql = "select * from MEMBER_TBL_02 where custno=?";
psmt = con.prepareStatement(sql);
psmt.setInt(1, custno);
rs = psmt.executeQuery();
if(rs.next()) {
int custno = rs.getInt(1);
String custname = rs.getString(2);
String phone = rs.getString(3);
String address = rs.getString(4);
String joindate = rs.getString(5);
String grade = rs.getString(6);
String city = rs.getString(7);
%>
<h2>홈쇼핑 회원 정보 수정</h2>
<form action="modify2.jsp" method="post">
<table border="1" width="600px" align="center">
<tr>
<th>회원번호</th>
<td><input type="text" name="custno" value="<%=custno%>" readonly></td>
</tr>
<tr>
<th>회원성명</th>
<td><input type="text" name="custname" value="<%=custname%>"></td>
</tr>
<tr>
<th>회원전화</th>
<td><input type="text" name="phone" value="<%=phone%>"></td>
</tr>
<tr>
<th>회원주소</th>
<td><input type="text" name="address" value="<%=address%>"></td>
</tr>
<tr>
<th>가입일자</th>
<td><input type="date" name="joindate" value="<%=joindate%>"></td>
</tr>
<tr>
<th>고객등급(A:VIP,B:일반,C:직원)</th>
<td><input type="text" name="grade" value="<%=grade%>"></td>
</tr>
<tr>
<th>도시코드</th>
<td><input type="text" name="city" value="<%=city%>"></td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="수정">
<input type="reset" value="취소">
</td>
</tr>
<%
}
%>
</table>
</form>
</section>
<jsp:include page="footer.jsp"/>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="top.jsp" %>
<%@include file="dbcon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
<%!
int custno;
String custname,phone,address,joindate,grade,city;
%>
<%
request.setCharacterEncoding("utf-8");
custno = Integer.parseInt(request.getParameter("custno"));
custname = request.getParameter("custname");
phone = request.getParameter("phone");
address = request.getParameter("address");
joindate = request.getParameter("joindate");
grade = request.getParameter("grade");
city = request.getParameter("city");
String sql = "update MEMBER_TBL_02 set custname=?,phone=?,address=?,"
+ "joindate=?,grade=?,city=? where custno=?";
psmt = con.prepareStatement(sql);
psmt.setString(1, custname);
psmt.setString(2, phone);
psmt.setString(3, address);
psmt.setString(4, joindate);
psmt.setString(5, grade);
psmt.setString(6, city);
psmt.setInt(7, custno);
psmt.executeUpdate();
response.sendRedirect("index.jsp");
%>
</section>
<jsp:include page="footer.jsp"/>
</html>
댓글