userdboperation.java

利用Mysql数据库、struts框架实现的web系统开发

package com.dao.dbcontrol.system.user;

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.Collection;

import com.dao.conn.*;
import com.bean.system.user.UserForm;
import com.dao.conn.*;

public class UserDBOperation {
	private Connection conn;

	private PreparedStatement pstmt;

	// private Statement st;
	private ResultSet rs;

	private String query;

	private Statement stmt;
	private int count = 0;// 记录SQL语句所影响的行数
    private int results;//记录查询结果总数
    public boolean checkLogon(String uname,String pwd) {
		conn=ConnDB.getConnection();
		boolean checked=false;
		query="select userid from user where login='"+uname+"' and password='"+pwd+"'";
		try {
			 conn.setAutoCommit(false);
			 stmt=conn.createStatement();
			 rs=stmt.executeQuery(query);
			 if(rs.next()) {
				 checked=true;
			 }
		}catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} 
		finally {
			try {
				if(rs==null) rs.close();
				if(stmt==null) stmt.close();
				if(conn==null) conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return checked;
	}
	public Collection userAll() {
		conn = ConnDB.getConnection();
		Collection list = new ArrayList();
		query = "select * from user";
		try {
			pstmt = conn.prepareStatement(query);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				UserForm bean = new UserForm();
				bean.setUserid(String.valueOf(rs.getInt(1)));
				bean.setLogin(rs.getString(2));
				bean.setUsername(rs.getString(3));
				bean.setCertificate(rs.getString(5));
				bean.setCardnum(rs.getString(6));
				bean.setDepartment(rs.getString(7));
				bean.setUsersex(rs.getString(8));
				bean.setContact(rs.getString(9));
				bean.setIdentity(rs.getString(10));
				list.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	public UserForm userSelectId(UserForm user) {
		conn = ConnDB.getConnection();
		query = "select * from user where userid = ?" ;
		UserForm bean = new UserForm();
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1,user.getUserid());
			rs = pstmt.executeQuery();
			while (rs.next()) {	
				bean.setUserid(String.valueOf(rs.getInt(1)));
				bean.setLogin(rs.getString(2));
				bean.setUsername(rs.getString(3));
				bean.setCertificate(rs.getString(5));
				bean.setCardnum(rs.getString(6));
				bean.setDepartment(rs.getString(7));
				bean.setUsersex(rs.getString(8));
				bean.setContact(rs.getString(9));
				bean.setIdentity(rs.getString(10));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return bean;
	}
	// 用户信息查询:分为管理员、普通用户和超级用户
	/*
	 * public Collection userInfoSelect(UserForm user){ conn =
	 * ConnDB.getConnection(); Collection list = new ArrayList(); String
	 * property = user.getUproperty(); //普通用户查询 if("普通用户".equals(property)){
	 * query = "select uId,uName,name,gender,password," +
	 * "certificateType,cardId,department,uProperty " + "from user"; query += "
	 * where name = ? and password = ?"; //只有用户名和密码同时的时候，并且只能看自己的信息 try { pstmt =
	 * conn.prepareStatement(query); pstmt.setString(1,user.getUname());
	 * pstmt.setString(2,user.getPassword()); } catch (SQLException e) {
	 * e.printStackTrace(); } } //管理员查询 if("管理员".equals(property)){ query =
	 * "select uId,uName,name,gender,password," +
	 * "certificateType,cardId,department,uProperty " + "from user"; query += "
	 * where name = ?";//姓名 query += " or gender = ?";//性别 query += " or
	 * department = ?";//部门 query += " or uProperty = ?";//用户性质 //能查看所有人的信息 try {
	 * pstmt = conn.prepareStatement(query); pstmt.setString(1,user.getName());
	 * pstmt.setString(2,user.getGender());
	 * pstmt.setString(3,user.getDepartment());
	 * pstmt.setString(4,user.getUproperty()); } catch (SQLException e) {
	 * e.printStackTrace(); } } try { rs = pstmt.executeQuery();
	 * while(rs.next()){ UserForm bean = new UserForm();
	 * bean.setUid(String.valueOf(rs.getInt(1)));
	 * bean.setUname(rs.getString(2)); bean.setName(rs.getString(3));
	 * bean.setGender(rs.getString(4)); bean.setPassword(rs.getString(5));
	 * bean.setCertificateType(rs.getString(6));
	 * bean.setCardId(rs.getString(7)); bean.setDepartment(rs.getString(8));
	 * bean.setUproperty(rs.getString(9)); list.add(bean); } } catch
	 * (SQLException e) { // TODO Auto-generated catch block
	 * e.printStackTrace(); }finally{ try { rs.close(); pstmt.close();
	 * conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return
	 * list; }
	 */
	// 备用方法
	public Collection userInfoSelect(UserForm user) {
		conn = ConnDB.getConnection();
		Collection list = new ArrayList();
		query = "select * from user where login = ?";
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, user.getLogin());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				UserForm bean = new UserForm();
				bean.setUserid(String.valueOf(rs.getInt(1)));
				bean.setLogin(rs.getString(2));
				bean.setUsername(rs.getString(3));
				bean.setCertificate(rs.getString(5));
				bean.setCardnum(rs.getString(6));
				bean.setDepartment(rs.getString(7));
				bean.setUsersex(rs.getString(8));
				bean.setContact(rs.getString(9));
				bean.setIdentity(rs.getString(10));
				list.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	public boolean checkLogoned(String login) {
		conn=ConnDB.getConnection();
		boolean checked=false;
		query="select * from user where login='"+login+"'";
		try {
			 conn.setAutoCommit(false);
			 stmt=conn.createStatement();
			 rs=stmt.executeQuery(query);
			 if(rs.next()) {
				 checked=true;
			 }
		}catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} 
		finally {
			try {
				 rs.close();
				 stmt.close();
				 conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return checked;
	}
	
	// 用户信息添加
	public boolean userInfoInsert(UserForm user) {
		conn = ConnDB.getConnection();
		query = "insert into user"
				+ "(login,username,password,certificate,cardnum,department,usersex,contact,cardnum)";
		query += " values(?,?,?,?,?,?,?,?,?)";
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, user.getLogin());
			pstmt.setString(2, user.getUsername());
			pstmt.setString(3, user.getPassword());
			pstmt.setString(4, user.getCertificate());
			pstmt.setString(5, user.getCardnum());
			pstmt.setString(6, user.getDepartment());
			pstmt.setString(7, user.getUsersex());
			pstmt.setString(8, user.getContact());
			pstmt.setString(9,user.getCardnum());
			
			count += pstmt.executeUpdate();
			if (1 == count) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				pstmt.close();
				conn.close();
				count = 0;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}

	// 用户信息更新
	public boolean userInfoUpdate(UserForm user) {
		conn = ConnDB.getConnection();
		query = "update user set login = ?,";
		query += "username = ?,";
		query += "password = ?,";
		query += "certificate = ?,";
		query += "cardnum = ?,";
		query += "department = ?,";
		query += "usersex = ?,";
		query += "contact = ?,";
		query += "cardnum = ?";
		query += " where userid = ?";
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, user.getLogin());
			pstmt.setString(2, user.getUsername());
			pstmt.setString(3, user.getPassword());
			pstmt.setString(4, user.getCertificate());
			pstmt.setString(5, user.getCardnum());
			pstmt.setString(6, user.getDepartment());
			pstmt.setString(7, user.getUsersex());
			pstmt.setString(8, user.getContact());
			pstmt.setString(9, user.getCardnum());
			pstmt.setInt(10, user.getUserid());
			count += pstmt.executeUpdate();
			if (1 == count) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}

	// 用户信息删除
	public boolean userInfoDel(UserForm user) {
		conn = ConnDB.getConnection();
		query = "delete from user where userid = ? ";
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, user.getUserid());
			count += pstmt.executeUpdate();
			if (1 == count) {
				return true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}
		return false;
	}
	
}