?? jdbc.java
字號:
package jaction.utility;
import java.util.*;
import java.sql.*;
import javax.sql.DataSource;
import java.lang.reflect.*;
import jaction.datapool.Result;
/**
* 類名： JDBC<br>
* 說明： 數據庫操作類<br>
* @author yanger
* @version 1.0
*/
public class JDBC {
/**
* 資源綁定對象
*/
private static MessageResources resource = MessageResources.getMessageResources("jaction.JactionConfig");
/**
* JTA JNDI 配置名稱
*/
public static final String JACTION_DATASOURCE=resource.getMessage("jaction.jndi.datasource");
/**
* 從連接池中取得一個空閑的數據庫連接
* @return Connection 數據庫連接
* @exception Exception 數據庫異常
*/
public static Connection getConnection() throws Exception{
Connection conn = null;
try {
javax.naming.InitialContext ctx = new javax.naming.InitialContext();
DataSource ds = (DataSource)ctx.lookup(JACTION_DATASOURCE);
conn= ds.getConnection();
} catch (java.lang.SecurityException se) {
SysLogger.error("JDBC","getConnection","數據庫連接錯誤，描述信息："+se);
se.printStackTrace();
throw se;
} catch(Exception e) {
SysLogger.error("JDBC","getConnection","數據庫連接錯誤，描述信息："+e);
e.printStackTrace();
throw e;
}
return conn;
}
/**
* 釋放一個空閑的數據庫連接至連接池
* @param conn 數據庫連接
*/
public static void freeConnection(Connection conn) {
try{
conn.close();
conn=null;
}catch(Exception e){
SysLogger.error("JDBC", "freeConnection","釋放數據庫連接錯誤,描述信息:"+e);
}
}
/**
* 設置指定的數據庫連接的提交方式(true/false)，并返回該連接
* @param conn Connection
* @param commitFlag 提交方式(true:自動提交/false:非自動提交)
* @return Connection 數據庫連接
* @exception Exception
*/
public static Connection setAutoCommit(Connection conn, boolean commitFlag) throws Exception {
try {
conn.setAutoCommit(commitFlag);
return conn;
} catch (SQLException sx) {
SysLogger.error("JDBC","setAutoCommit","數據庫錯誤，描述信息："+sx);
throw sx;
}
}
//commit connection
/**
* 對指定的連接進行提交
* @param conn 數據庫連接
* @exception Exception
*/
public static void commit(Connection conn) throws Exception {
try {
conn.commit();
setAutoCommit(conn, true);
}catch (SQLException ex){
SysLogger.error("JDBC","commit","數據庫錯誤，描述信息："+ex);
throw ex;
}
}
//rollback connection
/**
* 對指定的連接進行rollback
* @param conn 數據庫連接
* @exception Exception
*/
static public void rollback(Connection conn) throws Exception {
try{
conn.rollback();
} catch (SQLException ex) {
SysLogger.error("JDBC","rollback","數據庫錯誤，描述信息："+ex);
throw ex;
}
}
//execute update sql
/**
* 對指定的sql語句進行JDBC的update操作
* @param query update sql語句
* @return 返回成功執(zhí)行的記錄數
* @exception Exception
*/
static public int executeUpdate(String query) throws Exception{
Connection conn = getConnection();
try{
return executeUpdate(query, conn);
} catch(Exception e) {
throw e;
} finally {
freeConnection(conn);
}
}
//execute update sql use connection
/**
* 通過指定的連接對指定的sql語句進行JDBC的update操作
* @param query update sql語句
* @param conn 數據庫連接
* @return 返回成功執(zhí)行的記錄數
* @exception Exception
*/
static public int executeUpdate(String query, Connection conn) throws Exception{
SysLogger.infoLog(query);
Statement stmt = null;
try {
stmt = conn.createStatement();
if (conn == null || stmt == null) {
SysLogger.error("JDBC","executeUpdate","數據庫連接錯誤！");
return -1;
}
int i = stmt.executeUpdate(query);//執(zhí)行數據庫操作
return i;
} catch (SQLException ex) {
SysLogger.error("JDBC","executeUpdate","數據庫執(zhí)行錯誤！" + query + " SQLException : " + ex);
throw ex;
//return -1;
} finally {
try{
stmt.close();
stmt = null;
} catch (SQLException se) {
SysLogger.error("JDBC","executeUpdate","數據庫statement關閉執(zhí)行錯誤！" + se);
throw se;
}
}
}
//execute query sql
/**
* 對指定的sql語句進行JDBC的query操作
* @param query query sql語句
* @return Result 查詢結果集
* @exception Exception
*/
static public Result executeQuery(String query) throws Exception {
Connection conn = getConnection();
try {
return executeQuery(query, conn);
} catch (Exception e) {
throw e;
} finally {
freeConnection(conn);
}
}
//execute query sql use connection
/**
* 通過指定的連接對指定的sql語句進行JDBC的query操作
* @param query query sql語句
* @param conn 數據庫連接
* @return Result 查詢結果集
* @exception Exception
*/
static public Result executeQuery(String query,Connection conn) throws Exception {
Statement stmt = null;
ResultSet rs = null;
ResultSetMetaData metaData = null;
Vector rows = new Vector();
Object newRow[];
Object objectRows[][];
stmt = conn.createStatement();
if (conn == null || stmt == null) {
SysLogger.error("JDBC","executeQuery","數據庫連接錯誤！");
return null;
}
SysLogger.info("JDBC","executeQuery",query);
try {
rs = stmt.executeQuery(query);
metaData = rs.getMetaData();
int numberOfColumns = metaData.getColumnCount();
String columnNames[] = new String[numberOfColumns];
// Get the column names and cache them.
// Then we can close the connection.
for(int column = 0; column < numberOfColumns; column++) {
columnNames[column] = metaData.getColumnLabel(column+1);
}
// Get all rows.
//weigang modify 20021104
//boolean columnWritten = true;
rows.addElement(columnNames);
while (rs.next()) {
/*if (columnWritten)
{
rows.addElement(columnNames);
columnWritten = false;
}*/
newRow = new Object[numberOfColumns];
for (int i = 1; i <= numberOfColumns; i++) {
Object obj=rs.getObject(i);
newRow[i - 1] =(obj instanceof String)? (String)obj :obj;
}
rows.addElement(newRow);
}
} catch (SQLException ex) {
SysLogger.error("JDBC","executeQuery","數據庫查詢錯誤"+query + " SQLException " + ex);
throw ex;
}finally{
try{
rs.close();
}catch (SQLException se) {
SysLogger.error("JDBC","executeQuery","數據庫resultSet 關閉錯誤:"+se);
throw se;
}
try{
stmt.close();
} catch (SQLException se) {
SysLogger.error("JDBC","executeQuery","數據庫statement關閉錯誤:"+se);
throw se;
}
}
if (rows.size() == 0) {
objectRows = null;
} else {
objectRows = new Object[rows.size()][];
for (int i = 0; i < rows.size(); i++) {
objectRows[i] = (Object[])rows.elementAt(i);
}
}
Result res = new Result(objectRows);
return res;
}
/**
* 對指定的sql語句進行JDBC的query操作，返回從結果集中指定行開始的指定記錄數
*
* @param query 數據庫查詢sql語句
* @param beginRows 查詢結果的起始位置
* @param pageRows 查詢記錄數
* @return Result 查詢結果集
* @exception Exception
*/
/*weigang modify 20021104*/
static public Result executeQuery(String query, int beginRows, int pageRows ) throws Exception{
try {
if (beginRows <=0 || pageRows <= 0) {
throw new Exception("beginRows <=0 || pageRows <= 0");
}
Result res = executeQuery(query); //查詢結果集
if (res == null) {
return res;
}
int rowNumber = res.rowNum(); //結果集二位數組的行數
int colNumber = res.colNum(); //結果集二位數組的列數
if (rowNumber <= beginRows) {
return null; //沒有符合條件的查詢結果
}
Object[][] oArray = res.getData(); //結果集二維數組
/*從結果集中得到需要的記錄*/
Object[][] newArray;
if (rowNumber < beginRows + pageRows - 1) {
newArray = new Object[rowNumber - beginRows + 2][];
} else {
newArray = new Object[pageRows + 1][];
}
newArray[0] = res.getColumnName();
for (int i = 1; i < newArray.length; i++) {
newArray[i] = oArray[beginRows + i - 2];
}
Result newRes = new Result(newArray);
return newRes;
} catch (Exception e) {
throw e;
}
}
/**
* 根據表名和Where條件建立查詢語句
* @param tableName 表名
* @param whereStmt where條件
* @return 生成的查詢語句
* @exception Exception
*/
public static String queryStatementFromClass(String tableName, String whereStmt)throws Exception{
if(tableName==null){
throw new Exception("tableName==null");
}
//Class resultClass=getClassFromTableName(tableName);
String [] fields=getFieldFromTableName(tableName);
String uStmt="select ";
for(int i=0;i<fields.length;i++)
{
if(i!=0)uStmt += ", ";
uStmt += fields[i]+" ";
}
uStmt += " FROM "+tableName;
if(whereStmt!=null)
{
uStmt += " where " + whereStmt ;
}
return uStmt;
}
/**
* 根據名字, 返回類對象。
* @param tableName 表的名字。
* @return 類對象的名字。
* @exception Exception
*/
public static Class getClassFromTableName(String tableName)throws Exception{
// 是否定制
String strClassName = tableName;
//if (strClassName.equals(tableName)) strClassName = tableName;
return Class.forName("jaction.resource."+strClassName);
}
/**
* 根據表名稱得到所有字段名稱
* @param tableName 表的名字
* @return 字段數組
* @exception Exception
* @add by zjh 2002-11-1
*/
public static String[] getFieldFromTableName(String tableName) throws Exception{
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
ResultSetMetaData metaData = null;
int i=0;
try {
connection = getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery("Select * from "+tableName);
if (resultSet == null){
throw new Exception("resultSet == null");
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -