JDBC   «Prev  Next»
Lesson 6 SQL overview
ObjectiveExplain what SQL is and its role in database processing.

SQL Database Role in processing Data

SQL has been adopted by nearly every vendor of relational database systems. Because of that, it is an important tool for managing data. JDBC is oriented toward supporting this type of DBMS. JDBC was designed to package and deliver SQL statements to relational database systems.
With SQL, we can write commands to:
  1. Add rows to our tables with the INSERT command
  2. Remove rows from our tables with the DELETE command
  3. Change the data in a column with the UPDATE command
  4. Add new tables with the CREATE command
  5. Choose data from a table or combination of tables with the SELECT command

SQL Commands

While the specific APIs used by database management systems vary, a relational DBMS typically uses SQL commands.
Having a standardized way to talk to a DBMS is a major step forward for developer productivity. Previously, each DBMS had its own unique API for communicating with the outside world, which made things much more difficult. Developers had to learn different sets of commands when they needed to use different databases.
SQL is fairly easy to learn because its commands are very much like statements in English. (In fact, the original name for the language was "Structured English Query Language" with an acronym of SEQUEL.)
Below is an example of a SQL query that finds the cardiac specialists from the list of all the doctors in the in the Brazilian Hospital Project database.


Select from SQL Example

SELECT doctor id 
FROM specialty 
where specialty = 'Cardiology'

SQL keyword is case-insensitive

A SQL keyword is case-insensitive, but commonly they are placed in all uppercase so that you can easily identify them. We will follow that convention in this course. This insensitivity to case does not necessarily carry over to the DBMS-specific parts of a command. The database table and column names may be case sensitive, for example. You will have to consult your database documentation for help determining this.

The basic SQL commands used by Database Management Systems are:
  1. CREATE,
  2. INSERT,
  3. UPDATE,
  4. DELETE, and
  5. SELECT.

SQL Syntax

You will combine SQL with Java programming when you use JDBC. The following tips will help you while you are learning SQL commands.
Though SQL is not case sensitive, use all capital letters to easily locate the SQL commands in your code. The space between words in a SQL statement is not important.
You can use quotation marks to define strings. A single quotation mark (') indicates a string constant. A double quotation mark (") may be used to delimit database entities (such as table columns) that contain spaces. However, check your database documentation because these two characters are sometimes treated differently. If you hear the terms "JDBC SQL type," "SQL type," and "JDBC type," don't be confused. These are all terms that programmers use to refer to the generic SQL type identifiers.
When you begin working with JDBC, read the SQL manual published by your database vendor. The documentation should provide a reference for specific information you may need (Open Database Connectivity) in a solution.

package java.sql
Provides the API for accessing and processing data stored in a data source (usually a relational database) using the JavaTM programming language.
public interface ResultSet extends Wrapper

A table of data representing a database result set, which is usually generated by executing a statement that queries the database.
A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.
A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options.
See the following Slide Show for examples.
1) SQL Commands 1 2) SQL Commands 2 3) SQL Commands 3 4) SQL Commands 4 5) SQL Commands 5

Basic SQL Commands

SQL variations

There are several variations of SQL in use. The most widely used is SQL-92, and it is the specification our course follows. The most recently defined specification is commonly referred to as SQL3. Support for SQL3 features is not universal, and not all databases conform to the syntax or semantics for the advanced functionality. JDBC does support SQL3, but databases are not required to support it.
In the next lesson, you will learn about the design considerations for JDBC and ODBC (Open Database Connectivity) in a solution.