JDBC Objects

Meet the three objects that query MySQL from Java: a BasicDataSource to connect, a JdbcTemplate to run SQL, and a SqlRowSet to read the rows that come back.

Journeyman · Java · SQL · Updated

By

JDBC (Java Database Connectivity) is the Java library that contains the objects used to interact with databases.

Datasource

A datasource object holds the url, username and password: all the information needed to connect to the database.

BasicDataSource dataSource = new BasicDataSource();

dataSource.setUrl("jdbc:mysql://localhost:3306/northwind");
dataSource.setUsername("root");
dataSource.setPassword("your-root-password"); // the root password you chose when you installed MySQL

The url is the address to the database and consists of 5 parts

jdbc:mysql://localhost:3306/northwind
  • jdbc: the protocol being used to connect to the database
  • mysql: the database management system (DBMS)
  • localhost: the address of the server where the DBMS lives
  • 3306: the port on which the DBMS is listening for requests
  • northwind: the name of the database

JdbcTemplate

The JdbcTemplate uses a DataSource object to execute SQL queries on a database. It opens and closes a connection to the database as needed.

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

String query = """
        INSERT INTO products (product_name, unit_price)
        VALUES ('Milk Chocolate', 2.99);
        """;

jdbcTemplate.update(query);

There are 2 ways to execute a query using the JdbcTemplate:

  • jdbcTemplate.update(sql) runs a query that does not return rows. Use it for INSERT, UPDATE and DELETE statements.
  • jdbcTemplate.queryForRowSet(sql) runs a query that returns rows. Use it for a SELECT statement.

SqlRowSet

The SqlRowSet is the object that can read the stream of data that is returned from a query. The JdbcTemplate returns a SqlRowSet when the queryForRowSet() method is called.

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

String query = """
        SELECT product_id
            , product_name
            , unit_price
        FROM products;
        """;

SqlRowSet row = jdbcTemplate.queryForRowSet(query);

while(row.next())
{
    int productid = row.getInt("product_id");
    String productName = row.getString("product_name");
    double unitPrice = row.getDouble("unit_price");

    System.out.println("Id:    " + productid);
    System.out.println("Name:  " + productName);
    System.out.println("Price: quot; + unitPrice);
}

A SqlRowSet gives you access to the data set returned by the database, but you can only read one row at a time.

The row.next() method moves you to the next row in the data set. If the end of the data set is reached, it returns false, and the loop ends.

Query results for product_id, product_name and unit_price with the first row, Chai at 18.00, outlined: a SqlRowSet reads one row at a time

Do it: list the products for the budget shelf

The store's budget shelf shows every product that costs less than $10.00. The rows come from this query:

SELECT product_id
    , product_name
    , unit_price
FROM products;

Each row has three columns:

  • product_id: a whole number, read with getInt.
  • product_name: text, read with getString.
  • unit_price: a price such as 18.0, read with getDouble.

The practice box can't reach MySQL, so the bottom of Main.java has a stand-in for SqlRowSet, named StandInRowSet. Its rows are written in code, but its next(), getInt, getString and getDouble work as SqlRowSet's do, by column name. Leave it as it is. The tests make one like this:

StandInRowSet row = new StandInRowSet(
    new Object[] { 1, "Chai", 18.0 },
    new Object[] { 13, "Konbu", 6.0 },
    new Object[] { 3, "Aniseed Syrup", 10.0 },
    new Object[] { 33, "Geitost", 2.5 });
Row product_name unit_price Less than 10.0?
1st Chai 18.0 no
2nd Konbu 6.0 yes
3rd Aniseed Syrup 10.0 no: exactly 10.0 isn't less
4th Geitost 2.5 yes

So budgetShelf(row) returns the list Konbu, Geitost.

Your job: write the method budgetShelf(StandInRowSet row) so it returns an ArrayList<String> of product names.

  • Read the rows one at a time, in order, until there are no more.
  • When a row's unit_price is less than 10.0, add its product_name to the list.
  • A price of exactly 10.0 isn't less, so that product isn't added.
  • When no product is cheap enough, or there are no rows, return an empty list.

The method and the class sit in the file, Main.java, with nothing around them: since Java 25, a small program can be written this way, without the public class and main around it that the lesson's code has.

Practiced live on the lesson page: write the Java right there and check it.

Cheat sheets