package eu.stork.documentservice.data; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import eu.stork.documentservice.exceptions.DatabaseException; import eu.stork.documentservice.exceptions.ModelException; import eu.stork.documentservice.model.DocumentModel; import eu.stork.documentservice.model.RequestModel; import eu.stork.documentservice.model.TempDocumentModel; public class DatabaseConnectorMySQLImpl implements DatabaseConnector { private String user; private String password; private String server; private String database; /** * The MYSQL connector constructor * @author sveinbjorno * @param _user the database user * @param _password the users password * @param _server the mysql server * @param _database the mysql database */ public DatabaseConnectorMySQLImpl(String _user, String _password, String _server, String _database) { user = _user; password = _password; server = _server; database = _database; } /* (non-Javadoc) * @see eu.stork.documentservice.data.DatabaseConnector#addDocument(eu.stork.documentservice.model.DocumentModel) */ @Override public boolean addDocument(DocumentModel document) throws DatabaseException { boolean successful = false; Connection con = null; PreparedStatement pst = null; try { document.insertValidate(); String url = "jdbc:mysql://" + server + "/" + database; con = DriverManager.getConnection(url, user, password); pst = con.prepareStatement("INSERT INTO document(DOCID, DOCUMENT, FILENAME, MIMETYPE, CREATED, RECEIVERCERT, " + "ENCKEY, ENCIV) VALUES(?, ?, ?, ?, ?, ?, ?, ?)"); pst.setString(1, document.getDocid()); pst.setBlob(2, document.getDataStream()); pst.setString(3, document.getFilename()); pst.setString(4, document.getMimetype()); pst.setTimestamp(5, DatabaseHelper.getSqlCurrentDate()); pst.setString(6, document.getReicevercert()); pst.setString(7, document.getEnckey()); pst.setString(8, document.getEnciv()); pst.executeUpdate(); successful = true; } catch (SQLException ex) { throw new DatabaseException(ex); } catch (ModelException mex) { throw new DatabaseException(mex); } finally { try { if (pst != null) { pst.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { throw new DatabaseException(ex); } } return successful; } /* (non-Javadoc) * @see eu.stork.documentservice.data.DatabaseConnector#updateDocument(eu.stork.documentservice.model.DocumentModel) */ @Override public boolean updateDocument(DocumentModel document) throws DatabaseException { boolean successful = false; Connection con = null; PreparedStatement pst = null; try { document.updateValidate(); String url = "jdbc:mysql://" + server + "/" + database; con = DriverManager.getConnection(url, user, password); pst = con.prepareStatement("update document set DOCUMENT = ?, FILENAME = ?, MIMETYPE = ?, UPDATED = ?, " + "RECEIVERCERT = ?, ENCKEY = ?, ENCIV = ? where DOCID = ?"); pst.setBlob(1, document.getDataStream()); pst.setString(2, document.getFilename()); pst.setString(3, document.getMimetype()); pst.setTimestamp(4, DatabaseHelper.getSqlCurrentDate()); pst.setString(5, document.getReicevercert()); pst.setString(6, document.getEnckey()); pst.setString(7, document.getEnciv()); pst.setString(8, document.getDocid()); pst.executeUpdate(); successful = true; } catch (SQLException ex) { throw new DatabaseException(ex); } catch (ModelException mex) { throw new DatabaseException(mex); } finally { try { if (pst != null) { pst.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { throw new DatabaseException(ex); } } return successful; } /* (non-Javadoc) * @see eu.stork.documentservice.data.DatabaseConnector#getDocument(java.lang.String) */ @Override public DocumentModel getDocument(String docId) throws DatabaseException { DocumentModel document = null; Connection con = null; PreparedStatement pst = null; try { if (docId == null || docId.isEmpty()) throw new DatabaseException("Document ID is null or empty."); String url = "jdbc:mysql://" + server + "/" + database; con = DriverManager.getConnection(url, user, password); String commandString = "select DOCUMENT, FILENAME, MIMETYPE, CREATED, UPDATED, DELETED, RECEIVERCERT, ENCKEY, ENCIV " + "from document where DOCID = ?"; pst = con.prepareStatement(commandString); pst.setString(1, docId); ResultSet set = pst.executeQuery(); if (set.next()) { document = new DocumentModel(); document.setDocid(docId); document.setDataStream(set.getBinaryStream(1)); document.setFilename(set.getString(2)); document.setMimetype(set.getString(3)); document.setCreated(DatabaseHelper.getUtilDate(set.getTimestamp(4))); document.setUpdated(DatabaseHelper.getUtilDate(set.getTimestamp(5))); document.setDeleted(DatabaseHelper.getUtilDate(set.getTimestamp(6))); document.setReicevercert(set.getString(7)); document.setEnckey(set.getString(8)); document.setEnciv(set.getString(9)); } } catch (ModelException mex) { throw new DatabaseException(mex); } catch (SQLException ex) { throw new DatabaseException(ex); } finally { try { if (pst != null) { pst.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { throw new DatabaseException(ex); } } return document; } /* (non-Javadoc) * @see eu.stork.documentservice.data.DatabaseConnector#deleteDocument(java.lang.String) */ @Override public boolean deleteDocument(String docId) throws DatabaseException { boolean successful = false; Connection con = null; PreparedStatement pst = null; try { if (docId == null || docId.isEmpty()) throw new DatabaseException("Document ID is null or empty."); String url = "jdbc:mysql://" + server + "/" + database; con = DriverManager.getConnection(url, user, password); String commandString = "update document set document = null, deleted = ? where DOCID = ?"; pst = con.prepareStatement(commandString); pst.setTimestamp(1, DatabaseHelper.getSqlCurrentDate()); pst.setString(2, docId); pst.executeUpdate(); successful = true; } catch (SQLException ex) { throw new DatabaseException(ex); } finally { try { if (pst != null) { pst.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { throw new DatabaseException(ex); } } return successful; } /* (non-Javadoc) * @see eu.stork.documentservice.data.DatabaseConnector#addRequest(eu.stork.documentservice.model.RequestModel) */ @Override public boolean addRequest(RequestModel request) throws DatabaseException { boolean successful = false; Connection con = null; PreparedStatement pst = null; try { request.insertValidate(); String url = "jdbc:mysql://" + server + "/" + database; con = DriverManager.getConnection(url, user, password); pst = con.prepareStatement("INSERT INTO request(REQUESTID, DOCID, DESTCOUNTRY, SPCOUNTRY, " + "SPID, XMLREQUEST, REQTIMESTAMP) VALUES(?, ?, ?, ?, ?, ?, ?)"); pst.setString(1, request.getRequestid()); pst.setString(2, request.getDocid()); pst.setString(3, request.getDestcountry()); pst.setString(4, request.getSpcountry()); pst.setString(5, request.getSpid()); pst.setString(6, request.getXmlrequest()); pst.setTimestamp(7, DatabaseHelper.getSqlCurrentDate()); pst.executeUpdate(); successful = true; } catch (ModelException mex) { throw new DatabaseException(mex); } catch (SQLException ex) { throw new DatabaseException(ex); } finally { try { if (pst != null) { pst.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { throw new DatabaseException(ex); } } return successful; } /* (non-Javadoc) * @see eu.stork.documentservice.data.DatabaseConnector#getRequest(java.lang.String) */ @Override public RequestModel getRequest(String requestId) throws DatabaseException { RequestModel request = null; Connection con = null; PreparedStatement pst = null; try { if (requestId == null || requestId.isEmpty()) throw new DatabaseException("Request ID is null or empty."); String url = "jdbc:mysql://" + server + "/" + database; con = DriverManager.getConnection(url, user, password); String commandString = "select DOCID, DESTCOUNTRY, SPCOUNTRY, SPID, XMLREQUEST, XMLRESPONSE, REQTIMESTAMP, " + "RESTIMESTAMP from request where REQUESTID = ?"; pst = con.prepareStatement(commandString); pst.setString(1, requestId); ResultSet set = pst.executeQuery(); if (set.next()) { request = new RequestModel(); request.setRequestid(requestId); request.setDocid(set.getString(1)); request.setDestcountry(set.getString(2)); request.setSpcountry(set.getString(3)); request.setSpid(set.getString(4)); request.setXmlrequest(set.getString(5)); request.setXmlresponse(set.getString(6)); request.setReqtimestamp(DatabaseHelper.getUtilDate(set.getTimestamp(7))); request.setRestimestamp(DatabaseHelper.getSqlDate(set.getTimestamp(8))); } } catch (SQLException ex) { throw new DatabaseException(ex); } finally { try { if (pst != null) { pst.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { throw new DatabaseException(ex); } } return request; } /* (non-Javadoc) * @see eu.stork.documentservice.data.DatabaseConnector#getRequestByDocId(java.lang.String) */ @Override public RequestModel getRequestByDocId(String docId) throws DatabaseException { RequestModel request = null; Connection con = null; PreparedStatement pst = null; try { if (docId == null || docId.isEmpty()) throw new DatabaseException("Document ID is null or empty."); String url = "jdbc:mysql://" + server + "/" + database; con = DriverManager.getConnection(url, user, password); String commandString = "select REQUESTID, DESTCOUNTRY, SPCOUNTRY, SPID, XMLREQUEST, XMLRESPONSE, REQTIMESTAMP, " + "RESTIMESTAMP from request where DOCID = ?"; pst = con.prepareStatement(commandString); pst.setString(1, docId); ResultSet set = pst.executeQuery(); if (set.next()) { request = new RequestModel(); request.setDocid(docId); request.setRequestid(set.getString(1)); request.setDestcountry(set.getString(2)); request.setSpcountry(set.getString(3)); request.setSpid(set.getString(4)); request.setXmlrequest(set.getString(5)); request.setXmlresponse(set.getString(6)); request.setReqtimestamp(DatabaseHelper.getUtilDate(set.getTimestamp(7))); request.setRestimestamp(DatabaseHelper.getSqlDate(set.getTimestamp(8))); } } catch (SQLException ex) { throw new DatabaseException(ex); } finally { try { if (pst != null) { pst.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { throw new DatabaseException(ex); } } return request; } /* (non-Javadoc) * @see eu.stork.documentservice.data.DatabaseConnector#updateRequest(eu.stork.documentservice.model.RequestModel) */ @Override public boolean updateRequest(RequestModel request) throws DatabaseException { boolean successful = false; Connection con = null; PreparedStatement pst = null; try { request.updateValidate(); String url = "jdbc:mysql://" + server + "/" + database; con = DriverManager.getConnection(url, user, password); pst = con.prepareStatement("update request set DOCID = ?, DESTCOUNTRY = ?, SPCOUNTRY = ?, SPID = ?, " + "XMLREQUEST = ?, XMLRESPONSE = ?, RESTIMESTAMP = ? where REQUESTID = ?"); pst.setString(1, request.getDocid()); pst.setString(2, request.getDestcountry()); pst.setString(3, request.getSpcountry()); pst.setString(4, request.getSpid()); pst.setString(5, request.getXmlrequest()); pst.setString(6, request.getXmlresponse()); pst.setTimestamp(7, DatabaseHelper.getSqlDate(request.getRestimestamp())); pst.setString(8, request.getRequestid()); pst.executeUpdate(); successful = true; } catch (ModelException mex) { throw new DatabaseException(mex); } catch (SQLException ex) { throw new DatabaseException(ex); } finally { try { if (pst != null) { pst.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { throw new DatabaseException(ex); } } return successful; } @Override public boolean addTempDocument(TempDocumentModel document) throws DatabaseException { boolean successful = false; Connection con = null; PreparedStatement pst = null; try { document.insertValidate(); String url = "jdbc:mysql://" + server + "/" + database; con = DriverManager.getConnection(url, user, password); pst = con.prepareStatement("INSERT INTO temp_document(DOCID, DOCUMENT, MIMETYPE, CREATED, RECEIVERCERT, " + "ENCKEY, ENCIV, SPID) VALUES(?, ?, ?, ?, ?, ?, ?, (SELECT ID FROM spid where spid = ?))"); pst.setString(1, document.getDocid()); pst.setBlob(2, document.getDataStream()); pst.setString(3, document.getMimetype()); pst.setTimestamp(4, DatabaseHelper.getSqlCurrentDate()); pst.setString(5, document.getReicevercert()); pst.setString(6, document.getEnckey()); pst.setString(7, document.getEnciv()); pst.setString(8, document.getSpid()); pst.executeUpdate(); successful = true; } catch (SQLException ex) { throw new DatabaseException(ex); } catch (ModelException mex) { throw new DatabaseException(mex); } finally { try { if (pst != null) { pst.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { throw new DatabaseException(ex); } } return successful; } @Override public TempDocumentModel getTempDocument(String docId) throws DatabaseException { TempDocumentModel document = null; Connection con = null; PreparedStatement pst = null; try { if (docId == null || docId.isEmpty()) throw new DatabaseException("Document ID is null or empty."); String url = "jdbc:mysql://" + server + "/" + database; con = DriverManager.getConnection(url, user, password); String commandString = "select temp.DOCUMENT, temp.MIMETYPE, temp.CREATED, temp.FETCHED, temp.DELETED, " + "temp.RECEIVERCERT, temp.ENCKEY, temp.ENCIV, sp.SPID " + "from temp_document temp inner join spid sp on sp.ID = temp.SPID where DOCID = ?"; pst = con.prepareStatement(commandString); pst.setString(1, docId); ResultSet set = pst.executeQuery(); if (set.next()) { document = new TempDocumentModel(); document.setDocid(docId); document.setDataStream(set.getBinaryStream(1)); document.setMimetype(set.getString(2)); document.setCreated(DatabaseHelper.getUtilDate(set.getTimestamp(3))); document.setFetched(DatabaseHelper.getUtilDate(set.getTimestamp(4))); document.setDeleted(DatabaseHelper.getUtilDate(set.getTimestamp(5))); document.setReicevercert(set.getString(6)); document.setEnckey(set.getString(7)); document.setEnciv(set.getString(8)); document.setSpid(set.getString(9)); } } catch (ModelException mex) { throw new DatabaseException(mex); } catch (SQLException ex) { throw new DatabaseException(ex); } finally { try { if (pst != null) { pst.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { throw new DatabaseException(ex); } } return document; } @Override public boolean updateTempDocument(TempDocumentModel document) throws DatabaseException { boolean successful = false; Connection con = null; PreparedStatement pst = null; try { document.updateValidate(); String url = "jdbc:mysql://" + server + "/" + database; con = DriverManager.getConnection(url, user, password); pst = con.prepareStatement("update temp_document set DOCUMENT = ?, MIMETYPE = ?, FETCHED = ?, " + "RECEIVERCERT = ?, ENCKEY = ?, ENCIV = ? where DOCID = ?"); pst.setBlob(1, document.getDataStream()); pst.setString(2, document.getMimetype()); pst.setTimestamp(3, DatabaseHelper.getSqlCurrentDate()); pst.setString(4, document.getReicevercert()); pst.setString(5, document.getEnckey()); pst.setString(6, document.getEnciv()); pst.setString(7, document.getDocid()); pst.executeUpdate(); successful = true; } catch (SQLException ex) { throw new DatabaseException(ex); } catch (ModelException mex) { throw new DatabaseException(mex); } finally { try { if (pst != null) { pst.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { throw new DatabaseException(ex); } } return successful; } @Override public boolean deleteTempDocument(String docId) throws DatabaseException { boolean successful = false; Connection con = null; PreparedStatement pst = null; try { if (docId == null || docId.isEmpty()) throw new DatabaseException("Document ID is null or empty."); String url = "jdbc:mysql://" + server + "/" + database; con = DriverManager.getConnection(url, user, password); String commandString = "update temp_document set document = null, deleted = ? where DOCID = ?"; pst = con.prepareStatement(commandString); pst.setTimestamp(1, DatabaseHelper.getSqlCurrentDate()); pst.setString(2, docId); pst.executeUpdate(); successful = true; } catch (SQLException ex) { throw new DatabaseException(ex); } finally { try { if (pst != null) { pst.close(); } if (con != null) { con.close(); } } catch (SQLException ex) { throw new DatabaseException(ex); } } return successful; } }