How to fix the IntelliJ java code connecting to mysql database: Error connecting to database: Unknown column 'firstName' in 'where clause'

Question

I have an assignment for a database management systems class that requires me to create an SQL database for students at a university and populate the database with random data. I have to create a Java program that allows users to query the database and I keep getting the error "Error connecting to database: Unknown column 'firstName' in 'where clause'" and I'm not sure how to fix it. This is the java code I am running:

private static void searchByName(Connection conn, Scanner input) throws SQLException {
        System.out.print("Enter search string: ");
        String search = input.nextLine();
        String query = "SELECT * FROM Students WHERE LOWER(firstName) LIKE ? OR LOWER(lastName) LIKE ?";
        try (PreparedStatement stmt = conn.prepareStatement(query)) {
            stmt.setString(1, "%" + search.toLowerCase() + "%");
            stmt.setString(2, "%" + search.toLowerCase() + "%");
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String firstName = rs.getString("firstName");
                    String lastName = rs.getString("lastName");
                    String id = rs.getString("id");
                    System.out.println(firstName + " " + lastName + " (" + id + ")");
                }
            }
        }
    }

The expected output is supposed to show the name of the student that the user inputted in the command line.

hint: the error message is clear - try executing the query outside of java, in the DB console. — Andrew S, Apr 03 '23 at 17:31

duffymo · Answer 1 · 2023-04-03T18:03:37.717

I'd recommend separating the input from the database. Don't put the code to get the firstName value in that method. Get it and pass the value into the method. Same for printing output. Return a List of Student objects. You should not couple I/O with your database code.

I'd also see if your database supports case-insensitive LIKE.

private static final String FIND_BY_NAME = "SELECT id, firstName, lastName FROM Students WHERE firstName LIKE ? OR lastName LIKE ?";


private List<Student> searchByName(String fullName) throws SQLException {
        List<Student> students = new ArrayList<>();
        try (PreparedStatement stmt = this.connection.prepareStatement(FIND_BY_NAME)) {
            stmt.setString(1, fullName.toLowerCase());
            stmt.setString(2, fullName.toLowerCase());
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String firstName = rs.getString("firstName");
                    String lastName = rs.getString("lastName");
                    String id = rs.getString("id");
                    students.add(new Student(id, firstName, lastName);
                }
            }
        }
        return students;
    }

How to fix the IntelliJ java code connecting to mysql database: Error connecting to database: Unknown column 'firstName' in 'where clause'

1 Answers1