1 |
1 |
package cz.zcu.kiv.offscreen.user;
|
2 |
2 |
|
|
3 |
import java.sql.PreparedStatement;
|
3 |
4 |
import java.sql.ResultSet;
|
4 |
5 |
import java.sql.SQLException;
|
5 |
|
import java.sql.Statement;
|
6 |
|
import java.text.SimpleDateFormat;
|
7 |
|
import java.util.ArrayList;
|
8 |
|
import java.util.Date;
|
9 |
|
import java.util.HashMap;
|
10 |
|
import java.util.LinkedHashMap;
|
11 |
|
import java.util.Map;
|
12 |
|
|
13 |
|
import javax.servlet.ServletContext;
|
|
6 |
import java.util.*;
|
14 |
7 |
|
15 |
8 |
/**
|
16 |
|
* Class is usefull for saving and loading diagram params from database.
|
17 |
|
*
|
18 |
|
* @author Daniel Bureš
|
|
9 |
* Class is used for saving and loading diagram params from database.
|
19 |
10 |
*
|
|
11 |
* @author Daniel Bureš
|
|
12 |
* @author Tomáš Šimandl
|
20 |
13 |
*/
|
21 |
14 |
public class Diagram {
|
22 |
|
private DB db = null;
|
23 |
|
private int id = 0;
|
24 |
|
private ResultSet rs;
|
25 |
|
|
26 |
|
|
27 |
|
public Diagram(DB db) {
|
28 |
|
this(db,0);
|
29 |
|
}
|
30 |
|
|
31 |
|
|
32 |
|
public Diagram(DB db,int id) {
|
33 |
|
this.db = db;
|
34 |
|
this.id = id;
|
35 |
|
}
|
36 |
|
|
37 |
|
/**
|
38 |
|
* Method returns object id.
|
39 |
|
*
|
40 |
|
* @return id
|
41 |
|
*/
|
42 |
|
public int getId(){
|
43 |
|
return this.id;
|
44 |
|
}
|
45 |
|
|
46 |
|
/**
|
47 |
|
* Method returns id of object as string.
|
48 |
|
*
|
49 |
|
* @return
|
50 |
|
*/
|
51 |
|
public String getIdStr(){
|
52 |
|
return Integer.toString(this.id);
|
53 |
|
}
|
54 |
|
|
55 |
|
/**
|
56 |
|
* Method saves new diagram into database.
|
57 |
|
*
|
58 |
|
* @param param - diagram parameters
|
59 |
|
*/
|
60 |
|
public void update(Map<String, String> param){
|
61 |
|
try {
|
62 |
|
if ( this.id == 0 ) {
|
63 |
|
|
64 |
|
String diagram_name = param.get("diagram_name") ;
|
65 |
|
String hash = param.get("hash") ;
|
66 |
|
String component_count = param.get("component_count") ;
|
67 |
|
String user_id = param.get("user_id") ;
|
68 |
|
String public_diag = param.get("public") ;
|
69 |
|
|
70 |
|
String qy = "INSERT INTO diagram (id, created, user_id,component_count, name, session_id, hash,public ) " +
|
71 |
|
"VALUES ('0', NOW(), '"+user_id+"', '"+component_count+"', '"+diagram_name+"', '', '" + hash + "' , '" + public_diag + "' ) ";
|
72 |
|
Statement st = db.getConn().createStatement();
|
73 |
|
st.executeUpdate(qy, Statement.RETURN_GENERATED_KEYS);
|
74 |
|
ResultSet rs = st.getGeneratedKeys();
|
75 |
|
|
76 |
|
if(rs.next()){
|
77 |
|
this.id = rs.getInt(1);
|
78 |
|
}
|
79 |
|
|
80 |
|
|
81 |
|
}else{
|
82 |
|
String diagram_name = param.get("diagram_name") ;
|
83 |
|
String component_count = param.get("component_count") ;
|
84 |
|
String public_diag = param.get("public") ;
|
85 |
|
|
86 |
|
String qy = "UPDATE diagram SET component_count = '" + component_count + "', name = '"+diagram_name+"', public = '"+public_diag+"' " +
|
87 |
|
" WHERE id= '" + this.id + "'";
|
88 |
|
Statement st = db.getConn().createStatement();
|
89 |
|
st.executeUpdate(qy, Statement.RETURN_GENERATED_KEYS);
|
90 |
|
ResultSet rs = st.getGeneratedKeys();
|
91 |
|
|
92 |
|
}
|
93 |
|
|
94 |
|
|
95 |
|
|
96 |
|
} catch (SQLException e) {
|
97 |
|
e.printStackTrace();
|
98 |
|
}
|
99 |
|
}
|
100 |
|
|
101 |
|
/**
|
102 |
|
* Method deletes diagram from database.
|
103 |
|
*/
|
104 |
|
public void delete(){
|
105 |
|
String qy = "DELETE FROM diagram WHERE id = '"+ this.id+"' LIMIT 1";
|
106 |
|
|
107 |
|
db.exStatement(qy);
|
108 |
|
|
109 |
|
|
110 |
|
}
|
111 |
|
|
112 |
|
/*
|
113 |
|
public void updateViewport(String viewport_html){
|
114 |
|
String qy = "UPDATE diagram SET viewport_html = '"+ viewport_html+"' WHERE id = '"+ this.id+"' LIMIT 1";
|
115 |
|
try {
|
116 |
|
db.exStatement(qy);
|
117 |
|
|
118 |
|
} catch (SQLException e) {
|
119 |
|
|
120 |
|
e.printStackTrace();
|
121 |
|
}
|
122 |
|
}
|
123 |
|
|
124 |
|
public void updateRightpanel(String rightpanel_html){
|
125 |
|
String qy = "UPDATE diagram SET rightpanel_html = '"+ rightpanel_html+"' WHERE id = '"+ this.id+"' LIMIT 1";
|
126 |
|
try {
|
127 |
|
db.exStatement(qy);
|
128 |
|
|
129 |
|
} catch (SQLException e) {
|
130 |
|
|
131 |
|
e.printStackTrace();
|
132 |
|
}
|
133 |
|
}*/
|
134 |
|
|
135 |
|
/**
|
136 |
|
* Method updates vertices position in database. Position of vertices are saved in json.
|
137 |
|
*
|
138 |
|
* @param vertices_position_json
|
139 |
|
*/
|
140 |
|
public void updateVerticesPosition(String vertices_position_json){
|
141 |
|
String qy = "UPDATE diagram SET vertices_position = '"+ vertices_position_json+"' WHERE id = '"+ this.id+"' LIMIT 1";
|
142 |
|
|
143 |
|
db.exStatement(qy);
|
144 |
|
|
145 |
|
|
146 |
|
}
|
147 |
|
|
148 |
|
/**
|
149 |
|
* Method returns parameters of diagram.
|
150 |
|
* @param diagram_id
|
151 |
|
* @return
|
152 |
|
*/
|
153 |
|
public Map<String,String> getDiagramParam(int diagram_id){
|
154 |
|
HashMap<String, String> item_map = new HashMap<String, String>();
|
155 |
|
String qy = "SELECT * FROM diagram WHERE id = '"+diagram_id+"' ORDER BY created DESC ";
|
156 |
|
try {
|
157 |
|
rs = db.exQuery(qy);
|
158 |
|
while ( rs != null && rs.next() ) {
|
159 |
|
|
160 |
|
|
161 |
|
item_map.put("id", String.valueOf(rs.getInt("id")) );
|
162 |
|
item_map.put("name", rs.getString("name") );
|
163 |
|
item_map.put("hash", rs.getString("hash") );
|
164 |
|
item_map.put("public", String.valueOf(rs.getInt("public")) );
|
165 |
|
item_map.put("user_id", String.valueOf(rs.getInt("user_id")) );
|
166 |
|
item_map.put("vertices_position", rs.getString("vertices_position") );
|
167 |
|
item_map.put("component_count", String.valueOf(rs.getInt("component_count")) );
|
168 |
|
item_map.put("created", Util.formatDate(rs.getString("created") ) );
|
169 |
|
item_map.put("session_id", rs.getString("session_id") );
|
170 |
|
|
171 |
|
|
172 |
|
}
|
173 |
|
|
174 |
|
} catch (SQLException e) {
|
175 |
|
|
176 |
|
e.printStackTrace();
|
177 |
|
}
|
178 |
|
|
179 |
|
return item_map;
|
180 |
|
|
181 |
|
}
|
182 |
|
|
183 |
|
/**
|
184 |
|
* Method returns list of digrams, which are uploaded by user.
|
185 |
|
*
|
186 |
|
* @param user_id
|
187 |
|
* @return
|
188 |
|
*/
|
189 |
|
public ArrayList<Map<String,String>> getDiagramListByUserId(int user_id){
|
190 |
|
ArrayList<Map<String,String>> diagram_list = new ArrayList<Map<String,String>>();
|
191 |
|
String qy = "SELECT * FROM diagram WHERE user_id = '"+user_id+"' ORDER BY created DESC ";
|
192 |
|
try {
|
193 |
|
rs = db.exQuery(qy);
|
194 |
|
while ( rs != null && rs.next() ) {
|
195 |
|
HashMap<String, String> item_map = new HashMap<String, String>();
|
196 |
|
|
197 |
|
item_map.put("id", String.valueOf(rs.getInt("id")) );
|
198 |
|
item_map.put("name", (rs.getString("name").length()==0?"No name":rs.getString("name")) );
|
199 |
|
item_map.put("public", String.valueOf(rs.getInt("public")) );
|
200 |
|
item_map.put("component_count", String.valueOf(rs.getInt("component_count")) );
|
201 |
|
item_map.put("hash", rs.getString("hash") );
|
202 |
|
item_map.put("created", Util.formatDate(rs.getString("created") ) );
|
203 |
|
item_map.put("session_id", rs.getString("session_id") );
|
204 |
|
|
205 |
|
diagram_list.add(item_map);
|
206 |
|
}
|
207 |
|
|
208 |
|
} catch (SQLException e) {
|
209 |
|
|
210 |
|
e.printStackTrace();
|
211 |
|
}
|
212 |
|
|
213 |
|
return diagram_list;
|
214 |
|
|
215 |
|
}
|
216 |
|
|
217 |
|
|
218 |
|
/**
|
219 |
|
* Method returns list of public diagrams.
|
220 |
|
* @return
|
221 |
|
*/
|
222 |
|
public ArrayList<Map<String,String>> getDiagramPublicList(){
|
223 |
|
|
224 |
|
ArrayList<Map<String,String>> diagram_list = new ArrayList<Map<String,String>>();
|
225 |
|
String qy = "SELECT * FROM diagram WHERE public = 1 ORDER BY name ASC ";
|
226 |
|
try {
|
227 |
|
rs = db.exQuery(qy);
|
228 |
|
while ( rs != null && rs.next() ) {
|
229 |
|
HashMap<String, String> item_map = new HashMap<String, String>();
|
230 |
|
|
231 |
|
item_map.put("id", String.valueOf(rs.getInt("id")) );
|
232 |
|
item_map.put("name", (rs.getString("name").length()==0?"No name":rs.getString("name")) );
|
233 |
|
item_map.put("public", String.valueOf(rs.getInt("public")) );
|
234 |
|
item_map.put("component_count", String.valueOf(rs.getInt("component_count")) );
|
235 |
|
item_map.put("hash", rs.getString("hash") );
|
236 |
|
item_map.put("created", Util.formatDate(rs.getString("created") ) );
|
237 |
|
item_map.put("session_id", rs.getString("session_id") );
|
238 |
|
|
239 |
|
diagram_list.add(item_map);
|
240 |
|
}
|
241 |
|
|
242 |
|
} catch (SQLException e) {
|
243 |
|
|
244 |
|
e.printStackTrace();
|
245 |
|
}
|
246 |
|
|
247 |
|
return diagram_list;
|
248 |
|
|
249 |
|
}
|
250 |
|
|
|
15 |
private DB db = null;
|
|
16 |
private int id = -1;
|
|
17 |
|
|
18 |
|
|
19 |
public Diagram(DB db) {
|
|
20 |
this(db, -1);
|
|
21 |
}
|
|
22 |
|
|
23 |
public Diagram(DB db, int id) {
|
|
24 |
this.db = db;
|
|
25 |
this.id = id;
|
|
26 |
}
|
|
27 |
|
|
28 |
/**
|
|
29 |
* Method returns object id.
|
|
30 |
*
|
|
31 |
* @return id
|
|
32 |
*/
|
|
33 |
public int getId() {
|
|
34 |
return this.id;
|
|
35 |
}
|
|
36 |
|
|
37 |
public int getUserId(){
|
|
38 |
if(this.id < 0) return -1;
|
|
39 |
|
|
40 |
String qy = "SELECT user_id FROM diagram WHERE id = '" + this.id + "'";
|
|
41 |
ResultSet rs = db.exQuery(qy);
|
|
42 |
|
|
43 |
try{
|
|
44 |
if (rs != null && rs.next()) {
|
|
45 |
return rs.getInt("user_id");
|
|
46 |
}
|
|
47 |
} catch (SQLException e){
|
|
48 |
e.printStackTrace();
|
|
49 |
}
|
|
50 |
return -1;
|
|
51 |
}
|
|
52 |
|
|
53 |
/**
|
|
54 |
* Method return map of values of actual diagram or empty map if id of diagram is invalid.
|
|
55 |
*
|
|
56 |
* @return created map.
|
|
57 |
*/
|
|
58 |
public Map<String, String> getDiagram() {
|
|
59 |
if (this.id < 0) return Collections.emptyMap();
|
|
60 |
|
|
61 |
String qy = "SELECT * FROM diagram WHERE id = '" + this.id + "'";
|
|
62 |
|
|
63 |
try {
|
|
64 |
|
|
65 |
ResultSet rs = db.exQuery(qy);
|
|
66 |
if (rs != null && rs.next()) {
|
|
67 |
return createMap(rs);
|
|
68 |
}
|
|
69 |
|
|
70 |
} catch (SQLException e) {
|
|
71 |
e.printStackTrace();
|
|
72 |
}
|
|
73 |
|
|
74 |
return Collections.emptyMap();
|
|
75 |
}
|
|
76 |
|
|
77 |
/**
|
|
78 |
* Method saves new diagram into database or update existing diagram.
|
|
79 |
*
|
|
80 |
* @param param - diagram parameters
|
|
81 |
*/
|
|
82 |
public void update(Map<String, String> param) {
|
|
83 |
try {
|
|
84 |
if (this.id < 0) {
|
|
85 |
// crating new diagram
|
|
86 |
|
|
87 |
String name = param.get("name");
|
|
88 |
String userId = param.get("user_id");
|
|
89 |
String isPublic = param.get("public");
|
|
90 |
String graphJson = param.get("graph_json");
|
|
91 |
|
|
92 |
|
|
93 |
String qy = "INSERT INTO diagram (name, created, last_update, user_id, public, graph_json ) " +
|
|
94 |
"VALUES (?, NOW(), NOW(), ?, ?, ?) ";
|
|
95 |
|
|
96 |
PreparedStatement pst = db.getConn().prepareStatement(qy);
|
|
97 |
pst.setString(1, name);
|
|
98 |
pst.setString(2, userId);
|
|
99 |
pst.setString(3, isPublic);
|
|
100 |
pst.setString(4, graphJson);
|
|
101 |
pst.executeUpdate();
|
|
102 |
|
|
103 |
ResultSet rs = pst.getGeneratedKeys();
|
|
104 |
|
|
105 |
if (rs.next()) {
|
|
106 |
this.id = rs.getInt(1);
|
|
107 |
}
|
|
108 |
} else {
|
|
109 |
String name = param.get("name");
|
|
110 |
String isPublic = param.get("public");
|
|
111 |
String graphJson = param.get("graph_json");
|
|
112 |
|
|
113 |
String qy = "UPDATE diagram SET name = ?, public = ?, graph_json = ?, last_update = NOW() WHERE id= ?";
|
|
114 |
|
|
115 |
PreparedStatement pst = db.getConn().prepareStatement(qy);
|
|
116 |
pst.setString(1, name);
|
|
117 |
pst.setString(2, isPublic);
|
|
118 |
pst.setString(3, graphJson);
|
|
119 |
pst.setInt(4, this.id);
|
|
120 |
pst.executeUpdate();
|
|
121 |
}
|
|
122 |
} catch (SQLException e) {
|
|
123 |
e.printStackTrace();
|
|
124 |
}
|
|
125 |
}
|
|
126 |
|
|
127 |
/**
|
|
128 |
* Method deletes diagram from database.
|
|
129 |
*/
|
|
130 |
public void delete() {
|
|
131 |
if (id < 0) return;
|
|
132 |
|
|
133 |
String qy = "DELETE FROM diagram WHERE id = '" + this.id + "' LIMIT 1";
|
|
134 |
db.exStatement(qy);
|
|
135 |
}
|
|
136 |
|
|
137 |
/**
|
|
138 |
* Method change graph_json in diagram and return number of affected rows in database.
|
|
139 |
*
|
|
140 |
* @param graphJson json of diagram
|
|
141 |
* @return number of affected rows in database
|
|
142 |
*/
|
|
143 |
public int updateGraphJson(String graphJson){
|
|
144 |
if(this.id < 0) return 0;
|
|
145 |
|
|
146 |
String qy = "UPDATE diagram SET graph_json = ? WHERE id = ?";
|
|
147 |
|
|
148 |
try {
|
|
149 |
PreparedStatement pst = db.getConn().prepareStatement(qy);
|
|
150 |
pst.setString(1, graphJson);
|
|
151 |
pst.setInt(2, this.id);
|
|
152 |
pst.executeUpdate();
|
|
153 |
return pst.getUpdateCount();
|
|
154 |
|
|
155 |
} catch (SQLException e) {
|
|
156 |
e.printStackTrace();
|
|
157 |
}
|
|
158 |
return 0;
|
|
159 |
}
|
|
160 |
|
|
161 |
/**
|
|
162 |
* Method returns list of diagrams, which are uploaded by given user.
|
|
163 |
*
|
|
164 |
* @param user_id id of user
|
|
165 |
* @return created list of diagram params.
|
|
166 |
*/
|
|
167 |
public ArrayList<Map<String, String>> getDiagramListByUserId(int user_id) {
|
|
168 |
String qy = "SELECT * FROM diagram WHERE user_id = '" + user_id + "' ORDER BY created DESC ";
|
|
169 |
|
|
170 |
return createListOfMap(db.exQuery(qy));
|
|
171 |
}
|
|
172 |
|
|
173 |
|
|
174 |
/**
|
|
175 |
* Method returns list of all public diagrams.
|
|
176 |
*
|
|
177 |
* @return created list of diagram params
|
|
178 |
*/
|
|
179 |
public ArrayList<Map<String, String>> getDiagramPublicList() {
|
|
180 |
|
|
181 |
String qy = "SELECT * FROM diagram WHERE public = 1 ORDER BY name ASC ";
|
|
182 |
|
|
183 |
return createListOfMap(db.exQuery(qy));
|
|
184 |
}
|
|
185 |
|
|
186 |
/**
|
|
187 |
* Iterate over all items in input ResultSet and return list of all founded diagrams (map of diagram parameters).
|
|
188 |
*
|
|
189 |
* @param rs result set which contains diagram rows.
|
|
190 |
* @return created list of diagrams.
|
|
191 |
*/
|
|
192 |
private ArrayList<Map<String,String>> createListOfMap(ResultSet rs) {
|
|
193 |
ArrayList<Map<String, String>> diagram_list = new ArrayList<>();
|
|
194 |
|
|
195 |
try {
|
|
196 |
while (rs != null && rs.next()) {
|
|
197 |
diagram_list.add(createMap(rs));
|
|
198 |
}
|
|
199 |
return diagram_list;
|
|
200 |
|
|
201 |
} catch (SQLException e){
|
|
202 |
e.printStackTrace();
|
|
203 |
}
|
|
204 |
|
|
205 |
return new ArrayList<>();
|
|
206 |
}
|
|
207 |
|
|
208 |
/**
|
|
209 |
* Method take from input ResultSet all parameters of diagram. Result set must point to some row and can not be null.
|
|
210 |
*
|
|
211 |
* @param rs not null result set
|
|
212 |
* @return map of all parameters.
|
|
213 |
* @throws SQLException
|
|
214 |
*/
|
|
215 |
private Map<String,String> createMap(ResultSet rs) throws SQLException {
|
|
216 |
HashMap<String, String> item_map = new HashMap<>();
|
|
217 |
|
|
218 |
item_map.put("id", String.valueOf(rs.getInt("id")));
|
|
219 |
item_map.put("name", rs.getString("name"));
|
|
220 |
item_map.put("created", Util.formatDate(rs.getString("created")));
|
|
221 |
item_map.put("last_update", Util.formatDate(rs.getString("last_update")));
|
|
222 |
item_map.put("user_id", String.valueOf(rs.getInt("user_id")));
|
|
223 |
item_map.put("public", String.valueOf(rs.getInt("public")));
|
|
224 |
item_map.put("graph_json", String.valueOf(rs.getString("graph_json")));
|
|
225 |
|
|
226 |
return item_map;
|
|
227 |
}
|
251 |
228 |
}
|
Changed diagram table and Diagram class