Java and SQL Database

Question

So I am trying to create a application that simulates a online video store. I have created a database on Workbench and I am trying to create a if statement that checks if the user input matches those on the emails and passwords on the database. But I either get a error about the connection or about the driver. Hoping someone can help me out, thanks!

Here is the Java code

public static void main(String[] args) throws SQLException, ClassNotFoundException
{       
            Class.forName("com.mysql.jdbc.Driver");
            Scanner input = new Scanner(System.in);
            String answer = "";
            String sql = "";
            String email = "";
            String password = "";

            Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?zeroDateTimeBehavior=convertToNull [root on Default schema]", "<username>", "<password>");                        
            Statement myStmt = myConn.prepareStatement(sql);
            ResultSet rs;

            while(!answer.equals("n") || !answer.equals("y") )
            {
                System.out.print("Do you have an account? (Y/N) : ");
                answer = input.nextLine();

                if(answer.toLowerCase().equals("n"))
                {
                    System.out.println("Please enter the email and password for your new account.");
                    System.out.print("Email: ");
                    email = input.nextLine();
                    System.out.print("Password: ");
                    password = input.nextLine();
                    sql = "insert into accounts "
        + " (UserEmail, UserPassword)" + " values (?, ?)";

                    myStmt.executeUpdate(sql);


                }
                else if(answer.toLowerCase().equals("y"))
                {
                    System.out.print("\nEmail: ");
                    email = input.nextLine();
                    System.out.print("\nPassword:");
                    password = input.nextLine();
                    rs = myStmt.executeQuery(sql);
                    if(!rs.absolute(1))
                    {
                        System.out.println("You do not have an account. Please create one.");
                        continue;
                    }
                }
                else{
                    System.out.println("Invalid input. Please try again.");
                    continue;
                }
            }

Here is my SQL script

create database users;
use users;
create Table Accounts(
UserEamil Char(20) NOT NULL ,
UserPassword Char(20) NOT NULL
);

Here is my error: Exception in thread "main" java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

Answer 1

Have you downloaded the mysql jdbc driver? You should be able to fix it by adding the classpath:

C:\test>java -cp c:\test\mysql-connector-java-5.1.8-bin.jar;c:\test JDBCExample

Taken from: https://www.mkyong.com/jdbc/how-to-connect-to-mysql-with-jdbc-driver-java/

Answer 2

This code is not going to work as the values have not been set

sql = "insert into accounts "
    + " (UserEmail, UserPassword)" + " values (?, ?)";

myStmt.executeUpdate(sql);

what you should do is create a PreparedStatement using the sql and then call setString for each paramater like

sql = "insert into accounts "
    + " (UserEmail, UserPassword)" + " values (?, ?)";
Statement myStmt = myConn.prepareStatement(sql);
myStmt.setString (1, email);
myStmt.setString (2, password);

myStmt.executeUpdate ();

note

Currently at the top of your code you have

        Connection myConn = DriverManager.getConnection("....");                        
        Statement myStmt = myConn.prepareStatement(sql);

but the value of sql at this time is an empty string - it will not work

note2

Consult this answer for how to set your connection String correctly