databaseaccess.java

sp最新短信API(综合网关)

package com.wayout.wayoutsp.publics;

import java.sql.*;
import java.util.*;
import com.wayout.wayoutsp.communication.IsmgInfo;
/**
 * <p>Title: 公司运营项目</p>
 * <p>Description: 实现短信SP之功能.短信互动系统</p>
 * <p>Copyright: Copyright (c) 2002</p>
 * <p>Company: wayout</p>
 * @author wayout Software
 * @version 1.0
 */

public class DatabaseAccess
{
	private static Connection con = null;

	public DatabaseAccess() {
        }

        public static   synchronized  DatabaseAccess instance() {
		if(wayoutspDB == null)
			wayoutspDB = new DatabaseAccess();
		return wayoutspDB;
	}
	/**
	 * 从T_SYS_CONFIG表获取配置参数
         * @return boolean
	 */
 	public static boolean getSYSPara() {
        	return true;
	}

        /**
         * 从T_SYS_CONFIG表获取单个配置参数
         * @param paraname
         * @param conn1orConn2 连接名
         * @return String para
         */
//	public static String getOneSYSPara(String paraname)
//        {
//            Connection conn=connectDatabase(PublicConstants.CONNECTION_URL,PublicConstants.DB_USER,PublicConstants.DB_PASS);
//            ResultSet rs = null;
//	    PreparedStatement pstmt=null;
//		String sQuery =
//		    "select PARAM_VALUE "+
//		    "from T_SYS_CONFIG where PARAM_NAME = ? ";
//		try {
//			pstmt = conn.prepareStatement(sQuery) ;
//			pstmt.setString(1,paraname) ;
//			rs = pstmt.executeQuery() ;
//			if(rs.next()) {
//				//System.out.println(paraname +" : "+rs.getString(1));
//				String retstr = rs.getString(1) ;
//				pstmt.close() ;
//                disconnectDatabase(conn);
//				return retstr ;
//			} else {
//				pstmt.close() ;
//                disconnectDatabase(conn);
//				return null;
//			}
//		} catch (Exception ex) {
//			System.out.println("Read Para "+paraname+" :"+ex);
//			PublicConstants.writeLog.error("Read Para "+paraname+" :"+ex);
//			disconnectDatabase(conn);
//            return null;
//		}
//    }
    /**
 * 从T_SYS_CONFIG表获取单个配置参数
 * @param paraname
 * @param conn1orConn2 连接名
 * @return String para
 */
public static IsmgInfo getIsmgPara(String sectionNum,byte dealerType)
{
    Connection conn=connectDatabase(PublicConstants.CONNECTION_URL,PublicConstants.DB_USER,PublicConstants.DB_PASS);
    IsmgInfo ismg = new IsmgInfo();

    ResultSet rs = null;
    PreparedStatement pstmt=null;
        String sQuery =
            "select NUMPROFGID,VC2ISMG_NAME, VC2ISMG_IP, VC2ISMG_PASSWD, NUMISMG_ID ,NUMISMG_MO_PORT,"
            +" NUMISMG_MT_PORT,VC2SP_NAME,VC2SP_IP,VC2SP_PASSWD,VC2SP_ID,VC2SP_CODE "
            +"from WL_SYS_ISMGPROFILE where NUMISMG_STATUS= 0 and NUMDEALER_TYPE=? "
            +" and VC2SECTION_NUM= ? ";
        try {
                pstmt = conn.prepareStatement(sQuery) ;
                pstmt.setByte(1,dealerType) ;
                pstmt.setString(2,sectionNum) ;
                rs = pstmt.executeQuery() ;
                if(rs.next()) {
                  ismg.IsmgSectionFlag = rs.getInt("NUMPROFGID");
                  ismg.ismgName = rs.getString("VC2ISMG_NAME") ;
                  ismg.ismgIp = rs.getString("VC2ISMG_IP") ;
                  ismg.ismgPasswd = rs.getString("VC2ISMG_PASSWD") ;
                  ismg.ismgId = rs.getString("NUMISMG_ID") ;
                  ismg.ismgMoPort = rs.getInt("NUMISMG_MO_PORT") ;
                  ismg.ismgMtPort = rs.getInt("NUMISMG_MT_PORT") ;
                  ismg.spName = rs.getString("VC2SP_NAME") ;
                  ismg.spIp = rs.getString("VC2SP_IP") ;
                  ismg.spPasswd = rs.getString("VC2SP_PASSWD") ;
                  ismg.spId   = rs.getInt("VC2SP_ID") ;
                  ismg.spCode   = rs.getString("VC2SP_CODE") ;
                  pstmt.close() ;
                  disconnectDatabase(conn);
                  return ismg ;
                } else {
                        pstmt.close() ;
                        disconnectDatabase(conn);
                        return null;
                }
        } catch (Exception ex) {
                PublicConstants.writeLog.error("Read Para "+sectionNum+" :"+ex);
                disconnectDatabase(conn);
    return null;
        }
    }


    /**
     * 得到地区标志
     * @param sectionNum
     * @param dealerType
     * @return
     */
//    public static int getIsmgSectionFlag(String sectionNum,byte dealerType) {
//      Connection conn=connectDatabase(PublicConstants.CONNECTION_URL,PublicConstants.DB_USER,PublicConstants.DB_PASS);
//      ResultSet rs = null;
//      PreparedStatement pstmt=null;
//      int i=0;
//      String sQuery = "select NUMPROFGUID from WL_SMS_AREAPROFILE where VC2CODE=? and VC2SPTYPE=?";
//        try {
//                pstmt = conn.prepareStatement(sQuery) ;
//                pstmt.setString(1,sectionNum) ;
//                pstmt.setByte(2,dealerType) ;
//
//                rs = pstmt.executeQuery() ;
//                if(rs.next()) {
//                  i = rs.getInt(1);
//                  pstmt.close() ;
//                  disconnectDatabase(conn);
//                  return i;
//                } else {
//                        pstmt.close() ;
//                        disconnectDatabase(conn);
//                        return i;
//                }
//        } catch (Exception ex) {
//                PublicConstants.writeLog.error("getIsmgSectionFlag(): "+sectionNum+" :"+ex);
//                disconnectDatabase(conn);
//    return i;
//        }
//
//
//    }

    /**
     * 从T_SYS_CONFIG表获取单个配置参数
     * @param paraname
     * @param conn1orConn2 连接名
     * @return String para
     */
//    public static String getTestPara(String paraname)
//    {
//        Connection conn=connectDatabase(PublicConstants.CONNECTION_URL,PublicConstants.DB_USER,PublicConstants.DB_PASS);
//        ResultSet rs = null;
//        PreparedStatement pstmt=null;
//            String sQuery =
//                "select PARAM_VALUE "+
//                "from T_Test_Sms where PARAM_SEQ = ? ";
//            try {
//                    pstmt = conn.prepareStatement(sQuery) ;
//                    pstmt.setString(1,paraname) ;
//                    rs = pstmt.executeQuery() ;
//                    if(rs.next()) {
//                            //System.out.println(paraname +" : "+rs.getString(1));
//                            String retstr = rs.getString(1) ;
//                            pstmt.close() ;
//                            disconnectDatabase(conn);
//                            return retstr ;
//                    } else {
//                            pstmt.close() ;
//                            disconnectDatabase(conn);
//                            return null;
//                    }
//            } catch (Exception ex) {
//                    System.out.println("Read TEST Para "+paraname+" :"+ex);
//                    PublicConstants.writeLog.error("Read TEST Para "+paraname+" :"+ex);
//                    disconnectDatabase(conn);
//        return null;
//            }
//}

        /**
         * 建立与数据库的连接
         * @param Conn1orConn2
         * @return Connection
         */
	public static Connection connectDatabase(String db_url,String db_user,String db_passwd) {
	    Connection	dbc = null;
                 // System.out.println("CONNECTION_URL"+PublicConstants.CONNECTION_URL);
                 // System.out.println("DB_USER"+PublicConstants.DB_USER);
                 // System.out.println("DB_PASS"+PublicConstants.DB_PASS);
                  try {
                          Class.forName(PublicConstants.DB_DRIVER);
//                          Class.forName(PublicConstants.DB_DRIVER).newInstance();
                          DriverManager.setLoginTimeout(5) ;
                          dbc = DriverManager.getConnection(db_url,
                                                            db_user, db_passwd);
                          //System.out.println("Connect DB "+db_url+"OK!");
                          return dbc ;
                  } catch (ClassNotFoundException cnfe) {
                        System.out.println(db_url+" Not Found Oracle Driver: "+cnfe);
                        PublicConstants.writeLog.info(db_url+" Not Found Oracle Driver: "+cnfe,0);
                        return null;
                  }
                  catch (Exception e) {
                        System.out.println(db_url+" Connect Oracle: "+e);
                        PublicConstants.writeLog.info(db_url+" Connect Oracle: "+e,0);
                        return null;
                  }
	}

        /**
         * 断开与数据库之间的连接
         * @param conn
         */
	public static void disconnectDatabase(Connection conn) {
		if(conn == null)
			return ;
		try {
			conn.close();
		} catch (SQLException sqle) {
			// method.error("Database disconnecting failed") ;
		}
	}

	public static boolean isConnectDB(Connection dbc) {
		if(dbc == null )
			return false;

		ResultSet rs = null;
		String query = "select 1 from dual ";
		try {
			Statement stmt = dbc.createStatement();
			rs = stmt.executeQuery(query);
		} catch(SQLException e) {
			return false;
		}
		return true;
	}

       public static  DatabaseAccess wayoutspDB;
}