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
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 the3yourself.
Practiced live on the lesson page: write the Java right there and check it.