This article gives an introduction to the how-tos of connecting java source code to a database.

JDBC

Java Database Connectivity (JDBC) is the Java API that provides the interface/means of connecting java source code to a relational database such as MySQL. The API contains various classes and methods that can be used to achieve the interaction between code and database in terms of handling SQL commands and data received. The two major uses of JDBC includes:

• to connect java src code to a database and execute SQL statements

• to handle results/data received from the database.

The following steps describe how to connect java src code to an SQLite database and execute SQL statements within the java code.

Dependency

Update the application dependency to include the SQL library. For example, if building with Gradle, add the statement below to the build.gradle file dependencies. This allows Gradle to import the necessary JDBC libraries to your application.

dependencies {
        compile group:'org.xerial', name:'sqlite-jdbc', version:'3.8.11.2'
    }

Data Source

In the java Source Code create an object of the SQL data source and set the database location by calling the setURL method and passing the database URL as an argument to the method. This URL defines the database location, this can be an absolute or a relative URL.

import org.sqlite.SQLiteDataSource; 

SQLiteDataSource dataSource = new SQLiteDataSource(); 
dataSource.setUrl("url to database");

Connection

Create a connection object using the data source object and the get connection method of the connection class this method returns a connection object through which interactions can be made to the database.

import java.sql.Connection;

try (Connection connection = dataSource.getConnection()) {

 } catch (SQLException e) {
     e.printStackTrace();
 }

Note that with connecting to the database, it is important to close the connection when not in use to prevent a data leak. This can be done by calling the close() on the connection object, or as done above by using the try-with-resource.

Statement

Create a statement object using the connection object and the create statement method of the statement class. This statement class contains various methods that allow SQL commands to be passed as string arguments within the source code the execute method returns true if the database returns any form of data or false if no data is returned from the database. Another method in this class is the execute update method it works just like the execute method except that it returns an integer value specifying the number of rows affected by the SQL command that was passed as a string.

String createTable = "CREATE TABLE IF NOT EXISTS card(" +
                "    id INTEGER AUTO_INCREMENT," +
                "    number TEXT," +
                "    pin TEXT," +
                "    balance INTEGER DEFAULT 0" +
                ");";

        try (Connection connection = dataSource.getConnection()) {
            try (Statement statement = connection.createStatement(createTable)) {
                statement.executeUpdate();

            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

Use executeUpdate() for INSERT, DELETE and UPDATE statements and for commands that return nothing or do not affect rows in the database, such as CREATE or DROP, use the execute().

Prepared Statements

The Prepared statement class allows for dynamic formatting of the SQL commands passed as a string to the connection. In the example below, the setString method is used to dynamically pass the values required in the SQL string command, much like the printf method.

String updateTable = "INSERT INTO card (number, pin) VALUES (?, ?);";
        try (Connection connection = dataSource.getConnection()) {

            try (PreparedStatement preparedSt = connection.prepareStatement(updateTable)) {

                preparedSt.setString(1, "12345678");
                preparedSt.setString(2, "0000");

                int i = preparedSt.executeUpdate();

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }