001 /** 002 * 003 */ 004 package org.apache.geronimo.samples.inventory.dao; 005 006 import java.sql.Connection; 007 import java.sql.PreparedStatement; 008 import java.sql.ResultSet; 009 import java.sql.SQLException; 010 import java.util.ArrayList; 011 import java.util.List; 012 013 import org.apache.geronimo.samples.inventory.Item; 014 import org.apache.geronimo.samples.inventory.util.DBManager; 015 016 017 public class ItemDAO { 018 019 public List getItems(){ 020 ArrayList items = new ArrayList(); 021 022 Connection con = DBManager.getConnection(); 023 try { 024 025 PreparedStatement pStmt = con.prepareStatement("SELECT i.item_id as item_id, i.item_name as item_name, i.description as description, im.quantity as quantity FROM item i, item_master im WHERE im.item_id = i.item_id"); 026 ResultSet rs = pStmt.executeQuery(); 027 int j = 0; 028 while(rs.next()){ 029 ArrayList item = new ArrayList(); 030 031 String itemId = rs.getString("item_id"); 032 String itemName = rs.getString("item_name"); 033 String description = rs.getString("description"); 034 int quantity = rs.getInt("quantity"); 035 036 item.add(0, itemId); 037 item.add(1, itemName); 038 item.add(2, description); 039 item.add(3, quantity+""); 040 041 items.add(j,item); 042 j++; 043 044 } 045 } catch (SQLException e) { 046 e.printStackTrace(); 047 }finally{ 048 if(con != null){ 049 try { 050 con.close(); 051 } catch (SQLException e) { 052 e.printStackTrace(); 053 } 054 } 055 } 056 057 return items; 058 } 059 060 public int getQOH(String itemNo){ 061 062 int qoh = 0; 063 Connection con = DBManager.getConnection(); 064 try { 065 066 PreparedStatement pStmt = con.prepareStatement("SELECT quantity FROM item_master WHERE item_id =? "); 067 pStmt.setString(1, itemNo); 068 069 ResultSet rs = pStmt.executeQuery(); 070 while(rs.next()){ 071 qoh = rs.getInt("quantity"); 072 } 073 } catch (SQLException e) { 074 e.printStackTrace(); 075 }finally{ 076 if(con != null){ 077 try { 078 con.close(); 079 } catch (SQLException e) { 080 e.printStackTrace(); 081 } 082 } 083 } 084 return qoh; 085 } 086 087 public boolean isItemIdExists(String itemNo){ 088 boolean isExists = false; 089 Connection con = DBManager.getConnection(); 090 try { 091 092 PreparedStatement pStmt = con.prepareStatement("SELECT item_id FROM item WHERE item_id =? "); 093 pStmt.setString(1, itemNo); 094 095 ResultSet rs = pStmt.executeQuery(); 096 if(rs.next()){ 097 isExists = true; 098 } 099 } catch (SQLException e) { 100 e.printStackTrace(); 101 }finally{ 102 if(con != null){ 103 try { 104 con.close(); 105 } catch (SQLException e) { 106 e.printStackTrace(); 107 } 108 } 109 } 110 return isExists; 111 } 112 113 public void updateQOH(String itemNo, int qoh){ 114 Connection con = DBManager.getConnection(); 115 try { 116 117 PreparedStatement pStmt = con.prepareStatement("UPDATE item_master SET quantity=? WHERE item_id=?"); 118 pStmt.setInt(1, qoh); 119 pStmt.setString(2, itemNo); 120 121 pStmt.executeUpdate(); 122 } catch (SQLException e) { 123 e.printStackTrace(); 124 }finally{ 125 if(con != null){ 126 try { 127 con.close(); 128 } catch (SQLException e) { 129 e.printStackTrace(); 130 } 131 } 132 } 133 134 } 135 136 public void addItem(Item item) { 137 Connection con = DBManager.getConnection(); 138 try { 139 140 PreparedStatement pStmt = con.prepareStatement("INSERT INTO item VALUES(?, ?, ?)"); 141 pStmt.setString(1, item.getItemNo()); 142 pStmt.setString(2, item.getItemName()); 143 pStmt.setString(3, item.getDescription()); 144 145 pStmt.executeUpdate(); 146 147 pStmt = con.prepareStatement("INSERT INTO item_master VALUES(?, ?)"); 148 pStmt.setString(1, item.getItemNo()); 149 pStmt.setInt(2, 0); 150 151 pStmt.executeUpdate(); 152 153 } catch (SQLException e) { 154 e.printStackTrace(); 155 }finally{ 156 if(con != null){ 157 try { 158 con.close(); 159 } catch (SQLException e) { 160 e.printStackTrace(); 161 } 162 } 163 } 164 } 165 166 }