基于jsp的bbs论坛-(4)java中对dao的实现

DAO层一般有接口和该接口的实现类！ 接口用于规范实现类！ 实现类一般用于用于操作数据库！ 一般操作修改，添加，删除数据库操作的步骤很相似，就写了一个公共类DAO类 ，修改，添加，删除数据库操作时 直接调用公共类DAO类！

一、对com.itqinxun.dao包的实现

该包下有3个Java文件，分别定义了对上述包中所定义3个对象类与数据库的相关操作，也即定义了相关的功能，但只是定义，并未进行具体实现

1.1、在包下创建AccountDao.java文件

内容为：

package com.itqinxun.dao;
import com.itqinxun.model.Account;
import java.util.List;
public interface AccountDao {
    public   boolean   addAccount(Account account);//新增账号信息
    public   boolean   deleteAccount(Account account);//删除账号信息,将用户权限设为2，即不可登陆
    public   boolean   updateAccount(Account account);//更新账号信息
    public   List<Account>   searchAccountAll();//显示所有账号信息,只查询普通童虎
    public   List<Account>   searchAdminAll();//只查询管理员
    public   Account   returnAccountByaccount_account(Account account);  //根据用户数字账号返回用户
    public   Account   loginAccount(Account account); //查询登录信息以及返回用户
    public   Account   adminloginAccount(Account account);//查询管理用户信息以及返回用户
    public   boolean   setAccountAdmin(Account account);//将用户权限变为1，即使管理员
}

1.2、在包下创建MessageDao.java文件

内容为：

package com.itqinxun.dao;
import com.itqinxun.model.Message;
import java.util.List;
public interface MessageDao {
    public boolean addMessage(Message message);    //新增主题帖
    public boolean deleteMessage(Message message); //删除主题帖,根据主题帖的ID进行删除，即message_id
    public boolean deleteMessageByaccount_account(int account_account); //删除主题帖,根据用户数字账号删除该用户所有的主题帖
    public List<Message> searchMessageALL();     //显示所有主题帖信息，每查询日期，只查询了message表
    public List<Message> searchMessageALL2();   //按列查询，查询了日期，以及查询了account表的发帖人名字
    public List<Message> searchMessageIpost(int account_account);    //根据用户Id显示该用户所有的发布主题帖
    public List<Message> searchMessage(Message message);     //根据用户数字账号ID
    public List<Message> searchMessageById(Message message);     //根据用户帖子ID查找帖子
    public Message returnMessageBymessage_id(Message message);//显示所有主题帖信息给用户，包括发帖人名字，回帖数
}

1.3、在包下创建RevertDao.java文件

内容为：

package com.itqinxun.dao;
import com.itqinxun.model.Revert;
import java.util.List;
public interface RevertDao {
    public   boolean   addRevert(Revert revert);//新增回帖
    public   boolean   deleteRevert(Revert revert); //根据帖子id删除回帖
    public   boolean   deleteRevertByaccount_account(int account_account);      //根据用户数字账号，删除该用户回帖
    public   boolean   deleteRevertbyMessageid(int messageid);                  //根据主题帖id删除所有回帖
    public     List<Revert>     searchRevertALL();//显示所有回帖
    public     List<Revert>     searchNewRevertALL();//显示最新回帖
    public     List<Revert>     searchRevertById(int account_account);          //根据用户id查询该用户的所有回复
    public   List<Revert> returnRevertBymessage_id( int messageid);             //显示所有主题帖信息给用户，包括发帖人名字，回帖数
}

至此定义了对3个Dao对象的相关需求功能，只是进行相关定义，未进行具体实现

二、对com.itqinxun.dao.impl包的实现

• 在该包中分别完成上述定义的3个Dao对象，进行具体相关代码的实现
• 以在包下创建AccountDaoImpl.java为例：

在包下创建AccountDaoImpl.java文件，内容为：

package com.itqinxun.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.itqinxun.dao.AccountDao;
import com.itqinxun.model.Account;
import com.itqinxun.util.DBUtil;
public class AccountDaoImpl implements AccountDao{
    Connection conn;
    Statement stmt;//评测字符串执行SQL
    PreparedStatement pstmt;//?编译对象占位符 可防止sql注入
    ResultSet rs;
    /**
     *此方法是用户注册的相关方法 
     *@sql中的accountnum为数据库中设置的队列，调用accountnum.nextval会形成递增的数字,且为该账号表的主键！
     */
    public boolean addAccount(Account account) {
        // TODO Auto-generated method stub
        boolean flag=false;
        int i=0;
        // 1.定义sql
        String sql="INSERT into account_table(account_account,account_name,account_pass)VALUES(accountnum.nextval,?,?)";
        // 2.获得连接
        conn=DBUtil.getConnection();    
        try {
            // 3.编译sql
            pstmt = conn.prepareStatement(sql);
            // 4.执行sql
            pstmt.setString(1, account.getAccount_name());//1,2分别对应SQL中的问号顺序
            pstmt.setString(2, account.getAccount_pass());
            //5.返回数据操作结行数1
            i = pstmt.executeUpdate();//对结果进行判断，返回操作成功的行数，该例成功为1，失败为0
            if(i>0)
                flag= true;
        } catch (SQLException e) {
            e.printStackTrace(); 
        }
        //6.返回方法结果
        //System.out.println("addAccount..........go");
        return flag;
    }
    /**
     * 这是删除用户的方法
     * 根据用户数字账号进行删除，即acount_acount（用户数字账号识别列）的值
     */
    public boolean deleteAccount(Account account) {
        boolean flag=false;
        int i = 0;
        String sql = "UPDATE account_table set account_limit = 2  WHERE account_account=?"; 
        conn = DBUtil.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, account.getAccount_account());
            i = pstmt.executeUpdate();
            if(i>0)
                flag= true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //System.out.println("updateAccount..........go");
        return flag;
    }
    /**
     * 保存用户修改的方法
     */
    public boolean updateAccount(Account account) {
        // TODO Auto-generated method stub
        boolean flag=false;
        int i = 0;
        String sql = "UPDATE account_table set account_name =?,account_pass=? WHERE account_account=?"; 
        conn = DBUtil.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,account.getAccount_name());
            pstmt.setString(2,account.getAccount_pass());
            pstmt.setInt(3, account.getAccount_account());
            i = pstmt.executeUpdate();
            if(i>0)
                flag= true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //System.out.println("updateAccount..........go");
        return flag;
    }
    /**
     * 查询所有用户的方法，只查询普通用户，不查询管理员
     */
    public List<Account> searchAccountAll() {
        // TODO Auto-generated method stub
        List<Account> list = new ArrayList<Account>();
        Account account = null;
        String sql = null;

        sql = "SELECT * FROM account_table WHERE account_limit=0"; //只查询普通用户
        // get connection
        conn = DBUtil.getConnection();
        try {
            // 编译sql
            pstmt = conn.prepareStatement(sql);
            // 执行sql
            rs = pstmt.executeQuery();//executeQuery查询/executeUpdate增加、修改、删除
            while (rs.next()) {
                account = new Account();
                account.setAccount_account(rs.getInt("account_account"));
                account.setAccount_name(rs.getString("account_name"));
                account.setAccount_pass(rs.getString("account_pass"));
                account.setAccount_limit(rs.getInt("account_limit"));   //account_limit     用户账号权限识别码，0为用户，1为管理员                 数字类型
                account.setAccount_date(rs.getDate("account_date"));   // account_date     用户账号创建日期      
                list.add(account);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }   
        System.out.println("searchAccountAll..........go");
        return list;
    }
    /**
     * 根据用户的账号查找其他的信息，即申明一个对象，仅赋予该对象数字账号，调用该方法会把名称，密码再赋值到该对象中，更加安全
     */
    public Account returnAccountByaccount_account(Account account) {
        String sql = "SELECT * from account_table WHERE account_account=?";
        conn = DBUtil.getConnection();
        try { 
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, account.getAccount_account());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                account=new Account();
                account.setAccount_account(rs.getInt("account_account"));
                account.setAccount_name(rs.getString("account_name"));
                account.setAccount_pass(rs.getString("account_pass"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } 
        return account;
    }

    /**
     * 普通用户登录方法，查询Limit<2
     */
    public Account loginAccount(Account account) {
        // TODO Auto-generated method stub
        Account newaccount=null;
        String sql = "SELECT account_account,account_name,account_pass,account_date FROM account_table WHERE account_name=? and account_pass=? and account_limit<2";
        conn = DBUtil.getConnection();
        try {
            // 编译sql
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,account.getAccount_name());
            pstmt.setString(2,account.getAccount_pass() );
            // 执行sql
            rs = pstmt.executeQuery();//executeQuery查询/executeUpdate增加、修改、删除
            if(rs.next()) {
                newaccount = new Account();
                newaccount.setAccount_account(rs.getInt("account_account"));
                newaccount.setAccount_name(rs.getString("account_name"));
                newaccount.setAccount_pass(rs.getString("account_pass"));
                newaccount.setAccount_date(rs.getDate("account_date")); 
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }   
        System.out.println("loginAccount..........go");
        return newaccount;
    }
    /**
     * 普通管理员登录方法，查询Limit=1才能登录
     */
    public Account adminloginAccount(Account account) {
        // TODO Auto-generated method stub
        Account newaccount=null;
        String sql = "SELECT account_account,account_name,account_pass FROM account_table WHERE account_name=? and account_pass=? and account_limit=1";
        conn = DBUtil.getConnection();
        try {
            // 编译sql
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,account.getAccount_name());
            pstmt.setString(2,account.getAccount_pass() );
            // 执行sql
            rs = pstmt.executeQuery();//executeQuery查询/executeUpdate增加、修改、删除
            if(rs.next()) {
                newaccount = new Account();
                newaccount.setAccount_account(rs.getInt("account_account"));
                newaccount.setAccount_name(rs.getString("account_name"));
                newaccount.setAccount_pass(rs.getString("account_pass"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }   
        System.out.println("loginAccount..........go");
        return newaccount;
    }
    /**
     * 普通所有管理员
     */
    public List<Account> searchAdminAll() {
        // TODO Auto-generated method stub
        List<Account> list = new ArrayList<Account>();
        Account account = null;
        String sql = null;

        sql = "SELECT * FROM account_table WHERE account_limit=1"; //只查询普通用户
        // get connection
        conn = DBUtil.getConnection();
        try {
            // 编译sql
            pstmt = conn.prepareStatement(sql);
            // 执行sql
            rs = pstmt.executeQuery();//executeQuery查询/executeUpdate增加、修改、删除
            while (rs.next()) {
                account = new Account();
                account.setAccount_account(rs.getInt("account_account"));
                account.setAccount_name(rs.getString("account_name"));
                account.setAccount_pass(rs.getString("account_pass"));
                account.setAccount_limit(rs.getInt("account_limit"));   //account_limit     用户账号权限识别码，0为用户，1为管理员                 数字类型
                account.setAccount_date(rs.getDate("account_date"));   // account_date     用户账号创建日期      
                list.add(account);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }   
        System.out.println("searchAccountAll..........go");
        return list;
    }
    /**
     * 设置管理员
     */
    public boolean setAccountAdmin(Account account) {
        // TODO Auto-generated method stub
        boolean flag=false;
        int i = 0;
        String sql = "UPDATE account_table set account_limit = 1  WHERE account_account=?"; 
        conn = DBUtil.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, account.getAccount_account());
            i = pstmt.executeUpdate();
            if(i>0)
                flag= true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //System.out.println("updateAccount..........go");
        return flag;
    }
}

三、总结

• 至此实现了对Dao的定义及具体实现，可通过相关调用完成相应的功能，完成对数据的相关操作访问
• 在此定义好了方法，其余编程只需进行相关调用即可
• 由于分为定义与实现，也更加方便进行相关功能的修改、增加等