[Java] .Net人員適用的JDBC存取層物件(有用到DataTable)
使用前請先自備JDBC Driver和本人寫的DataTable.jar
package DAL;
import System.Data.*;
import java.io.*;
import java.sql.*;
import java.util.*;
//import com.microsoft.sqlserver.jdbc.*;// SQLServerDataSource ds = new SQLServerDataSource();
/**
*
* <br/>.net開發人員適用的ADO存取物件
* <br/>最大特色，可以傳"欄位名稱"或"欄位索引"，取得該欄位的資料
* <br/>使用前要先new 物件，執行SQL完可以不用自己關閉連線
* <br/>資料表裡的NULL在前端要用dr.getValue()==null判斷
* @author Create by Shadow at 2011.6.6
*/
public class DBUtil {
//<editor-fold defaultstate="collapsed" desc="連線字串">
String connectionUrl = "";
String UID = "";
String PWD = "";
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="宣告JDBC物件">
Connection conn = null;
Statement stmt = null;
CallableStatement cstmt = null;
ResultSet rs = null;
//</editor-fold>
public String errorMsg = "";
/**
* 物件被new出來時，會自動設定連線字串資訊(不含Connection物件的建立)
*/
public DBUtil()
{
//this.connectionUrl = "jdbc:sqlserver://127.0.0.1\\sqlexpress:1433;" +
// "database=NorthwindChinese;";
//this.UID = "sa";
// this.PWD = "test";
try {
//C:\apache-tomcat-7.0.14(Catalina_Home)資料夾底下的webapps路徑開始算起
File file = new File("../webapps/Connections/DBUtil.properties");
Properties props = new Properties();
props.load(new FileInputStream(file.getAbsolutePath()));//讀取檔案總管裡的檔案
this.connectionUrl = props.getProperty("connectionUrl");
this.UID = props.getProperty("UID");
this.PWD = props.getProperty("PWD");
} catch (Exception ex) {
this.errorMsg += ex.toString() + "<br/> DBUtil的建構子發生例外 <br/>";
}
}
//<editor-fold defaultstate="collapsed" desc="建立和DB的連線">
public void initConnection()
{
// Establish the connection.
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //Driver 3.0的寫法
//Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");//SQL Server 2000 Driver的寫法
this.conn = DriverManager.getConnection(connectionUrl, UID, PWD);
}catch(Exception ex)
{
this.errorMsg += ex.toString() + "<br/> DBUtil的initConnection()方法發生例外 <br/>";
}
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Select取得單一值">
/**
* 可以傳新刪修+Select指令
* 前端可用Object==null來判斷是否有撈到資料
* @param sql 要執行的SQL指令
* @return Object
*/
public Object GetSingle(String sql)
{
this.initConnection();//建立連線
Object obj=null;
try
{
this.stmt = this.conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
this.rs = this.stmt.executeQuery(sql);//產生ResultSet物件(類似.net的DataReader)
//<editor-fold defaultstate="collapsed" desc="ResultSet移動一次">
if(this.rs.next())
{
obj = this.rs.getObject(1);
}
//</editor-fold>
}catch(Exception ex)
{
this.errorMsg += ex.toString() + "<br /> DBUtil物件的GetSingle方法發生例外。<br/>";
}
finally
{
this.closeconnection();
}
return obj;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="回傳Select的資料集DataTable">
/**
* 可以傳新刪修+Select指令
* 前端可用DataTable.Rows.size()>0來判斷是否有撈到資料
* @param sql 要執行的SQL指令
* @return DataTable結果集
*/
public DataTable QueryDataTable(String sql)
{
this.initConnection();//建立連線
DataTable dt = new DataTable();
try
{
this.stmt = this.conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
this.rs = this.stmt.executeQuery(sql);//產生ResultSet物件(類似.net的DataReader)
//<editor-fold defaultstate="collapsed" desc="DataTable加入欄位標題">
ResultSetMetaData rsmd = this.rs.getMetaData();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
dt.Columns.Add(rsmd.getColumnName(i+1));
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="DataTable加入資料">
while(this.rs.next())
{
DataRow dr = dt.NewRow();//產生一列DataRow
for (int i = 0; i < dt.Columns.size(); i++)
{
dr.setValue(i, this.rs.getObject(i+1));//DataRow一欄一欄填入資料
}
dt.Rows.add(dr);//DataTable加入此DataRow
}
//</editor-fold>
}catch(Exception ex)
{
this.errorMsg += ex.toString() + "<br /> DBUtil物件的QueryDataTable方法發生例外。<br/>";
}
finally
{
this.closeconnection();
}
return dt;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="執行異動作業，回傳影響筆數">
/**
* 若傳Select語法，會發生例外被catch
* @param sql 要執行的新刪修語法
* @return 影響筆數
*/
public int ExecuteNonQuery(String sql)
{
this.initConnection();//建立連線
int rows = 0;
try {
this.stmt = this.conn.createStatement();
rows = this.stmt.executeUpdate(sql);
}catch (Exception ex) {
this.errorMsg += ex.toString() + "<br /> DBUtil物件的ExecuteNonQuery方法發生例外。<br/>";
}
finally {
this.closeconnection();
}
return rows;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="執行預存程序，傳輸出入參數和回傳值">
public void StoredProcedureOutReturnValue(String StoredProcedureName,ArrayList<Object> paramObject,
ArrayList<Integer> paramDataType,ArrayList<Boolean> isOutParam,boolean isReturnValue)
{
this.initConnection();//建立連線
String callProc = "";
try {
//<editor-fold defaultstate="collapsed" desc="產生 ? 個參數">
String param = "";
int paramCount = paramObject.size();//參數數量
if(isReturnValue){//若有回傳值的預存程序，因為有一個參數要給returnValue使用，所以減一
paramCount--;
}
for (int i = 0; i < paramCount; i++) {//走訪參數值集合
param +="?,";
}
param = param.substring(0,param.length()-1);
//</editor-fold>
if (isReturnValue) {//是回傳值類型的預存程序
callProc = "{? = call "+StoredProcedureName+"("+param+")}";
}else
{
callProc = "{call "+StoredProcedureName+"("+param+")}";
}
cstmt = this.conn.prepareCall(callProc);
for (int i = 0; i < paramObject.size(); i++) {//走訪參數值集合
if (isOutParam.get(i)) { //設定輸出入參數
cstmt.registerOutParameter(i+1,paramDataType.get(i));
}else
{
cstmt.setObject(i+1, paramObject.get(i), paramDataType.get(i));
}
}
cstmt.execute();//執行預存程序
for (int i = 0; i < paramObject.size(); i++) {//走訪參數值集合
if (isOutParam.get(i)) {//把輸出參數的結果指派回給paramObject
paramObject.set(i, cstmt.getObject(i+1));
}
}
}
catch (Exception ex)
{
this.errorMsg += ex.toString() + "<br/> DBUtil物件的StoredProcedureOutReturnValue方法發生例外。<br/>執行的語法："+callProc+"<br/>";
}finally
{
this.closeconnection();
}
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="執行預存程序，只有輸入參數，回傳結果集">
/**
* 前端可用ArrayList<DataTable>.size() > 0，來判斷是否有DataTable
* @param StoredProcedureName
* @param paramObject
* @param paramDataType
* @return
*/
public ArrayList<DataTable> StoredProcedureQuery(String StoredProcedureName,ArrayList<Object> paramObject,ArrayList<Integer> paramDataType)
{
this.initConnection();//建立連線
ArrayList<DataTable> tables= new ArrayList<DataTable>();//預存程序回傳的資料集們
String callProc = "";
try {
//<editor-fold defaultstate="collapsed" desc="產生 ? 個參數">
String param = "";
for (int i = 0; i < paramObject.size(); i++) {//走訪參數值集合
param +="?,";
}
param = param.substring(0,param.length()-1);
//</editor-fold>
callProc = "{call "+StoredProcedureName+"("+param+")}";
this.cstmt = this.conn.prepareCall(callProc);
for (int i = 0; i < paramObject.size(); i++) {//走訪參數值集合，設定輸入參數
this.cstmt.setObject(i+1, paramObject.get(i), paramDataType.get(i));
}
cstmt.execute();//執行預存程序
//executeQuery會重新產生一份ResultSet，所以用getResultSet()
for (int i = 0; (this.rs = this.cstmt.getResultSet())!=null; i++,cstmt.getMoreResults())
{
DataTable dt = new DataTable();//產生DataTable物件
//<editor-fold defaultstate="collapsed" desc="DataTable加入欄位標題">
ResultSetMetaData rsmd = this.rs.getMetaData();
for (int j = 0; j < rsmd.getColumnCount(); j++) {
dt.Columns.Add(rsmd.getColumnName(j+1));
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="DataTable加入資料">
while(this.rs.next())
{
DataRow dr = dt.NewRow();//產生一列DataRow
for (int k = 0; k < dt.Columns.size(); k++)
{
dr.setValue(k, this.rs.getObject(k+1));//DataRow一欄一欄填入資料
}
dt.Rows.add(dr);//DataTable加入此DataRow
}
//</editor-fold>
tables.add(dt);
}
}
catch (Exception ex)
{
this.errorMsg += ex.toString() + "<br/> DBUtil物件的StoredProceeQuery方法發生例外。<br/>執行的語法："+callProc+"<br/>";
}finally
{
this.closeconnection();
}
return tables;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="關閉連線">
public void closeconnection()
{
try {
if(this.rs!=null) this.rs.close();
if(this.stmt!=null) this.stmt.close();
if(this.cstmt!=null) this.cstmt.close();
if(this.conn!=null) this.conn.close();
}
catch (SQLException ex) {
this.errorMsg += ex.toString() + "<br/> DBUtil物件的closeconnection方法發生例外。<br/>";
}
}
//</editor-fold>
}
新刪修JSP範例程式碼：
<%@page import="DAL.DBUtil"%>
<%@page import="java.util.*"%>
<%@page import="System.Data.*"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%
String ET = request.getParameter("ET")!=null?request.getParameter("ET"):"";
String param = request.getParameter("param")!=null?request.getParameter("param"):"";
DBUtil db=new DBUtil();
String errorMsg="";
if(ET.equals("insert"))
{
db.ExecuteNonQuery("Insert into Categories (CategoryName) Values ('"+param+"')");
errorMsg = db.errorMsg;
}//End if
if(ET.equals("update"))
{
db.ExecuteNonQuery("Update Categories Set CategoryName = '"+param+"' Where CategoryName = 'test'");
errorMsg = db.errorMsg;
}//End if
if(ET.equals("delete"))
{
db.ExecuteNonQuery("Delete From Categories Where CategoryName = '"+param+"'");
errorMsg = db.errorMsg;
}//End if
%>
<script type="text/javascript">
function Insert(msg)
{
form1.param.value = msg;
form1.ET.value = "insert";
form1.submit();
}
function Update(msg)
{
form1.param.value = msg;
form1.ET.value = "update";
form1.submit();
}
function Delete(msg)
{
form1.param.value = msg;
form1.ET.value = "delete";
form1.submit();
}
</script>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<form name="form1" method="post" action="InsertUpdateDelete.jsp" >
DB錯誤訊息：<%= errorMsg %>
<br/>
<input type="hidden" name ="ET" value="" />
<input type="hidden" name="param" value="" />
<input type="button" name="btn_Insert" value="新增" onclick="Insert('test');" />
<br/>
<input type="button" name="btn_Update" value="修改" onclick="Update('test1');" />
<br/>
<input type="button" name="btn_Delete" value="刪除" onclick="Delete('test1');" />
<br/>
</form>
</body>
</html>
查詢Select，JSP範例程式碼：
<%@page import="DAL.DBUtil"%>
<%@page import="java.util.*"%>
<%@page import="System.Data.*"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%
String ET = request.getParameter("ET")!=null?request.getParameter("ET"):"";
String param = request.getParameter("param")!=null?request.getParameter("param"):"";
DBUtil db=new DBUtil();
String errorMsg="";
DataTable dt = new DataTable();
if(ET.equals("select"))
{
dt = db.QueryDataTable("Select * From Categories Where CategoryID > '"+param+"'");
errorMsg = db.errorMsg;
}//End if
%>
<script type="text/javascript">
function Select()
{
form1.param.value= "2";
form1.ET.value = "select";
form1.submit();
}
</script>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<form name="form1" method="post" action="SelectDataTable.jsp" >
DB錯誤訊息：<%= errorMsg %>
<br/>
<input type="hidden" name ="ET" value="" />
<input type="hidden" name="param" value="" />
<input type="button" name="btn_Insert" value="查詢" onclick="Select();" />
<br/>
<%
if(dt.Rows.size()>0){
out.print("<table border='1'>");
out.print("<tr><td>CategroyID</td><td>CategoryName</td></tr>");
for(DataRow dr : dt.Rows){
out.print("<tr><td>"+dr.getValue("CategoryID") +"</td><td>"+dr.getValue("CategoryName") +"</td></tr>");
}
out.print("</table>");
}
%>
</form>
</body>
</html>
執行後，按下畫面結果：
執行分頁預存程序程式碼範例：
<%@page import="DAL.DBUtil"%>
<%@page import="java.util.*"%>
<%@page import="System.Data.*"%>
<%@page import="java.sql.ResultSet"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%
//預設一開始是0，點了按鈕後runProc的Value會是1，執行預存程序
String runProc = request.getParameter("runProc")!=null?request.getParameter("runProc"):"0";
DBUtil db=new DBUtil();
String errorMsg="";
if(runProc.equals("1"))
{
ArrayList<Object> paramObject= new ArrayList<Object>();//參數值的集合
ArrayList<Integer> paramDataType= new ArrayList<Integer>();//參數的型別集合
paramObject.add("Select * from Products");paramDataType.add(java.sql.Types.VARCHAR);
paramObject.add("Order by ProductID");paramDataType.add(java.sql.Types.VARCHAR);
//目前在第二頁
paramObject.add(2);paramDataType.add(java.sql.Types.INTEGER);
//每頁顯示10筆
paramObject.add(10);paramDataType.add(java.sql.Types.INTEGER);
ArrayList<DataTable> tables = db.StoredProcedureQuery("uSP_pageSQL_passPageNum", paramObject, paramDataType);
if(tables.size()==2)
{
for(DataRow dr:tables.get(0).Rows)
{
out.print("<br/>" + dr.getValue("ProductID") + " " + dr.getValue("ProductName"));
}
}
out.print("<br/>共"+tables.get(1).getValue(0,1) +"頁");
errorMsg = db.errorMsg;
}//End if
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<form name="form1" method="post" action="towDataTable.jsp" >
DB錯誤訊息：<%= errorMsg %>
<br/>
<input type="hidden" name ="runProc" value="1" />
<input type="button" name="btn_GO" value="執行" onclick="javascript:form1.submit();" />
</form>
</body>
</html>
執行結果：
執行輸出參數、回傳值的預存程序範例：
(先看預存程式的建立)
CREATE Procedure [passOutParam]
(@paramIn int ,@paramOut varchar(50) out)
As
Begin
Set @paramOut = 'Shadow''s Show';
Select * from Products
Where ProductID <= @paramIn
Order by ProductID ASC
return @@RowCount;
End
GOJSP呼叫此預存程序的程式碼：
<%@page import="DAL.DBUtil"%>
<%@page import="java.sql.CallableStatement"%>
<%@page import="java.util.*"%>
<%@page import="System.Data.*"%>
<%@page import="java.sql.ResultSet"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%
//預設一開始是0，點了按鈕後runProc的Value會是1，執行預存程序
String runProc = request.getParameter("runProc")!=null?request.getParameter("runProc"):"0";
DBUtil db=new DBUtil();
String errorMsg="";
int paramIn = 4;//輸入參數的值
String paramOut="outParam";//輸出參數的值
int returnValue =0;//回傳值
ArrayList<Object> paramObject= new ArrayList<Object>();//參數值的集合
ArrayList<Integer> paramDataType= new ArrayList<Integer>();//參數的型別集合
ArrayList<Boolean> isOutParam = new ArrayList<Boolean>();//每個參數是否為輸出參數
if(runProc.equals("1"))
{
//接回傳值的參數要在第一個加入
paramObject.add(returnValue); paramDataType.add(java.sql.Types.INTEGER);isOutParam.add(true);
paramObject.add(paramIn); paramDataType.add(java.sql.Types.INTEGER);isOutParam.add(false);
paramObject.add(paramOut);paramDataType.add(java.sql.Types.VARCHAR);isOutParam.add(true);
//passOutParam預存程序有回傳值，所以最後的參數為true
db.StoredProcedureOutReturnValue("passOutParam", paramObject, paramDataType, isOutParam,true);
errorMsg = db.errorMsg;
returnValue = (Integer)paramObject.get(0);//從參數值集合取得回傳值
paramOut = (String)paramObject.get(2);//從參數值集合取得輸出參數結果值
}//End if
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<form name="form1" method="post" action="outParam_returnValue.jsp" >
DB錯誤訊息：<%= errorMsg %>
<br/>
輸出參數值：<%= paramOut %>
<br/>
回傳值(資料集的筆數)：<%= returnValue %>
<br/>
<input type="hidden" name ="runProc" value="1" />
<input type="button" name="btn_GO" value="執行" onclick="javascript:form1.submit();" />
</form>
</body>
</html>
按下按鈕後的執行結果：
