package library;
import java.sql.*;
import java.util.Vector;
import java.lang.*;
import javax.servlet.http.HttpServletRequest;
import library.*;
/**
* Title:
* Description:
* Copyright:
* Company:
* @author:
* @version 1.0
*/
public class UserDB{
private User user=new User() ; //新的用户对象
private javax.servlet.http.HttpServletRequest request; //建立页面请求
private String message = ""; //出错信息提示
private long userid = 0; //注册后返回的用户ID
private int id;
private String userName ; //注册后返回的用户名
private Vector userlist; //显示用户列表向量数组
private int page = 1; //显示的页码
private int pageSize=6; //每页显示的图书数
private int pageCount =0; //页面总数
private int recordCount =0; //查询的记录总数
private int deptid;
private String sqlStr;
private ResultSet Rs;
private OpenDB userDB;
public UserDB() throws Exception{
}
public Vector getUserlist() {
return userlist;
}
public String getGbk( String str) {
try
{
return new String(str.getBytes("ISO8859-1"));
}
catch (Exception e)
{
return str;
}
}
//将页面表单传来的资料分解
public boolean getRequest(javax.servlet.http.HttpServletRequest newrequest) {
boolean flag = false;
try
{
request = newrequest;
String ID = request.getParameter("userid");
if (ID!=null )
{
userid = 0;
try
{
userid = Long.parseLong(ID);
user.setId(userid);
}
catch (Exception e)
{
message = message + "你要修改的用户号出错!";
}
}
String username = request.getParameter("username");
if (username==null || username.equals(""))
{
username = "";
message = message + "用户名为空!";
return false;
}
user.setUserName(getGbk(username));
String password = request.getParameter("passwd");
if (password==null || password.equals(""))
{
password = "";
message = message + "密码为空!";
return false;
}
String pwdconfirm = request.getParameter("passconfirm");
if (!password.equals(pwdconfirm))
{
message = message + "确认密码不相同!";
return false;
}
user.setPassWord(getGbk(password));
String sex = request.getParameter("sex");
user.setSex(getGbk(sex));
String address = request.getParameter("address");
if (address == null)
{
address = "";
}
user.setAddress(getGbk(address));
String dept = request.getParameter("dept");
if (dept != null)
{
deptid = 0;
try
{
deptid = Integer.parseInt(dept);
user.setDeptId(deptid);
}
catch (Exception e)
{
// message = message + "你要修改的用户号出错!";
}
}
user.setDept(getGbk(dept));
String phone = request.getParameter("phone");
if (phone== null)
{
phone = "";
}
user.setPhone(phone);
String email = request.getParameter("email");
if (email == null)
{
email = "";
}
user.setEmail(getGbk(email));
if (message.equals(""))
{
flag = true;
}
return flag;
}
catch (Exception e)
{
return flag;
}
}
public boolean insert(HttpServletRequest req) throws Exception {
if (getRequest(req)) {
OpenDB userDB=new OpenDB();
sqlStr = "select * from users where username = '" + user.getUserName() +"'";
Rs = userDB.executeQuery(sqlStr);
if (Rs.next())
{
message = message + "该用户名已存在!";
return false;
}
sqlStr = "insert into users (username,password,sex,Address,Phone,dept,Email,RegTime) values ('";
sqlStr = sqlStr + strFormat.toSql(user.getUserName()) + "','";
sqlStr = sqlStr + strFormat.toSql(user.getPassWord()) + "','";
sqlStr = sqlStr + strFormat.toSql(user.getSex()) + "','";
sqlStr = sqlStr + strFormat.toSql(user.getAddress()) + "','";
sqlStr = sqlStr + strFormat.toSql(user.getPhone()) + "',";
sqlStr = sqlStr + strFormat.toSql(String.valueOf(user.getDeptId())) + ",'";
sqlStr = sqlStr + strFormat.toSql(user.getEmail()) + "',getdate())";
System.out.println(sqlStr);
try
{
Rs = userDB.executeQuery(sqlStr);
sqlStr = "select id from users where username = '" +user.getUserName()+ "'";
Rs = userDB.executeQuery(sqlStr);
while (Rs.next())
{
userid = Rs.getLong(1);
}
userDB.CloseConn();
return true;
}
catch (Exception e)
{
return false;
}
} else {
return false;
}
}
public boolean update(HttpServletRequest req) throws Exception {
if (getRequest(req)){
sqlStr = "update users set ";
sqlStr = sqlStr + "username = '" + strFormat.toSql(user.getUserName()) + "',";
sqlStr = sqlStr + "password = '" + strFormat.toSql(user.getPassWord()) + "',";
sqlStr = sqlStr + "sex = '" + strFormat.toSql(user.getSex()) + "',";
sqlStr = sqlStr + "address = '" + strFormat.toSql(user.getAddress()) + "',";
sqlStr = sqlStr + "phone = '" + strFormat.toSql(user.getPhone()) + "',";
sqlStr = sqlStr +"dept="+ strFormat.toSql(String.valueOf(user.getDeptId())) + ",";
sqlStr = sqlStr + "Email = '" + strFormat.toSql(user.getEmail()) + "' ";
sqlStr = sqlStr + " where id = '" + user.getId() + "'";
try{
OpenDB userDB=new OpenDB();
System.out.println(sqlStr);
userDB.executeUpdate(sqlStr);
userDB.CloseConn();
return true;}
catch (Exception e)
{
return false;
}
} else {
System.out.println(message);
return false;
}
}
public boolean delete( long aid ) throws Exception {
sqlStr = "delete from users where id = " + aid ;
try
{
OpenDB userDB=new OpenDB();
userDB.executeQuery(sqlStr);
userDB.CloseConn();
return true;
}
catch (Exception e)
{
System.out.println(e);
return false;
}
}
public boolean execute(HttpServletRequest res) throws Exception {
request = res;
String PAGE = request.getParameter("page"); //页码
String classid = request.getParameter("classid"); //分类ID号
String keyword = request.getParameter("keyword"); //查询关键词
String Id=request.getParameter("userid");
if (classid==null) classid="";
if (keyword==null) keyword = "";
if(PAGE==null) PAGE="";
if(Id==null) Id="";
keyword = getGbk(keyword).toUpperCase();
try
{
page = Integer.parseInt(PAGE);
}
catch (NumberFormatException e)
{
page = 1;
}
try
{
id = Integer.parseInt(Id);
}
catch (NumberFormatException e)
{
id = 1;
}
if (page<0) page=1;
String contion1=" and a.dept='"+classid+"'";
String contion2=" and upper(a.username) like '%" +keyword+ "%'";
String contion3=" and a.id='"+id+"'";
sqlStr = "select a.id ,username ,password, sex ,address ,phone, dept ,email, regtime ,L_OfficeName from users a,L_SectionOffice b where a.dept=b.id";
if (keyword.equals("")&&Id.equals("")){
if (!classid.equals("")) sqlStr+=contion1;}
else {
if (!Id.equals("")) sqlStr+=contion3;
else sqlStr+=contion2;
}
try
{
System.out.println(sqlStr);
OpenDB userDB=new OpenDB();
Rs = userDB.executeQuery(sqlStr);
Rs.last();
recordCount=Rs.getRow();
if(recordCount<0) pageCount=0;
else pageCount=(recordCount+pageSize-1)/pageSize;
if (page>pageCount) page=pageCount;
Rs.absolute((page-1)*pageSize+1);
int i=0;
userlist = new Vector();
while (i<pageSize&&!Rs.isAfterLast()){
User user=new User();
user.setId(Rs.getLong("id"));
user.setUserName(Rs.getString("username"));
user.setPassWord(Rs.getString("password"));
user.setSex(Rs.getString("sex"));
user.setAddress(Rs.getString("address"));
user.setPhone(Rs.getString("Phone"));
user.setDept(Rs.getString("L_OfficeName"));
user.setEmail(Rs.getString("email"));
user.setDeptId(Rs.getInt("dept"));
user.setRegTime(Rs.getString("regtime"));
userlist.addElement(user);
Rs.next();
i++;
}
userDB.CloseConn();
return true;
}
catch (SQLException e)
{
return false;
}
}
public boolean getUserinfo(long newid ) throws Exception {
try
{
sqlStr="select a.id ,username ,password, sex ,address ,phone, dept ,email, regtime ,L_OfficeName from users a,L_SectionOffice b where a.dept=b.id and a.Id = " + newid ;
OpenDB userDB=new OpenDB();
Rs = userDB.executeQuery(sqlStr);
userlist = new Vector();
while (Rs.next()){
user.setId(Rs.getLong("id"));
user.setUserName(Rs.getString("username"));
user.setPassWord(Rs.getString("password"));
user.setSex(Rs.getString("sex"));
user.setAddress(Rs.getString("address"));
user.setPhone(Rs.getString("Phone"));
user.setDept(Rs.getString("L_OfficeName"));
user.setEmail(Rs.getString("email"));
user.setRegTime(Rs.getString("regtime"));
user.setDeptId(Rs.getInt("dept"));
userlist.addElement(user);
}
userDB.CloseConn();
return true;
}
catch (SQLException e)
{
return false;
}
}
public boolean isAdmin(String dept)throws Exception {
try{
sqlStr="select * from L_SectionOffice where L_officename='"+dept+"'";
OpenDB userDB=new OpenDB();
int admin=0;
Rs = userDB.executeQuery(sqlStr);
System.out.println(sqlStr);
while (Rs.next()){
admin=Rs.getInt("L_Lend");
}
userDB.CloseConn();
if (admin==1) return true;
else return false;
}
catch (SQLException e)
{
userDB.CloseConn();
return false;
}
}
public String getMessage() {
return message;
}
public void setMessage(String msg) {
message = msg;
}
public void setUserid(long uid) {
userid = uid;
}
public long getUserid() {
return userid;
}
public void setName(String uName) {
userName = uName;
}
public String getName() {
return userName;
}
public int getPage() { //显示的页码
return page;
}
public void setPage(int newpage) {
page = newpage;
}
public int getPageSize(){ //每页显示的图书数
return pageSize;
}
public void setPageSize(int newpsize) {
pageSize = newpsize;
}
public int getPageCount() { //页面总数
return pageCount;
}
public void setPageCount(int newpcount) {
pageCount = newpcount;
}
public long getRecordCount() {
return recordCount;
}
public void setRecordCount(int newrcount) {
recordCount= newrcount;
}
}