/**
* Copyright (c) 2009 Juwi MacMillan Group GmbH
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
/*
* Created on 12.05.2005
*/
package de.juwimm.cms.search.xmldb.test;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.Locale;
import java.util.Properties;
import java.util.TimeZone;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;
import org.junit.Ignore;
import de.juwimm.cms.search.vo.XmlSearchValue;
/**
* Test for SQL-Server 2005
*
* @author <a href="mailto:carsten.schalm@juwimm.com">Carsten Schalm</a>
* company Juwi|MacMillan Group Gmbh, Walsrode, Germany
* @version $Id$
*/
@Ignore
public final class TestSqlServer {
private static Logger log = Logger.getLogger(TestSqlServer.class);
private Connection connection = null;
private static final SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss.SSS", Locale.GERMAN);
private TestSqlServer() {
}
public static void main(String[] args) {
PropertyConfigurator.configure("log4j.properties");
TestSqlServer main = new TestSqlServer();
main.execute();
}
private void execute() {
try {
this.connection = this.connect();
XmlSearchValue[] results = this.searchXml(1, "//content");
if (results != null && results.length > 0) {
for (int i = (results.length - 1); i >= 0; i--) {
log.info(results[i].getViewComponentId().toString() + ": " + results[i].getText());
}
}
} catch (Exception e) {
log.error("Error: " + e.getMessage(), e);
} finally {
try {
if (this.connection != null) {
this.connection.close();
this.connection = null;
}
} catch (SQLException e) {
log.error(e.getMessage());
}
}
}
private Connection connect() {
try {
Properties properties = new Properties();
properties.put("user", "cms");
properties.put("password", "cms");
String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String connectionUrl = "jdbc:sqlserver://192.168.30.129:1433;databaseName=cms";
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(connectionUrl, properties);
DatabaseMetaData dmd = connection.getMetaData();
StringBuilder sb = new StringBuilder();
sb.append("\nConnection Details");
sb.append("\n==================");
sb.append("\nConnection URL : " + dmd.getURL());
sb.append("\nDB Name : " + dmd.getDatabaseProductName());
sb.append("\nDB Version : " + dmd.getDatabaseMajorVersion() + "." + dmd.getDatabaseMinorVersion());
sb.append("\nDB Version Name: " + dmd.getDatabaseProductVersion());
sb.append("\nDriver Name : " + dmd.getDriverName());
sb.append("\nDriver Version : " + dmd.getDriverVersion());
sb.append("\nUsername : " + dmd.getUserName());
sb.append("\n-------------------------------------");
log.info(sb.toString());
return connection;
} catch (ClassNotFoundException e) {
log.error("Class not found: " + e.getMessage());
} catch (SQLException e) {
log.error(e.getMessage());
}
return null;
}
public static String calcHMS(long timeInSeconds) {
SimpleDateFormat dateFormat = new SimpleDateFormat("HH:mm:ss.SSS");
dateFormat.setTimeZone(TimeZone.getTimeZone("GMT"));
return (dateFormat.format(new java.util.Date(timeInSeconds)));
}
public synchronized XmlSearchValue[] searchXml(Integer siteId, String xpathQuery) {
if (log.isDebugEnabled()) log.debug("searchXML(...) -> begin at " + sdf.format(new Date()));
String select = "SELECT xdb.VIEW_COMPONENT_ID, xdb.UNIT_ID, xdb.INFO_TEXT, xdb.TEXT, xdb.CONTENT FROM XML_SEARCH_DB xdb WHERE xdb.SITE_ID = ? AND xdb.CONTENT.exist('" + xpathQuery + "') = 1";
XmlSearchValue[] retArray = null;
LinkedList<XmlSearchValue> tmpList = new LinkedList<XmlSearchValue>();
PreparedStatement pstmt = null;
try {
pstmt = this.connection.prepareStatement(select);
pstmt.setInt(1, siteId.intValue());
ResultSet qResult = pstmt.executeQuery();
while (qResult.next()) {
XmlSearchValue newEntry = new XmlSearchValue();
newEntry.setViewComponentId(qResult.getInt(1));
newEntry.setUnitId(qResult.getInt(2));
newEntry.setInfoText(qResult.getString(3));
newEntry.setText(qResult.getString(4));
newEntry.setContent(qResult.getString(5));
tmpList.add(newEntry);
}
retArray = tmpList.toArray(new XmlSearchValue[0]);
} catch (SQLException sqle) {
log.error("searchXML(...) -> failed to excecute query for xpathQuery = \"" + xpathQuery + "\": " + sqle.getMessage());
} catch (Exception e) {
log.error("searchXML(...) -> unkown exception occured " + e.getMessage());
} finally {
try {
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
log.error("searchXML(...) -> error closing pstmt " + e.getMessage());
}
}
if (log.isDebugEnabled()) log.debug("searchXML(...) -> end at " + sdf.format(new Date()));
return retArray;
}
}