JavaBean操作Oracle資料庫

oracle|資料|資料庫

本文以一個完整的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; charset=gb2312" %>

<html>
<head>
<title>
hello
</title>
</head>
<body bgcolor="#ffffc0">
<h1>
<jsp:useBean id="main" class="WebRelease.MainBean" scope ="session"/>
<jsp:useBean id="con" class="WebRelease.ConnectionManager" scope ="session"/>
<%
main.setConnectionManager(con);
con.login();
%>
<%
if(main.isConnected())
out.print("mainbean connect success!!!");
main.processQuery();

%>

</h1>

</body>

</html>

在tomcat中瀏覽這個頁面，我們應該可以看到輸出“Mainbean connect sucess!!!”

注釋不是太多，希望你能看明白。