Java/MySQL

[MarcL]

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

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

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

}

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

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]

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:

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]

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.


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?

[servlet]

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

[MarcL]

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]

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]

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]

That is because you don't set parameter.

Set parameter


ps.setString(1,scUsername);


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

[MarcL]

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]

Here is the code for you.



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]

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