I get a lot of questions based on a very common school assignment.
A student is asked to make a desktop GUI app and, as part of that, connect to and work with a locally hosted MySQL database.
In this setup, presumably due to the same set of circumstances that leads to someone showing MySQL as an option for a locally hosted database (W.T.H. right?), people are shown some downright dangerously wrong ways of working with SQL.
This bit of writing is for me to send as a first message next time this comes up.
java.sqljava.sql is the module that contains the classes needed to connect to SQL databases in Java. We also call this API "JDBC", which stands for Java Database Connectivity.
You don't need to do anything special to get access to this, but if you have a module-info.java file in your program you will need to add a requires java.sql; line to it.
Though the mechanisms you use to work with databases come with Java, the code to connect to the specific database you are using will not. This means you need to include a dependency.
For MySQL, you need to have the mysql-connector-j library. You should have been shown how to do this by now, but if not reach out.
Other DBs:
DataSourceThe first thing you want to do is get an object which implements the DataSource interface.
A DataSource is an object that can give you a connection to a database.
The exact way to do this varies from database to database, but for MySQL you need to create a new MysqlDataSource(). This is also the step where you should fill in any authentication info like username and password.
Also, only create one of these at the top of your program and pass it to everything else. Do not create a DataSource every time you want to run a query.
For MySQL this is going to be a MysqlDataSource. For Postgres start with PGSimpleDataSource. For SQLite, SQLiteDataSource.
The exact .set* methods you need to call will be different depending on your db and maybe your deployment situation.
import javax.sql.DataSource;
import com.mysql.cj.jdbc.MysqlDataSource;
class Main {
public static void main(String[] args) {
MysqlDataSource db = new MysqlDataSource();
db.setPort(3306);
db.setUser("username");
db.setPassword("password");
}
}ConnectionOnce you have a DataSource you can call the getConnection method to get an active connection to the database.
import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.Connection;
import javax.sql.DataSource;
class Main {
public static void main(String[] args) {
MysqlDataSource db = new MysqlDataSource();
db.setPort(3306);
db.setUser("username");
db.setPassword("password");
try (Connection conn = db.getConnection()) {
}
}
}You will notice that I put the connection inside a try( ... ) {} thing. This is called a try-with-resources and all it does is make sure to call conn.close() after the block is exited, even if an exception happens. Since you want to generally close a connection when you are done with it, this is the way to go.
The alternative is this, which you might have seen on your teacher's slides and the example code you were given. This hasn't been needed since 2011.
Connection conn = null;
try {
conn = db.getConnection();
// Code that might crash
}
finally {
if (conn != null) {
conn.close();
}
}While you can re-use connections, I have to ask that you do not store any Connection objects in fields. Whenever you need a connection object, get a fresh one from the DataSource. This might sound inefficient, but trust me its better than the alternatives.
PreparedStatementThere are other ways to run queries on your database, but this is the most consistent one.
On a connection object you can call a method named prepareStatement and give it a String containing a SQL Query. This PreparedStatement object also should be set up to automatically close like a Connection.
import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import javax.sql.DataSource;
class Main {
public static void main(String[] args) {
MysqlDataSource db = new MysqlDataSource();
db.setPort(3306);
db.setUser("username");
db.setPassword("password");
try (Connection conn = db.getConnection()) {
try (PreparedStatement stmt = conn.prepareStatement(
"SELECT 1 as number;"
)) {
}
}
}
}ResultSetTo execute a SQL query that will give you results, you call executeQuery on a PreparedStatement.
This gives you an object called a ResultSet. A ResultSet represents a "cursor" over all the rows that came as results from your queries.
It starts before any rows in the query and each time you call next it moves to the next row. Once you are at a particular row, you call various .get* methods to access the data in that row.
import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
class Main {
public static void main(String[] args) {
MysqlDataSource db = new MysqlDataSource();
db.setPort(3306);
db.setUser("username");
db.setPassword("password");
try (Connection conn = db.getConnection()) {
try (PreparedStatement stmt = conn.prepareStatement(
"SELECT 1 as number;"
)) {
ResultSet rs = stmt.executeQuery();
rs.next();
System.out.println(rs.getInt("number"));
}
}
}
}If you select more than one row, you can use the fact that rs.next() returns false when there are no more rows to loop through them all.
import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
class Main {
public static void main(String[] args) {
MysqlDataSource db = new MysqlDataSource();
db.setPort(3306);
db.setUser("username");
db.setPassword("password");
try (Connection conn = db.getConnection()) {
try (PreparedStatement stmt = conn.prepareStatement(
"SELECT name FROM person;"
)) {
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("name"));
}
}
}
}
}And if you are unsure if you will even get one row, you can use that fact in a similar way.
import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
class Main {
public static void main(String[] args) {
MysqlDataSource db = new MysqlDataSource();
db.setPort(3306);
db.setUser("username");
db.setPassword("password");
try (Connection conn = db.getConnection()) {
try (PreparedStatement stmt = conn.prepareStatement(
"SELECT name FROM person WHERE ssn='111111111';"
)) {
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
System.out.println(rs.getString("name"));
}
else {
System.out.println("No matching person");
}
}
}
}
}The queries you want to run will involve data that comes from a user typing stuff into a box. The way to deal with this is not. I repeat not, under any circumstances the following.
"SELECT name FROM person WHERE birthday='" + birthday + "'";This is the root cause of SQL Injection and is generally not something you want to ever do.
The way to include data in a query is to put a ? in the places that data should go, then call various .set* methods to set the data. You pass them the data and then the ? you are replacing. These start counting from 1, which is unique.
import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
class Main {
public static void main(String[] args) {
MysqlDataSource db = new MysqlDataSource();
db.setPort(3306);
db.setUser("username");
db.setPassword("password");
try (Connection conn = db.getConnection()) {
try (PreparedStatement stmt = conn.prepareStatement(
"SELECT name FROM person WHERE birthday=?"
)) {
stmt.setString(1, "9/9/1999");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("name"));
}
}
}
}
}As your queries get bigger, they will probably be going on multiple lines. To do this use three double quotes on either side.
"""
SELECT name FROM person
WHERE birthday=?
"""It's important to know this because your maybe very old curriculum will still have examples like
"SELECT name FROM person \n" +
"WHERE birthday=?"Which can get tedious.
To do something that isn't a query, like inserting rows, you use the .execute() method instead of .executeQuery(). This will not give you a ResultSet object.
import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
class Main {
public static void main(String[] args) {
MysqlDataSource db = new MysqlDataSource();
db.setPort(3306);
db.setUser("username");
db.setPassword("password");
try (Connection conn = db.getConnection()) {
try (PreparedStatement stmt = conn.prepareStatement(
"""
INSERT INTO person(name, status)
VALUES (?, ?)
"""
)) {
stmt.setString(1, "tiny tim");
stmt.setString(2, "not dead");
stmt.execute();
}
}
}
}Getting a fresh connection to the database every time you want to make a query is ultimately inefficient.
Think of getting a connection like making a phone call. You need to dial, it needs to ring, and the other end needs to pick up. That all takes time.
To resolve this we use "Connection Pools." These are DataSource implementations which keep some number of connections always active and re-use them between calls to .getConnection,
The library to use for this is called HikariCP.
import com.mysql.cj.jdbc.MysqlDataSource;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
public class Main {
public static void main(String[] args) {
MysqlDataSource mysql = new MysqlDataSource();
mysql.setPort(3306);
mysql.setUser("username");
mysql.setPassword("password");
HikariConfig config = new HikariConfig();
config.setDataSource(mysql);
HikariDataSource db = new HikariDataSource(config);
try (var conn = db.getConnection()) {
// ...
}
}
}Note that you do not need to pool connections with a database like SQLite. There making a connection isn't like making a phone call, it's like shouting at your cousin in the other room. There's only one cousin and he can hear you.