userdb.java

JSP+oracle开发实例精粹》随书光盘

package qyxx;

import java.sql.*;

public class userDB
{
	String DBDriver =  "oracle.jdbc.driver.OracleDriver";
	String DBUrl = "jdbc:oracle:thin:@localhost:1521:ORACLE";
	String DBUser = "jerry";
	String DBPsw = "123456";
	Connection conn = null;
	PreparedStatement stmt = null;
	ResultSet rs = null;

	private Connection initDB()
	{
		try
		{
			Class.forName(DBDriver);
			return DriverManager.getConnection(DBUrl,DBUser,DBPsw);
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
	}

	public userTable loginCheck(String username, String password)
	{
		try
		{
			this.conn = null;
			conn = this.initDB();
			String sql = "select DISTINCT priority,name,age,sex, depart_name, depart_id from QYXX_USER a, QYXX_DEPARTMENT b where a.username='"+username+"' and a.password = '"+password+"' and b.id = a.depart_id";
			this.stmt = null;
			this.rs = null;
			this.stmt = conn.prepareStatement(sql);
			rs = this.stmt.executeQuery();
			if(rs.next())
			{
				userTable ut = new userTable();
				ut.setUsername(username);
				ut.setPriority(rs.getString(1));
				ut.setName(rs.getString(2));
				ut.setAge(rs.getString(3));
				ut.setSex(rs.getString(4));
				ut.setDepartname(rs.getString(5));
				ut.setDepartid(String.valueOf(rs.getInt(6)));

				return ut;
			}
			else
			{
				return null;
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				conn.close();
				stmt.close();
				rs.close();
			}
			catch(Exception e)
			{

			}
		}
	}

	public userTable getUserInfo(String username)
	{
		try
		{
			String sql = "select * from QYXX_USER where username = '"+username+"'";
			this.conn = null;
			conn = this.initDB();
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			rs = this.stmt.executeQuery();
			if(rs.next())
			{
				userTable ut = new userTable();
				ut.setUsername(rs.getString("username"));
                                ut.setPassword(rs.getString("password"));
				ut.setName(rs.getString("name"));
				ut.setAge(rs.getString("age"));
				ut.setSex(rs.getString("sex"));
				ut.setDepartid(String.valueOf(rs.getInt("depart_id")));

				return ut;
			}
			else
			{
				return null;
			}

		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				conn.close();
				stmt.close();
				rs.close();
			}
			catch(Exception e)
			{

			}
		}
	}


	public int addUser(qyxx.userTable utable)
	{
		try
		{
			this.conn = null;
			conn = this.initDB();
			String sql = "insert into QYXX_USER(username,password,priority,name,age,sex,depart_id) values(?,?,?,?,?,?,?)";
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			stmt.setString(1,utable.getUsername());
			stmt.setString(2,utable.getPassword());
			stmt.setString(3,utable.getPriority());
			stmt.setString(4,utable.getName());
			stmt.setString(5,utable.getAge());
			stmt.setString(6,utable.getSex());
			stmt.setString(7,utable.getDepartid());
			this.stmt.executeUpdate();
			return 1;
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return 0;
		}
		finally
		{
			try
			{
				conn.close();
				stmt.close();
			}
			catch(Exception e)
			{

			}
		}
	}

	public qyxx.userTable editUser(qyxx.userTable utable)
	{
		try
		{
			this.conn = null;
			conn = this.initDB();
			String sql = "update QYXX_USER set password = ?, name=?, age=?, sex=?, depart_id=? where username=?";
			this.stmt = null;
			this.rs = null;
			this.stmt = conn.prepareStatement(sql);
			stmt.setString(1,utable.getPassword());
			stmt.setString(2,utable.getName());
			stmt.setString(3,utable.getAge());
			stmt.setString(4,utable.getSex());
			stmt.setString(5,utable.getDepartid());
			stmt.setString(6,utable.getUsername());
			stmt.executeUpdate();

			sql = "select DISTINCT name,age,sex,depart_name,depart_id from QYXX_USER a,QYXX_DEPARTMENT b where a.username=? and b.id = a.depart_id";
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			stmt.setString(1,utable.getUsername());
			rs = this.stmt.executeQuery();
			if(rs.next())
			{
				utable.setName(rs.getString(1));
				utable.setAge(rs.getString(2));
				utable.setSex(rs.getString(3));
				utable.setDepartname(rs.getString(4));
				utable.setDepartid(String.valueOf(rs.getInt(5)));

				return utable;
			}
			else
			{
				return null;
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				conn.close();
				stmt.close();
				rs.close();
			}
			catch(Exception e)
			{

			}
		}
	}

	public int deleteUser(String [] username)
	{
		try
		{
			this.conn = null;
			conn = this.initDB();

			for(int i=0; i<username.length; i++)
			{
				String sql = "delete from QYXX_USER where username = '"+username[i]+"'";
				this.stmt = null;
				this.stmt = conn.prepareStatement(sql);
				this.stmt.executeUpdate();
			}

			return 1;
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return 0;
		}
		finally
		{
			try
			{
				conn.close();
				stmt.close();
			}
			catch(Exception e)
			{

			}
		}
	}

	public int editUserPriority(String [] username,int choice)
	{
		try
		{
			if(choice == 1)
			{
				this.conn = null;
				conn = this.initDB();

				for(int i=0; i<username.length; i++)
				{
					String sql = "update QYXX_USER set priority='2' where username = '"+username[i]+"'";
					this.stmt = null;
					this.stmt = conn.prepareStatement(sql);
					this.stmt.executeUpdate();
				}
			}
			else
			{
				if(choice == 2)
				{
					this.conn = null;
					conn = this.initDB();

					for(int i=0; i<username.length; i++)
					{
						String sql = "update QYXX_USER set priority='1' where username = '"+username[i]+"'";
						this.stmt = null;
						this.stmt = conn.prepareStatement(sql);
						this.stmt.executeUpdate();
					}
				}
				else
				{
					rs = null;
					conn = this.initDB();
					for(int i=0; i<username.length; i++)
					{
						String sql = "select priority from QYXX_USER where username = '"+username[i]+"'";
						this.stmt = null;
						this.stmt = conn.prepareStatement(sql);
						rs = this.stmt.executeQuery();
						if(rs.next())
						{
							String pr = rs.getString(1);
							int p = Integer.parseInt(pr) + 1;
							sql = "update QYXX_USER set priority = '"+String.valueOf(p)+"' where username = '"+username[i]+"'";
							this.stmt = null;
							this.stmt = conn.prepareStatement(sql);
							stmt.executeUpdate();
						}
						else
						{
							return 0;
						}
					}
				}
			}
			return 1;
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return 0;
		}
		finally
		{
			try
			{
				conn.close();
				stmt.close();
			}
			catch(Exception e)
			{

			}
		}
	}
}