rendered paste bodypackage sqldata;import javax.servlet.ServletException;import java.util.List;import java.util.ArrayList;import java.sql.*;/** * @author Sean Robinson * @version v. 1 */public class LaptopData { public List getInventory(Connection conn) { List inventoryList = new ArrayList(); try { Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("select * FROM inventory order by laptopid asc"); while (rs.next()) { inventoryList.add(new Inventory(rs.getString("laptopname"), rs.getString("laptopid"), rs.getString("brand"), rs.getString("model"), rs.getString("serial"), rs.getString("returned"))); } } catch (Exception e) { System.out.println("Inventory gathering error occurred"); e.printStackTrace(); } return inventoryList; } public List getLaptopsOut(Connection conn) { List laptopsOut = new ArrayList(); try { Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("Select * from out"); while (rs.next()) { laptopsOut.add(new Laptop(rs.getString("id"), rs.getString("teachername"), rs.getString("school"), rs.getString("laptopid"), rs.getString("laptopname"), rs.getString("issuedate"))); } } catch (Exception e) { System.out.println("Error getting list of laptops that are in use"); e.printStackTrace(System.out); } return laptopsOut; } public void insertNewInventory(Connection conn, String name, String brand, String model, String serial, String returned) throws ServletException { try { PreparedStatement ps = conn.prepareStatement("INSERT INTO inventory(laptopname, brand, model, serial, returned) " + "VALUES(?,?,?,?,?)"); ps.setString(1, name); ps.setString(2, brand); ps.setString(3, model); ps.setString(4, serial); ps.setString(5, returned); ps.executeUpdate(); ps.close(); } catch (Exception e) { throw new ServletException("Error in inserting new Laptop into Inventory", e); } } public List getReturned(Connection conn) { List returnedLaptops = new ArrayList(); try { Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("select * from inventory WHERE returned = 'yes' order by laptopid asc"); while (rs.next()) { returnedLaptops.add(new Inventory(rs.getString("laptopname"), rs.getString("laptopid"), rs.getString("brand"), rs.getString("model"), rs.getString("serial"), rs.getString("returned"))); } } catch (Exception e) { System.out.println("Error building returned list"); e.printStackTrace(System.out); } return returnedLaptops; } public List getTeacherswithLaptops(Connection conn) throws ServletException { List teacherlist = new ArrayList(); try { Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("Select teachername FROM out ORDER BY laptopid asc"); while (rs.next()) { teacherlist.add(rs.getString("teachername")); } } catch (Exception e) { throw new ServletException("Error Gathering teachers for removal page", e); } return teacherlist; } public List getLaptopsinInventory(Connection conn) throws ServletException { List laptopsinventory = new ArrayList(); try { Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("Select laptopname from Inventory Order by laptopid asc"); while (rs.next()) { laptopsinventory.add(rs.getString("laptopname")); } } catch (Exception e) { throw new ServletException("An Error Occurred gathering the Laptop names from Inventory for removal page", e); } return laptopsinventory; } public void RemoveLaptopfromInventory(Connection conn, String laptop) throws ServletException { try { Statement st = conn.createStatement(); st.executeUpdate("DELETE FROM inventory WHERE laptopname ='" + laptop + "'"); st.close(); } catch (Exception e) { throw new ServletException("Error Occurred in deleting laptop from inventory", e); } } public void removeLaptopFromTeacher(Connection conn, String teacher) throws ServletException { try { Statement st = conn.createStatement(); st.executeUpdate("DELETE FROM out WHERE teachername = '" + teacher + "'"); st.close(); } catch (Exception e) { throw new ServletException("An error occurred removing " + "laptop usage record from the out table(removing laptop)", e); } } public void reAssignLaptop(Connection conn, String id) throws ServletException { try { PreparedStatement ps1 = conn.prepareStatement("UPDATE inventory SET returned = 'no' WHERE laptopid=?"); String number = id; int idlaptop = Integer.decode(number); ps1.setInt(1, idlaptop); ps1.executeUpdate(); ps1.close(); PreparedStatement ps2 = conn.prepareStatement("DELETE FROM out WHERE laptopid='" + number + "'"); ps2.executeUpdate(); ps2.close(); } catch (Exception e) { e.printStackTrace(); } } public void finishReAssign(Connection conn, String id, String laptopname, String school, String teachername, String issuedate) throws ServletException { try { PreparedStatement ps = conn.prepareStatement( "INSERT INTO out(laptopid,laptopname,school,teachername,issuedate)" + "VALUES(?,?,?,?,?)"); ps.setString(1, id); ps.setString(2, laptopname); ps.setString(3, school); ps.setString(4, teachername); ps.setString(5, issuedate); ps.executeUpdate(); ps.close(); } catch (Exception e) { throw new ServletException(e); } } public void laptopReturned(Connection conn, String id) throws ServletException { int laptopid = Integer.decode(id); try { PreparedStatement st = conn.prepareStatement("UPDATE inventory SET returned = 'yes' WHERE laptopid=" + laptopid); st.executeUpdate(); st.close(); } catch (Exception e) { throw new ServletException("Error in updating laptop status to returned", e); } } public static void main(String args[]) { LaptopData ld = new LaptopData(); try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection condb = DriverManager.getConnection("jdbc:odbc:laptops", "", ""); ld.finishReAssign(condb, "12", "boo", "highschoo", "john", "90909"); } catch (Exception e) { e.printStackTrace(System.out); } }}