Velocityweb 1.3 was released, adding SQL paging support !! Multiple databases are supported!

Velocityweb 1.3 was released, adding SQL paging support !! Multiple databases are supported!

Project Network Station http://velocityweb.sourceforge.net

 

Here is the introduction:

Paging of SQL query results

When an SQL query results in too much data, it is necessary to paging the results data so that users can only see a small amount of data at a time. If too much data is displayed at a time, the human eye cannot see it.

However, this is not taken into consideration in the ansi SQL standard. In some popular data, MySQL/PostgreSQL has customized the extension to solve this problem. This may be one of the reasons why MySQL is popular in Web applications. Because Web applications need to care about the data size of a single page.

SQL standard 2003 introduces the SQL Window Function row_number () over (). The database SQL Server 2005/DB2/Oracle supports it. Oracle is slightly different from the standard. MySQL/PostgreSQL does not support row_number ().

This is enough. Based on this, velocityweb provides a unified interface to write paging programs suitable for various databases and supports special operations such as select distinct/group. Paging programs on the network do not support these special SQL statements.

The paging operation generally involves two parts: the current page data and the total data volume. In addition, it should be noted that retrieving the current page data involves sorting data, that is, order by in SQL. Sorting is required for paging to make sense.

This involves several classes and implements the pagedialect interface: standardpagedialect (SQL Server 2005, DB2 supported), sqlserver2000pagedialect, oraclepagedialect, mysqlpagedialect, and postgresqlpagedialect.

The following is an example:

SQL = new stringbuffer ();
SQL. append ("select I. Itemid, QTY ");
SQL. append ("from inventory V, item I ");
SQL. append ("where I. Itemid = V. Itemid ");
SQL. append ("and I. Itemid>? ");
SQL. append ("and V. Itemid>? ");

Orderby = "I. Itemid, V. qty DESC ";

Paralist = new partition list ();
Paralist. Add ("AA ");
Paralist. Add ("BB ");

Countsql = dialect.Getcountsql(SQL. tostring ());
Count = (number) This. queryscalar (countsql, paralist. toarray (), 1 );

Pagesql = dialect.Getpagesqlwithoutpageinfo(SQL. tostring (), orderby );
Values = dialect.Getpageparemetervalueswithpageinfo(Pageindex, pagesize );
Paralist. Add (New Long (Values [0]);
Paralist. Add (New Long (Values [1]);

List = This. querybeanlist (pagesql, paralist. toarray (), inventory. Class );

 

From the current use effect, it is still good.

Based on this, velocityweb provides further encapsulation. If you use JDBC as Dao, you can write as follows:

List resultdatalist = new partition list ();
Int recordcount = jdbcpageutils.Getpagedata(SQL. tostring (), paralist, orderby,
Pageindex, pagesize, Con, dialect, resultdatalist );

In the current page, each element in resultdatalist is object [].

 

If you use jdbcdao of velocityweb, you can write as follows:

 

List resultdatalist = new partition list ();
Int recordcount = velocitywebpageutils.Getpagedata(SQL. tostring (), paralist, orderby,
Pageindex, pagesize, Dao, dialect, inventory. Class, resultdatalist );

Each element in the current page data resultdatalist is an inventory object.