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

e.printStackTrace();

}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();

}

}

}

}