数据库分页是web开发中必不可少的一项,下面我介绍一下我的分页方法
为了尽可能能实现软件的层次化,我单独写了一个分页Bean
package org.cdy.www.db;
public class DBPaging {
private int totalPage;
public static int currentPage = 1;
private int pageSize;
private DBManager instance;
public DBPaging() {
instance = DBManager.getInstance("org.cdy.www.db.DBManager");
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalPage() {
return totalPage;
}
public void gotoPage(int page) {
switch (page) {
case 0:// first page
currentPage = 1;
break;
case -1:// previous page
if (currentPage > 1) {
--currentPage;
} else
currentPage = 1;
break;
case 2:// next page
if (currentPage < totalPage)
++currentPage;
else
currentPage = totalPage;
break;
case -4:// last page
currentPage = totalPage;
break;
}
DBPaging.setCurrentPage(currentPage);
}
public void setTotalPage(String userName) {
this.totalPage = instance.getTotalPage(userName, pageSize);
}
public void setTotalPage() {
this.totalPage = instance.getTotalPage(pageSize);
}
public static int getCurrentPage() {
return currentPage;
}
public static void setCurrentPage(int currentPage) {
DBPaging.currentPage = currentPage;
}
}
//下面就是这个数据库管理员DBManager的定义
package org.cdy.www.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import javax.ejb.Local;
import org.cdy.www.bean.Note;
import org.cdy.www.bean.SystemUser;
public class DBManager {
private static DBManager instance = null;// 单例模式,学过设计模式的应该知道是怎么会事
public static synchronized DBManager getInstance(String className) {
if (instance != null) {
return instance;
}
try {
instance = (DBManager) Class.forName(className).newInstance();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return instance;
}
protected DBManager() {
}
public int getTotalPage(int pageSize) {
int totalPage = 1;
int totalRow = 0;
String sql = null;
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
conn = DBConnection.getConnection();
sql = "select * from note";
try {
pst = conn.prepareStatement(sql);
// System.out.println("fdffffffffffffff");
rs = pst.executeQuery();
// System.out.println("fdffffffffffffff");
while (rs.next()) {
++totalRow;
}
if (totalRow % pageSize == 0) {
totalPage = totalRow / pageSize;
} else {
totalPage = totalRow / pageSize + 1;
}
System.out.println("totalRow=" + totalRow);
rs.close();
pst.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (conn != null)
DBConnection.close(conn);
}
return totalPage;
}
public List<Note> pageShowAllNote(int page, int pageSize) {
List<Note> listNote = new ArrayList();
String sql = null;
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
conn = DBConnection.getConnection();
// mssql = "select * from (select top "
// + pageSize
// + " * from ( select top "
// + pageSize
// * page
// + " * from note order by nid asc ) as tempA order by nid desc) as
// tempB order by nid asc";
sql = "select * from note limit " + pageSize + " offset " + pageSize
* (page - 1);
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
while (rs.next()) {
Note note = new Note();
note.setId(rs.getInt("nid"));
note.setUserName(rs.getString("username"));
note.setContent(rs.getString("issuecontent"));
System.out.println("issuetime from db="
+ rs.getString("issuetime"));
java.util.Date issueDate = new java.util.Date(rs.getTimestamp(
"issuetime").getTime());
note.setIssueTime(issueDate);
java.util.Date updateDate = new java.util.Date(rs.getTimestamp(
"updatetime").getTime());
note.setUpdateTime(updateDate);
listNote.add(note);
}
rs.close();
pst.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (conn != null)
DBConnection.close(conn);
}
return listNote;
}
}
//在这其中用到了DBConnection类,这个类的定义如下:
package org.cdy.www.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnection {
public synchronized static Connection getConnection() {
Connection conn = null;
try {
// System.out.println(DBConfig.driver);
Class.forName(DBConfig.driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(DBConfig.url, DBConfig.user,
DBConfig.password);
} catch (SQLException e) {
System.out.print("/n/n********************Exeception***********************/n"
+ "*");
System.out.print("Make sure your DB server is running.If not,start it"
+ "*/n");
System.out.println("*****************************************************");
//e.printStackTrace();
}
return conn;
}
public synchronized static void close(Connection conn) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//在这个类中又有一个类叫DBConfig,安是用来读取配置文件了
它的定义如下:
//下面是配置文件DBConfig.properties
package org.cdy.www.db;
import java.io.IOException;
import java.util.Properties;
public class DBConfig {
private static Properties prop = new Properties();
static {
try {
prop.load(DBConfig.class.getResourceAsStream(
"DBConfig.properties"));
} catch (IOException e) {
e.printStackTrace();
}
}
public static String driver = prop.getProperty("driver");
public static String url = prop.getProperty("url");
public static String user = prop.getProperty("user");
public static String password = prop.getProperty("password");
}
#******************************************************************
#MSSQL #driver=com.microsoft.jdbc.sqlserver.SQLServerDriver #url=jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=mydb #user=sa #password=sa
#******************************************************************
#Mysql #driver=com.mysql.jdbc.Driver #url=jdbc:mysql://127.0.0.1:3306/myDB #user=root #password=123456
#******************************************************************
#Oracle #driver=oracle.jdbc.driver.OracleDriver #url=jdbc:oracle:thin:@127.0.0.1:1521:oracledb #user=scott #password=tiger
#******************************************************************
#Postgresql driver=org.postgresql.Driver url=jdbc:postgresql://127.0.0.1:5432/notebooks user=postgres password=123456
#******************************************************************
从这个配置文件,大家可以看出,不管你用什么数据库都是可以的,只要能给出合适的驱动,我试过上面这几种数据库,都能连接上……
以上是我个人自己写的数据库分页类,不恰当之处请多多指教……