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