JAVA MYSQL做分頁

閑來看看 2012-04-01

展開全文

JAVA MYSQL做分頁

package com.hcwy.test.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import com.hcwy.basic.jdbc.DBConnection;
import com.hcwy.basic.page.PageBean;
public class ArticlesDAO {
private static final Map HashMap = null;
private PreparedStatement pstmt;
private ResultSet rs;
private Connection con;
// private DBConnection conn;
public Connection conn(){
try {
Class.forName("com.mysql.jdbc.Driver");
try {
con=DriverManager.getConnection("jdbc:mysql://localhost:3316/hcwy","root","root");
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return con;
}
//查詢SQL
public ArrayList chaSQL(String sql){
ArrayList list=new ArrayList();
try {
pstmt=this.conn().prepareStatement(sql);
rs=pstmt.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();
int count=rsmd.getColumnCount();
while(rs.next()){
// System.out.println("名字是-->"+rsmd.getColumnName(i)+"/t 得到的object是-->"+rs.getObject(i)+" "+i);
HashMap map=new HashMap();
for(int i=0;i<count;i++){
map.put(rsmd.getColumnName(i+1), rs.getObject(i+1));
}
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//查詢所總條數(shù)
public int count(String name){
String sql="select count(*) as aa from "+name;
int i=0;
try {
pstmt=this.conn().prepareStatement(sql);
rs=pstmt.executeQuery();
if(rs.next()){
i=rs.getInt("aa");
}
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
//查詢SQL帶分頁
public ArrayList chaSQL(String sql,String name,PageBean page){
ArrayList list=new ArrayList();
if(page!=null){
page.setTotalCount(this.count(name));
sql=sql+" limit "+page.getStart()+","+page.getPageSize();
}
System.out.println(sql);
try {
pstmt=this.conn().prepareStatement(sql);
rs=pstmt.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();
int count=rsmd.getColumnCount();//得到表里字段的總數(shù)
while(rs.next()){
// System.out.println("名字是-->"+rsmd.getColumnName(i)+"/t 得到的object是-->"+rs.getObject(i)+" "+i);
HashMap map=new HashMap();
for(int i=0;i<count;i++){
map.put(rsmd.getColumnName(i+1), rs.getObject(i+1));//名字和值
}
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public static void main(String[] args) {
PageBean page=new PageBean();
ArticlesDAO dd=new ArticlesDAO();
ArrayList list=dd.chaSQL("select * from articles","articles",page);//如果這里不寫page和articles的意思就是說不要分頁
//任何對象都能解析
for(int i=0;i<list.size();i++){
HashMap map=(HashMap)list.get(i);
Iterator it=map.keySet().iterator();
while(it.hasNext()){
Object id=it.next();
System.out.println(""+map.get(id));
}
System.out.println("/n");
}
// ArticlesDAO dd=new ArticlesDAO();
// System.out.println(dd.count("articles"));
}
}

[java] view plain copy ?

package com.hcwy.test.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import com.hcwy.basic.jdbc.DBConnection;
import com.hcwy.basic.page.PageBean;
public class ArticlesDAO {
private static final Map HashMap = null;
private PreparedStatement pstmt;
private ResultSet rs;
private Connection con;
// private DBConnection conn;
public Connection conn(){
try {
Class.forName("com.mysql.jdbc.Driver");
try {
con=DriverManager.getConnection("jdbc:mysql://localhost:3316/hcwy","root","root");
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return con;
}
//查詢SQL
public ArrayList chaSQL(String sql){
ArrayList list=new ArrayList();
try {
pstmt=this.conn().prepareStatement(sql);
rs=pstmt.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();
int count=rsmd.getColumnCount();
while(rs.next()){
// System.out.println("名字是-->"+rsmd.getColumnName(i)+"/t 得到的object是-->"+rs.getObject(i)+" "+i);
HashMap map=new HashMap();
for(int i=0;i<count;i++){
map.put(rsmd.getColumnName(i+1), rs.getObject(i+1));
}
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//查詢所總條數(shù)
public int count(String name){
String sql="select count(*) as aa from "+name;
int i=0;
try {
pstmt=this.conn().prepareStatement(sql);
rs=pstmt.executeQuery();
if(rs.next()){
i=rs.getInt("aa");
}
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
//查詢SQL帶分頁
public ArrayList chaSQL(String sql,String name,PageBean page){
ArrayList list=new ArrayList();
if(page!=null){
page.setTotalCount(this.count(name));
sql=sql+" limit "+page.getStart()+","+page.getPageSize();
}
System.out.println(sql);
try {
pstmt=this.conn().prepareStatement(sql);
rs=pstmt.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();
int count=rsmd.getColumnCount();//得到表里字段的總數(shù)
while(rs.next()){
// System.out.println("名字是-->"+rsmd.getColumnName(i)+"/t 得到的object是-->"+rs.getObject(i)+" "+i);
HashMap map=new HashMap();
for(int i=0;i<count;i++){
map.put(rsmd.getColumnName(i+1), rs.getObject(i+1));//名字和值
}
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public static void main(String[] args) {
PageBean page=new PageBean();
ArticlesDAO dd=new ArticlesDAO();
ArrayList list=dd.chaSQL("select * from articles","articles",page);//如果這里不寫page和articles的意思就是說不要分頁
//任何對象都能解析
for(int i=0;i<list.size();i++){
HashMap map=(HashMap)list.get(i);
Iterator it=map.keySet().iterator();
while(it.hasNext()){
Object id=it.next();
System.out.println(""+map.get(id));
}
System.out.println("/n");
}
// ArticlesDAO dd=new ArticlesDAO();
// System.out.println(dd.count("articles"));
}
}