Your First Query in Java

The shop keeps its data in a database, a file called shop.db. Inside, the data sits in tables. A table is like a list of records that all have the same fields: each row is one item, and each column is one of its fields. The products table has one row per product, with the columns id, name, price, category and stock.

Java opens the file through JDBC: DriverManager.getConnection("jdbc:sqlite:shop.db") gives back a Connection, the line your program talks to the database through. The address names JDBC, then the kind of database, SQLite, then the file. Your questions are written in SQL, as a String. SELECT says which columns you want, and * means all of them; FROM says which table to read. This one reads every column of every customer:

String query = "";
query += "SELECT * ";
query += "FROM customers ";

+= adds each piece at the end of the string, so query holds one string, "SELECT * FROM customers ". That's why each piece ends with a space: it keeps the words apart. Write +=, not =: a plain = would throw away what is already there.

The check runs your query and prints the column names, the rows and how many there are. The gift card has no category, so that value is NULL, SQL's word for "no value", like null in Java. Your code must print:

id | name | price | category | stock
1 | Mug | 12 | kitchen | 10
2 | Notebook | 7 | office | 0
3 | Lamp | 29 | home | 1
4 | Pen | 2 | office | 120
5 | Plant | 15 | home | 6
6 | Teapot | 22 | kitchen | 4
7 | Stapler | 9 | office | 15
8 | Gift card | 50 | NULL | 999
(8 rows)

Your task: open shop.db and keep the connection in a Connection named db. Then add to query the pieces that read every column of the products table.

Assignment
Follow the Coding Tutorial to open the shop's database and write your first query: every column, from the products table.

Hint
The address "jdbc:sqlite:shop.db" is a string, so it goes in quotes. In the query, each piece is its own string ending with a space, added with += on its own line.