?? database.java
字號:
package oa.main;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.io.*;
import java.lang.*;
import javax.naming.*;
/**
* 此文件為數據庫的基本操作處理類
* @author 劉中兵
* @version 1.0-hg
*/
public class DataBase extends Object
{
Connection conn = null;
Statement stm = null;
PreparedStatement pstm = null;
ResultSet rs = null;
boolean connected = false;
/**構造函數*/
public DataBase()
{
}
public void showConnNUM()
{
//連接池中使用
DealString ds = new DealString();
int curnum = Configuration.connMgr.getCurConns(Configuration.ConnectionPoolName);
int sumnum = Configuration.connMgr.getSumConns(Configuration.ConnectionPoolName);
int maxnum = Configuration.connMgr.getMaxConns(Configuration.ConnectionPoolName);
int isNullPool = Configuration.connMgr.getNullPool(Configuration.ConnectionPoolName);
int isOracleErr = Configuration.connMgr.getOracleErr(Configuration.ConnectionPoolName);
int jvmcount = ParentBean.count;
float jvm1 = (float)Runtime.getRuntime().totalMemory()/1024/1024;
float jvm2 = (float)Runtime.getRuntime().maxMemory()/1024/1024;
System.out.print("\r" + ds.getDateTime ( ).substring ( 11 , 19 ) + " 連接累計> "+ds.toLengthStrRight(sumnum+"",10)+"當前連接> "+ds.toLengthStrRight(curnum+"",10)+"池中連接> "+ds.toLengthStrRight(maxnum+"",10)+"池空未連接> " +ds.toLengthStrRight(isNullPool+"",10)+"DB未響應> " +ds.toLengthStrRight(isOracleErr+"",10)+"JVM垃圾回收> "+jvmcount+" ");
}
/**取得連接池*/
public void getConnPool()
{
try
{
if(connected){
throw new SQLException("數據庫已連接，無須重連！");
}
Context initCtx = new InitialContext();
Context ctx = (Context) initCtx.lookup("java:comp/env");
/**獲取連接池對象*/
Object obj = (Object) ctx.lookup("jdbc/OracleDB");
/**類型轉換*/
javax.sql.DataSource ds = (javax.sql.DataSource)obj;
conn = ds.getConnection();
/**測試連接計數*/
connected = true;
showConnNUM();
}
catch(NamingException e){System.out.println("從數據池取得數據庫連接時出錯;\r\n錯誤為:"+e);}
catch(SQLException e){System.out.println("從數據池取得數據庫連接時出錯;\r\n錯誤為:"+e);}
}
/**創建我的連接池*/
public boolean getMyConnPool()
{
conn = Configuration.connMgr.getConnection(Configuration.ConnectionPoolName);
showConnNUM();
if(conn == null)
{
return false;
}
else
{
return true;
}
}
/**釋放我的連接池*/
public boolean releaseMyConnPool()
{
boolean b;
if ( conn !=null )
{
b = true;
}
else
{
b = false;
}
Configuration.connMgr.freeConnection(Configuration.ConnectionPoolName, conn);
conn = null ;
showConnNUM();
return b;
}
/*
public void getConnDataSourse()
{
try
{
OracleDataSource ods = new OracleDataSource();
ods.setDriverType("thin");
ods.setServerName("10.0.0.94");
ods.setNetworkProtocol("tcp");
ods.setDatabaseName("hg");
ods.setPortNumber(1521);
ods.setUser("oaadminuser");
ods.setPassword("lancom4454");
// 從數據源中獲取數據庫連接
conn = ods.getConnection();
/**測試連接計數
ParentBean.curnum++;
ParentBean.sumnum++;
}
catch(SQLException e){System.out.println("從自身建立數據庫連接時出錯;\r\n錯誤為:"+e);}
}
public void getConnJNDI()
{
Context ctx = null;
try
{
//建立上下文環境
Hashtable env = new Hashtable (5);
env.put (Context.INITIAL_CONTEXT_FACTORY,"com.sun.jndi.fscontext.RefFSContextFactory");
env.put (Context.PROVIDER_URL, "file:JNDI");
ctx = new InitialContext(env);
//從JNDI取得連接
OracleDataSource ods1 = (OracleDataSource) ctx.lookup ("jdbc/chidb");
if(ods1==null)
{
//建立數據源綁定
OracleDataSource ods = new OracleDataSource();
ods.setDriverType("thin");
ods.setServerName("10.0.0.94");
ods.setNetworkProtocol("tcp");
ods.setDatabaseName("hg");
ods.setPortNumber(1521);
ods.setUser("oaadminuser");
ods.setPassword("lancom4454");
ctx.bind ("jdbc/chidb",ods);
}
ods1 = (OracleDataSource) ctx.lookup ("jdbc/chidb");
conn = ods1.getConnection();
}
catch(SQLException e){System.out.println("從自身建立數據庫連接時出錯;\r\n錯誤為:"+e);}
catch (NamingException ne)
{
ne.printStackTrace();
}
}
*/
/**生成Oracle SQLServer等的連接*/
public void createConn(String drv,String url,String usr,String pwd)
{
try
{
if(connected){
throw new SQLException("數據庫已連接，無須重連！");
}
Class.forName(drv).newInstance();
conn = DriverManager.getConnection(url,usr,pwd);
connected = true;
/**測試連接計數*/
showConnNUM();
}
catch(ClassNotFoundException ec){System.out.println("從自身建立數據庫連接時出錯;\r\n錯誤為:"+ec);}
catch(SQLException e){System.out.println("從自身建立數據庫連接時出錯;\r\n錯誤為:"+e);}
catch(Exception et){System.out.println("從自身建立數據庫連接時出錯;\r\n錯誤為:"+et);}
}
/**生成Access連接*/
public void createConn(String drv,String url)
{
try
{
if(connected){
throw new Exception("數據庫已連接，無須重連！");
}
Class.forName(drv).newInstance();
conn = DriverManager.getConnection(url);
connected = true;
/**測試連接計數*/
showConnNUM();
}
catch(ClassNotFoundException ec){System.out.println("從自身建立數據庫連接時出錯;\r\n錯誤為:"+ec);}
catch(SQLException e){System.out.println("從自身建立數據庫連接時出錯;\r\n錯誤為:"+e);}
catch(Exception et){System.out.println("從自身建立數據庫連接時出錯;\r\n錯誤為:"+et);}
}
/**釋放數據庫連接*/
public void releaseConn()
{
try
{
if(!connected){
//throw new SQLException("數據庫未連接！");
}
if(conn!=null)
{
conn.close();
connected = false;
/**測試連接計數*/
showConnNUM();
}
}
catch(SQLException e){System.out.println("關閉數據庫連接時出錯;\r\n錯誤為:"+e);}
}
/**查詢記錄*/
public ResultSet QuerySQL(String sql)
{
ResultSet rs = null;
try
{
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
}catch(SQLException sqle){
System.out.println("執行DataBase::QuerySQL(String)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+sqle);
if(pstm!=null)
try{pstm.close();}catch(Exception e){System.out.println("執行DataBase::QuerySQL(String)試圖關閉錯誤的聲明時出錯;\r\n錯誤為："+e);}
}
return rs;
}
/**執行增刪改的語句*/
public int ExecuteSQL(String sql)
{
try
{
pstm = conn.prepareStatement(sql);
pstm.executeUpdate();
conn.commit();
}
catch(SQLException sqle)
{
//System.out.println("執行DataBase::ExecuteSQL(String)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+sqle);
return sqle.getErrorCode();
}
finally{
try{
pstm.close();
}catch(SQLException sqle){System.out.println("執行DataBase::ExecuteSQL(String)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+sqle);}
}
return 0;
}
/**產生唯一編號*/
public int makeID(String table,String field1,String field2,String value1,boolean type1)
{
int out = -1;
String sql = "";
try
{
//只有唯一主鍵field1
sql = "select "+field1+" as ID from "+table+" order by "+field1;
//有兩個主鍵field1、field2
if(!value1.equals(""))//當第一個字段不空時，作為條件查詢第二個字段
{
sql = "select "+field2+" as ID from "+table+" where "+field1+"="+value1+" order by "+field2;
if(!type1) //是字符串時 將type1設為false
sql = "select "+field2+" as ID from "+table+" where "+field1+"='"+value1+"' order by "+field2;
}
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
int t1 = 1;
int t2 = 2;
if(rs.next()) //有記錄
{
t1 = rs.getInt("ID");
out = t1;
boolean bool = false;
while(rs.next()) //不止一條紀錄
{
bool = true;
t2 = rs.getInt("ID");
if((t2-t1)>1)break; //如果t2與t1相差大于1,則跳出去,新編號為t1++（見后面**）
t1 = t2; //否則將t2賦給t1
}
if(!bool) //如果只有一條紀錄
{
if(t1>1)t1 = 1; //如果已有紀錄的ID號大于1，則新編號設為1
else t1++;
}
else t1++; //**
}
if(out>1)out = 1;
else out = t1;
}catch(SQLException sqle){System.out.println("執行DataBase::makeID(String table,String field1,String field2,String value1,boolean type1)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+sqle);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("執行DataBase::makeID(String table,String field1,String field2,String value1,boolean type1)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("執行DataBase::makeID(String table,String field1,String field2,String value1,boolean type1)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
}
}
return out;
}
/**產生唯一編號*/
public int makeID_Add1(String table,String field1,String field2,String value1,boolean type1)
{
int out = -1;
String sql = "";
try
{
//只有唯一主鍵field1
sql = "select max("+field1+")+1 as ID from "+table+" order by "+field1;
//有兩個主鍵field1、field2
if(!value1.equals(""))//當第一個字段不空時，作為條件查詢第二個字段
{
sql = "select ("+field2+")+1 as ID from "+table+" where "+field1+"="+value1+" order by "+field2;
if(!type1) //是字符串時 將type1設為false
sql = "select ("+field2+")+1 as ID from "+table+" where "+field1+"='"+value1+"' order by "+field2;
}
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
if(rs.next()) //有記錄
{
out = rs.getInt(1);
}
}catch(SQLException sqle){System.out.println("執行DataBase::makeID_Add1(String table,String field1,String field2,String value1,boolean type1)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+sqle);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("執行DataBase::makeID_Add1(String table,String field1,String field2,String value1,boolean type1)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("執行DataBase::makeID_Add1(String table,String field1,String field2,String value1,boolean type1)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
}
}
return out;
}
public int makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)
{
int out = -1;
String sql = "";
try
{
//只有唯一主鍵field1
sql = "select "+field1+" as ID from "+table+" order by "+field1;
//有兩個主鍵field1、field2
if(!value1.equals(""))//當第一個字段不空時，作為條件查詢第二個字段
{
sql = "select "+field2+" as ID from "+table+" where "+field1+"="+value1+" order by "+field2;
if(!type1) //是字符串時 將type1設為false
sql = "select "+field2+" as ID from "+table+" where "+field1+"='"+value1+"' order by "+field2;
}
if(!value2.equals(""))//當第二個字段不空時，作為條件查詢第三個字段
{
sql = "select "+field3+" as ID from "+table+" where "+field1+"="+value1+" and "+field2+"="+value2+" order by "+field3;
if(!type2) //是字符串時 將type1設為false
sql = "select "+field3+" as ID from "+table+" where "+field1+"='"+value1+"' and "+field2+"='"+value2+"' order by "+field3;
}
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
int t1 = 1;
int t2 = 2;
if(rs.next()) //有記錄
{
t1 = rs.getInt("ID");
out = t1;
boolean bool = false;
while(rs.next()) //不止一條紀錄
{
bool = true;
t2 = rs.getInt("ID");
if((t2-t1)>1)break; //如果t2與t1相差大于1,則跳出去,新編號為t1++（見后面**）
t1 = t2; //否則將t2賦給t1
}
if(!bool) //如果只有一條紀錄
{
if(t1>1)t1 = 1; //如果已有紀錄的ID號大于1，則新編號設為1
else t1++;
}
else t1++; //**
}
if(out>1)out = 1;
else out = t1;
}catch(SQLException sqle){System.out.println("執行DataBase::makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+sqle);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("執行DataBase::makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("執行DataBase::makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
}
}
return out;
}
public int makeID_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)
{
int out = -1;
String sql = "";
try
{
//只有唯一主鍵field1
sql = "select max("+field1+") as ID from "+table+" order by "+field1;
//有兩個主鍵field1、field2
if(!value1.equals(""))//當第一個字段不空時，作為條件查詢第二個字段
{
sql = "select max("+field2+") as ID from "+table+" where "+field1+"="+value1+" order by "+field2;
if(!type1) //是字符串時 將type1設為false
sql = "select max("+field2+") as ID from "+table+" where "+field1+"='"+value1+"' order by "+field2;
}
if(!value2.equals(""))//當第二個字段不空時，作為條件查詢第三個字段
{
sql = "select max("+field3+") as ID from "+table+" where "+field1+"="+value1+" and "+field2+"="+value2+" order by "+field3;
if(!type2) //是字符串時 將type1設為false
sql = "select max("+field3+") as ID from "+table+" where "+field1+"='"+value1+"' and "+field2+"='"+value2+"' order by "+field3;
}
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
if(rs.next()) //有記錄
{
out = rs.getInt("ID");
}
}catch(SQLException sqle){System.out.println("執行DataBase::makeID_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+sqle);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("執行DataBase::makeID_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("執行DataBase::makeID_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -