dao/ItemDAO.java > MODEL2

package dao;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.ArrayList;

import model.Category;

import model.Item;

public class ItemDAO {

Statement stmt;

PreparedStatement pstmt;

ResultSet rs;

DAO d = new DAO();

//총 수

public int getAllcount(String field, String search) {

d.getCon();

int count = 0;

try {

String sql = "";

if(search != null && !search.equals("")) {

sql = "select count(*) from item where "+field+" like '%"+search+"%'";

}else {

sql = "select count(*) from item";

}

stmt = d.conn.createStatement();

rs = stmt.executeQuery(sql);

if(rs.next()) {

count = rs.getInt(1);

}

}catch(Exception e) {

e.printStackTrace();

}finally {

try {

stmt.close();

d.conn.close();

}catch(Exception e) {

e.printStackTrace();

}

}

return count;

}

//목록

public ArrayList<Item> getAllMember(int startRow, int endRow, String field, String search) {

d.getCon();

ArrayList<Item> v = new ArrayList<Item>();

try {

String sql = "select * from item order by it_uid desc limit ?,?";

if(search != null && !search.equals("")) {

sql = "select * from item where "+field+" like '%"+search+"%' order by it_uid asc limit ?,?";

}

pstmt = d.conn.prepareStatement(sql);

pstmt.setInt(1, startRow);

pstmt.setInt(2, endRow);

rs = pstmt.executeQuery();

while(rs.next()) {

Item i = new Item();

i.setIt_uid(rs.getInt("it_uid"));

i.setIt_name(rs.getString("it_name"));

i.setCategory1(rs.getString("category1"));

i.setCategory2(rs.getString("category2"));

i.setMb_id(rs.getString("mb_id"));

i.setIt_sale(rs.getInt("it_sale"));

i.setIt_price(rs.getInt("it_price"));

i.setIt_use(rs.getNString("it_use"));

i.setFile1(rs.getString("file1"));

i.setFile1_thumb(rs.getString("file1_thumb"));

v.add(i);

}

}catch(Exception e) {

e.printStackTrace();

}finally {

try {

rs.close();

pstmt.close();

d.conn.close();

}catch(Exception e) {

e.printStackTrace();

}

}

return v;

}

//카테고리 대분류

public ArrayList<Category> getTotal(){

d.getCon();

ArrayList<Category> v = new ArrayList<Category>();

try {

String sql = "select * from category where length(ca_id)=2 and ca_use='Y'";

pstmt = d.conn.prepareStatement(sql);

rs = pstmt.executeQuery();

while(rs.next()) {

Category c = new Category();

c.setCa_id(rs.getString("ca_id"));

c.setCa_name(rs.getString("ca_name"));

v.add(c);

}

} catch (Exception e) {

// TODO: handle exception

}finally {

try {

rs.close();

pstmt.close();

d.conn.close();

}catch(Exception e) {

e.printStackTrace();

}

}

return v;

}

//카테고리 중분류

public ArrayList<Category> getCategory2(String ca_id){

d.getCon();

ArrayList<Category> v = new ArrayList<Category>();

try {

String sql = "select * from category where left(ca_id,2)=? and length(ca_id)=4 and ca_use='Y'";

pstmt = d.conn.prepareStatement(sql);

pstmt.setString(1, ca_id);

rs = pstmt.executeQuery();

while(rs.next()) {

Category c = new Category();

c.setCa_id(rs.getString("ca_id"));

c.setCa_name(rs.getString("ca_name"));

v.add(c);

}

} catch (Exception e) {

e.printStackTrace();

}finally {

try {

rs.close();

pstmt.close();

d.conn.close();

}catch(Exception e) {

e.printStackTrace();

}

}

return v;

}

//추가

public void insertItem(Item i) {

d.getCon();

try {

String sql = "insert into item values (null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

pstmt = d.conn.prepareStatement(sql);

pstmt.setString(1, i.getCategory1());

pstmt.setString(2, i.getCategory2());

pstmt.setString(3, i.getMb_id());

pstmt.setString(4, i.getIt_name());

pstmt.setInt(5, i.getIt_sale());

pstmt.setInt(6, i.getIt_price());

pstmt.setInt(7, i.getIt_point());

pstmt.setInt(8, i.getIt_qty());

pstmt.setString(9, i.getIt_option());

pstmt.setString(10, i.getIt_use());

pstmt.setString(11, i.getIt_date());

pstmt.setString(12, i.getIt_type1());

pstmt.setString(13, i.getIt_type2());

pstmt.setString(14, i.getIt_type3());

pstmt.setString(15, i.getIt_type4());

pstmt.setString(16, i.getIt_type5());

pstmt.setString(17, i.getFile1());

pstmt.setString(18, i.getFile2());

pstmt.setString(19, i.getFile3());

pstmt.setString(20, i.getFile4());

pstmt.setString(21, i.getFile5());

pstmt.setString(22, i.getFile1_thumb()); //썸네일

pstmt.executeUpdate();

}catch(Exception e) {

e.printStackTrace();

}finally {

try {

pstmt.close();

d.conn.close();

}catch(Exception e) {

e.printStackTrace();

}

}

}

//상품 상세

public Item oneItem(int it_uid) {

d.getCon();

Item item = new Item();

try {

String sql = "select * from item where it_uid=?";

pstmt = d.conn.prepareStatement(sql);

pstmt.setInt(1, it_uid);

rs = pstmt.executeQuery();

if(rs.next()) {

item.setIt_uid(rs.getInt("it_uid"));

item.setCategory1(rs.getString("category1"));

item.setCategory2(rs.getString("category2"));

item.setMb_id(rs.getString("mb_id"));

item.setIt_name(rs.getString("it_name"));

item.setIt_sale(rs.getInt("it_sale"));

item.setIt_price(rs.getInt("it_price"));

item.setIt_point(rs.getInt("it_point"));

item.setIt_qty(rs.getInt("it_qty"));

item.setIt_option(rs.getString("it_option"));

item.setIt_use(rs.getString("it_use"));

item.setIt_date(rs.getString("it_date"));

item.setIt_type1(rs.getString("it_type1"));

item.setIt_type2(rs.getString("it_type2"));

item.setIt_type3(rs.getString("it_type3"));

item.setIt_type4(rs.getString("it_type4"));

item.setIt_type5(rs.getString("it_type5"));

item.setFile1(rs.getString("file1"));

item.setFile2(rs.getString("file2"));

item.setFile3(rs.getString("file3"));

item.setFile4(rs.getString("file4"));

item.setFile5(rs.getString("file5"));

item.setFile1_thumb(rs.getString("file1_thumb"));

}

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

pstmt.close();

d.conn.close();

}catch(Exception e) {

e.printStackTrace();

}

}

return item;

}

//상품 수정

public void updateItem(Item i) {

d.getCon();

try {

String file1,file2,file3,file4,file5,file1_thumb;

if(!i.getFile1().equals("")) { file1 = ",file1='"+i.getFile1()+"'"; }else { file1 = ""; }

if(!i.getFile2().equals("")) { file2 = ",file2='"+i.getFile2()+"'"; }else { file2 = ""; }

if(!i.getFile3().equals("")) { file3 = ",file3='"+i.getFile3()+"'"; }else { file3 = ""; }

if(!i.getFile4().equals("")) { file4 = ",file4='"+i.getFile4()+"'"; }else { file4 = ""; }

if(!i.getFile5().equals("")) { file5 = ",file5='"+i.getFile5()+"'"; }else { file5 = ""; }

if(!i.getFile1_thumb().equals("")) { file1_thumb = ",file1_thumb='"+i.getFile1_thumb()+"'"; }else { file1_thumb = ""; }

String sql = "update item set category1=?,category2=?,mb_id=?,it_name=?,it_sale=?,it_price=?";

sql += ",it_point=?,it_qty=?,it_option=?,it_use=?,it_date=?,it_type1=?,it_type2=?,it_type3=?,it_type4=?,it_type5=?";

sql += file1+file2+file3+file4+file5+file1_thumb+" where it_uid=?";

pstmt = d.conn.prepareStatement(sql);

pstmt.setString(1, i.getCategory1());

pstmt.setString(2, i.getCategory2());

pstmt.setString(3, i.getMb_id());

pstmt.setString(4, i.getIt_name());

pstmt.setInt(5, i.getIt_sale());

pstmt.setInt(6, i.getIt_price());

pstmt.setInt(7, i.getIt_point());

pstmt.setInt(8, i.getIt_qty());

pstmt.setString(9, i.getIt_option());

pstmt.setString(10, i.getIt_use());

pstmt.setString(11, i.getIt_date());

pstmt.setString(12, i.getIt_type1());

pstmt.setString(13, i.getIt_type2());

pstmt.setString(14, i.getIt_type3());

pstmt.setString(15, i.getIt_type4());

pstmt.setString(16, i.getIt_type5());

//첨부파일

pstmt.setInt(17, i.getIt_uid());

pstmt.executeUpdate();

}catch(Exception e) {

e.printStackTrace();

}finally {

try {

pstmt.close();

d.conn.close();

}catch(Exception e) {

e.printStackTrace();

}

}

}

//상품 삭제

public void deleteItem(int it_uid) {

d.getCon();

try {

String sql = "delete from item where it_uid=?";

pstmt = d.conn.prepareStatement(sql);

pstmt.setInt(1, it_uid);

pstmt.executeUpdate();

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

pstmt.close();

d.conn.close();

}catch(Exception e) {

e.printStackTrace();

}

}

}

}