Connect to MySQL database using JDBC-typical case studies (eight)----paging query for employee data

Reprint Please specify : Http://blog.csdn.net/uniquewonderq

Questions :

Use JDBC to connect to MySQL database, to realize the paging query function of EMP table data.

Solution :

For large amounts of data, paging is usually used. Different database products have different database level paging query strategies. For example, Oracle typically uses rownum, while MySQL uses limit.

Oracle uses ROWNUM and subqueries to implement paged queries, with SQL statements 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 function of the above SQL statement is to get 5 employee information between the 6th to 10th digits sorted by employee number in ascending employee information.

The SQL statement for the MySQL database that implements the above features is as follows:

SELECT * from emp ORDER by Empno limit 5, 5;

MySQL uses the Limit keyword for paging queries. Where the first parameter after limit is the line number starting to get the data (starting at 0), and the second parameter is the number of rows to get the record. The second argument can be omitted, which means that all subsequent records are obtained from the first argument.

Steps :

The implementation of this case needs to follow the steps below.

Step: Add method Findbypagemysql method, implement the connection MySQL database, implement the data in the EMP table paging query, 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.finda ll ();//2.insert//emp emp=new Emp (1001, "Rose", "Analyst", 7901, "2014-05-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 second page, per page 3}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 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);//Obtain the total number of records rs=stmt.executequery (); if (Rs.next ()) {Total=rs.getint (1);} SYSTEM.OUT.PRINTLN ("Total 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 view is greater than the maximum page, or less than 1, take the last page or the first page if (page>pages) {page=pages;} else if (page<1) {page=1;} SYSTEM.OUT.PRINTLN ("SQL statement is:" +sql), int start= (page-1) *pagesize;stmt=con.preparestatement (SQL); Stmt.setint (1, Start); Stmt.setint (2, pageSize); Rs=stmt.executequery (); 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 while releasing the resource! ");}}} public void FindAll () {Connection con=null; Statement Stmt=null; ResultSet rs=null;try {con=connectionsource.getconnection (); Stmt=con.createstatement (); Rs=stmt.executequery (" 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 ("Database access Exception! "); 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 while 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.getconnection (); stmt=  Con.createstatement (); flag =stmt.executeupdate (SQL);//executes the given SQL statement, which May is an INSERT, UPDATE, or DELETE statement or an SQL statement that returns Nothing,//such as a SQL DDL Statement.//either (1) The row count for S QL Data Manipulation LaNguage (DML) statements or (2) 0//for SQL statements that return nothing//this flag returns in two cases: 1. Returns the number of rows executed//If it is a DDL statement then nothing is returned. DDL statements: Data Definition language//such as: CREATE database,create table,alter TABLE, drop table,create view,alter VIEW, Drop VI EW etc if (flag>0) {System.out.println ("New record succeeded! ");}} 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 ("releasing the resource has an exception! ");}}} 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 {con= Connectionsource.getconnection (); Stmt=con.createstatement (); flag=stmt.executeupdate (SQL); if (flag>0) { SYSTEM.OUT.PRINTLN ("The update record was 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 ("the release of the resource has an exception! ");}}}}

Execute the above code:

As can be seen from the table: the third article is 7499

Operation Result:

The total number of records is 11 yes, same as expected. Then the output is consistent.

This section concludes ....

Connect to MySQL database using JDBC-typical case studies (eight)----paging query for employee data