Java/MySQL

MarcL · 02-04-2009, 05:30 PM

Hi, I'm new to Java and mySQL. So any help here will be appreciated. Thanks

What i'm trying to do here is to print some values from my database.
But when i tried compiling it, it showed the error statement shown below. How should i declare the stats variable in the LoginMenu method where it would derive the stats statement from MainMenu class?

C:\Program Files\Java\Login.java:20: cannot find symbol
symbol : method executeQuery(java.lang.String)
location: class java.lang.String
ResultSet results = stat.executeQuery(selectQuery);

--------------------------------------------------------

Code:

import java.sql.*;
import java.util.*;
public class Login {

  public void loginMenu(){
    
    MainMenu mainMenu = new MainMenu();
		       
    String selectQuery = "Select * from userinfo";
    //get the results
    ResultSet results = stat.executeQuery(selectQuery) <----- Error Line
    //output the results
    while (results.next())
    {
     //example - column is called 'firstname'
    System.out.println("first name: " +
    results.getString("name"));
    }

}

----------------------------------------------------------

Code:

import java.sql.*;
import java.util.*;
public class MainMenu {
	
    public void dbConnection(){
      try{
        //create driver 
        Class.forName("com.mysql.jdbc.Driver");
        java.sql.Connection myConnection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/battlestation","root","123");
			
        //create statement handle for executing queries
        statement stat = myConnection.createStatement();
      }
      catch( Exception E ) { 
        System.out.println( E.getMessage() );	
      }
   }
    public static void main (String[]args){
    	
    	
    	MainMenu mainMenu = new MainMenu();
    	
    	mainMenu.dbConnection();

    }
}

---------------------------------------------------------------

Fou-Lu · 02-05-2009, 12:59 AM

Simple scope problem.
MainMenu should use a property for the Statement object (yes, case sensitive), and I'd use a property for the Connection object as well (so it can be closed later, perhaps with a finalize).
Then add an accessor for the Statement, and go from there in the Login object. Something like this:

Code:

import java.sql.*;
import java.util.*;
public class Login {

  public void loginMenu(){
    
    MainMenu mainMenu = new MainMenu();
		       
    String selectQuery = "Select * from userinfo";
    //get the results
    ResultSet results = mainMenu.getStatement().executeQuery(selectQuery) <----- Error Line
    //output the results
    while (results.next())
    {
     //example - column is called 'firstname'
    System.out.println("first name: " +
    results.getString("name"));
    }

}

Make sure you write a constructor for the MainMenu class that includes a call to db connection. Consider writing this as a singleton pattern as well so that everything can reuse the same sql connection object instead of creating new instances for every mainMenu call.
I'd also create a checked exception for you're dbConnection method instead of try/catching it. This forces whatever makes a call to dbConnection to enforce their own rules on an exception (such as terminating the program, logging to a file, etc).

MarcL · 02-09-2009, 04:47 AM

Hi i'm having trouble with my if else statement. Whenever i key in the username and password, i get a extra printout line that says "Invalid Username".

Even when i have the username and password right, it still prints out the invalid statement(as shown below). And when the username and password is invalid, the statement will be printed out twice.

Code:

public void loginMenu(){
    	
    	MainMenu mainMenu = new MainMenu();
    	
    	System.out.println("\n== BattleStations :: Login");
   		System.out.println("-------------------");
		Scanner sc = new Scanner(System.in);			
		System.out.print("Enter your username >");
		String scUsername = sc.nextLine();
		System.out.print("Enter your password >");
		String scPassword = sc.nextLine();
      	
      	try {
      	  //SQL statement to retrieve the data to be check against
          PreparedStatement ps=con.prepareStatement("Select * from userinfo");
          //sets the ? in the SQL statement
          //resultset to store the result of the query
          ResultSet rs=ps.executeQuery();
          //if resultset is not null
          if(rs!=null) {
             
             while(rs.next()){
            	 //this check if the password matches
            	 
            	if(scUsername.equals(rs.getString("username"))){
            		if(scPassword.equals(rs.getString("password"))){
            			System.out.println("Welcome " +rs.getString("username"));
	
     				} else{
       					 System.out.println("Invalid Password");
     				} 
        
    			} else{
       				System.out.println("Invalid Username");
    			}	  
             }
           }
          
          ps.close();
        } catch (SQLException e) {
          System.out.println(e+": Error has ocurred!");
        }
   //     return result;

      
	}

servlet · 02-09-2009, 06:03 AM

Did u verify that the database table has only one record? I doubt. verify it.

MarcL · 02-09-2009, 06:32 AM

Thanks for the reply. I actually have quite a few records in the database. I don't quite understand what you mean by verifying.

servlet · 02-09-2009, 06:39 AM

You have two records in db.

You have used 'Select * from userinfo' so it would return all the records. than you iterate over it, compate user name, and print.

There should be two records (I guess) , so when usernmae/password matches with one of the record, it prints welcome msg, and invalid user name msg (for the second record).

When username/password don't match with any of them, it prints invalid msg for two times.

You should use 'Select * from userinfo where username = ?'

MarcL · 02-09-2009, 08:25 AM

I get a error statement "java.sql.SQLException: No value specified for parameter 1: Error has ocurred!" when i use 'Select * from userinfo where username = ?'

servlet · 02-09-2009, 09:17 AM

That is because you don't set parameter.

Set parameter

Code:

ps.setString(1,scUsername);

Look at usage of PreparedStatement http://java.sun.com/docs/books/tutor.../prepared.html

MarcL · 02-09-2009, 01:10 PM

Code:

public void loginMenu(){
    //	boolean result=false;
    	
    	MainMenu mainMenu = new MainMenu();
    	
    	System.out.println("\n== BattleStations :: Login");
   		System.out.println("-------------------");
		Scanner sc = new Scanner(System.in);			
		System.out.print("Enter your username >");
		String scUsername = sc.nextLine();
		System.out.print("Enter your password >");
		String scPassword = sc.nextLine();
      	//int id = 0;
      	try {
      	  //SQL statement to retrieve the data to be check against
          PreparedStatement ps=con.prepareStatement("Select * from userinfo where username = ? AND password = ?");
          //sets the ? in the SQL statement
          //resultset to store the result of the query
          ps.setString(1,scUsername);
          ps.setString(2,scPassword);
          ResultSet rs=ps.executeQuery();
          //if resultset is not null
          if(rs!=null) {
             
             while(rs.next()){
            	 //this check if the password matches
            	 
            	if(scUsername.equals(rs.getString("username"))){
            		if(scPassword.equals(rs.getString("password"))){
            			System.out.println("Welcome " +rs.getString("username"));
	
     				} else{
       					 System.out.println("Invalid Password");
       					 mainMenu.displayLogin();
     				} 
        
    			} else{
       				System.out.println("Invalid Username");
    			}	  
             }
           }
          
          ps.close();
        } catch (SQLException e) {
          System.out.println(e+": Error has ocurred!");
        }
   //     return result;

      
	}

I don't know if i'm on the right track now. I did a "Select * from userinfo where username = ? AND password = ?" and setString for both scPassword and scUsername.

Now when the values are valid, it prints out fine.

But when i enter the invalid values, there are no printouts. Instead it should print out invalid username or invalid password.

Output:
== BattleStations :: Login
-------------------
Enter your username >123
Enter your password >123
<-- Invalid username should be displayed

servlet · 02-09-2009, 01:52 PM

Here is the code for you.

Code:

PreparedStatement ps=con.prepareStatement("Select * from userinfo where username = ?"); 
                
          ps.setString(1,scUsername);
        
          ResultSet rs=ps.executeQuery();
        
          if(rs!=null) {
             
             if (rs.next()) {
            	            	             	
            		if(scPassword.equals(rs.getString("password"))){
            			System.out.println("Welcome " +rs.getString("username"));
	
     				} else{
       					 System.out.println("Invalid Password");
       					 mainMenu.displayLogin();
     				} 
        
    			} 
                               else {
       				System.out.println("Invalid Username");
    			 }

I suggest, you learn basic branching/looping concepts and other basic stuff before diving in to this type of code.

MarcL · 02-09-2009, 03:20 PM

Just started doing programming, esp with data management. So been struggling to debug some problems. Thanks a lot appreciate your help.