CODEWITHSUNDEEP
javamysql
exxcellent
exxcellent

Reputation: 659

web application servlet to query and display user information from mysql

I need to perform a task for a webpage application, that is to have a page to display the client's information by querying for them via the client's session id (or some other method) and do a mySQL query into my membership database.

The sequence should be as follows: 1. client logs in 2. compare of client's password and membership database, if match, client will be able to access some pages 3. one of these accessible pages(mentioned in 2)in will contain a link which will then query for the client's information in membership database 4. queried rows(results) will be displayed in the webpage with proper css

just a disclaimer, I am a total beginner at java

Membership database table's name = member

id | first name | last name | address | telephone number

I have already created and setup a jdbc connection to database:

   public void init () throws ServletException {
      try {
         Class.forName("com.mysql.jdbc.Driver");

         conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/membership","root","");

         stmt = conn.createStatement();

      }  catch (ClassNotFoundException ex) {  // for Class.forName()
         ex.printStackTrace();
      }  catch (SQLException ex) {            // for getConnection()
         ex.printStackTrace();
      }
   }

         String query = "SELECT * FROM member WHERE xxx ";

         ResultSet rset = stmt.executeQuery(query);

my question is: how do i grab the necessary rows for my problem here? and how should i approach to solve this question?

many thanks for your advice

Upvotes: 0

Views: 1020

Answers (2)

niczero
niczero

Reputation: 367

First, I strongly recommend you have no spaces in your column names (eg use '_' instead).
What are your user credentials for logging in? I imagine it's username or email_address as well as password. Whatever the first part of that is (eg username) you could use that as the primary key on your member table. So after they've logged in, you already have username and that forms the 'where' clause in your SQL query. (If they log in with email_address, everything works the same, using that in place of username.)

So the steps are

  1. Take username + password from user
  2. Select count of rows matching that pair
  3. If count == 1 then they're authenticated, otherwise reject
  4. Select other fields matching username
  5. Display to user

Don't store passwords plaintext, so whatever transform you apply for storing them, also apply that to the given password before doing #2. If you choose to have username not be a primary key, ensure you make it at least 'unique' via a constraint.

Upvotes: 0

Mechkov
Mechkov

Reputation: 4324

"my question is: how do i grab the necessary rows for my problem here?"

Use your ResultSet object (rset). Then retrieve any columns you need. Something similar to this:

 while (rs.next()) {
    int ID = rs.getInt("id");  
    String fName = rs.getString("first_name");
    String lName = rs.getString("last_name");
    String address = rs.getString("address");
    String phone = rs.getString("telephone_number");        
  }

Upvotes: 1

Related Questions