JDBC连接及查询事例

package org.accp.news.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.accp.news.entities.*;

import org.apache.tomcat.dbcp.dbcp.BasicDataSource;

public class BaseDao {
 
 final static String DRIVER = "oracle.jdbc.driver.OracleDriver";
 final static String URL= "jdbc:oracle:thin:@localhost:1521:orcll";
 final static String USER= "lianxi";
 final static String PASS = "lianxi";
 final String SQL_PAGER_COUNT = "SELECT COUNT(1) FROM ({SQL_PAGER})";
 final String SQL_PAGER = "SELECT * FROM "+
         " (SELECT ROWNUM AS R,MODELA.*  FROM "+
         "  ({SQL_PAGER}) MODELA "+
         " WHERE ROWNUM <={TOP_COUNT}) MODEB "+
         " WHERE MODEB.R>{PASS_COUNT} ";
 
 static BasicDataSource ds = new BasicDataSource();
 static{
  ds.setDriverClassName(DRIVER);
  ds.setUrl(URL);
  ds.setUsername(USER);
  ds.setPassword(PASS);
 }
 /**
  * 关闭资源
  * @param st 预处理
  * @param conn 连接
  * @param rs 结果集
  */
 public void closeResource(ResultSet rs,Statement st,Connection conn){
  try {
   if(st !=null)st.close();
   if(conn !=null)conn.close();
   if(rs !=null)rs.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
 /** 关闭资源 */
 public void closeResource(ResultSet rs) {
  Statement st = null;
  Connection c = null;
  try {
   
   if (rs != null) {
    st = rs.getStatement();
   }
   if (st!=null){
    c = st.getConnection();
   }

  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } finally {
   try {
    if (rs!=null){rs.close();}
    if (st!=null){st.close();}
    if (c!=null){c.close();}
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 }
 /**
  * 执行查询语句
  * @param sql sql语句
  * @param params sql语句中的参数
  * @return 返回一个结果集
  */
 protected ResultSet executeQuery(String sql,Object...params){
  Connection c = null;
  PreparedStatement pst = null;
  ResultSet rs = null;
   try {
    c = ds.getConnection();
    pst = c.prepareStatement(sql);
    if (params!=null && params.length>0){
     for(int i=0; i<params.length; i++){
      pst.setObject(i+1, params[i]);
     }
    }
    rs = pst.executeQuery();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
   return rs;
 }
 
 /**
  * 执行增删改语句
  * @param sql 增删改命令
  * @param params 增删改命令参数
  * @return 执行成功返回1，否则返回-1
  */
 public int excuteUpdate(String sql,Object...params){
  Connection conn =null;
  PreparedStatement pst =null;
  int result =-1;
  try {
   conn=ds.getConnection();
   pst =conn.prepareStatement(sql);
   if(params !=null&&params.length>0){
    for(int i =0;i<params.length;i++){
     pst.setObject(i+1, params[i]);
    }
   }
   result=pst.executeUpdate();
  } catch (SQLException e) {
   e.printStackTrace();
  }finally{
   this.closeResource(null,pst, conn);
  }
  return result;
 }
 
 /**
  * 查询单行单列
  * */
 protected Object executeScaler(String sql, Object... params) {
  Connection c = null;
  PreparedStatement pst = null;
  ResultSet rs = null;
  Object ret = null;

  try {
   c = ds.getConnection();
   pst = c.prepareStatement(sql);
   if (params != null && params.length > 0) {
    for (int i = 0; i < params.length; i++) {
     pst.setObject(i + 1, params[i]);
    }
   }
   rs = pst.executeQuery();
   if (rs != null && rs.next()) {
    ret = rs.getObject(1);
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } finally {
   closeResource(rs, pst, c);
  }

  return ret;
 }
 
 /*分页查询*/
 public void pager(PageInfo pi){
  //计算总条数
  int recordCount =  Integer.parseInt(executeScaler(SQL_PAGER_COUNT.replace("{SQL_PAGER}", pi.getSqlPager())).toString());
  pi.setRecordCount(recordCount);//设置总条数
  pi.setPageCount(
    pi.getRecordCount()%pi.getPageSize()==0?
      pi.getRecordCount()/pi.getPageSize():
       pi.getRecordCount()/pi.getPageSize()+1 
  );//设置总页数
  //执行分页
  pi.setResult(
    executeQuery(
      SQL_PAGER
       .replace("{SQL_PAGER}", pi.getSqlPager())
       .replace("{TOP_COUNT}", pi.getPageIndex()*pi.getPageSize()+"")
       .replace("{PASS_COUNT}", (pi.getPageIndex()-1)*pi.getPageSize()+"")       
    )
  );
 }
 
}