Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11

Thread: Java/MySQL

  1. #1
    New Coder
    Join Date
    Feb 2009
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Java/MySQL

    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();
    
        }
    }
    ---------------------------------------------------------------

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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).
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    New Coder
    Join Date
    Feb 2009
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Java/MySQL : if else

    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;
    
          
    	}
    Database:
    table : userinfo
    field : id|name|username|password
    value : 1|Marc|MarcL|123

    output :

    == BattleStations :: Login
    -------------------
    Enter your username >MarcL
    Enter your password >123
    Welcome MarcL
    Invalid Username <--- where did this come from?

  • #4
    Regular Coder
    Join Date
    Jan 2009
    Location
    india
    Posts
    145
    Thanks
    0
    Thanked 5 Times in 5 Posts
    Did u verify that the database table has only one record? I doubt. verify it.

  • #5
    New Coder
    Join Date
    Feb 2009
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the reply. I actually have quite a few records in the database. I don't quite understand what you mean by verifying.

  • #6
    Regular Coder
    Join Date
    Jan 2009
    Location
    india
    Posts
    145
    Thanks
    0
    Thanked 5 Times in 5 Posts
    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 = ?'

  • #7
    New Coder
    Join Date
    Feb 2009
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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 = ?'

  • #8
    Regular Coder
    Join Date
    Jan 2009
    Location
    india
    Posts
    145
    Thanks
    0
    Thanked 5 Times in 5 Posts
    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

  • #9
    New Coder
    Join Date
    Feb 2009
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  • #10
    Regular Coder
    Join Date
    Jan 2009
    Location
    india
    Posts
    145
    Thanks
    0
    Thanked 5 Times in 5 Posts
    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.

  • #11
    New Coder
    Join Date
    Feb 2009
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Just started doing programming, esp with data management. So been struggling to debug some problems. Thanks a lot appreciate your help.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •