www.pudn.com > java-jdbc.rar > jdbc.java
package jdbc;
import java.io.*;
import java.sql.*;
import java.util.*;
import javax.naming.*;
import java.text.*;
public class jdbc
{
Context initCtx = null;
Context ctx = null;
Object obj = null;
javax.sql.DataSource ds = null;
public Connection conn = null;
public Statement stmt = null;
public ResultSet rs = null;
static long connectCount=0;
public jdbc()
{
conn = null;
stmt = null;
rs = null;
}
public Statement getConn()
{
try
{
//SYBASE
//Class.forName("com.sybase.jdbc2.jdbc.SybDriver").newInstance();
//conn = DriverManager.getConnection("jdbc:sybase:Tds:10.199.6.8:5000/altisdb","sa","");
//ORACLE
//Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
//conn = DriverManager.getConnection("jdbc:oracle:thin:@10.199.6.8:1521:ora7","altis112","oracle");
initCtx = new InitialContext();
//获取连接池对象
ctx = (Context) initCtx.lookup("java:comp/env");
obj = (Object) ctx.lookup("AltisDB");
//类型转换
ds = (javax.sql.DataSource)obj;
conn = ds.getConnection();
setConnectcountInc();
stmt = conn.createStatement();
}
catch(Exception e)
{
//e.printStackTrace();
System.out.println("DB CONNECIT ERROR!"+e);
closeConn();
rs = null;
stmt = null;
conn = null;
}
return stmt ;
}
public Statement getSybConn()
{
try
{
//SYBASE
//Class.forName("com.sybase.jdbc2.jdbc.SybDriver").newInstance();
//conn = DriverManager.getConnection("jdbc:sybase:Tds:10.199.6.8:5000/altisdb","sa","");
//ORACLE
//Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
//conn = DriverManager.getConnection("jdbc:oracle:thin:@10.199.6.8:1521:ora7","altis112","oracle");
initCtx = new InitialContext();
//获取连接池对象
ctx = (Context) initCtx.lookup("java:comp/env");
obj = (Object) ctx.lookup("SybaseDB");
//类型转换
ds = (javax.sql.DataSource)obj;
conn = ds.getConnection();
setConnectcountInc();
stmt = conn.createStatement();
}
catch(Exception e)
{
//e.printStackTrace();
System.out.println("DB CONNECIT ERROR!"+e);
closeConn();
rs = null;
stmt = null;
conn = null;
}
return stmt ;
}
public void closeConn()
{
if(conn!=null)
{
setConnectcountDec();
}
try
{
if(rs!=null)
{
rs.close();
}
}
catch(Exception e)
{
System.out.println("ERROR HAPPEN TIME:"+getTime());
System.out.println(e.toString());
System.out.println("DB RELEASE RS ERROR!");
}
rs=null;
try
{
if(stmt!=null)
{
stmt.close();
}
}
catch(Exception e)
{
System.out.println("ERROR HAPPEN TIME:"+getTime());
System.out.println(e.toString());
System.out.println("DB RELEASE STMT ERROR!");
}
stmt=null;
try
{
if(conn!=null)
{
conn.close();
}
}
catch(Exception e)
{
System.out.println("ERROR HAPPEN TIME:"+getTime());
System.out.println(e.toString());
System.out.println("DB RELEASE CONN ERROR!");
}
conn=null;
return ;
}
public String ex_chinese(String str)
{
if(str==null)
{
str = "" ;
}
else
{
try
{
str = new String(str.getBytes("iso-8859-1"),"gb2312") ;
}
catch (Exception ex)
{
str = "exchange error !";
}
}
return str ;
}
public String ex_iso8859_1(String str)
{
if(str==null)
{
str = "" ;
}
else
{
try
{
str = new String(str.getBytes("gb2312"),"iso-8859-1") ;
}
catch (Exception ex)
{
str = "exchange error !";
}
}
return str ;
}
public String isNullString(String str)
{
if(str==null)
{
str = "" ;
}
return str ;
}
public String getTime()
{
String datestr = "" ;
try
{
java.text.DateFormat df = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
datestr = df.format(new java.util.Date()) ;
}
catch (Exception ex)
{
datestr = "1970/01/01 00:00:00";
}
return datestr ;
}
public String getTime(int diffDay)
{
String datestr = "" ;
java.util.Date nowDate = new java.util.Date();
long lTime=nowDate.getTime();
lTime=lTime-diffDay*24*3600*1000;
java.util.Date beforeDate = new java.util.Date(lTime);
try
{
java.text.DateFormat df = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
datestr = df.format(beforeDate) ;
}
catch (Exception ex)
{
datestr = "1970/01/01 00:00:00";
}
return datestr ;
}
public String getCurMonth(int startDay)
{
String rtnStr = "";
int int_year = 0;
int int_mon = 0;
int int_day = 0;
Calendar now = Calendar.getInstance();
int_year = now.get(Calendar.YEAR);
int_mon = now.get(Calendar.MONTH);
int_day = now.get(Calendar.DAY_OF_MONTH);
if(startDay!=1) startDay--;
Object[] objs = {new Integer(int_year),new Integer(int_mon+1),new Integer(startDay)};
rtnStr = MessageFormat.format("{0,number,0000}-{1,number,00}-{2,number,00}",objs);
return rtnStr;
}
public String getBackMonth(int startDay)
{
String rtnStr = "";
int int_year = 0;
int int_mon = 0;
int int_day = 0;
Calendar now = Calendar.getInstance();
int_year = now.get(Calendar.YEAR);
int_mon = now.get(Calendar.MONTH);
int_day = now.get(Calendar.DAY_OF_MONTH);
if( int_mon >= 1 )
{
int_mon--;
}
else // months=1 DECEMBER
{
int_year--;
int_mon = 11;
}
Object[] objs = {new Integer(int_year),new Integer(int_mon+1),new Integer(startDay)};
rtnStr = MessageFormat.format("{0,number,0000}-{1,number,00}-{2,number,00}",objs);
return rtnStr;
}
public int comTime(String dt1, String dt2)
{
String monday[]={"31", "28", "31", "30", "31", "30", "31", "31", "30", "31", "30", "31"};
String ynmonday[]={"31", "29", "31", "30", "31", "30", "31", "31", "30", "31", "30", "31"};
if(dt1==null||dt2==null)
{
return -1;
}
if(dt1.length()<10||dt2.length()<10)
{
return -1;
}
else if(dt1.length()==19&&dt2.length()==19)
{
if(dt1.charAt(10)!=' '||dt1.charAt(13)!=':'||dt1.charAt(16)!=':'||
dt2.charAt(10)!=' '||dt2.charAt(13)!=':'||dt2.charAt(16)!=':')
{
return -1;
}
else
{
if(dt1.substring(11,13).compareTo("00")<0||dt1.substring(11,13).compareTo("23")>0||
dt2.substring(11,13).compareTo("00")<0||dt2.substring(11,13).compareTo("23")>0)
{
return -1;
}
if(dt1.substring(14,16).compareTo("00")<0||dt1.substring(14,16).compareTo("59")>0||
dt2.substring(14,16).compareTo("00")<0||dt2.substring(14,16).compareTo("59")>0)
{
return -1;
}
if(dt1.substring(17,19).compareTo("00")<0||dt1.substring(17,19).compareTo("59")>0||
dt2.substring(17,19).compareTo("00")<0||dt2.substring(17,19).compareTo("59")>0)
{
return -1;
}
}
}
else if(dt1.length()>10&&dt1.length()<19||dt1.length()>19||
dt2.length()>10&&dt2.length()<19||dt2.length()>19)
{
return -1;
}
if(dt1.charAt(4)!='/'&&dt1.charAt(4)!='-'||dt1.charAt(7)!='/'&&dt1.charAt(7)!='-'||
dt2.charAt(4)!='/'&&dt2.charAt(4)!='-'||dt2.charAt(7)!='/'&&dt2.charAt(7)!='-')
{
return -1;
}
if(dt1.substring(0,4).compareTo("1970")<0||dt1.substring(0,4).compareTo("2070")>0||
dt2.substring(0,4).compareTo("1970")<0||dt2.substring(0,4).compareTo("2070")>0)
{
return -1;
}
if(dt1.substring(5,7).compareTo("01")<0||dt1.substring(5,7).compareTo("12")>0||
dt2.substring(5,7).compareTo("01")<0||dt2.substring(5,7).compareTo("12")>0)
{
return -1;
}
int year1=Integer.parseInt(dt1.substring(0,4));
int mon1=Integer.parseInt(dt1.substring(5,7));
if(year1%4==0&&year1%100!=0||year1%400==0)
{
if(dt1.substring(8,10).compareTo("01")<0||dt1.substring(8,10).compareTo(ynmonday[mon1-1])>0)
return -1;
}
else
{
if(dt1.substring(8,10).compareTo("01")<0||dt1.substring(8,10).compareTo(monday[mon1-1])>0)
return -1;
}
int year2=Integer.parseInt(dt2.substring(0,4));
int mon2=Integer.parseInt(dt2.substring(5,7));
if(year2%4==0&&year2%100!=0||year2%400==0)
{
if(dt2.substring(8,10).compareTo("01")<0||dt2.substring(8,10).compareTo(ynmonday[mon2-1])>0)
return -1;
}
else
{
if(dt2.substring(8,10).compareTo("01")<0||dt2.substring(8,10).compareTo(monday[mon2-1])>0)
return -1;
}
return dt2.compareTo(dt1);
}
public int checkTime(String dt1)
{
String monday[]={"31", "28", "31", "30", "31", "30", "31", "31", "30", "31", "30", "31"};
String ynmonday[]={"31", "29", "31", "30", "31", "30", "31", "31", "30", "31", "30", "31"};
if(dt1==null)
{
return -1;
}
if(dt1.length()<10)
{
return -1;
}
else if(dt1.length()==19)
{
if(dt1.charAt(10)!=' '||dt1.charAt(13)!=':'||dt1.charAt(16)!=':')
{
return -1;
}
else
{
if(dt1.substring(11,13).compareTo("00")<0||dt1.substring(11,13).compareTo("23")>0)
{
return -1;
}
if(dt1.substring(14,16).compareTo("00")<0||dt1.substring(14,16).compareTo("59")>0)
{
return -1;
}
if(dt1.substring(17,19).compareTo("00")<0||dt1.substring(17,19).compareTo("59")>0)
{
return -1;
}
}
}
else if(dt1.length()>10&&dt1.length()<19||dt1.length()>19)
{
return -1;
}
if(dt1.charAt(4)!='/'||dt1.charAt(7)!='/')
{
if(dt1.charAt(4)!='-'||dt1.charAt(7)!='-')
return -1;
}
if(dt1.substring(0,4).compareTo("1970")<0||dt1.substring(0,4).compareTo("2070")>0)
{
return -1;
}
if(dt1.substring(5,7).compareTo("01")<0||dt1.substring(5,7).compareTo("12")>0)
{
return -1;
}
int year1=Integer.parseInt(dt1.substring(0,4));
int mon1=Integer.parseInt(dt1.substring(5,7));
if(year1%4==0&&year1%100!=0||year1%400==0)
{
if(dt1.substring(8,10).compareTo("01")<0||dt1.substring(8,10).compareTo(ynmonday[mon1-1])>0)
return -1;
}
else
{
if(dt1.substring(8,10).compareTo("01")<0||dt1.substring(8,10).compareTo(monday[mon1-1])>0)
return -1;
}
return 1;
}
public boolean checkDigital(String checkStr)
{
if(checkStr==null)
return false;
if(checkStr.length()==0)
return false;
char checkArray[] = checkStr.toCharArray();
for(int i=0;i57)
return false;
}
return true;
}
public String getRepTime(String strll,double diffDay)
{
String datestr;
try
{
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
java.util.Date date= formatter.parse(strll);
long lTime=date.getTime();
lTime=lTime+(long)(diffDay*3600*1000);
java.util.Date nowDate=new java.util.Date(lTime);
java.text.DateFormat df = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
datestr = df.format(nowDate) ;
}
catch (Exception ex)
{
datestr = "1970/01/01 00:00:00";
}
return datestr;
}
public String format(String format,double number)
{
String rtnStr = "";
try
{
MessageFormat mf = new MessageFormat("{0,number,"+format+"}");
Object[] objs = {new Double(number)};
rtnStr = mf.format( objs );
}
catch(Exception e)
{
rtnStr = "format error!";
}
return rtnStr;
}
public String format(String format,String number)
{
String rtnStr =number;
int f_len=format.length();
int n_len=number.length();
for(int i=0;i< f_len - n_len;i++)
rtnStr="0"+rtnStr;
return rtnStr;
}
public String getDatebaseType()
{
//读取数据库类型
InputStream is = getClass().getResourceAsStream("dbset.txt");
Properties props = new Properties();
try
{
props.load(is);
}
catch (Exception e)
{
return "oracle";
}
String datebasetype = props.getProperty("datebasetype").trim();
if(datebasetype==null)
datebasetype="oracle";
if((!datebasetype.equals("oracle"))&&(!datebasetype.equals("sybase")))
datebasetype="oracle";
return datebasetype;
}
public int length(String str)
{
if(str==null)
return 0;
if(str.equals(""))
return 0;
int len=str.length();
int all_len=0;
String temp="";
for(int i=0;i=0)&&(temp.compareTo("~")<=0))||(temp.compareTo(" ")==0))
all_len=all_len+1;
else
all_len=all_len+2;
}
return all_len;
}
public String substring(String srcStr,int startpos,int length)
{
int len=0;
if(srcStr==null)
return "";
len=length(srcStr);
if(startpos>=len)
return "";
if(startpos<0)
return "";
if(length==0)
return "";
String temp=""; //临时存储字符
String reStr=""; //记载返回字符串的值
int len_true=0; //记载字符串的实际长度
int temp_len=0; //
int len_pos=0; //记载字符的位置
len_true=srcStr.length();//记载字符串的实际长度
len_pos =startpos+length;//记载截取字符的终止位置
for(int i=0;i=0)&&(temp.compareTo("~")<=0))||(temp.compareTo(" ")==0))
{
temp_len=temp_len+1;
if(temp_len > startpos)
reStr=reStr+temp;
}
else
{
temp_len=temp_len+2;
if(temp_len > startpos+1)
reStr=reStr+temp;
}
}
else
{
return reStr;
}
}
return reStr;
}
public String getObsTable(int startDay)
{
String rtnStr = "";
int int_year = 0;
int int_mon = 0;
int int_day = 0;
Calendar now = Calendar.getInstance();
int_year = now.get(Calendar.YEAR);
int_mon = now.get(Calendar.MONTH);
int_day = now.get(Calendar.DAY_OF_MONTH);
if( int_day >= startDay && startDay > 1 )
{
if( int_mon >= 11 ) // months=12 DECEMBER
{
int_year = int_year + 1;
int_mon = 0;
}
else
{
int_mon++;
}
}
Object[] objs = {new Integer(int_year),new Integer(int_mon+1)};
rtnStr = MessageFormat.format("OBS_{0,number,0000}{1,number,00}",objs);
return rtnStr;
}
public String getObsTable(int startDay,String byDate)
{
String rtnStr = "";
int int_year = 0;
int int_mon = 0;
int int_day = 0;
int_year = Integer.parseInt(byDate.substring(0,4));
int_mon = Integer.parseInt(byDate.substring(5,7));
int_day = Integer.parseInt(byDate.substring(8,10));
if(int_year==0||int_mon==0||int_day==0)
return "dateFormat error!";
if( int_day >= startDay && startDay > 1 )
{
if( int_mon >= 12 ) // months=12 DECEMBER
{
int_year = int_year + 1;
int_mon = 1;
}
else
{
int_mon++;
}
}
Object[] objs = {new Integer(int_year),new Integer(int_mon)};
rtnStr = MessageFormat.format("OBS_{0,number,0000}{1,number,00}",objs);
return rtnStr;
}
public String getDupTable(int startDay)
{
String rtnStr = "";
int int_year = 0;
int int_mon = 0;
int int_day = 0;
Calendar now = Calendar.getInstance();
int_year = now.get(Calendar.YEAR);
int_mon = now.get(Calendar.MONTH);
int_day = now.get(Calendar.DAY_OF_MONTH);
if( int_day >= startDay && startDay > 1 )
{
if( int_mon >= 11 ) // months=12 DECEMBER
{
int_year = int_year + 1;
int_mon = 0;
}
else
{
int_mon++;
}
}
Object[] objs = {new Integer(int_year),new Integer(int_mon+1)};
rtnStr = MessageFormat.format("DUP_{0,number,0000}{1,number,00}",objs);
return rtnStr;
}
public String getRefRepTable(String current_time,int startDay)
{
if(current_time==null)
current_time="";
if(current_time.equals(""))
return "";
int int_year=Integer.parseInt(current_time.substring(0,4));
int int_mon=Integer.parseInt(current_time.substring(5,7));
int int_day=Integer.parseInt(current_time.substring(8,10));
if( int_day >= startDay && startDay > 1 )
{
if( int_mon > 11 ) // months=12 DECEMBER
{
int_year = int_year + 1;
int_mon = 1;
}
else
{
int_mon++;
}
}
String dup_name="";
if(int_mon< 10)
dup_name="rep_"+int_year+"0"+int_mon;
else
dup_name="rep_"+int_year+int_mon;
return dup_name;
}
public String getDupTable(int startDay,String byDate)
{
String rtnStr = "";
int int_year = 0;
int int_mon = 0;
int int_day = 0;
int_year = Integer.parseInt(byDate.substring(0,4));
int_mon = Integer.parseInt(byDate.substring(5,7));
int_day = Integer.parseInt(byDate.substring(8,10));
if(int_year==0||int_mon==0||int_day==0)
return "dateFormat error!";
if( int_day >= startDay && startDay > 1 )
{
if( int_mon >= 12 ) // months=12 DECEMBER
{
int_year = int_year + 1;
int_mon = 1;
}
else
{
int_mon++;
}
}
Object[] objs = {new Integer(int_year),new Integer(int_mon)};
rtnStr = MessageFormat.format("DUP_{0,number,0000}{1,number,00}",objs);
return rtnStr;
}
public String getRepTable(int startDay)
{
String rtnStr = "";
int int_year = 0;
int int_mon = 0;
int int_day = 0;
Calendar now = Calendar.getInstance();
int_year = now.get(Calendar.YEAR);
int_mon = now.get(Calendar.MONTH);
int_day = now.get(Calendar.DAY_OF_MONTH);
if( int_day >= startDay && startDay > 1 )
{
if( int_mon >= 11 ) // months=12 DECEMBER
{
int_year = int_year + 1;
int_mon = 0;
}
else
{
int_mon++;
}
}
Object[] objs = {new Integer(int_year),new Integer(int_mon+1)};
rtnStr = MessageFormat.format("REP_{0,number,0000}{1,number,00}",objs);
return rtnStr;
}
public String getRepTable(int startDay,String byDate)
{
String rtnStr = "";
int int_year = 0;
int int_mon = 0;
int int_day = 0;
int_year = Integer.parseInt(byDate.substring(0,4));
int_mon = Integer.parseInt(byDate.substring(5,7));
int_day = Integer.parseInt(byDate.substring(8,10));
if(int_year==0||int_mon==0||int_day==0)
return "dateFormat error!";
if( int_day >= startDay && startDay > 1 )
{
if( int_mon >= 12 ) // months=12 DECEMBER
{
int_year = int_year + 1;
int_mon = 1;
}
else
{
int_mon++;
}
}
Object[] objs = {new Integer(int_year),new Integer(int_mon)};
rtnStr = MessageFormat.format("REP_{0,number,0000}{1,number,00}",objs);
return rtnStr;
}
public String getVpwTable(int startDay)
{
String rtnStr = "";
int int_year = 0;
int int_mon = 0;
int int_day = 0;
Calendar now = Calendar.getInstance();
int_year = now.get(Calendar.YEAR);
int_mon = now.get(Calendar.MONTH);
int_day = now.get(Calendar.DAY_OF_MONTH);
if( int_day >= startDay && startDay > 1 )
{
if( int_mon >= 11 ) // months=12 DECEMBER
{
int_year = int_year + 1;
int_mon = 0;
}
else
{
int_mon++;
}
}
Object[] objs = {new Integer(int_year),new Integer(int_mon+1)};
rtnStr = MessageFormat.format("VPW_{0,number,0000}{1,number,00}",objs);
return rtnStr;
}
public String getVpwTable(int startDay,String byDate)
{
String rtnStr = "";
int int_year = 0;
int int_mon = 0;
int int_day = 0;
int_year = Integer.parseInt(byDate.substring(0,4));
int_mon = Integer.parseInt(byDate.substring(5,7));
int_day = Integer.parseInt(byDate.substring(8,10));
if(int_year==0||int_mon==0||int_day==0)
return "dateFormat error!";
if( int_day >= startDay && startDay > 1 )
{
if( int_mon >= 12 ) // months=12 DECEMBER
{
int_year = int_year + 1;
int_mon = 1;
}
else
{
int_mon++;
}
}
Object[] objs = {new Integer(int_year),new Integer(int_mon)};
rtnStr = MessageFormat.format("VPW_{0,number,0000}{1,number,00}",objs);
return rtnStr;
}
public String getOtwTable(int startDay)
{
String rtnStr = "";
int int_year = 0;
int int_mon = 0;
int int_day = 0;
Calendar now = Calendar.getInstance();
int_year = now.get(Calendar.YEAR);
int_mon = now.get(Calendar.MONTH);
int_day = now.get(Calendar.DAY_OF_MONTH);
if( int_day >= startDay && startDay > 1 )
{
if( int_mon >= 11 ) // months=12 DECEMBER
{
int_year = int_year + 1;
int_mon = 0;
}
else
{
int_mon++;
}
}
Object[] objs = {new Integer(int_year),new Integer(int_mon+1)};
rtnStr = MessageFormat.format("OTW_{0,number,0000}{1,number,00}",objs);
return rtnStr;
}
public String getOtwTable(int startDay,String byDate)
{
String rtnStr = "";
int int_year = 0;
int int_mon = 0;
int int_day = 0;
int_year = Integer.parseInt(byDate.substring(0,4));
int_mon = Integer.parseInt(byDate.substring(5,7));
int_day = Integer.parseInt(byDate.substring(8,10));
if(int_year==0||int_mon==0||int_day==0)
return "dateFormat error!";
if( int_day >= startDay && startDay > 1 )
{
if( int_mon >= 12 ) // months=12 DECEMBER
{
int_year = int_year + 1;
int_mon = 1;
}
else
{
int_mon++;
}
}
Object[] objs = {new Integer(int_year),new Integer(int_mon)};
rtnStr = MessageFormat.format("OTW_{0,number,0000}{1,number,00}",objs);
return rtnStr;
}
public String createAllView(int startDay) throws Exception
{
String rtnStr = "";
CallableStatement cstmt=null;
jdbc myJdbc=new jdbc();
myJdbc.getConn();
try
{
cstmt=myJdbc.conn.prepareCall("{call create_view_all(?)}");
cstmt.registerOutParameter(1,java.sql.Types.VARCHAR);
cstmt.execute();
rtnStr=cstmt.getString(1);
cstmt.close();
}
catch(Exception e)
{
System.out.println(e);
cstmt.close();
rtnStr="createAllView() execute error!";
}
myJdbc.closeConn();
return rtnStr;
}
public String createMonView(String dt1, String dt2, int startDay) throws Exception
{
String rtnStr = "";
CallableStatement cstmt=null;
if(comTime(dt1,dt2)<0)
return "The format of dtime is error!";
jdbc myJdbc=new jdbc();
myJdbc.getConn();
try
{
cstmt=myJdbc.conn.prepareCall("{call create_obs_view(?,?,?)}");
cstmt.setString(1,dt1);
cstmt.setString(2,dt2);
cstmt.registerOutParameter(3,java.sql.Types.VARCHAR);
cstmt.execute();
rtnStr=cstmt.getString(3);
rtnStr=rtnStr.replaceFirst("ALLOBS_","REPOBS_");
cstmt.close();
}
catch(Exception e)
{
System.out.println(e);
cstmt.close();
rtnStr="createMonView execute error!";
}
myJdbc.closeConn();
return rtnStr;
}
public String getMaxPartmentId(String user_type,String style,String area_code,String exch_id)
{
String group_style="",group_user_type="",group_area_code="",group_id="",group_bureau_id="";
String ls_sql="";
if(exch_id==null) exch_id="";
if(exch_id.equals("")) return "";
ls_sql=" select group_id,user_type,bureau_id,styletype,area_code from grouplogin,group_level"+
" where group_id <=stop_id"+
" and group_id >=start_id"+
" and (accept_obstacle_tyle is null or accept_obstacle_tyle <> '1') "+
" and area_code="+area_code+
" and (( ','||bureau_id||',' like '%,"+exch_id+",%') "+
" or ((bureau_id is null or bureau_id='')))" +
" and (( ','||styletype||',' like '%,"+style+",%') "+
" or ((styletype is null or styletype='')))" +
" order by group_id desc ";
jdbc myJdbc=new jdbc();
myJdbc.getConn();
try
{
myJdbc.rs=myJdbc.stmt.executeQuery(ls_sql);
while(myJdbc.rs.next())
{
group_bureau_id=myJdbc.rs.getString("bureau_id");
group_area_code=myJdbc.rs.getString("area_code");
group_user_type=myJdbc.rs.getString("user_type");
group_id=myJdbc.rs.getString("group_id");
group_style=myJdbc.rs.getString("styletype");
if(group_bureau_id==null) group_bureau_id="";
if(group_user_type==null) group_user_type="";
if(group_style==null) group_style="";
if(!group_user_type.equals(""))
group_user_type=","+group_user_type+",";
if(!group_style.equals(""))
group_style=","+group_style+",";
if(!group_style.equals(""))
{
if(group_style.indexOf(style)>=0)
{
if(!group_user_type.equals(""))
{
if(group_user_type.indexOf(user_type)>=0)
{
myJdbc.closeConn();
return group_id;
}
}
else
{
myJdbc.closeConn();
return group_id;
}
}
}
else
{
if(!group_user_type.equals(""))
{
if(group_user_type.indexOf(user_type)>=0)
{
myJdbc.closeConn();
return group_id;
}
}
else
{
myJdbc.closeConn();
return group_id;
}
}
}
myJdbc.closeConn();
}
catch(Exception e)
{
myJdbc.closeConn();
System.out.println(ls_sql+e);
return null;
}
return group_id;
}
public String getPartmentId(String user_type,String style,String area_code,String exch_id)
{
String group_style="",group_user_type="",group_area_code="",group_id="",group_bureau_id="";
String ls_sql="";
String all_group_id="";
if(exch_id==null) exch_id="";
if(exch_id.equals("")) return "";
ls_sql=" select group_id,user_type,bureau_id,styletype,area_code from grouplogin,group_level"+
" where group_id <=stop_id"+
" and group_id >=start_id"+
" and group_level >=4 "+
" and (accept_obstacle_tyle is null or accept_obstacle_tyle <> '1') "+
" and (( ','||bureau_id||',' like '%,"+exch_id+",%') "+
" or (area_code="+area_code+" and (bureau_id is null or bureau_id='')))" +
" order by styletype";
jdbc myJdbc=new jdbc();
myJdbc.getConn();
try
{
myJdbc.rs=myJdbc.stmt.executeQuery(ls_sql);
while(myJdbc.rs.next())
{
group_bureau_id=myJdbc.rs.getString("bureau_id");
group_area_code=myJdbc.rs.getString("area_code");
group_user_type=myJdbc.rs.getString("user_type");
group_id=myJdbc.rs.getString("group_id");
group_style=myJdbc.rs.getString("styletype");
if(group_bureau_id==null) group_bureau_id="";
if(group_user_type==null) group_user_type="";
if(group_style==null) group_style="";
if(!group_user_type.equals(""))
group_user_type=","+group_user_type+",";
if(!group_style.equals(""))
group_style=","+group_style+",";
if(!group_style.equals(""))
{
if(group_style.indexOf(style)>=0)
{
if(!group_user_type.equals(""))
{
if(group_user_type.indexOf(user_type)>=0)
{
all_group_id+=","+group_id;
}
}
else
{
all_group_id+=","+group_id;
}
}
}
else
{
if(!group_user_type.equals(""))
{
if(group_user_type.indexOf(user_type)>=0)
{
all_group_id+=","+group_id;
}
}
else
{
all_group_id+=","+group_id;
}
}
}
myJdbc.closeConn();
}
catch(Exception e)
{
myJdbc.closeConn();
System.out.println(ls_sql+e);
return null;
}
return all_group_id;
}
public String getCheckerSid( String test_number, int cmd, String error_style ) throws Exception
{
String checker_id=null;
CallableStatement cstmt=null;
jdbc myJdbc=new jdbc();
myJdbc.getConn();
try
{
cstmt=myJdbc.conn.prepareCall("{call ?:=getchkid_bypho(?, ?, ?)}");
cstmt.registerOutParameter(1,java.sql.Types.VARCHAR);
cstmt.setString(2,test_number);
cstmt.setString(3,error_style);
cstmt.setInt(4,cmd);
cstmt.execute();
checker_id=cstmt.getString(1);
cstmt.close();
}
catch(Exception e)
{
System.out.println(e);
cstmt.close();
checker_id=null;
}
myJdbc.closeConn();
return checker_id;
}
public void setConnectcountInc()
{
connectCount++;
System.out.println(getTime()+" 目前所用连接的数量为："+connectCount);
}
public void setConnectcountDec()
{
connectCount--;
System.out.println(getTime()+" 目前所用连接的数量为："+connectCount);
}
public static void main(String arg[])
{
jdbc jdbc=new jdbc();
System.out.println(jdbc.getTime());
System.out.println(jdbc.format("000",1));
System.out.println(jdbc.format("-000",1));
System.out.println(jdbc.format("000.00",1));
System.out.println(jdbc.format("##,###.##",123456.789));
System.out.println(jdbc.format("$.##",1.039));
System.out.println(jdbc.format("￥.##元",1.039));
System.out.println(jdbc.getObsTable(21));
System.out.println(jdbc.getDupTable(21));
System.out.println(jdbc.getRepTable(21));
System.out.println(jdbc.getObsTable(21,"2003/12/01"));
System.out.println(jdbc.getDupTable(21,"2003/12/21"));
System.out.println(jdbc.getRepTable(21,"2003/12/25"));
System.out.println("2000-03-21-2000-03-21:"+jdbc.comTime("2000-03-21 00:00:59","2000-03-21 00:00:59"));
System.out.println("2000/03/21-2000/03/21:"+jdbc.comTime("2000/03/21","2000/03/21"));
System.out.println("2000/03/21-2000/04/21:"+jdbc.comTime("2000/03/21","2000/04/21"));
System.out.println("2000/03/21-2000/05/21:"+jdbc.comTime("2000/03/21","2000/05/21"));
System.out.println("2000/03/01-2000/03/09:"+jdbc.comTime("2000/03/01","2000/03/09"));
System.out.println("2001/02/28-2002/03/30:"+jdbc.comTime("2001/02/28","2002/03/30"));
System.out.println("2001/02/29-2002/03/30:"+jdbc.comTime("2001/02/29","2002/03/30"));
System.out.println("2000/02/29-2000/03/30:"+jdbc.comTime("2000/02/29","2000/03/30"));
System.out.println("2002/02/01-2000/03/32:"+jdbc.comTime("2002/02/01","2000/03/32"));
System.out.println("2000/02/01-2000/03-30:"+jdbc.comTime("2002/02/01","2000/03-30"));
System.out.println("2000/02/01-2000/3/30:"+jdbc.comTime("2002/02/01","2000/3/30"));
System.out.println(jdbc.getRepTime("2004-03-20 06:41:10",18.0));
System.out.println(jdbc.getDatebaseType());
}
}