SQL Commands in Java

SQL Commands

In the previous lesson, you created records in your Java DB database table. In this lesson, you'll learn a few SQL commands so that you can manipulate records in a table.

SQL stands for Structured Query Language, and is a way to query databases. You can select records, insert, delete, and update records, create tables, drop tables, and more besides. It's quite a powerful tool.

SQL uses simple keywords to do the work. If you want to select all the records from a table, the words SELECT and FROM are used, along with the "all records" symbol, which is *:

SELECT * FROM table_name

If you look at the top half of the NetBeans window, you'll see that a SELECT statement has already been set up: (NOTE: SQL is not case sensitive)

select * from ADMIN.WORKERS

This says "Select all the records from the table called Workers". (The ADMIN part, before the dot of Workers, is something called a Schema. This describes the structure of the database, but also identifies things like users and the privileges they have. Don't worry about schemas, as we won't be going into them.)

In NetBeans, you run a SQL statement by clicking the Run button on the toolbar:

The results from the SQL statements are then displayed in the bottom half of the window:

The WHERE Clause

To narrow down your search results, you can use a WHERE clause with the SELECT statement:

SELECT * FROM table_name WHERE column_name=value

After the keyword WHERE you need the name of a column from your table. You then type an equals sign, followed by a value. As an example, here's a SQL statement that returns all the programmers in our table:

SELECT * FROM ADMIN.WORKERS WHERE JOB_TITLE='Programmer'

To try this SQL Statement out, right-click your table name in the Services area. From the menu that appears, select Execute Command:

When you click on Execute Command, a new window appears. Type the above SQL Statement, and then click the Run icon:

The results will be displayed in the bottom half of the window:

As you can see, two rows are returned from the query.

You can also use the keyword LIKE with the WHERE clause. This then replaces the equals sign. LIKE is usually used with a wildcard character. The wildcard character % means "any characters", for example, while an underscore is used for just a single character.

Instead of the equals sign or the keyword LIKE, you can also use the conditional operators (Greater Than, Less Than, etc.). If we had a salary column, we could search for all workers who are getting paid more than 1000 a week:

SELECT * FROM ADMIN.WORKERS WHERE SALARY > 1000

Exercise

Try running the following SQL Statement:

SELECT * FROM ADMIN.WORKERS WHERE JOB_TITLE LIKE '%er'

How many results are displayed?

We'll leave SQL Statements there, as we now have enough to start programming. Before doing so, however, close down the connection to your table by right clicking it in the Services are. From the menu, select Disconnect:

We'll connect to this database and table again, using Java code this time