JDBC+Oracle分页查询
JDBC+Oracle分页查询
- import java.io.IOException;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.ResourceBundle;
- import javax.servlet.ServletConfig;
- import javax.servlet.ServletException;
- import javax.servlet.http.HttpServlet;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import javax.servlet.http.HttpSession;
- public class Session extends HttpServlet {
- private Connection connection;
- private PreparedStatement statement;
- private ResultSet set;
- public void service(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- HttpSession session = request.getSession();
- session.setAttribute("", session.getId());
- session.setMaxInactiveInterval(60 * 30);
- System.out.println(session.getAttribute(""));
- String sql="select num from (select rownum num from emp where rownum<?) where num>=?";
- try {
- ResultSet set=set(1, 7, sql);
- while (set.next()) {
- System.out.println(set.getInt(1));
- }
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- session.invalidate();
- }
- public void init() {
- String driver = getString("db", "driver");
- String url = getString("db", "url");
- String user = getString("db", "user");
- String password = getString("db", "password");
- try {
- Class.forName(driver);
- } catch (ClassNotFoundException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- try {
- connection = DriverManager.getConnection(url, user, password);
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- public void destroy() {
- try {
- if (set != null) {
- set.close();
- }
- if (statement != null) {
- statement.close();
- }
- if (connection != null) {
- connection.close();
- }
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- private ResultSet set(int page, int rowsPerPage, String sql)
- throws SQLException {
- int start = (page - 1) * rowsPerPage + 1;
- int end = start + rowsPerPage;
- statement = connection.prepareStatement(sql);
- statement.setInt(1, end);
- statement.setInt(2, start);
- set = statement.executeQuery();
- return set;
- }
- private String getString(String baseName, String key) {
- return ResourceBundle.getBundle(baseName).getString(key);
- }
- }
- <servlet>
- <servlet-name></servlet-name>
- <servlet-class>Session</servlet-class>
- <load-on-startup>0</load-on-startup>
- </servlet>
- <servlet-mapping>
- <servlet-name></servlet-name>
- <url-pattern>/</url-pattern>
- </servlet-mapping>
- driver=Oracle.jdbc.driver.OracleDriver
- url=jdbc:oracle:thin:@localhost:1521:wish
- user=scott
- password=tiger
评论暂时关闭