?? database.java
字號:
/**********************************************************
Copyright (C),2003-10-12, Beijing USTB.
All rights reserved.
Filename: Datebase.java
Author: 劉中兵
Version 1.0
Date:2003-10-12
Description:此文件為數(shù)據(jù)庫的基本操作處理類
Other:
Variable List:
1.Connection conn = null;//數(shù)據(jù)庫連接對象
Function List:
//基本操作
1.public DataBase()//構造函數(shù)
2.public void getConnPool()//取得連接池
3.public void createConn(String url,String usr,String pwd)//生成Oracle SQLServer等的連接
4.public void createConn(String url)//生成Access連接
5.public void releaseConn()//釋放數(shù)據(jù)庫連接
6.public ResultSet QuerySQL(String sql)//查詢記錄
7.public int ExecuteSQL(String sql)//執(zhí)行增刪改的語句
//輔助使用函數(shù)
8.public int makeID(String table,String field1,String field2,String value1,boolean type1)//產(chǎn)生唯一編號,type1為false時表示條件字段為字符串類型
public int makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)
public int makeID_Add1(String table,String field1,String field2,String value1,boolean type1)//產(chǎn)生唯一編號,type1為false時表示條件字段為字符串類型
public int makeID_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)
9.public int toID(String table,String field1,String field2,String value1)//將名稱轉換為編號
10.public String toName(String table,String field1,String field2,String value)//將編號轉換為名稱
11.public Vector (String sql,int page,int records)//分頁時取得一頁的數(shù)據(jù)量
12.public int setSort(String table,String field1,String field2,String wherestr,String orderstr,boolean b)//為某一個字段進行重新排序
//查詢與更新LOB類型
13.public String QueryCLOB(String table,String wherestr,String clobfield)//查詢CLOB類型值
14.public int UpdateCLOB(String table,String wherestr,String blobfield,String blobvalue)//修改CLOB類型值
15.public String QueryBLOB(String table,String wherestr,String blobfield)//查詢BLOB類型值
16.public int UpdateBLOB(String table,String wherestr,String blobfield,String blobvalue)//修改BLOB類型值
17.public String QueryBLOB_JNDI(String table,String wherestr,String blobfield)//查詢BLOB類型值
178.public int UpdateBLOB_JNDI(String table,String wherestr,String blobfield,String blobvalue)//修改BLOB類型值
//LOB類型基本操作（到文件）
19.public int clobInsert(String sql,String table,String wherestr,String clobfield,String infile)//往數(shù)據(jù)庫中插入一個新的CLOB對象
20.public int clobModify(String table,String wherestr,String clobfield,String infile)//修改CLOB對象（是在原CLOB對象基礎上進行覆蓋式的修改）
21.public int clobReplace(String table,String wherestr,String clobfield,String infile)//替換CLOB對象（將原CLOB對象清除，換成一個全新的CLOB對象）
22.public int blobInsert(String sql,String table,String wherestr,String blobfield,String infile)//往數(shù)據(jù)庫中插入一個新的BLOB對象
23.public int blobModify(String table,String wherestr,String blobfield,String infile)//修改BLOB對象（是在原BLOB對象基礎上進行覆蓋式的修改）
24.public int blobReplace(String table,String wherestr,String blobfield,String infile)//替換BLOB對象（將原BLOB對象清除，換成一個全新的BLOB對象）
//數(shù)據(jù)庫結構信息
25.public Hashtable getDataBaseInfo()//數(shù)據(jù)庫信息
26;public Vector getTableList()//數(shù)據(jù)表列表
27.public Vector getTableStruct(String table)//數(shù)據(jù)表的結構
28.public Vector getResultSetData(ResultSet rs)//取得數(shù)據(jù)集內容
//對sql語句的分布處理
29.public void prepareStatement(String sql)//創(chuàng)建申明對象
30.public void executeQuery()//執(zhí)行查詢
31.public boolean next()//轉向下一條
32.public String getObject(String field,String sqlType)//取得數(shù)據(jù)并根據(jù)數(shù)據(jù)類型轉化為字符串
33.public void setObject(int index,String value,String sqlType)//根據(jù)數(shù)據(jù)類型保存到數(shù)據(jù)庫
34.public void executeUpdate()//執(zhí)行更新
35.public void closePstm()//關閉申明對象
36.public void closeRs()//關閉游標
History:
date:2003-11-21
name:liumei
action:修改getOnePage(String sql,int page,int records)//分頁時取得一頁的數(shù)據(jù)量
每頁的記錄顯示條數(shù)從字典中取,即去掉records的作用
***********************************************************/
package com.ideas.bean;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.io.*;
import java.lang.*;
import javax.naming.*;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.*;
import com.ideas.util.*;
/**
* 此文件為數(shù)據(jù)庫的基本操作處理類
* @author 劉中兵
* @version 1.0-hg
*/
public class DataBase extends Object
{
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
DBConnectionManager connMgr = null;
/**構造函數(shù)*/
public DataBase()
{
}
/**取得連接池*/
public void getConnPool()
{
try
{
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();
/**測試連接計數(shù)*/
// ParentBean.curnum++;
// ParentBean.sumnum++;
ParentBean.showConnNUM(true);
}
catch(NamingException e)
{
System.out.println("從數(shù)據(jù)池取得數(shù)據(jù)庫連接時出錯;\r\n錯誤為:" + e);
}
catch(SQLException e)
{
System.out.println("從數(shù)據(jù)池取得數(shù)據(jù)庫連接時出錯;\r\n錯誤為:" + e);
}
}
/**創(chuàng)建我的連接池*/
public boolean getMyConnPool()
{
//Configuration.DB_URL = "jdbc:mysql://192.168.0.1:3306/yj";
//Configuration.DB_JDBCDRIVER = "org.gjt.mm.mysql.Driver";
connMgr = DBConnectionManager.getInstance();
conn = connMgr.getConnection(Configuration.ConnectionPoolName);
if(conn == null)
{
return false;
}
else
{
return true;
}
}
/**釋放我的連接池*/
public boolean releaseMyConnPool()
{
connMgr.freeConnection(Configuration.ConnectionPoolName, conn);
return true;
}
/**生成Oracle SQLServer等的連接*/
public void createConn(String drv, String url, String usr, String pwd)
{
try
{
Class.forName(drv).newInstance();
conn = DriverManager.getConnection(url, usr, pwd);
/**測試連接計數(shù)*/
ParentBean.showConnNUM(true);
}
catch(ClassNotFoundException ec)
{
System.out.println("從自身建立數(shù)據(jù)庫連接時出錯;\r\n錯誤為:" + ec);
}
catch(SQLException e)
{
System.out.println("從自身建立數(shù)據(jù)庫連接時出錯;\r\n錯誤為:" + e);
}
catch(Exception et)
{
System.out.println("從自身建立數(shù)據(jù)庫連接時出錯;\r\n錯誤為:" + et);
}
}
/**生成Access連接*/
public void createConn(String drv, String url)
{
try
{
Class.forName(drv).newInstance();
conn = DriverManager.getConnection(url);
/**測試連接計數(shù)*/
ParentBean.showConnNUM(true);
}
catch(ClassNotFoundException ec)
{
System.out.println("從自身建立數(shù)據(jù)庫連接時出錯;\r\n錯誤為:" + ec);
}
catch(SQLException e)
{
System.out.println("從自身建立數(shù)據(jù)庫連接時出錯;\r\n錯誤為:" + e);
}
catch(Exception et)
{
System.out.println("從自身建立數(shù)據(jù)庫連接時出錯;\r\n錯誤為:" + et);
}
}
/**釋放數(shù)據(jù)庫連接*/
public void releaseConn()
{
try
{
if(conn != null)
{
conn.close();
/**測試連接計數(shù)*/
ParentBean.showConnNUM(false);
}
}
catch(SQLException e)
{
System.out.println("關閉數(shù)據(jù)庫連接時出錯;\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("調用SQL語句 " + sql + " 時出錯;\r\n錯誤為:" + sqle);
}
return rs;
}
/**執(zhí)行增刪改的語句*/
public int ExecuteSQL(String sql)
{
try
{
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
pstm = conn.prepareStatement(sql);
pstm.executeUpdate();
conn.commit();
conn.setAutoCommit(defaultCommit);
pstm.close();
}
catch(SQLException sqle)
{
System.out.println("調用SQL語句 " + sql + " 時出錯;\r\n錯誤為:" + sqle);
return sqle.getErrorCode();
}
return 0;
}
/**產(chǎn)生唯一編號*/
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(""))
{ //當?shù)谝粋€字段不空時，作為條件查詢第二個字段
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;
}
rs.close();
pstm.close();
}
catch(SQLException sqle)
{
System.out.println("調用SQL語句 " + sql + " 時出錯;\r\n錯誤為:" + sqle);
}
return out;
}
/**產(chǎn)生唯一編號*/
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(""))
{ //當?shù)谝粋€字段不空時，作為條件查詢第二個字段
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);
}
rs.close();
pstm.close();
}
catch(SQLException sqle)
{
System.out.println("調用SQL語句 " + sql + " 時出錯;\r\n錯誤為:" + sqle);
}
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(""))
{ //當?shù)谝粋€字段不空時，作為條件查詢第二個字段
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(""))
{ //當?shù)诙€字段不空時，作為條件查詢第三個字段
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;
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -