Programming JDBC result set paging display

Abstract: At present, the database applications are very common in the application development process, often related to access to the database. Java using JDBC technology, access to the database. The operation of the database query is the more frequently a kind of operation, the returned results can sometimes be a lot of records in the result set, users browse and processing inconvenient, often used in paging processing.
Keywords: JDBC database; driver; result set CLC number: TP311 Document: A Article ID :1009-3044 (2007) 06-11511-01

1 JDBC technology, JDBC (Java Database Connectivity) is the Sun provides a set of database programming interface API functions, from the Java language classes and interfaces. JDBC for database developers with a standard API, using the Java programming language and JDBC conjunction with the preparation of applications, without regard to the preparation of different platforms for different applications. Truly "Write Once, Run Everywhere!".

2 JDBC programming database access using JDBC technology, when, Java application through JDBC API and the JDBC driver manager to communicate between, JDBC driver manager and the final in two ways to communicate with the database: First, the use of JDBC - ODBC bridge driver, an indirect way, and the other is to use the JDBC driver direct way.
The following JDBC driver to direct way to illustrate how Java language and SQL Server2000 connected to the whole process of JDBC programming:
(1) load the driver Class.forName ( "com.microsoft.jdbc.sqlserver.SQLServerDriver");
(2) through DriverManager to get a database connection handle private static String url = "jdbc: microsoft: sqlserver: / / localhost: 1433; databasename = xxxx";
Connection con = DriverManager.getConnection (url, user, password);
(3) by connecting the handle to bind to execute the statement Statement stmt = con.createStatement ();
(4) to receive results of the implementation ResultSet rs = stmt.executeQuery (sql);
Or ResultSet rs = stmt.executeUpdate (sql);
(5) deal with the results called get XXX () method to obtain the result value.
(6) Close the database connection rs.close ();
stmt.close ();
con.close ();

3 JDBC programming page is displayed in the result set ResultSet class provides a set of data, paging processing.
3.1 Technical points (1) createStatement () method:
public Statement createStatement ()
Get the default connect statement, there is no pointer to operate public Statement createStatement (int resultSetType, int resultSetConcurrency)
resultSetType decision result set type of a rolling basis, and its value as: ResultSet.TYPE_FORWARD_ONLY, Result.TYPE_SCROLL_INSENSITIVE and ResultSet.TYPE_SCROLL_SENSITIVE
resultSetConcurrency deciding whether to update the database with the result set. Its value is: Result.CONCUR_READ_ONLY, Result.CONCUR_UPDATETABLE
(2) ResultSetMetaData result set meta-object classes result set meta-data by using the getMetaData () to get the result set meta-object. ResultSetMetaData result set meta-object class methods:
getColumnCount ()
getColumnName (int index)
getColumnTypeName (int index)
3.2 The establishment of a database in SQL Server 2000 creates a directory named book of the database, book database, established under an information table book bookInfo, the table structure: bookInfo (no, bookname, author, price, public)
3.3 Java source program in order to make a certain versatility and flexibility, will be displayed in number and page size (the number of records per page) from the command line input. The command-line arguments arg [0] on behalf of pageno, arguments arg [1] on behalf of pagesize. JDBCScrollDisplay.java source code is as follows:
import java.sql .*;
public class JDBCScrollDisplay (
private static String url = "jdbc: microsoft: sqlserver: / / localhost: 1433; databasename = book";
private static String username = "sa";
private static String password = "sa";
public Connection conn () (
try (
Class.forName ( "com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection con = DriverManager.getConnection (url, username, password);
return con;
)
catch (SQLException e1) (
System.out.println ( "can't connection db:" + e1);
return null;
)
catch (Exception e2) (
System.out.println ( "Failed to load JDBC driver.");
return null;
)
)
public void query (Connection con, String sql, int pageNo, int pageSize) (
try (
if (con == null) (
throw new Exception ( "database connection can't use!");
)
if (sql == null) (
throw new Exception ( "check your parameter: 'sql'! don't input null!");
)
int rowCount; / / Record the total number of int pageCount; / / total number of pages Statement stmt = con.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery (sql); / / execute the query ResultSetMetaData rmeta = rs.getMetaData ();// get the number of data fields int numColumns = rmeta.getColumnCount ();
rs.last ();
rowCount = rs.getRow ();
pageCount = (rowCount + pageSize-1) / pageSize; / / calculating the total number of pages if (pageNo> pageCount) pageNo = pageCount; / / adjust the page to be displayed if (pageCount> 0) (
rs.absolute ((pageNo-1) * pageSize +1); / / pointer to locate the record to be displayed on the pages of the first record int i = 0;
while (i) (
for (int j = 0; j <numColumns; j + +)
(
String sTemp = rs.getString (j +1);
System.out.print (sTemp + "");
)
i + +;
rs.next ();
System.out.println ("");
)
)
)
catch (Exception e) (
System.out.println ( "query error:" + e);
)
)
public void demo (int pageNo, int pageSize) (
try (
JDBCScrollDisplay jd = new JDBCScrollDisplay ();
Connection con = jd.conn ();
String sql = "select * from bookInfo order by no";
jd.query (con, sql, pageNo, pageSize);
con.close ();)
catch (SQLException se) (System.out.println (se);)
catch (Exception e) (System.out.println (e);))
public static void main (String [] arg) (
JDBCScrollDisplay jsd = new JDBCScrollDisplay ();
int pageno = Integer.parseInt (arg [0]);
int pagesize = Integer.parseInt (arg [1]);
jsd.demo (pageno, pagesize);))

4 Conclusion JDBC is Java, a standard SQL database access interface, which for database application developers, database front tool developers to provide a standard application programming interfaces. Developers simply use JDBC provides classes, call these class methods can access the database, without concern to what is used in the specific database system. In the actual use often when we try to provide scrollable result set, just before the use of paging data declaration is a movable SQL statements. The cases of any application can do any operation on the database, based on the re-paging display.
References:
[1] DU, Guan Payson. Java Application Programming 100 cases of [M]. China Railway Publishing House ,2004,4.171-189.
[2] Yu Dong Jun, Yang Jingyu, Li Qian-mu, the Kingdom-wide. Java program design and application development [M]. Tsinghua University Press ,2005.3.216-231.
In this article are involved in graphics, comments, formulas etc. Please refer to the PDF format to read the original.

分类:Java 时间:2010-03-29 人气:360
分享到:
blog comments powered by Disqus

相关文章

  • Common database connection statement 2010-09-19

    1: Mysql database connection statement Class.forName ("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection. ("Jdbc: mysql: / / localhost: 3306/bookinfo", "root", "123"); Statement st = con.createSt

  • Database connection statement 2010-10-04

    A: Mysql database connection statement Class.forName ("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection ("jdbc: mysql: / / localhost / bookinfo", "root", "123"); Statement st = con.createStateme

  • JDBC database connection pool Statment 2010-09-08

    1. Foreword   database applications, in many software systems are often used, is indispensable for the development of supporting large-scale systems. But if not well managed database resources (such as: failure to make timely recovery of the databa

  • A simple implementation of JDBC database connection pool 2011-01-06

    Of :snapbug From :CSDN   directory :   1. Introduction   2. Connection pool technology background   2.1 JDBC   2.2 JDBC Connection Pool   2.3 connection pool (ConnectionPool) And resource management   3. Simple JDBC Implementation of co

  • JDBC database connection pool implementation and principles 2010-11-07

    1 Introduction Database applications, many software systems are often used, is the development of large systems indispensable aid. But if not well managed database resources (such as: failure to make timely recovery of the database cursor (ResultSet)

  • The basic principles of database connection pool 2010-06-11

    Traditional database connection (refer to the basic realization through DriverManager and DataSource to connect), a database connection objects are corresponding to a physical database connection, database connection and the closure of the establishm

  • There is a relatively easy way to track which does not release database connection 2009-06-03

    There is a relatively easy way to track which database connection is not released, is to do a proxy class, the proxy class that they have a time-out mechanisms, timed out do not print the call stack is released package com.poweroa.jdbc; import java.s

  • JDCB basic formulation and implementation of database connection pool 2010-04-12

    1, using the static method to achieve package util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DbConnStatic { private final static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver"; p

  • java.sql.SQLException: [Microsoft] [SQLServer 2000 Driver for JDBC] Error establis 2010-05-02

    jdbc connection sql server2000 exception: java.sql.SQLException: [Microsoft] [SQLServer 2000 Driver for JDBC] Error establishing socket. Reason: As the development environment is Win 2003 Server / xp, it needs SQL 2000 patch, or under the 2k3, SQL de

  • java.sql.SQLException: [Microsoft] [SQLServer 2000 Driver for JDBC] Can't start a 2011-05-09

    Error conditions: java.sql.SQLException: [Microsoft] [SQLServer 2000 Driver for JDBC] Can't start a cloned connection while in manual transaction mode. Error reason: (1) When you are in a SQL SERVER the JDBC connection to perform the operation multip

iOS 开发

Android 开发

Python 开发

JAVA 开发

开发语言

PHP 开发

Ruby 开发

搜索

前端开发

数据库

开发工具

开放平台

Javascript 开发

.NET 开发

云计算

服务器

Copyright (C) codeweblog.com, All Rights Reserved.

CodeWeblog.com 版权所有 黔ICP备15002463号-1

processed in 1.092 (s). 12 q(s)