Introduction to databases and SQL
Enterprise (business organization) data is stored in a database. The database is the heart of an enterprise system. A database is the collection of related data and the relationships supporting the data. Database Management System (DBMS) is software that is used to access data in the database. The primary objective of a DBMS is to provide a convenient environment to retrieve and store database information. SQL (Structured Query Language) is used to communicate with the DBMS to deal with database data. IBM developed this language in 1970s. SQL comprises of the following important sub languages.
- Data Definition Language (DDL): - It is used to create, alter and drop tables.
- Data Manipulation Language (DML): - It is used to INSERT, UPDATE and DELETE data.
- Data Query Language (DQL): - It is used to retrieve data from the from the database table using SELECT.
In a Relational Database Management System, for example in Oracle, data is stored in the form of tables. Each table is made up of one or more rows. Each row contains a number of fields, corresponding to the columns or attributes of the table. Customers’ information, Employees’ information, Students’ information is represented in the form of corresponding tables.
Student table
ROLLNO / NAME / MARKS1001 / Rama / 70
1002 / David / 60
1003 / Rahim / 65
In the above table, ROLLNO, NAME and MARKS are the columns of the table, which are the attributes of each student. Each row in the table is known as a record. Each record represents one student information. Before we create such a table and store data in it, we need to know few SQL data types. While creating a table we need to specify the column type. In the above table, ROLLNO type is NUMBER. NAME is VARCHAR and MARKS also NUMBER type.
Creating a table
In order to create a table in the database, we follow the given syntax.
CREATE TABLE TABLENAME(COLUMN1 TYPE(SIZE), COLUMN2 TYPE(SIZE));
SQL>create table student(rollno number(5),name varchar(12),marks number(3));
The above command at the SQL prompt creates a student table with three columns. rollno column is of type number that can have a maximum of 5 digits. name column is of type varchar that can store a string of length 12. marks column is of type number that can store a number with a maximum of 3 digits.
Q) Create a table EMPLOYEE with the following columns. EMPNO, NAME and SALARY.
A) We have to give the following command at the SQL prompt to create the table.
create table employee(empno number(5),name varchar(12),salary number(7,2));
Note:- In the above command, for the salary column number(7,2) means, it can store a maximum of 7 digit decimal number. After decimal point, 2 digits can occur.
Inserting data into the database table
In order to insert records into the table, we use the following SQL syntax.
INSERT INTO TABLENAME VALUES(value1,value2,value3);
Note: - For inserting strings into the table we enclose them with single quotes.
Q) Write SQL command to store employee information into EMPLOYEE TABLE.
A) INSERT INTO EMPLOYEE VALUES(1001,’Rama’,5000);
EMPNO / NAME / SALARY1001 / Rama / 5000
Note: - If we submit the above command at the SQL prompt, one employee record is stored into employee table. But it will not become permanent. To make the storage permanent we have to say
SQL>commit;
Deleting data from the table
In order to delete records from the table we use the DELETE command.
Syntax: - SQL> DELETE FROM TABLENAME;
If we use the above syntax, all the records will be deleted from the table. In order to delete specified record(s), we have to specify the criteria using WHERE clause.
Q) Write an SQL command to delete all the records from the employee table.
A) DELETE FROM EMPLOYEE;
Note: - With the above command all the records are deleted temporarily. To make the deletion permanent, we have to say SQL>commit;
Q) Write an SQL command to delete all the records from the employee table whose salary is greater than 5000.
A) SQL>DELETE FROM EMPLOYEE WHERE SALARY>5000;
Updating table data
We use UPDATE command to modify column values of the records of the table.
Syntax: - UPDATE TABLENAME SET COLUMNAME=NEW VALUE;
With the above syntax, all the records in the table will be modified. Specified column of each record is set to new value. If we want to modify the column values of specified record(s), we have to use WHERE CLAUSE.
Q) Write an SQL command that increases salaries of all employees by Rs. 2000.
A) SQL>UPDATE EMPLOYEE SET SALARY=SALARY+2000;
Q) Write an SQL command that increases the salary of each employee by Rs. 1000 whose salary is less than Rs. 5000.
A) SQL> UPDATE EMPLOYEE SET SALARY=SALARY+1000 WHERE SALARY<5000;
Retrieving table data
We use SELECT command to retrieve data from the database table.
Syntax: - SELECT COLUM1, COLUMN2 FROM TABLENAME;
The above syntax retrieves 2 columns of all the records of the table. If we want all the columns of each record, we use the following syntax.
SELECT * FROM TABLENAME;
Here star indicates all the column values. If we want to retrieve all the columns of specified records we have to use WHERE clause to specify the criteria.
SELECT * FROM TABLENAME WHERE CRITERIA;
Q) Write an SQL command to retrieve names and salaries of all the employees.
A) SQL> SELECT NAME, SALARY FROM EMPLOYEE;
Q) Write an SQL command to display all the details of each employee.
A) SQL> SELECT * FROM EMPLOYEE;
Q) Write an SQL command that displays employee number and salary of all the employees whose salary is more than 5000.
A) SQL>SELECT EMPNO, SALARY FROM EMPLOYEE WHERE SALARY>5000;
Note: - Database programmers perform so many operations on the table data than what we have discussed here. Application developers, i.e. java programmers, .NET professionals etc. mostly perform DML and DQL operations only. It is strongly recommended for application developers to master SQL and PL/SQL programming skills.
Database programming using JDBC
Even though Java has many features, it became famous and widely accepted in the industry because of its platform independency. The acceptance does not become hundred percent complete unless business community endorses java. Every enterprise (business) application is required to access the database either for retrieving the data to be processed or for storing the processed data. Java offers a clean and simple approach for database connectivity through JDBC using which a java application can connect virtually to any database. Therefore enterprises endorsed java and rest is the history.
What is JDBC?
JDBC is a trademark name from Sun. It is not an acronym for Java Database Connectivity. JDBC is an API. Using this API we can develop java applications that can communicate with databases. JDBC API support is made available to Java applications through 2 packages.
- java.sql
- javax.sql
java.sql package offers a set of fundamental interfaces and classes used for java-database communication. Extension package is used for advanced Java-database operations.
JDBC ARCHITECTURE
Java Application: - It can be a stand-alone java program, a servlet, a jsp, a Java bean or an EJB, which uses the JDBC API to get connected and perform operations on the database data.
JDBC API: -It is a set of classes and interfaces used in a java program for database operations. java.sql & javax.sql packages provide the necessary library support.
DriverManager: - Java program uses DriverManager to get the connection with the database.
Driver: - It is the software that establishes connection with the database. It is the translation software that translates the JDBC method calls. This software enables the communication between java program and the database.
Database: - Enterprise data is stored in the database.
Standard steps to connect to the database from a Java program
- Loading the driver
- Preparing the connection string
- Requesting for the connection
- After performing database operations closing the connection.
Loading the Driver: - Important entity in the Java-Database communication is the driver. JDBC driver is a class. We have to load this Java class programmatically into memory. We have so many kinds of drivers. Initially we make use of Sun Microsystems developed driver. Fully qualified name of that driver class is “sun.jdbc.odbc.JdbcOdbcDriver”. To load the class dynamically, we use the following static method. Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Preparing the connection string: - We need to supply the database URL for connectivity purpose. This URL is known as connection string. When we are using Sun driver, connection string will be in the following format.
String cs=”jdbc:odbc:dsn”;
Data Source Name (DSN) we have to configure externally. After configuring, we give some name to the configuration. That name we use in the connection string.
Requesting for connection: - Java program calls the following method on the DriverManager to get the database connection.
Connection con=DriverManager.getConnection(cs,”username”,”password”);
DriverManager initializes the connection process on the driver. Driver creates the connection and gives to the DriverManager. In turn, it gives to the Java program.
Closing the connection: - After performing database operations (Insertion, deletion, updation or selection) we close the database connection.
Task: - stand-alone Java application that connects to the oracle database.
/*
Source code: -ConnectToOracle.java
*/
import java.sql.*;
class ConnectToOracle
{
public static void main(String args[]) throws Exception
{
String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
String cs=”jdbc:odbc:student”;
String user=”scott”;
String pwd=”tiger”;
Class.forName(driver);
System.out.println(“Driver loaded”);
Connection con=DriverManager.getConnection(cs,user,pwd);
System.out.println(“Connected to the oracle database”);
con.close();
}//main()
}//class
If the above program is successfully executed, we get the following output.
Driver loaded
Connected to the oracle database
Observations to be made
- In the connection string, “student” is the DSN. We need to configure it using Data Source Administrator.
- The user name “scott” and password “tiger” is the default user name and password to login to oracle database. The database administrators change them. Accordingly we have to change them in our program.
- forName method throws ClassNotFoundException. getConnection and close methods throw SQLException. Either we have to handle them explicitly by writing try catch OR pass on them by using throws class for the main. Otherwise, compiler generates syntactical errors and forces us to deal with exceptions.
Performing Database operations
java.sql.Statement is used to perform database operations from the Java program. Its object is produced as follows.
Statement st=con.createStatement();
“st” is the JDBC object used to submit SQL statements from a Java program. It has 2 important methods.
- executeUpdate()
- executeQuery()
The first method is used to submit DML statements to the database. I.e. when we want to insert a record, delete records or update records of a database table from a Java program we have to use executeUpdate method of the Statement object. The second method is used to submit SELECT statement to the database to retrieve data from the table. Both methods throw SQLException.
Task: - Java application inserting a record into the database table.
// Source code: - InsertRecord.java
import java.sql.*;
class InsertRecord
{
public static void main(String args[]) throws Exception
{
String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
String cs=”jdbc:odbc:student”;
String user=”scott”;
String pwd=”tiger”;
String sqlstmt=”INSERT INTO EMPLOYEE VALUES(1001,’Rama’,6000)”;
Class.forName(driver);
Connection con=DriverManager.getConnection(cs,user,pwd);
Statement st=con.createStatement();
int updatecount=st.executeUpdate(sqlstmt);
System.out.println(updatecount+ “ row created”);
st.close();
con.close();
}//main()
}//class
Observations to be made
- executeUpdate method returns an integer that indicates the number of rows effected in the database. In this program “updatecount” is holding that value.
- Before we close the connection, we need to close the Statement object.
Task: - Java application deleting multiple records from the database table.
/*
Source code: - DeleteRecords.java
*/
import java.sql.*;
class DeleteRecords
{
public static void main(String args[]) throws Exception
{
String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
String cs=”jdbc:odbc:student”;
String user=”scott”;
String pwd=”tiger”;
String sqlstmt=”DELETE FROM EMPLOYEE WHERE SALARY>5000”;
Class.forName(driver);
System.out.println(“driver loaded”);
Connection con=DriverManager.getConnection(cs,user,pwd);
System.out.println(“Java program connected to the oracle database”);
Statement st=con.createStatement();
int updatecount=st.executeUpdate(sqlstmt);
System.out.println(updatecount+ “ rows deleted”);
st.close();
con.close();
}//main()
}//class
If the above program is successfully executed, we get the following output.
driver loaded
Java program connected to the oracle database
n rows deleted
Note: - ‘n’ depends upon the number of matching records present in the database table.
Retrieving data from database table
When we submit SELECT statement to the database, it returns a table of records. Using JDBC API we retrieve each column of each row in the returned records in object oriented manner. We submit the SELECT statement to the database as follows. ResultSet rs=st.executeQuery(“SELECT …..”);
java.sql.ResultSet object holds the table of records returned from the database. When the ResultSet is opened, the cursor points to the zero record. Using the ResultSet methods we have to move the cursor to each record and get the column values. Most important method from ResultSet that is used to move the cursor is next method. ResultSet gives getter methods to retrieve the column values.
Task: - Java application that retrieves a single record from the table.
//Source code: - RetrieveRecord.java
import java.sql.*;
class RetrieveRecord
{
public static void main(String args[]) throws Exception
{
String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
String cs=”jdbc:odbc:student”; String user=”scott”; String pwd=”tiger”;
String sqlstmt=”SELECT * FROM EMPLOYEE WHERE EMPNO=1001”;
Class.forName(driver);
Connection con=DriverManager.getConnection(cs,user,pwd);
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(sqlstmt);
if(rs.next())
{
System.out.println(“Employee Name:”+rs.getString(2));
System.out.println(“Employee Salary:”+rs.getFloat(3));
}
else
System.out.println(“Employee does not exist”);
rs.close(); st.close();
con.close();
}//main()
}//class
If the above program successfully executes, name and salary of the employee whose empno is 1001 will be displayed. With that number if no employee exists the same is displayed.
Observations to be made
- next() method moved the cursor from zero record to the first record. It returns true if the record exists. Otherwise, it returns false.
- The kind of get method we have to call depends upon the column type. If the column type is VARCHAR, we called getString method. To this method we supplied the column number as argument. It returns the column value. If the column type is decimal number, we called getFloat method on the ResultSet object.
- getter methods of ResultSet act upon the columns of that row to which the cursor is currently pointing.
- We have to close the ResultSet befoe Statement.
Task: - Java application that retrieves multiple records from the table.
//Source code: - RetrieveRecords.java
import java.sql.*;
class RetrieveRecords
{
public static void main(String args[]) throws Exception
{
String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
String cs=”jdbc:odbc:student”; String user=”scott”; String pwd=”tiger”;
String sqlstmt=”SELECT * FROM EMPLOYEE”; Class.forName(driver);
Connection con=DriverManager.getConnection(cs,user,pwd);
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(sqlstmt);
while(rs.next())
System.out.println(rs.getInt(1)+” “+rs.getString(2)+” “+rs.getFloat(3));
rs.close(); st.close(); con.close();
}//main()
}//class
The above program displays all the records of the employee table. In each iteration of while loop cursor points to the next record and getter methods get column values.
java.sql.PreparedStatement
If we are executing similar SQL statements multiple times, using parameterized (or “prepared”) statements can be more efficient than executing a raw query each time. The idea is to create a parameterized statement in a standard from that is sent to the database for compilation before actually being used. We use a question mark to indicate the places where a value will be substituted into the statement. Each time we use the prepared statement;we simply replace the marked parameters, using a setxxx method call corresponding to the entry we want to set (using 1 based indexing) and the type of parameter. To execute the prepared statement object we call executeUpadate().
The PreparedStatement interface inherits from Statement interface and differs from it in two ways.
- Instances of PrepaedStatement contain an SQL statement that has already been compiled. This is what makes a Statement “prepared”.
2.The SQL statement contained in a PreparedStatement object may have one or more IN parameters. An IN parameter is a parameter whose value is not specified when the SQL statement is created. Instead the statement has a question mark(?) as a placeholder for each IN parameter. A value for each question mark must be supplied by the appropriate setxxx method before the statement is executed. Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement. Being a subclass of Statement, PreparedStatement inherits all the functionality of Statement. In addition, it adds a set of methods that are needed for setting the values to be sent to the database in the place of placeholders for IN parameters. To create the preparedStatement we call the prepareStatement() method on the connection object.