JDBC - SQL Syntax
Structured Query Language
(SQL) is a standardized language that allows you to perform operations on a
database, such as creating entries, reading content, updating content, and
deleting entries.
SQL is supported by all most any database you will
likely use, and it allows you to write database code independently of the
underlying database.
This tutorial gives an overview of SQL, which is a
pre-requisite to understand JDBC concepts. This tutorial gives you enough SQL
to be able to Create, Read, Update, and Delete
(often referred to as CRUD operations) data from a database.
For a detailed understanding on SQL, you can read our MySQL Tutorial.
Create
Database:
The CREATE DATABASE statement is used for creating a
new database. The syntax is:
SQL> CREATE DATABASE DATABASE_NAME;
Example:
The following SQL statement creates a Database named
EMP:
SQL> CREATE DATABASE EMP;
Drop
Database:
The DROP DATABASE statement is used for deleting an
existing database. The syntax is:
SQL> DROP DATABASE DATABASE_NAME;
Note: To create or drop a database you should
have administrator privilege on your database server. Be careful, deleting a
database would loss all the data stored in database.
Create
Table:
The CREATE TABLE statement is used for creating a new
table. The syntax is:
SQL> CREATE TABLE table_name
(
column_name column_data_type,
column_name column_data_type,
column_name column_data_type
...
);
Example:
The following SQL statement creates a table named
Employees with four columns:
SQL> CREATE TABLE Employees
(
id INT NOT NULL,
age INT NOT NULL,
first VARCHAR(255),
last VARCHAR(255),
PRIMARY KEY ( id )
);
Drop
Table:
The DROP TABLE statement is used for deleting an
existing table. The syntax is:
SQL> DROP TABLE table_name;
Example:
The following SQL statement deletes a table named
Employees:
SQL> DROP TABLE Employees;
INSERT
Data:
The syntax for INSERT looks similar to the following,
where column1, column2, and so on represent the new data to appear in the
respective columns:
SQL> INSERT INTO table_name VALUES (column1, column2, ...);
Example:
The following SQL INSERT statement inserts a new row in
the Employees database created earlier:
SQL> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
SELECT
Data:
The SELECT statement is used to retrieve data from a
database. The syntax for SELECT is:
SQL> SELECT column_name, column_name, ...
FROM table_name
WHERE conditions;
The WHERE clause can use the comparison operators such
as =, !=, <, >, <=,and >=, as well as the BETWEEN and LIKE
operators.
Example:
The following SQL statement selects the age, first and
last columns from the Employees table where id column is 100:
SQL> SELECT first, last, age
FROM Employees
WHERE id = 100;
The following SQL statement selects the age, first and
last columns from the Employees table where first column contains Zara:
SQL> SELECT first, last, age
FROM Employees
WHERE first LIKE '%Zara%';
UPDATE
Data:
The UPDATE statement is used to update data. The syntax
for UPDATE is:
SQL> UPDATE table_name
SET column_name = value, column_name = value, ...
WHERE conditions;
The WHERE clause can use the comparison operators such
as =, !=, <, >, <=,and >=, as well as the BETWEEN and LIKE
operators.
Example:
The following SQL UPDATE statement changes the age
column of the employee whose id is 100:
SQL> UPDATE Employees SET age=20 WHERE id=100;
DELETE
Data:
The DELETE statement is used to delete data from
tables. The syntax for DELETE is:
SQL> DELETE FROM table_name WHERE conditions;
The WHERE clause can use the comparison operators such
as =, !=, <, >, <=,and >=, as well as the BETWEEN and LIKE
operators.
Example:
The following SQL DELETE statement delete the record of
the employee whose id is 100:
SQL> DELETE FROM Employees WHERE id=100;
JDBC - Sample, Example Code
This tutorial provides an example of how to create a
simple JDBC application. This will show you how to open a database connection,
execute a SQL query, and display the results.
All the steps mentioned in this template example, would
be explained in subsequent chapters of this tutorial.
Creating
JDBC Application:
There are following six steps involved in building a
JDBC application:
·
Import the packages .
Requires that you include the packages containing the JDBC classes needed for
database programming. Most often, using import java.sql.* will suffice.
·
Register the JDBC driver .
Requires that you initialize a driver so you can open a communications channel
with the database.
·
Open a connection .
Requires using the DriverManager.getConnection() method to create a
Connection object, which represents a physical connection with the database.
·
Execute a query .
Requires using an object of type Statement for building and submitting an SQL
statement to the database.
·
Extract data from result set .
Requires that you use the appropriate ResultSet.getXXX() method to
retrieve the data from the result set.
·
Clean up the environment .
Requires explicitly closing all database resources versus relying on the JVM's
garbage collection.
Sample
Code:
This sample example can serve as a template when
you need to create your own JDBC application in the future.
This sample code has been written based on the
environment and database setup done in previous chapter.
Copy and past following example in FirstExample.java,
compile and run as follows:
//STEP 1. Import required packages
import java.sql.*;
public class FirstExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/EMP";
// Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
//STEP 4: Execute a query
System.out.println("Creating statement...");
stmt = conn.createStatement();
String sql;
sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
//STEP 6: Clean-up environment
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
}//end FirstExample
Now let us compile above example as follows:
C:\>javac FirstExample.java
C:\>
When you run FirstExample, it produces following
result:
C:\>java FirstExample
Connecting to database...
Creating statement...
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:\>
Tags:
Advanced Java