queryheaderservice.java

来自「管理公司合同」· Java 代码 · 共 225 行

/*
 * Created on 2006-11-11 12:05:12
 *
 * By Yehailong
 * Copyright juneng.com.cn, 2005-2006, All rights reserved.
 */
package cn.com.juneng.system.service;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.com.juneng.system.common.COMMON;
import cn.com.juneng.system.common.SpringBeanFactory;
import cn.com.juneng.system.common.util.SimpleDAO;
import cn.com.juneng.system.dao.QueryHeaderDAO;
import cn.com.juneng.system.vo.DataSourceVOImpl;
import cn.com.juneng.system.vo.QueryHeaderVOImpl;

/**
 * service类，由Spring容器注入DAO实现，并管理事务性
 * 
 * @author yehailong
 */
public class QueryHeaderService {
	private QueryHeaderDAO queryHeaderDAO;

	/**
	 * spring注入dao实现
	 * 
	 * @param queryHeaderDAO
	 */
	public void setQueryHeaderDAO(QueryHeaderDAO queryHeaderDAO) {
		this.queryHeaderDAO = queryHeaderDAO;
	}

	public QueryHeaderDAO getQueryHeaderDAO() {
		return this.queryHeaderDAO;
	}

	public void remove(Serializable[] key) throws Exception {
		if (key != null) {
			for (int i = 0; i < key.length; i++) {
				queryHeaderDAO.remove(key[i]);
			}
		}
	}

	public QueryHeaderVOImpl findByPrimaryKey(String key) throws Exception {
		return queryHeaderDAO.findByPrimaryKey(key);
	}

	public void create(Object obj) throws Exception {
		queryHeaderDAO.create(obj);
	}

	public void update(Object obj) throws Exception {
		queryHeaderDAO.update(obj);
	}

	public List getList(String queryHql, String orderHql, int start,
			int maxResults) throws Exception {
		String hql = "from QueryHeaderVOImpl ";
		if (!COMMON.isEmpty(queryHql)) {
			hql += "where " + queryHql;
		}
		if (COMMON.isEmpty(orderHql)) {
			orderHql = " order by queryId desc";
		}
		hql += " " + orderHql;

		return queryHeaderDAO.find(hql, start, maxResults);
	}

	public int getRowCount(String queryHql) {
		String hql = "from QueryHeaderVOImpl ";
		if (!COMMON.isEmpty(queryHql)) {
			hql += "where " + queryHql;
		}
		return queryHeaderDAO.getRowCount(hql, null);
	}

	/**
	 * 获取连接，未实现连接池，有性能问题
	 */
	private Connection getConnection(DataSourceVOImpl ds)
			throws Exception {
		String sourceHost = ds.getSourceHost();
		String dbName = ds.getDbName();
		String userName = ds.getUserName();
		String password = ds.getPassword();
		Connection connection = null;
		String url = "";
		String dbType = ds.getDbType();
		if (dbType.equals("1")) {
			// 连接sql server
			Class.forName("com.ddtek.jdbc.sqlserver.SQLServerDriver");
			url = "jdbc:datadirect:sqlserver://" + sourceHost
					+ ":1433;SelectMethod=curror;DatabaseName=" + dbName;
			connection = DriverManager.getConnection(url, userName, password);
		} else if (dbType.equals("2")) {
			// 连接ORACEL
			Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
			url = "jdbc:oracle:thin:@" + sourceHost + ":1521:" + dbName;
			connection = DriverManager.getConnection(url, userName, password);
		} else if (dbType.equals("3")) {
			// 连接DB2
			Class.forName("com.ibm.db2.jdbc.app.DB2Driver ").newInstance();
			url = "jdbc:db2://" + sourceHost + ":5000/" + dbName;
			connection = DriverManager.getConnection(url, userName, password);
		} else if (dbType.equals("4")) {
			// 连接Informix
			Class.forName("com.informix.jdbc.IfxDriver").newInstance();
			url = "jdbc:informix-sqli://" + sourceHost + ":1533/" + dbName
					+ ":INFORMIXSERVER=myserver;user=" + userName
					+ ";password=" + password;
			connection = DriverManager.getConnection(url);
		} else if (dbType.equals("5")) {
			// 连接Sybase
			Class.forName("com.sybase.jdbc.SybDriver").newInstance();
			url = " jdbc:sybase:Tds:" + sourceHost + ":5007/" + dbName;
			java.util.Properties sysProps = System.getProperties();
			sysProps.put("user", userName);
			sysProps.put("password", password);
			connection = DriverManager.getConnection(url, sysProps);
		} else if (dbType.equals("6")) {
			// 连接MySQL
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
			url = "jdbc:mysql://" + sourceHost + "/" + dbName + "?user="
					+ userName + "&password=" + password
					+ "&useUnicode=true&characterEncoding=8859_1";
			connection = DriverManager.getConnection(url);
		} else if (dbType.equals("7")) {
			// 连接PostgreSQL
			Class.forName("org.postgresql.Driver").newInstance();
			url = "jdbc:postgresql://" + sourceHost + "/" + dbName;
			connection = DriverManager.getConnection(url, userName, password);

		}

		return connection;
	}

	private void closeConnection(Connection connection)
			throws SQLException {
		if (connection != null) {
			connection.close();
		}
	}

	public List queryBySql(QueryHeaderVOImpl query, String querySql)
			throws SQLException, Exception {
		List rowList = new ArrayList();
		String queryType = query.getQueryType();// 数据源
		DataSourceService dsService = (DataSourceService) SpringBeanFactory
				.getBean("dataSourceService");
		DataSourceVOImpl ds = dsService.findByPrimaryKey(queryType);
		String sourceType = ds.getSourceType();

		// Session session = null;
		Connection connection = null;
		try {
			if (sourceType.equals("1")) {
				connection = SimpleDAO.getSqlConnection();
			} else {
				connection = getConnection(ds);
			}

			rowList = queryBySql(connection, querySql);
		} finally {
			closeConnection(connection);
		}

		return rowList;
	}

	private List queryBySql(Connection connection, String querySql)
			throws  Exception {
		System.out.println(querySql);
		List rowList = new ArrayList();
		List colList = null;
		List typeList = new ArrayList();

		Statement stmt = connection.createStatement();
		ResultSet rset = stmt.executeQuery(querySql);
		ResultSetMetaData rsMetaData = rset.getMetaData();
		int columnCount = rsMetaData.getColumnCount();

		colList = new ArrayList();
		for (int i = 1; i <= columnCount; i++) {
			typeList.add(rsMetaData.getColumnClassName(i));
			colList.add(rsMetaData.getColumnName(i));
		}
		rowList.add(typeList);
		rowList.add(colList);
		while (rset != null && rset.next()) {
			colList = new ArrayList();
			for (int i = 1; i <= columnCount; i++) {
				colList.add(rset.getString(i) == null ? "" : rset.getString(i));
			}
			rowList.add(colList);
		}

		rset.close();
		stmt.close();

		return rowList;
	}
	
	public QueryHeaderVOImpl findByQueryIndex(String queryIndex)throws Exception{
		String hql = "from QueryHeaderVOImpl where queryIndex = '"+queryIndex+"'";
		List list = this.getQueryHeaderDAO().getHibernateTemplate().find(hql);
		if(list.size()!=0){
			return (QueryHeaderVOImpl)list.get(0);
		}
		return null;
	}
}