基于JDBC有哪些数据库通用访问方法?
1. 通用数据库Bean设计
本实例中对数据库连接和执行SQL语句等通用数据库操作进行了封装,通过实现DBConnBean和DBQueryBean两个JavaBean来完成上述功能。其中DBConnBean负责Java应用程序和数据库的连接;DBQueryBean提供了一组执行标准SQL的功能,可以实现标准SQL完成的所有功能。其功能代码分别如下所示:
① DBConnBean.Java的源代码如下所示:
package dbaccess;
import Java.sql.*;
import Java.util.*;
import Java.io.*;
public class DBConnBean
implements Serializable{
private String DBDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
private String DBHost = "127.0.0.1";
private String DBName = "demo";
private String conp = "jdbc:odbc:db_demo";
private String username = "";
private String password = "";
private boolean xdebug = true;
public Connection con = null;
public String sql = null;
Statement stmt = null;
public ResultSet result = null;
private int affectedRows = 0;
public DBConnBean()
{
xdebug = true;
con = null;
sql = null;
}
public Connection Connect()
throws Exception
{
String msg = null;
try
{
Class.forName(DBDriver).newInstance();
}
catch(Exception e)
{
msg = "加载数据库驱动失败";
if (xdebug) msg += "(驱动'"+DBDriver+"')";
throw new Exception(msg);
}
try
{
String conStr = conp;
con = DriverManager.getConnection(conStr,username,password);
}
catch(SQLException e)
{
msg = "!!数据库连接失败";
if (xdebug)
{
msg += "(错误信息='" + e.getMessage()+"' SQL状态值='" + e.getSQLState()+"' 错误代码='" + e.getErrorCode()+"')";
}
throw new Exception(msg);
}
return con;
}
protected void finalize()
throws Throwable
{
super.finalize();
if (stmt != null) stmt.close();
if (result != null) result.close();
}
//最近一次对数据库查询受影响的行数
public int getAffectedRows()
{
return affectedRows;
}
public Connection getCon()
{
return con;
}
public String getConp()
{
return conp;
}
public String getDBDriver()
{
return DBDriver;
}
public String getDBName()
{
return DBName;
}
public boolean getDebug()
{
return xdebug;
}
public String getPassword()
{
return password;
}
public ResultSet getResult()
{
return result;
}
public String getSql()
{
return sql;
}
public String getUsername()
{
return username;
}
public void over()
throws Throwable
{
finalize();
}
public ResultSet query()
throws Exception
{
result = null;
affectedRows = 0;
if (con == null)
Connect();
if (stmt == null)
stmt = con.createStatement();
if (sql.substring(0,6).equalsIgnoreCase("select"))
{
result = stmt.executeQuery(sql);
}
else
{
affectedRows = stmt.executeUpdate(sql);
}
return result;
}
public ResultSet query(String s)
throws Exception
{
sql = s;
return query();
}
public void setDBDriver(String s)
{
DBDriver = s;
}
public void setDebug(boolean b)
{
xdebug = b;
}
public void setgetConp(String s)
{
conp = s;
}
public void setgetDBName(String s)
{
DBName = s;
}
public void setgetUsername(String s)
{
username = s;
}
public void setPassword(String s)
{
password = s;
}
public void setSql(String s)
{
sql = s;
}
}
② DBQueryBean.Java的源代码如下所示:
package dbaccess;
import Java.sql.*;
import Java.util.*;
import Java.io.*;
import Java.lang.reflect.*;
public class DBQueryBean
implements Serializable
{
DBConnBean dbc;
String sql = null;
int rowcount = 0;
int colcount = 0;
// int limitcount = 0;
Vector result = null;
public String _WATCH = "";
public DBQueryBean()
{
dbc = new DBConnBean();
try {
dbc.Connect();
} catch(Exception e) {
handleException(e);
}
}
protected void finalize()
throws Throwable
{
super.finalize();
if (dbc != null) dbc.over();
if (result != null) result.removeAllElements();
}
public String get(int row, int col)
{
if (result==null || row >= result.size()) return null;
String r[] = (String[])result.elementAt(row);
if (col >= Java.lang.reflect.Array.getLength(r)) return null;
return r[col];
}
public int getAffRows() { return dbc.getAffectedRows(); }
public int getColumncount() {
return colcount;
}
public String[] getRow(int row)
{
if (result==null || row >= result.size()) return null;
return (String [])result.elementAt(row);
/*String ret[] = new String[colcount];
Vector r = (Vector)result.elementAt(row);
for (int i=0; i<colcount; i++)
ret[i] = (String)r.elementAt(i);
return ret;*/
}
public int getRowcount() {
return rowcount;
}
public void handleException(Exception e)
{
_WATCH = e.getMessage();
}
public void init()
{
rowcount = 0;
colcount = 0;
// limitcount = 0;
result = null;
}
public void over()
throws Throwable
{
finalize();
}
public int query(String sql)
{
result = new Vector();
int ret = 0;
try {
ResultSet rs = dbc.query(sql);
if (rs == null)
{
ret = dbc.getAffectedRows();
}
else
{
ResultSetMetaData rm = rs.getMetaData();
colcount = rm.getColumnCount();
while (rs.next())
{
String row[] = new String[colcount];
for (int i=0; i<colcount; i++)
row[i] = rs.getString(i+1);
result.addElement(row);
rowcount++;
}
rs.close(); // to release the resource.
ret = result.size();
}
}
catch(Exception e)
{
handleException(e);
return -1;
}
return ret;
}
}
2. 数据库表结构
本实例中主要出现了三个数据库表,表名和字段分别如下所示:
计划采购表:jhcg_table
字段名称 中文名称 类型 长度
Goods_no 物品编号 vchar 10
Goods_name 物品名称 Vchar 50
Amount 采购数量 Int
Price 采购单价 float
Gold 币种 Vchar 15
Units 单位 Vchar 10
Date 时间 Date
Remark 备注 vchar 100
库存统计表:kctj_table
字段名称 中文名称 类型 长度
Goods_no 物品编号 Vchar 10
Goods_name 物品名称 Vchar 50
amount 库存数量 Int
Date 时间 Date
remark 备注 Vchar 100
实际采购表:sjcg_table
字段名称 中文名称 类型 长度
Goods_no 物品编号 Vchar 10
Goods_name 物品名称 Vchar 50
Amount 采购数量 Int
Price Price 采购单价 Float
Gold 币种 Vchar 15
Units 采购单位 Vchar 10
Date 时间 Date
Remark 备注 vchar 100
其中业务逻辑非常简单,即根据计划采购表和库存统计表生成实际采购表。同时,对各表完成数据库的增、删、改、查等通用操作。
3. JSP设计
① 插入操作
完成对数据库表的记录插入功能,其中计划采购表的插入主页面(insert_jhcg.htm)为:
insert_jhcg.htm将用户输入传送给demo_insert_jhcg.jsp,完成插入操作。改jsp文件的功能代码为:
<html>
<body>
<jsp:useBean id="DBConn" class="dbaccess.DBConnBean" scope="page"/>
<jsp:useBean id="DBBean" class="dbaccess.DBQueryBean" scope="page"/>
<hr>
<!--test JavaBean-->
<%
if (DBConn == null||DBBean == null){
out.println("JavaBean not found!");
return;
}
%>
<!--try db_demo connection-->
<%
try{
DBConn.Connect();
}catch(Exception e){
out.println(e.getMessage());
}
%>
<!--execute sql statement-->
<%
String insGoodno = request.getParameter("ed_jhcg_no");
String insGoodname = request.getParameter("ed_jhcg_name");
int insAmount = (Integer.valueOf(request.getParameter("ed_jhcg_amount"))).intValue();
float insPrice = (Float.valueOf(request.getParameter("ed_jhcg_price"))).floatValue();
String insGold = request.getParameter("ed_jhcg_gold");
String insUnit = request.getParameter("ed_jhcg_unit");
String insRemark = request.getParameter("ed_jhcg_remark");
String sqlStatement = "insert into jhcg_table(good_no,good_name,amount,
price,gold,unit,remark) values("+"'"+insGoodno+"'"+","+"'"+insGoodname+"'"+",
"+insAmount+","+insPrice+","+"'"+insGold+"'"+","+"'"+insUnit+"'"+","+"'"+
insRemark+"'"+")";
try{
DBBean.query(sqlStatement);
}catch(Exception e){
out.println(e.getMessage());
}
%>
<a href="demo_main.htm">Records inserted...Click here to return</a></p>
</body>
</html>
② 查询操作
该查询主页面主要提供对三个数据库表的条件查询功能,
query.htm将用户选择查询的数据库表和查询条件发送给demo_query.jsp,由jsp文件完成数据库查询操作和查询结果集的返回及显示,其功能代码如下:
<html>
<body>
<%
String sqlStatement;
String sqlField = "";
String whichTable = "";
String whereClause = "";
String queryNo = "";
String queryName = "";
%>
<jsp:useBean id="DBConn" class="dbaccess.DBConnBean" scope="page"/>
<jsp:useBean id="DBBean" class="dbaccess.DBQueryBean" scope="page"/>
<hr>
<!--test JavaBean-->
<%
if (DBConn == null||DBBean == null){
out.println("JavaBean not found!");
return;
}
%>
<!--try db_demo connection-->
<%
try{
DBConn.Connect();
}catch(Exception e){
out.println(e.getMessage());
}
%>
<!--prepare sql statement-->
<%
String queryRequest = request.getParameter("rb_request");
//out.println("queryRequest:"+queryRequest);
String whichCB = "";
if (queryRequest.equals("1")){
whichCB = "ck_jhcg";
whichTable = "jhcg_table";
queryNo = request.getParameter("ed_jhcg_no");
queryName = request.getParameter("ed_jhcg_name");
if (!queryNo.equals(""))
whereClause = " where good_no="+"'"+queryNo+"'";
if (!queryName.equals("")){
if (!queryNo.equals(""))
whereClause += " and good_name="+"'"+queryName+"'";
else whereClause = " where good_name="+"'"+queryName+"'";
}
}
if (queryRequest.equals("2")){
whichCB = "ck_kctj";
whichTable = "kctj_table";
queryNo = request.getParameter("ed_kctj_no");
queryName = request.getParameter("ed_kctj_name");
if (!queryNo.equals(""))
whereClause = " where good_no="+"'"+queryNo+"'";
if (!queryName.equals("")){
if (!queryNo.equals(""))
whereClause += " and good_name="+"'"+queryName+"'";
else whereClause = " where good_name="+"'"+queryName+"'";
}
}
if (queryRequest.equals("3")){
whichCB = "ck_sjcg";
whichTable = "sjcg_table";
queryNo = request.getParameter("ed_sjcg_no");
queryName = request.getParameter("ed_sjcg_name");
if (!queryNo.equals(""))
whereClause = " where good_no="+"'"+queryNo+"'";
if (!queryName.equals("")){
if (!queryNo.equals(""))
whereClause += " and good_name="+"'"+queryName+"'";
else whereClause = " where good_name="+"'"+queryName+"'";
}
}
String[] printTitle = request.getParameterValues(whichCB);
%>
<!--create query sql statement-->
<%
sqlStatement = "select ";
for(int i = 0;i<printTitle.length;i++){
sqlField += printTitle[i]+",";
}
sqlStatement += sqlField.substring(0,sqlField.length()-1)+" from "+whichTable;
if (!whereClause.equals(""))
sqlStatement += whereClause;
%>
<!--show query response-->
<%
try{
DBBean.query(sqlStatement);
}catch(Exception e){
out.println("Database Error!");
}
int rows = DBBean.getRowcount();
int cols = DBBean.getColumncount();
%>
<Table align="center" width="80%" border=1>
<tr align=center>
<%
for(int i = 0;i < printTitle.length;i++){
out.println("<td><b>");
out.println(printTitle[i]);
out.println("</b></td>");
}
%>
</tr>
<%
for (int i = 0;i < rows;i++){
out.println("<tr>");
for (int j = 0;j < cols;j++)
out.println("<td>"+DBBean.get(i,j)+"</td>");
out.println("</tr>");
}
%>
</Table>
<br>
<hr>
<a href="demo_main.htm">Click here to return</a></p>
</body>
</html>
③ 生成实际采购表
生成数据库表是一个隐式操作,程序根据计划采购表和库存统计表的相应字段生成实际采购表,不需要用户的任何输入,其功能代码如下(demo_create.jsp):
<%@page import="Java.util.*"%>
<html>
<body>
<jsp:useBean id="DBConn" class="dbaccess.DBConnBean" scope="page"/>
<jsp:useBean id="DBBean" class="dbaccess.DBQueryBean" scope="page"/>
<hr>
<!--test JavaBean-->
<%
if (DBConn == null||DBBean == null){
out.println("JavaBean not found!");
return;
}
%>
<!--try db_demo connection-->
<%
try{
DBConn.Connect();
}catch(Exception e){
out.println(e.getMessage());
}
%>
<!--prepare sql statement-->
<%
int amount_jhcg,amount_kctj;
Vector updateRs = new Vector();
DBBean.query("delete * from sjcg_table"); //delete all old records in sjcg_table
DBBean.query("select jhcg_table.good_no,jhcg_table.good_name,jhcg_table.amount,kctj_table.amount,jhcg_table.unit from jhcg_table left join kctj_table on kctj_table.good_no=jhcg_table.good_no");
int rows = DBBean.getRowcount();
int cols = DBBean.getColumncount();
for (int i = 0;i < rows;i++){
String record[] = new String[4];
record[0] = DBBean.get(i,0);
record[1] = DBBean.get(i,1);
amount_jhcg = (Integer.valueOf(DBBean.get(i,2))).intValue();
if (DBBean.get(i,3) == null) amount_kctj = 0;
else amount_kctj = (Integer.valueOf(DBBean.get(i,3))).intValue();
record[2] = Integer.toString(amount_jhcg - amount_kctj);
record[3] = DBBean.get(i,4);
updateRs.addElement(record);
}
for (int i = 0;i < rows;i++){
String insRecord[] = (String [])updateRs.elementAt(i);
String insGoodno,insGoodname,insUnit,insAmount;
insGoodno = insRecord[0];
insGoodname = insRecord[1];
insAmount = insRecord[2];
insUnit = insRecord[3];
String sqlStatement = "insert into sjcg_table(good_no,good_name,amount,unit) values?quot;+"'"+insGoodno+"'"+","+"'"+insGoodname+"'"+","+insAmount+","+"'"+insUnit+"'"+")";
DBBean.query(sqlStatement);
DBBean.query("delete * from sjcg_table where amount<=0");
}
%>
<a href="demo_main.htm">Database created...Click here to return</a></p>
</body>
</html>
上述的开发工具综合应用介绍了基于Java开发电子商务应用系统的全过程,包括应用开发平台搭建、业务流程分析、JavaBean封装和JSP开发等内容,其中JSP开发中涉及到了通用SQL(查询和插入数据库表)和游标操作(生成实际采购表),基本可以完成任何网络数据库应用的需求。本实例基本上可以将前面介绍的基于Java的电子商务开发技术串接起来,指导读者进行电子商务应用开发。
本文地址:http://com.8s8s.com/it/it11903.htm