userdb.java

《Web程序测试实训教程（Java版）》-徐民鹰-源代码

package user;

import java.sql.*;
import java.util.*;
import com.ConnDB1;

public class UserDB extends ConnDB1 implements IUserDB{

    private ArrayList user;
   
    public UserDB () throws Exception
    {
        //
    }

    //
    public boolean userExist(String username){
      boolean occupied=true;
      try
      {
    	  ConnDB1.initialize();	// create database connection
    	  PreparedStatement preparedStatement = aConnection.prepareStatement (
    			  "select userid from Buser where username=?");
    	  preparedStatement.setString (1, username);
    	  ResultSet resultSet = preparedStatement.executeQuery ();
          if(!resultSet.next())
              occupied=false;
              preparedStatement.close ();
              ConnDB1.terminate();
          }
          catch(SQLException e){
              e.printStackTrace();
              ConnDB1.terminate();
          }
      return occupied;
    }
    
    public boolean isValidUser (String username, String password)
    {
        boolean isValid=false;
    	try
        {
    		ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            		"SELECT username, password FROM Buser WHERE username = ? and password= ?");
            preparedStatement.setString (1, username);
            preparedStatement.setString (2, password);
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ())
            {
                isValid=true;
            	preparedStatement.close ();
            	ConnDB1.terminate();
            }
            else
            {
                preparedStatement.close ();
                ConnDB1.terminate();
                //return isValid;
            }
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            //return isValid;
        }
        return isValid;
    }
    
    public boolean find(String key) 
	{    
    	boolean gotIt=false;
    	try
        {
    		ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            		"SELECT username, password FROM Buser WHERE username = ?");
            preparedStatement.setString (1, key);
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ())
            {
            	gotIt=true;
            	preparedStatement.close ();
            	ConnDB1.terminate();
            }
            else
            {
                preparedStatement.close ();
                ConnDB1.terminate();
                //return isValid;
            }
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            //return gotIt;
        }
        return gotIt;
	}
    
    public User getUser (int id)
    {
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            		"SELECT userid, username, rname, password, email, address, postdate, tel " +
            		"FROM Buser WHERE userId = ?");
            preparedStatement.setInt (1, id);
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ())
            {
                User users = new User
                (
                    resultSet.getInt (1), resultSet.getString (2), resultSet.getString (3), resultSet.getString (4),
                    resultSet.getString (5), resultSet.getString (6), resultSet.getString (7), resultSet.getString (8)
                );
                preparedStatement.close ();
                ConnDB1.terminate();
                return users;
            }
            else
            {
                preparedStatement.close ();
                ConnDB1.terminate();
                return null;
            }
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return null;
        }
    }
    
    public User getUser (String username)
    {
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            		"SELECT userid, username, rname, password, email, address, postdate, tel " +
            		"FROM Buser WHERE username = ?");
            preparedStatement.setString (1, username);
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ())
            {
                User users = new User
                (
                    resultSet.getInt (1), resultSet.getString (2), resultSet.getString (3), resultSet.getString (4),
                    resultSet.getString (5), resultSet.getString (6), resultSet.getString (7), resultSet.getString (8)
                );
                preparedStatement.close ();
                ConnDB1.terminate();
                return users;
            }
            else
            {
                preparedStatement.close ();
                ConnDB1.terminate();
                return null;
            }
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return null;
        }
    }
    
    public int addUsers (User users)
    {
        int rowsAffected = 0;
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            		"INSERT INTO Buser (username, rname, password, email, address, postdate, tel) " +
            		"VALUES (?, ?, ?, ?, ?, ?, ?)");
            preparedStatement.setString (1, users.getUsername ());
            preparedStatement.setString (2, users.getRname ());
            preparedStatement.setString (3, users.getPassword());
            preparedStatement.setString (4, users.getEmail());
            preparedStatement.setString (5, users.getAddress());
            preparedStatement.setString (6, users.getDatetime());
            preparedStatement.setString (7, users.getTel());
            //鍒ゆ柇鏄