`
Now that we have learned how to set up databases, let’s see how we can work with them using Java. We do this using JDBC, the standard Java API for talking to databases.
Your Java code should not need to know whether it's talking to PostgreSQL, MySQL, or any other SQL database. JDBC achieves this by sitting between your code and the database as a common interface. You write against JDBC; the database-specific driver handles the actual communication underneath.
<aside>
Your Java code ↓ JDBC API ← you write against this ↓ PostgreSQL Driver ← translates JDBC calls to PostgreSQL's wire protocol ↓ PostgreSQL
</aside>
The JDBC API is part of the standard library: these classes live in the java.sql.* package. For specific drivers (PostgreSQL, MySQL, etc) you need to add specific libraries.
Everything in JDBC flows through four objects, always in this order:
DriverManager is the entry point. You give it a connection string, a username, and a password, and it hands you back a Connection.
Connection represents one open session with the database. It is expensive to create, so you should close it when done. From a connection you create statements.
Statement / PreparedStatement is how you send SQL to the database. A plain Statement is for static SQL with no user input. A PreparedStatement is for any SQL that includes variables, like specific columns values. It compiles the query first and plugs in values safely, which is what prevents SQL injection. Some more on that later.
ResultSet is the cursor that holds the rows returned by a query. You call rs.next() to advance row by row, and rs.getString("column_name") (or getInt, getDate, etc.) to read each value.
Set up a new project in your IDE, and add the postgres driver as a dependency to your pom.xml file:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.7.3</version>
</dependency>
Next, add the following to your Main file. Make sure to change the package, and set the correct password if you have any:
package org.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Main {
static void main() throws SQLException {
String url = "jdbc:postgresql://localhost:5432/POSTIFY";
String user = "postgres";
String pass = "secret";
// Write a query for a prepared statement; the ? will be populated later.
String sql = "SELECT artist_id, artist_name, artist_country FROM artists WHERE artist_country = ?";
// A try-with-resources, so that the connection and prepared statement resources will be closed
// after we are done with them.
try (
Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement ps = conn.prepareStatement(sql)
) {
// We want to query all Canadian musicians.
// Set the ? parameter on the prepared statement to "CA".
ps.setString(1, "CA");
// Another try-with-resources for the result set.
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
Integer artistId = rs.getInt("artist_id");
String artistName = rs.getString("artist_name");
String artistCountry = rs.getString("artist_country");
System.out.println(artistId + " | " + artistName + " | " + artistCountry);
}
}
}
}
}
If you’ve got the example working, we can zoom in a little bit to try and understand better what is happening. The first few lines just define some string variables. Afterwards, some more interesting bits happen:
ConnectionWe need to connect to our postgres database, and we need to be authorized to do so. We get the connection by passing the database location (via the url), and we authenticate with the user and password.
A database can only hold a limited amount of connections, so we need to clean up after ourselves: after we are done querying, the connection needs to be closed. That’s why we use try-with-resources.
PreparedStatementWe want to send some SQL to the database, in this case to execute our query. We have a variable in our query for the country name (the ? in the string); the actual value is set via the ps.setString call. If we want to query something else, we can just call ps.setString(1, "CA") with some other country code than “CA”. But why the hassle? Why not just defining a simple string like so:
String myCountry = "NL"
String query = "select artist_id, artist_name, artist_country from artists where artist_country = " + myCountry + ";"
The reason is that the myCountry variable could, potentially, contain ANY string. In this example we know where the string comes from, we are defining it ourselves right before! In actual real-life situations, the value might come from some user input. Or the value was generated by some other bits of code, and you cannot easily predict what kind of values it may hold.
<aside> 🙋♀️
String myCountry = "'CA'; DROP TABLE streams"; ?
</aside>Hackers might abuse such flaws in your system; this is called SQL injection. To protect against string variables with undesirable values like that, the concept of Prepared Statements was introduced to SQL. The variables in prepared statements are always treated as simple values, never as commands to be executed by the database.
Prepared Statements are created and run by the database itself. Creating one is requires some (minimal) resources from the database. Once you are done with a prepared statement, the database should clean its resources up. That’s why prepared statements are also dealt with using a try-with-resources in Java.
As a final benefit, prepared statements can provide some performance boosts when queries are executed very often. Prepared statements are precompiled, compared to regular statements which the database has to compile itself on each query execution. See https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html for more info.
ResultSetThe prepared statement is run by calling ps.executeQuery() on it (again in a try-with-resources!). This returns a ResultSet object. A result set represents the rows returned by the query, but it doesn’t hold all the results at once. Image you’re querying a table with hundreds of gigabytes of rows; loading them all into your Java program at once would crash it!
Instead, you use the result set to move over the rows one-by-one. The result set keeps a “cursor” to remember at which row it currently is. It starts at the first row. Calling next() on the result set moves the cursor forward, and it returns True as long as there are rows to process. Once the cursor is at the end, next() returns False. That’s how it’s used in the while loop.
Calling methods like rs.getInt("artist_id") and rs.getString("artist_name") fetches the columns from the current row in the result set (pointed at by the cursor), and casts the result to Java types, like Integer and String in these cases.
The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*
Built with ❤️ by the HackYourFuture community · Thank you, contributors
Found a mistake or have a suggestion? Let us know in the feedback form.