managerdao.java

这是一个基于图书馆系统 主要是用struts hibernate oracle做的

package com.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;

import com.actionForm.ManagerForm;
import com.core.ConnDB;

public class ManagerDAO {// 系统登陆模块
	com.core.ConnDB conn = new ConnDB();

	public int checkManager(ManagerForm managerForm) {
		int flog = 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("pwd"))) {
					flog = 1;
					int rowSum = rs.getRow();
					if (rowSum != 1) {
						flog = 0;
						System.out.println("获得row的值" + sql + rowSum);
					}
				} else {
					flog = 0;
				}
			} else {
				flog = 0;
			}

		} catch (SQLException e) {
			flog = 0;
			e.printStackTrace();
		}
		return flog;

	}

	/** ************************************************************* */
	// 查询用户权限(集合)
	public Collection query(String queryif) {
		ManagerForm manager = null;
		Collection managercoll = new java.util.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 and m.name ='"
					+ queryif + "'";
		}
		ResultSet rs = conn.executeQuery(sql);
		int a = 0;
		try {
			while (rs.next()) {
				manager = new ManagerForm();
				manager.setId(Integer.valueOf(rs.getString(1)));
				manager.setName(rs.getString(2));
				manager.setPwd(rs.getString(3));
				manager.setSysset(rs.getInt(4));
				manager.setReaderset(rs.getInt(5));
				manager.setBookset(rs.getInt(6));
				manager.setBorrowback(rs.getInt(7));
				manager.setSysquery(rs.getInt(8));
				managercoll.add(manager);
				a++;
				System.out.println("记录总数" + a);

			}
		} catch (SQLException e) {
		}
		return managercoll;

	}

	/** ************************************************************* */

	// 按照对象查询
	public ManagerForm query_p(String str) {
		ManagerForm manager = null;
		String sql = "select m.*,p.sysset,p.readerset,p.bookset,p.borrowback,p.sysquery from tb_manager m,tb_purview p where m.id=p.id and m.name='"
				+ str + "'";
		ResultSet rs = conn.executeQuery(sql);
		System.out.println(rs);
		try {
			while (rs.next()) {

				manager = new ManagerForm();
				manager.setId(Integer.valueOf(rs.getString(1)));
				manager.setName(rs.getString(2));
				manager.setPwd(rs.getString(3));
				manager.setSysset(rs.getInt(4));
				manager.setReaderset(rs.getInt(5));
				manager.setBookset(rs.getInt(6));
				manager.setBorrowback(rs.getInt(7));
				manager.setSysquery(rs.getInt(8));

			}
		} catch (SQLException e) {
		}
		return manager;

	}

	// 根据id修改权限要先查询
	  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;
	    }

	// 根据name查询 更改口令
	public ManagerForm query_pwd(ManagerForm managerForm) {
		String sql = "SELECT * FROM tb_manager WHERE name='"
				+ managerForm.getName() + "'";
		ManagerForm managerForm1 = null;
		ResultSet rs = conn.executeQuery(sql);
		try {
			while (rs.next()) {
				managerForm1.setId(Integer.valueOf(rs.getString(1)));
				managerForm1.setName(rs.getString(2));
				managerForm1.setPwd(rs.getString(3));

			}
		} catch (SQLException e) {
		}
		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 flog = 0;
		try {
			if (rs.next()) {
				flog = 2;
			} else {
				sql = "insert into tb_manager(name,pwd) values ('"
						+ managerForm.getName() + "','" + managerForm.getPwd()
						+ "')";
				flog = conn.executeUpdate(sql);
				System.out.println("添加管理员信息的sql" + sql);
				conn.close();
			}
		} catch (SQLException e) {
			flog = 0;
		}

		return flog;

	}

	// 修改数据
	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.getName()
				+ "' 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 flag = conn.executeUpdate(sql);
		if (flag != 0) {
			String sql1 = "DELETE FROM tb_purview where id="
					+ managerForm.getId() + "";
			conn.executeUpdate(sql1);
		}
		conn.close();
		return flag;
	}

	public static void main(String args[]) {
		ManagerDAO managerDAO = new ManagerDAO();
		ManagerForm manager1 = managerDAO.query_p("admin");

		System.out.println(manager1.getBorrowback());
	}
}