BACK END/Servlet
4일(08.10) Servlet_DB 예제
라미보
2022. 8. 16. 02:26
personupdate, servlet파일 넣고 프로덕트 파일넣기
ProductsBean.java
package products;
public class ProductsBean {
private int pnum;
private String pname;
private int pprice;
private String pdate;
public ProductsBean() {
super();
}
public ProductsBean(int pnum, String pname, int pprice, String pdate) {
super();
this.pnum = pnum;
this.pname = pname;
this.pprice = pprice;
this.pdate = pdate;
}
public int getPnum() {
return pnum;
}
public void setPnum(int pnum) {
this.pnum = pnum;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
public int getPprice() {
return pprice;
}
public void setPprice(int pprice) {
this.pprice = pprice;
}
public String getPdate() {
return pdate;
}
public void setPdate(String pdate) {
this.pdate = pdate;
}
}
ProductsDao.java
package products;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
public class ProductsDao {
String driver ="oracle.jdbc.driver.OracleDriver";
String url ="jdbc:oracle:thin:@localhost:1521:orcl";
String id ="jspid";
String pw="jsppw";
Connection conn =null;
public ProductsDao(){
try {
Class.forName(driver);
conn = DriverManager.getConnection(url,id,pw);
} catch (ClassNotFoundException e) {
System.out.println("드라이버로드 실패");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("계정접속 실패");
e.printStackTrace();
}
}//ProductsDao
public int insertData(ProductsBean pb){ //리턴형 void로 할거면 servlet에서 return받으려하면 안됨
System.out.println("insertData:"+pb.getPname());
System.out.println("insertData:"+pb.getPdate());
int result=-1;
PreparedStatement ps = null;
String sql ="insert into products values(seqprd.nextval,?,?,?)";
try {
ps = conn.prepareStatement(sql);
ps.setString(1,pb.getPname());
ps.setInt(2, pb.getPprice());
ps.setString(3,pb.getPdate());
result = ps.executeUpdate();
} catch (SQLException e) {
System.out.println("insertData 실패");
e.printStackTrace();
}finally {
try {
if(ps != null)
ps.close();
if(conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//System.out.println("insertData result:"+result);
return result;
}//insertData
public ArrayList<ProductsBean> selectData(){
ArrayList<ProductsBean> lists = new ArrayList<ProductsBean>();
PreparedStatement ps = null;
ResultSet rs = null;
String sql ="select * from products order by pnum";
try {
ps=conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
ProductsBean pb = new ProductsBean();
pb.setPnum(rs.getInt("pnum"));
pb.setPname(rs.getString("pname"));
pb.setPprice(rs.getInt("pprice"));
pb.setPdate(rs.getString("pdate"));
lists.add(pb);
}
} catch (SQLException e) {
System.out.println("selectData 실패");
e.printStackTrace();
}finally {
try {
if(ps != null)
ps.close();
if(conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
System.out.println("selecData list:"+lists);
return lists;
}//selectData
public int deleteDate(int pnum) {
int cnt =-1;
PreparedStatement ps = null;
String sql ="delete products where pnum=?";
try {
ps=conn.prepareStatement(sql);
ps.setInt(1, pnum);
cnt = ps.executeUpdate();
} catch (SQLException e) {
System.out.println("deleteDate 실패");
e.printStackTrace();
}finally {
try {
if(ps != null)
ps.close();
if(conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return cnt;
}//deleteDate
public ProductsBean selectPnum(int pnum) {
ProductsBean pb=null;
PreparedStatement ps =null;
ResultSet rs =null;
String sql="select * from products where pnum=?";
try {
ps=conn.prepareStatement(sql);
ps.setInt(1, pnum);
rs = ps.executeQuery();
if(rs.next()) {
pb= new ProductsBean();
pb.setPnum(rs.getInt("pnum"));
pb.setPname(rs.getString("pname"));
pb.setPprice(rs.getInt("pprice"));
pb.setPdate(rs.getString("pdate"));
}
} catch (SQLException e) {
System.out.println("selectPnum 실패");
e.printStackTrace();
}finally {
try {
if(ps!=null)
ps.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return pb;
}//selectPnum
public int updateDate(ProductsBean pb) {
int cnt =-1;
PreparedStatement ps =null;
String sql="update products set pname=?, pprice=?, pdate=? where pnum=?";
try {
ps = conn.prepareStatement(sql);
ps.setString(1, pb.getPname());
ps.setInt(2, pb.getPprice());
ps.setString(3, pb.getPdate());
ps.setInt(4, pb.getPnum());
cnt = ps.executeUpdate();
} catch (SQLException e) {
System.out.println("updateDate 실패");
e.printStackTrace();
}finally {
try {
if(ps != null)
ps.close();
if(conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return cnt;
}//updateDate
}
ProductsList.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
select.prd 요청 => ProductsList.jsp<br>
<!-- 삽입안거치고 리스트바로 보고싶으면 프로젝트명 눌러서 실행하고 select.prd로 이동한다. -->
상품목록 보기(상품수:${fn:length(lists) })<br>
<!-- lists=속성으로 설정한것. -->
<table border="1">
<tr bgcolor="pink">
<th>번호</th>
<th>상품명</th>
<th>가격</th>
<th>입고일</th>
<th>삭제</th>
<th>수정</th>
</tr>
<!-- lists는 requestScope.lists 로 받아지고 있음! -->
<c:forEach var="pb" items="${lists }">
<tr>
<td>${pb.pnum }</td>
<td>${pb.pname }</td>
<td>${pb['pprice'] }</td>
<td>${pb.pdate }</td>
<td><a href="delete.prd?pnum=${pb.pnum }">삭제</a></td>
<td><a href="updateForm.prd?pnum=${pb.pnum }">수정</a></td>
</tr>
</c:forEach>
</table>
<a href="productInputForm.jsp">삽입</a>