jdbc连接数据库BaseDao类.doc

package com.dao.impl;

import java.sql.*;

import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;

public class BaseDao {

private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl2";
private static final String USRENAME = "zhangsan";
private static final String PASSWORD = "123";

// 仅作为返回ResultSet查询时使用
private Connection conn = null;
private PreparedStatement pst = null;
private ResultSet rs = null;

/**
* 返回连接
*
* @return Connection
*/
private Connection getConnection() {

try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USRENAME, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}

return conn;
}

/**
* 返回ResultSet,需每次实例化一个BaseDao,然后调用CloseALL方法关闭对象
*
* @param sql
* 预编译的 SQL 语句
* @param param
* 预编译的 SQL 语句中的‘？’参数的字符串数组
*
*/
public ResultSet executeQuerySQL(String sql, Object... param) {
try {
conn = getConnection(); // 得到数据库连接
pst = conn.prepareStatement(sql); // 得到PreparedStatement对象
if (param != null) {
for (int i = 0; i < param.length; i++) {
pst.setObject(i + 1, param[i]); // 为预编译sql设置参数
}
}
rs = pst.executeQuery(); // 执行SQL语句
} catch (Exception e) {
e.printStackTrace(); // 处理ClassNotFoundException异常
}
return rs;
}

/**
* 添，删，改
*
* @param sql
* @param param
* @return
*/
public int executeUpdate(String sql, Object... param) {
conn = getConnection();
int row = 0;
try {
pst = conn.prepareStatement(sql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
pst.setObject(i + 1, param[i]);
}
}

row = pst.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
closeALL(null, pst, conn);
}
return row;
}

/**
* 返回Result
*
* @param sql
* @param param
* @return
*/
public Result executeQuery(String sql, Object... param) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
Result rst = null;
try {
conn = getConnection(); // 得到数据库连接
pst = conn.prepareStatement(sql); // 得到PreparedStatement对象
if (param != null) {
for (int i = 0; i < param.length; i++) {
pst.setObject(i + 1, param[i]); // 为预编译sql设置参数
}
}
rs = pst.executeQuery(); // 执行SQL语句
rst = ResultSupport.toResult(rs);
// 关闭连接对象
rs.close();
pst.close();
conn.close();
} catch (Exception e) {
e.printStackTrace(); // 处理ClassNotFoundException异常
}
return rst;
}

/**
* 查询单个值,如count,sum,max,avg等
*
* @param sql
* @param param
* @return
*/

public Object executeScalar(String sql, Object... param) {
Connection conn = getConnection();
PreparedStatement pst = null;
ResultSet rs = null;
Object obj = null;
try {
pst = conn.prepareStatement(sql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
pst.setObject(i + 1, param[i]);
}
}
rs = pst.executeQuery();
obj = rs.getObject(1);
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeALL(null, pst, conn);
}
return obj;
}

// 关闭ResultSet查询时的对象
public void closeALL() {
try {
if (rs != null)
rs.close();
if (pst != null)
pst.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

/**
* 关闭连接
*
* @param conn
* Connection
*/
public void closeALL(ResultSet rs, Statement pst, Connection conn) {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (conn != null && (!conn.isClosed())) {
conn.close();
}
} catch (SQLException sqlEx) {
sqlEx.printStackTrace();
}

}

}