clientfactory.java

SQL Plus是一款通用数据库操纵、存取程序

package com.ora.db;

import com.ora.db.Client;
import java.sql.*;
import java.util.*;
/**
 * Class ClientFactory <br>
 * 实现数据库客户端事务与创建。
 * @author 杨连宝
 * @version 1.1
 */
public class ClientFactory implements Client {

	private String  jdbc = Client.DB_JDBC;
	private String  url  = Client.DB_URL;
	private String  user = Client.DB_USER;
	private String  pass = Client.DB_PASS;
	private HashMap hm   = new HashMap();
	
	/** the unique Connection of db*/
	private static Connection conn = null;
	/**
	 * Method newInstanse() <br>
	 * 取得一个Client实例
	 */
	public static Client newInstanse()
	{
		return (Client)new ClientFactory();
	}
	/**
	 * private Construct <br>
	 */
	private ClientFactory()
	{
		try{
			connectDB(); // Connect to default db
		}catch(SQLException e)
		{
			e.printStackTrace();
			log("Can not connect to the default DB. \nConnect Failed.");
		}
		catch(ClassNotFoundException se)
		{
			log("Can not found the jdbc driver. \nConnect Failed.");
		}
	}
	/**
	 * Method isClosed()
	 * <br>
	 * judge is the connection closed.
	 * 
	 */
	private void isClosed() throws SQLException
	{
		if(conn == null)
			throw new SQLException("The Connection To the DB is NULL.");
		if(conn.isClosed())
			throw new SQLException("The Connection To the DB is Closed.");
	}
	/**
	 * Method version <br>
	 * implements <br>
	 * print the SQL Client version information
	 */
	public void version()
	{
		log("\nThe Basic JDBC extends Client.\nVersion:1.0\n");
	}
	/**
	 * Method setAutoCommit
	 *
	 * <br>jump the autocommit parameter of the connection
	 */
	public void setAutoCommit()
	{		
		try{
			isClosed();
			conn.setAutoCommit(!conn.getAutoCommit());
			log("\nNow the auto Commit is: " + conn.getAutoCommit() + ".\n");
		}catch(Exception e)
		{
			log("\nSet the autocommit failed.\n" + e.getMessage() + "\n");
		}
	}
	/**
	 * Method connectDB
	 *
	 *
	 @throws SQLException
	 @throws ClassNotFoundException
	 *
	 */
	public void connectDB() throws SQLException, ClassNotFoundException {
		// TODO: Add your code here
		this.jdbc = Client.DB_JDBC;
		this.url  = Client.DB_URL;
		this.user = Client.DB_USER;
		this.pass = Client.DB_PASS;
		connect();
	}

	/**
	 * Method connectDB
	 *
	 *
	 * @param jdbc  JDBC driver class
	 * @param url  the url to the db
	 * @param user login username
	 * @param pass login password
	 *
	 @throws SQLException
	 @throws ClassNotFoundException
	 *
	 */
	public void connectDB(String jdbc, String url, String user, String pass) throws SQLException, ClassNotFoundException {
		// TODO: Add your code here
		this.jdbc = jdbc;
		this.url  = url;
		this.user = user;
		this.pass = pass;
		connect();
	}

	/**
	 * Method closeDB
	 * <br>
	 * Close the connection to the db
	 @throws SQLException
	 *
	 */
	public void closeDB() throws SQLException {
		// TODO: Add your code here
		if(ClientFactory.conn != null && !ClientFactory.conn.isClosed())
		    ClientFactory.conn.close();
		else
			throw new SQLException("The Connection is Closed.");
		log("\r\nThe Connect Closed Success.\r\n You must restart by 'connect' to resume the connection.\r\n");
	}

	/**
	 * Method execSQL
	 *
	 *
	 * @param sql
	 *
	 @throws SQLException
	 *
	 */
	public void execSQL(String sql){
		// TODO: Add your code here
		try{
			isClosed();
			if(sql == null)
			{
				System.out.println("\r\nPlease input SQL statement.\r\n");
				return;
			}
			// proccess the command sql
			String cmd = sql.toLowerCase().trim();
			if(cmd.startsWith("desc"))
			{
				// print the table structure
				desc(sql);
			}
			else if(cmd.startsWith("select") || cmd.startsWith("show status"))
			{
				// select qurry
				select(sql);
			}
			else if(cmd.startsWith("commit"))
			{
				// commit
				commit();
			}
			else if(cmd.startsWith("savepoint"))
			{
				if("savepoint".equals(cmd))
				{
					savepoint(null);
				}
				else
				{
					String sname = cmd.substring(cmd.indexOf(" ")).trim();
					savepoint(sname);
				}				
			}
			else if(cmd.startsWith("rollback"))
			{				
				if("rollback".equals(cmd))
				{
					savepoint(null);
				}
				else
				{
					String sname = cmd.substring(cmd.indexOf(" ")).trim();
					rollback(sname);
				}
			}
			else
			{
				// Execute the sql
				execsql(sql);
			}
		}catch(Exception e)
		{
			//e.printStackTrace();
			log("\r\nExesql Failed: " + e.getMessage() + "\r\nSQL:" + sql + "\r\n\r\n");
		}
	}
		
	/**
     * @param sql
     */
    private void call(String sql) {
        String sqld = sql;
        CallableStatement ca = null;
        try {
            ca = conn.prepareCall(sqld);
            ca.execute();
            System.out.println(sqld + "Call OK!");
        } catch (SQLException e) {
            
        } finally {
            if(ca != null)
                try {
                    ca.close();
                } catch (SQLException e1) {
                    
                }
        }
    }
    /** Commit*/
	private void commit()throws SQLException
	{
		isClosed();
		
		if(!conn.getAutoCommit())
		{
			conn.commit();
			log("\r\n数据库提交成功。\r\n");
		}else
		{
			throw new SQLException("\r\n目前数据库不用手动提交，因为连接是自动提交的。\r\n");
		}
	}
	/**
	 * method select
	 * method execsql
	 */
	private void select(String sql)throws SQLException
	{
		// check the connection
		isClosed();
		
		// show the top num record
		long output = 100;
		if(sql.indexOf("-topnum ") != -1)
		{
			try{
				output = Long.parseLong(sql.substring(sql.indexOf("-topnum ") + 8).trim());
			}catch(Exception e)
			{
				output = -1;
			}
			sql = sql.substring(0, sql.indexOf("-topnum ")).trim();
		}
		java.sql.PreparedStatement ps = conn.prepareStatement(sql);
        java.sql.ResultSet rs = ps.executeQuery(); //取得当前记录的最大值
        java.sql.ResultSetMetaData rmd = rs.getMetaData(); // table headers
        try{
	        int[] ichars = new int[rmd.getColumnCount()]; // data length
	        int[] nchars = new int[rmd.getColumnCount()]; // name length
	        // Print Header
	        for(int i = 1; i <= rmd.getColumnCount(); i++)
	        {
	        	String cname = rmd.getColumnName(i);
	        	int    chars = rmd.getColumnDisplaySize(i);
	        	String ntype = rmd.getColumnTypeName(i);
	        	if(chars > 4000)chars = 4000;
	        	if("DATE".equals(ntype))chars = 25; // date
	        	int   icname = cname.toCharArray().length; // char width
	        	ichars[i-1] = chars;
	        	nchars[i-1] = icname;
	        	if(icname < chars)
	        	{
	        		System.out.print(cname);
	        		for(int j = 0; j < chars - icname; j++)
	        			System.out.print(" ");
	        	}
	        	System.out.print(" ");
	        }
	        System.out.print("\r\n");
	        // Print ----
	        for(int i = 1; i <= rmd.getColumnCount(); i++)
	        {
	        	if(nchars[i-1] < ichars[i-1])
	        	{
	        		for(int j = 0; j < ichars[i-1]; j++)
	        			System.out.print("-");
	        	}else
	        	{
	        		for(int j = 0; j < nchars[i-1]; j++)
	        			System.out.print("-");
	        	}
	        	System.out.print(" ");
	        }
	        System.out.print("\r\n\r\n");
	        // Print datas
	        long count = 0; // 总的有多少条记录被选择
	        while(rs.next())
	        {
	        	if(output != -1 && count >= output)break;
	        	for(int i = 1; i <= rmd.getColumnCount(); i++)
		        {
		        	String ntype = rmd.getColumnTypeName(i);
		        	String cname = null;//rs.getString(i);
		        	try{
		        		//if("DATE".equals(ntype))cname = ((java.util.Date)rs.getDate(i)).toLocaleString(); // date
		        	    cname = rs.getString(i);		        	    
		        	}catch(Exception e){
		        	}
		        	cname = cname == null? " " : cname.trim();
		        	int    chars = rmd.getColumnDisplaySize(i);
		        	if(chars > 4000)chars = 4000;
		        	if("DATE".equals(ntype) || "datetime".equals(ntype))chars = 25;
		        	int   icname = 0;
		        	
		        	try{
		        		icname = cname.toCharArray().length; // char width
		        	}catch(Exception e){icname = 0;}
		        	ichars[i-1] = chars;
		        	nchars[i-1] = icname;
		        	if(icname <= chars)
		        	{
		        		System.out.print(cname);
		        		for(int j = 0; j < chars - icname; j++)
		        			System.out.print(" ");
		        	}else{
		        	    System.out.print(cname);
		        	}
		        	System.out.print(" ");
		        }
		        count++;	        
		        System.out.print("\r\n");	        
	        }
	        // print the select info
			System.out.print("\r\n---------------------\r\n" + count + " Records Selected.\r\nSelect OK!\r\n\r\n");
		}catch(SQLException e)
		{
			throw e;
		}finally{
			// close the resultset and preparestatement
			try{
				if(rs != null)rs.close();
				if(ps != null)ps.close();
			}catch(Exception e){}
		}
	}
	/**
	 * method select
	 * method execsql
	 */
	private void execsql(String sql)throws SQLException
	{
		isClosed(); // judge is the connection closed.
		
		java.sql.PreparedStatement ps = conn.prepareStatement(sql);
		
        try{
	        int ret = ps.executeUpdate();
	        String warn = "";
	        try{
	            ps.getWarnings().getMessage();
	        }catch(Exception e){}
	        String cmd = sql.toLowerCase();
	        
	        if(cmd.startsWith("delete"))
	        {
	        	// delete
	        	System.out.print("\r\n\r\nDelete " + ret + " Rows Success.\r\n\r\n");
	        }else if(cmd.startsWith("update"))
	        {
	        	// select
	        	System.out.print("\r\n\r\nUpdate " + ret + " Rows Success.\r\n\r\n");
	        }else
	        {
	        	if(warn == null)warn = "";
	        	System.out.print("\r\n\r\nExecute SQL Success.\r\n"+warn+"\r\n\r\n");
	        }
        }catch(SQLException e)
        {
        	throw e;
        }finally{
	        // close the statement
			try{
				if(ps != null)ps.close();
			}catch(SQLException e){}
		}
	}
	/**
	 * 打印表结构
	 */
	private void desc(String sql)throws SQLException
	{
		isClosed(); // judge is the connection closed.
		
		String cmd = sql.substring(5); // fade the 'desc'