Full Example

A complete main method that connects to the northwind database, selects every product, and prints each product's id, name and price.

Journeyman · Java · SQL · Updated

By

Here are all three steps together in one program.

public static void main(String[] args)
{
    // 1. Connect to the database
    BasicDataSource dataSource = new BasicDataSource();

    dataSource.setUrl("jdbc:mysql://localhost:3306/northwind");
    dataSource.setUsername("root");
    dataSource.setPassword("your-root-password");

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    // 2. Define and execute the query
    String sql = """
        SELECT product_id
            , product_name
            , unit_price
        FROM products;
        """;

    SqlRowSet row = jdbcTemplate.queryForRowSet(sql);

    // 3. Use the data returned by the database
    while(row.next())
    {
        int id = row.getInt("product_id");
        String name = row.getString("product_name");
        double price = row.getDouble("unit_price");

        System.out.println("Id:    " + id);
        System.out.println("Name:  " + name);
        System.out.println("Price: quot; + price);
        System.out.println();
    }
}

Do it: print the shippers report

The shipping desk wants a short report: every shipper with its phone number, then how many shippers there are. Steps 1 and 2, connecting and running the query, are done for you: the variable row holds what this query gave back.

SELECT shipper_id
    , company_name
    , phone
FROM shippers;

As in JDBC Objects, row is a StandInRowSet, a stand-in for SqlRowSet with its rows written in code, because the practice box can't reach MySQL. Leave it as it is. It holds three rows:

shipper_id company_name phone
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931

The program prints exactly:

Speedy Express: (503) 555-9831
United Package: (503) 555-3199
Federal Shipping: (503) 555-9931
3 shippers

Your job: write step 3 inside main().

  • Read the rows one at a time, in order, until there are no more.
  • For each row, print one line: the company's name, a colon and a space, then its phone.
  • Count the rows as you go. After the last one, print the count, a space and shippers.
  • Read every value from row; don't type the names, phone numbers or the 3 yourself.

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

Cheat sheets