/*
 * Created on 2006-5-15
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.bookstore.db;

import java.io.*;
import java.sql.*;
import java.util.*;

/**
 * @author zhanghong
 * 
 */
public class DBSource
{
	private Connection	g_C_Connection;

	private Statement	g_C_Statement;

	private String		g_S_Driver;

	private String		g_S_Url;

	private String		g_S_Username;

	private String		g_S_Password;

	private String		g_S_PropFileName;

	private boolean		g_b_UseFile;

	private String		m_PoolName;

	public DBSource()
	{
		g_C_Connection = null;
		g_C_Statement = null;
		g_S_Driver = null;
		g_S_Url = null;
		g_S_Username = null;
		g_S_Password = null;
		g_S_PropFileName = "db.properties";
		g_b_UseFile = true;
		m_PoolName = "";
	}

	public void Init(String strPoolName)
	{
		m_PoolName = strPoolName;
	}

	public void setG_S_PropFileName(String l_S_FileName)
	{
		g_S_PropFileName = l_S_FileName;
	}

	private void connect()
	{
		try
		{
			if (g_C_Connection == null || g_C_Connection.isClosed())
			{
				loadPropties();
				Class.forName(g_S_Driver);
				g_C_Connection = DriverManager.getConnection(g_S_Url, g_S_Username, g_S_Password);
				g_C_Statement = g_C_Connection.createStatement();
			}
			if (g_C_Statement == null)
				g_C_Statement = g_C_Connection.createStatement();
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}
	}

	public void loadPropties()
	{
		if (g_b_UseFile)
		{
			InputStream is = getClass().getResourceAsStream(g_S_PropFileName);
			Properties g_Properties_Db = new Properties();
			try
			{
				g_Properties_Db.load(is);
				g_S_Driver = g_Properties_Db.getProperty(m_PoolName + "Driver");
				g_S_Url = g_Properties_Db.getProperty(m_PoolName + "Url");
				g_S_Username = g_Properties_Db.getProperty(m_PoolName + "Username");
				g_S_Password = g_Properties_Db.getProperty(m_PoolName + "Password");
				is.close();
			}
			catch (IOException ex)
			{
				ex.printStackTrace();
			}
		}
	}

	public Connection getConnection()
	{
		connect();
		return g_C_Connection;
	}

	public Statement getStatement() throws SQLException
	{
		connect();
		return g_C_Connection.createStatement();
	}

	public void setMaxrow(int l_i_MaxRowNum) throws SQLException
	{
		connect();
		g_C_Statement.setMaxRows(l_i_MaxRowNum);
	}

	public ResultSet executeQuery(String l_S_Sql) throws SQLException
	{
		connect();
		
		return g_C_Statement.executeQuery(l_S_Sql);
	}

	public ResultSet executeQueryFromNew(String l_S_Sql) throws SQLException
	{
		connect();
		return getStatement().executeQuery(l_S_Sql);
	}

	public boolean execute(String sql) throws SQLException
	{
		connect();
		return g_C_Statement.execute(sql);
	}

	public void clear() throws SQLException
	{
		if (g_C_Connection != null && !g_C_Connection.isClosed())
		{
			g_C_Statement.close();
			g_C_Connection.close();
		}
	}

	public void close() throws SQLException
	{
		clear();
	}

	public String getUrl()
	{
		return this.g_S_Url;
	}

	public String getUsername()
	{
		return this.g_S_Username;
	}

	public String getPassword()
	{
		return this.g_S_Password;
	}

	public Vector ListOfMapData(String sql) throws SQLException
	{
		Vector vector = new Vector();
		String ColName;
		connect();
		ResultSet rs = executeQuery(sql);

		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();
		while (rs.next())
		{
			HashMap map = new HashMap(count);
			for (int i = 0; i < count; i++)
			{
				ColName = rsmd.getColumnName(i + 1);
				map.put(ColName, rs.getString(i + 1));
			}
			vector.add(map);
		}
		return vector;
	}

	public boolean InsertData(String TableName, Map map) throws SQLException
	{
		String sql, sql1, sql2, Value;
		String ColName;
		int ColType = 0;
		connect();

		ResultSet rs = this.executeQuery("select * from " + TableName + " where 1=0");
		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();

		sql1 = "INSERT INTO " + TableName + "(";
		sql2 = " VALUES(";

		for (int i = 0; i < count; i++)
		{
			ColType = rsmd.getColumnType(i + 1);
			if (rsmd.isAutoIncrement(i + 1))
				continue;
			if (Types.BINARY == ColType)
			{
				if (16 == rsmd.getColumnDisplaySize(i + 1))
					ColType = Types.VARCHAR;
			}
			if (Types.DATE == ColType || Types.TIME == ColType || Types.TIMESTAMP == ColType)
			{
				ColType = Types.VARCHAR;
			}

			ColName = (String) rsmd.getColumnName(i + 1);
			sql1 += ColName;
			Value = (String) map.get(ColName);
			if ((ColType == Types.VARCHAR || ColType == Types.CHAR) && Value != null)
				sql2 += "'" + Value + "'";
			else
				sql2 += Value;
			if (i != count - 1)
			{
				sql1 += ",";
				sql2 += ",";
			}
		}
		sql1 += ")";
		sql2 += ")";
		sql = sql1 + sql2;

		rs.close();

		return execute(sql);
	}

	public boolean UpdateData(String TableName, Map map, String KeyName) throws SQLException
	{
		String sql, sql1, Value;
		String ColName;
		int KeyType = 0, colType = 0;
		connect();

		ResultSet rs = this.executeQuery("select * from " + TableName + " where 1=0");
		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();

		sql = "UPDATE " + TableName + " SET ";
		sql1 = "";
		for (int i = 0; i < count; i++)
		{
			ColName = rsmd.getColumnName(i + 1);
			Value = (String) map.get(ColName);
			if (Value != null)
			{
				colType = rsmd.getColumnType(i + 1);
				if (ColName.equals(KeyName))
				{
					KeyType = colType;
					if (Types.BINARY == KeyType)
					{
						if (16 == rsmd.getColumnDisplaySize(i + 1))
							KeyType = Types.VARCHAR;
					}
				}
				else
				{
					if (rsmd.isAutoIncrement(i + 1))
						continue;
					if (sql1.equals(""))
						sql1 += ColName + "=";
					else
						sql1 += "," + ColName + "=";
					if (Types.BINARY == colType)
					{
						if (16 == rsmd.getColumnDisplaySize(i + 1))
							colType = Types.VARCHAR;
					}
					if (Types.DATE == colType || Types.TIME == colType || Types.TIMESTAMP == colType)
					{
						colType = Types.VARCHAR;
					}

					if (colType == Types.VARCHAR || colType == Types.CHAR)
						sql1 += "'" + Value + "'";
					else
						sql1 += Value;
				}
			}
		}
		sql += sql1;
		sql += " WHERE " + KeyName + "=";
		Value = (String) map.get(KeyName);
		if ((KeyType == Types.VARCHAR || KeyType == Types.CHAR) && Value != null)
			sql += "'" + Value + "'";
		else
			sql += Value;

		rs.close();

		return execute(sql);
	}

	public void flush() throws SQLException
	{
		g_C_Connection.commit();
	}
}