recorddao.java

老师给的一个简易的通讯录 （jsp+mysql）。

package addressbook.db;

import java.sql.*;
import java.util.ArrayList;
import addressbook.bean.*;

/**
 * @version 2007-3-14
 * @author Xiaofei 
 */
public class RecordDAO {	
	Connection con;//数据库连接
	String apend = "insert into Record(name,sex,address,phone,email) values(?,?,?,?,?)";
	String delete = "delete from Record where id=?";
	String getRecordById = "select * from Record where id=?";
	String update = "update Record set name=?,sex=?,address=?,phone=?,email=? where id=?";
	
	public RecordDAO(){}
	
	public void query(PageBean pageBean,String conditions){	
		String q1 = "select count(*) as total from Record "+conditions;
		String q2 = "select * from Record "+conditions+" order by name limit ?,?";
		try{
			con = DatabaseConnection.getConnection();
			PreparedStatement ps1 = con.prepareStatement(q1);
			ResultSet rs1 = ps1.executeQuery();
			int total = 0;
			if(rs1.next()){
				total = rs1.getInt("total");
			}
			pageBean.setMaxRowCount(total);
			pageBean.setMaxPage();
			
			int num = pageBean.getRowsPerPage();
			int start = (pageBean.getCurPage()-1)*num;
			PreparedStatement ps2 = con.prepareStatement(q2);
			ps2.setInt(1,start);
			ps2.setInt(2,num);
			ResultSet rs2 = ps2.executeQuery();
			ArrayList records = new ArrayList();		
			Record r;
			while(rs2.next()){
				r = new Record();
				r.setId(rs2.getInt("id"));
				r.setName(rs2.getString("name"));
				r.setSex(rs2.getInt("sex"));
				r.setAddress(rs2.getString("address"));
				r.setPhone(rs2.getString("phone"));
				r.setEmail(rs2.getString("email"));
				
				records.add(r);
			}
			pageBean.setData(records);
		}catch(Exception e){
			System.err.print(e);
		}finally{
			try{
				con.close();
			}catch(Exception e2){}
		}
	}
	
	public void append(Record record) {
		try{
			con = DatabaseConnection.getConnection();
			PreparedStatement ps = con.prepareStatement(apend);
			ps.setString(1,record.getName());
			ps.setInt(2,record.getSex());
			ps.setString(3,record.getAddress());
			ps.setString(4,record.getPhone());
			ps.setString(5,record.getEmail());
			ps.executeUpdate();
		}catch(Exception e){
			System.err.print(e);
		}finally{
			try{
				con.close();
			}catch(Exception e2){}
		}
	}
	
	public void delete(int id) {
		try{
			con = DatabaseConnection.getConnection();
			PreparedStatement ps = con.prepareStatement(delete);
			ps.setInt(1,id);
			ps.executeUpdate();
		}catch(Exception e){
			System.err.print(e);
		}finally{
			try{
				con.close();
			}catch(Exception e2){}
		}
	}
	
	public Record getRecordById(int id) {
		Record r = new Record();
		try{
			con = DatabaseConnection.getConnection();
			PreparedStatement ps = con.prepareStatement(getRecordById);
			ps.setInt(1,id);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				r.setId(id);
				r.setName(rs.getString("name"));
				r.setSex(rs.getInt("sex"));
				r.setAddress(rs.getString("address"));
				r.setPhone(rs.getString("phone"));
				r.setEmail(rs.getString("email"));
			}
		}catch(Exception e){
			System.err.print(e);
		}finally{
			try{
				con.close();
			}catch(Exception e2){}
		}
		return r;
	}
	
	public void update(Record record) {
		try{
			con = DatabaseConnection.getConnection();
			PreparedStatement ps = con.prepareStatement(update);
			ps.setString(1,record.getName());
			ps.setInt(2,record.getSex());
			ps.setString(3,record.getAddress());
			ps.setString(4,record.getPhone());
			ps.setString(5,record.getEmail());
			ps.setInt(6,record.getId());
			ps.executeUpdate();
		}catch(Exception e){
			System.err.print(e);
		}finally{
			try{
				con.close();
			}catch(Exception e2){}
		}
	}
	
}