DBtool.java
package com.zte.upf.client.util;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.log4j.Logger;
public class DBTool {
private static final Logger log = Logger.getLogger(DBTool.class);
private static Properties config = new Properties();
static{
try {
config.load(DBTool.class.getClassLoader().getResourceAsStream("database/oracle.properties"));
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 实现分页查询的功能
* @param sql 查询的SQL语句
* @param params SQL参数
* @param page 分页参数(要查询的页数、每页大小)
* @param isCount 是否查询总数
* @return 结果集，包括分页信息
*/
public Page queryDataOfPage(Page page, String sql,boolean isCount, Object ...params){
List<Map<String,String>> resultList = new ArrayList<Map<String,String>>();
try {
if(isCount){
page.setTotalCount(count(sql, params));
if(page.getPageNum() > page.getPageCount()){
page.setPageNum(page.getPageCount());
}
}
String _pageHandle = config.getProperty("PageHandle");
_pageHandle = _pageHandle.replace("#SQL#", sql);
_pageHandle = _pageHandle.replace("#BEGININDEX#", "" + page.getBeginIndex());
_pageHandle = _pageHandle.replace("#ENDINDEX#", "" + page.getEndIndex());
resultList = (List<Map<String, String>>) this.doQuery(_pageHandle, params);
page.setData(resultList);
} catch (Exception e) {
e.printStackTrace();
}
return page;
}
/**
* 查询总记录数
* @param sql 查询的SQL语句
* @param params SQL参数
* @return int 记录数
* @throws Exception
*/
public int count(String sql, Object ...params) throws Exception{
StringBuffer sb = new StringBuffer("SELECT count(1) as count FROM (");
sb.append(sql);
sb.append(")");
List<Map<String, String>> countList = doQuery(sb.toString(), params);
if(countList.size() > 0){
Map<String,String> countMap = countList.get(0);
return Integer.parseInt(countMap.get("count"));
}
return 0;
}
/**
* 根据SQL查询结果集
* @param sql 查询的SQL语句
* @param objs SQL参数
* @return List<Map<String, String>> 查询结果列表
* @throws Exception
*/
public List<Map<String, String>> doQuery(String sql, Object ... params) throws Exception {
Connection conn = null;
//Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet result = null;
try {
// 取出一个数据库连接
conn = ConnectionPool.getInstance().getConnection();
// 取出游标
pstmt = conn.prepareStatement(sql);
//设置参数
for(int i = 0; i < params.length; i++){
pstmt.setObject(i + 1, params[i]);
}
// 执行SQL查询语句
result = pstmt.executeQuery();
// 获得结果集中字段类型属性
ResultSetMetaData metaData = result.getMetaData();
String[] names = new String[metaData.getColumnCount()];
// 循环定位，获得字段名称和类型
int i = 0;
for (i = 0; i < metaData.getColumnCount(); i++) {
names[i] = (metaData.getColumnLabel(i + 1)).toLowerCase();
}
String str = "";
// 取出查询结果
List<Map<String, String>> lst = new ArrayList<Map<String, String>>();
HashMap<String, String> tmp = new HashMap<String, String>();
while (result.next()) {
// 重构输出结构
tmp = new HashMap<String, String>();
// 按字段名循环定制输出结构
for (i = 0; i < names.length; i++) {
// 不管什么数据类型，一律按字符串取出
str = result.getString(i + 1);
// 如果取出的字段是null值，将这个字符串置为空字符串
str = str == null ? "" : str.trim();
// 定制输出结构
tmp.put(names[i], str);
}
lst.add(tmp);
}
return lst;
} catch (Exception e) {
log.error("sql:" + sql, e);
throw e;
} finally {
try {
// 关闭数据库查询结果集
if (result != null) {
result.close();
}
result = null;
if (pstmt != null) {
pstmt.close();
}
} catch (Exception e) {
}
try {
// 归还数据库连接
ConnectionPool.getInstance().returnConnection(conn);
} catch (Exception e) {
}
}
}
/**
* 执行增、删、改操作
* @param sql 要执行的SQL语句
* @param objs SQL参数
* @return 执行返回的成功记录数,0-失败;大于0成功
* @throws Exception
*/
public int doUpdate(String sql, Object... objs) throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
// 取出一个数据库连接
conn = ConnectionPool.getInstance().getConnection();
// 取出游标
ps = conn.prepareStatement(sql);
for (int i=0; i<objs.length; i++){
ps.setObject(i+1, objs[i]);
}
int ret = ps.executeUpdate();
return ret;
} catch (Exception e) {
log.error("sql:" + sql, e);
throw e;
} finally {
try {
// 关闭数据库查询结果集
if (ps != null) {
ps.close();
}
} catch (Exception e) {
}
try {
// 归还数据库连接
ConnectionPool.getInstance().returnConnection(conn);
} catch (Exception e) {
}
}
}
}
DBUtil.java
package com.zte.aspportal.comm.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.zte.aspportal.application.bean.AppServiceArea;
import com.zte.aspportal.application.bean.Application;
import com.zte.aspportal.comm.exception.WebException;
import com.zte.aspportal.comm.util.Page;
import com.zte.toolstore.tools.InitConstants;
import com.zte.zxywpub.ZXYWException;
import examples.nntp.newsgroups;
public class DBUtil extends ywaccess{
public static ywaccess yw=new ywaccess();
/**
* 根据开关判断是否打印sql语句
* @return
*/
public static boolean printSql(){
String printsql = InitConstants.getInstance().getString("printsql");
if(printsql == null || "".equals(printsql))
printsql = "1";
return Integer.valueOf(printsql) == 0 ? false:true;
}
/**
* 查询语句
* @param sql
* @return
* @throws Exception
*/
public static List<Map<String,String>> doQuery(String sql,List<Object> listParma) throws WebException{
List<Map<String,String>> list = new ArrayList<Map<String,String>>();
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet result = null;
String connstr = "";
if(printSql())
logger.info("doQuery:"+sql);
try
{
connstr = yw.getDefScpid();
conn = yw.dbconn(connstr);
if(listParma == null || listParma.size() == 0){
stmt = yw.getstmt(conn);
result = yw.executeQuery(sql, stmt);
}else {
pstmt = yw.getPreStmt(conn, sql);
for(int i =0 ;i < listParma.size();i++){
pstmt.setString(i+1, (String)(listParma.get(i)+""));
}
result = yw.executeQuery(pstmt);
}
// 获得结果集中字段类型属性
ResultSetMetaData metaData = result.getMetaData();
String[] names = new String[metaData.getColumnCount()];
// 循环定位，获得字段名称和类型
int i = 0;
for (i = 0; i < metaData.getColumnCount(); i++) {
names[i] = (metaData.getColumnLabel(i + 1)).toLowerCase();
}
String str = "";
// 取出查询结果
HashMap<String, String> tmp = new HashMap<String, String>();
while (result.next()) {
// 重构输出结构
tmp = new HashMap<String, String>();
// 按字段名循环定制输出结构
for (i = 0; i < names.length; i++) {
// 不管什么数据类型，一律按字符串取出
str = result.getString(i + 1);
// 如果取出的字段是null值，将这个字符串置为空字符串
str = str == null ? "" : str.trim();
// 定制输出结构
tmp.put(names[i], str);
}
list.add(tmp);
}
return list;
} catch (WebException e)
{
throw e;
} catch (Exception e)
{
throw new WebException("任务执行失败，请与系统管理员联系！");
} finally
{
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
yw.dbfree(conn, connstr);
}
} catch (Exception e)
{
throw new WebException(e.getMessage());
}
}
}
/**
*
* 实现查询分页的功能
*
* @param page
* @param sql
* @param isCount
* @param params
* @return 结果集
*/
public static List<Map<String,String>> handlePage(Page page, String sql,boolean isCount,List<Object> listParma){
List<Map<String,String>> resultList = new ArrayList<Map<String,String>>();
try {
if(isCount){
page.setTotalCount(count(sql,listParma));
if(page.getPageNum() > page.getPageCount()){
page.setPageNum(page.getPageCount());
}
}
String _pageHandle = "select dt.*,rownum from (select dataTable.*, rownum as rnum from ( #SQL# ) dataTable where rownum <= #ENDINDEX# ) dt where rnum > #BEGININDEX#";//InitConstants.getInstance().getString("pageHander");
_pageHandle = _pageHandle.replace("#SQL#", sql);
_pageHandle = _pageHandle.replace("#BEGININDEX#", "" + page.getBeginIndex());
_pageHandle = _pageHandle.replace("#ENDINDEX#", "" + page.getEndIndex());
resultList = (List<Map<String, String>>)doQuery(_pageHandle,listParma);
page.setData(resultList);
} catch (Exception e) {
e.printStackTrace();
}
return resultList;
}
/**
* 查询条数
* @param sql
* @return
* @throws Exception
*/
public static int count(String sql,List<Object> listParma) throws Exception{
List<Map<String,String>> list = new ArrayList<Map<String,String>>();
StringBuffer sb = new StringBuffer("SELECT count(1) as count FROM ").append("(");
sb.append(sql).append(")");
list=doQuery(sb.toString(),listParma);
if(list.size()>0){
return Integer.parseInt(list.get(0).get("count"));
}
return 0;
}
/**
* 查询条数
* @param sql
* @return
* @throws Exception
*/
public static int getCount(String sql,List<Object> listParma) throws Exception{
List<Map<String,String>> list = new ArrayList<Map<String,String>>();
StringBuffer sb = new StringBuffer("SELECT count(1) as count FROM ").append(sql);
list=doQuery(sb.toString(),listParma);
if(list.size()>0){
return Integer.parseInt(list.get(0).get("count"));
}
return 0;
}
/**
* 插入和修改
* @param allIndex
* @return
* @throws WebException
*/
public static boolean doUpdate(String sql,List<Object> paramList) throws WebException
{
if(printSql()){
logger.warn("Enter getSql: sql[" + sql + "]");
logger.info("doUpdate:"+sql);
}
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
// ResultSet result = null;
boolean isSuccess = true;
String connstr = "";
try
{
connstr = yw.getDefScpid();
logger.warn("start creating database connection....");
conn = yw.dbconn(connstr);
logger.warn("create database connection completed successfully");
try
{
if(paramList == null || paramList.size() == 0){
stmt = yw.getstmt(conn);
stmt.executeUpdate(sql);
}else {
pstmt = yw.getPreStmt(conn, sql);
for(int i = 0; i < paramList.size();i++){
pstmt.setString(i+1, (String)(paramList.get(i)+""));
}
pstmt.executeUpdate();
}
if(printSql())
logger.warn("update_sql[" + sql + "]");
} catch (Exception e)
{
isSuccess=false;
logger.error("print msgsql error[" + e.getMessage() + "]");
}
logger.warn("read data from result completed successfully");
if (stmt != null)
{
stmt.close();
}
return isSuccess;
} catch (ZXYWException e)
{
logger.error("Error Exception occurred while checking :", e);
throw new WebException(e.getMessage());
} catch (Exception e)
{
logger.error("Error Exception occurred while checking :", e);
throw new WebException(e.getMessage());
} finally
{
logger.warn("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
yw.dbfree(conn, connstr);
}
} catch (Exception e)
{
logger.error("Error Exception occurred while checking :", e);
throw new WebException(e.getMessage());
}
}
}
/**
* 根据序列名称获取序列的下个值
* @param seqName 序列名称
* @return
*/
public static String getSeqNextValue(String seqName){
List<Map<String, String>> list=new ArrayList<Map<String,String>>();
StringBuffer stringBuffer=new StringBuffer("select ");
stringBuffer.append(dbSCP);
stringBuffer.append(seqName);
stringBuffer.append(".nextval from dual");
String nextValueString="";
try {
list=DBUtil.doQuery(stringBuffer.toString(),null);
if(list.size() > 0){
nextValueString = list.get(0).get("nextval");
}
if(printSql())
logger.info(seqName+".nextval :"+nextValueString);
} catch (WebException e) {
// TODO Auto-generated catch block
logger.info("get "+seqName+".nextval failed");
e.printStackTrace();
}
return nextValueString;
}
/**
* 获取序列后不够位数，左边默认用 0 补齐
* @param seqName
* @param length
* @return
*/
public static String getSeqNextValue(String seqName,int length){
String nextValueString=getSeqNextValue(seqName);
int nextValueStringLength=nextValueString.length();
if(length < nextValueStringLength){
if(printSql())
logger.info("the "+seqName+".nextval's length"+nextValueString.length()+" is longer than you give "+length);
return "the "+seqName+".nextval's length"+nextValueString.length()+" is longer than you give "+length;
}else{
for (int i = 0; i < length - nextValueStringLength; i++) {
nextValueString = "0"+nextValueString;
}
return nextValueString;
}
}
/**
* 获取流水号 时间戳＋4位序号。时间戳格式 yyyyMMDDH24miss 序号顺序生成
* @return
*/
public static String getStreamingNo(){
Calendar cal = Calendar.getInstance();
cal.setLenient(true);
Date da = cal.getTime();
String nowDate = new SimpleDateFormat("yyyyMMddHHmmss").format(da);
String sequence = getSeqNextValue("STREAMINGNO", 4);
return (nowDate+sequence);
}
/**
* 通过国家名称查询国码
*/
public static String findCodeByName(String countryName){
String result="";
StringBuffer sql = new StringBuffer();
sql.append(" select countrycode from ").append(dbUMAP).append("v_aspump_service_country");
if(countryName!=null && !"".equals(countryName))
{
sql.append( " where countryName='"+countryName+"'");
}
List<Object> listPrarm = new ArrayList<Object>();
// listPrarm.add(countryName);
try{
result = DBUtil.doQuery(sql.toString(),listPrarm).get(0).get("countrycode");
}catch(WebException e){
e.printStackTrace();
}
return result;
}
// /**
// * 公共调用存储过程
// * @param procName 存储过程名称
// * @param outParam 输出参数
// * @param inParams 输入参数列表
// * @return ProcReturn 输出参数对象
// */
// public static ProcReturn doExecProc(String procName, ProcReturn outParam ,Object ...inParams) throws WebException{
//
// logger.warn("start collect...");
// logger.warn("calling parameters: inParams[" + inParams + "]");
//
//
// int ret = -1;
// Connection conn = null;
// CallableStatement stmt = null;
// ResultSet result = null;
// StringBuffer sql = new StringBuffer("");
// String connstr = "";
//
// try
// {
// if (ywaccess.Ver_DB == 1)
// { // sybase
// sql = sql.append("exec " + dbSCP);
// } else if (ywaccess.Ver_DB == 2)
// { // oracle
// sql = sql.append("{call " + dbSCP);
// }
// sql.append(procName+"(");
// for (int i = 0 ; i<inParams.length;i++) {
// sql.append("?,");
// }
// sql.append("?,?) }");
//
// System.out.println("===="+sql);
// connstr = yw.getDefScpid();
// logger.info("start creating database connection....");
// conn = yw.dbconn(connstr);
// logger.info("create database connection completed successfully");
// stmt = yw.getCallStmt(conn, sql.toString());
// for (int i = 0 ; i<inParams.length;i++) {
// stmt.setString(i+1,(String)inParams[i]);
// }
// stmt.registerOutParameter(inParams.length+1, Types.VARCHAR);
// stmt.registerOutParameter(inParams.length+2, Types.VARCHAR);
// Object[] params = inParams;
// try
// {
// ywaccess yw = new ywaccess();
// String msg = "";
// msg = yw.getPreparedSQL(sql.toString(), params);
// logger.warn("getcollect_sql[" + msg + "]");
// } catch (Exception e)
// {
// logger.error("print msgsql error[" + e.getMessage() + "]");
// }
// if (ywaccess.Ver_DB == 1)
// { // sybase
// result = stmt.executeQuery();
// logger.warn("execute SQL completed successfully,start reading data from result...");
//
// if (result.next())
// {
// ret = result.getInt(1);
// }
// } else if (ywaccess.Ver_DB == 2)
// { // oracle
// result = stmt.executeQuery();
// logger.warn("execute SQL completed successfully,start reading data from result...");
// outParam.setResultcode(stmt.getString(inParams.length+1));
// outParam.setResultmsg(stmt.getString(inParams.length+2));
// }
// logger.warn("read data from result completed successfully");
// if (result != null)
// {
// result.close();
// }
// if (stmt != null)
// {
// stmt.close();
// }
// logger.warn("return ret[" + ret + "]");
// return outParam;
// } catch (ZXYWException e)
// {
// logger.error("Error Exception occurred while collect", e);
// throw new WebException("20002");
// } catch (SQLException e)
// {
// logger.error("Error Exception occurred while collect," + e.getMessage(), e);
// // 任务执行失败，请与系统管理员联系20002
// throw new WebException("20002");
// } finally
// {
// logger.info("start releasing connection...");
// try
// {
// if (stmt != null)
// {
// stmt.close();
// }
// } catch (Exception e)
// {
// stmt = null;
// }
// try
// {
// if (conn != null)
// {
// yw.dbfree(conn, connstr);
// }
// } catch (Exception e)
// {
// logger.error("Error Exception occurred while collect," + e.getMessage());
// // 任务执行失败，请与系统管理员联系20002
// throw new WebException("20002");
// }
// logger.info("release connection completed successfully");
// logger.warn("collect completed.");
// }
// }
public static void main(String[] args) {
try {
//测试查询
// List<Map<String,String>> list=DBUtils.doQuery("select EAName EANAME from "+ywaccess.dbSCP+"ea where eaprovidername= '111'");
// for(int i=0;i<list.size();i++){
// System.out.println(list.get(i).get("eaname"));
// }
// 测试更新
// boolean flag=DBUtils.doUpdate("update "+ywaccess.dbSCP+"application set app_type='22223333' where app_id='11111111'");
// System.out.println(flag);
// DBUtils.doUpdate("delete from "+ywaccess.dbSCP+"application where app_id='1'");
// System.out.println(DBUtil.doQuery("select count(1) from "+ywaccess.dbSCP+"developer"));;
// List<Map<String,String>> list=DBUtils.handlePage(new Page(1,2), "select EAName EANAME from "+ywaccess.dbSCP+"ea", true);
// System.out.println(list.size());
// System.out.println(DBUtil.getSeqNextValue("aspdev_aspidseq",12));
System.out.println(DBUtil.findCodeByName("Mali"));
// Application application = new Application();
// application.setAppid("1'1'1'");
// application.setAppname("1");
// application.setStatus("1");
// application.setWorkflow("1");
// application.setWorkflowstatus("2");
// application.setDescription("fasdfasd&gg1111ggg");
// application.setCapabilityflag("1");
// application.setApplicationcategory("fdsafa");
// application.setRegisterday("fdaf");
// application.setAspid("aspd");
// application.setFlag("1");
// application.setImgurl("fdsafd");
// AppServiceArea appServiceArea = new AppServiceArea();
// appServiceArea.setAppid("123");
// appServiceArea.setServiceCountry("234");
// List<AppServiceArea> appServiceAreas = new ArrayList<AppServiceArea>();
// appServiceAreas.add(appServiceArea);
// application.setAppServiceAreaList(appServiceAreas);
// new DBUtil().applyApp(application);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
ywaccess.java
package com.zte.aspportal.comm.db;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Map;
import org.apache.log4j.Logger;
import com.zte.aspportal.comm.exception.WebException;
import com.zte.zxywpub.ZXYWException;
import com.zte.zxywpub.zxdb_access;
public class ywaccess extends zxdb_access
{
public static final Logger logger = Logger.getLogger(ywaccess.class);
// These vars you can change,but you must javac after change.
protected static final int SYSUSERTYPE = 4;
protected static String DBMacro04 = "";
protected static String DBMacro05 = "";
protected static String DBMacro06 = "";
protected static String DBMacro12 = "";
protected static String DBMacro13 = "";
protected static String serviceKey = "gsm80";
public static String dbSCP = "aspdev.";
public static String dbUMAP = "zxdbm_umap.";
public static String dbWAP = "wap.";
public static String dbSYS = "zxinsys.";
private static String defScpConn = "133";
public static int Ver_DBTYPE = Ver_DB;
protected static String tblRingGroupInfo = "s50ringgroupinfo0";
protected static String tblTimeCallingNum = "s50timecallingnum0";
private static ArrayList scplist = new ArrayList();
private static Map map = new HashMap();
private static final Object lock = new Object();
private final String CACHNAME_ERRORMESSAGE = "errormessagemap";
private final String CACHNAME_PARA = "para";
private final static String CACHNAME_MIMETYPE = "mimetype";
// 缺省主scp配置的卡前缀
final static String cardprefix = "1112";
/**
* 最大查询记录条数。 这个变量不自动起作用，需要各个方法自己调用。
*/
protected static int maxrows = 5000;
// ywaccess的构建器
public ywaccess()
{
if (Ver_DB == 1)
{ // sybase
DBMacro04 = "isnull(";
DBMacro05 = ",' ')";
DBMacro06 = ",0)";
DBMacro12 = "convert(varchar(10), ";
DBMacro13 = ",102)";
dbSCP = "zxdbg_80.dbo.";
dbUMAP = "zxdbm_umap.dbo.";
dbWAP = "wap.dbo.";
dbSYS = "zxinsys.dbo.";
} else if (Ver_DB == 2)
{ // oracle
DBMacro04 = "nvl(";
DBMacro05 = ",' ')";
DBMacro06 = ",0)";
DBMacro12 = "TO_CHAR(";
DBMacro13 = ",'YYYY.MM.DD')";
}
try
{
defScpConn = getDefScpid();
} catch (WebException e)
{
}
}
/**
* 得到scp服务器的ip地址
*
* @param scpid
* String
* @return String
* @throws ZXYWException
*/
public String getScpIP(String scpid) throws ZXYWException
{
logger.warn("start getting scp ip...");
logger.warn("calling parameters: scpid[" + scpid + "]");
Connection conn = null; // 数据库连接
PreparedStatement stmt = null;
ResultSet rs = null;
String bRet = "";
String sSql = "select distinct ipaddr from zxinsys." + DBMacro01 + "zxin_device where deviceid=" + scpid;
try
{
logger.info("start creating database connection....");
conn = dbconn(smpconnstr);
logger.info("create database connection completed successfully");
stmt = getPreStmt(conn, sSql);
logger.warn("start executing SQL:" + sSql);
rs = executeQuery(stmt);
logger.warn("execute SQL completed successfully！start reading data from result...");
if (rs.next())
{
bRet = rs.getString(1).trim();
}
logger.warn("read data from result completed successfully");
rs.close();
} catch (Exception es)
{
Strmsg = "Qu get op Pwd:" + es.getMessage();
// if == 1)
// {
Strmsg = Strmsg + " ErrQu: " + sSql;
// }
logger.error("Error Exception occurred while getting scp ip," + Strmsg, es);
throw new ZXYWException("zxdb_access.getZXPwd(): " + Strmsg);
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
}
try
{
if (conn != null)
{
dbfree(conn, smpconnstr);
}
} catch (Exception e)
{
logger.error("Error Exception occurred while getting scp ip," + Strmsg, e);
throw new ZXYWException("zxdb_access.getDEVSmp(): " + Strmsg);
}
logger.info("release connection completed successfully");
logger.warn("getting scp ip completed.");
}
logger.warn("return String[" + bRet + "]");
return bRet;
}
/**
* 获取某用户所在的scp
*
* @param usernumber
* String
* @return String
* @throws WebException
*/
public String getScpid(String usernumber) throws WebException
{
logger.warn("start getting scp id...");
logger.warn("calling parameters: userNumber[" + usernumber + "]");
String tmp = "";
try
{
tmp = super.getScpid(serviceKey, usernumber);
} catch (Exception e)
{
logger.error("Error Exception occurred while getting scp id," + e.getMessage(), e);
throw new WebException(e.getMessage());
}
if (tmp == null || tmp.equals(""))
{
tmp = defScpConn;
}
if (tmp == null || tmp.equals(""))
{
logger.error("Error Exception occurred while getting scp id");
// 系统没有该号码的号段！20003
throw new WebException("20003");
}
logger.warn("getting scp id completed.");
logger.warn("return String[" + tmp + "]");
return tmp;
}
/**
* 获取scp列表，在第一次获取时将主scp排在第一位
*
* @return ArrayList
* @throws WebException
*/
public ArrayList getScpList() throws WebException
{
logger.warn("start getting scp list...");
if (scplist != null)
{
if (scplist.size() > 0)
{
return scplist;
}
}
try
{
scplist = super.getScplist(serviceKey);
} catch (Exception e)
{
logger.error("Error Exception occurred while getting scp list," + e.getMessage(), e);
throw new WebException(e.getMessage());
}
if (scplist == null || scplist.size() == 0)
{
logger
.error("Error Exception occurred while getting scp list,cannot find scp,please make sure business is load");
// 系统没有配置号段！20005
throw new WebException("20005");
}
// 获取主scp
String scpdef = this.getDefScpid();
if (!scpdef.equals(""))
{
ArrayList scpnew = new ArrayList();
scpnew.add(scpdef);
for (int i = 0; i < scplist.size(); i++)
{
String tmp = (String) scplist.get(i);
if (!tmp.equals(scpdef))
{
scpnew.add(tmp);
}
}
scplist = scpnew;
}
logger.warn("getting scp list completed.");
if (scplist != null)
{
logger.warn("return ArrayList[" + scplist.size() + "]");
}
return scplist;
}
/**
* 公用函数,获取smp上ser_pstn51_maxindex表中的索引值
*
* @param bflag
* 索引序号
* @return int 索引值
* @exception WebException
* 数据库错误，抛出此异常。
* @author mgb
* @version 2004-07-27
*/
public int getMaxIndex(int bflag) throws WebException, ZXYWException
{
logger.warn("start getting max index...");
logger.warn("calling parameters: bflag[" + bflag + "]");
Connection conn = null;
CallableStatement stmt = null;
ResultSet result = null;
int ret = 0;
String connstr = "";
try
{
String sql = "";
if (super.Ver_DB == 1)
{ // sybase
sql = "exec " + DBsmp + "sp_pstn51_getmaxvalue ?,?,?";
} else if (super.Ver_DB == 2)
{ // oracle
sql = "{call " + DBsmp + "sp_pstn51_getmaxvalue(?,?,?)}";
}
connstr = smpconnstr;
logger.info("start creating database connection....");
conn = super.dbconn(connstr);
logger.info("create database connection completed successfully");
logger.warn("start executing SQL:" + sql);
stmt = super.getCallStmt(conn, sql);
logger.warn("execute SQL completed successfully！start reading data from result...");
stmt.setInt(1, bflag);
stmt.setInt(2, 1);
stmt.registerOutParameter(3, java.sql.Types.INTEGER);
logger.warn("start executing SQL:" + sql + "[" + bflag + "[1]");
if (super.Ver_DB == 1)
{ // sybase
result = stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
if (result.next())
{
ret = result.getInt(1);
}
} else if (super.Ver_DB == 2)
{ // oracle
stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
ret = stmt.getInt(3);
}
logger.warn("read data from result completed successfully");
if (result != null)
{
result.close();
}
if (stmt != null)
{
stmt.close();
}
if (ret == 0)
{
logger.error("Error Exception occurred while getting max index,smp gex index error");
// smp获取索引失败20006
throw new WebException("20006");
}
} catch (WebException e)
{
logger.error("Error Exception occurred while getting max index", e);
throw e;
} catch (ZXYWException e)
{
logger.error("Error Exception occurred while getting max index", e);
throw e;
} catch (SQLException e)
{
logger.error("Error Exception occurred while getting max index," + e.getMessage(), e);
// 任务执行失败，请与系统管理员联系！20002
throw new WebException("20002");
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
super.dbfree(conn, connstr);
}
} catch (Exception e)
{
logger.error("Error Exception occurred while getting max index," + e.getMessage(), e);
throw new WebException(e.getMessage());
}
logger.info("release connection completed successfully");
logger.warn("getting max index completed.");
}
logger.warn("return int[" + ret + "]");
return ret;
}
/**
* 公用函数,获取smp上ser_pstn51_maxindex表中的索引值
*
* @param bflag
* 索引序号
* @return int 索引值
* @exception WebException
* 数据库错误，抛出此异常。
* @author gs 为在一级分类已有铃音的情况下增加二级分类专用
* @version 2006-06-13
*/
public int getMaxIndex1(int bflag) throws WebException, ZXYWException
{
logger.warn("start getting max index...");
logger.warn("calling parameters: bflag[" + bflag + "]");
Connection conn = null;
CallableStatement stmt = null;
ResultSet result = null;
int ret = 0;
String connstr = "";
try
{
String sql = "";
if (super.Ver_DB == 1)
{ // sybase
sql = "exec " + DBsmp + "sp_pstn51_getmaxvalue ?,?,?";
} else if (super.Ver_DB == 2)
{ // oracle
sql = "{call " + DBsmp + "sp_pstn51_getmaxvalue(?,?,?)}";
}
connstr = smpconnstr;
logger.info("start creating database connection....");
conn = super.dbconn(connstr);
logger.info("create database connection completed successfully");
logger.warn("start executing SQL:" + sql);
stmt = super.getCallStmt(conn, sql);
logger.warn("execute SQL completed successfully！start reading data from result...");
stmt.setInt(1, bflag);
stmt.setInt(2, 10);
stmt.registerOutParameter(3, java.sql.Types.INTEGER);
logger.warn("start executing SQL:" + sql + "[" + bflag + "][10]");
if (super.Ver_DB == 1)
{ // sybase
result = stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
if (result.next())
{
ret = result.getInt(1);
}
} else if (super.Ver_DB == 2)
{ // oracle
stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
ret = stmt.getInt(3);
}
logger.warn("read data from result completed successfully");
if (result != null)
{
result.close();
}
if (stmt != null)
{
stmt.close();
}
if (ret == 0)
{
logger.error("Error Exception occurred while getting max index,smp get index error");
// smp获取索引失败20006
throw new WebException("20006");
}
} catch (WebException e)
{
logger.error("Error Exception occurred while getting max index", e);
throw e;
} catch (ZXYWException e)
{
logger.error("Error Exception occurred while getting max index", e);
throw e;
} catch (SQLException e)
{
logger.error("Error Exception occurred while getting max index," + e.getMessage(), e);
// 任务执行失败，请与系统管理员联系！20002
throw new WebException("20002");
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
super.dbfree(conn, connstr);
}
} catch (Exception e)
{
logger.error("Error Exception occurred while getting max index," + e.getMessage(), e);
throw new WebException(e.getMessage());
}
logger.info("release connection completed successfully");
logger.warn("getting max index completed.");
}
logger.warn("return int[" + ret + "]");
return ret;
}
/**
* 公用函数,获取主scp上s50maxinde表中的索引值
*
* @param bflag
* 索引序号
* @return int 索引值
* @exception WebException
* 数据库错误，抛出此异常。
* @author mgb
* @version 2004-07-27
*/
public int getScpMaxIndex(int bflag) throws WebException, ZXYWException
{
logger.warn("start getting scp max index...");
logger.warn("calling parameters: bflag[" + bflag + "]");
Connection conn = null;
CallableStatement stmt = null;
ResultSet result = null;
int ret = 0;
String connstr = "";
try
{
String sql = "";
if (super.Ver_DB == 1)
{ // sybase
sql = "exec " + dbSCP + "sp_imp_getmaxvalue ?,?,?";
} else if (super.Ver_DB == 2)
{ // oracle
sql = "{call " + dbSCP + "sp_imp_getmaxvalue(?,?,?)}";
}
connstr = this.getDefScpid();
logger.info("start creating database connection....");
conn = super.dbconn(connstr);
logger.info("create database connection completed successfully");
logger.warn("start executing SQL:" + sql);
stmt = super.getCallStmt(conn, sql);
logger.warn("execute SQL completed successfully！start reading data from result...");
stmt.setInt(1, bflag);
stmt.setInt(2, 1);
stmt.registerOutParameter(3, java.sql.Types.INTEGER);
logger.warn("start executing SQL:" + sql + "[" + bflag + "][1]");
if (super.Ver_DB == 1)
{ // sybase
result = stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
if (result.next())
{
ret = result.getInt(1);
}
} else if (super.Ver_DB == 2)
{ // oracle
stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
ret = stmt.getInt(3);
}
logger.warn("read data from result completed successfully");
if (result != null)
{
result.close();
}
if (stmt != null)
{
stmt.close();
}
if (ret == 0)
{
logger.error("Error Exception occurred while getting scp max index,scp get index error");
// scp获取索引失败20007
throw new WebException("20007");
}
} catch (WebException e)
{
logger.error("Error Exception occurred while getting scp max index", e);
throw e;
} catch (ZXYWException e)
{
logger.error("Error Exception occurred while getting scp max index", e);
throw e;
} catch (SQLException e)
{
logger.error("Error Exception occurred while getting scp max index," + e.getMessage(), e);
throw new WebException("任务执行失败，请与系统管理员联系！");
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
super.dbfree(conn, connstr);
}
} catch (Exception e)
{
logger.error("Error Exception occurred while getting scp max index," + e.getMessage(), e);
throw new WebException(e.getMessage());
}
logger.info("release connection completed successfully");
logger.warn("getting scp max index completed.");
}
logger.warn("return int[" + ret + "]");
return ret;
}
/**
* 公用函数,采用存储过程在各scp上执行
*
* @param sql
* --------------可执行的存储过程字符串
* @param ArrayList
* --------存储过程的参数值 <br>
* 入参ArrayList的对象hashtable <br>
* <i>type--------数据类型 0：int 其余：string</i> <br>
* <i>value-------数据值/i>
* @return ArrayList 返回各scp的执行情况，如果第一个scp执行失败，则直接退出 <br>
* <i>scp--------scp</i> <br>
* <i>flag-------操作标志/i> <br>
* <i>reason-----失败原因</i>
* @author mgb
* @version 2004-07-27
*/
public ArrayList upDateAllScp(String sSql, ArrayList paralist) throws WebException
{
logger.warn("start updateing all scp...");
logger.warn("calling parameters: sSql[" + sSql + "]");
Connection conn = null;
CallableStatement stmt = null;
ResultSet result = null;
String connstr = "";
ArrayList vet = new ArrayList();
int ret = -1;
Hashtable hash = null;
Hashtable tmp = null;
String failres = "";
ArrayList scplist = null;
try
{
scplist = getScpList();
} catch (Exception e)
{
logger.error("Error Exception occurred while updateing all scp," + e.getMessage(), e);
throw new WebException(e.getMessage());
}
for (int j = 0; j < scplist.size(); j++)
{
connstr = (String) scplist.get(j);
failres = "";
ret = -1;
try
{
logger.info("start creating database connection....");
conn = super.dbconn(connstr);
logger.info("create database connection completed successfully");
stmt = super.getCallStmt(conn, sSql);
int i = 0;
String params = "";
for (i = 0; i < paralist.size(); i++)
{
hash = (Hashtable) paralist.get(i);
params += (String) hash.get("value") + ";";
if (((String) hash.get("type")).equals("0"))
{
stmt.setInt(i + 1, Integer.parseInt((String) hash.get("value")));
} else
{
stmt.setString(i + 1, (String) hash.get("value"));
}
}
logger.warn("updateing all scp input params:" + params);
i = i + 1;
stmt.registerOutParameter(i, java.sql.Types.INTEGER);
ret = -1;
logger.warn("start executing SQL:" + sSql);
if (super.Ver_DB == 1)
{ // sybase
result = stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
if (result.next())
{
ret = result.getInt(1);
}
} else if (super.Ver_DB == 2)
{ // oracle
stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
ret = stmt.getInt(i);
}
logger.warn("updateing all scp output params:" + ret);
logger.warn("read data from result completed successfully");
if (result != null)
{
result.close();
}
if (stmt != null)
{
stmt.close();
}
if (ret > 0)
{
failres = this.getStrmsg(ret);
}
} catch (ZXYWException e)
{
failres = e.getMessage();
} catch (SQLException e)
{
logger.info("Error Exception occurred while updateing all scp,", e);
// 数据库执行失败，请与系统管理员联系！20008
failres = GetResourceStr.getResourceStr("20008");
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
super.dbfree(conn, connstr);
}
} catch (Exception e)
{
}
logger.info("release connection completed successfully");
}
// 填写日志
// if (!failres.equals("") && super.Debug > 0)
// {
String sSqlLog = getSqlLog(sSql, paralist);
logger.warn("[" + connstr + "]excute：" + sSqlLog + " result：" + failres + "[" + ret + "]");
// }
tmp = new Hashtable();
tmp.put("scp", connstr);
tmp.put("result", ret + "");
tmp.put("reason", failres);
vet.add(tmp);
if (ret < 0 || (j == 0 && ret > 0))
{ // 第一个scp操作出错则退出系统;
break;
}
}
logger.warn("updateing all scp completed.");
if (vet != null)
{
logger.warn("return Vector[" + vet.size() + "]");
}
return vet;
}
/**
* 公用函数,采用存储过程在各scp上执行
*
* @param sql
* --------------可执行的存储过程字符串
* @param ArrayList
* --------存储过程的参数值 <br>
* 入参ArrayList的对象hashtable <br>
* <i>type--------数据类型 0：int 其余：string</i> <br>
* <i>value-------数据值/i>
* @return ArrayList 返回各scp的执行情况，如果第一个scp执行失败，则直接退出 <br>
* <i>scp--------scp</i> <br>
* <i>flag-------操作标志/i> <br>
* <i>reason-----失败原因</i>
* @author mgb
* @version 2004-07-27
*/
public boolean AddAllGroupScp(String sSql, ArrayList paralist) throws WebException
{
logger.warn("start adding all group scp with calling parameters: sSql[" + sSql + "] and following paralist...");
logger.warn(paralist);
Connection conn = null;
CallableStatement stmt = null;
ResultSet result = null;
String connstr = "";
int ret = -1;
boolean bflag = true;
Hashtable hash = null;
String failres = "";
ArrayList scplist = null;
try
{
scplist = getScpList();
} catch (Exception e)
{
logger.error("Error Exception occurred while adding all group scp," + e.getMessage(), e);
throw new WebException(e.getMessage());
}
for (int j = 0; j < scplist.size(); j++)
{
connstr = (String) scplist.get(j);
ret = -1;
try
{
logger.info("start creating database connection....");
conn = super.dbconn(connstr);
logger.info("create database connection completed successfully");
stmt = super.getCallStmt(conn, sSql);
int i = 0;
for (i = 0; i < paralist.size(); i++)
{
hash = (Hashtable) paralist.get(i);
if (((String) hash.get("type")).equals("0"))
{
stmt.setInt(i + 1, Integer.parseInt((String) hash.get("value")));
} else
{
stmt.setString(i + 1, (String) hash.get("value"));
}
}
i = i + 1;
stmt.registerOutParameter(i, java.sql.Types.INTEGER);
ret = -1;
logger.warn("start executing SQL:" + sSql);
if (super.Ver_DB == 1)
{ // sybase
result = stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
if (result.next())
{
ret = result.getInt(1);
}
} else if (super.Ver_DB == 2)
{ // oracle
stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
ret = stmt.getInt(i);
}
logger.warn("read data from result completed successfully");
if (result != null)
{
result.close();
}
if (stmt != null)
{
stmt.close();
}
if (ret > 0)
{
failres = this.getStrmsg(ret);
logger.error("Error Exception occurred while adding all group scp,[" + ret + "]" + failres);
throw new WebException(connstr + ":" + failres);
}
} catch (ZXYWException e)
{
failres = e.getMessage();
logger.error("Error Exception occurred while adding all group scp," + failres, e);
throw new WebException(failres);
} catch (SQLException e)
{
// "数据库执行失败，请与系统管理员联系！"20008
failres = connstr + ":" + GetResourceStr.getResourceStr("20008");
logger.error("Error Exception occurred while adding all group scp," + failres + "," + e.getMessage());
throw new WebException(failres);
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
super.dbfree(conn, connstr);
}
} catch (Exception e)
{
logger.warn("return boolean[false]");
return false;
}
logger.info("release connection completed successfully");
logger.warn("adding all group scp completed.");
}
if (ret < 0)
{
bflag = false;
break;
}
}
logger.warn("return boolean[" + bflag + "]");
return bflag;
}
/**
* 公用函数,采用存储过程在各scp上执行
*
* @param sql
* --------------可执行的存储过程字符串
* @param ArrayList
* --------存储过程的参数值 <br>
* 入参ArrayList的对象hashtable <br>
* <i>type--------数据类型 0：int 其余：string</i> <br>
* <i>value-------数据值/i>
* @return ArrayList 返回各scp的执行情况，如果有一个scp执行失败，则直接退出 <br>
* <i>scp--------scp</i> <br>
* <i>flag-------操作标志/i> <br>
* <i>reason-----失败原因</i>
* @author mgb
* @version 2004-07-27
*/
public boolean delAllGroupScp(String sSql, ArrayList paralist) throws WebException
{
logger.warn("start deleting all group scp...");
logger.warn("calling parameters: sSql[" + sSql + "]");
Connection conn = null;
CallableStatement stmt = null;
ResultSet result = null;
String connstr = "";
ArrayList vet = new ArrayList();
int ret = -1;
boolean bflag = true;
Hashtable hash = null;
Hashtable tmp = null;
String failres = "";
ArrayList scplist = null;
try
{
scplist = getScpList();
} catch (Exception e)
{
logger.error("Error Exception occurred while deleting all group scp," + e.getMessage(), e);
throw new WebException(e.getMessage());
}
for (int j = 0; j < scplist.size(); j++)
{
connstr = (String) scplist.get(j);
failres = "";
ret = -1;
try
{
logger.info("start creating database connection....");
conn = super.dbconn(connstr);
logger.info("create database connection completed successfully");
stmt = super.getCallStmt(conn, sSql);
int i = 0;
for (i = 0; i < paralist.size(); i++)
{
hash = (Hashtable) paralist.get(i);
if (((String) hash.get("type")).equals("0"))
{
stmt.setInt(i + 1, Integer.parseInt((String) hash.get("value")));
} else
{
stmt.setString(i + 1, (String) hash.get("value"));
}
}
i = i + 1;
stmt.registerOutParameter(i, java.sql.Types.INTEGER);
ret = -1;
logger.warn("start executing SQL:" + sSql);
if (super.Ver_DB == 1)
{ // sybase
result = stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
if (result.next())
{
ret = result.getInt(1);
}
} else if (super.Ver_DB == 2)
{ // oracle
stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
ret = stmt.getInt(i);
}
logger.warn("read data from result completed successfully");
if (result != null)
{
result.close();
}
if (stmt != null)
{
stmt.close();
}
if (ret > 0 && ret != 9301)
{
failres = this.getStrmsg(ret);
logger.error("Error Exception occurred while deleting all group scp," + connstr + ":" + failres);
throw new WebException(connstr + ":" + failres);
}
} catch (ZXYWException e)
{
failres = connstr + ":" + e.getMessage();
logger.error("Error Exception occurred while deleting all group scp," + failres, e);
throw new WebException(failres);
} catch (SQLException e)
{
logger.error("Error Exception occurred while deleting all group scp," + e.getMessage(), e);
// "数据库执行失败！"20009
throw new WebException(connstr + GetResourceStr.getResourceStr("20009"));
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
super.dbfree(conn, connstr);
}
} catch (Exception e)
{
logger.warn("return boolean[false]");
return false;
}
logger.info("release connection completed successfully");
logger.warn("deleting all group scp completed.");
}
if (ret < 0)
{
bflag = false;
break;
}
}
logger.warn("return boolean[" + bflag + "]");
return bflag;
}
/**
* 公用函数,采用存储过程在各scp上执行
*
* @param sql
* --------------可执行的存储过程字符串
* @param ArrayList
* --------存储过程的参数值 <br>
* 入参ArrayList的对象hashtable <br>
* <i>type--------数据类型 0：int 其余：string</i> <br>
* <i>value-------数据值/i>
* @return ArrayList 返回各scp的执行情况，如果第一个scp执行失败，则直接退出 <br>
* <i>scp--------scp</i> <br>
* <i>flag-------操作标志/i> <br>
* <i>reason-----失败原因</i>
* @author mgb
* @version 2004-07-27
*/
public ArrayList upDateAllScpForFee(int opcode, String sSql, ArrayList paralist) throws WebException
{
logger.warn("start updating all scp for fee...");
logger.warn("calling parameters: opcode[" + opcode + "]sSql[" + sSql + "]");
Connection conn = null;
CallableStatement stmt = null;
ResultSet result = null;
String connstr = "";
ArrayList vet = new ArrayList();
int ret = -1;
Hashtable hash = null;
Hashtable tmp = null;
String failres = "";
ArrayList scplist = null;
try
{
scplist = getScpList();
} catch (Exception e)
{
logger.error("Error Exception occurred while updating all scp for fee," + e.getMessage(), e);
throw new WebException(e.getMessage());
}
for (int j = 0; j < scplist.size(); j++)
{
connstr = (String) scplist.get(j);
if (opcode == 1)
{
if (connstr.equals(getDefScpid()))
{
failres = "";
tmp = new Hashtable();
tmp.put("scp", connstr);
tmp.put("result", "0");
tmp.put("reason", failres);
vet.add(tmp);
continue;
}
}
failres = "";
ret = -1;
try
{
logger.info("start creating database connection....");
conn = super.dbconn(connstr);
logger.info("create database connection completed successfully");
stmt = super.getCallStmt(conn, sSql);
int i = 0;
for (i = 0; i < paralist.size(); i++)
{
hash = (Hashtable) paralist.get(i);
if (((String) hash.get("type")).equals("0"))
{
stmt.setInt(i + 1, Integer.parseInt((String) hash.get("value")));
} else
{
stmt.setString(i + 1, (String) hash.get("value"));
}
}
i = i + 1;
stmt.registerOutParameter(i, java.sql.Types.INTEGER);
stmt.registerOutParameter(i + 1, java.sql.Types.INTEGER);
ret = -1;
logger.warn("start executing SQL:" + sSql);
if (super.Ver_DB == 1)
{ // sybase
result = stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
if (result.next())
{
ret = result.getInt(1);
}
} else if (super.Ver_DB == 2)
{ // oracle
stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
ret = stmt.getInt(i);
}
logger.warn("read data from result completed successfully");
if (result != null)
{
result.close();
}
if (stmt != null)
{
stmt.close();
}
if (ret > 0)
{
failres = this.getStrmsg(ret);
}
} catch (ZXYWException e)
{
failres = e.getMessage();
} catch (SQLException e)
{
logger.info("Error Exception occurred while updating all scp for fee,", e);
// "数据库执行失败，请与系统管理员联系！"20008
failres = GetResourceStr.getResourceStr("20008");
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
super.dbfree(conn, connstr);
}
} catch (Exception e)
{
}
logger.info("release connection completed successfully");
}
// 填写日志
// if (!failres.equals("") && super.Debug > 0)
// {
String sSqlLog = getSqlLog(sSql, paralist);
logger.warn("[" + connstr + "]excute：" + sSqlLog + " result：" + failres + "[" + ret + "]");
// }
tmp = new Hashtable();
tmp.put("scp", connstr);
tmp.put("result", ret + "");
tmp.put("reason", failres);
vet.add(tmp);
if (ret < 0 || (j == 0 && ret > 0))
{ // 第一个scp操作出错则退出系统;
break;
}
}
if (vet != null)
{
logger.warn("return ArrayList[" + vet.size() + "]");
}
logger.warn("updating all scp for fee completed.");
return vet;
}
/**
* 公用函数,采用存储过程在各scp上执行
*
* @param sql
* --------------可执行的存储过程字符串
* @param ArrayList
* --------存储过程的参数值 <br>
* 入参ArrayList的对象hashtable <br>
* <i>type--------数据类型 0：int 其余：string</i> <br>
* <i>value-------数据值/i>
* @return ArrayList 返回各scp的执行情况，如果第一个scp执行失败，则直接退出 <br>
* <i>scp--------scp</i> <br>
* <i>flag-------操作标志/i> <br>
* <i>reason-----失败原因</i>
* @author liuxj
* @version 2008-09-25
*/
public ArrayList upDateAllScpForCP(int opcode, String sSql, ArrayList paralist) throws WebException
{
logger.warn("start updating all scp for cp...");
logger.warn("calling parameters: opcode[" + opcode + "]sSql[" + sSql + "]");
Connection conn = null;
CallableStatement stmt = null;
ResultSet result = null;
String connstr = "";
ArrayList vet = new ArrayList();
int ret = -1;
Hashtable hash = null;
Hashtable tmp = null;
String failres = "";
ArrayList scplist = null;
try
{
scplist = getScpList();
} catch (Exception e)
{
logger.error("Error Exception occurred while updating all scp for cp," + e.getMessage(), e);
throw new WebException();
}
for (int j = 0; j < scplist.size(); j++)
{
connstr = (String) scplist.get(j);
if (opcode == 1)
{
if (connstr.equals(getDefScpid()))
{
failres = "";
tmp = new Hashtable();
tmp.put("scp", connstr);
tmp.put("result", "0");
tmp.put("reason", failres);
vet.add(tmp);
continue;
}
}
failres = "";
ret = -1;
try
{
logger.info("start creating database connection....");
conn = super.dbconn(connstr);
logger.info("create database connection completed successfully");
stmt = super.getCallStmt(conn, sSql);
int i = 0;
for (i = 0; i < paralist.size(); i++)
{
hash = (Hashtable) paralist.get(i);
if (((String) hash.get("type")).equals("0"))
{
stmt.setInt(i + 1, Integer.parseInt((String) hash.get("value")));
} else
{
stmt.setString(i + 1, (String) hash.get("value"));
}
}
i = i + 1;
stmt.registerOutParameter(i, java.sql.Types.INTEGER);
stmt.registerOutParameter(i + 1, java.sql.Types.INTEGER);
ret = -1;
logger.warn("start executing SQL:" + sSql);
if (super.Ver_DB == 1)
{ // sybase
result = stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
if (result.next())
{
ret = result.getInt(1);
}
} else if (super.Ver_DB == 2)
{ // oracle
stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
ret = stmt.getInt(i);
}
logger.warn("read data from result completed successfully");
if (result != null)
{
result.close();
}
if (stmt != null)
{
stmt.close();
}
if (ret > 0)
{
failres = this.getStrmsg(ret);
}
} catch (ZXYWException e)
{
failres = e.getMessage();
} catch (SQLException e)
{
logger.info("Error Exception occurred while updating all scp for cp", e);
// "数据库执行失败，请与系统管理员联系！"20008
failres = GetResourceStr.getResourceStr("20008");
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
super.dbfree(conn, connstr);
}
} catch (Exception e)
{
}
logger.info("release connection completed successfully");
}
// 填写日志
// if (!failres.equals("") && super.Debug > 0)
// {
String sSqlLog = getSqlLog(sSql, paralist);
logger.warn("[" + connstr + "]excute：" + sSqlLog + " result：" + failres + "[" + ret + "]");
// }
tmp = new Hashtable();
tmp.put("scp", connstr);
tmp.put("result", ret + "");
tmp.put("reason", failres);
vet.add(tmp);
if (ret < 0 || (j == 0 && ret > 0))
{ // 第一个scp操作出错则退出系统;
break;
}
}
if (vet != null)
{
logger.warn("return ArrayList[" + vet.size() + "]");
}
logger.warn("updating all scp for cp completed.");
return vet;
}
/**
* 公用函数,采用存储过程在各scp上执行
*
* @param opcode
* ----------操作类型 1 增加 2 删除 3 修改
* @param sql
* --------------可执行的存储过程字符串
* @param ArrayList
* --------存储过程的参数值 <br>
* 入参ArrayList的对象hashtable <br>
* <i>type--------数据类型 0：int 其余：string</i> <br>
* <i>value-------数据值/i>
* @return ArrayList 返回各scp的执行情况，如果第一个scp执行失败，则直接退出 <br>
* <i>scp--------scp</i> <br>
* <i>flag-------操作标志/i> <br>
* <i>reason-----失败原因</i>
* @author mgb
* @version 2004-07-27
*/
public ArrayList upDateAllScpforDiscount(int opcode, String sSql, ArrayList paralist) throws WebException
{
logger.warn("start updating all scp for discount...");
logger.warn("calling parameters: opcode[" + opcode + "]sSql[" + sSql + "]");
Connection conn = null;
CallableStatement stmt = null;
ResultSet result = null;
String connstr = "";
ArrayList vet = new ArrayList();
int ret = -1;
Hashtable hash = null;
Hashtable tmp = null;
String failres = "";
ArrayList scplist = null;
try
{
scplist = getScpList();
// 如果是增加，不需要在主scp上再次调用
} catch (Exception e)
{
logger.error("Error Exception occurred while updating all scp for discount," + e.getMessage(), e);
throw new WebException(e.getMessage());
}
for (int j = 0; j < scplist.size(); j++)
{
connstr = (String) scplist.get(j);
if (opcode == 1)
{
if (connstr.equals(getDefScpid()))
{
tmp = new Hashtable();
tmp.put("scp", connstr);
tmp.put("result", "0");
tmp.put("reason", "");
vet.add(tmp);
continue;
}
}
failres = "";
ret = -1;
try
{
logger.info("start creating database connection....");
conn = super.dbconn(connstr);
logger.info("create database connection completed successfully");
stmt = super.getCallStmt(conn, sSql);
int i = 0;
for (i = 0; i < paralist.size(); i++)
{
hash = (Hashtable) paralist.get(i);
if (((String) hash.get("type")).equals("0"))
{
stmt.setInt(i + 1, Integer.parseInt((String) hash.get("value")));
} else
{
stmt.setString(i + 1, (String) hash.get("value"));
}
}
i = i + 1;
stmt.registerOutParameter(i, java.sql.Types.INTEGER); // 返回值
stmt.registerOutParameter(i + 1, java.sql.Types.INTEGER); // 套餐索引号
stmt.registerOutParameter(i + 2, java.sql.Types.VARCHAR); // 套餐ID
ret = -1;
logger.warn("start executing SQL:" + sSql);
if (super.Ver_DB == 1)
{ // sybase
result = stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
if (result.next())
{
ret = result.getInt(1);
}
} else if (super.Ver_DB == 2)
{ // oracle
stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
ret = stmt.getInt(i);
}
logger.warn("read data from result completed successfully");
if (result != null)
{
result.close();
}
if (stmt != null)
{
stmt.close();
}
if (ret > 0)
{
failres = this.getStrmsg(ret);
}
} catch (ZXYWException e)
{
logger.info("Error Exception occurred while updating all scp for discount");
failres = e.getMessage();
} catch (SQLException e)
{
logger.info("Error Exception occurred while updating all scp for discount");
// 数据库执行失败，请与系统管理员联系！20008
failres = GetResourceStr.getResourceStr("20008");
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
super.dbfree(conn, connstr);
}
} catch (Exception e)
{
}
logger.info("release connection completed successfully");
}
// 填写日志
// if (!failres.equals("") && super.Debug > 0)
// {
String sSqlLog = getSqlLog(sSql, paralist);
logger.warn("[" + connstr + "]excute：" + sSqlLog + " result：" + failres + "[" + ret + "]");
// }
tmp = new Hashtable();
tmp.put("scp", connstr);
tmp.put("result", ret + "");
tmp.put("reason", failres);
vet.add(tmp);
if (ret < 0 || (j == 0 && ret > 0))
{ // 第一个scp操作出错则退出系统;
break;
}
}
if (vet != null)
{
logger.warn("return ArrayList[" + vet.size() + "]");
}
logger.warn(" updating all scp for discount completed.");
return vet;
}
/**
* 获取调用的存储过程日志，为upDateAllScp使用
*
* @param sSql
* @param paralist
* @return String
* @author mgb 2004.12.20
*/
private String getSqlLog(String sSql, ArrayList paralist)
{
logger.warn("start getting sql log...");
logger.warn("calling parameters: sSql[" + sSql + "]");
String sSqlLog = "";
Hashtable hash = null;
if (sSql == null || sSql.length() == 0)
{
return sSqlLog;
}
sSqlLog = sSql.substring(0, sSql.indexOf("?"));
for (int i = 0; i < paralist.size(); i++)
{
hash = (Hashtable) paralist.get(i);
String sHead = i == 0 ? " " : ",";
if (((String) hash.get("type")).equals("0"))
{
sSqlLog = sSqlLog + sHead + (String) hash.get("value");
} else
{
sSqlLog = sSqlLog + sHead + "'" + (String) hash.get("value") + "'";
}
}
if (super.Ver_DB == 2)
{ // oracle
sSqlLog = sSqlLog + "}";
}
logger.warn("getting sql log completed.");
logger.warn("return String[" + sSqlLog + "]");
return sSqlLog;
}
/**
* 公用函数,在所有的scp上执行sql语句
*
* @param sql
* --------------可执行的存储过程字符串
* @param ArrayList
* --------存储过程的参数值
* @return ArrayList 返回各scp的执行情况，如果第一个scp执行失败，则直接退出 <br>
* <i>scp--------scp</i> <br>
* <i>flag-------操作标志/i> <br>
* <i>reason-----失败原因</i>
* @author mgb
* @version 2004-07-27
*/
public ArrayList excuteAllScp(String sSql) throws WebException
{
logger.warn("start excuting all scp...");
logger.warn("calling parameters: sSql[" + sSql + "]");
Connection conn = null;
Statement stmt = null;
String connstr = "";
ArrayList vet = new ArrayList();
int ret = 0;
Hashtable hash = null;
Hashtable tmp = null;
String failres = "";
ArrayList scplist = null;
try
{
scplist = getScpList();
} catch (Exception e)
{
logger.error("Error Exception occurred while excuting all scp," + e.getMessage(), e);
throw new WebException(e.getMessage());
}
for (int j = 0; j < scplist.size(); j++)
{
connstr = (String) scplist.get(j);
failres = "";
ret = 0;
try
{
logger.info("start creating database connection....");
conn = super.dbconn(connstr);
logger.info("create database connection completed successfully");
stmt = super.getstmt(conn);
if (super.executeUpdate(sSql, stmt) < 0)
{
ret = -1;
// "数据库执行失败，请与系统管理员联系！"20008
failres = GetResourceStr.getResourceStr("20008");
}
if (stmt != null)
{
stmt.close();
}
} catch (ZXYWException e)
{
logger.info("Error Exception occurred while excuting all scp");
ret = -1;
failres = e.getMessage();
} catch (SQLException e)
{
logger.info("Error Exception occurred while excuting all scp");
ret = -1;
// "数据库执行失败，请与系统管理员联系！"20008
failres = GetResourceStr.getResourceStr("20008");
;
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
super.dbfree(conn, connstr);
}
} catch (Exception e)
{
}
logger.info("release connection completed successfully");
logger.warn("excuting all scp completed.");
}
tmp = new Hashtable();
tmp.put("scp", connstr);
tmp.put("result", ret + "");
tmp.put("reason", failres);
vet.add(tmp);
if (j == 0 && ret > 0)
{ // 第一个scp操作出错则退出系统;
break;
}
}
if (vet != null)
{
logger.warn("return ArrayList[" + vet.size() + "]");
}
return vet;
}
// 获取缺省scp 即为获取deviceid最小scp
public String getDefScpid() throws WebException
{
if (defScpConn != null && !defScpConn.equals(""))
{
return defScpConn;
}
try
{
defScpConn = super.getScpid(serviceKey, cardprefix);
} catch (Exception e)
{
logger.error("Exception occurred while executing getDefScpId method," + e.getMessage(), e);
}
return defScpConn;
}
/**
* 根据铃音类型取s50ringcfg表中铃音id前缀长度限制，替代s50parameters表中第32项配置
*
* @param ringtype
* int
* @return int
* @throws WebException
* @throws ZXYWException
*/
public int getRidPrelen(int ringtype) throws WebException, ZXYWException
{
logger.warn("start getting rid pre len...");
logger.warn("calling parameters: ringtype[" + ringtype + "]");
Connection conn = null;
Statement stmt = null;
ResultSet result = null;
int ret = 0;
String connstr = "";
try
{
connstr = this.getDefScpid();
String sql = "select ridprelen from " + dbSCP + "s50ringcfg where ringtype=" + ringtype;
logger.info("start creating database connection....");
conn = super.dbconn(connstr);
logger.info("create database connection completed successfully");
stmt = super.getstmt(conn);
result = super.executeQuery(sql, stmt);
if (result.next())
{
ret = Integer.parseInt(result.getString(1));
}
logger.warn("read data from result completed successfully");
if (result != null)
{
result.close();
}
stmt.close();
} catch (ZXYWException e)
{
logger.error("Error Exception occurred while getting rid pre len", e);
throw e;
} catch (SQLException e)
{
logger.error("Error Exception occurred while getting rid pre len," + e.getMessage(), e);
// 任务执行失败，请与系统管理员联系！
throw new WebException("20002");
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
// 关闭数据库资源
if (conn != null)
{
super.dbfree(conn, connstr);
}
} catch (Exception e)
{
logger.error("Error Exception occurred while getting rid pre len," + e.getMessage(), e);
// 任务执行失败，请与系统管理员联系！
throw new WebException("20002");
}
logger.info("release connection completed successfully");
logger.warn("getting rid pre len completed.");
}
logger.warn("return int[" + ret + "]");
return ret;
}
/**
* 公用函数,采用存储过程在各scp上执行
*
* @param sql
* --------------可执行的存储过程字符串
* @param ArrayList
* --------存储过程的参数值 <br>
* 入参ArrayList的对象hashtable <br>
* <i>type--------数据类型 0：int 其余：string</i> <br>
* <i>value-------数据值/i>
* @return ArrayList 返回各scp的执行情况，如果第一个scp执行失败，则直接退出 <br>
* <i>scp--------scp</i> <br>
* <i>flag-------操作标志/i> <br>
* <i>reason-----失败原因</i>
* @author huxiao
* @version 2007-01-04
*/
public ArrayList upDateAllScpForModCheckRing(String sSql, ArrayList paralist) throws WebException
{
logger.warn("start updating all scp fro mod check ring...");
logger.warn("calling parameters: sSql[" + sSql + "]");
Connection conn = null;
CallableStatement stmt = null;
ResultSet result = null;
String connstr = "";
ArrayList vet = new ArrayList();
int ret = -1;
Hashtable hash = null;
Hashtable tmp = null;
String failres = "";
ArrayList scplist = null;
try
{
scplist = getScpList();
} catch (Exception e)
{
logger.error("Error Exception occurred while updating all scp fro mod check ring," + e.getMessage(), e);
throw new WebException(e.getMessage());
}
for (int j = 0; j < scplist.size(); j++)
{
connstr = (String) scplist.get(j);
failres = "";
ret = -1;
try
{
logger.info("start creating database connection....");
conn = super.dbconn(connstr);
logger.info("create database connection completed successfully");
stmt = super.getCallStmt(conn, sSql);
int i = 0;
for (i = 0; i < paralist.size(); i++)
{
hash = (Hashtable) paralist.get(i);
if (((String) hash.get("type")).equals("0"))
{
stmt.setInt(i + 1, Integer.parseInt((String) hash.get("value")));
} else
{
stmt.setString(i + 1, (String) hash.get("value"));
}
}
i = i + 1;
stmt.registerOutParameter(i, java.sql.Types.INTEGER);
i = i + 1;
stmt.registerOutParameter(i, java.sql.Types.INTEGER);
ret = -1;
logger.warn("start executing SQL:" + sSql);
if (super.Ver_DB == 1)
{ // sybase
result = stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
if (result.next())
{
ret = result.getInt(1);
}
} else if (super.Ver_DB == 2)
{ // oracle
stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
ret = stmt.getInt(i - 1);
}
logger.warn("read data from result completed successfully");
if (result != null)
{
result.close();
}
if (stmt != null)
{
stmt.close();
}
if (ret > 0)
{
failres = this.getStrmsg(ret);
}
} catch (ZXYWException e)
{
failres = e.getMessage();
} catch (SQLException e)
{
logger.info("Error Exception occurred while updating all scp fro mod check ring,", e);
// 数据库执行失败，请与系统管理员联系！20008
failres = GetResourceStr.getResourceStr("20008");
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
super.dbfree(conn, connstr);
}
} catch (Exception e)
{
}
logger.info("release connection completed successfully");
logger.warn("updating all scp fro mod check ring completed.");
}
// 填写日志
// if (!failres.equals("") && super.Debug > 0)
// {
String sSqlLog = getSqlLog(sSql, paralist);
logger.warn("[" + connstr + "]excute：" + sSqlLog + " result：" + failres + "[" + ret + "]");
// }
tmp = new Hashtable();
tmp.put("scp", connstr);
tmp.put("result", ret + "");
tmp.put("reason", failres);
vet.add(tmp);
if (ret < 0 || (j == 0 && ret > 0))
{ // 第一个scp操作出错则退出系统;
break;
}
}
if (vet != null)
{
logger.warn("return ArrayList[" + vet.size() + "]");
}
return vet;
}
public static String getSysTime() throws WebException
{
logger.warn("start getting system time...");
try
{
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return formatter.format(new java.util.Date());
} catch (Exception e)
{
logger.error("Error Exception occurred while getting system time", e);
// 无法获得系统时间！10009
throw new WebException("10009");
}
}
public static String getSerkey()
{
return serviceKey;
}
// public int getDebug()
// {
// return super.Debug;
// }
public ArrayList upDateAllScpForSP(String sSql, ArrayList paralist) throws WebException
{
logger.warn("start updating all scp for sp...");
logger.warn("calling parameters: sSql[" + sSql + "]");
Connection conn = null;
CallableStatement stmt = null;
ResultSet result = null;
String connstr = "";
ArrayList vet = new ArrayList();
int ret = -1;
Hashtable hash = null;
Hashtable tmp = null;
String failres = "";
ArrayList scplist = null;
try
{
scplist = getScpList();
} catch (Exception e)
{
logger.error("Error Exception occurred while updating all scp for sp," + e.getMessage(), e);
throw new WebException(e.getMessage());
}
for (int j = 0; j < scplist.size(); j++)
{
connstr = (String) scplist.get(j);
failres = "";
ret = -1;
try
{
logger.info("start creating database connection....");
conn = super.dbconn(connstr);
logger.info("create database connection completed successfully");
stmt = super.getCallStmt(conn, sSql);
int i = 0;
for (i = 0; i < paralist.size(); i++)
{
hash = (Hashtable) paralist.get(i);
if (((String) hash.get("type")).equals("0"))
{
stmt.setInt(i + 1, Integer.parseInt((String) hash.get("value")));
} else
{
stmt.setString(i + 1, (String) hash.get("value"));
}
}
i = i + 1;
stmt.registerOutParameter(i, java.sql.Types.INTEGER);
stmt.registerOutParameter(i + 1, java.sql.Types.INTEGER);
ret = -1;
logger.warn("start executing SQL:" + sSql);
if (super.Ver_DB == 1)
{ // sybase
result = stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
if (result.next())
{
ret = result.getInt(1);
}
} else if (super.Ver_DB == 2)
{ // oracle
stmt.executeQuery();
logger.warn("execute SQL completed successfully！start reading data from result...");
ret = stmt.getInt(i);
}
logger.warn("read data from result completed successfully");
if (result != null)
{
result.close();
}
if (stmt != null)
{
stmt.close();
}
if (ret > 0)
{
failres = this.getStrmsg(ret);
}
} catch (ZXYWException e)
{
failres = e.getMessage();
} catch (SQLException e)
{
failres = "数据库执行失败，请与系统管理员联系！";
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
stmt = null;
}
try
{
if (conn != null)
{
super.dbfree(conn, connstr);
}
} catch (Exception e)
{
}
logger.info("release connection completed successfully");
}
// 填写日志
// if (!failres.equals("") && super.Debug > 0)
// {
String sSqlLog = getSqlLog(sSql, paralist);
logger.warn("[" + connstr + "]执行：" + sSqlLog + " 结果：" + failres + "[" + ret + "]");
// }
tmp = new Hashtable();
tmp.put("scp", connstr);
tmp.put("result", ret + "");
tmp.put("reason", failres);
vet.add(tmp);
if (ret < 0 || (j == 0 && ret > 0))
{ // 第一个scp操作出错则退出系统;
break;
}
}
logger.warn("updating all scp for sp completed.");
if (vet != null)
{
logger.warn("return ArrayList[" + vet.size() + "]");
}
return vet;
}
/**
* 得到冗余scp
*
* @param cardprefix
* String
* @return String
* @throws ZXYWException
*/
public Hashtable getDuplicateScp() throws ZXYWException
{
logger.warn("start getting duplicate scp ...");
Connection conn = null; // 数据库连接
PreparedStatement stmt = null;
ResultSet rs = null;
Hashtable result = new Hashtable();
String sSql = "select cardprefix, devicemodule from zxinsys." + DBMacro01
+ "zxin_cardmap where servicekey='pstn52' ";
try
{
logger.info("start creating database connection....");
conn = dbconn(smpconnstr);
logger.info("create database connection completed successfully");
stmt = getPreStmt(conn, sSql);
logger.warn("start executing SQL:" + sSql);
rs = executeQuery(stmt);
logger.warn("execute SQL completed successfully！start reading data from result...");
while (rs.next())
{
result.put(rs.getString(1).trim(), rs.getString(2).trim());
}
logger.warn("read data from result completed successfully");
rs.close();
} catch (Exception es)
{
Strmsg = "getDuplicateScp():" + es.getMessage();
// if (Debug == 1)
// {
Strmsg = Strmsg + " ErrQu: " + sSql;
// }
logger.error("Error Exception occurred while getting duplicate scp ," + Strmsg);
throw new ZXYWException("zxdb_access.getDuplicateScp(): " + Strmsg);
} finally
{
logger.info("start releasing connection...");
try
{
if (stmt != null)
{
stmt.close();
}
} catch (Exception e)
{
}
try
{
if (conn != null)
{
dbfree(conn, smpconnstr);
}
} catch (Exception e)
{
logger.error("Error Exception occurred while getting duplicate scp," + Strmsg, e);
throw new ZXYWException("zxdb_access.getDuplicateScp(): " + Strmsg);
}
logger.info("release connection completed successfully");
logger.warn("getting duplicate scp completed.");
}
logger.warn("return Hashtable[" + result.size() + "]");
return result;
}
public Date DateUtil2SQL(java.util.Date date)
{
return new java.sql.Date(date.getTime());
}
public String getPreparedSQL(String sql, Object[] params) throws ZXYWException
{
// 1 如果没有参数，说明是不是动态SQL语句
int paramNum = 0;
if (null != params)
paramNum = params.length;
if (1 > paramNum)
return sql;
// 2 如果有参数，则是动态SQL语句
StringBuffer returnSQL = new StringBuffer();
String[] subSQL = sql.split("\\?");
for (int i = 0; i < paramNum; i++)
{
if (params[i] instanceof Date)
{
returnSQL.append(subSQL[i]).append(" '").append(DateUtil2SQL((java.util.Date) params[i])).append("' ");
} else if (params[i] instanceof Integer)
{
returnSQL.append(subSQL[i]).append(params[i]);
} else
{
returnSQL.append(subSQL[i]).append(" '").append(params[i]).append("' ");
}
}
if (subSQL.length > params.length)
{
returnSQL.append(subSQL[subSQL.length - 1]);
}
return returnSQL.toString();
}
/*
* 对于String型参数，如果参数为空或者为null，返回####
*/
public String getParamString(String param)
{
if (param == null || "".equals(param))
{
return "####";
}
return param;
}
/*
* 对于Int型参数，如果参数为空或者为null，返回-1
*/
public int getParamInt(String param)
{
int ret = -1;
if (param != null && !"".equals(param))
{
try
{
return Integer.valueOf(param);
} catch (NumberFormatException e)
{
}
}
return ret;
}
}
分享到：
发表评论
-
manven构建spring+springmvc+mybaitis框架
2016-04-23 22:51 0最近要离职了,闲暇的时候想自己构建个项目，说干就干。 具体 ... -
有个内存溢出的问题请教啊！
2015-08-18 16:58 979public Map<String, Object ... -
六年JAVA兼职
2014-10-10 14:08 355我叫刘洋，JAVA开发做了六年多了，大大小小的项目做过很多 ... -
新手搭车maven
2014-04-29 15:50 59最近工作太繁忙，一直没有时间来充电。 偶然的一个机会 ... -
关于DB2日常使用中遇到的问题
2013-07-30 10:07 8471 经常需要用到在不drop表的前提下去修改表字段的数据 ... -
我还年轻,我渴望上路.
2012-12-02 09:19 72不知不觉快工作两年多了,最近玩的也比较多,是时候放下玩的心了, ... -
ijetty的应用开发
2012-11-30 16:29 3193最近使用ijetty开发了一 ... -
求助一个关于公式编辑器插件的问题。
2012-08-09 11:27 74附件是一个网页可用的公式编辑器插件。 总体上很符合我想找的 ... -
关于java优化的东东
2012-07-17 11:33 952最近的机器内存又爆满了，除了新增机器内存外，还应该好好revi ... -
struts2国际化文件配置
2012-07-16 14:51 12632struts2的国际化分三种情况：前台页面的国际化，Act ... -
web.xml常用标签命令详解
2012-06-13 14:14 5357web.xml文件是用来初始化配置信息：比如welcome页面 ... -
关于常用的一些linux下命令
2012-06-12 17:14 9021,linux 创建文件 mkdir XXX 创建目录 ... -
做java开发的困惑
2012-06-05 20:23 1029从事java开发也快两年了。 忽然很迷茫了。 也发现越来越 ... -
开发中用到过的UTIL类
2012-06-01 14:58 24231，加密的Util类 /* ============= ... -
build的那些东西
2012-05-30 16:01 1141<?xml version="1.0" ... -
HTTPClient发送请求的几种实现
2012-04-01 17:21 139421，可以使用最基本的流对象 URL对象直接将请求封装 然后发送 ... -
静态页面拖拽实现代码
2012-04-01 17:18 1056静态拖拽行: <html> ... -
回忆 struts1/2
2012-04-01 17:14 1084struts1 与 struts2的对比。 action类 ...
相关推荐
在这个项目中，可能会看到如何在后台线程执行数据库操作，然后在主线程更新UI。 7. **序列化与反序列化**：在某些情况下，为了提高效率，可能会选择将笔记对象序列化为JSON字符串，再存储到数据库中。这需要用到...
【标题】"谷粒学院项目的数据库文件"涵盖了与谷粒学院相关的数据库设计和管理方面的知识。谷粒学院可能是一个在线教育平台，其数据库是存储用户信息、课程资料、学习进度等核心数据的关键部分。数据库文件通常包含...
总结，本示例的"web项目数据库连接样例"旨在展示如何在Web应用中建立数据库连接，执行SQL操作，以及使用连接池和事务管理。通过理解和实践这些知识点，开发者可以更好地构建高效、稳定的数据库驱动的Web应用。
"测试计划与执行"部分可能会讨论单元测试、集成测试和性能测试的方法，比如使用SQL查询性能测试工具或模拟高并发场景来评估数据库的负载能力。 后期的运维包括监控、备份恢复、性能调优和安全策略。PPT可能提供...
10. **数据库执行脚本**: 压缩包中的数据库执行脚本可能是创建数据库表结构、初始化数据的SQL语句，用于设置数据库环境。 11. **使用说明**: 提供的使用说明文档可能会详细解释如何运行项目、如何配置数据库连接...
标题 "第一个数据库项目" 暗示我们正在讨论一个初学者级别的数据库开发实践。这个项目可能涉及了数据库设计、创建、管理和数据操作的基础知识。在描述中提到了一个博客链接，虽然具体内容没有给出，但我们可以推测...
在数据库应用开发中，随着项目的迭代，数据库结构的变化是不可避免的，Flyway提供了一种规范化的、自动化的方式来跟踪、管理和应用这些变化，使得数据库的演变过程变得有序且可回溯。 在"flyway-4.0.3"这个版本中，...
本项目名为"Java学生管理系统-数据库小项目"，它是一个基于Java编程语言和MySQL数据库的实用系统，旨在提供对学生信息的管理功能。这个项目不仅适用于初学者熟悉Java和数据库的基本操作，同时也为有经验的开发者提供...
标题中的“新手做的月结项目数据库”表明这是一个初级开发者完成的项目，主要涉及数据库的使用。这个项目可能是为了帮助新手实践在一个月的学习中积累的数据库管理技能，通过实际操作来巩固理论知识。数据库通常用于...
数据操纵语言（DML），用于对数据库中的数据执行操作；以及数据控制语言（DCL），用于控制数据的访问权限和安全。掌握SQL语言对于任何使用关系型数据库的应用程序开发者来说是必不可少的。 **知识点三：MySQL数据库...
MyKTV后台则很可能是后台服务端程序，处理来自前端的请求，与数据库交互，执行业务逻辑。后台通常使用Java、Python、Node.js等后端语言开发，结合框架如Spring Boot或Django来构建。 总的来说，MYKTV项目涉及了软件...
本项目"android 项目数据库操作"主要探讨了如何在Android应用中有效地管理和操作SQLite数据库。 首先，我们需要创建数据库。在Android中，我们通常会创建一个继承自`SQLiteOpenHelper`的类，如`DatabaseHelper`。这...
3. 实现测试代码：在C#测试项目中编写测试方法，使用***、Entity Framework或其他数据库访问技术与数据库交互，并编写断言来验证数据库操作的正确性。 4. 运行测试：执行测试用例并观察测试结果。如果测试未通过，...
5. **实际应用**：在实际操作中，开发人员会依据这些模板来撰写具体的项目文档，将用户需求转化为可执行的代码，同时数据库设计师根据需求说明来构建适应业务场景的数据存储方案。这样既保证了项目的规范性，又确保...
3. **新手学习资源**：对于初学者来说，这个项目源码是一个很好的学习平台，可以了解如何在ASP.NET环境中建立数据库连接，执行SQL命令，以及如何封装这些操作以供上层业务逻辑调用。 【标签】"ASP.NET 数据库操作层...
《Visual C# 2005数据库项目案例导航源文件》是针对使用Microsoft的C#编程语言和2005版本的开发环境进行数据库应用程序设计的一份珍贵资源。这个压缩包包含了多个实际项目的源代码，旨在帮助开发者深入理解如何在C#...
《ASP数据库项目案例导航》是一本专注于ASP技术与数据库结合应用的实践教程，旨在通过一系列经典案例，帮助读者深入理解并掌握ASP在实际数据库项目中的应用。在这个领域，ASP（Active Server Pages）作为微软开发的...
在“Java数据库系统项目开发实践”这个主题中，我们将深入探讨使用Java语言进行数据库系统开发的关键技术和实践。这个项目旨在帮助开发者理解如何有效地利用Java与各种数据库进行交互，包括设计、实现以及优化数据库...
在Java自学过程中，数据库项目实例是非常重要的一部分，它能帮助我们深入理解如何使用Java与数据库进行交互，构建实际的应用系统。本项目实例聚焦于“管理系统”，特别是“学生管理系统”，这通常是一个初学者常接触...
然后，导入项目到IDE（如IntelliJ IDEA或Eclipse），配置好数据库连接，执行数据库脚本创建表。最后，通过Maven构建项目，将生成的war文件部署到Tomcat服务器，启动服务器即可访问项目。 总结来说，这个"SSM完整...