Programming JDBC result set paging display

sponsored links
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.
  • del.icio.us
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of Programming JDBC result set paging display

  • JDBC driver types

    1, JDBC is a included in the J2SE and J2EE platform API, you have access to a variety of data sources, in particular, are so typical as Oracle relational database management system. Sun Microsystems Inc. in January 1997 the introduction of JDBC techn ...

  • JDBC example of a long time do not have JDBC forgot

    A back-up here to stay. The first: The second:

  • Spring + Hibernate to use Glassfish Database Connection Pool

    applicationContext.xml file content <? xml version = "1.0" encoding = "UTF-8"?> <beans xmlns = " http://www.springframework.org/schema/beans " xmlns: xsi = " http://www.w3.org/2001/XMLSchema-instance " ...

  • In the servlet use Bean

    According to Sun's definition, JavaBean is a reusable software components. In fact JavaBean is a Java class, through the package into a property and methods of treatment of a function or a business object, referred to as bean. Because JavaBean is ...

  • Hibernate.cfg.xml configuration file (including the primary key generation strategy Introduction)

    Hibernate.cfg.xml configuration file: <? xml version = "1.0" encoding = "utf-8"?> <! DOCTYPE hibernate-configuration PUBLIC "- / / Hibernate / Hibernate Configuration DTD / / EN" "hibernate-configuration-2.0.dtd

  • The EJB3 Persistence

    EJB3 persistence with Hibernate is very similar to the mechanism: Environment: Server: JBOSS5.0 Database: MySQL5.0 1. Set up a data source First of all, in jboss-5.0.0.GA \ server \ default \ deploy, the establishment of a database used to connect the dat

  • hibernate to use the principle of

    The use of hibernate, implementation of data persistence. Has the following several processes. One configuration database connection information. Hibernate.config 2 configuration mapping. 3 use: the use of the process are the following steps: 3.1: Ge ...

  • Servlet brief introduction

    Servlet brief introduction: Servlet is a small application server Are used to complete the B / S architecture, the client requests the response to treatment Platform independence, performance, able to run thread Servlet API for Servlet provides the s ...

  • spring struts2.0 hibernate environmental structures .. despair carried out more than one hour only with good.

    http://www.qqread.com/java/2008/06/f413762.html Look here. . Note added myeclipse support spring when necessary add the commons-dbcp database connection pool package. And to add hibernate support. . Finally add struts2 support. . Oh the lazy point. . . fu

blog comments powered by Disqus
Recent
Recent Entries
Tag Cloud
Random Entries