Use JDBC to connect to the MySQL database-Typical Case Analysis (8)-implement paging query of employee data, jdbcmysql

Use JDBC to connect to the MySQL database-Typical Case Analysis (8)-implement paging query of employee data, jdbcmysql

Reprinted Please note: http://blog.csdn.net/uniquewonderq

Problem:

Use JDBC to connect to the Mysql database and implement the paging query function for Emp table data.

Solution:

For large data volumes, the query by page is usually used. Different database products have different database-level paging query policies. For example, Oracle usually uses rownum, while Mysql uses limit.

Oracle uses rownum and subquery to implement paging query. The SQL statement is as follows,

Select * from (select rownum rn, empno, ename, job, mgr, hiredate, sal, comm, deptno from (select * fron emp order by empno) where rn between 6 and 10

The preceding SQL statement is used to obtain the staff information in ascending order of employee numbers, and obtain the information of five employees ranging from 6th to 10.

The SQL statement of the MySQL database that implements the above functions is as follows:

Select * from emp order by empno limit 5;

MYSQL uses the limit keyword for paging query. The first parameter after limit is the row number (starting from 0) for getting data, and the second parameter is the row number for getting records. The second parameter can be omitted, indicating that all subsequent records are obtained starting from the first parameter.

Procedure:

To implement this case, follow these steps.

Step: add the findByPageMySQL method to connect to the Mysql database and query data in the Emp table by page. The Code is as follows:

Package dao; import java. SQL. connection; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; import java. SQL. preparedStatement; import com.sun.org. apache. regexp. internal. recompile; import Entity. emp; public class EmpDAO {public static void main (String [] args) {EmpDAO dao = new EmpDAO (); // 1. select all // dao. findAll (); // 2. insert // Emp emp = new Emp (1001, "rose", "Analyst", 7901, "2014-0 5-01 ", 3000.00, 500.00, 10); // System. out. println ("emp. getEmpNo () "+ emp. getEmpNo (); // dao. add (emp); // 3. update // emp. setSal (4500.00); // dao. update (emp); // 4. findByPageMysqldao. findByPageMySQL (2, 3); // view the second page, 3 entries per page} public void findByPageMySQL (int page, int pageSize) {Connection con = null; PreparedStatement stmt = null; resultSet rs = null; int total =-1; // total number of records int pages =-1; // total number of pages String SQL _total = "select count (*) from emp "; String SQL = "select * from emp order by empno limit ?,? "; Try {con = ConnectionSource. getConnection (); stmt = con. prepareStatement (SQL _total); // The total number of records rs1_stmt.exe cuteQuery (); if (rs. next () {total = rs. getInt (1);} System. out. println ("total number of records:" + total); // calculate the total number of pages int mod = total % pageSize; if (mod = 0) {pages = total/pageSize ;} else pages = total/pageSize + 1; // if the number of pages to be viewed is greater than or less than 1, take the last or first page if (page> pages) {page = pages;} else if (page <1) {page = 1 ;}system. out. println ("SQL statement:" + SQL); int star T = (page-1) * pageSize; stmt = con. prepareStatement (SQL); stmt. setInt (1, start); stmt. setInt (2, pagesize1_rs1_stmt.exe cuteQuery (); while (rs. next () {System. out. println (rs. getInt ("empno") + "," + rs. getString ("ename") + "," + rs. getDouble ("sal") + "," + rs. getDate ("hiredate");} catch (SQLException e) {System. out. println ("database access exception! "); Throw new RuntimeException (e);} finally {try {if (stmt! = Null) {stmt. close () ;}if (con! = Null) {con. close () ;}} catch (SQLException e) {System. out. println ("an exception occurred when releasing the resource! ") ;}} Public void findAll () {Connection con = null; Statement stmt = null; ResultSet rs = null; try using cuteQuery (" select empno, ename, sal, hiredate from emp; "); while (rs. next () {System. out. println (rs. getInt ("empno") + "," + rs. getString ("ename") + "," + rs. getDouble ("sal") + "," + rs. getDate ("hiredate");} catch (SQLException e) {System. out. println (" An error occurred while accessing the database! "); Throw new RuntimeException (e);} finally {try {if (rs! = Null) {rs. close () ;}if (stmt! = Null) {stmt. close () ;}if (con! = Null) {con. close () ;}} catch (SQLException e) {System. out. println ("an exception occurred when releasing the resource! ") ;}} Public void add (Emp emp) {Connection con = null; Statement stmt = null; int flag =-1; String SQL =" insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values ("+ emp. getEmpNo () + "," + "'" + emp. getEname () + "'," + "'" + emp. getJob () + "'," + emp. getMgr () + "," + "str_to_date ('" + emp. getHiredate () + "',' % Y-% m-% d % H: % I: % s')," + emp. getSal () + "," + emp. getComm () + "," + emp. getDeptno () + ")"; try {con = ConnectionSource. getConnectio N (); stmt = con. createStatement (); flag implements stmt.exe cuteUpdate (SQL); // Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, // such as an SQL DDL statement. // either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 // for SQL statements that return nothing // this flag returns two situations: 1. returns the number of rows that have been executed // if it is a DDL statement, nothing is returned. // DDL statement: Data Definition Language // For example: create database, create table, alter table, drop table, create view, alter view, drop view, if (flag> 0) {System. out. println ("New Record added successfully! ") ;}} Catch (SQLException e) {System. out. println (" database access exception! "); Throw new RuntimeException (e);} finally {try {if (stmt! = Null) {stmt. close () ;}if (con! = Null) {con. close () ;}} catch (SQLException e2) {System. out. println ("an exception occurred when releasing resources! ") ;}} Public void update (Emp emp) {Connection con = null; Statement stmt = null; int flag =-1; string SQL = "update emp set sal =" + emp. getSal () + "," + "comm =" + emp. getComm () + "where empno =" + emp. getEmpNo (); try again con=onsource.getconnection({stmt=con.createstatement({{flag}stmt.exe cuteUpdate (SQL); if (flag> 0) {System. out. println ("Update record successful! ") ;}} Catch (SQLException e) {System. out. println (" database access exception! "); Throw new RuntimeException (e);} finally {try {if (stmt! = Null) {stmt. close () ;}if (con! = Null) {con. close () ;}} catch (SQLException e2) {System. out. println ("an exception occurred when releasing resources! ");}}}}

Run the above Code:

The table shows that the third is 7499.

Running result:

The total number of records is 11, which is the same as expected. The output result is also consistent.

This section ends ....