jdbc+ pagination-"DRP"

Jdbc

Package Com.bjpowernode.drp.util;import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import java.sql.sqlexception;import JAVA.SQL.RESULTSET;//JDBC Tool class public class DbUtil {/* * Use a singleton mode to get Connection * @return * */public static Connection getconnection () {/*connection conn=null;try{class.forname (" Oracle.jdbc.driver.OracleDriver "); String url= "Jdbc:oracle:thin: @localhost: 1521:ORCL"; String username= "WM"; String password= "a123456a"; conn=drivermanager.getconnection (Url,username,password);} catch (ClassNotFoundException e) {e.printstacktrace ();} catch (SQLException e) {e.printstacktrace ();}  return conn;*/connection Conn=null; The data connection is empty Try{jdbcconfig jdbcconfig=xmlconfigreader.getinstance (). Getjdbcconfig ();//Get the database configuration file Class.forName ( Jdbcconfig.getdrivername ());//LOAD Database driver//use JDBC to connect to database address, username, password conn=drivermanager.getconnection ( Jdbcconfig.geturl (), Jdbcconfig.getusername (), Jdbcconfig.getpassword ());} catch (ClassNotFoundException e) {e.printstacktrace ();} catch (SQLException e) {E.printstacktrace ();} Return conn;} Close database connection public static void Close (Connection conn) {if (conn!=null) {try {conn.close ()} catch (SQLException e) {//TODO Au To-generated catch Blocke.printstacktrace ();}}} Close the database assembly public static void Close (ResultSet rs) {if (rs!=null) {try {rs.close ()} catch (SQLException e) {//TODO Auto-gene Rated catch Blocke.printstacktrace ();}}} Close SQL compilation set public static void Close (PreparedStatement pstmt) {if (pstmt!=null) {try {pstmt.close ();} catch (SQLException E ) {e.printstacktrace ();}}}  Test class public static void Main (string[] args) {System.out.println (dbutil.getconnection ());}}

Paging:

/** * Paged Query * @param pageno page * @param pageSize how many data per page * @return Pagemodel */public pagemodel finduserlist (int pageno, I NT PageSize) {StringBuffer sbsql = new StringBuffer ();//define a StringBuffer, using the Append method, append the content to the end of the current StringBuffer// Stitched SQL statements Sbsql.append ("Select user_id, user_name, password, contact_tel, email, create_date"). Append ("from"). Append ("  ("). Append (" Select RowNum rn, USER_ID, user_name, password, contact_tel, email, create_date "). Append (" from "). Append (" (  "). Append (" Select user_id, user_name, password, contact_tel, email, create_date from T_user where user_id <> ' root ' ORDER by user_id "). Append (") where RowNum <=? "). Append (") where rn >?");         Connection conn = null; Defines a static database connection PreparedStatement pstmt = null;    Create precompiled SQL statement ResultSet rs = null;//define result set Pagemodel Pagemodel = null;//Define page Information entity try {conn = dbutil.getconnection (); Get Connection pstmt = Conn.preparestatement (sbsql.tostring ());//Execute SQL statement pstmt.setint (1, PageNo * pageSize);//The first one? Represents all the number of bars that the last page can contain pstmT.setint (2, (pageNo-1) * pageSize);//The second one? represents all the number of bars contained in the penultimate page//executequery returns the//execute, returns a Boolean value, whether the table name executes the SQL statement returns the result Executeupdate, the return value is an integer that indicates the number of rows affected by rs = Pstmt.executequery ();//execute query List userlist = new ArrayList ();//Instantiate an array while ( Rs.next ()) {//Iterate through the query result set User user = New user () User.setuserid (rs.getstring ("user_id"));//user ID is assigned user.setusername ( Rs.getstring ("user_name"));//Assign the user name User.setpassword (rs.getstring ("password"));//Assign a value to the user's password User.setcontacttel (Rs.getstring ("Contact_tel"));//Assign a value User.setemail (rs.getstring ("email") to the user's phone, or//assign a value to the email user.setcreatedate ( Rs.gettimestamp ("Create_date"));//Assign a value to the creation date userlist.add (user);//Add an object to the collection list}pagemodel = new Pagemodel (); Instantiate paged entity pagemodel.setlist (userlist); Assign a value pagemodel.settotalrecords (Gettotalrecords (conn)) to the result set;//Record number pagemodel.setpagesize (pageSize);// Number of data bars Pagemodel.setpageno (pageno);//How many pages}catch (SQLException e) {e.printstacktrace ();} Finally {Dbutil.close (RS);//Close Recordset dbutil.close (PSTMT);//Close SQL DataSet Dbutil.close (conn);//Close SQL connection}return pagemodel;// Returns the paged entity}/** * Total Record count * @param conn * @return *//** * Total Records obtained * @param conn * @return */private int gettotalrecords (Connection conn) T Hrows SQLException {//Query the number of bars for all users id<>root String sql = "SELECT COUNT (*) from T_user where user_id <> ' root '; PreparedStatement pstmt = null;//define SQL precompiled set ResultSet rs = null;//define Recordset int count = 0;//define the number of try {pstmt = Conn.preparestatemen t (SQL);//Execute SQL Statement rs = pstmt.executequery ();//Execute Query Rs.next ();//traverse Recordset count = Rs.getint (1);//Get the value of the first column}finally { Dbutil.close (RS);//Turn off DataSet Dbutil.close (PSTMT);//Close SQL Recordset}return count;}

jdbc+ pagination-"DRP"