struts。。在普通javabean中连接数据库 在普通的javabean中连接数据库,需要得到DataBase对象,原来在action中有servlet等自带的属性可以用,在普通bean里没有。那么该如何写代码来连接数据库??觉得应该在javabean中封装数据库连接,然后在action中调用,不知道对不对?? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 为什么要在bean里连接数据库呢?在action里连就好import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.sql.DataSource;import org.apache.struts.action.Action;import org.apache.struts.action.ActionForm;import org.apache.struts.action.ActionForward;import org.apache.struts.action.ActionMapping;import com.duylz.Log.Log;import com.duylz.mainview.AnnouncementBean;public class LogonAction extends Action{ public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception { ActionForward forward = null; String username = ((LogonForm)form).getUsername(); String password = ((LogonForm)form).getPassword(); Connection con = null;; Statement st = null; ResultSet rsLogon = null; ResultSet rsMain = null; try{ DataSource dataSource = getDataSource(request,"dataSource"); con = dataSource.getConnection(); st = con.createStatement(); String sqlSent = "select password from sys_user where username='" + username + "'"; rsLogon = st.executeQuery(sqlSent); String dbPassword = null; if(rsLogon.next()){ dbPassword = rsLogon.getString(1); } if(dbPassword == null || !dbPassword.equals(password)){ forward = mapping.findForward("failure"); }else{ forward = mapping.findForward("success"); } //查询登陆后的首页的记录 String selAnnoun = "select id,announ_content,announ_date,announ_papers,memo from announcement"; rsMain = st.executeQuery(selAnnoun); List lstAnnoun = rs2List(rsMain); request.setAttribute("mainview" , lstAnnoun); }catch(Exception e){ e.printStackTrace(); }finally{ try{ rsLogon.close(); rsMain.close(); st.close(); con.close(); }catch(Exception e){ e.printStackTrace(); } } return forward; } private List rs2List(ResultSet rs){ List lst = new ArrayList(); try{ while(rs.next()){ AnnouncementBean ab = new AnnouncementBean(); ab.setId(rs.getInt(1)); ab.setAnnoun_content(rs.getString(2)); ab.setAnnoun_date(rs.getString(3)); ab.setAnnoun_papers(rs.getString(4)); ab.setMemo(rs.getString(5)); lst.add(ab); //Log.showLog(ab.toString()); } }catch(Exception e){ e.printStackTrace(); } return lst; }} 2楼,你不觉得在action中过多带有数据库操作不是很合适吗?action 只和javabean通信,javabean操作数据库,提供和action通信的接口。DataSource dataSource = getDataSource(request,"dataSource");这个是action自带的函数,所以在action中操作数据库很容易,但我想把数据库逻辑操作单独封装,使action对数据库操作是透明的,该如何写?? package com.newyl.sms8adm.db;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import javax.sql.DataSource;import javax.naming.InitialContext;import javax.naming.Context;import javax.naming.NamingException;import java.sql.CallableStatement;import java.sql.ResultSetMetaData;import java.util.Hashtable;import java.util.ArrayList;import com.newyl.sms8adm.db.ApplicationException;import com.newyl.sms8adm.db.JNDINames;import com.newyl.sms8adm.db.Debug;/** * <p></p> * @version $Id: DBConn.java,v 1.1.1.1 2004/07/28 01:25:06 jeaf Exp $ * @since 13 May 2004 * @author CRM项目组 */public class DBTool{ private transient Connection dbConnection = null; private transient DataSource datasource = null; private String errorMessage=""; private String errorDesc = null; /** * 数据库连接通用类 */ public DBTool() throws NamingException,ApplicationException { try { datasource = (DataSource)DataSources.getInstance().getDataSource("FA_DataSource"); } catch (NamingException ne) { throw new ApplicationException(ne.getMessage()); } } /** * 数据库连接 */ public Connection getDBConnection() throws ApplicationException { try { dbConnection = datasource.getConnection(); } catch (SQLException se) { System.out.println(se.getMessage()); throw new ApplicationException(se.getMessage(),se.getErrorCode()); }catch(Exception e) { } return dbConnection; } /** * 关闭数据库连接*/ public void closeConnection() throws ApplicationException { try { if (dbConnection != null && !dbConnection.isClosed()) { dbConnection.close(); } } catch (SQLException se) { throw new ApplicationException(se.getMessage(),se.getErrorCode()); } } /** * 关闭结果集 * @param result */ public void closeResultSet(ResultSet result) throws ApplicationException { try { if (result != null) { result.close(); } } catch (SQLException se) { throw new ApplicationException(se.getMessage(),se.getErrorCode()); } } /** * 关闭执行语句 * @param stmt PreparedStatement * @param ctmt CallableStatement */ public void closeStatement(PreparedStatement stmt, CallableStatement ctmt) throws ApplicationException { try { if (stmt != null) { stmt.close(); } if (ctmt != null) { ctmt.close(); } } catch (SQLException se) { throw new ApplicationException(se.getMessage(),se.getErrorCode()); } } /** * 关闭执行语句 * @param stmt PreparedStatement */ public void closeStatement(PreparedStatement stmt)throws ApplicationException { try { if (stmt != null) { stmt.close(); } } catch (SQLException se) { throw new ApplicationException(se.getMessage(),se.getErrorCode()); } } /** * 通用的SQL查询/删除/修改和查询总记录的方法. * @param sql * @param hash */ public int execute(String sql,Hashtable hash) throws ApplicationException { PreparedStatement stmt = null; ResultSet rs = null; int i_count = -1; int flag = 0; dbConnection = this.getDBConnection(); try { stmt = dbConnection.prepareStatement(sql); if (hash.get("flag") != null) { flag = Integer.parseInt((String)hash.get("flag")); } switch (flag) { case 1: i_count = stmt.executeUpdate(); break; case 2: rs = stmt.executeQuery(); if (rs.next()) { Debug.print("DBConn.execute rs_count :" + rs.getString(1)+"\n"); i_count = Integer.parseInt(rs.getString(1)); } break; default: break; } this.closeStatement(stmt); this.closeResultSet(rs); this.closeConnection(); } catch (SQLException ex) { this.closeStatement(stmt); this.closeResultSet(rs); this.closeConnection(); throw new ApplicationException(ex.getMessage(),ex.getErrorCode()); } return i_count; } /** * 通用的SQL查询汇总的方法. * @param sql */ public float executeSum(String sql) throws ApplicationException { PreparedStatement stmt = null; ResultSet rs = null; float i_count = 0; int flag = 0; dbConnection = this.getDBConnection(); try { stmt = dbConnection.prepareStatement(sql); rs = stmt.executeQuery(); if (rs.next()) { i_count = Float.parseFloat(rs.getString(1)); } this.closeStatement(stmt); this.closeResultSet(rs); this.closeConnection(); } catch (SQLException ex) { this.closeStatement(stmt); this.closeResultSet(rs); this.closeConnection(); throw new ApplicationException(ex.getMessage(),ex.getErrorCode()); } return i_count; } /** * 通用的SQL查询的方法. * @param sql * @return ResultCollection[] */ public ResultCollection[] getResult(String sql) throws ApplicationException { PreparedStatement stmt = null; ResultSet rs = null; ResultCollection result[] = null; dbConnection = this.getDBConnection(); try { stmt = dbConnection.prepareStatement(sql); rs = stmt.executeQuery(); RecordOperation op = new RecordOperation(); RecordSet record = op.getResult(rs); if (record != null) { result = record.getResultCollection(); record.clear(); } } catch (SQLException ex) { System.out.println("error...." + ex.getMessage()); throw new ApplicationException(ex.getMessage(),ex.getErrorCode()); } finally { this.closeStatement(stmt); this.closeResultSet(rs); this.closeConnection(); } if (result == null) { Debug.print("DBConn.getResult: result is Null\n"); } return result; } /** * 不通过数据库 * @param data * @param new_rs * @param itemName * @param rows * @param op_flag * @return ResultCollection[] * @throws ApplicationException */ public ResultCollection[] getNoDBResult(ResultCollection[] data,ArrayList new_rs,String[] itemName,int rows,String op_flag) throws ApplicationException { ResultCollection result[] = null; RecordOperation op = new RecordOperation(); RecordSet record = op.getNoDBResult(data,new_rs,itemName,rows,op_flag); if (record != null) { result = record.getResultCollection(); record.clear() ; } return result; } /** * 获取元数据 * @param tableName * @throws ApplicationException */ public Hashtable getResultSetMetaData(String tableName) throws ApplicationException { Hashtable hash = new Hashtable(); ResultSetMetaData rsetMD = null; PreparedStatement stmt = null; ResultSet rs = null; ResultCollection result[] = null; dbConnection = this.getDBConnection(); String sql = "select * from " + tableName; try { stmt = dbConnection.prepareStatement(sql); rs = stmt.executeQuery(); rsetMD = rs.getMetaData(); for (int i = 0; i < rsetMD.getColumnCount(); i++) { hash.put(rsetMD.getColumnName(i+1).toLowerCase(),rsetMD.getColumnTypeName(i+1).toLowerCase()); } } catch (SQLException ex) { throw new ApplicationException(ex.getMessage(),ex.getErrorCode()); } finally { this.closeStatement(stmt); this.closeResultSet(rs); this.closeConnection(); } return hash; } /** * 通用的SQL查询/删除/修改和查询总记录的方法(支持事物处理). * @param sqlArray * @throws ApplicationException */ public void executeCommit(ArrayList sqlArray) throws ApplicationException { PreparedStatement stmt = null; ResultSet rs = null; dbConnection = this.getDBConnection(); try { dbConnection.setAutoCommit(false); if (sqlArray!=null) { for (int i = 0; i < sqlArray.size(); i++) { String array_sql = (String)sqlArray.get(i); if ((array_sql != null) && (array_sql.trim().length() > 0)) { stmt = dbConnection.prepareStatement((String)sqlArray.get(i)); stmt.executeUpdate(); } } } dbConnection.commit(); this.closeStatement(stmt); this.closeResultSet(rs); this.closeConnection(); } catch (SQLException ex) { try { dbConnection.rollback(); this.closeStatement(stmt); this.closeResultSet(rs); this.closeConnection(); } catch (SQLException sqlEx) { this.closeStatement(stmt); this.closeResultSet(rs); this.closeConnection(); throw new ApplicationException(ex.getMessage(),ex.getErrorCode()); } ex.printStackTrace(); throw new ApplicationException(ex.getMessage(),ex.getErrorCode()); } } /** * 返回系统的错误 */ public String getErrorDesc() { return this.errorDesc; } /** * 调用存储过程 * @param paraList * @param paraTypeList * @throws ApplicationException */ public ArrayList callProcedure(ArrayList paraList,ArrayList paraTypeList) throws ApplicationException { CallableStatement stored = null; ArrayList resultList = new ArrayList(); String procName = null; String paraType = null; String resultValue = null; String str = null; procName = (String)paraList.get(0); resultList.add(procName); str = "{ call " + procName + "("; for (int i = 1; i < paraList.size(); i++) { resultList.add((String)paraList.get(i)); if (i == paraList.size() - 1) { str = str+"?)"; } else { str = str+"?,"; } } if (paraList.size() == 1) { str = str+")}"; } else { str = str+"}"; } // Debug.print("str 403 : " + str); dbConnection = this.getDBConnection(); try { stored = dbConnection.prepareCall(str); for (int j = 1; j < paraList.size(); j++) { paraType = (String)paraTypeList.get(j); if (paraType.equals("1")) { stored.setString(j,(String)paraList.get(j)); } if (paraType.equals("2")) { stored.registerOutParameter(j,java.sql.Types.VARCHAR); } if (paraType.equals("3")) { stored.setString(j,(String)paraList.get(j)); stored.registerOutParameter(j,java.sql.Types.VARCHAR); } } stored.executeQuery(); for (int k = 1; k < paraTypeList.size(); k++) { paraType = (String)paraTypeList.get(k); if (paraType.equals("2") || paraType.equals("3")) { resultValue = stored.getString(k); resultList.set(k,resultValue); } } } catch (SQLException ex) { throw new ApplicationException(ex.getMessage(),ex.getErrorCode()); } finally { try { stored.close(); } catch (SQLException ex) { Debug.print(ex.getMessage()); } this.closeConnection(); } return resultList; }} 一般的话,一个业务流程用一个session,如果把数据的连接操作都放在javabean中会造成一个业务流程中频繁的连接和释放动作,造成不必要的数据库负载,合理的做法就是在action中从连接池取得session,然后将session作为参数传递到各个javabean中,一个业务流程结束后transaction提交,finally关闭session 多谢各位解答。举个例子:若查询数据库中在校生的信息,我建立OnCampusStudent的类,里面有相应学生信息以及存取方法。同时封装了连接数据库关闭连接和查询学生表的方法。在Action里我调用相应方法,同时可以在finally里关闭数据库连接。这样做有什么问题?象2楼的代码:Connection con = null;;Statement st = null;ResultSet rsLogon = null;ResultSet rsMain = null;try{DataSource dataSource = getDataSource(request,"dataSource");con = dataSource.getConnection();st = con.createStatement();String sqlSent = "select password from sys_user where username='" + username + "'";rsLogon = st.executeQuery(sqlSent);这部分代码是否可以封装在sys_user里,Action中调用方法就可以了?在Action中只要做判断就行? 5楼,你的DataSources从呵而来??还是写错了? Action中连接数据库的确是大错特错的做法...强烈的批评.MVC强调的3层结构完全打乱.action只不过是和页面的交互层,action中得到请求参数 传到业务逻辑层,业务逻辑层封装好传到Dao层这才是一个健壮的框架结构. 你可以写一个BaseDaopackage com.test.spring.dao;/** * <p>Title: </p> * <p>Description: </p> * <p>Copyright: Copyright (c) 2005</p> * <p>Company: </p> * @author not attributable * @version 1.0 */import java.io.InputStream;import java.io.OutputStream;import javax.naming.Context;import javax.sql.DataSource;import javax.naming.InitialContext;import java.lang.StringBuffer;import java.sql.ResultSet;import java.sql.Clob;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.Statement;import java.sql.*;public class BaseDao { /** * <ol> * <li> 取数据库连接池,并返回一个数据库的连接, * 用完此连接池后切记关闭。 * </ol> * @return conTemp Connection 对返回的连接要进行判断,以免异常得到空的连接。 */public Connection getConnection()throws Exception{ Connection conTemp=null; try{ Context initCtx = new InitialContext(); DataSource ds = (DataSource) initCtx.lookup("java:comp/env/SPRINGJNDI");//初始化连接池。 conTemp = ds.getConnection();//从连接池中得到连接 } catch(Exception e){ throw new Exception("从连接池中取连接,出现异常.请核对JDNI是否真确 "); } return conTemp; }} 然后你所有的其他Dao都继承该BaseDao,在业务逻辑层调用你自己的dao ;在业务层的代码里打开连接,然后把连接传到你自己的Dao中,最后在业务层关闭你打开的连接. 这样做的用以在于把业务逻辑集中到Service层.好处我想就不要多说了吧!? 1.lookup jndi得到datasource,然后getconnection即可2.javabean中连接数据库,然后在action中调用javabean 有没有可以不用jndi得到datasource的方法。。根据在struts-config.xml中的datasource来在javabean中获取数据库连接 struts2快疯了,急急急!!!!! 关于客户端与网页交互 后台所有修改,都修改不了,请高手指教呀 web服务器与浏览器 小弟初学JAVA,关于servlet有个小问题请教 数据库操作回滚问题?请教高手! servlet的调用问题 请帮忙!! jsp如何得到来路referrer? JSP/BEAN/SERVLET 哪一种方式效率更高一点呢? 怎样在jsp中select下拉框选择的参数传递给另外一个jsp页面? 急~~~~~~~~~` 在struts中点击链接后将链接文字显示到文本框?
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;import com.duylz.Log.Log;
import com.duylz.mainview.AnnouncementBean;public class LogonAction extends Action{
public ActionForward execute(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) throws Exception {
ActionForward forward = null;
String username = ((LogonForm)form).getUsername();
String password = ((LogonForm)form).getPassword();
Connection con = null;;
Statement st = null;
ResultSet rsLogon = null;
ResultSet rsMain = null;
try{
DataSource dataSource = getDataSource(request,"dataSource");
con = dataSource.getConnection();
st = con.createStatement();
String sqlSent = "select password from sys_user where username='" + username + "'";
rsLogon = st.executeQuery(sqlSent);
String dbPassword = null;
if(rsLogon.next()){
dbPassword = rsLogon.getString(1);
}
if(dbPassword == null || !dbPassword.equals(password)){
forward = mapping.findForward("failure");
}else{
forward = mapping.findForward("success");
}
//查询登陆后的首页的记录
String selAnnoun = "select id,announ_content,announ_date,announ_papers,memo from announcement";
rsMain = st.executeQuery(selAnnoun);
List lstAnnoun = rs2List(rsMain);
request.setAttribute("mainview" , lstAnnoun);
}catch(Exception e){
e.printStackTrace();
}finally{
try{
rsLogon.close();
rsMain.close();
st.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
return forward;
}
private List rs2List(ResultSet rs){
List lst = new ArrayList();
try{
while(rs.next()){
AnnouncementBean ab = new AnnouncementBean();
ab.setId(rs.getInt(1));
ab.setAnnoun_content(rs.getString(2));
ab.setAnnoun_date(rs.getString(3));
ab.setAnnoun_papers(rs.getString(4));
ab.setMemo(rs.getString(5));
lst.add(ab);
//Log.showLog(ab.toString());
}
}catch(Exception e){
e.printStackTrace();
}
return lst;
}
}
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.Context;
import javax.naming.NamingException;
import java.sql.CallableStatement;
import java.sql.ResultSetMetaData;import java.util.Hashtable;
import java.util.ArrayList;import com.newyl.sms8adm.db.ApplicationException;
import com.newyl.sms8adm.db.JNDINames;
import com.newyl.sms8adm.db.Debug;/**
* <p></p>
* @version $Id: DBConn.java,v 1.1.1.1 2004/07/28 01:25:06 jeaf Exp $
* @since 13 May 2004
* @author CRM项目组
*/public class DBTool
{
private transient Connection dbConnection = null;
private transient DataSource datasource = null;
private String errorMessage="";
private String errorDesc = null; /**
* 数据库连接通用类
*/
public DBTool() throws NamingException,ApplicationException
{
try
{
datasource = (DataSource)DataSources.getInstance().getDataSource("FA_DataSource");
} catch (NamingException ne) {
throw new ApplicationException(ne.getMessage());
}
} /**
* 数据库连接
*/
public Connection getDBConnection() throws ApplicationException
{
try
{
dbConnection = datasource.getConnection();
} catch (SQLException se) {
System.out.println(se.getMessage());
throw new ApplicationException(se.getMessage(),se.getErrorCode());
}catch(Exception e) {
}
return dbConnection;
} /**
* 关闭数据库连接*/
public void closeConnection() throws ApplicationException
{
try
{
if (dbConnection != null && !dbConnection.isClosed())
{
dbConnection.close();
}
} catch (SQLException se) {
throw new ApplicationException(se.getMessage(),se.getErrorCode());
}
} /**
* 关闭结果集
* @param result
*/
public void closeResultSet(ResultSet result) throws ApplicationException
{
try
{
if (result != null)
{
result.close();
}
} catch (SQLException se) {
throw new ApplicationException(se.getMessage(),se.getErrorCode());
}
} /**
* 关闭执行语句
* @param stmt PreparedStatement
* @param ctmt CallableStatement
*/
public void closeStatement(PreparedStatement stmt, CallableStatement ctmt) throws ApplicationException
{
try
{
if (stmt != null)
{
stmt.close();
} if (ctmt != null)
{
ctmt.close();
}
} catch (SQLException se) {
throw new ApplicationException(se.getMessage(),se.getErrorCode());
}
} /**
* 关闭执行语句
* @param stmt PreparedStatement
*/
public void closeStatement(PreparedStatement stmt)throws ApplicationException
{
try
{
if (stmt != null)
{
stmt.close();
}
} catch (SQLException se) {
throw new ApplicationException(se.getMessage(),se.getErrorCode());
}
} /**
* 通用的SQL查询/删除/修改和查询总记录的方法.
* @param sql
* @param hash
*/
public int execute(String sql,Hashtable hash) throws ApplicationException
{
PreparedStatement stmt = null;
ResultSet rs = null;
int i_count = -1;
int flag = 0;
dbConnection = this.getDBConnection();
try
{
stmt = dbConnection.prepareStatement(sql);
if (hash.get("flag") != null)
{
flag = Integer.parseInt((String)hash.get("flag"));
}
switch (flag)
{
case 1:
i_count = stmt.executeUpdate();
break;
case 2:
rs = stmt.executeQuery();
if (rs.next())
{
Debug.print("DBConn.execute rs_count :" + rs.getString(1)+"\n");
i_count = Integer.parseInt(rs.getString(1));
}
break;
default:
break;
}
this.closeStatement(stmt);
this.closeResultSet(rs);
this.closeConnection();
} catch (SQLException ex) {
this.closeStatement(stmt);
this.closeResultSet(rs);
this.closeConnection();
throw new ApplicationException(ex.getMessage(),ex.getErrorCode());
}
return i_count;
} /**
* 通用的SQL查询汇总的方法.
* @param sql
*/
public float executeSum(String sql) throws ApplicationException
{
PreparedStatement stmt = null;
ResultSet rs = null;
float i_count = 0;
int flag = 0;
dbConnection = this.getDBConnection();
try {
stmt = dbConnection.prepareStatement(sql);
rs = stmt.executeQuery();
if (rs.next())
{
i_count = Float.parseFloat(rs.getString(1));
} this.closeStatement(stmt);
this.closeResultSet(rs);
this.closeConnection();
} catch (SQLException ex) {
this.closeStatement(stmt);
this.closeResultSet(rs);
this.closeConnection();
throw new ApplicationException(ex.getMessage(),ex.getErrorCode());
}
return i_count;
} /**
* 通用的SQL查询的方法.
* @param sql
* @return ResultCollection[]
*/
public ResultCollection[] getResult(String sql) throws ApplicationException
{
PreparedStatement stmt = null;
ResultSet rs = null;
ResultCollection result[] = null; dbConnection = this.getDBConnection();
try {
stmt = dbConnection.prepareStatement(sql);
rs = stmt.executeQuery();
RecordOperation op = new RecordOperation();
RecordSet record = op.getResult(rs);
if (record != null)
{ result = record.getResultCollection();
record.clear();
}
} catch (SQLException ex) {
System.out.println("error...." + ex.getMessage());
throw new ApplicationException(ex.getMessage(),ex.getErrorCode());
} finally { this.closeStatement(stmt);
this.closeResultSet(rs);
this.closeConnection();
}
if (result == null)
{
Debug.print("DBConn.getResult: result is Null\n");
}
return result;
}
* 不通过数据库
* @param data
* @param new_rs
* @param itemName
* @param rows
* @param op_flag
* @return ResultCollection[]
* @throws ApplicationException
*/
public ResultCollection[] getNoDBResult(ResultCollection[] data,ArrayList new_rs,String[] itemName,int rows,String op_flag) throws ApplicationException
{
ResultCollection result[] = null;
RecordOperation op = new RecordOperation();
RecordSet record = op.getNoDBResult(data,new_rs,itemName,rows,op_flag);
if (record != null) {
result = record.getResultCollection();
record.clear() ;
}
return result;
} /**
* 获取元数据
* @param tableName
* @throws ApplicationException
*/
public Hashtable getResultSetMetaData(String tableName) throws ApplicationException
{
Hashtable hash = new Hashtable();
ResultSetMetaData rsetMD = null;
PreparedStatement stmt = null;
ResultSet rs = null;
ResultCollection result[] = null;
dbConnection = this.getDBConnection();
String sql = "select * from " + tableName;
try {
stmt = dbConnection.prepareStatement(sql);
rs = stmt.executeQuery();
rsetMD = rs.getMetaData(); for (int i = 0; i < rsetMD.getColumnCount(); i++)
{
hash.put(rsetMD.getColumnName(i+1).toLowerCase(),rsetMD.getColumnTypeName(i+1).toLowerCase());
}
} catch (SQLException ex) {
throw new ApplicationException(ex.getMessage(),ex.getErrorCode());
} finally {
this.closeStatement(stmt);
this.closeResultSet(rs);
this.closeConnection();
}
return hash;
} /**
* 通用的SQL查询/删除/修改和查询总记录的方法(支持事物处理).
* @param sqlArray
* @throws ApplicationException
*/
public void executeCommit(ArrayList sqlArray) throws ApplicationException
{
PreparedStatement stmt = null;
ResultSet rs = null;
dbConnection = this.getDBConnection();
try
{
dbConnection.setAutoCommit(false);
if (sqlArray!=null)
{
for (int i = 0; i < sqlArray.size(); i++)
{
String array_sql = (String)sqlArray.get(i);
if ((array_sql != null) && (array_sql.trim().length() > 0))
{
stmt = dbConnection.prepareStatement((String)sqlArray.get(i));
stmt.executeUpdate();
}
}
}
dbConnection.commit();
this.closeStatement(stmt);
this.closeResultSet(rs);
this.closeConnection();
} catch (SQLException ex) {
try
{
dbConnection.rollback();
this.closeStatement(stmt);
this.closeResultSet(rs);
this.closeConnection();
} catch (SQLException sqlEx) {
this.closeStatement(stmt);
this.closeResultSet(rs);
this.closeConnection();
throw new ApplicationException(ex.getMessage(),ex.getErrorCode());
}
ex.printStackTrace();
throw new ApplicationException(ex.getMessage(),ex.getErrorCode());
}
} /**
* 返回系统的错误
*/
public String getErrorDesc()
{
return this.errorDesc;
} /**
* 调用存储过程
* @param paraList
* @param paraTypeList
* @throws ApplicationException
*/
public ArrayList callProcedure(ArrayList paraList,ArrayList paraTypeList) throws ApplicationException
{
CallableStatement stored = null;
ArrayList resultList = new ArrayList();
String procName = null;
String paraType = null;
String resultValue = null;
String str = null;
procName = (String)paraList.get(0);
resultList.add(procName);
str = "{ call " + procName + "(";
for (int i = 1; i < paraList.size(); i++)
{
resultList.add((String)paraList.get(i));
if (i == paraList.size() - 1)
{
str = str+"?)";
} else {
str = str+"?,";
}
}
if (paraList.size() == 1)
{
str = str+")}";
} else {
str = str+"}";
}
// Debug.print("str 403 : " + str);
dbConnection = this.getDBConnection();
try {
stored = dbConnection.prepareCall(str);
for (int j = 1; j < paraList.size(); j++)
{
paraType = (String)paraTypeList.get(j);
if (paraType.equals("1"))
{
stored.setString(j,(String)paraList.get(j));
}
if (paraType.equals("2"))
{
stored.registerOutParameter(j,java.sql.Types.VARCHAR);
}
if (paraType.equals("3"))
{
stored.setString(j,(String)paraList.get(j));
stored.registerOutParameter(j,java.sql.Types.VARCHAR);
}
} stored.executeQuery();
for (int k = 1; k < paraTypeList.size(); k++)
{
paraType = (String)paraTypeList.get(k);
if (paraType.equals("2") || paraType.equals("3"))
{
resultValue = stored.getString(k);
resultList.set(k,resultValue);
}
}
} catch (SQLException ex) {
throw new ApplicationException(ex.getMessage(),ex.getErrorCode());
} finally {
try
{
stored.close();
} catch (SQLException ex) {
Debug.print(ex.getMessage());
}
this.closeConnection(); }
return resultList;
}
}
举个例子:
若查询数据库中在校生的信息,我建立OnCampusStudent的类,里面有相应学生信息以及存取方法。同时封装了连接数据库关闭连接和查询学生表的方法。在Action里我调用相应方法,同时可以在finally里关闭数据库连接。这样做有什么问题?象2楼的代码:
Connection con = null;;
Statement st = null;
ResultSet rsLogon = null;
ResultSet rsMain = null;
try{
DataSource dataSource = getDataSource(request,"dataSource");
con = dataSource.getConnection();
st = con.createStatement();
String sqlSent = "select password from sys_user where username='" + username + "'";
rsLogon = st.executeQuery(sqlSent);
这部分代码是否可以封装在sys_user里,Action中调用方法就可以了?在Action中只要做判断就行?
package com.test.spring.dao;
/**
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2005</p>
* <p>Company: </p>
* @author not attributable
* @version 1.0
*/import java.io.InputStream;
import java.io.OutputStream;
import javax.naming.Context;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import java.lang.StringBuffer;
import java.sql.ResultSet;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.*;public class BaseDao {
/**
* <ol>
* <li> 取数据库连接池,并返回一个数据库的连接,
* 用完此连接池后切记关闭。
* </ol>
* @return conTemp Connection 对返回的连接要进行判断,以免异常得到空的连接。
*/public Connection getConnection()throws Exception{
Connection conTemp=null;
try{
Context initCtx = new InitialContext();
DataSource ds = (DataSource) initCtx.lookup("java:comp/env/SPRINGJNDI");//初始化连接池。
conTemp = ds.getConnection();//从连接池中得到连接
}
catch(Exception e){
throw new Exception("从连接池中取连接,出现异常.请核对JDNI是否真确 "); }
return conTemp;
}
}