Understanding Common SQL statements

Common SQL statements

The commonly used SQL statements are:

1): Select

2): Insert

3): Update

4): Delete

SQL Select statement:

The SELECT statement is used to select data from a table.

Syntax:

Select column_names FROM table_name;

The result from a SQL query is stored in a resultset. The SELECT statement has mainly three clauses.

1). Select

2.) From

3). Where

The Select specifies the table columns that are retrieved. The From clause tells from where the tables has been accessed. The Where clause specifies which tables are used. The Where clause is optional, if not used then all the table rows will be selected.

We can see that we have used semicolon at the end of the select statement. It is used to separate each SQL statement in database Systems which helps us to execute more than one SQL statement in the same call to the Server.

SQL INSERT Statement:

This statement allows you to insert a single or multiple records into the Database. We can specify the name of the column in which we want to insert the data.

Syntax:

Insert into table_name values (value1, value2..);

The Insert statement has mainly three clauses.

1). Insert: It specifies which table column has to be inserted in the table.

2). Into : It tells in which the data will be stored.

3). Values: In this we insert the values we have to insert. We can also specify the columns for which we want to insert data.

The UPDATE Statement:

The Update statement is used to modify the data in the table. Whenever we want to update or delete a row then we use the Update statement.

syntax :

UPDATE table_name Set colunm_name = new_value WHERE column_name = some_name;

The Update statement has mainly three clauses.

1). UPDATE: It specifies which table column has to be updated.

2). Set: It sets the column in which the data has to be updated.

3). Where: It tells which tables are used.

SQL DELETE Statement:

This delete statement is used to delete rows in a table.

Systax:

DELETE FROM table_name WHERE column_name = some_name;

The Delete statement has following clauses.

1). Delete: It specifies which table column has to be deleted.

2). From: It tells from where the Table has been accessed.

3). Where: It tells which tables are used.

Important JDBC Concepts

JDBC Concepts

Transactions:

Whenever a connection is created by using the JDBC, then by default it is in auto- commit mode. This means that SQL statement will be automatically committed immediately after it is executed and it is treated as a transaction. But imagine a situation where you want to execute a batch of statements, either they should commit at on go or they should get failed together. For this we need to disable the auto- commit mode by using the method:con.setAutoCommit(false).
After setting the auto- commit as false, no SQL statement will be committed until we call the con.commit() method. If there arises any problem while committing then the set of statements will be rollback, without committing. Logging: on the server--->logging--->JDBC.By this we can enable JDBC logging and specify a log file name for the JDBC log.

Attributes of Logging:

1) Enable JDBC Logging: It determines whether the server has a JDBC log file.
2) JDBC Log File Name: It is the name of the log file.

Isolation:

The isolation is needed when there are concurrent transactions. Concurrent transactions are transactions are transactions that occurs at the same time. In isolation one transaction does not interfere with another. For setting the isolation level for a JDBC transaction, use theConnection.setTransaction(int level) method By using the snapshot isolation level we can only see the snapshot of the data locked by other transactions when running from inside the transaction with snapshot isolation level.

Some of the transaction level are given below:

1). TRANSACTION_NONE
2). TRANSACTION_READ_UNCOMMITED
3. TRANSACTION_READ_COMMITTED
4. TRANSACTION_REPEATABLE_READ
5. TRANSACTION_SERIALIZABLE

By setting the isolation levels you are having an impact on the performance of the transaction. You can get the existing isolation level with:getTransactionIsolation() method. Concurrency: Database concurrency controls ensure that the transactions occur in an ordered fashion. Concurrency control deals with the issue involved with allowing multiple people simultaneous access to shared entities.

Introduction to java.sql packageThis package provides the APIs for accessing and processing data which is stored in the database especially relational database by using the java programming language. It includes a framework where we different drivers can be installed dynamically to access different databases especially relational databases.

This java.sql package

This java.sql package contains API for the following


1 Making a connection with a database with the help of DriverManager class

a) DriverManager class: It helps to make a connection with the driver.
b) SQLPermission class: It provides a permission when the code is running within a Security Manager, such as an applet. It attempts to set up a logging stream through the DriverManager class.
c) Driver interface : This interface is mainly used by the DriverManager class for registering and connecting drivers based on JDBC technology.
d). DriverPropertyInfo class : This class is generally not used by the general user.

2). Sending SQL Parameters to a database :

a). Statement interface: It is used to send basic SQL statements.
b). PreparedStatement interface: It is used to send prepared statements or derived SQL statements from the Statement object.
c). CallableStatement interface : This interface is used to call database stored procedures.
d). Connection interface : It provides methods for creating statements and managing their connections and properties.
e). Savepoint : It helps to make the savepoints in a transaction.

3). Updating and retrieving the results of a query:

a). ResultSet interface: This object maintains a cursor pointing to its current row of data. The cursor is initially positioned before the first row. The next method of the resultset interface moves the cursor to the next row and it will return false if there are no more rows in the ResultSet object. By default ResultSet object is not updatable and has a cursor that moves forward only.

4.) Providing Standard mappings for SQL types to classes and interfaces in Java Programming language.

a). Array interface: It provides the mapping for SQL Array.
b). Blob interface : It provides the mapping for SQL Blob.
c). Clob interface: It provides the mapping for SQL Clob.
d). Date class: It provides the mapping for SQL Date.
e). Ref interface: It provides the mapping for SQL Ref.
f). Struct interface: It provides the mapping for SQL Struct.
g). Time class: It provides the mapping for SQL Time.
h). Timestamp: It provides the mapping for SQL Timestamp.
i). Types: It provides the mapping for SQL types.

5). Metadata

a). DatabaseMetaData interface: It keeps the data about the data. It provides information about the database.
b). ResultSetMetaData: It gives the information about the columns of a ResultSet object.
c). ParameterMetaData: It gives the information about the parameters to the PreparedStatement commands.

6). Exceptions

a). SQLException: It is thrown by the mehods whenever there is a problem while accessing the data or any other things.
b). SQLWarning: This exception is thrown to indicate the warning.
c). BatchUpdateException: This exception is thrown to indicate that all commands in a batch update are not executed successfully.
d). DataTruncation: It is thrown to indicate that the data may have been truncated.

7). Custom mapping an SQL user- defined type (UDT) to a class in the java programming language.

a). SQLData interface: It gives the mapping of a UDT to an intance of this class.
b). SQLInput interface: It gives the methods for reading UDT attributes from a stream.
c). SQLOutput: It gives the methods for writing UDT attributes back to a stream.

Driver Manager Class

The JDBC Driver Manager

The JDBC Driver Manager is a very important class that defines objects which connect Java applications to a JDBC driver. Usually Driver Manager is the backbone of the JDBC architecture. It's very simple and small that is used to provide a means of managing the different types of JDBC database driver running on an application. The main responsibility of JDBC database driver is to load all the drivers found in the system properly as well as to select the most appropriate driver from opening a connection to a database. The Driver Manager also helps to select the most appropriate driver from the previously loaded drivers when a new open database is connected.

The DriverManager class works between the user and the drivers. The task of the DriverManager class is to keep track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. It even keeps track of the driver login time limits and printing of log and tracing messages. This class is mainly useful for the simple application, the most frequently used method of this class is DriverManager.getConnetion(). We can know by the name of the method that this method establishes a connection to a database.

The DriverManager class maintains the list of the Driver classes. Each driver has to be get registered in the DriverManager class by calling the method DriverManager.registerDriver(). By calling the Class.forName() method the driver class get automatically loaded. The driver is loaded by calling the Class.forName() method. JDBC drivers are designed to tell the DriverManager about themselves automatically when their driver implementation class get loads.

This class has many methods.

Some of the commonly used methods are given below:

1. deregisterDriver(Driver driver) :

It drops the driver from the list of drivers registered in the DriverManager class.

2. registerDriver(Driver driver) :

It registers the driver with the DriverManager class.
3. getConnection(String url) :

It tries to establish the connection to a given database URL.
4. getConnection(String url, Sting user, String password) :

It tries to establish the connection to a given database URL.
5. getConnection(String url, Properties info) :

It tries to establish the connection to a given database URL.

6. getDriver(String url) :

It attempts to locate the driver by the given string.

7. getDrivers() :

It retrieves the enumeration of the drivers which has been registered with the DriverManager class.

UnderstandingData Source

Understanding Data Source

The JDBC API provides the DataSource interface as an alternative to the DriverManager for establishing the connection. A DataSource object is the representation of database or the data source in the Java programming language. DataSouce object is mostly preferred over the DriverManager for establishing a connection to the database.

DataSource object can be thought as a factory for making connections to the particular database that the DataSource instance represents.

DataSource has a set of properties that identify and describe the real world data source that it represents. The properties include information about the location of the database server, the network protocol use to communicate with the server the name of the database and so on.

DataSource object works with JNDI (Java Naming and Directory interface) naming service so application can use the JNDI API to access the DataSource object.

In short we can say that the DataSource interface is implemented to provide three kinds of connections:

1). Basic DataSource class

This class is provided by the driver vendor. It is used for portability and easy maintence.

2). To provide connection pooling.

It is provided by the application server vendor or driver vendor. It works with ConnectionPoolDataSource class provided by a driver vendor. Its advantage is portability, easy maintenence and increased performance.

3). To provide distributed transactions

This class works with an XADataSource class, which is provided by the driver vendor. Its advantages are easy maintenence, portability and ability to participate in distributed transactions.

Understanding Connection Object

Connection Object

A Connection object represents a connection with a database. When we connect to a database by using connection method, we create a Connection Object, which represents the connection to the database. An application may have one or more than one connections with a single database or many connections with the different databases also.

We can use the Connection object for the following things:

1). It creates the Statement, PreparedStatement and CallableStatement objects for executing the SQL statements.

2). It helps us to Commit or roll back a jdbc transactionn.

3). If you want to know about the database or data source to which you are connected then the Connection object gathers information about the database or data source by the use of DatabaseMetaData.

4). It helps us to close the data source. The Connection.isClosed() method returns true only if the Connection.close() has been called. This method is used to close all the connection.

Firstly we need to to establish the connection with the database. This is done by using the method DriverManager.getConnection().

This method takes a string containing a URL. The DriverManager class, attempts to locate a driver that can connect to the database represented by the string URL. Whenever the getConnection() method is called the DriverManager class checks the list of all registered Driver classes that can connect to the database specified in the URL.

Syntax:

String url = "jdbc: odbc: makeConnection";
Connection con = DriverManager.getConnection(url, "userID", "password");

JDBC Steps

JDBC Steps – Basic steps in writing a JDBC Application

This section gives you brief description of JDBC Steps for making connection with the database, executing the query and showing the data to the user. In this application we have connected to the MySQL database and retrieved the employee names from the database.

Here are the JDBC

Steps to be followed while writing JDBC program:

• Loading Driver
• Establishing Connection
• Executing Statements
• Getting Results
• Closing Database Connection

Before explaining you the JDBC Steps for making connection to the database and retrieving the employee from the tables, we will provide you the structure of the database and sample data.

Here is the sql script to create table and populate the table with data:

Table structure for table `employee`

CREATE TABLE `employee` ( `employee_name` varchar(50) NOT NULL, PRIMARY KEY (`employee_name`) );

INSERT INTO `employee` (`employee_name`) VALUES
('Deepak Kumar'),
('Harish Joshi'),
('Rinku roy'),
('Vinod Kumar');

Data inserting in MySQL database table:

mysql> insert into employee values('Deepak Kumar');
mysql> insert into employee values('Harish Joshi');
mysql> insert into employee values('Harish Joshi');
mysql> insert into employee values('Rinku roy');
mysql> insert into employee values('Vinod Kumar');

mysql> select *from employee;

employee_name

Deepak Kumar

Harish Joshi

Rinku roy

Vinod Kumar

Here is the code of java program that retrieves all the employee data from database and displays on the console:

/*Import JDBC core packages.
Following statement imports the java.sql package, which contains the JDBC core API. */

import java.sql.*;
public class RetriveAllEmployees{
public static void main(String[] args) {
System.out.println("Getting All Rows from employee table!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "jdbc";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pass = "root";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db, user, pass);
Statement st = con.createStatement();
ResultSet res = st.executeQuery("SELECT * FROM employee");
System.out.println("Employee Name: " );
while (res.next()) {
String employeeName = res.getString("employee_name");
System.out.println(employeeName );
}
con.close();
}
catch (ClassNotFoundException e){
System.err.println("Could not load JDBC driver");
System.out.println("Exception: " + e);
e.printStackTrace();
}
catch(SQLException ex){
System.err.println("SQLException information");
while(ex!=null) {
System.err.println ("Error msg: " + ex.getMessage());
System.err.println ("SQLSTATE: " + ex.getSQLState());
System.err.println ("Error code: " + ex.getErrorCode());
ex.printStackTrace();
ex = ex.getNextException(); // For drivers that support chained exceptions
}
}
}}

Explanation of JDBC Steps:

Explanation of JDBC

Steps

They are Five Steps to Explanation of JDBC

1. Loading Driver
2. Establishing Connection
3. Executing Statements
4. Getting Results
5. Closing Database Connection


Loading Driver

Loading Database driver is very first step towards making JDBC connectivity with the database. It is necessary to load the JDBC drivers before attempting to connect to the database. The JDBC drivers automatically register themselves with the JDBC system when loaded. Here is the code for loading the JDBC driver:Class.forName(driver).newInstance();


Establishing Connection

In the above step we have loaded the database driver to be used. Now its time to make the connection with the database server. In the Establishing Connection step we will logon to the database with user name and password. Following code we have used to make the connection with the database:con = DriverManager.getConnection(url+db, user, pass);

Executing Statements

In the previous step we established the connection with the database, now its time to execute query against database. You can run any type of query against database to perform database operations. In this example we will select all the rows from employee table. Here is the code that actually execute the statements against database:ResultSet res = st.executeQuery( "SELECT * FROM employee" );

Getting Results

In this step we receives the result of execute statement. In this case we will fetch the employees records from the recordset object and show on the console.

Here is the code:

while (res.next())

{
String employeeName = res.getInt( " employee_name " );

System.out.println( employeeName );

}

Closing Database Connection

Finally it is necessary to disconnect from the database and release resources being used. If you don’t close the connection then in the production environment your application will fail due to hanging database connections. Here is the code for disconnecting the application from database:

con.close();

In this section you learnt about the JDBC Steps necessary for performing database operations.

Output

C:\vinod>javac Employee.java
C:\vinod>java Employee

Getting All Rows from employee table!

Employee Name:

Deepak Kumar

Harish

JoshiRinku roy

Vinod Kumar

BDK Customizers

BDK Customizers

The OurButtonCustomizer serves as an example that demonstrates the mechanics of building a customizer.

OurButtonCustomizer:

Extends java.awt.Panel (a Component subclass).
Implements the Customizer interface, and uses a PropertyChangeSupport object to manage PropertyChangeListener registration and notification.

See the bound property section for a PropertyChangeSupport description.
Implements a default constructor:
public OurButtonCustomizer()
{
setLayout(null);
}

Is associated with its target class, ExplicitButton, by the following ExplicitButtonBeanInfo code: public BeanDescriptor getBeanDescriptor()
{
return new BeanDescriptor(beanClass, customizerClass);
}
private final static Class customizerClass = OurButtonCustomizer.class;