本文以一个完整的JavaBean数据库访问程序简要说明jsp操作数据库。
本程序由3个bean组成，其中WebConstants中定义全局变量，ConnectionManager管理数据库连接，MainBean利用WebConstants和ConnectionManager操作数据库。
首先定义全局变量，如下：
package WebRelease;
public interface WebConstants
{
public static final String driverClass ="driverClass";
const userId is the user id to connect to database
public static final String userId ="comm";
const passWd is the user password to connect to database
public static final String passWd ="comm123";
const url is the url to connect to database
public static final String url="jdbc:oracle:thin:@10.2.0.1:1521:ORCL";
public static final String selectType ="select";
public static final String connection ="connection";
public static final String connError ="conError";
}
接着创建一个数据库连接管理bean，如下：
package WebRelease;
import java.io.*;
import java.beans.*;
import java.util.*;
import java.sql.*;
import WebRelease.WebConstants;
import oracle.jdbc.driver.*;
public class ConnectionManager implements WebConstants
{
private boolean debug = true;
protected Connection con;
protected DebugWriter writer;
PropertyChangeSupport pcs;
////////////////////////////////////////////////////////////////////////////////
public ConnectionManager()
{
pcs = new PropertyChangeSupport(this);
writer = new DebugWriter();
}
////////////////////////////////////////////////////////////////////////////////
public void setDebug(String b)
{
debug = b.equals("true");
}
////////////////////////////////////////////////////////////////////////////////
public void addPropertyChangeListener( PropertyChangeListener l)
{
pcs.addPropertyChangeListener(l);
}
////////////////////////////////////////////////////////////////////////////////
public void removePropertyChangeListener(PropertyChangeListener l)
{
pcs.removePropertyChangeListener(l);
}
////////////////////////////////////////////////////////////////////////////////
public void login()
{
try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(Exception e)
{
if(debug) { writer.writeDebug("Error setting driver:"+e.getMessage());}
}
try
{
con = DriverManager.getConnection(url,userId,passWd);
pcs.firePropertyChange(connection,null,con);
if(debug)
{
writer.writeDebug("connection succeded ! URL:"+url+"User:"+userId+
"Pwd:"+passWd);
}
}
catch(Exception e)
{
pcs.firePropertyChange(connError,null,e);
if(debug)
{
writer.writeDebug("connection failed ! URL:"+url+"User:"+userId+
"Pwd:"+passWd);
}
}
}
}
在MainBean中，侦听数据库是否连接，如果连接则对数据库进行操作：
package WebRelease;
import java.io.*;
import java.beans.*;
import java.util.*;
import java.sql.*;
import WebRelease.*;
public class MainBean implements PropertyChangeListener ,WebConstants
{
private boolean debug = true;
protected Connection con;
protected DebugWriter writer;
////////////////////////////////////////////////////////////////////////////////
public MainBean()
{
writer = new DebugWriter();
}
////////////////////////////////////////////////////////////////////////////////
public void propertyChange(PropertyChangeEvent evt)
{
String prop = evt.getPropertyName();
//see if we got a connection
if(prop.equals(connection))
{//ok so update the local connection
try
{
//make sure it really is a connection
con = (Connection)evt.getNewValue();
}
catch(Exception e)
{
writer.writeDebug("Error connecting to database"+e.getMessage());
}
}
}
////////////////////////////////////////////////////////////////////////////////
public void setConnectionManager(ConnectionManager cm)
{
if(cm != null)
{
// to remove the old listener from cm
// to avoid confusion in the mainbean
cm.removePropertyChangeListener(this);
cm.addPropertyChangeListener(this);
if(debug)
{
writer.writeDebug("MainBean;Set connection manager"+ cm.toString());
}
}
else
{
if(debug)
{
writer.writeDebug("MainBean;Tried to set a null connection manager");
}
}
}
////////////////////////////////////////////////////////////////////////////////
public boolean isConnected()
{
//see if datatabe is connected
return (con != null);
}
////////////////////////////////////////////////////////////////////////////////
public void processQuery()
{
try
{
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from hello order by id");
writer.writeDebug("select * from hello order by id");
while(rs.next())
{
writer.writeDebug("next");
String str=rs.getString("name");
writer.writeDebug(str+"   ");
}
rs.close();
}
catch(Exception e)
{
}
}
}
OK，现在我们来测试一下这个程序，创建一个测试页面：
<%@ page contentType="text/html;
