问题描述

oa系统怎么使用jdbc链接数据库调用查询方法最后在jsp上分页显示？？？

表名：users ， 用户字段：realname

要求

效果：

解决方案

http://blog.csdn.net/aaabendan/article/details/5442144

解决方案二：

http://download.csdn.net/detail/zhai56565/5885775

解决方案三：

JdbcTemplate类

package com.cloudwebsoft.framework.db;

import cn.js.fan.db.ResultIterator;
import cn.js.fan.db.ResultWrapper;
import cn.js.fan.db.SQLFilter;
import cn.js.fan.util.StrUtil;
import cn.js.fan.web.Global;
import com.cloudwebsoft.framework.util.LogUtil;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.HashMap;
import java.util.Vector;
import org.apache.log4j.Logger;

public class JdbcTemplate
{
  int rowCount = 0;
  int colCount = 0;
  int pageSize = 10;
  public int curPage = 1;
  public long total = 0L;
  Logger logger;
  Connection connection = null;
  String connName;
  private boolean autoClose = true;

  public JdbcTemplate()
  {
    this.connection = new Connection(Global.getDefaultDB());
    this.connName = this.connection.connName;
    this.logger = Logger.getLogger(JdbcTemplate.class.getName());
  }

  public JdbcTemplate(Connection conn) {
    this.connection = conn;
    this.connName = this.connection.connName;
    this.logger = Logger.getLogger(JdbcTemplate.class.getName());
  }

  public JdbcTemplate(DataSource ds) {
    this.connection = ds.getConnection();
    this.connName = this.connection.connName;
    this.logger = Logger.getLogger(JdbcTemplate.class.getName());
  }

  public JdbcTemplate(DataSource ds, int curPage, int pageSize) {
    this.connection = ds.getConnection();
    this.connName = this.connection.connName;
    this.logger = Logger.getLogger(JdbcTemplate.class.getName());
    this.curPage = curPage;
    this.pageSize = pageSize;
  }

  public Connection getConnection() {
    return this.connection;
  }

  public long getTotal() {
    return this.total;
  }

  public int getColumnCount() {
    return this.colCount;
  }

  public int getRowCount() {
    return this.rowCount;
  }

  public boolean isAutoClose() {
    return this.autoClose;
  }

  public ResultIterator executeQuery(String sql)
    throws SQLException
  {
    ResultIterator ri = new ResultIterator();

    this.rowCount = 0;
    this.colCount = 0;
    ResultSet rs = null;
    Vector result = null;
    HashMap mapIndex = new HashMap();
    try
    {
      checkConnection();

      rs = this.connection.executeQuery(sql);
      if (rs == null) {
        return ri;
      }

      ResultSetMetaData rm = rs.getMetaData();
      this.colCount = rm.getColumnCount();
      for (int i = 1; i <= this.colCount; i++) {
        mapIndex.put(rm.getColumnName(i).toUpperCase(), new Integer(i));
      }
      result = new Vector();
      ResultWrapper rsw = new ResultWrapper(rs);
      while (rsw.next()) {
        Vector row = new Vector();
        for (int i = 0; i < this.colCount; i++)
          row.addElement(rsw.getObject(i + 1));
        result.addElement(row);
        this.rowCount += 1;
      }
    }
    finally
    {
      if (rs != null) {
        try {
          rs.close(); } catch (Exception e) {
        }
        rs = null;
      }

      if ((this.autoClose) && (this.connection.getAutoCommit())) {
        this.connection.close();
        this.connection = null;
      }
    }
    return new ResultIterator(result, mapIndex);
  }

  public ResultIterator executeQueryTFO(String sql)
    throws SQLException
  {
    ResultIterator ri = new ResultIterator();

    this.rowCount = 0;
    this.colCount = 0;
    ResultSet rs = null;
    Vector result = null;
    HashMap mapIndex = new HashMap();
    try
    {
      checkConnection();

      rs = this.connection.executeQueryTFO(sql);
      if (rs == null) {
        return ri;
      }

      ResultSetMetaData rm = rs.getMetaData();
      this.colCount = rm.getColumnCount();
      for (int i = 1; i <= this.colCount; i++) {
        mapIndex.put(rm.getColumnName(i).toUpperCase(), new Integer(i));
      }
      result = new Vector();
      ResultWrapper rsw = new ResultWrapper(rs);
      while (rsw.next()) {
        Vector row = new Vector();
        for (int i = 0; i < this.colCount; i++)
          row.addElement(rsw.getObject(i + 1));
        result.addElement(row);
        this.rowCount += 1;
      }
    }
    finally
    {
      if (rs != null) {
        try {
          rs.close(); } catch (Exception e) {
        }
        rs = null;
      }

      if ((this.autoClose) && (this.connection.getAutoCommit())) {
        this.connection.close();
        this.connection = null;
      }
    }
    return new ResultIterator(result, mapIndex);
  }

  public void addBatch(String sql) throws SQLException {
    this.connection.addBatch(sql);
  }

  public int[] executeBatch() throws SQLException {
    int[] r = null;
    try
    {
      checkConnection();
      r = this.connection.executeBatch();
    }
    finally {
      if ((this.autoClose) && (this.connection.getAutoCommit())) {
        this.connection.close();
        this.connection = null;
      }
    }
    return r;
  }

  public static void fillPreparedStatement(PreparedStatement ps, Object[] objectParams)
    throws SQLException
  {
    if (objectParams == null) {
      return;
    }
    int len = objectParams.length;
    for (int i = 1; i <= len; i++) {
      Object obj = objectParams[(i - 1)];

      if (obj == null)
      {
        ps.setNull(i, 12);
      }
      else if ((obj instanceof String))
        ps.setString(i, (String)obj);
      else if ((obj instanceof Integer))
        ps.setInt(i, ((Integer)obj).intValue());
      else if ((obj instanceof Date))
        ps.setTimestamp(i, new Timestamp(((Date)obj).getTime()));
      else if ((obj instanceof Timestamp))
        ps.setTimestamp(i, (Timestamp)obj);
      else if ((obj instanceof Long))
        ps.setLong(i, ((Long)obj).longValue());
      else if ((obj instanceof Short))
        ps.setShort(i, ((Short)obj).shortValue());
      else if ((obj instanceof Double))
        ps.setDouble(i, ((Double)obj).doubleValue());
      else if ((obj instanceof Float))
        ps.setFloat(i, ((Float)obj).floatValue());
      else if ((obj instanceof Clob))
        ps.setClob(i, (Clob)obj);
      else if ((obj instanceof Blob))
        ps.setBlob(i, (Blob)obj);
      else if ((obj instanceof Boolean))
        ps.setBoolean(i, ((Boolean)obj).booleanValue());
      else if ((obj instanceof Byte)) {
        ps.setByte(i, ((Byte)obj).byteValue());
      }
      else if ((obj instanceof BigDecimal)) {
        ps.setBigDecimal(i, (BigDecimal)obj);
      }
      else
        throw new SQLException("fillPreparedStatement: Object " + obj + " type is not supported. It's sequence number is " + i + " in parameters");
    }
  }

  public ResultIterator executeQuery(String sql, Object[] objectParams, int curPage, int pageSize)
    throws SQLException
  {
    ResultIterator ri = new ResultIterator();

    this.curPage = curPage;
    this.pageSize = pageSize;

    this.rowCount = 0;
    this.colCount = 0;

    ResultSet rs = null;
    Vector result = null;
    PreparedStatement ps = null;
    HashMap mapIndex = new HashMap();
    try
    {
      checkConnection();

      String countsql = SQLFilter.getCountSql(sql);
      ps = this.connection.prepareStatement(countsql);
      fillPreparedStatement(ps, objectParams);

      rs = this.connection.executePreQuery();
      if ((rs != null) && (rs.next())) {
        this.total = rs.getLong(1);
      }
      if (rs != null) {
        rs.close();
        rs = null;
      }
      if (ps != null) {
        ps.close();
        ps = null;
      }

      int totalpages = (int)Math.ceil(this.total / pageSize);
      if (curPage > totalpages)
        curPage = totalpages;
      if (curPage <= 0) {
        curPage = 1;
      }
      String listsql = sql;
      ps = this.connection.prepareStatement(sql);

      if (this.total != 0L) {
        this.connection.setMaxRows(curPage * pageSize);
      }
      fillPreparedStatement(ps, objectParams);
      rs = this.connection.executePreQuery();
      if (rs == null) {
        return ri;
      }

      ResultSetMetaData rm = rs.getMetaData();
      this.colCount = rm.getColumnCount();
      for (int i = 1; i <= this.colCount; i++)
      {
        mapIndex.put(rm.getColumnName(i).toUpperCase(), new Integer(i));
      }

      rs.setFetchSize(pageSize);

      int absoluteLocation = pageSize * (curPage - 1) + 1;

      if (!rs.absolute(absoluteLocation)) {
        return ri;
      }

      result = new Vector();

      ResultWrapper rsw = new ResultWrapper(rs);
      do {
        Vector row = new Vector();
        for (int i = 0; i < this.colCount; i++)
          row.addElement(rsw.getObject(i + 1));
        result.addElement(row);
        this.rowCount += 1;
      }while (rsw.next());
    }
    catch (SQLException e) {
      throw e;
    } finally {
      if (rs != null) {
        try {
          rs.close(); } catch (Exception e) {
        }
        rs = null;
      }
      if (ps != null) {
        try {
          ps.close();
        } catch (Exception e) {
        }
        ps = null;
      }
      if ((this.autoClose) && (this.connection.getAutoCommit())) {
        this.connection.close();
        this.connection = null;
      }
    }
    return new ResultIterator(result, mapIndex, this.total);
  }

  public ResultIterator executeQuery(String sql, Object[] objectParams, long total, int curPage, int pageSize)
    throws SQLException
  {
    ResultIterator ri = new ResultIterator();

    this.curPage = curPage;
    this.pageSize = pageSize;

    this.rowCount = 0;
    this.colCount = 0;

    ResultSet rs = null;
    Vector result = null;
    PreparedStatement ps = null;
    HashMap mapIndex = new HashMap();
    try
    {
      checkConnection();

      int totalpages = (int)Math.ceil(total / pageSize);
      if (curPage > totalpages)
        curPage = totalpages;
      if (curPage <= 0) {
        curPage = 1;
      }
      ps = this.connection.prepareStatement(sql);

      if (total != 0L) {
        this.connection.setMaxRows(curPage * pageSize);
      }
      fillPreparedStatement(ps, objectParams);
      rs = this.connection.executePreQuery();
      if (rs == null) {
        return ri;
      }

      ResultSetMetaData rm = rs.getMetaData();
      this.colCount = rm.getColumnCount();
      for (int i = 1; i <= this.colCount; i++)
      {
        mapIndex.put(rm.getColumnName(i).toUpperCase(), new Integer(i));
      }

      rs.setFetchSize(pageSize);

      int absoluteLocation = pageSize * (curPage - 1) + 1;

      if (!rs.absolute(absoluteLocation)) {
        return ri;
      }

      result = new Vector();

      ResultWrapper rsw = new ResultWrapper(rs);
      do {
        Vector row = new Vector();
        for (int i = 0; i < this.colCount; i++)
          row.addElement(rsw.getObject(i + 1));
        result.addElement(row);
        this.rowCount += 1;
      }while (rsw.next());
    }
    catch (SQLException e) {
      throw e;
    } finally {
      if (rs != null) {
        try {
          rs.close(); } catch (Exception e) {
        }
        rs = null;
      }
      if (ps != null) {
        try {
          ps.close();
        } catch (Exception e) {
        }
        ps = null;
      }
      if ((this.autoClose) && (this.connection.getAutoCommit())) {
        this.connection.close();
        this.connection = null;
      }
    }
    return new ResultIterator(result, mapIndex, total);
  }

  public ResultIterator executeQuery(String sql, Object[] objectParams)
    throws SQLException
  {
    ResultIterator ri = new ResultIterator();

    this.rowCount = 0;
    this.colCount = 0;

    ResultSet rs = null;
    Vector result = null;
    PreparedStatement ps = null;
    HashMap mapIndex = new HashMap();
    try
    {
      checkConnection();

      ps = this.connection.prepareStatement(sql);
      fillPreparedStatement(ps, objectParams);
      rs = this.connection.executePreQuery();
      if (rs == null) {
        return ri;
      }

      ResultSetMetaData rm = rs.getMetaData();
      this.colCount = rm.getColumnCount();
      for (int i = 1; i <= this.colCount; i++) {
        mapIndex.put(rm.getColumnName(i).toUpperCase(), new Integer(i));
      }

      result = new Vector();

      ResultWrapper rsw = new ResultWrapper(rs);
      while (rsw.next()) {
        Vector row = new Vector();
        for (int i = 0; i < this.colCount; i++) {
          try {
            row.addElement(rsw.getObject(i + 1));
          }
          catch (SQLException e) {
            row.addElement(null);
            LogUtil.getLog(getClass()).error(StrUtil.trace(e));
          }
        }
        result.addElement(row);
        this.rowCount += 1;
      }
    }
    catch (SQLException e) {
      throw e;
    } finally {
      if (rs != null) {
        rs.close();
        rs = null;
      }
      if (ps != null) {
        ps.close();
        ps = null;
      }
      if ((this.autoClose) && (this.connection.getAutoCommit())) {
        this.connection.close();
        this.connection = null;
      }
    }
    return new ResultIterator(result, mapIndex);
  }

  public ResultIterator executeQueryTFO(String sql, Object[] objectParams)
    throws SQLException
  {
    ResultIterator ri = new ResultIterator();

    this.rowCount = 0;
    this.colCount = 0;

    ResultSet rs = null;
    Vector result = null;
    PreparedStatement ps = null;
    HashMap mapIndex = new HashMap();
    try
    {
      checkConnection();

      ps = this.connection.prepareStatementTFO(sql);
      fillPreparedStatement(ps, objectParams);
      rs = this.connection.executePreQuery();
      if (rs == null) {
        return ri;
      }

      ResultSetMetaData rm = rs.getMetaData();
      this.colCount = rm.getColumnCount();
      for (int i = 1; i <= this.colCount; i++) {
        mapIndex.put(rm.getColumnName(i).toUpperCase(), new Integer(i));
      }

      result = new Vector();

      ResultWrapper rsw = new ResultWrapper(rs);
      while (rsw.next()) {
        Vector row = new Vector();
        for (int i = 0; i < this.colCount; i++) {
          try {
            row.addElement(rsw.getObject(i + 1));
          }
          catch (SQLException e) {
            row.addElement(null);
            LogUtil.getLog(getClass()).error(StrUtil.trace(e));
          }
        }
        result.addElement(row);
        this.rowCount += 1;
      }
    }
    catch (SQLException e) {
      throw e;
    } finally {
      if (rs != null) {
        rs.close();
        rs = null;
      }
      if (ps != null) {
        ps.close();
        ps = null;
      }
      if ((this.autoClose) && (this.connection.getAutoCommit())) {
        this.connection.close();
        this.connection = null;
      }
    }
    return new ResultIterator(result, mapIndex);
  }

  public ResultIterator executeQuery(String sql, int curPage, int pageSize)
    throws SQLException
  {
    ResultIterator ri = new ResultIterator();

    this.curPage = curPage;
    this.pageSize = pageSize;

    this.rowCount = 0;
    this.colCount = 0;

    ResultSet rs = null;
    Vector result = null;
    HashMap mapIndex = new HashMap();
    try
    {
      checkConnection();

      String countsql = SQLFilter.getCountSql(sql);

      rs = this.connection.executeQuery(countsql);
      if ((rs != null) && (rs.next())) {
        this.total = rs.getLong(1);
      }
      if (rs != null) {
        rs.close();
        rs = null;
      }

      int totalpages = (int)Math.ceil(this.total / pageSize)+1;
      if (curPage > totalpages)
        curPage = totalpages;
      if (curPage <= 0) {
        curPage = 1;
      }
      if (this.total != 0L) {
        this.connection.setMaxRows(curPage * pageSize);
      }
      rs = this.connection.executeQuery(sql);
      if (rs == null) {
        return ri;
      }

      ResultSetMetaData rm = rs.getMetaData();
      this.colCount = rm.getColumnCount();
      for (int i = 1; i <= this.colCount; i++)
      {
        mapIndex.put(rm.getColumnName(i).toUpperCase(), new Integer(i));
      }

      rs.setFetchSize(pageSize);

      int absoluteLocation = pageSize * (curPage - 1) + 1;

      if (!rs.absolute(absoluteLocation)) {
        return ri;
      }

      result = new Vector();

      ResultWrapper rsw = new ResultWrapper(rs);
      do {
        Vector row = new Vector();
        for (int i = 1; i <= this.colCount; i++)
          row.addElement(rsw.getObject(i));
        result.addElement(row);
        this.rowCount += 1;
      }while (rsw.next());
    }
    finally {
      if (rs != null) {
        try {
          rs.close(); } catch (Exception e) {
        }
        rs = null;
      }
      if ((this.autoClose) && (this.connection.getAutoCommit())) {
        this.connection.close();
        this.connection = null;
      }
    }
    return new ResultIterator(result, mapIndex, this.total);
  }

  public int executeUpdate(String sql)
    throws SQLException
  {
    int r = 0;
    try
    {
      checkConnection();
      r = this.connection.executeUpdate(sql);
    } finally {
      if ((this.autoClose) && (this.connection.getAutoCommit())) {
        this.connection.close();
        this.connection = null;
      }
    }
    return r;
  }

  public int executeUpdate(String sql, Object[] objectParams)
    throws SQLException
  {
    int r = 0;
    PreparedStatement ps = null;
    try
    {
      checkConnection();
      ps = this.connection.prepareStatement(sql);
      fillPreparedStatement(ps, objectParams);
      r = this.connection.executePreUpdate();
    }
    finally {
      if (ps != null) {
        try {
          ps.close();
        } catch (Exception e) {
        }
        ps = null;
      }
      if ((this.autoClose) && (this.connection.getAutoCommit())) {
        this.connection.close();
        this.connection = null;
      }
    }
    return r;
  }

  public void beginTrans() throws SQLException {
    this.connection.beginTrans();
  }

  public void commit() throws SQLException
  {
    if (this.connection != null)
      this.connection.commit();
  }

  public void rollback() {
    this.connection.rollback();
  }

  public void close()
  {
    if (this.connection != null)
      this.connection.close();
  }

  public boolean isClosed() {
    if (this.connection == null) {
      return true;
    }
    return this.connection.isClosed();
  }

  public void checkConnection()
    throws SQLException
  {
    if (isClosed())
      this.connection = new Connection(this.connName);
  }

  public void setAutoClose(boolean autoClose)
  {
    this.autoClose = autoClose;
  }
}

解决方案四：

JSP访问数据库分页显示