managerdao.java~103~

图书馆管理系统 第1章 系统简介 图书馆是各大高等院校的重要组成部门

package com.dao;

import com.core.ConnDB;
import java.util.*;
import com.actionForm.ManagerForm;
import java.sql.*;
import java.*;

public class ManagerDAO {
    private ConnDB conn = new ConnDB();
    //查询数据
    public Collection query(String queryif) {
        ManagerForm managerForm = null;
        Collection managercoll = new ArrayList();
        String sql = "";
        if (queryif == null || queryif == "" || queryif == "all") {
            //此处需要应用左连接
            sql = "select m.*,p.sysset,p.readerset,p.bookset,p.borrowback,p.sysquery from tb_manager m left join tb_purview p on m.id=p.id";
        }else{
            sql="select m.*,p.sysset,p.readerset,p.bookset,p.borrowback,p.sysquery from tb_manager m left join tb_purview p on m.id=p.id where m.name='"+queryif+"'";
        }
        ResultSet rs = conn.executeQuery(sql);
        int a = 0;
        try {
            while (rs.next()) {
                managerForm = new ManagerForm();
                managerForm.setId(Integer.valueOf(rs.getString(1)));
                managerForm.setName(rs.getString(2));
                managerForm.setPwd(rs.getString(3));
                managerForm.setSysset(rs.getInt(4));
                managerForm.setReaderset(rs.getInt(5));
                managerForm.setBookset(rs.getInt(6));
                managerForm.setBorrowback(rs.getInt(7));
                managerForm.setSysquery(rs.getInt(8));
                managercoll.add(managerForm);
                a++;
            }
            System.out.println("记录总数：" + a);
        } catch (SQLException e) {}
        return managercoll;
    }

    //管理员身份验证
    public int checkManager(ManagerForm managerForm) {
        int flag = 0;
        String sql = "SELECT * FROM tb_manager where name='" +
                     managerForm.getName() + "'";
        ResultSet rs = conn.executeQuery(sql);
        try {
            if (rs.next()) {
                String pwd = managerForm.getPwd();
                if (pwd.equals(rs.getString(3))) {
                    flag = 1;
                } else {
                    flag = 0;
                }
            }
            rs.last();
            int rowSum = rs.getRow();
            rs.first();
            if (rowSum != 1) {
                flag = 0;
                System.out.print("获取的row的值：" + sql + rowSum);
            }
        } catch (SQLException ex) {
            flag = 0;
        }
        return flag;
    }

    //修改时应用的查询方法
    public ManagerForm query_update(ManagerForm managerForm) {
        ManagerForm managerForm1 = null;
        String sql = "select m.*,p.sysset,p.readerset,p.bookset,p.borrowback,p.sysquery from tb_manager m left join tb_purview p on m.id=p.id where m.id=" +
                     managerForm.getId() + "";

        ResultSet rs = conn.executeQuery(sql);
        try {
            while (rs.next()) {
                managerForm1 = new ManagerForm();
                managerForm1.setId(Integer.valueOf(rs.getString(1)));
                managerForm1.setName(rs.getString(2));
                managerForm1.setPwd(rs.getString(3));
                managerForm1.setSysset(rs.getInt(4));
                managerForm1.setReaderset(rs.getInt(5));
                managerForm1.setBookset(rs.getInt(6));
                managerForm1.setBorrowback(rs.getInt(7));
                managerForm1.setSysquery(rs.getInt(8));
            }
        } catch (SQLException ex) {}
        return managerForm1;
    }
    //更改口令时应用的查询方法
    public ManagerForm query_pwd(ManagerForm managerForm) {
        ManagerForm managerForm1 = null;
        String sql = "SELECT * FROM tb_manager WHERE name='" +managerForm.getName() + "'";
        ResultSet rs = conn.executeQuery(sql);
        try {
            while (rs.next()) {
                managerForm1 = new ManagerForm();
                managerForm1.setId(Integer.valueOf(rs.getString(1)));
                managerForm1.setName(rs.getString(2));
                managerForm1.setPwd(rs.getString(3));
            }
        } catch (SQLException ex) {}
        return managerForm1;
    }
    //添加数据
    public int insert(ManagerForm managerForm) {
        String sql1="SELECT * FROM tb_manager WHERE name='"+managerForm.getName()+"'";
        ResultSet rs = conn.executeQuery(sql1);
        String sql = "";
        int falg = 0;
            try {
                if (rs.next()) {
                    falg=2;
                } else {
                    sql = "INSERT INTO tb_manager (name,pwd) values('" +
                                 managerForm.getName() + "','" +
                                 managerForm.getPwd() +
                                 "')";
                    falg = conn.executeUpdate(sql);
                    System.out.println("添加管理员信息的SQL：" + sql);
                    conn.close();
                }
            } catch (SQLException ex) {
                falg=0;
            }
        return falg;
    }

    //修改数据
    public int update(ManagerForm managerForm) {
        String sql1="SELECT * FROM tb_purview WHERE id="+managerForm.getId()+"";
        ResultSet rs=conn.executeQuery(sql1);
        String sql="";
        int falg=0;
        try {
            if (rs.next()) {
                sql = "Update tb_purview set sysset=" + managerForm.getSysset() +
                             ",readerset=" + managerForm.getReaderset() + ",bookset="+managerForm.getBookset()+",borrowback="+managerForm.getBorrowback()+",sysquery="+managerForm.getSysquery()+" where id=" +
                     managerForm.getId() + "";
            }else{
                sql="INSERT INTO tb_purview values("+managerForm.getId()+","+managerForm.getSysset()+","+managerForm.getReaderset()+","+managerForm.getBookset()+","+managerForm.getBorrowback()+","+managerForm.getSysquery()+")";
            }
            falg = conn.executeUpdate(sql);
            System.out.println("修改数据时的SQL：" + sql);
            conn.close();
        } catch (SQLException ex) {
            falg=0;
        }
        return falg;
    }
    //修改管理员密码
public int updatePwd(ManagerForm managerForm){
    String sql="UPDATE tb_manager SET pwd='"+managerForm.getPwd()+"' where name='"+managerForm.getName()+"'";
    int ret=conn.executeUpdate(sql);
    System.out.println("修改管理员密码时的SQL："+sql);
    conn.close();
    return ret;
}

//删除数据
    public int delete(ManagerForm managerForm) {
        String sql = "DELETE FROM tb_manager where id=" + managerForm.getId() +
                     "";
        int falg = conn.executeUpdate(sql);
        System.out.println("删除管理员信息的SQL：" + sql);
        conn.close();
        return falg;
    }
}