package com.vst.service.impl;
import com.vst.dao.*;
import com.vst.exceptions.ServerDataUnloadException;
import com.vst.model.*;
import com.vst.service.SynchronizationManager;
import com.vst.util.BeanUtils;
import com.vst.util.db2sql;
import org.apache.commons.dbcp.BasicDataSource;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.cfg.Configuration;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.io.IOException;
/**
* Created by IntelliJ IDEA.
* User: ALEXEI
* Date: 01.04.2008
* Time: 1:03:27
* To change this template use File | Settings | File Templates.
*/
public class SynchronizationManagerImpl implements SynchronizationManager {
private BuildingObjectDao buildingObjectDao;
private ConstructionExampleDao constructionExampleDao;
private ObjectConstructionDao objectConstructionDao;
private ConstructionDefectDao constructionDefectDao;
private StrengthDao strengthDao;
private ObjectPhotoDao objectPhotoDao;
public ObjectPhotoDao getObjectPhotoDao() {
return objectPhotoDao;
}
public void setObjectPhotoDao(ObjectPhotoDao objectPhotoDao) {
this.objectPhotoDao = objectPhotoDao;
}
public void setBuildingObjectDao(BuildingObjectDao buildingObjectDao) {
this.buildingObjectDao = buildingObjectDao;
}
public void setConstructionExampleDao(ConstructionExampleDao constructionExampleDao) {
this.constructionExampleDao = constructionExampleDao;
}
public void setObjectConstructionDao(ObjectConstructionDao objectConstructionDao) {
this.objectConstructionDao = objectConstructionDao;
}
public void setConstructionDefectDao(ConstructionDefectDao constructionDefectDao) {
this.constructionDefectDao = constructionDefectDao;
}
public void setStrengthDao(StrengthDao strengthDao) {
this.strengthDao = strengthDao;
}//names of the tables we neeed to take from server
static final String hexDigitChars = "0123456789abcdef";
private final static String[] spravochnikTables = {"user_role", "role", "regions", "reasons", "reasonNmaes", "reasonDefects", "questions", "questionTypes", "questionHintRelations", "questionConstructionRelations", "parameters", "objectTypes", "measures", "materials", "materialClassifications", "hints", "documentTypes", "defectZones", "defectZoneRealtions", "defectVariaties", "defectTypes", "defectTypeVarityRelation", "defectRecomendations", "defectParameterRelations", "defectMaterialRelations", "defectCategories", "dangerCategories", "defectCategoryNames", "defectAuthentificationElements", "constructionTypes", "categoryParameters", "authentificationElements", "answers", "app_user","defectTypeConstructionTypeRelation"};
private final static String[] researchTables = {"objectBuilding", "objectConstructions", "objectPhotoes", "reasonDefects", "strengthPoints", " answerObjectRelations", "constructionDefects", "constructionExamples", "objectAnswers"};
private void deleteLocalObjects(HttpServletRequest request, HttpServletResponse response) throws ServerDataUnloadException {
String deleteQuery = "";
for (int i = 0; i < researchTables.length; i++) {
deleteQuery += "DELETE FROM " + researchTables[i] + ";;;\n";
}
if (!db2sql.execDump(deleteQuery, null, request,response)) {
throw new ServerDataUnloadException("Возникла ошибка при удалении объектов обследования с клиента!");
}
}
private boolean isSpravochnikTable(String tableName) {
for (int i = 0; i < spravochnikTables.length; i++) {
if (spravochnikTables[i].equalsIgnoreCase(tableName)) {
return true;
}
}
return false;
}
private boolean isResearchTable(String tableName) {
for (int i = 0; i < researchTables.length; i++) {
if (researchTables[i].equals(tableName)) {
return true;
}
}
return false;
}
public boolean hasConnection(String address, HttpServletRequest request) throws IOException {
//getting new data from server to the client
Properties props = new Properties();
BasicDataSource basicDataSource= (BasicDataSource) BeanUtils.getBean("dataSource",request);
props.setProperty("driver.class",basicDataSource.getDriverClassName());
props.setProperty("user",basicDataSource.getUsername());
props.setProperty("password",basicDataSource.getPassword());
String driverClassName = basicDataSource.getDriverClassName();
String driverURL = basicDataSource.getUrl().replaceFirst("localhost",address);
//String driverURL = "jdbc:mysql://" + address + "/vstbase";
// Default to not having a quote character
/*todo String columnNameQuote == "" ??? */
String columnNameQuote = ""; //= props.getProperty("columnName.quoteChar", "");
DatabaseMetaData dbMetaData = null;
Connection dbConn = null;
try {
Class.forName(driverClassName);
dbConn = DriverManager.getConnection(driverURL, props);
dbMetaData = dbConn.getMetaData();
}
catch (Exception e) {
return false;
}
return true;
}
public void synchronizeSpravochnikFromServer(String address, HttpServletRequest request, HttpServletResponse response) throws ServerDataUnloadException, IOException {
//INSERTING RESEARCH DATA ON THE SERVER
//creating back up of previous client database version
db2sql.makeDump(request);
//System.out.println("resertve copy made");
// creating dump on the client
//creating new hibernate configuration
BasicDataSource basicDataSource= (BasicDataSource) BeanUtils.getBean("dataSource",request);
String driverURL = basicDataSource.getUrl().replaceFirst("localhost",address);
// String driverURL = "jdbc:mysql://" + address + "/vstbase";
Configuration cfg = new Configuration()
.addClass(Answer.class)
.addClass(AuthentificationElement.class)
.addClass(BuildingObject.class)
.addClass(CategoryParameter.class)
.addClass(ConstructionDefect.class)
.addClass(ConstructionExample.class)
.addClass(ConstructionType.class)
.addClass(DangerCategory.class)
.addClass(DefectCategory.class)
.addClass(DefectCategoryName.class)
.addClass(DefectParameter.class)
.addClass(DefectRecomendation.class)
.addClass(DefectType.class)
.addClass(DefectVarity.class)
.addClass(DefectZone.class)
.addClass(DocumentType.class)
.addClass(Hint.class)
.addClass(Material.class)
.addClass(MaterialClassification.class)
.addClass(Measure.class)
.addClass(ObjectAnswer.class)
.addClass(ObjectConstruction.class)
.addClass(ObjectPhoto.class)
.addClass(ObjectType.class)
.addClass(Parameter.class)
.addClass(Question.class)
.addClass(QuestionType.class)
.addClass(Reason.class)
.addClass(ReasonName.class)
.addClass(Role.class)
.addClass(Strength.class)
.addClass(User.class)
.addClass(DefectAuthentification.class)
.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQLInnoDBDialect")
.setProperty("hibernate.connection.url", driverURL )
.setProperty("hibernate.order_updates", "true")
.setProperty("hibernate.connection.username", "root")
.setProperty("hibernate.connection.password", "root")
.setProperty("hibernate.connection.pool_size", "10")
.setProperty("hibernate.connection.driver_class", "com.mysql.jdbc.Driver");
Session session = cfg.buildSessionFactory().openSession();
//retrieving object list from database
List buildingObjectList = buildingObjectDao.getBuildingObjects(null);
//making all objects new
try {
for (int i = 0; i < buildingObjectList.size(); i++) {
BuildingObject buildingObject = (BuildingObject) buildingObjectList.get(i);
buildingObjectDao.evict(buildingObject);
List objectQuestions = buildingObject.getDocumentationQuestions();
List ObjectPhotoes = buildingObject.getPhotoes();
List objectConstructionList = buildingObject.getConstructionTypes();
buildingObject.setObjectId(null);
session.save(buildingObject);
session.evict(buildingObject);
buildingObject = (BuildingObject) session.load(BuildingObject.class, buildingObject.getObjectId());
List buildingAnswerList = new ArrayList();
for (int j = 0; j < objectQuestions.size(); j++) {
ObjectAnswer objectAnswer = (ObjectAnswer) objectQuestions.get(j);
List answerList = objectAnswer.getAnswers();
objectAnswer.setObjectAnswerId(null);
objectAnswer.setBuildingObjectId(buildingObject.getObjectId());
// objectAnswer.setBuildingObject(buildingObject);
session.save(objectAnswer);
objectAnswer = (ObjectAnswer) session.load(ObjectAnswer.class, objectAnswer.getObjectAnswerId());
// objectAnswer.getAnswers().clear();
List newAnswerList = new ArrayList();
for (int k = 0; k < answerList.size(); k++) {
Answer answer = (Answer) answerList.get(k);
newAnswerList.add(answer);
}
objectAnswer.setObjectAnswerPosition(new Integer(j));
session.save(objectAnswer);
objectAnswer.getAnswers().clear();
for (int k = 0; k < newAnswerList.size(); k++) {
Answer answer = (Answer) newAnswerList.get(k);
session.evict(answer);
answer = (Answer) session.load(Answer.class, answer.getAnswerId());
objectAnswer.getAnswers().add(answer);
objectAnswer.setObjectAnswerPosition(new Integer(j));
session.save(objectAnswer);
}
session.flush();
buildingAnswerList.add(objectAnswer);
}
buildingObject.setDocumentationQuestions(buildingAnswerList);
List newObjectPhotoes = new ArrayList();
for (int j = 0; j < ObjectPhotoes.size(); j++) {
ObjectPhoto objectPhoto = (ObjectPhoto) ObjectPhotoes.get(j);
objectPhoto.setPhotoId(null);
objectPhoto.setObjectId(buildingObject.getObjectId());
objectPhoto.setPhotoPosition(new Integer(j));
session.save(objectPhoto);
newObjectPhotoes.add(objectPhoto);
}
// buildingObject.getPhotoes().clear();
buildingObject.getPhotoes().clear();
for (int j = 0; j < newObjectPhotoes.size(); j++) {
ObjectPhoto objectPhoto = (ObjectPhoto) newObjectPhotoes.get(j);
buildingObject.getPhotoes().add(objectPhoto);
}
// session.save(buildingObject);
// session.evict(buildingObject);
// buildingObject = (BuildingObject) session.load(BuildingObject.class, buildingObject.getObjectId());
List newObjectConstructionList = new ArrayList();
for (int j = 0; j < objectConstructionList.size(); j++) {
ObjectConstruction objectConstruction = (ObjectConstruction) objectConstructionList.get(j);
newObjectConstructionList.add(objectConstruction);
objectConstruction.setTypeId(null);
objectConstruction.setObjectId(buildingObject.getObjectId());
objectConstruction.setTypePosition(new Integer(j));
List constructionExamples = objectConstruction.getConstructionExamples();
List constructionQuestions = objectConstruction.getDocumentationQuestions();
List newDocumentationQuestions = new ArrayList();
session.save(objectConstruction);
List newConstructionExamples = new ArrayList();
session.evict(objectConstruction);
objectConstruction = (ObjectConstruction) session.load(ObjectConstruction.class, objectConstruction.getTypeId());
for (int k = 0; k < constructionQuestions.size(); k++) {
ObjectAnswer objectAnswer = (ObjectAnswer) constructionQuestions.get(k);
List answerList = objectAnswer.getAnswers();
// session.evict(objectAnswer);
objectAnswer.setObjectAnswerId(null);
objectAnswer.setBuildingObject(null);
objectAnswer.setBuildingObjectId(null);
objectAnswer.setObjectConstructionId(objectConstruction.getTypeId());
objectAnswer.setObjectAnswerPosition(new Integer(k));
session.save(objectAnswer);
objectAnswer = (ObjectAnswer) session.load(ObjectAnswer.class, objectAnswer.getObjectAnswerId());
List newAnswerList = new ArrayList();
for (int l = 0; l < answerList.size(); l++) {
Answer answer = (Answer) answerList.get(l);
newAnswerList.add(answer);
}
objectAnswer.getAnswers().clear();
for (int l = 0; l < newAnswerList.size(); l++) {
Answer answer = (Answer) newAnswerList.get(l);
answer = (Answer) session.load(Answer.class, answer.getAnswerId());
objectAnswer.getAnswers().add(answer);
}
objectAnswer.setObjectAnswerPosition(new Integer(k));
session.save(objectAnswer);
// session.flush();
// newDocumentationQuestions.add(objectAnswer);
}
// objectConstruction.setDocumentationQuestions(newDocumentationQuestions);
// session.evict(objectConstruction);
for (int k = 0; k < constructionExamples.size(); k++) {
ConstructionExample constructionExample = (ConstructionExample) constructionExamples.get(k);
constructionExample.setExampleId(null);
constructionExample.setExamplePosition(new Integer(k));
newConstructionExamples.add(constructionExample);
// constructionExample.setc(new Integer(k));
constructionExample.setBuildObjectId(buildingObject.getObjectId());
constructionExample.setObjectId(buildingObject.getObjectId());
constructionExample.setObjectConstructionId(objectConstruction.getTypeId());
List strengthPointList = constructionExample.getStrengthPoints();
List constructionDefectList = constructionExample.getExampleDefects();
session.save(constructionExample);
// constructionExample = (ConstructionExample) session.load(ConstructionExample.class, constructionExample.getExampleId());
for (int l = 0; l < constructionDefectList.size(); l++) {
ConstructionDefect constructionDefect = (ConstructionDefect) constructionDefectList.get(l);
session.evict(constructionDefect);
constructionDefect.setConstructionDefectId(null);
constructionDefect.setExampleId(constructionExample.getExampleId());
constructionDefect.setConstructionDefectPosition(new Integer(l));
List defectParameters = constructionDefect.getDefectParameters();
session.save(constructionDefect);
// session.flush();
// session.evict(constructionDefect);
constructionDefect = (ConstructionDefect) session.load(ConstructionDefect.class, constructionDefect.getConstructionDefectId());
for (int m = 0; m < defectParameters.size(); m++) {
DefectParameter defectParameter = (DefectParameter) defectParameters.get(m);
session.evict(defectParameter);
defectParameter.setDefectParameterId(null);
defectParameter.setConstructionDefectId(constructionDefect.getConstructionDefectId());
defectParameter.setDefectParameterPosition(new Integer(m));
session.save(defectParameter);
}
}
for (int l = 0; l < strengthPointList.size(); l++) {
Strength strength = (Strength) strengthPointList.get(l);
session.evict(strength);
strength.setPointId(null);
strength.setExampleId(constructionExample.getExampleId());
strength.setPointPosition(new Integer(l));
session.save(strength);
}
session.save(constructionExample);
}
// objectConstruction.getConstructionExamples().clear();
// objectConstruction.setConstructionExamples(newConstructionExamples);
// session.save(objectConstruction);
}
// buildingObject.getConstructionTypes().clear();
// buildingObject.setConstructionTypes(newObjectConstructionList);
// session.save(buildingObject);
}
//System.out.println("Objects moved to the server!");
} catch (HibernateException e) {
e.printStackTrace();
throw new ServerDataUnloadException("Возникла ошибка при при переброске объектов обследования с клиента на сервер!");
}
// removing all objects from base
deleteLocalObjects(request,response);
//System.out.println("Local objects deleted");
//getting new data from server to the client
Properties props = new Properties();
props.setProperty("driver.class",basicDataSource.getDriverClassName());
props.setProperty("user",basicDataSource.getUsername());
props.setProperty("password",basicDataSource.getPassword());
String driverClassName = props.getProperty("driver.class");
//driverURL = "jdbc:mysql://" + address + "/vstbase";
driverURL = basicDataSource.getUrl().replaceFirst("localhost",address);
// Default to not having a quote character
String columnNameQuote = props.getProperty("columnName.quoteChar", "");
DatabaseMetaData dbMetaData = null;
Connection dbConn = null;
try {
Class.forName(driverClassName);
//System.out.println("driverURL=" + driverURL);
dbConn = DriverManager.getConnection(driverURL, props);
dbMetaData = dbConn.getMetaData();
}
catch (Exception e) {
e.printStackTrace();
throw new ServerDataUnloadException("Возникла ошибка при установлении соединения с базой данных сервера!");
}
StringBuffer result = new StringBuffer();
try {
String catalog = props.getProperty("catalog");
String schema = props.getProperty("schemaPattern");
String tables = props.getProperty("tableName");
ResultSet rs = dbMetaData.getTables(catalog, schema, tables, null);
if (!rs.next()) {
System.err.println("Unable to find any tables matching: catalog=" + catalog + " schema=" + schema + " tables=" + tables);
rs.close();
} else {
do {
String tableName = rs.getString("TABLE_NAME");
String tableType = rs.getString("TABLE_TYPE");
//System.out.println("tableName=====" + tableName);
/*TODO this is a dump of the tables structure/ It's not needed in this application
*/
//dump the data to the local db if it's the table we need
if (isSpravochnikTable(tableName)) {
//creating local connection
// BasicDataSource basicDataSource= (BasicDataSource) BeanUtils.getBean("dataSource");
// String driverLocalURL = props.getProperty("driver.url");
String driverLocalURL = basicDataSource.getUrl();
Connection dbLocalConn = null;
try {
Class.forName(driverClassName);
dbLocalConn = DriverManager.getConnection(driverLocalURL, props);
} catch (ClassNotFoundException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
throw new ServerDataUnloadException("Не удалось найти файл настройки для базы данных на клиенте!");
}
try {
dumpTable(dbConn, result, tableName, true);
} catch (SQLException e) {
throw new ServerDataUnloadException("Ошибка при получении данных с серврера от таблицы - "+tableName);
}
}
} while (rs.next());
rs.close();
}
dbConn.close();
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use Options | File Templates.
throw new ServerDataUnloadException("Произошла ошибка при получении справочников из базы сервера!");
}
//System.out.println("Spravochniks received from server");
//System.out.println(result.toString());
//executing the dump on the localhost db
if (!db2sql.execDump(result.toString(), null,request,response)) {
throw new ServerDataUnloadException("Произошла ошибка при передачи справочников в базу клиента");
}
// fileName = "sql.properties";
// props = new Properties();
// try {
// props.load(new FileInputStream(fileName));
// }
// catch (Exception e) {
// e.printStackTrace();
// }
// driverClassName = props.getProperty("driver.class");
// driverURL = "jdbc:mysql://localhost/vstbase";
// // Default to not having a quote character
// columnNameQuote = props.getProperty("columnName.quoteChar", "");
// dbMetaData = null;
// dbConn = null;
// try {
// Class.forName(driverClassName);
// dbConn = DriverManager.getConnection(driverURL, props);
// dbMetaData = dbConn.getMetaData();
// }
// catch (Exception e) {
// throw new Exception("Unable to create connection!");
//
// }
// result = new StringBuffer();
// try {
//
// String catalog = props.getProperty("catalog");
// String schema = props.getProperty("schemaPattern");
// String tables = props.getProperty("tableName");
// ResultSet rs = dbMetaData.getTables(catalog, schema, tables, null);
// if (!rs.next()) {
// System.err.println("Unable to find any tables matching: catalog=" + catalog + " schema=" + schema + " tables=" + tables);
// rs.close();
// } else {
//
// do {
// String tableName = rs.getString("TABLE_NAME");
// String tableType = rs.getString("TABLE_TYPE");
///*TODO this is a dump of the tables structure/ It's not needed in this application
// */
// //dump the data to the local db if it's the table we need
// if (isResearchTable(tableName)) {
// //creating local connection
// String driverLocalURL = props.getProperty("driver.url");
// Connection dbLocalConn = null;
// try {
// Class.forName(driverClassName);
// dbLocalConn = DriverManager.getConnection(driverLocalURL, props);
// } catch (ClassNotFoundException e) {
// e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
// }
// dumpTable(dbLocalConn, result, tableName, false);
// }
// } while (rs.next());
// rs.close();
// }
// dbConn.close();
// } catch (SQLException e) {
// e.printStackTrace(); //To change body of catch statement use Options | File Templates.
// }
//
////inserting the dump to the server
// BasicDataSource basicDataSource= (BasicDataSource) BeanUtils.getBean("dataSource");
// String driverURL = basicDataSource.getUrl().replaceFirst("localhost",address);
// /*String driverURL = "jdbc:mysql://" + address + "/vstbase"; */
// db2sql.execDump(result.toString(), driverURL);
//System.out.println("Sinchronization was finished");
}
public static String byteArrayToHex(byte[] a) {
int hn, ln, cx;
StringBuffer buf = new StringBuffer(a.length * 2);
for (cx = 0; cx < a.length; cx++) {
hn = ((int) (a[cx]) & 0x00ff) / 16;
ln = ((int) (a[cx]) & 0x000f);
buf.append(hexDigitChars.charAt(hn));
buf.append(hexDigitChars.charAt(ln));
// buf.append('');
}
return buf.toString();
}
private static void dumpTable(Connection dbConn, StringBuffer result, String tableName, boolean needsDeleting) throws SQLException {
try {
// First we output the create table stuff
PreparedStatement stmt = dbConn.prepareStatement("SELECT * FROM " + tableName);
ResultSet rs = stmt.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
// Now we can output the actual data
if (needsDeleting) {
result.append("\nDELETE from " + tableName + ";;;\n");
}
while (rs.next()) {
result.append("INSERT INTO " + tableName + " VALUES (");
for (int i = 0; i < columnCount; i++) {
if (i > 0) {
result.append(", ");
}
Object value = rs.getObject(i + 1);
if (value == null) {
result.append("NULL");
} else {
if (value.equals(new Boolean(true))) {
value = String.valueOf(1);
}
if (value.equals(new Boolean(false))) {
value = String.valueOf(0);
}
String outputValue = "";
if (value instanceof byte[]) {
byte[] blobBytes = (byte[]) value;
result.append("0x" + byteArrayToHex(blobBytes));
// result.append("'");
} else {
outputValue = value.toString();
outputValue = outputValue.replaceAll("'", "\\'");
result.append("'" + outputValue + "'");
}
}
}
result.append(");;;\n");
}
rs.close();
stmt.close();
} catch (SQLException e) {
System.err.println("Unable to dump table " + tableName + " because: " + e);
throw new SQLException();
}
}
}