1 /** 2 * 3 */ 4 package org.apache.geronimo.samples.inventory.dao; 5 6 import java.sql.Connection; 7 import java.sql.PreparedStatement; 8 import java.sql.ResultSet; 9 import java.sql.SQLException; 10 import java.util.ArrayList; 11 import java.util.List; 12 13 import org.apache.geronimo.samples.inventory.Item; 14 import org.apache.geronimo.samples.inventory.util.DBManager; 15 16 17 public class ItemDAO { 18 19 public List getItems(){ 20 ArrayList items = new ArrayList(); 21 22 Connection con = DBManager.getConnection(); 23 try { 24 25 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"); 26 ResultSet rs = pStmt.executeQuery(); 27 int j = 0; 28 while(rs.next()){ 29 ArrayList item = new ArrayList(); 30 31 String itemId = rs.getString("item_id"); 32 String itemName = rs.getString("item_name"); 33 String description = rs.getString("description"); 34 int quantity = rs.getInt("quantity"); 35 36 item.add(0, itemId); 37 item.add(1, itemName); 38 item.add(2, description); 39 item.add(3, quantity+""); 40 41 items.add(j,item); 42 j++; 43 44 } 45 } catch (SQLException e) { 46 e.printStackTrace(); 47 }finally{ 48 if(con != null){ 49 try { 50 con.close(); 51 } catch (SQLException e) { 52 e.printStackTrace(); 53 } 54 } 55 } 56 57 return items; 58 } 59 60 public int getQOH(String itemNo){ 61 62 int qoh = 0; 63 Connection con = DBManager.getConnection(); 64 try { 65 66 PreparedStatement pStmt = con.prepareStatement("SELECT quantity FROM item_master WHERE item_id =? "); 67 pStmt.setString(1, itemNo); 68 69 ResultSet rs = pStmt.executeQuery(); 70 while(rs.next()){ 71 qoh = rs.getInt("quantity"); 72 } 73 } catch (SQLException e) { 74 e.printStackTrace(); 75 }finally{ 76 if(con != null){ 77 try { 78 con.close(); 79 } catch (SQLException e) { 80 e.printStackTrace(); 81 } 82 } 83 } 84 return qoh; 85 } 86 87 public boolean isItemIdExists(String itemNo){ 88 boolean isExists = false; 89 Connection con = DBManager.getConnection(); 90 try { 91 92 PreparedStatement pStmt = con.prepareStatement("SELECT item_id FROM item WHERE item_id =? "); 93 pStmt.setString(1, itemNo); 94 95 ResultSet rs = pStmt.executeQuery(); 96 if(rs.next()){ 97 isExists = true; 98 } 99 } 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 }