Revize 346e9ba2

Simplify communication with DB

- use of MyBatis framework

sources/imiger-core/pom.xml

@@ -145 +145 @@
             <artifactId>mysql-connector-java</artifactId>
             <version>8.0.15</version>
         </dependency>
+        <dependency>
+            <groupId>org.mybatis</groupId>
+            <artifactId>mybatis</artifactId>
+            <version>3.5.0</version>
+        </dependency>
     </dependencies>
 </project>

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/servlets/DataAccessError.java

@@ -2 +2 @@
 
 import com.google.gson.Gson;
 import com.google.gson.JsonObject;
+import org.apache.logging.log4j.LogManager;
+import org.apache.logging.log4j.Logger;
 
 import javax.servlet.ServletException;
 import javax.servlet.http.HttpServlet;
@@ -11 +13 @@
 import java.io.PrintWriter;
 
 public class DataAccessError extends HttpServlet {
+    private static final Logger logger = LogManager.getLogger();
 
     @Override
     protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
@@ -25 +28 @@
 
     private void handleRequest(HttpServletRequest request, HttpServletResponse response) throws IOException {
         Throwable throwable = (Throwable) request.getAttribute("javax.servlet.error.exception");
+        logger.error("Data access exception: ", throwable);
+
         response.setContentType("application/json");
         PrintWriter out = response.getWriter();
 

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/servlets/UploadFiles.java

@@ -2 +2 @@
 
 import cz.zcu.kiv.offscreen.modularization.ModuleProvider;
 import cz.zcu.kiv.offscreen.storage.FileLoader;
-import cz.zcu.kiv.offscreen.user.DB;
 import cz.zcu.kiv.offscreen.user.DataAccessException;
-import cz.zcu.kiv.offscreen.user.Diagram;
+import cz.zcu.kiv.offscreen.user.dao.DiagramDAO;
 import org.apache.commons.lang3.StringUtils;
 import org.apache.logging.log4j.LogManager;
 import org.apache.logging.log4j.Logger;
@@ -30 +29 @@
 
         logger.debug("Processing request");
 
-        List<Map<String, String>> userDiagramList = new ArrayList<>();
-        List<Map<String, String>> publicDiagramList = new ArrayList<>();
+        List<Map<String, Object>> userDiagramList = new ArrayList<>();
+        List<Map<String, Object>> publicDiagramList = new ArrayList<>();
         try {
-            DB db = new DB(getServletContext());
-            Diagram diagram = new Diagram(db);
+            DiagramDAO diagramDAO = new DiagramDAO();
 
             if (isLoggedIn(request)) {
                 logger.debug("Logged user");
                 int loggedUserId = getUserId(request);
 
-                userDiagramList = diagram.getDiagramListByUserId(loggedUserId);
+                userDiagramList = diagramDAO.getDiagramsByUserId(loggedUserId);
             }
 
-            publicDiagramList = diagram.getDiagramPublicList();
+            publicDiagramList = diagramDAO.getPublicDiagrams();
 
         } catch (DataAccessException e){
             logger.error("Data access exception");

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/servlets/api/GetDBDiagram.java

@@ -2 +2 @@
 
 import com.google.gson.Gson;
 import cz.zcu.kiv.offscreen.servlets.BaseServlet;
-import cz.zcu.kiv.offscreen.user.DB;
-import cz.zcu.kiv.offscreen.user.Diagram;
+import cz.zcu.kiv.offscreen.user.dao.DiagramDAO;
 import org.apache.commons.lang3.StringUtils;
 import org.apache.logging.log4j.LogManager;
 import org.apache.logging.log4j.Logger;
@@ -11 +10 @@
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 import java.io.IOException;
+import java.util.Map;
 
 /**
  * This class is used for loading diagrams from database.
@@ -30 +30 @@
             return;
         }
 
-        DB db = new DB(getServletContext());
-        Diagram diagram = new Diagram(db, Integer.parseInt(diagramId));
+        DiagramDAO diagramDAO = new DiagramDAO();
+        Map<String, Object> diagram = diagramDAO.getDiagram(Integer.parseInt(diagramId));
 
-        if (!diagram.isPublic() && (!isLoggedIn(request) || (isLoggedIn(request) && diagram.getUserId() != getUserId(request)))) {
+        if (!(boolean)diagram.get("public") && (!isLoggedIn(request) || (isLoggedIn(request) && (int)diagram.get("user_id") != getUserId(request)))) {
             response.setStatus(HttpServletResponse.SC_UNAUTHORIZED);
             logger.debug("User is unauthorized");
             return;
         }
 
-        String json = new Gson().toJson(diagram.getDiagram());
+        String json = new Gson().toJson(diagram);
 
         response.setContentType("application/json");
         response.setCharacterEncoding("UTF-8");

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/servlets/api/GetDBDiagramData.java

@@ -1 +1 @@
 package cz.zcu.kiv.offscreen.servlets.api;
 
 import cz.zcu.kiv.offscreen.servlets.BaseServlet;
-import cz.zcu.kiv.offscreen.user.DB;
-import cz.zcu.kiv.offscreen.user.Diagram;
+import cz.zcu.kiv.offscreen.user.dao.DiagramDAO;
 import org.apache.commons.lang3.StringUtils;
 import org.apache.logging.log4j.LogManager;
 import org.apache.logging.log4j.Logger;
@@ -10 +9 @@
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 import java.io.IOException;
+import java.util.Map;
 
 /**
  * This class is used for loading diagram's graph JSON from database.
@@ -29 +29 @@
             return;
         }
 
-        DB db = new DB(getServletContext());
-        Diagram diagram = new Diagram(db, Integer.parseInt(diagramId));
+        DiagramDAO diagramDAO = new DiagramDAO();
+        Map<String, Object> diagram = diagramDAO.getDiagram(Integer.parseInt(diagramId));
 
-        if (!diagram.isPublic() && (!isLoggedIn(request) || (isLoggedIn(request) && diagram.getUserId() != getUserId(request)))) {
+        if (!(boolean)diagram.get("public") && (!isLoggedIn(request) || (isLoggedIn(request) && (int)diagram.get("user_id") != getUserId(request)))) {
             response.setStatus(HttpServletResponse.SC_UNAUTHORIZED);
             logger.debug("User is unauthorized");
             return;
         }
 
-        String json = diagram.getDiagram().get("graph_json");
+        String json = (String)diagram.get("graph_json");
 
         response.setContentType("application/json");
         response.setCharacterEncoding("UTF-8");

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/servlets/api/GetPrivateDiagrams.java

@@ -3 +3 @@
 import com.google.gson.JsonArray;
 import com.google.gson.JsonObject;
 import cz.zcu.kiv.offscreen.servlets.BaseServlet;
-import cz.zcu.kiv.offscreen.user.DB;
-import cz.zcu.kiv.offscreen.user.Diagram;
+import cz.zcu.kiv.offscreen.user.dao.DiagramDAO;
 import org.apache.logging.log4j.LogManager;
 import org.apache.logging.log4j.Logger;
 
@@ -28 +27 @@
             return;
         }
 
-        DB db = new DB(getServletContext());
-        Diagram diagram = new Diagram(db);
+        DiagramDAO diagramDAO = new DiagramDAO();
 
         int loggedUserId = getUserId(request);
-        List<Map<String, String>> userDiagramList = diagram.getDiagramListByUserId(loggedUserId);
+        List<Map<String, Object>> userDiagramList = diagramDAO.getDiagramsByUserId(loggedUserId);
 
         JsonArray jsonArray = new JsonArray();
-        for (Map<String, String> userDiagram : userDiagramList) {
+        for (Map<String, Object> userDiagram : userDiagramList) {
+
             JsonObject jsonObject = new JsonObject();
-            jsonObject.addProperty("id", userDiagram.get("id"));
-            jsonObject.addProperty("name", userDiagram.get("name"));
-            jsonObject.addProperty("created", userDiagram.get("created"));
-            jsonObject.addProperty("last_update", userDiagram.get("last_update"));
+            jsonObject.addProperty("id", String.valueOf(userDiagram.get("id")));
+            jsonObject.addProperty("name", String.valueOf(userDiagram.get("name")));
+            jsonObject.addProperty("created", String.valueOf(userDiagram.get("created")));
+            jsonObject.addProperty("last_update", String.valueOf(userDiagram.get("last_update")));
             jsonArray.add(jsonObject);
         }
 

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/servlets/api/Login.java

@@ -2 +2 @@
 
 import com.google.gson.JsonObject;
 import cz.zcu.kiv.offscreen.servlets.BaseServlet;
-import cz.zcu.kiv.offscreen.user.DB;
-import cz.zcu.kiv.offscreen.user.User;
+import cz.zcu.kiv.offscreen.user.dao.UserDAO;
 import cz.zcu.kiv.offscreen.vo.UserVO;
 import org.apache.commons.lang3.StringUtils;
 import org.apache.logging.log4j.LogManager;
@@ -12 +11 @@
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 import java.io.IOException;
+import java.util.Map;
 
 public class Login extends BaseServlet {
     private static final Logger logger = LogManager.getLogger();
@@ -35 +35 @@
         }
 
         if (errors.size() == 0) {
-            DB db = new DB(getServletContext());
-            User user = new User(db);
+            UserDAO userDAO = new UserDAO();
+            Map<String, Object> user = userDAO.login(username, password);
 
-            if (user.login(username, password)) {
+            if (user != null) {
                 UserVO userVO = new UserVO();
-                userVO.setId(user.getId());
-                userVO.setUsername(user.getNick());
+                userVO.setId((int)user.get("id"));
+                userVO.setUsername((String)user.get("nick"));
 
                 request.getSession().setAttribute("isLoggedIn", true);
                 request.getSession().setAttribute("userId", userVO.getId());
                 request.getSession().setAttribute("user", userVO);
 
                 JsonObject json = new JsonObject();
-                json.addProperty("id", user.getId());
-                json.addProperty("username", user.getNick());
+                json.addProperty("id",(int) user.get("id"));
+                json.addProperty("username", (String) user.get("nick"));
 
 
                 response.setContentType("application/json");

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/servlets/api/Register.java

@@ -2 +2 @@
 
 import com.google.gson.JsonObject;
 import cz.zcu.kiv.offscreen.servlets.BaseServlet;
-import cz.zcu.kiv.offscreen.user.DB;
-import cz.zcu.kiv.offscreen.user.User;
+import cz.zcu.kiv.offscreen.user.dao.UserDAO;
 import org.apache.commons.lang3.StringUtils;
 import org.apache.commons.validator.routines.EmailValidator;
 import org.apache.logging.log4j.LogManager;
@@ -12 +11 @@
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 import java.io.IOException;
-import java.util.HashMap;
-import java.util.Map;
 
 public class Register extends BaseServlet {
 
@@ -29 +26 @@
 		String password = request.getParameter("password");
 		String passwordCheck = request.getParameter("passwordCheck");
 
-		DB db = new DB(getServletContext());
-		User user = new User(db);
+		UserDAO userDAO = new UserDAO();
 
 		JsonObject errors = new JsonObject();
 
@@ -47 +43 @@
 			errors.addProperty("email", "Please enter valid e-mail address.");
 			logger.debug("Invalid e-mail address");
 
-		} else if (user.isEmailExists(email)) {
+		} else if (userDAO.isEmailExists(email)) {
 			errors.addProperty("email", "E-mail already exists.");
 			logger.debug("E-mail exists");
 		}
@@ -56 +52 @@
 			errors.addProperty("username", "Please enter username.");
 			logger.debug("Empty user name");
 
-		} else if (user.isNickExists(username)) {
+		} else if (userDAO.isNickExists(username)) {
 			errors.addProperty("username", "Nickname already exists.");
 			logger.debug("Username(nickname) exists");
 		}
@@ -75 +71 @@
 		}
 
     	if (errors.size() == 0) {
-    		Map<String, String> userMap = new HashMap<>();
-    		userMap.put("name", name);
-    		userMap.put("email", email);
-    		userMap.put("nick", username);
-    		userMap.put("password", password);
-    		userMap.put("session", request.getSession().getId());
-
-    		user.register(userMap);
+    		userDAO.register(username, name, password, request.getSession().getId(), email);
 
 			response.setStatus(HttpServletResponse.SC_CREATED);
 			logger.debug("User created");

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/servlets/api/RemoveDiagram.java

@@ -1 +1 @@
 package cz.zcu.kiv.offscreen.servlets.api;
 
 import cz.zcu.kiv.offscreen.servlets.BaseServlet;
-import cz.zcu.kiv.offscreen.user.DB;
-import cz.zcu.kiv.offscreen.user.Diagram;
+import cz.zcu.kiv.offscreen.user.dao.DiagramDAO;
 import org.apache.commons.lang3.StringUtils;
 import org.apache.logging.log4j.LogManager;
 import org.apache.logging.log4j.Logger;
@@ -33 +32 @@
             return;
         }
 
-        DB db = new DB(getServletContext());
-        Diagram diagram = new Diagram(db, Integer.parseInt(diagramId));
+        DiagramDAO diagramDAO = new DiagramDAO();
 
-        if (diagram.getUserId() != loggedUserId) {
+        if (diagramDAO.getDiagramUserId(Integer.parseInt(diagramId)) != loggedUserId) {
             response.sendError(HttpServletResponse.SC_UNAUTHORIZED);
             logger.debug("User is not owner of diagram");
             return;
         }
 
-        diagram.delete();
+        diagramDAO.deleteDiagram(Integer.parseInt(diagramId));
         logger.debug("Diagram removed");
 
         response.setStatus(HttpServletResponse.SC_NO_CONTENT);

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/servlets/api/SaveDiagram.java

@@ -2 +2 @@
 
 import com.google.gson.Gson;
 import cz.zcu.kiv.offscreen.servlets.BaseServlet;
-import cz.zcu.kiv.offscreen.user.DB;
-import cz.zcu.kiv.offscreen.user.Diagram;
+import cz.zcu.kiv.offscreen.user.dao.DiagramDAO;
 import org.apache.commons.lang3.StringUtils;
 import org.apache.logging.log4j.LogManager;
 import org.apache.logging.log4j.Logger;
@@ -11 +10 @@
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 import java.io.IOException;
-import java.util.HashMap;
-import java.util.Map;
 
 public class SaveDiagram extends BaseServlet {
     private static final Logger logger = LogManager.getLogger();
@@ -43 +40 @@
             return;
         }
 
-        DB db = new DB(getServletContext());
-        Diagram diagram;
+        DiagramDAO diagramDAO = new DiagramDAO();
+
+        int intDiagramId;
 
         if (StringUtils.isBlank(diagramId)) {
             logger.debug("Creating new diagram");
-            diagram = new Diagram(db);
+            intDiagramId = diagramDAO.createDiagram(name, Integer.toString(loggedUserId), isPublic, graphJson);
+            logger.debug("Diagram created or updated.");
 
         } else {
             logger.debug("Getting existing diagram from database");
-            diagram = new Diagram(db, Integer.parseInt(diagramId));
+            intDiagramId = Integer.parseInt(diagramId);
+            int diagramUserId = diagramDAO.getDiagramUserId(intDiagramId);
 
-            if (loggedUserId != diagram.getUserId()) {
+            if (loggedUserId != diagramUserId) {
                 response.sendError(HttpServletResponse.SC_UNAUTHORIZED);
                 logger.debug("User is not owner of diagram");
                 return;
             }
-        }
 
-        Map<String, String> diagramParams = new HashMap<>();
-        diagramParams.put("name", name);
-        diagramParams.put("public", isPublic);
-        diagramParams.put("graph_json", graphJson);
-        diagramParams.put("user_id", Integer.toString(loggedUserId));
-
-        diagram.update(diagramParams);
-        logger.debug("Diagram created or updated.");
+            diagramDAO.updateDiagram(intDiagramId, name, isPublic, graphJson);
+            logger.debug("Diagram updated.");
+        }
 
         // send response
 
-        String json = new Gson().toJson(diagram.getDiagram());
+        String json = new Gson().toJson(diagramDAO.getDiagram(intDiagramId));
 
         response.setContentType("application/json");
         response.setCharacterEncoding("UTF-8");

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/user/DB.java

@@ -1 @@
-package cz.zcu.kiv.offscreen.user;
-
-import org.apache.logging.log4j.LogManager;
-import org.apache.logging.log4j.Logger;
-
-import javax.servlet.ServletContext;
-import java.sql.*;
-
-/**
- * Class Db is useful for communication with mysql database.
- * 
- * @author Daniel Bureš
- *
- */
-
-public class DB {
-
-	private static final Logger logger = LogManager.getLogger();
-	private Connection connection;
-	
-	/**
-	 * Constructor opens connection do database. Access credentials must be stored in ServletContext as InitParameter.
-	 */
-	public DB(ServletContext context) {
-		String driverClassName = context.getInitParameter("jdbc.driverClassName");
-		String url = context.getInitParameter("jdbc.url");
-		String username = context.getInitParameter("jdbc.username");
-		String password = context.getInitParameter("jdbc.password");
-
-		try {
-			logger.debug("Opening database connection");
-			Class.forName(driverClassName);
-			connection = DriverManager.getConnection(url, username, password);
-			connection.setAutoCommit(true);
-		} catch (ClassNotFoundException | SQLException e) {
-			logger.error("Can not open database connection: ", e);
-			throw new DataAccessException(e);
-		}
-	}
-
-	/**
-	 * Method return prepared statement for inserting of values
-
-	 * @param query query with ? on values.
-	 * @param returnGeneratedKeys true - RETURN_GENERATED_KEYS flag is set, false - no flag is set
-	 */
-	PreparedStatement getPreparedStatement(String query, boolean returnGeneratedKeys) {
-		try {
-			if (returnGeneratedKeys) {
-				return connection.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);
-			} else {
-				return connection.prepareStatement(query);
-			}
-		}catch (SQLException e){
-			logger.error("Can not execute query: " + query);
-			throw new DataAccessException(e);
-		}
-	}
-
-	/**
-	 * Method execute sql query like SELECT created with prepared statement and returns affected rows as resultSet or null.
-	 *
-	 * @param preparedStatement prepared statement with query and set all parameters.
-	 * @return ResultSet - data from database.
-	 */
-	ResultSet executeQuery(PreparedStatement preparedStatement) {
-		try {
-			preparedStatement.execute();
-			return preparedStatement.getResultSet();
-		} catch (SQLException e) {
-			logger.error("Can not execute database query: ", e);
-			throw new DataAccessException(e);
-		}
-	}
-
-	/**
-	 * Method execute update or insert sql query created with prepared statement and return resultSet of generated keys or null.
-	 * @param preparedStatement prepared statement with query and set all parameters.
-	 * @return ResultSet - generated keys or null.
-	 */
-	ResultSet executeUpdate(PreparedStatement preparedStatement) {
-		try {
-			preparedStatement.executeUpdate();
-			return preparedStatement.getGeneratedKeys();
-		} catch (SQLException e) {
-			logger.error("Can not execute database query: ", e);
-			throw new DataAccessException(e);
-		}
-	}
-
-	/**
-	 * Method execute sql query like SELECT and returns affected rows as resultSet or null.
-	 * @param sql - completed sql query
-	 * @return ResultSet - data from database
-	 */
-	ResultSet executeQuery(String sql) {
-		try {
-			Statement stat = connection.createStatement();
-			stat.execute(sql);
-			return stat.getResultSet();
-
-		} catch(SQLException | NullPointerException e) {
-			logger.error("Can not execute database query: ", e);
-			throw new DataAccessException(e);
-		}
-	}
-
-	/**
-	 * Method execute prepared sql query. Sql query can be INSERT, DELETE, UPDATE
-	 *
-	 * @return count of affected rows or -1 on SQLException and -2 on NullPointerException
-	 * */
-	int executeStatement(PreparedStatement preparedStatement) {
-		try {
-			return preparedStatement.executeUpdate();
-		} catch(NullPointerException | SQLException e) {
-			logger.error("Can not execute database query: ", e);
-			throw new DataAccessException(e);
-		}
-	}
-
-}

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/user/Diagram.java

@@ -1 @@
-package cz.zcu.kiv.offscreen.user;
-
-import org.apache.logging.log4j.LogManager;
-import org.apache.logging.log4j.Logger;
-
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.util.ArrayList;
-import java.util.Collections;
-import java.util.HashMap;
-import java.util.Map;
-
-/**
- * Class is used for saving and loading diagram params from database.
- *
- * @author Daniel Bureš
- * @author Tomáš Šimandl
- */
-public class Diagram {
-    private static final Logger logger = LogManager.getLogger();
-
-    private DB db = null;
-    private int id = 0;
-
-
-    public Diagram(DB db) {
-        this(db, 0);
-    }
-
-    public Diagram(DB db, int id) {
-        this.db = db;
-        this.id = id;
-    }
-
-    /**
-     * Method returns object id.
-     *
-     * @return id
-     */
-    public int getId() {
-        return this.id;
-    }
-
-    /**
-     * Return id of user which is owner of diagram.
-     * @return user id
-     */
-    public int getUserId(){
-        if(this.id == 0) return 0;
-
-        String qy = "SELECT user_id FROM diagram WHERE id = '" + this.id + "'";
-        ResultSet rs = db.executeQuery(qy);
-
-        try{
-            if (rs != null && rs.next()) {
-                return rs.getInt("user_id");
-            }
-        }  catch (SQLException e){
-            logger.error("Can not get owner id: ", e);
-            throw new DataAccessException(e);
-        }
-        return -1;
-    }
-
-    /**
-     * Returns if diagram is public or not
-     * @return true - diagram is public, false - diagram is not public
-     */
-    public boolean isPublic(){
-        if(this.id == 0) return false;
-
-        String qy = "SELECT public FROM diagram WHERE id = '" + this.id + "'";
-        ResultSet rs = db.executeQuery(qy);
-
-        try{
-            if (rs != null && rs.next()) {
-                return rs.getString("public").equals("1");
-            }
-        }  catch (SQLException e){
-            logger.error("Can not check if diagram is public: ", e);
-            throw new DataAccessException(e);
-        }
-        return false;
-    }
-
-    /**
-     * Returns json of diagram. Json can be send straight to frontend.
-     */
-    public String getJsonDiagram(){
-        if(this.id == 0) return "";
-
-        String qy = "SELECT graph_json FROM diagram WHERE id = '" + this.id + "'";
-        ResultSet rs = db.executeQuery(qy);
-
-        try{
-            if (rs != null && rs.next()) {
-                return rs.getString("graph_json");
-            }
-        }  catch (SQLException e){
-            logger.error("Can not get json of diagram: ", e);
-            throw new DataAccessException(e);
-        }
-        return "";
-    }
-
-    /**
-     * Method return map of values of actual diagram or empty map if id of diagram is invalid.
-     *
-     * @return created map.
-     */
-    public Map<String, String> getDiagram() {
-        if (this.id == 0) return Collections.emptyMap();
-
-        String qy = "SELECT * FROM diagram WHERE id = '" + this.id + "'";
-
-        try {
-
-            ResultSet rs = db.executeQuery(qy);
-            if (rs != null && rs.next()) {
-                return createMap(rs);
-            }
-
-        } catch (SQLException e) {
-            logger.error("Can not get diagram: ", e);
-            throw new DataAccessException(e);
-        }
-
-        return Collections.emptyMap();
-    }
-
-    /**
-     * Method saves new diagram into database or update existing diagram.
-     *
-     * @param param - diagram parameters
-     */
-    public void update(Map<String, String> param) {
-        try {
-            if (this.id == 0) {
-                // crating new diagram
-
-                String name = param.get("name");
-                String userId = param.get("user_id");
-                String isPublic = param.get("public");
-                String graphJson = param.get("graph_json");
-
-
-                String qy = "INSERT INTO diagram (name, created, last_update, user_id, public, graph_json ) " +
-                        "VALUES (?, NOW(), NOW(), ?, ?, ?) ";
-
-                PreparedStatement pst = db.getPreparedStatement(qy, true);
-                pst.setString(1, name);
-                pst.setString(2, userId);
-                pst.setString(3, isPublic);
-                pst.setString(4, graphJson);
-
-                ResultSet rs = db.executeUpdate(pst);
-
-                if (rs != null && rs.next()) {
-                    this.id = rs.getInt(1);
-                }
-            } else {
-                String name = param.get("name");
-                String isPublic = param.get("public");
-                String graphJson = param.get("graph_json");
-
-                String qy = "UPDATE diagram SET name = ?, public = ?, graph_json = ?, last_update = NOW() WHERE id= ?";
-
-                PreparedStatement pst = db.getPreparedStatement(qy, false);
-                pst.setString(1, name);
-                pst.setString(2, isPublic);
-                pst.setString(3, graphJson);
-                pst.setInt(4, this.id);
-
-                db.executeStatement(pst);
-            }
-        } catch (SQLException e) {
-            logger.error("Can not update diagram: ", e);
-            throw new DataAccessException(e);
-        }
-    }
-
-    /**
-     * Method deletes diagram from database.
-     */
-    public void delete() {
-        if (this.id == 0) return;
-
-        String qy = "DELETE FROM diagram WHERE id = ? LIMIT 1";
-
-        try {
-            PreparedStatement pst = db.getPreparedStatement(qy, false);
-            pst.setInt(1, this.id);
-            db.executeStatement(pst);
-
-        } catch (SQLException e) {
-            logger.error("Can not delete diagram: ", e);
-            throw new DataAccessException(e);
-        }
-    }
-
-    /**
-     * Method returns list of diagrams, which are uploaded by given user.
-     *
-     * @param user_id id of user
-     * @return created list of diagram params.
-     */
-    public ArrayList<Map<String, String>> getDiagramListByUserId(int user_id) {
-        String qy = "SELECT * FROM diagram WHERE user_id = '" + user_id + "' ORDER BY created DESC ";
-
-        return createListOfMap(db.executeQuery(qy));
-    }
-
-
-    /**
-     * Method returns list of all public diagrams.
-     *
-     * @return created list of diagram params
-     */
-    public ArrayList<Map<String, String>> getDiagramPublicList() {
-
-        String qy = "SELECT * FROM diagram WHERE public = 1 ORDER BY name ASC ";
-
-        return createListOfMap(db.executeQuery(qy));
-    }
-
-    /**
-     * Iterate over all items in input ResultSet and return list of all founded diagrams (map of diagram parameters).
-     *
-     * @param rs result set which contains diagram rows.
-     * @return created list of diagrams.
-     */
-    private ArrayList<Map<String,String>> createListOfMap(ResultSet rs) {
-        ArrayList<Map<String, String>> diagram_list = new ArrayList<>();
-
-        try {
-            while (rs != null && rs.next()) {
-                diagram_list.add(createMap(rs));
-            }
-            return diagram_list;
-
-        } catch (SQLException e){
-            logger.error("Can not create map from input result set: ", e);
-            throw new DataAccessException(e);
-        }
-    }
-
-    /**
-     * Method take from input ResultSet all parameters of diagram. Result set must point to some row and can not be null.
-     *
-     * @param rs not null result set
-     * @return map of all parameters.
-     */
-    private Map<String,String> createMap(ResultSet rs) throws SQLException {
-        HashMap<String, String> item_map = new HashMap<>();
-
-        item_map.put("id", String.valueOf(rs.getInt("id")));
-        item_map.put("name", rs.getString("name"));
-        item_map.put("created", Util.formatDate(rs.getString("created")));
-        item_map.put("last_update", Util.formatDate(rs.getString("last_update")));
-        item_map.put("user_id", String.valueOf(rs.getInt("user_id")));
-        item_map.put("public", String.valueOf(rs.getInt("public")));
-        item_map.put("graph_json", String.valueOf(rs.getString("graph_json")));
-
-        return item_map;
-    }
-}

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/user/MyBatisUtil.java

@@ +1 @@
+package cz.zcu.kiv.offscreen.user;
+
+import org.apache.ibatis.io.Resources;
+import org.apache.ibatis.session.SqlSessionFactory;
+import org.apache.ibatis.session.SqlSessionFactoryBuilder;
+
+import java.io.InputStream;
+
+public class MyBatisUtil {
+    private static final String CONFIGURATION = "mybatis-config.xml";
+    private static SqlSessionFactory sqlSessionFactory;
+
+    public static SqlSessionFactory getSqlSessionFactory() {
+
+        if (sqlSessionFactory == null) {
+            try {
+                InputStream inputStream = Resources.getResourceAsStream(CONFIGURATION);
+                sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
+            } catch (Exception e) {
+                throw new DataAccessException(e);
+            }
+        }
+        return sqlSessionFactory;
+    }
+}

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/user/User.java

@@ -1 @@
-package cz.zcu.kiv.offscreen.user;
-import org.apache.logging.log4j.LogManager;
-import org.apache.logging.log4j.Logger;
-import org.springframework.security.crypto.bcrypt.BCrypt;
-
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.util.Collections;
-import java.util.HashMap;
-import java.util.Map;
-
-/**
- * Class User is used for creating, updating or loading user from database.
- *
- * @author Daniel Bureš
- *
- */
-public class User {
-
-	private static final Logger logger = LogManager.getLogger();
-
-	private DB db = null;
-	private int id = 0;
-
-	/**
-	 * Constructs object, where is saved db connection.
-	 */
-	public User(DB db) {
-		this(db,0);
-	}
-
-	/**
-	 * Constructs object, where is saved db connection and user id. User, who has this id is loaded from database.
-	 */
-	public User(DB db, int id){
-		this.db = db;
-		this.id = id;
-	}
-
-	/**
-	 * Method try to login user by his nick name and password. If login is OK then returns true else  returns false;
-	 *
-	 * @param nick users login name
-	 * @param psw users password
-	 * @return true - login ok
-	 * 		   false - login failed
-	 */
-	public boolean login(String nick, String psw){
-		String qy = "SELECT * FROM user WHERE nick LIKE ? AND active = '1' LIMIT 1";
-
-		try{
-			PreparedStatement pst = db.getPreparedStatement(qy, false);
-			pst.setString(1, nick);
-			ResultSet rs = db.executeQuery(pst);
-
-			if ( rs != null && rs.next() ) {
-				if(rs.getInt("id") > 0 && BCrypt.checkpw(psw, rs.getString("psw"))){
-					this.id = rs.getInt("id");
-					return true;
-				}
-			}
-		} catch (SQLException | IllegalArgumentException e) {
-			logger.error("Can not login user: ", e);
-			throw new DataAccessException(e);
-		}
-
-		return false;
-	}
-
-	/**
-	 * Method register new user to database. Id of user (variable id) must not be set.
-	 * When register is success variable id is set.
-	 *
-	 * @param param Parameters which describing user. Keys must be: nick, name, password, session, email.
-	 */
-	public void register(Map<String, String> param){
-		try {
-			if ( this.id  == 0 ) {
-
-				String qy = "INSERT INTO user (id, created, active, nick, name, psw, session, email) " +
-						"VALUES ( ?, NOW(), ?, ?, ?, ?, ?, ?)";
-
-				PreparedStatement pst = db.getPreparedStatement(qy, true);
-				pst.setInt(1, 0);
-				pst.setInt(2, 1);
-				pst.setString(3, param.get("nick"));
-				pst.setString(4, param.get("name"));
-				pst.setString(5, BCrypt.hashpw(param.get("password"), BCrypt.gensalt()));
-				pst.setString(6, param.get("session"));
-				pst.setString(7, param.get("email"));
-
-				ResultSet rs = db.executeUpdate(pst);
-
-				if(rs != null && rs.next()){
-					this.id = rs.getInt(1);
-				}
-			}
-
-		} catch (SQLException e) {
-			logger.error("Can not register user: ", e);
-			throw new DataAccessException(e);
-		}
-	}
-
-	/**
-	 * Return all information about user from database stored in map. Keys of map are: id, nick, name, psw, session,
-	 * active, created, email.
-	 *
-	 * @return created map or empty map.
-	 */
-	public Map<String, String> getUser(){
-		String qy = "SELECT * FROM user WHERE id = '" + this.id + "'";
-		try {
-			ResultSet rs = db.executeQuery(qy);
-
-			if ( rs != null && rs.next() ) {
-				Map<String, String> params = new HashMap<>(8);
-				params.put("id", rs.getString("id"));
-				params.put("nick", rs.getString("nick"));
-				params.put("name", rs.getString("name"));
-				params.put("psw", rs.getString("psw"));
-				params.put("session", rs.getString("session"));
-				params.put("active", rs.getString("active"));
-				params.put("created", rs.getString("created"));
-				params.put("email", rs.getString("email"));
-
-				return params;
-
-			} else {
-				this.id = 0;
-			}
-		} catch (SQLException e) {
-			logger.error("Can not get user: ", e);
-			throw new DataAccessException(e);
-		}
-
-		return Collections.emptyMap();
-	}
-
-	/**
-	 * Returns id of user. Value 0 indicates not existing user.
-	 *
-	 * @return int with user id or 0.
-	 */
-	public int getId(){
-		return id;
-	}
-
-	/**
-	 * Method sets session for current user and save session to database.
-	 */
-	public void setSession(String session){
-		if(id > 0){
-			String qy = "UPDATE user SET session= ? WHERE id='" + this.id + "'";
-
-			try {
-
-				PreparedStatement pst = db.getPreparedStatement(qy, false);
-				pst.setString(1, session);
-				db.executeStatement(pst);
-
-			} catch (SQLException e) {
-				logger.error("Can not get user session: ", e);
-				throw new DataAccessException(e);
-			}
-		}
-	}
-
-	/**
-	 * Method saves parameters into database. Method can change only email, nick and active parameters.
-	 * @param param - parameters to save
-	 */
-	public void update(Map<String, String> param) {
-		if ( this.id  != 0 ) {
-			try {
-				String qy = "UPDATE user SET  email = ?, nick = ?, active = ? WHERE id = ?";
-
-				PreparedStatement pst = db.getPreparedStatement(qy, false);
-				pst.setString(1, param.get("email"));
-				pst.setString(2, param.get("nick"));
-				pst.setString(3, param.get("active"));
-				pst.setInt(4, this.id);
-
-				db.executeStatement(pst);
-			} catch (SQLException e) {
-				logger.error("Can not update user: ", e);
-				throw new DataAccessException(e);
-			}
-		}
-	}
-
-	/**
-	 * Returns nick of user or empty string when no user is loaded.
-	 */
-	public String getNick(){
-		if (this.id != 0) {
-			String qy = "SELECT nick FROM user WHERE id = '" + this.id + "'";
-			ResultSet rs = db.executeQuery(qy);
-
-			try{
-				if (rs != null && rs.next()) {
-					return rs.getString("nick");
-				}
-			}  catch (SQLException e){
-				logger.error("Can not get users nick name: ", e);
-				throw new DataAccessException(e);
-			}
-		}
-
-		return "";
-	}
-
-	/**
-	 * Method checks if exists user with input nick.
-	 *
-	 * @return true - nick already exists, false - otherwise
-	 */
-	public boolean isNickExists(String testNick){
-		return isExists("nick", testNick);
-	}
-
-	/**
-	 * Method checks if exists user with input e-mail.
-	 *
-	 * @return true - e-mail already exists, false otherwise
-	 */
-	public boolean isEmailExists(String testEmail){
-		return isExists("email", testEmail);
-	}
-
-	/**
-	 * Method checks if exists user whose attribute given by name exists with given value
-	 *
-	 * @return true - minimal one user exists, false - otherwise
-	 */
-	private boolean isExists(String name, String value){
-		String qy = "SELECT * FROM user WHERE LOWER(" + name + ") LIKE ? LIMIT 1";
-
-		try {
-			PreparedStatement pst = db.getPreparedStatement(qy, false);
-			pst.setString(1, value.toLowerCase());
-			ResultSet rs = db.executeQuery(pst);
-
-			if ( rs != null && rs.next() && rs.getInt("id") > 0) {
-				return true;
-			}
-		} catch (SQLException e) {
-			logger.error("Can not check if user exits: ", e);
-			throw new DataAccessException(e);
-		}
-
-		return false;
-	}
-}

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/user/Util.java

@@ -1 @@
-package cz.zcu.kiv.offscreen.user;
-
-import java.util.regex.Matcher;
-import java.util.regex.Pattern;
-
-class Util {
-
-
-	static String formatDate(String date){
-		String pattern = "(\\d{4})-(\\d{2})-(\\d{2}) (\\d{2}):(\\d{2}):(\\d{2}).*";
-		Pattern pat = Pattern.compile(pattern);
-		Matcher matches = pat.matcher(date);
-		String formatedDate = "";
-		
-		if(matches.find()){			
-			formatedDate = matches.group(3) + "." + matches.group(2) + "." +  matches.group(1) + " " + matches.group(4) + ":" +  matches.group(5);			
-		}
-		
-		return formatedDate;
-	}
-}

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/user/dao/DiagramDAO.java

@@ +1 @@
+package cz.zcu.kiv.offscreen.user.dao;
+
+import cz.zcu.kiv.offscreen.user.DataAccessException;
+import cz.zcu.kiv.offscreen.user.MyBatisUtil;
+import cz.zcu.kiv.offscreen.user.mapper.IDiagramMapper;
+import org.apache.ibatis.session.SqlSession;
+
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+public class DiagramDAO {
+
+    public Map<String, Object> getDiagram(int userId){
+
+        try (SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession()) {
+
+            IDiagramMapper mapper = session.getMapper(IDiagramMapper.class);
+            return mapper.getDiagram(userId);
+
+        } catch (Exception e) {
+            throw new DataAccessException(e);
+        }
+    }
+
+    public List<Map<String, Object>> getDiagramsByUserId(int userId){
+        try (SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession()) {
+            IDiagramMapper mapper = session.getMapper(IDiagramMapper.class);
+
+            return mapper.getDiagramsByUserId(userId);
+
+        } catch (Exception e) {
+            throw new DataAccessException(e);
+        }
+    }
+
+
+    public List<Map<String, Object>> getPublicDiagrams(){
+        try (SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession()) {
+            IDiagramMapper mapper = session.getMapper(IDiagramMapper.class);
+
+            return mapper.getPublicDiagrams();
+
+        } catch (Exception e) {
+            throw new DataAccessException(e);
+        }
+    }
+
+    public void deleteDiagram(int diagramId){
+        try (SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession()) {
+            IDiagramMapper mapper = session.getMapper(IDiagramMapper.class);
+
+            mapper.deleteDiagram(diagramId);
+            session.commit();
+
+        } catch (Exception e) {
+            throw new DataAccessException(e);
+        }
+    }
+
+    public int getDiagramUserId(int diagramId){
+        try (SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession()) {
+            IDiagramMapper mapper = session.getMapper(IDiagramMapper.class);
+
+            return mapper.getDiagramUserId(diagramId);
+
+        } catch (Exception e) {
+            throw new DataAccessException(e);
+        }
+    }
+
+    public int createDiagram(String name, String userId, String isPublic, String graphJson) {
+        try (SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession()) {
+            IDiagramMapper mapper = session.getMapper(IDiagramMapper.class);
+
+            Map<String, Object> id = new HashMap<>();
+            id.put("id", 0);
+
+            mapper.createDiagram(id, name, userId, isPublic, graphJson);
+            session.commit();
+
+            return (int)id.get("id");
+
+        } catch (Exception e) {
+            throw new DataAccessException(e);
+        }
+    }
+
+    public void updateDiagram(int diagramId, String name, String isPublic, String graphJson){
+        try (SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession()) {
+            IDiagramMapper mapper = session.getMapper(IDiagramMapper.class);
+
+            mapper.updateDiagram(diagramId, name, isPublic, graphJson);
+            session.commit();
+
+        } catch (Exception e) {
+            throw new DataAccessException(e);
+        }
+    }
+
+}

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/user/dao/UserDAO.java

@@ +1 @@
+package cz.zcu.kiv.offscreen.user.dao;
+
+import cz.zcu.kiv.offscreen.user.DataAccessException;
+import cz.zcu.kiv.offscreen.user.MyBatisUtil;
+import cz.zcu.kiv.offscreen.user.mapper.IUserMapper;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.ibatis.session.SqlSession;
+import org.springframework.security.crypto.bcrypt.BCrypt;
+
+import java.util.Map;
+
+public class UserDAO {
+
+    public Map<String, Object> login(String nick, String psw){
+        try (SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession()) {
+            IUserMapper mapper = session.getMapper(IUserMapper.class);
+            Map<String, Object> user = mapper.getUserByNick(nick);
+
+            if(user != null &&
+                    !user.isEmpty() &&
+                    (Integer)user.get("id") > 0 &&
+                    StringUtils.isNotBlank((String)user.get("psw")) &&
+                    BCrypt.checkpw(psw, (String)user.get("psw"))) {
+                return user;
+            }
+
+            return null;
+
+        } catch (Exception e) {
+            throw new DataAccessException(e);
+        }
+    }
+
+    public void register(String nick, String name, String password, String session, String email){
+        try (SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession()) {
+            IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
+
+            String hashPassword = BCrypt.hashpw(password, BCrypt.gensalt());
+            mapper.createUser(nick, name, hashPassword, session, email);
+            sqlSession.commit();
+
+        } catch (Exception e) {
+            throw new DataAccessException(e);
+        }
+    }
+
+    public boolean isEmailExists(String email){
+        try (SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession()) {
+            IUserMapper mapper = session.getMapper(IUserMapper.class);
+            Map<String, Object> user = mapper.getUserByEmail(email);
+
+            return user != null && !user.isEmpty();
+
+        } catch (Exception e) {
+            throw new DataAccessException(e);
+        }
+    }
+
+    public boolean isNickExists(String nick){
+        try (SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession()) {
+            IUserMapper mapper = session.getMapper(IUserMapper.class);
+            Map<String, Object> user = mapper.getUserByNick(nick);
+
+            return user != null && !user.isEmpty();
+
+        } catch (Exception e) {
+            throw new DataAccessException(e);
+        }
+    }
+}

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/user/mapper/IDiagramMapper.java

@@ +1 @@
+package cz.zcu.kiv.offscreen.user.mapper;
+
+import org.apache.ibatis.annotations.*;
+
+import java.util.List;
+import java.util.Map;
+
+public interface IDiagramMapper {
+
+    @Select("SELECT * FROM diagram WHERE id = #{id}")
+    Map<String, Object> getDiagram(int id);
+
+    @Select("SELECT id, name, created, last_update, user_id, public FROM diagram " +
+            "WHERE user_id = #{userId} ORDER BY created DESC")
+    List<Map<String, Object>> getDiagramsByUserId(int userId);
+
+    @Select("SELECT id, name, created, last_update, user_id, public FROM diagram " +
+            "WHERE public = 1 ORDER BY name ASC")
+    List<Map<String, Object>> getPublicDiagrams();
+
+    @Select("SELECT user_id FROM diagram WHERE id = #{id}")
+    int getDiagramUserId(int id);
+
+    @Delete("DELETE FROM diagram WHERE id = #{id}")
+    void deleteDiagram(int id);
+
+    @Insert("INSERT INTO diagram (name, created, last_update, user_id, public, graph_json) " +
+            "VALUES (#{name}, NOW(), NOW(), #{userId}, #{isPublic}, #{graphJson}) ")
+    @Options(useGeneratedKeys=true, keyColumn = "id", keyProperty="map.id")
+    void createDiagram(
+            @Param("map") Map<String, Object> map,
+            @Param("name") String name,
+            @Param("userId") String userId,
+            @Param("isPublic") String isPublic,
+            @Param("graphJson") String graphJson);
+
+    @Update("UPDATE diagram SET name=#{name}, public=#{isPublic}, graph_json=#{graphJson}, last_update=NOW() " +
+            "WHERE id=#{diagramId}")
+    void updateDiagram(
+            @Param("diagramId") int diagramId,
+            @Param("name") String name,
+            @Param("isPublic") String isPublic,
+            @Param("graphJson") String graphJson);
+}

sources/imiger-core/src/main/java/cz/zcu/kiv/offscreen/user/mapper/IUserMapper.java

@@ +1 @@
+package cz.zcu.kiv.offscreen.user.mapper;
+
+import org.apache.ibatis.annotations.Insert;
+import org.apache.ibatis.annotations.Param;
+import org.apache.ibatis.annotations.Select;
+
+import java.util.Map;
+
+public interface IUserMapper {
+
+    @Select("SELECT * FROM user WHERE nick LIKE #{nick} AND active = '1' LIMIT 1")
+    Map<String, Object> getUserByNick(String nick);
+
+    @Select("SELECT * FROM user WHERE email LIKE #{email} AND active = '1' LIMIT 1")
+    Map<String, Object> getUserByEmail(String email);
+
+    @Insert("INSERT INTO user (id, created, active, nick, name, psw, session, email) " +
+            "VALUES ( 0, NOW(), 1, #{nick}, #{name}, #{password}, #{session}, #{email})")
+    void createUser(
+            @Param("nick") String nick,
+            @Param("name") String name,
+            @Param("password") String password,
+            @Param("session") String session,
+            @Param("email") String email);
+}

sources/imiger-core/src/main/resources/mybatis-config.xml

@@ +1 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!DOCTYPE configuration
+        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
+        "http://mybatis.org/dtd/mybatis-3-config.dtd">
+
+<configuration>
+    <environments default="development">
+        <environment id="development">
+            <transactionManager type="JDBC"/>
+            <dataSource type="POOLED">
+                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
+                <property name="url" value="jdbc:mysql://localhost/visualization_tool?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC"/>
+                <property name="username" value="admin"/>
+                <property name="password" value="admin"/>
+            </dataSource>
+        </environment>
+    </environments>
+    <mappers>
+        <mapper class="cz.zcu.kiv.offscreen.user.mapper.IUserMapper"/>
+        <mapper class="cz.zcu.kiv.offscreen.user.mapper.IDiagramMapper"/>
+    </mappers>
+</configuration>