· 7 years ago · Sep 27, 2018, 04:50 AM
1package lab6.inventory.database;
2
3import java.sql.PreparedStatement;
4import java.sql.SQLException;
5import java.util.ArrayList;
6import java.util.Arrays;
7import java.util.List;
8
9import lab6.inventory.models.Marker;
10
11import org.apache.logging.log4j.LogManager;
12import org.apache.logging.log4j.Logger;
13
14public class MarkerSQLProvider extends SQLProvider<Marker> {
15
16 private static Logger logger = LogManager.getLogger(MarkerSQLProvider.class);
17
18 public static final String TABLE_NAME = "lb6_marker";
19
20 public MarkerSQLProvider() {
21 super();
22
23 }
24
25 @Override
26 protected void initSQLDatabase() {
27 try {
28 statement = con.createStatement();
29 if (statement
30 .execute("create table if not exists "
31 + TABLE_NAME
32 + " (id INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(50) )")) {
33 logger.debug("ITEM table created");
34 } else {
35 logger.debug("ITEM table does not need to be created");
36 }
37 logger.debug("ITEM table exists");
38 } catch (SQLException e) {
39 logger.error("Unable to initialize SQL Database", e);
40 }
41
42 }
43
44 @Override
45 public List<Marker> selectAll() {
46 List<Marker> items = new ArrayList<Marker>();
47
48 try {
49 statement = con.createStatement(); // create SQL statement
50 String query = "SELECT * FROM " + TABLE_NAME; // specify query to
51 // pull all
52 // records
53 logger.debug(query);
54 result = statement.executeQuery(query); // execute query
55
56 while (result.next()) // while records were retrieved and unread
57 // results
58 {
59 Marker item = new Marker(); // declare new Student
60 item.setId(result.getInt(1)); // get ID column 1
61 item.setName(result.getString(2)); // get Name 2
62
63 items.add(item);
64
65 }
66 logger.debug("Retrieved "+items.size()+" markers");
67 } catch (SQLException e) {
68 logger.error("Unable to retrieve markers",e);
69
70 }
71 return items;
72
73 }
74
75 @Override
76 public Marker get(int id) {
77 Marker item = null;
78 try{
79 statement = con.createStatement();
80 String query = "select * from "+TABLE_NAME+" where id = "+id+";";
81 logger.debug("QUERY : "+query);
82 result = statement.executeQuery(query);
83 while(result.next()){
84 item = new Marker();
85 item.setId(result.getInt(1)); // get ID column 1
86 item.setName(result.getString(2)); // get Name 2
87 }
88
89 return item;
90
91
92 }catch(SQLException e){
93 logger.error("Unable to retrieve marker with id "+id,e);
94
95 }
96 return item;
97
98 }
99
100 @Override
101 public int update(Marker item, int id) {
102 try{
103 String query = "UPDATE "+TABLE_NAME
104 + " SET name = ? "
105 + " WHERE id = ? ";
106 PreparedStatement ps = con.prepareStatement(query);
107 ps.setString(1, item.getName());
108 ps.setInt(2, id);
109 return ps.executeUpdate();
110
111 }catch(SQLException e){
112 logger.error("Unable to update marker with id "+id,e);
113 }
114
115 return 0;
116 }
117
118 @Override
119 public int delete(int id) {
120 try{
121 String query = "DELETE FROM "+TABLE_NAME
122 + " WHERE id = ? ";
123 PreparedStatement ps = con.prepareStatement(query);
124 ps.setInt(1, id);
125 return ps.executeUpdate();
126
127 }catch(SQLException e){
128 logger.error("Unable to delete marker with id "+id,e);
129 }
130
131 return 0;
132 }
133
134 @Override
135 public int add(Marker item) {
136 try{
137 String query = "INSERT INTO "+TABLE_NAME
138 + "(name) VALUES (?) ";
139 PreparedStatement ps = con.prepareStatement(query);
140 ps.setString(1, item.getName());
141 return ps.executeUpdate();
142
143 }catch(SQLException e){
144 logger.error("Unable to add marker",e);
145 }
146
147 return 0;
148 }
149
150 @Override
151 public int deleteMultiple(int[] ids) {
152 try{
153 String IdsConcatenated = Arrays.toString(ids).replace("[","").replace("]","");
154 String query = "DELETE FROM "+TABLE_NAME
155 + " WHERE id in ("+IdsConcatenated+") ";
156 PreparedStatement ps = con.prepareStatement(query);
157
158 return ps.executeUpdate();
159
160 }catch(SQLException e){
161 logger.error("Unable to delete markers with ids : "+Arrays.toString(ids),e);
162 }
163
164 return 0;
165 }
166
167}