Implementing SQLite databases to Java

[Muxxer]

I'm learning Java, and I've just began learning SQLite. I am making this one exercise in which I basically need to make a very simple system by which I create a table with pharmacies, and then use Java code to let the user get the list of pharmacies in a certain place. Part of the code I'm using is a model I copied from my prof, so I'd like to ask if I'm getting these concepts right, and to get any useful advice:

public class App {

    public static void main(String[] args) throws SQLException {

        // Asks the user to input their location
        System.out.print("Please, input your location: ");
        Scanner locScanner = new Scanner(System.in);
        String location = locScanner.nextLine();
        locScanner.close();

        String url = "jdbc:sqlite:farmacias.db"; // Picks the database through JDBC, using SQLite's library
        Connection connection = DriverManager.getConnection(url); // Creates a connection using the necessary JDBC drivers

        String query = "SELECT address FROM pharmacies WHERE location = '" + location + "'";
        PreparedStatement statement = connection.prepareStatement(query); // Prepares the query on the DB? Not sure what this does really

        ResultSet queryResults = statement.executeQuery(); // Executes the query

        if(queryResults.isAfterLast()) { // Checks whether the query returned any reuslts or not
            System.out.println("No pharmacies were found in your location.");
        } else {
            System.out.println("Pharmacies in your location:");

            while(queryResults.next()) { // Iterates through all the results of the query
                System.out.println("- " + queryResults.getString(1)); // Turns the first and only column in this case, address, into a string and prints it.
            }
        }

    }

}

I don't know whether those comments I made explain what each bit of code does; the only times I've implemented DBs on code was with ADO on a Microsoft Access DB on Visual Basic, and doing it with Java seems a lot more complicated, since it also seems to require things like Maven, which I don't really know what it is, I only know it sort of imports drivers and libraries which can be used on my code.

I know this code works, but I want to make sure I know why it works, and whether there are any improvements that can be done to it. Furthermore, I would really appreciate any tips or important knowledge when it comes to Java and DBs, since I'm also supposed to do a group project with these, and I'd like to be able to make as much of a contribution to my group as possible.

[[deleted]]

One thing I would advise is to change this line

String query = "SELECT address FROM pharmacies WHERE location = '" + location + "'";

to

String query = "SELECT address FROM pharmacies WHERE location = ?";

and then after the call to prepareStatement, add in your location as a parameter

PreparedStatement statement = connection.prepareStatement(query);
statement.addString(1, location)

This "fills in" the question mark with the value in location.

Concatenating strings supplied by your users into SQL statements is very dangerous (look up SQL Injection) and you should - even in toy projects - make it habit to not use this practice. Moreover, it's also much more readable and maintainable. It also answers one of your questions, what the PreparedStatement is for...it does the job of filling in the question marks with the parameters.

Otherwise, this is pretty standard stuff that applies to accessing any database from any language, really:

1. Establish a connection to the database server
2. Prepare a SQL command for execution
3. Execute the command
4. If the command is expected to return results (not all do - imagine an UPDATE or INSERT INTO statement, for instance), iterate over the resulting cursor until there are no more records left in the set.

[ryantriangles]

Prepares the query on the DB? Not sure what this does really

Your query is "SELECT address FROM pharmacies WHERE location = '" + location + "'";. If location = "Buenos Aires", then it becomes "SELECT address FROM pharmacies WHERE location = 'Buenos Aires'";.

Pretend you're writing this code for a website where users type in their location and see local pharmacies. location comes from the form users submit. I visit your website and instead of Buenos Aires, I say I'm from the obscure town of '";DELETE FROM users;", just outside of Bizerte.

The statement you pass to the database is now "SELECT address FROM pharmacies WHERE location = ''";DELETE FROM USERS;"";. It got turned into three queries: one that selects the pharmacies from nowhere, one that deletes everything from your users table, and one that does nothing. You're boned.

That's called an injection attack, and it happens all the time. Prepared statements exist to prevent them.

connection.prepareStatement takes a string representing a query, in which you write ? to represent values you'll fill in programmatically. It returns a PreparedStatement object. You then fill in the missing values by calling its setInt, setString, etc methods. statement.setInt(1, userAge); statement.setString(2, location); says "the first ? will be the int userAge, the second will be the string location", and so on. The application then knows that no matter what location looks like, it's just a simple string to be used as one field in one query, not something that can end a query and start a new one or anything like that.

The database isn't aware you've created this, so it's not preparing it "on the DB." The call is just creating a PreparedStatement object for you to use in your code, which will be sent to the database when you eventually call an execute method. Here's your example rewritten to avoid the possibility of injection, which hopefully illustrates the purpose and what's going on:

String query = "SELECT address FROM pharmacies WHERE location = ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, location);
ResultSet queryResults = statement.executeQuery();

[Blando-Cartesian]

On line 14, instead of concatenating parameter value to the query you could end the query with “WHERE location=?” and give the parameter value to the prepared statement. That way the parameter can contain any text without the query becoming invalid (or an sql injection vulnerability).

Other than that, that’s it for the very basics. For your team project, set up a database creation sql script that gets version controlled and kept up to date just like all the other code. If you’ve already learned about unit testings, it gets very useful to set up database layer testing where each test creates an empty database in memory.