dbmanager.java

旅游自助系统

/**
 * 
 */
package org.tshs.storage.rdbms;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.sql.Date;
import java.util.List;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.tshs.core.CacheManager;
import org.tshs.core.CfgManager;
import org.tshs.core.Constant.ObjectType;
import org.tshs.exception.DBTableFullException;

/**
 * @author Administrator
 *
 */
public class DbManager {

	private static boolean initialized;

	/**
	 * Initialize the DatabaseManager.
	 */
	public static void initialize() throws SQLException {
		if (initialized) {
			return;
		}
		// Register basic JDBC driver
		Class driverClass = null;
		try {
			driverClass = Class.forName(CfgManager.getProperty("db.driver"));
			Driver basicDriver = (Driver) driverClass.newInstance();
			DriverManager.registerDriver(basicDriver);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		// Read pool configuration parameter or use defaults
		int maxConnections = CfgManager.getIntProperty("db.maxconnections");

		if (CfgManager.getProperty("db.maxconnections") == null) {
			maxConnections = 30;
		}

		int maxWait = CfgManager.getIntProperty("db.maxwait");

		if (CfgManager.getProperty("db.maxwait") == null) {
			maxWait = 5000;
		}

		int maxIdle = CfgManager.getIntProperty("db.maxidle");

		if (CfgManager.getProperty("db.maxidle") == null) {
			maxIdle = 0;
		}

		ObjectPool connectionPool = new GenericObjectPool(
				null, // PoolableObjectFactory
				maxConnections, // max connections
				GenericObjectPool.WHEN_EXHAUSTED_BLOCK, 
				maxWait, 
				maxIdle, 
				true, // validate when we borrow connections from pool
				false // don't bother validation returned connections
		);

		// ConnectionFactory the pool will use to create connections.
		ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
				CfgManager.getProperty("db.url"), 
				CfgManager.getProperty("db.username"), 
				CfgManager.getProperty("db.password")
				);

		// Now we'll create the PoolableConnectionFactory, which wraps
		// the "real" Connections created by the ConnectionFactory with
		// the classes that implement the pooling functionality.
		String validationQuery = "SELECT 1;";

		new PoolableConnectionFactory(
				connectionFactory, 
				connectionPool, 
				null, 
				validationQuery, // validation query
				false, // read only is not default for now
				false // Autocommit defaults to none
		);

		PoolingDriver driver = new PoolingDriver();
		driver.registerPool("tshspool", connectionPool);

		initialized = true;
	}

	/**
	 * The entry of all the query related method.
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public static TableRowIterator query(String sql) throws SQLException {
		Connection connection = getConnection();
		Statement statement = connection.createStatement();
		
		ResultSet res = statement.executeQuery(sql);
		TableRowIterator iterator = new TableRowIterator(res, statement);
		
		return iterator;
	}

	/**
	 * Query the database and get a single or none result.
	 * 
	 * @param table store the result's source
	 * @param sql
	 * @return
	 * @throws Exception 
	 */
	public static TableRow querySingle(String table, String sql) throws Exception {
		
		TableRowIterator iterator = query(sql);
		
		TableRow row = null;
		if(iterator.hasNext()){
			row = iterator.next();
			addRow(table, row);
		}
		
		iterator.close();
		
		return row;
	}

	/**
	 * @param table
	 * @param row
	 * @throws Exception 
	 */
	public static void addRow(String table, TableRow row) throws Exception {

		row.setTableName(table);
		if("client".equals(table)){			
			List groups = new ArrayList();
			TableRowIterator groupIds = query("select groupId from group2client where clientId=" + row.getColumn("id"));
			while(groupIds.hasNext()){
				TableRow trow = groupIds.next();
				Long groupid = Long.valueOf(trow.getColumn("groupId").toString());
				groups.add(groupid);
			}
			row.addColumn("groupIds", "groupIds", groups);
		}else if("travelgroup".equals(table)){	
			List<Long> sights = new ArrayList<Long>();
			TableRowIterator sightspotIds = query("select sightspotId from sightspot2group where groupId=" + row.getColumn("id"));
			while(sightspotIds.hasNext()){
				TableRow trow = sightspotIds.next();
				Long sightid = Long.valueOf(trow.getColumn("sightspotId").toString());
				sights.add(sightid);
			}
			row.addColumn("sightIds", "sightIds", sights);	//sightIds	
			List<Long> clients = new ArrayList<Long>();
			TableRowIterator clientIds = query("select clientId from group2client where groupId=" + row.getColumn("id"));
			while(clientIds.hasNext()){
				clients.add(Long.valueOf(clientIds.next().getColumn("clientId").toString()));
			}
			row.addColumn("clientIds", "clientIds", clients);
		}else if("travelcorp".equals(table)){			
			List groups = new ArrayList();
			TableRowIterator groupIds = query("select id from travelgroup where corpId=" + row.getColumn("id"));
			while(groupIds.hasNext()){
				TableRow trow = groupIds.next();
				Long groupid = Long.valueOf(trow.getColumn("id").toString());
				groups.add(groupid);
			}
			row.addColumn("groupIds", "groupIds", groups);	
		}else if("traveldept".equals(table)){			
					
		}else if("sightspot".equals(table)){			
			List groups = new ArrayList();
			TableRowIterator groupIds = query("select groupId from sightspot2group where sightspotId=" + row.getColumn("id"));
			while(groupIds.hasNext()){
				TableRow trow = groupIds.next();
				Long groupid = Long.valueOf(trow.getColumn("groupId").toString());
				groups.add(groupid);
			}
			row.addColumn("groupIds", "groupIds", groups);		
		}else{
			//test
			System.out.println(table);
		}
	}

	/**
	 * Query the database by the unique column.
	 * 
	 * @param table
	 * @param column
	 * @param value
	 * @return
	 * @throws Exception 
	 */
	public static TableRow queryByUnique(String table, String column, String value) throws Exception {
		
		String sql = "select * from "+ table +" where "+ column +" = '"+ value +"';"; 

		return querySingle(table, sql);
	}
	
	public static int insert(TableRow row) throws SQLException{
		
		String table = row.getTableName();
		if(row == null || row.getTableName() == null){
			return 0;
		}
		
		ColumnInfo columnInfo = row.getColumnInfo();
		
		StringBuffer sqlHeader = new StringBuffer("insert into " + table + "(");
		StringBuffer sqlTail = new StringBuffer(") values(");
		
		int size = columnInfo.getColumnSize();
		for(int i = 0; i < size; i++){
			if(i != 0){
				sqlHeader.append(",");
				sqlTail.append(",");
			}
			sqlHeader.append(columnInfo.getName(i));
			sqlTail.append("?");
		}		
		sqlTail.append(")");
		
		String sql = sqlHeader.append(sqlTail).toString();
		
		// test
		System.out.println(sql);
		
		return execute(sql, row, columnInfo);
	}
	
	public static int update(TableRow row) throws SQLException{

		String table = row.getTableName();
		if(row == null || row.getTableName() == null){
			return 0;
		}
		
		ColumnInfo columnInfo = row.getColumnInfo();
		
		StringBuffer sql = new StringBuffer("update " + table + " set ");
		
		int size = columnInfo.getColumnSize();
		for(int i = 0; i < size; i++){
			if(i != 0){
				sql.append(",");
			}
			sql.append(columnInfo.getName(i) + "=?");
		}		
		sql.append(" where id=" + row.getColumn("id") + ";");
		
		// test
		System.out.println(sql);
		
		return execute(sql.toString(), row, columnInfo);
	}
	
	public static int delete(TableRow row) throws SQLException{
		
		String table = row.getTableName();