dbutil.java

一个用于监控WEB服务器和数据库服务器的客户端程序。

package com.jobcn.database;

import java.io.FileOutputStream;
import java.sql.*;
import java.util.Arrays;
import java.util.Hashtable;
import javax.sql.*;
import javax.sql.rowset.WebRowSet;
import javax.naming.*;
import com.sun.rowset.WebRowSetImpl;

import net.snapbug.util.dbtool.*;

/**
 * <p>Title: 卓博营运支撑系统</p>
 *
 * <p>Description: 为本企业内部运作提供支撑，为企业資源的E化管理提供解決方案</p>
 *
 * <p>Copyright: Copyright (c) 2006</p>
 *
 * <p>Company: 卓博信息科技有限公司</p>
 *
 * @author Henry
 * @version 1.0
 */
public class DBUtil {

	/**
	 * @param args
	 */
	//private static String odbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
	//private static String jdbcDriver = "net.sourceforge.jtds.jdbc.Driver";
	
	static{
		try {
			ConnectionManager.init("conf/ConnPool.conf");
			//Class.forName(jdbcDriver);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public DBUtil() {
	}

	public Connection getJNDIConnection() {
		try {
			ctx = new InitialContext();
			ct = (Context) ctx.lookup("java:comp/env");
			ds = (DataSource) ct.lookup("jdbc/boss");
			conn = ds.getConnection();
			System.out.println("Connection opened!");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	public Connection getJNDIConnection(String dsName) {
		try {
			ctx = new InitialContext();
			ct = (Context) ctx.lookup("java:comp/env");
			ds = (DataSource) ct.lookup(dsName);
			conn = ds.getConnection();
			System.out.println("Connection opened!");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static Connection getConnection() {
		ConnectionPool pool = null;
		try {			
			ConnectionManager.init("conf/ConnPool.conf");
			pool = ConnectionManager.getConnectionPool("jobcn_boss_214");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return pool;
	}

	public static Connection getConnection(String ds) {
		ConnectionPool pool = null;
		try {
			pool = ConnectionManager.getConnectionPool(ds);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return pool;
	}

	public static int getActiveConnNum(String ds) throws NoinitialException {
		ConnectionPool pool = ConnectionManager.getConnectionPool(ds);
		String poolStr = pool.toString();
		int startIdx = poolStr.indexOf("activeConnectionNumber=");
		int endIdx = poolStr.lastIndexOf(", .");
		int activeConnNum = Integer.parseInt(poolStr.substring(startIdx + 23,
				endIdx));
		return activeConnNum;
	}

	public static int getActiveConnNum(String ds, Connection pool) {
		String poolStr = pool.toString();
		int startIdx = poolStr.indexOf("activeConnectionNumber=");
		int endIdx = poolStr.lastIndexOf(", .");
		int activeConnNum = Integer.parseInt(poolStr.substring(startIdx + 23,
				endIdx));
		return activeConnNum;
	}

	public static int getActiveStmtNum(String ds, Connection pool) {
		int aStmtNum = 0;
		String poolStr = pool.toString();
		int stmtIdx = poolStr.indexOf("[");
		if (stmtIdx > 0) {
			String subPoolStr = poolStr.substring(stmtIdx);
			String[] stmtStrs = subPoolStr.split("\n");
			int leng = stmtStrs.length;
			for (int i = 0; i < leng - 1; i++) {
				int startIdx = stmtStrs[i].indexOf("activeStatementNumber=");
				int endIdx = stmtStrs[i].lastIndexOf(",");
				aStmtNum += Integer.parseInt(stmtStrs[i].substring(
						startIdx + 22, endIdx));
			}
		}
		return aStmtNum;
	}

	public static void balanceAccessServer(String[] servers,int accessNum) {
		int size = servers.length;
		int[] conns = new int[size];
		int[] stmts = new int[size];
		int[] requests = new int[size];
		Hashtable<Integer,Server> ht = new Hashtable<Integer,Server>();
		for (int i = 0; i < size; i++) {
			Server server = new Server(servers[i], conns[i], stmts[i],
					requests[i]);
			ht.put(new Integer(i), server);
		}
		Object[] obj = ht.values().toArray();
		long start = System.currentTimeMillis();
		for (int i = 0; i < accessNum; i++) {
			if (i > 1) {
				Arrays.sort(obj);
			}
			Server s = (Server) obj[0];
			String ds = (String) s.getName();
			Connection conn = DBUtil.getConnection(ds);
			try {	
				Statement stmt = conn.createStatement();
				String query = "select * from sysobjects";
				ResultSet rs = stmt.executeQuery(query);
				if(rs.next()){
					System.out.println(rs.getString(1));					
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			int aConnNum = DBUtil.getActiveConnNum(ds, conn);
			int aStmtNum = DBUtil.getActiveStmtNum(ds, conn);
			((Server) obj[0]).setActiveConnNum(aConnNum);
			((Server) obj[0]).setActiveStmtNum(aStmtNum);
			((Server) obj[0]).setRequestNum(((Server) obj[0]).getRequestNum() + 1);
			for (int j = 0; j < obj.length; j++) {
				Server server = (Server) obj[j];
				System.out.print("服务器:" + server.getName() + " 占用conn资源:"
						+ server.getActiveConnNum());
				System.out.print("    占用stmt资源:" + server.getActiveStmtNum());
				System.out.print("    请求次数:" + server.getRequestNum());
				System.out.println();
			}
		}
		long end = System.currentTimeMillis();
		System.out.println("total time = " + (end - start));
	}

	private static void testConnSpeed(){
		String odbcUrl = "jdbc:odbc:boss_222";
		String jdbcUrl = "jdbc:jtds:sqlserver://192.168.60.222:1433/jobcn_boss";
		String user = "up_bos$_top";
		String pass = "uptopbos$";
		long start = System.currentTimeMillis();
		try{
			Connection conn = DriverManager.getConnection(odbcUrl,user,pass);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select getdate()");
			if(rs.next()){
				System.out.println(rs.getString(1));
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		long end = System.currentTimeMillis();
		System.out.println("total time = " + (end - start));
		
		start = System.currentTimeMillis();
		try{
			Connection conn = DriverManager.getConnection(jdbcUrl,user,pass);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select getdate()");
			if(rs.next()){
				System.out.println(rs.getString(1));
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		end = System.currentTimeMillis();
		System.out.println("total time = " + (end - start));
	}
	
	private static void useRowSet() {
		try{
			WebRowSet wrs = new WebRowSetImpl();
			wrs.setUrl(dbUrl);   
			wrs.setUsername(dbUser);
			wrs.setPassword(dbPass);
			wrs.setCommand("select * from Base_Menu");
			wrs.execute();
			wrs.writeXml(new FileOutputStream("Base_Menu.xml"));
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) throws Exception {
		//String[] servers = { "jobcn_boss_214", "jobcn_boss_214",
				//"jobcn_boss_214" };
		//DBUtil.testConnSpeed();
		//DBUtil.balanceAccessServer(servers,100);
		//DBUtil.balanceAccessServer(
				//new String[]{"jobcn_boss_229_odbc", "jobcn_boss_222_odbc","jobcn_boss_214_odbc"},300);
		DBUtil.testConnSpeed();
		
		/*Connection conn = DBUtil.getConnection();
		for (int i=0;i<500;i++) {
			Statement stmt = conn.createStatement();
			System.out.println(stmt);
			stmt.close();
		}
		conn.close();*/
		
	}

	private static String dbUrl = "jdbc:jtds:sqlserver://192.168.60.214:1433/jobcn_boss_Base";
	private static String dbUser = "sa";
	private static String dbPass = "develop";
	private Context ctx, ct;
	private DataSource ds;
	private Connection conn;
}