站内搜索: 请输入搜索关键词

当前页面: 开发资料首页JSP 专题struts。。在普通javabean中连接数据库

struts。。在普通javabean中连接数据库

摘要: struts。。在普通javabean中连接数据库


在普通的javabean中连接数据库,需要得到DataBase对象,原来在action中有servlet等自带的属性可以用,在普通bean里没有。那么该如何写代码来连接数据库??

觉得应该在javabean中封装数据库连接,然后在action中调用,不知道对不对??


为什么要在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;

/**
*
* @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从呵而来??还是写错了?


顶一下


好东西!up


倒,谁写下代码,需要什么库,很简单的。。。。。5楼的代码有点错误也。。。

不要老是顶的。。。。发表一下见解


Action中连接数据库的确是大错特错的做法...强烈的批评.MVC强调的3层结构完全打乱.action只不过是和页面的交互层,action中得到请求参数 传到业务逻辑层,业务逻辑层封装好传到Dao层这才是一个健壮的框架结构. 你可以写一个BaseDao
package com.test.spring.dao;


/**
*

Title:


*

Description:


*

Copyright: Copyright (c) 2005


*

Company:


* @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 {
/**
*

    *
  1. 取数据库连接池,并返回一个数据库的连接,
    * 用完此连接池后切记关闭。
    *

* @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中获取数据库连接。。。


↑返回目录
前一篇: 怎样在jsp中select下拉框选择的参数传递给另外一个jsp页面? 急~~~~~~~~~`
后一篇: 在struts中点击链接后将链接文字显示到文本框?