与数据库连接的封装

前几天的时候一直在做与数据库的连接，虽然也是用到了数据库连接池进行连接，但是与数据库连接这方面很混乱，所以在这儿学习一下。如何将数据库的链接做的更加简洁和清楚。

 

1，首先先定义一个Exception。建立一个package(org.hrs.report.Exception)。

package org.hrs.report.Exception;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.hrs.report.listener.AppListener;public class DAOException extends Exception {private static Log log = LogFactory.getLog(AppListener.class.getName());public String toSting(){String string = "��ݿ�t�ӻ�sql����쳣";log.info(string);return string;}}

 

2,建立一个package(org.hrs.report.common.inf)和一个接口。

package org.hrs.report.common.inf;import java.sql.Connection;import org.hrs.report.Exception.DAOException;public interface DAO {public Connection getConnection() throws DAOException;}

 

 

3,定义数据库连接池

package org.hrs.report.common;import javax.naming.Context;import javax.naming.InitialContext;import javax.naming.NamingException;import javax.sql.DataSource;public class Cache {private static Cache instance;private DataSource dataSource;  static {  try {instance=new Cache();} catch (NamingException e) {// TODO Auto-generated catch blocke.printStackTrace();}    }  private Cache() throws NamingException{ Context context=null;  try {context = new InitialContext();dataSource = (DataSource) context.lookup("java:/comp/env/HRS");} catch (NamingException e) {e.printStackTrace();}  }  public static Cache getInstance(){    return instance;  }    public DataSource getDataSource(){    return dataSource;  }}

 

4，将第二步的接口，实现它。

package org.hrs.report.common;import java.sql.Connection;import java.sql.SQLException;import javax.sql.DataSource;import org.hrs.report.Exception.DAOException;import org.hrs.report.common.inf.DAO;public class DAOBASE implements DAO {public Connection getConnection() throws DAOException {Connection connection=null;DataSource dataSource=Cache.getInstance().getDataSource();if(dataSource!=null){try {connection=dataSource.getConnection();} catch (SQLException e) { e.printStackTrace();}}return connection;}}

 

5，定义一些数据库的操作方法。

package org.hrs.report.common;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.sql.PreparedStatement;import org.hrs.report.Exception.DAOException;public final class DBoperator extends DAOBASE{    private Connection connection = null;private Statement cstmt = null;private ResultSet rstRet = null;      public void Execute(String sql) throws SQLException {try {connection = getConnection();cstmt = connection.createStatement();cstmt.executeQuery(sql);//dbclose();} catch (DAOException e1) {// TODO Auto-generated catch blocke1.printStackTrace();} finally {dbclose();}}    public ArrayList sqlExecute(String sql) throws SQLException {ArrayList alResult = new ArrayList();try {connection = getConnection();cstmt = connection.createStatement();rstRet = (ResultSet) cstmt.executeQuery(sql);ResultSetMetaData rsmd = rstRet.getMetaData();int numCols = rsmd.getColumnCount();while (rstRet.next()) {String strTempArray[] = new String[numCols];for (int i = 1; i <= numCols; i++) {strTempArray[i - 1] = rstRet.getString(i) == null ? " " : rstRet.getString(i);}alResult.add(strTempArray);}//dbclose();} catch (DAOException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}  finally {dbclose();}return alResult;}    public int getCount(String sql) throws SQLException {int counts = 0;try {connection = getConnection();cstmt = connection.createStatement();rstRet = (ResultSet) cstmt.executeQuery(sql);while (rstRet.next()) {counts = Integer.parseInt(rstRet.getString(1));}//dbclose();} catch (DAOException e1) {// TODO Auto-generated catch blocke1.printStackTrace();} finally {dbclose();}return counts;}       public void procedureCall(String REPORTID, String reportdate,String reporttype, String sendtime) throws SQLException {try {String precedure = "{call reportSend(?,?,?,?)}";connection = getConnection();CallableStatement castmt = connection.prepareCall(precedure);castmt.setString(1, REPORTID);castmt.setString(2, reportdate);castmt.setString(3, reporttype);castmt.setString(4, sendtime);castmt.executeUpdate();dbclose();System.out.print("<script language='javascript'>alert('�洢�ɹ���');</script>");} catch (DAOException e1) {// TODO Auto-generated catch blocke1.printStackTrace();} finally {dbclose();}}        public void dbclose() throws SQLException {if (rstRet != null) {try {rstRet.close();} catch (SQLException e) {// TODO �Զ���� catch ��e.printStackTrace();}}try {if (cstmt != null)cstmt.close();} catch (SQLException e) {// TODO �Զ���� catch ��e.printStackTrace();}if (connection != null) {connection.close();}}        /**     * @功能介绍： 返回门诊科室的收费的统计信息     * @param sql     * @return     * @author 郑林     */    public double getOutserviceMoney(String sql){        double totleMoney=0.0;    double registerFree=0.0;    double clinicFree=0.0;    double accountFree=0.0;        try{    connection=getConnection();    cstmt=connection.createStatement();    rstRet=cstmt.executeQuery(sql);        while(rstRet.next()){    if(rstRet.getString(1)==null||rstRet.getString(1).equals("")){    registerFree=0.0;    }else{    registerFree=Double.parseDouble(rstRet.getString(1));    }    if(rstRet.getString(2)==null||rstRet.getString(2).equals("")||rstRet.getString(2).equals("null")){    clinicFree=0.0;    }else{    clinicFree = Double.parseDouble(rstRet.getString(2));    }    if(rstRet.getString(3)==null||rstRet.getString(3).equals("")||rstRet.getString(3).equals("null")){    accountFree=0.0;    }else{    accountFree = Double.parseDouble(rstRet.getString(3));    }    totleMoney=registerFree+clinicFree-accountFree;    }        }catch(DAOException e){    e.printStackTrace();    }catch(SQLException e){    e.printStackTrace();    }finally{    try {dbclose();} catch (SQLException e) {e.printStackTrace();}    }    return totleMoney;    } }

 

 

剩下的就是一些自己的业务层的操作了