与数据库连接的封装
前几天的时候一直在做与数据库的连接，虽然也是用到了数据库连接池进行连接，但是与数据库连接这方面很混乱，所以在这儿学习一下。如何将数据库的链接做的更加简洁和清楚。
?
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; } }?
?
剩下的就是一些自己的业务层的操作了