dbhelper.java

采用JAVA开发

package com.gctech.sms.sp.cms.util;

import java.util.*;
import java.io.*;
import javax.sql.*;
import java.sql.*;
import javax.naming.*;
/**
 *
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: ted</p>
 * @author li jia zhi
 * @version 1.0
 */
public class DBHelper
{

  public static  boolean isDebug = false;
  public static DataSource ds = null;
  static Context context = null;
  public  static Connection getConn() throws CMSException
  {

    if(isDebug)
    {
      return getDebugConnection();
    }
    try
    {
      return getDataSource().getConnection();
    }
    catch (SQLException ex)
    {
      throw new CMSException("得到connection pool错误"+ex.getMessage());
    }
    catch(CMSException ure)
    {
      throw ure;
    }




  }

  public  static void cleanup(Connection conn)
  {
    try
    {
      if(conn==null)
      {
        return;
      }
      else
      {
        conn.close();
      }
    }
    catch (Exception ex)
    {

    }
  }

  private  static DataSource getDataSource() throws CMSException
  {
    try
    {
      if(ds==null)
      {
        Context context = getInitialContext();
        ds = (DataSource)context.lookup("CMSDataSource");
      }

      return ds;

    }
    catch(CMSException ure)
    {
      throw ure;
    }
    catch (Exception ex)
    {
      throw new CMSException("不能得到数据源"+ex.getMessage());
    }
  }

  /**
   * 得到context
   * @return
   * @throws CMSException
   * @todo 验证线程安全
   */
  public  static Context getInitialContext() throws CMSException
  {
    try
    {

       return context = new InitialContext();

      //return new InitialContext(environment);

    }
    catch (Exception ex) {

      throw new CMSException("得到context失败"+ ex.getMessage());
    }

   // return context;
  }

  public static Connection getDebugConnection() throws CMSException
  {
    Connection con = null;
    //getting the properties
    String driver = PropertyLoader.getInstance().getProperty("DataBase.JDBCDriver");
    if(driver == null)
      throw new CMSException("Missing property 'DataBase.JDBCDriver' in SourceGenerator.properties file");
    String dburl = PropertyLoader.getInstance().getProperty("DataBase.url");
    if(dburl == null)
      throw new CMSException("Missing property 'DataBase.url' in SourceGenerator.properties file");
    String userid = PropertyLoader.getInstance().getProperty("DataBase.userid");
    if(userid == null)
      throw new CMSException("Missing property 'DataBase.userid' property in SourceGenerator.properties file");
    String password = PropertyLoader.getInstance().getProperty("DataBase.password");
    if(password==null)
      throw new CMSException("Missing property 'DataBase.password' property in SourceGenerator.properties file");

    try {
      //creating connection
      Class.forName(driver);
      dburl="jdbc:oracle:thin:@192.168.12.2:1521:ninecard";
      userid=password="cms";
      con = DriverManager.getConnection(dburl,userid,password);
      if(con == null)
        throw new CMSException("Error in creating DBConnection. Make sure your DBAccess properties are correct." +
                                      "if correct there may be other problem in Accessing given Database ");
    }catch(Exception cnf ) {

      cnf.printStackTrace();
      throw new CMSException(cnf.getMessage());

    }
    return con;

  }

  public static void cleanupPs(PreparedStatement ps)
 {
   try
   {
     if(ps != null)
       ps.close();
   }
   catch(SQLException sqle)
   {
     //ignore;
   }

 }

 public static int nextSeqId(Connection conn)
     throws SQLException
 {
   int seqId = 0;
   try
   {
     String sql = "select cms_seq.nextval from dual";
     PreparedStatement ps = conn.prepareStatement(sql);
     ResultSet rs = ps.executeQuery();
     if(rs.next())
     {
       seqId = rs.getInt(1);
     }
     else
     {
       throw new SQLException("找不到指定序列值");
     }

     rs.close();
   }
   catch(SQLException ex)
   {
   }
   return seqId;
 }


  public static void main(String[] args) throws Exception
  {
    //Connection conn = new EJBUtil().getConnection();
    DBHelper.isDebug = true ;
    DBHelper util = new DBHelper();
    Connection conn = util.getConn();
//    PreparedStatement ps = conn.prepareStatement("select * from t_nm_record");
//    ResultSet rs = ps.executeQuery();
//    while(rs.next())
//    {
//      System.out.println(rs.getInt(1));
//    }
  }


}

class PropertyLoader
  {
    static PropertyLoader l = null;
    static String path = System.getProperty("user.home")+"/DaoTool.txt";
    Properties p = null;
    private PropertyLoader()
    {
      load();
    }

    public static PropertyLoader getInstance()
    {
      if(l==null)
      {
        l = new PropertyLoader();
      }
      return l;
    }

    private void load()
    {
      try
      {
        p =new Properties();
        p.load(new FileInputStream(new File(path)));
      }
      catch (Exception ex)
      {
        ex.printStackTrace();
//        Logger.info(ex.getMessage());
      }
    }

    public String getProperty(String key)
    {
      return (String)p.get(key);
    }
  }
/*
     select t.phone,sum(decode(t.submit_type,'B',1,0)),sum(decode(t.submit_type,'A',1,0)),sum(decode(t.submit_type,'XM',1,0))
  from t_nm_record t
  group by t.phone

  */