diff options
Diffstat (limited to 'DocumentService/src/eu/stork/documentservice/data/DatabaseConnectorMySQLImpl.java')
-rw-r--r-- | DocumentService/src/eu/stork/documentservice/data/DatabaseConnectorMySQLImpl.java | 713 |
1 files changed, 713 insertions, 0 deletions
diff --git a/DocumentService/src/eu/stork/documentservice/data/DatabaseConnectorMySQLImpl.java b/DocumentService/src/eu/stork/documentservice/data/DatabaseConnectorMySQLImpl.java new file mode 100644 index 000000000..bdabc55bf --- /dev/null +++ b/DocumentService/src/eu/stork/documentservice/data/DatabaseConnectorMySQLImpl.java @@ -0,0 +1,713 @@ +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; + } +} |