How do I "simplify" my Java code for a MySQL database program?

Question

I'm looking for some help in the following.

Here is my code:

package com.company;

import com.mysql.jdbc.exceptions.MySQLDataException;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class Main {

public static void main(String[] args) {

    Connection mysql = null;
    PreparedStatement pst = null;
    ResultSet data = null;

    try
    {
        Class.forName("com.mysql.jdbc.Driver");
        String connectionStringURL = "jdbc:mysql://us-cdbr-azure-west-b.cleardb.com:3306/database";
        mysql = DriverManager.getConnection(connectionStringURL, "username", "password");

        if(mysql == null)
            System.out.println("Connection Failed");
        else
            System.out.println("Success");

        System.out.println("What would you like to do?");
        System.out.println("Type 1 to display all students.");
        System.out.println("Type 2 to Add/Update students.");
        System.out.println("Type 3 to Delete students.");
        System.out.println("Type 4 to Search for a student.");

        Scanner scan = new Scanner(System.in);
        int input = scan.nextInt();

        if(input == 1)
        {
            pst = mysql.prepareStatement("SELECT * FROM Student;");
            data = pst.executeQuery();
            while (data.next()) {
                System.out.println(data.getString("studentId"));
                System.out.println(data.getString("firstname"));
                System.out.println(data.getString("lastname"));
                System.out.println(data.getInt("gpa"));
                System.out.println(data.getString("major"));
                System.out.println(data.getString("facultyAdvisor"));
                System.out.println();
            }
            data.close();
        }

        if(input == 2)
        {
            System.out.println("What would you like to do?");
            System.out.println("Type Add to add a student.");
            System.out.println("Type Update to update a student's information.");
            String answer = scan.next();

            if(answer.equals("Add"))
            {
                System.out.println("Enter the student First Name");
                String firstname = scan.next();
                System.out.println("Enter the student's Last Name");
                String lastname = scan.next();
                System.out.println("Enter the student's GPA");
                int GPA = scan.nextInt();
                System.out.println("Enter the student's Major");
                String Major = scan.next();
                System.out.println("Enter the student's Faculty Advisor");
                String FacultyAdvisor = scan.next();

                pst = mysql.prepareStatement("INSERT INTO Student (FirstName, LastName, GPA, Major, FacultyAdvisor) VALUES (?, ?, ?, ?, ?)");
                pst.setString(1, firstname);
                pst.setString(2, lastname);
                pst.setInt(3, GPA);
                pst.setString(4, Major);
                pst.setString(5, FacultyAdvisor);
                pst.executeUpdate();
            }

            if(answer.equals("Update"))
            {
                System.out.println("Please enter the student's Id");
                int id = scan.nextInt();
                System.out.println("You may only update the Major or the Advisor.  Which would you like to update?");
                System.out.println("Enter Major to update the major.");
                System.out.println("Enter Advisor to update the advisor.");
                String result = scan.next();

                if(result.equals("Major"))
                {
                    System.out.println("Please enter the new Major");
                    String Major = scan.next();
                    pst = mysql.prepareStatement("UPDATE Student SET Major = ? WHERE StudentId = ?");
                    pst.setString(1, Major);
                    pst.setInt(2, id);
                    pst.executeUpdate();
                }

                if(result.equals("Advisor"))
                {
                    System.out.println("Please enter the new Advisor");
                    String Advisor = scan.next();
                    pst = mysql.prepareStatement("UPDATE Student SEt FacultyAdvisor = ? WHERE StudentId = ?");
                    pst.setString(1, Advisor);
                    pst.setInt(2, id);
                    pst.executeUpdate();
                }
            }
        }

        if(input == 3)
        {
            System.out.println("Enter the student's ID");
            int Studentid = scan.nextInt();
            pst = mysql.prepareStatement("DELETE FROM Student WHERE StudentId = ?");
            pst.setInt(1, Studentid);
            pst.executeUpdate();
        }

        if(input == 4) {
            System.out.println("How would you like to search for students?");
            System.out.println("Type Major to search by the student's Major.");
            System.out.println("Type GPA to search by the student's GPA.");
            System.out.println("Type Advisor to search by the student's Faculty Advisor.");
            String search = scan.next();

            if (search.equals("Major")) {
                System.out.println("Enter the Student's Major");
                String major = scan.next();
                pst = mysql.prepareStatement("SELECT * FROM Student WHERE Major = ?");
                pst.setString(1, major);
                data = pst.executeQuery();
                while (data.next())
                {
                    System.out.println(data.getString("studentId"));
                    System.out.println(data.getString("firstname"));
                    System.out.println(data.getString("lastname"));
                    System.out.println(data.getInt("gpa"));
                    System.out.println(data.getString("major"));
                    System.out.println(data.getString("facultyAdvisor"));
                    System.out.println();
                }
                data.close();
            }

            if (search.equals("GPA")) {
                System.out.println("Enter the Student's GPA");
                int GPA = scan.nextInt();
                pst = mysql.prepareStatement("SELECT * FROM Student WHERE GPA = ?");
                pst.setInt(1, GPA);
                data = pst.executeQuery();
                while (data.next())
                {
                    System.out.println(data.getString("studentId"));
                    System.out.println(data.getString("firstname"));
                    System.out.println(data.getString("lastname"));
                    System.out.println(data.getInt("gpa"));
                    System.out.println(data.getString("major"));
                    System.out.println(data.getString("facultyAdvisor"));
                    System.out.println();
                }
                data.close();
            }

            if (search.equals("Advisor")) {
                System.out.println("Enter the Student's Advisor");
                String advisor = scan.next();
                pst = mysql.prepareStatement("SELECT * FROM Student WHERE FacultyAdvisor = ?");
                pst.setString(1, advisor);
                data = pst.executeQuery();
                while (data.next())
                {
                    System.out.println(data.getString("studentId"));
                    System.out.println(data.getString("firstname"));
                    System.out.println(data.getString("lastname"));
                    System.out.println(data.getInt("gpa"));
                    System.out.println(data.getString("major"));
                    System.out.println(data.getString("facultyAdvisor"));
                    System.out.println();
                }
                data.close();
            }
        }
        mysql.close();
    }
    catch(Exception ex)
    {
        ex.printStackTrace();
    }
}
}

What I need help with is making the section at the top that connects to my MySQL database into it's own method (or something of the sort (if you don't believe that it's really necessary to do so please let me know)). I've looked at other people's code on here (and other websites) as to how to do this, but whenever I try to do something similar to what they've done, I get an insane amount of errors, so that's why I've left it as so.

Also I would like to make each of the sections (if (input == 1) etc..) into their own methods so that I can just call them from the main method. I learned Java many years ago and the research that I've done so far isn't really helping me out much. One last thing, if anyone has any tips on how to make this more "user friendly" (<-- only thing I could think of) then please let me know.

Thank you so much in advance.

P.S. Software I'm using is JetBrains IntelliJ (for the actual coding) and JetBrains DataGrip (for making the table/database and checking if changes occured)

P.P.S. Also everything seems to be working correctly and does as it's supposed to. However in IntelliJ wherever there is a ".prepareStatement("some SQL code")" in the entire code, it highlights it and tells me that "this method may invoke the java.lang.NullPointerException" is this something I should be worried about?

Edit:

So here's what i have now:

public class Main {

private static Connection mysql;
private static PreparedStatement pst;
private static ResultSet data;

private static void method1()
{
    PreparedStatement pst = null;
    pst = mysql.prepareStatement("SELECT * FROM Student;");
    data = pst.executeQuery();
    while (data.next()) {
        System.out.println(data.getString("studentId"));
        System.out.println(data.getString("firstname"));
        System.out.println(data.getString("lastname"));
        System.out.println(data.getInt("gpa"));
        System.out.println(data.getString("major"));
        System.out.println(data.getString("facultyAdvisor"));
        System.out.println();
    }
}

private static void method2()
{
    System.out.println("What would you like to do?");
    System.out.println("Type Add to add a student.");
    System.out.println("Type Update to update a student's information.");
    String answer = scan.next();

    if(answer.equals("Add"))
    {
        System.out.println("Enter the student First Name");
        String firstname = scan.next();
        System.out.println("Enter the student's Last Name");
        String lastname = scan.next();
        System.out.println("Enter the student's GPA");
        int GPA = scan.nextInt();
        System.out.println("Enter the student's Major");
        String Major = scan.next();
        System.out.println("Enter the student's Faculty Advisor");
        String FacultyAdvisor = scan.next();

        pst = mysql.prepareStatement("INSERT INTO Student (FirstName, LastName, GPA, Major, FacultyAdvisor) VALUES (?, ?, ?, ?, ?)");
        pst.setString(1, firstname);
        pst.setString(2, lastname);
        pst.setInt(3, GPA);
        pst.setString(4, Major);
        pst.setString(5, FacultyAdvisor);
        pst.executeUpdate();
    }

    if(answer.equals("Update"))
    {
        System.out.println("Please enter the student's Id");
        int id = scan.nextInt();
        System.out.println("You may only update the Major or the Advisor.  Which would you like to update?");
        System.out.println("Enter Major to update the major.");
        System.out.println("Enter Advisor to update the advisor.");
        Scanner scanner= new Scanner(System.in);
        String result = scanner.next();

        if(result.equals("Major"))
        {
            System.out.println("Please enter the new Major");
            String Major = scanner.next();
            pst = mysql.prepareStatement("UPDATE Student SET Major = ? WHERE StudentId = ?");
            pst.setString(1, Major);
            pst.setInt(2, id);
            pst.executeUpdate();
        }

        if(result.equals("Advisor"))
        {
            System.out.println("Please enter the new Advisor");
            String Advisor = scanner.next();
            pst = mysql.prepareStatement("UPDATE Student SEt FacultyAdvisor = ? WHERE StudentId = ?");
            pst.setString(1, Advisor);
            pst.setInt(2, id);
            pst.executeUpdate();
        }
    }
}

private static void method3()
{
    System.out.println("Enter the student's ID");
    int Studentid = scan.nextInt();
    pst = mysql.prepareStatement("DELETE FROM Student WHERE StudentId = ?");
    pst.setInt(1, Studentid);
    pst.executeUpdate();
}


private static void method4()
{
    System.out.println("How would you like to search for students?");
    System.out.println("Type Major to search by the student's Major.");
    System.out.println("Type GPA to search by the student's GPA.");
    System.out.println("Type Advisor to search by the student's Faculty Advisor.");
    Scanner find = new Scanner(System.in);
    String search = find.next();

    if (search.equals("Major")) {
        System.out.println("Enter the Student's Major");
        String major = find.next();
        pst = mysql.prepareStatement("SELECT * FROM Student WHERE Major = ?");
        pst.setString(1, major);
        data = pst.executeQuery();
        while (data.next())
        {
            System.out.println(data.getString("studentId"));
            System.out.println(data.getString("firstname"));
            System.out.println(data.getString("lastname"));
            System.out.println(data.getInt("gpa"));
            System.out.println(data.getString("major"));
            System.out.println(data.getString("facultyAdvisor"));
            System.out.println();
        }
        data.close();
    }

    if (search.equals("GPA")) {
        System.out.println("Enter the Student's GPA");
        int GPA = find.nextInt();
        pst = mysql.prepareStatement("SELECT * FROM Student WHERE GPA = ?");
        pst.setInt(1, GPA);
        data = pst.executeQuery();
        while (data.next())
        {
            System.out.println(data.getString("studentId"));
            System.out.println(data.getString("firstname"));
            System.out.println(data.getString("lastname"));
            System.out.println(data.getInt("gpa"));
            System.out.println(data.getString("major"));
            System.out.println(data.getString("facultyAdvisor"));
            System.out.println();
        }
        data.close();
    }

    if (search.equals("Advisor")) {
        System.out.println("Enter the Student's Advisor");
        String advisor = find.next();
        pst = mysql.prepareStatement("SELECT * FROM Student WHERE FacultyAdvisor = ?");
        pst.setString(1, advisor);
        data = pst.executeQuery();
        while (data.next())
        {
            System.out.println(data.getString("studentId"));
            System.out.println(data.getString("firstname"));
            System.out.println(data.getString("lastname"));
            System.out.println(data.getInt("gpa"));
            System.out.println(data.getString("major"));
            System.out.println(data.getString("facultyAdvisor"));
            System.out.println();
        }
        data.close();
    }
}

public static void main(String[] args) {
    connect();

    try
    {
        System.out.println("What would you like to do?");
        System.out.println("Type 1 to Display all students.");
        System.out.println("Type 2 to Add/Update students.");
        System.out.println("Type 3 to Delete students.");
        System.out.println("Type 4 to Search for a student.");

        Scanner scan = new Scanner(System.in);
        int input = scan.nextInt();

        switch (input)
        {
            case 1:
                method1();
                break;
            case 2:
                method2();
                break;
            case 3:
                method3();
                break;
            case 4:
                method4();
                break;
            default:
                System.out.println("You chose incorrectly and this program will now terminate.");
        }
    }
    catch(Exception ex)
    {
        ex.printStackTrace();
    }
}

private static void connect()
{
    Class.forName("com.mysql.jdbc.Driver");
    String connectionStringURL = "jdbc:mysql://us-cdbr-azure-west-b.cleardb.com:3306/acsm_0a00c1270f36f77";
    mysql = DriverManager.getConnection(connectionStringURL, "b448ce74b4a1f1", "595da2aa");

    if(mysql == null)
        System.out.println("Connection Failed");
    else
        System.out.println("Success");
}

}

Everywhere it says "mysql.preparestatement("//...")" or "data.//..." or "DriverManager.getConnection(//...)" they all give me the same error : Unhandled exception: java.sql.SQLException.

Where it says "Class.forname(//...)" it says : Unhandled exception: java.lang.ClassNotFoundException. How do i fix these?

Tokazio · Accepted Answer · 2016-09-26 08:15:57Z

1

in a first step, you could use switch statement to handle menu inputs. create methods for connexion and for each input cases. add field to hold mysql connexion...

like

switch(input){
    case 1:
        method1();
        break;
}

the method

private static void method1(){
    //move code from input 1 here
}

then you need a field to hold the connexion

 private static Connection mysql;

Edit:

public class Main {

    private static Connection mysql;

    public static void main(String[] args) {
        connect();
        //...
    }

    private static void connect(){
            Class.forName("com.mysql.jdbc.Driver");
            String connectionStringURL = "jdbc:mysql://us-cdbr-azure-west-b.cleardb.com:3306/database";
            mysql = DriverManager.getConnection(connectionStringURL, "username", "password");
        if(mysql == null){
            System.out.println("Connection Failed");
        }else{
            System.out.println("Success");
        }
    }

//...

}

edited Sep 26, 2016 at 8:15

answered Sep 25, 2016 at 23:52

Tokazio

5302 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marc Karam Over a year ago

is the connection field within the method? and if so do i need to put it in all the methods?

Tokazio Over a year ago

sorry, i mean a 'class' field. Then you can use it everywhere in the class, after you init it with a connection method for example. See Edit.

Collectives™ on Stack Overflow

How do I "simplify" my Java code for a MySQL database program?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related