数据库连接类DataBase

/*
 *
 */

package com.util;

import java.sql.CallableStatement;
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.Properties;

import javax.sql.DataSource;

import org.logicalcobwebs.proxool.ProxoolFacade;

 
// import java.util.*;
// import org.logicalcobwebs.proxool.*;

/**
 * remark: 用于连接数据源，并提供基本的数据读取与操作方法
 *
 * @author: date:
 * @version: v1.0
 *
 * upd_remark: upd_user: upd_date:
 */

public class DataBase {
 
 Properties properties_Base = new Properties();
 private static String driver;
 private static String url;
 
 private static String pwd;
 String alias_Base = "";
 String driverClass_Base = "";
 String driverUrl_Base = "";
 String url_Base="";
 
 
 

 
 // 构造函数1
 public DataBase() {
  // this("boss_10", "boss_dev", "boss_dev2008");
  //this("BOSSSITE", "boss118119", "111189boss");
 }

 // 构造函数2
 public DataBase(String databaseName, String user, String password) {
  
 }

 
 // 数据库连接:用odbc直接连接或是由连接池进行管理
 public Connection connect(String database) throws SQLException {
  try {
   return connectjdbcpool(database);
  } catch (Exception e) {
   e.printStackTrace();
   return null;
  }
 }
 public Connection connectodbc(String database) throws SQLException {

  try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  /*
   if (database.equalsIgnoreCase("boss_229"))
    return DriverManager.getConnection("jdbc:odbc:" + database,
      "boss118119", "111189boss");
   else
  */
   return DriverManager.getConnection("jdbc:odbc:" + database,
     "boss_dev", "boss_dev2008");
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
   return null;
  }  

 }
 /*public Connection connectjdbc(String database) throws Exception {

  try {
   return connectjdbcpool(database);
  } catch (Exception e) {
   e.printStackTrace();
   return null;
  }

 }*/
 public Connection connectjdbc(String driver,String url,String user,String password) throws SQLException {
  Connection con = null;
  try {
    Class.forName(driver);
                con = DriverManager.getConnection(url,user,password);
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
   return null;
  }
  return con;

 }
 
 public Connection connectjdbcpool(String database) throws Exception {

  try {
   return DriverManager.getConnection("proxool." + database);
  } catch (Exception e) {
   e.printStackTrace();
   return null;
  }

 }
 
 

 

 

 // 创建语句
 public Statement createStatement(Connection conn) {
  try {
   return conn.createStatement(
     java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
     java.sql.ResultSet.CONCUR_READ_ONLY);
  } catch (Exception e) {
   e.printStackTrace();
   return null;
  }
 }

 // 取得记录集
 public ResultSet getRS(Statement stmt, String SQL) throws SQLException {
  return stmt.executeQuery(SQL);
 }

 // 执行SQL 语句
 public void execute(Statement stmt, String SQL) throws SQLException {
  stmt.execute(SQL);
 }

 // 取得记录集的行数。
 public int getRowCount(ResultSet RS) {
  int rowCount = 0;
  try {
   String rowStatus = "";
   int preRow = 0;
   if (RS.isBeforeFirst()) {
    rowStatus = "isBeforeFirst";
   } else {
    if (RS.isAfterLast()) {
     rowStatus = "isAfterLast";
    } else {
     rowStatus = "normal";
     preRow = RS.getRow();
    }
   }

   RS.last();
   rowCount = RS.getRow();
   if (rowStatus.equals("isBeforeFirst") || preRow == 0) {
    RS.beforeFirst();
   } else {
    if (rowStatus.equals("isAfterLast")) {
     RS.afterLast();
    } else {
     RS.absolute(preRow);
    }
   }
  } catch (SQLException e) {
   rowCount = 0;
  }
  return rowCount;
 }

 // 取得记录集的列数。
 public int getColCount(ResultSet RS) {
  int colCount = 0;
  try {
   ResultSetMetaData RSMD = RS.getMetaData();
   colCount = RSMD.getColumnCount();
  } catch (SQLException e) {
   colCount = -1;
  }
  return colCount;
 }

 // 分页
 public void absolutePage(ResultSet RS, int pageSize, int page) {
  try {
   RS.absolute(pageSize * (page - 1) + 1);
  } catch (Exception e) {
  }
 }

 // 从存储过程中得到返回值。
 public int getReturnCode(CallableStatement callablestatement, boolean flag) {
  int i = -1;
  ResultSet resultset = null;
  if (flag) {
   try {
    resultset = callablestatement.getResultSet();
    resultset.next();
    i = resultset.getInt(1);
   } catch (SQLException _ex) {
    i = -1;
   } finally {
    try {
     resultset.close();
    } catch (SQLException _ex) {
    }
   }
  }
  return i;
 }

 public void close(ResultSet rs, Statement stmt, Connection conn) {
  try {
   // if (!rs.isClosed())
   // {
   if (rs != null) {
    rs.close();
   }
   // }
  } catch (SQLException sqle) {
   // System.out.print(sqle.getMessage());
  } catch (Exception e1) {
   e1.printStackTrace();
  }
  try {
   // if (!stmt.isClosed() && stmt !=null)
   if (stmt != null) {
    // System.out.println("stmt");
    //stmt = null;
    stmt.close();
    
   }
  } catch (Exception e2) {
   e2.printStackTrace();
  }
  try {
   // if (!conn.isClosed() && conn !=null)
   if (conn != null) {
    // System.out.println("conn");
    //conn = null;
    conn.close();
    
   }
  } catch (Exception e3) {
   e3.printStackTrace();
  }
 }

 public String getTableFields(String DataBase, String Table) {
  int rows = 0;
  String fields = "";
  try {

   Connection DBConn = connect(DataBase);
   Statement stmt = DBConn.createStatement();
   String SQL = "select column_name as COLUMN_NAME,data_type as TYPE_NAME,"
     + "isnull(character_maximum_length,0) as COLUMN_SIZE,"
     + "des.COLUMN_DESC "
     + "from information_schema.columns  isc "
     + "left join ( select sc.name as scname, sp.value as COLUMN_DESC "
     + "from sysproperties sp inner join syscolumns sc on sp.id = sc.id "
     + "and sp.smallid = sc.colid left join sysobjects so on so.id = sc.id "
     + "where so.name='"
     + Table
     + "' ) des on isc.column_name =des.scname "
     + "where table_name = '"
     + Table
     + "' order by ordinal_position asc ";

   ResultSet RS = stmt.executeQuery(SQL);
   while (RS.next()) {
    fields += RS.getString("COLUMN_NAME") + ",";
    // coltype+=RS.getString("TYPE_NAME")+",";
    // colnumber+=RS.getString("COLUMN_SIZE")+",";
    // clodesc+=RS.getString("COLUMN_DESC")+",";
    rows++;
   }
   close(RS, stmt, DBConn);
   // stmt.close();
   // RS.close();
   // DBConn.close();
   if (rows == 0) {
    return null;
   } else {
    return fields.substring(0, fields.length() - 1);
   }
  } catch (Exception e) {
   return e.getMessage();
   // e.printStackTrace();
  }
 }

 public String getTableFields(String DataBase, String Table,
   boolean TableFlag) {
  int rows = 0;
  String fields = "";
  try {

   Connection DBConn = connect(DataBase);
   Statement stmt = DBConn.createStatement();
   String SQL = "select column_name as COLUMN_NAME,data_type as TYPE_NAME,"
     + "isnull(character_maximum_length,0) as COLUMN_SIZE,"
     + "des.COLUMN_DESC "
     + "from information_schema.columns  isc "
     + "left join ( select sc.name as scname, sp.value as COLUMN_DESC "
     + "from sysproperties sp inner join syscolumns sc on sp.id = sc.id "
     + "and sp.smallid = sc.colid left join sysobjects so on so.id = sc.id "
     + "where so.name='"
     + Table
     + "' ) des on isc.column_name =des.scname "
     + "where table_name = '"
     + Table
     + "' order by ordinal_position asc ";

   ResultSet RS = stmt.executeQuery(SQL);
   while (RS.next()) {
    if (TableFlag)
     fields += Table + "." + RS.getString("COLUMN_NAME") + ",";
    else
     fields += RS.getString("COLUMN_NAME") + ",";
    // coltype+=RS.getString("TYPE_NAME")+",";
    // colnumber+=RS.getString("COLUMN_SIZE")+",";
    // clodesc+=RS.getString("COLUMN_DESC")+",";
    rows++;
   }
   close(RS, stmt, DBConn);
   // stmt.close();
   // RS.close();
   // DBConn.close();
   if (rows == 0) {
    return null;
   } else {
    return fields.substring(0, fields.length() - 1);
   }
  } catch (Exception e) {
   return e.getMessage();
   // e.printStackTrace();
  }
 }

 public String getTableFieldsDesc(String DataBase, String Table) {
  int rows = 0;
  String fieldsdesc = "";
  try {

   Connection DBConn = connect(DataBase);
   Statement stmt = DBConn.createStatement();
   String SQL = "select column_name as COLUMN_NAME,data_type as TYPE_NAME,"
     + "isnull(character_maximum_length,0) as COLUMN_SIZE,"
     + "des.COLUMN_DESC "
     + "from information_schema.columns  isc "
     + "left join ( select  sc.name as scname, sp.value as COLUMN_DESC "
     + "from sysproperties sp inner join syscolumns sc on sp.id = sc.id "
     + "and sp.smallid = sc.colid left join sysobjects so on so.id = sc.id "
     + "where so.name='"
     + Table
     + "'  ) des on isc.column_name =des.scname "
     + "where table_name = '"
     + Table
     + "' order by ordinal_position asc";

   ResultSet RS = stmt.executeQuery(SQL);
   while (RS.next()) {
    // fields+=RS.getString("COLUMN_NAME")+",";
    // coltype+=RS.getString("TYPE_NAME")+",";
    // colnumber+=RS.getString("COLUMN_SIZE")+",";
    fieldsdesc += RS.getString("COLUMN_DESC") + ",";
    rows++;
   }
   close(RS, stmt, DBConn);
   // stmt.close();
   // RS.close();
   // DBConn.close();
   if (rows == 0) {
    return null;
   } else {
    return fieldsdesc.substring(0, fieldsdesc.length() - 1);
   }
  } catch (Exception e) {
   return e.getMessage();
   // e.printStackTrace();
  }
 }

 public ResultSet getTableDesc(String DataBase, String Table) {

  try {

   Connection DBConn = connect(DataBase);
   Statement stmt = DBConn.createStatement();
   String SQL = "select column_name as COLUMN_NAME,data_type as TYPE_NAME,"
     + "isnull(character_maximum_length,0) as COLUMN_SIZE,"
     + "des.COLUMN_DESC "
     + "from information_schema.columns  isc "
     + "left join ( select    sc.name as scname, sp.value as COLUMN_DESC "
     + "from sysproperties sp inner join syscolumns sc on sp.id = sc.id "
     + "and sp.smallid = sc.colid left join sysobjects so on so.id = sc.id "
     + "where so.name='"
     + Table
     + "'   ) des on isc.column_name =des.scname "
     + "where table_name = '"
     + Table
     + "' order by ordinal_position asc";

   ResultSet RS = stmt.executeQuery(SQL);
   // DBConn.close();
   if (RS == null) {
    return null;
   } else {
    return RS;
   }
  } catch (Exception e) {
   return null;
  }
 }

 public static void maino(String[] args) throws SQLException {
  DataBase db = null;
  Connection conn = null;
  Statement stmt = null;
  ResultSet RS = null;
  String SQL = "select getdate() as 'name' select Id, Name, children from V_DicBase where 1=1 ";
  // db = new DataBase();
  // System.out.println(db.getTableFields("jobcn_boss_base", "DicBase"));
  // System.out.println(db.getTableFieldsDesc("jobcn_boss_base",
  // "DicBase"));
  try {
   db = new DataBase();
   // conn = db.connect("jobcn_boss_base");
   // stmt = conn.createStatement();
   // RS = stmt.executeQuery(SQL);
   // RS=db.getTableDesc("jobcn_boss_base", "Menu");
   // if(RS.next()) {
   // System.out.println(RS.getString(1));
   // }

   // System.out.println(conn);
   System.out.println(db
     .getTableFields("jobcn_boss_hr", "Person_Main"));
   db.close(RS, stmt, conn);

  } catch (Exception sqle) {
   sqle.printStackTrace();
  } finally {
   try {
    conn.close();
   } catch (Exception e) {
    // e.printStackTrace();
   }

  }
 }

 public Connection getPoolConnection(String poolName)
 {
  DataSource ds = null;
  Connection conn = null;
  try {
   if((conn=connectjdbcpool(poolName))==null){
    System.out.println("数据库："+poolName+"连接池无法获取，将关闭连接池！");
    ProxoolFacade.removeConnectionPool(poolName);
    System.out.println("数据库："+poolName+"连接池即将建立...");
    registerjdbcpool(poolName,"2","1");
    System.out.println("数据库："+poolName+"连接池建立！");
    return connectregister(poolName);
   
    
   }
  } catch (Exception e) {
   conn = getConnection();
   
   
  }
  
  return conn;
    
    
 }
 public void registerjdbcpool(String database,String max,String min)  {

  try {
   Class.forName("net.sourceforge.jtds.jdbc.Driver");
   // Class.forName("com.mysql.jdbc.Driver");
   Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
   
   properties_Base.setProperty("proxool.maximum-connection-count",max);
   properties_Base.setProperty("proxool.minimum-connection-count", min);
   properties_Base.setProperty("proxool.house-keeping-test-sql","SELECT top 1 id from Kindergarden8_mp3 ");
   properties_Base.setProperty("user", "root");
   properties_Base.setProperty("password", "root");
   alias_Base = database;
   driverClass_Base = "net.sourceforge.jtds.jdbc.Driver";
   if(database.equals("mydb")){
    driverUrl_Base = "jdbc:mysql://localhost:3306/"+database+";";
   }
   url_Base = "proxool." + alias_Base + ":" + driverClass_Base+ ":" + driverUrl_Base;
   ProxoolFacade.registerConnectionPool(url_Base, properties_Base);
   
   //return DriverManager.getConnection("proxool." + database);
  } catch (Exception e) {
   e.printStackTrace();
   //return null;
  }

 }
 public static Connection getConnection()
 {
  Connection con = null;
  try {
   con = DriverManager.getConnection(Config.con_url,Config.con_user,Config.con_pwd);
  } catch (Exception e) {
   e.printStackTrace();
   //System.out.println("DataBase.getConnection:数据库连接错误!");
   return null;
  }
  return con;
 }
 public Connection connectregister(String database) throws SQLException {
  
  try {
   return connectjdbcpool(database);

  } catch (Exception e) {
   e.printStackTrace();
   return null;
  }

}
 public static void main(String args[]){
  try{
  Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
  Connection connection=DriverManager.getConnection(
    "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=chat", "sa", "");
  
   Statement stmt =connection.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_UPDATABLE);
  //ResultSet rs = stmt.executeQuery("exec T_GetUserMenuXml '', 'JCNEP0347','style'");
   ResultSet rs = stmt.executeQuery("select top 1 Name from USER_INFO;");
  rs.next();
  
  System.out.println(rs.getString("Name"));//odbc
  //System.out.println(rs.getBinaryStream(1));//odbc
  //System.out.println(rs.getCharacterStream(1));

 
  // System.out.println(RS.getCharacterStream(1));

  // InputStream is =RS.getBinaryStream(1);
  // //RS.getBinaryStream(1);//odbc
  // InputStreamReader isr = new
  // InputStreamReader(is,"UnicodeLittle");
  // while ((c = isr.read()) != -1)
  // {
  // //System.out.println((char)c);
  // sb.append((char)c);
  // }

  // sb.append(RS.getString(1));
  // temp=RS.getString(1);
  // temp=temp.getBytes("GBK");

  // System.out.println("");
  // System.out.println("");
  // sb.append(temp);
  
  

  stmt.close();

  connection.close();
  System.out.println("ok");

  }catch(Exception ex){
   ex.printStackTrace();
  }
 }

}