java通用分页条件查询_通用分页查询

packagecom.dao;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;importcom.bean.Author;importcom.db.DB;importcom.mysql.jdbc.Connection;importcom.mysql.jdbc.PreparedStatement;public classAuthorDao {//检验用户名或密码是否正确

publicAuthor CheckLogin(String username,String password) {

Author author=null;

DB db=newDB();//获取数据库连接对象

Connection con=(Connection) db.getCon();//定义sql语句

String sql="select * from author where username= ? and password= ?";try{

PreparedStatement ps=(PreparedStatement) con.prepareStatement(sql);//设置用户名和密码和密码作为参数放到sql

ps.setString(1,username);

ps.setString(2, password);

ResultSet rs=ps.executeQuery();//用户名和密码正确，得到用户数据，进行打包

if(rs.next()) {

author=newAuthor();

author.setId(rs.getInt(1));

author.setUsername(rs.getString(2));

author.setPassword(rs.getString(3));

author.setEmail(rs.getString(4));

author.setAddress(rs.getString(5));

author.setPhone(rs.getString(6));

}

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}returnauthor;

}//查询所有用户信息

public Listselectallauthor(){

List authorlist=new ArrayList();

DB db=newDB();//获取数据库连接对象

Connection con=(Connection) db.getCon();

String sql="select * from author";try{

PreparedStatement ps=(PreparedStatement) con.prepareStatement(sql);

ResultSet rs=ps.executeQuery();//循环遍历获取用户信息

while(rs.next()) {

Author author=newAuthor();

author.setId(rs.getInt(1));

author.setUsername(rs.getString(2));

author.setPassword(rs.getString(3));

author.setEmail(rs.getString(4));

author.setAddress(rs.getString(5));

author.setPhone(rs.getString(6));

authorlist.add(author);//System.out.println("存放成功");

}

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}returnauthorlist;

}//查询用户表总记录数(分页查询)

public intselectallauctorcount() {

DB db=newDB();

Connection con=(Connection) db.getCon();

String sql="select count(*) from author";try{

PreparedStatement ps=(PreparedStatement) con.prepareStatement(sql);

ResultSet rs=ps.executeQuery();if(rs.next()) {return rs.getInt(1);

}

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}return 0;

}/** 分页查询用户数据类型

* padeIndex数据起始索引

* padeSize每页显示的数据条数*/

public List sellectallauthorPage(int pageIndex,intpageSize){

List list=new ArrayList();

Author author=newAuthor();

DB db=newDB();

Connection con=(Connection) db.getCon();

String sql="select * from author limit ?,?";try{

PreparedStatement ps=(PreparedStatement) con.prepareStatement(sql);

ps.setObject(1,pageIndex);

ps.setObject(2, pageSize);

ResultSet rs=ps.executeQuery();//获取结果集//遍历结果集获取用户的列表信息

while(rs.next()) {

author=newAuthor();

author.setId(rs.getInt(1));

author.setUsername(rs.getString(2));

author.setPassword(rs.getString(3));

author.setEmail(rs.getString(4));

author.setAddress(rs.getString(5));

author.setPhone(rs.getString(6));

list.add(author);

}

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}returnlist;

}//新增用户信息

public voidadd(Author author) {

DB db=newDB();

Connection con=(Connection) db.getCon();

String sql="insert into author values(id,?,?,?,?,?)";try{

PreparedStatement ps=(PreparedStatement) con.prepareStatement(sql);

ps.setObject(1,author.getUsername());

ps.setObject(2,author.getPassword());

ps.setObject(3,author.getEmail());

ps.setObject(4,author.getAddress());

ps.setObject(5, author.getPhone());

ps.execute();

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}//删除用户消息

public void delete(intid) {

DB db=newDB();

Connection con=(Connection) db.getCon();

String sql="delete from author where id=?";try{

PreparedStatement ps=(PreparedStatement)con.prepareStatement(sql);

ps.setObject(1,id);

ps.execute();

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}

}