bookdao.java

通过jsp,servlet,javaBean,三成架构实现的好友通讯录系统

package com.Dao;

import java.sql.*;
import com.db.DBManager;
import java.util.ArrayList;
import com.entity.BookBean;
public class bookDao
{
    public Connection conn;
    public PreparedStatement ps;
    public ResultSet rs;

    public bookDao()
    {
        conn=DBManager.getConnetion();
    }
   // //查询当前用户的通讯录
    public ArrayList findBookByUserId(int userId)
    {
        ArrayList list=new ArrayList() ;
        String sql="select *from BOOK where userId=?";
        try
        {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,userId);
            rs=ps.executeQuery();
            while(rs.next())
            {
                BookBean book=new BookBean();
                book.setId(rs.getInt(1));
                book.setName(rs.getString(2));
                book.setSex(rs.getString(3));
                book.setPhone(rs.getString(4));
                book.setAddress(rs.getString(5));
                book.setMobilePhone(rs.getString(6));
                book.setCompany(rs.getString(7));
                book.setComPhone(rs.getString(8));
                book.setComAddress(rs.getString(9));
                book.setRelation(rs.getInt(10));
                book.setUserId(rs.getInt(11));
                list.add(book);
            }

        }catch(Exception e)
        {
            e.printStackTrace();
        }
        return list;
    }
  //增加联系人
    public int addBook(BookBean book)
    {
        int result=0;
        String sql="insert into BOOK values(?,?,?,?,?,?,?,?,?,?)";
        try
        {
            ps=conn.prepareStatement(sql);
            ps.setString(1,book.getName());
            ps.setString(2,book.getSex());
            ps.setString(3,book.getPhone());
            ps.setString(4,book.getAddress());
            ps.setString(5,book.getMobilePhone());
            ps.setString(6,book.getCompany());
            ps.setString(7,book.getComPhone());
            ps.setString(8,book.getComAddress());
            ps.setInt(9,book.getRelation());
            ps.setInt(10,book.getUserId());
            result=ps.executeUpdate();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return result;
    }

  //删除联系人
    public int delBookById(int bookId)
    {
        int result=0;
        String sql="delete from BOOK where id=?";
        try
        {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,bookId);
            result=ps.executeUpdate();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return result;
    }
    //查询当前用户的通讯录
        public ArrayList findBookRelationById(int i,int j)
        {
                ArrayList list=new ArrayList();
                String sql="select * from Book where userId=? and relation=?";
                try {
                        ps=conn.prepareStatement(sql);
                        ps.setInt(1,i);
                        ps.setInt(2,j);
                        rs=ps.executeQuery();
                        while(rs.next())
                        {
                                BookBean book=new BookBean();
                                book.setId(rs.getInt(1));
                                book.setName(rs.getString(2));
                                book.setSex(rs.getString(3));
                                book.setPhone(rs.getString(4));
                                book.setAddress(rs.getString(5));
                                book.setMobilePhone(rs.getString(6));
                                book.setCompany(rs.getString(7));
                                book.setComPhone(rs.getString(8));
                                book.setComAddress(rs.getString(9));
                                book.setRelation(rs.getInt(10));
                                book.setUserId(rs.getInt(11));
                                list.add(book);
                        }
                } catch (SQLException e) {
                        e.printStackTrace();
                }
                return list;
        }
        //更新联系人
        public int updateBook(BookBean book,int bookId)
        {
                int count=0;
                String sql="update BOOK set [name]=?,sex=?,phone=?,address=?,mobilePhone=?,company=?,comPhone=?,comAddress=?,relation=? where id=?";
                try {
                        ps=conn.prepareStatement(sql);
                        ps.setString(1,book.getName());
                        ps.setString(2,book.getSex());
                        ps.setString(3,book.getPhone());
                        ps.setString(4,book.getAddress());
                        ps.setString(5,book.getMobilePhone());
                        ps.setString(6,book.getCompany());
                        ps.setString(7,book.getComPhone());
                        ps.setString(8,book.getComAddress());
                        ps.setInt(9,book.getRelation());
                        ps.setInt(10,bookId);
                        count=ps.executeUpdate();
                } catch (SQLException e) {
                        e.printStackTrace();
                }
                return count;
	}
        public BookBean findBookid(int userId){
            BookBean book=new BookBean();
            String sql="select * from Book where id=?";
            try{
                ps=conn.prepareStatement(sql);
                ps.setInt(1,userId);
                rs=ps.executeQuery();
                while(rs.next())
                {
                 book.setId(rs.getInt("id"));
                 book.setName(rs.getString("name"));
                 book.setSex(rs.getString("sex"));
                 book.setPhone(rs.getString("phone"));
                 book.setAddress(rs.getString("address"));
                 book.setMobilePhone(rs.getString("mobilephone"));
                 book.setComPhone(rs.getString("comPhone"));
                 book.setComAddress(rs.getString("comAddress"));
                 book.setRelation(rs.getInt("relation"));
                 book.setUserId(rs.getInt("userId"));
                 book.setCompany(rs.getString("company"));
                }
            }catch(Exception e){
                System.out.println("Book表查询失败");
                e.printStackTrace();
            }
            return book;
    }
}