JAVA连接ORACLE

1. 把oracle/ora90/jdbc/lib/ 下的 classes12.jar复制到 j2sdk../jre/lib/ext下。

2. 在struts配置文件struts-config.xml中配置STRUTS数据源，这些数据源将由ActionServlet负责管理。

<struts-config>
  <data-sources>
   <data-source key="oracleDB1" type="org.apache.commons.dbcp.BasicDataSource">
    <set-property property="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
    <set-property property="url" value="jdbc:oracle:thin:@127.0.0.1:1521:oradb"/>
    <set-property property="maxActive" value="5"/>
    <set-property property="username" value="carol"/>
    <set-property property="password" value="carol"/>
    <set-property property="autoCommit" value="true"/>
   </data-source>

其中<data-sources>中可以包含多个<data-source>子元素，每个<data-source》子元素可以对应一个数据源连接池。

3. 定义模型类DataStore，在该类中封装了与数据库操作相关的业务逻辑。

package classmate;
import java.sql.*;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class DataStore {
 
 DataSource dataSource = null;
 private static Connection conn = null;
 private Statement stmt = null;
 private boolean hadErrors = false;
 
 //construct
 public DataStore(DataSource dataSource) throws Exception{
  this.dataSource = dataSource;
  conn = dataSource.getConnection();
  stmt = conn.createStatement();
 }
 
 //invoke transaction
 public void beginTransaction() throws SQLException{
  conn.setAutoCommit(false);
 }
 
 //perform transaction
 public void commitTransaction() throws SQLException{
  //hadErrors = false
  if(!hadErrors){
   conn.commit();
  }
  else{  //hadErrors = true
   conn.rollback();
   hadErrors = false;
  }
  hadErrors = false;
  conn.setAutoCommit(true);
 }
 
 //mark error
 public void ErrorOccur(){
  hadErrors = true;
 }
 
 //execute insert,delete,update
 synchronized public void execute(String sql) throws SQLException{
  if(stmt != null)
   stmt.executeUpdate(sql);
  else{
   Log log = LogFactory.getLog("mylog");
   log.error("fail to execute sql");
  }
 }
 
 //execute select
 synchronized public ResultSet read(String sql) throws SQLException{
  if(stmt != null){
   ResultSet tmp = null;
   tmp = stmt.executeQuery(sql);
   return tmp;
  }else{
   return null;
  }
 }
 
 // count of result
 synchronized public int readCount(String sql) throws SQLException{
  int nCount = 0;
  try{
   if(stmt != null){
    ResultSet tmp = null;
    tmp = stmt.executeQuery(sql);
    if(tmp != null && tmp.next()){
     nCount = tmp.getInt(1);
    }else{
     nCount = 0;
    }
   }
  }catch(SQLException e){
   nCount = 0;
  }
  return nCount;
 }
 
 // close db
 synchronized public void stop(){
  try{
   if(conn != null){
    conn.close();
    stmt.close();
   }
  }catch(Exception e){
   Log log = LogFactory.getLog("mylog");
   log.error("fail to close db- DataStore.java");
  }finally{
   conn = null;
  }
 }
}

4. 在Action Bean类中使用前面配置的数据源oracleDB1.

 package classmate;

import javax.servlet.ServletContext;
import javax.sql.DataSource;
import javax.servlet.http.*;
import java.sql.*;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionErrors;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import org.apache.struts.action.ActionMessages;
import org.apache.struts.action.ActionMessage;

public final class LoginAction extends Action{ 
public ActionForward execute(
 ActionMapping mapping,
 ActionForm form,
 HttpServletRequest request, 
 HttpServletResponse response) throws Exception {
 
 //get form
    UserForm userform = (UserForm) form;        
 String name = userform.getName();
 String psw = userform.getPsw();
 
 //get datasource
 ServletContext context = servlet.getServletContext();
 DataSource dataSource = (DataSource)context.getAttribute("oracleDB1");
 
 DataStore dataStore = new DataStore(dataSource);
 HttpSession session = request.getSession();
 
 String sql = "select xm from client where xm='"+name+"' and password='"+psw+"'";
 ResultSet rs = dataStore.read(sql);
 if(rs != null && rs.next())
  return mapping.findForward("successed");
 else{
  ActionMessages errors = new ActionMessages();
  errors.add(ActionMessages.GLOBAL_MESSAGE,new ActionMessage("label.deny"));
  if (!errors.isEmpty()) {
   saveErrors(request, errors);
  }
     return  mapping.findForward("failed"); 
    }
 }
}

不在配置文件struts-config.xml里配置数据源可以这样写：

package bit.jeffy.db;
import java.sql.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class DataStore {
 
 //the object of static factory pattern
 private static DataStore db = null;
 // connect to db
 private static Connection conn = null;
 private Statement stmt = null;
 private boolean hadErrors = false;
 
 //construct
 private DataStore() throws Exception{
  if(conn == null){
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:oradb",
     "carol","carol");
   stmt = conn.createStatement();
  }
 }
 
 //invoke transaction
 public void beginTransaction() throws SQLException{
  conn.setAutoCommit(false);
 }
 
 //perform transaction
 public void commitTransaction() throws SQLException{
  //hadErrors = false
  if(!hadErrors){
   conn.commit();
  }
  else{  //hadErrors = true
   conn.rollback();
   hadErrors = false;
  }
  hadErrors = false;
  conn.setAutoCommit(true);
 }
 
 //mark error
 public void ErrorOccur(){
  hadErrors = true;
 }
 
 //get DataStore Instance
 public static DataStore getInstance(){
  if (db == null){
   try{
    db = new DataStore();
   }catch(Exception e){
    conn = null;
    Log log = LogFactory.getLog("mylog");
    log.error("failed to access to database -DataStore.java");
    return null;
   }
  }
  return db;
 }
 .................................................
 
 }
}