与数据库连接的封装

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

 

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 block
		e.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 block
			e1.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 block
			e1.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 block
			e1.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 block
			e1.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;
    } 
}

 

 

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