|
![]() |
名片设计 CorelDRAW Illustrator AuotoCAD Painter 其他软件 Photoshop Fireworks Flash |
|
基于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)为: 图1 计划采购表插入主页面 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> ② 查询操作 该查询主页面主要提供对三个数据库表的条件查询功能,如下图所示: 图2 查询主页面 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的电子商务开发技术串接起来,指导读者进行电子商务应用开发。 返回类别: 教程 上一教程: 用JAVABEAN实现文件上载 下一教程: 如何使用JSP连接DB2数据库 您可以阅读与"全面解析JDBC(六)"相关的教程: · 全面解析JDBC(五) · 全面解析JDBC(四) · 全面解析JDBC(三) · 全面解析JDBC(七) · 全面解析JDBC(八) |
![]() ![]() |
快精灵印艺坊 版权所有 |
首页![]() ![]() ![]() ![]() ![]() ![]() ![]() |