Monday, 31 July 2017

Java Database Connectivity (JDBC)

The Java JDBC API enables Java applications to connect to relational databases via a standard API, so your Java applications become independent (almost) of the database the application uses.
JDBC standardizes how to connect to a database, how to execute queries 
against it, how to navigate the result of such a query, and how to exeucte 
updates in the database. JDBC does not standardize the SQL sent to the 
database. This may still vary from database to database.

The JDBC API consists of the following core parts:
  • JDBC Drivers
  • Connections
  • Statements
  • Result Sets
There are four basic JDBC use cases around which most JDBC work evolves:
  • Query the database (read data from it).
  • Query the database meta data.
  • Update the database.
  • Perform transactions.

JDBC Drivers:

A JDBC driver is a collection of Java classes that enables you to connect to a 
certain database. For instance, MySQL will have its own JDBC driver. A JDBC 
driver implements a lot of the JDBC interfaces. When your code uses a given 
JDBC driver, it actually just uses the standard JDBC interfaces. The concrete 
JDBC driver used is hidden behind the JDBC interfaces. Thus you can plugin a 
new JDBC driver without your code noticing it.

Of course, the JDBC drivers may vary a little in the features they support.

Connections:

Once a JDBC driver is loaded and initialized, you need to connect to the 
database. You do so by obtaining a Connection to the database via the JDBC 
API and the loaded driver. All communication with the database happens via 
a connection. An application can have more than one connection open to a 
database at a time. This is actually very common.

Statements:

A Statement is what you use to execute queries and updates against the 
database. There are a few different types of statements you can use. Each 
statement corresponds to a single query or update.

ResultSets:

When you perform a query against the database you get back a ResultSet. 
You can then traverse this ResultSet to read the result of the query.

Common JDBC Use Cases:

Query the database:

One of the most common use cases is to read data from a database. Reading 
data from a database is called querying the database.

Update the database:

Another very common JDBC use case is to update the database. Updating the 
database means writing data to it. In other words, adding new records or 
modifying (updating) existing records.

Perform transactions:

Transactions is anoter common use case. A transaction groups multiple 
updates and possibly queries into a single action. Either all of the actions are 
executed, or none of them are.

Database Connectivity:
Before you can read or write data in a database via JDBC, you need to open a 
connection to the database. This text will show you how to do that.

Loading the JDBC Driver:

The first thing you need to do before you can open a database connection is 
to load the JDBC driver for the database. Actually, from Java 6 this is no 
longer necessary, but doing so will not fail. You load the JDBC driver like this:


Class.forName("driverClassName");

Each JDBC driver has a primary driver class that initializes the driver when it 
is loaded. For instance, to load the Mysql driver, you write this:

Class.forName("com.mysql.jdbc.Driver");

You only have to load the driver once. You do not need to load it before every connection opened. Only before the first connection opened.

Opening the Connection:

To open a database connection you use the java.sql.DriverManager class. You 
call its getConnection() method, like this:

Connection connect = DriverManager
                    .getConnection("jdbc:mysql://localhost/feedback?user=sqluser&password=yourpassword");

The url is the url to your database. You should check the documentation for 
your database and JDBC driver to see what the format is for your specific 
database. The url shown above is for a H2Database.

The user and password parameters are the user name and password for your 
database.

Closing the Connection:

Once you are done using the database connection you should close it. This is 
done by calling the Connection.close() method, like this:

connection.close();

Querying a database means searching through its data. You do so be sending QL statements to the database. To do so, you first need a connection which we did above. Once you have an open 
connection, you need to create a Statement object, like this:

Statement statement = connection.createStatement(); 

Once you have created the Statement you can use it to execute SQL queries, like this:

String sql = "select * from employee"; 
 ResultSet result = statement.executeQuery(sql);

When you execute an SQL query you get back a ResultSet. The ResultSet 
contains the result of your SQL query. The result is returned in rows with 
columns of data. You iterate the rows of the ResultSet like this:

while(result.next()) { 
     String name = result.getString("name"); 
     long age = result.getLong ("age"); 
}

The ResultSet.next() method moves to the next row in the ResultSet, if there 
are anymore rows. If there are anymore rows, it returns true. If there were no 
more rows, it will return false.

You need to call next() at least one time before you can read any data. Before
the first next() call the ResultSet is positioned before the first row.

You can get column data for the current row by calling some of the getXXX() 
methods, where XXX is a primitive data type. For instance:

result.getString ("columnName"); 
result.getLong ("columnName"); 
result.getInt ("columnName"); 
result.getDouble ("columnName"); 
result.getBigDecimal("columnName"); etc.

The column name to get the value of is passed as parameter to any of these 
getXXX() method calls.

You can also pass an index of the column instead, like this:

result.getString (1); 
result.getLong (2); 
result.getInt (3); 
result.getDouble (4);
result.getBigDecimal(5); etc.

For that to work you need to know what index a given column has in the 
ResultSet. You can get the index of a given column by calling the 
ResultSet.findColumn() method, like this:

int columnIndex = result.findColumn("columnName");

If iterating large amounts of rows, referencing the columns by their index 
might be faster than by their name.

When you are done iterating the ResultSet you need to close both the 
ResultSet and the Statement object that created it (if you are done with it, 
that is). You do so by calling their close() methods, like this:

result.close(); 
statement.close();

Of course you should call these mehtods inside a finally block to make sure 
that they are called even if an exception occurs during ResultSet iteration. 
Please refer try-with-resources section in post Exception Handling.


Update Database:

In order to update the database you need to use a Statement. But, instead of 
calling the executeQuery()method, you call the executeUpdate() method.

There are two types of updates you can perform on a database:
  • Update record values
  • Delete records
The executeUpdate() method is used for both of these types of updates.

Updating Records:

Here is an update record value example:

Statement statement = connection.createStatement(); 
String sql = "update people set name='John' where id=123"; 
int rowsAffected = statement.executeUpdate(sql); 

The rowsAffected returned by the statement.executeUpdate(sql) call, tells 
how many records in the database were affected by the SQL statement.

Deleting Records:

Here is a delete record example:


Statement statement = connection.createStatement();
String    sql       = "delete from people where id=123";
int rowsAffected    = statement.executeUpdate(sql);

Again, the rowsAffected returned by the statement.executeUpdate(sql) call, 
tells how many records in the database were affected by the SQL statement.

Let's put all together including connecting and querying the database in an 
example below.


import java.sql.*;

public class JavaMysqlDemo {

    public static void main(String[] args) throws ClassNotFoundException {
        String myUrl = "jdbc:mysql://localhost:3306";
        Class.forName("com.mysql.jdbc.Driver");
        // create our mysql database connection and create the java statement
        try (Connection conn = DriverManager.getConnection(myUrl, "root", "password"); Statement st = conn.createStatement()) {

            // our SQL SELECT query.
            // if you only need a few columns, specify them by name instead of using "*"
            String query = "SELECT * FROM sugarcrm.accounts;";

            // execute the query, and get a java resultset
            try (ResultSet rs = st.executeQuery(query)) {

                // iterate through the java resultset
                while (rs.next()) {
                    String id = rs.getString("id");
                    String name = rs.getString("name");
                    String dateEntered = rs.getString("date_entered");
                    Date dateModified = rs.getDate("date_modified");

                    // print the results
                    System.out.format("%s, %s, %s, %s,\n", id, name, dateEntered, dateModified);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}



3 comments:

  1. Thanks for posting such a great article.you done a great job
    selenium Online Training Bangalore

    ReplyDelete
  2. I just stumbled over this page and have to say - wow. The site is really good and kept up to date. Continue like
    MU BCOM TimeTable 2020
    MDSU BCOM TimeTable 2020

    ReplyDelete
  3. Hello, Where is the use Selenium library/ jar files?

    ReplyDelete