JDBC
JDBC is a set of Java classes and interfaces for executing statements SQL
It is the CLI part of Java (Call-Level Interface)
It was jointly developed by JavaSoft, Sybase, Informix and IBM among others
JDBC allows the manipulation of any SQL database
You don't need to do a specific program to manipulate Oracle, Sybase, etc...
Our program can handle any database
Bringing together Java with JDBC, we get programs that can run on any platform, and they can manipulate any database
The classes and interfaces JDBC is located within the package java.sql
Process of working with JDBC
The JDBC worker process consists of the following steps:
-
Connect to the database (using DriverManager)
-
Issue SQL statements (using Statement, PreparedStatement, CallableStatement)
-
Processing the results (ResultSet)
JDBC Driver
Each particular database implements the JDBC interface in a particular way, as do the helper and utility classes that are required for that database
That is why they are necessary drivers of databases
The same application will be able to connect to different databases simply by changing that driver (we should not confuse the database driver with the class Driver, because they are different things)
The interface Driver specifies the methods that any JDBC driver must implement, so drivers can be loaded in two ways:
-
When you start the class Driver, the DriverManager consultation the property jdbc.drivers
This property contains a list of drivers (classes) that must be loaded
To do this, you must run a command like this (to use ODBC) on the command line:
-
If you want to enter a new driver after the DriverManager is initialized we should use the method forname of the class Class
To do this we must include in our code the following statement (to use ODBC):
It is advisable to use of static in our application (because the driver will only load once, when loading the class)
Database | Driver |
---|---|
ODBC | sun.jdbc.odbc.JdbcOdbcDriver |
Oracle | oracle.jdbc.driver.OracleDriver |
SQLServer | com.microsoft.jdbc.sqlserver.SQLServerDriver |
MySQL | com.mysql.jdbcDriver |
Tipos de driver:
- Bridge JDBC-ODBC
Translates JDBC to ODBC and then relays it to the ODBC driver of the machine
Is the ODBC driver actually communicates with the database
Is included in the JDK but does not include JDBC2
Inconvenience:
-
It's useful for testing, but it's slow in production
-
ODBC driver required on client (lower portability)
-
- Driver JDBC on a native driver of the database
Relays JDBC the native driver installed on the machine
The native driver is the one that actually communicates with the database
Drawback:
- need for native driver (lower portability)
- Driver Java on network
Translates JDBC calls into a network protocol independent of the platform that contact with the server
The server translates these requests to the specific protocol of each database
It uses a middleware on the network server that is capable of connecting customers with pure Java to many different databases
Advantage:
-
is fast, platform-independent and requires no installation on the client
-
- Driver pure Java and native protocol
Translates the JDBC calls to the specific protocol of the database by directly contacting it
Get connections
Using the specific driver we will make the connections, but connection requests must be made using the DriverManager
Once the class Driver has been uploaded and registered, the DriverManager you can establish connections to the database by using these two steps:
-
The method is called DriverManager.getConnection(url, user, pass); and you get an object of type Connection
-
The DriverManager tests the registered drivers to see if you can establish the connection and if it wasn't possible, launch a SQLException
A single application can have multiple connections to the same database or multiple connections to other databases (up to the maximum allowed by the database)
The parameters supported by getConnection are the url (which is a subprotocol that uses the database to make the connection), the user (the username to be connected) and the pass (the password that the user uses to connect)
The parameter url has the following format:
The subprotocol is particular to each database, and uses the DriverManager to find the proper driver to handle it
The subname depends on the subprotocol specific
The driver will be responsible to interpret and will help you to locate the database
Our app won't work with the specific driver, but will work on the DriverManager
In this way applications can work with the object Connection without worrying about the type of database we're working with (no code modifications need to be made, just change the parameter url)
The connection should always be closed at the end, because otherwise resources are consumed unnecessarily, although connections can be reused, it is not advisable, it is better to always use a new connection
In the example we have used a skeleton of connection to Oracle
To use the connection we need to create an object of type Connection, which represents an open session with the database
The object provides a context with which to issue SQL statements and get results
The object Connection must be initialized to an initial null, so we can check if there was a connection because we'll include a block try to handle bugs, which will launch SQLException
To close the connection must be made using the method close(), within a finaly (which will also have its block try that spear SQLException)
The interface Statement
The connection allows us to create objects Statement by using the method createStatemen()
The objects Statement allow sql statements to be executed and results obtained (using the objects ResultSet)
The methods to execute SQL accept String (JDBC is CLI), which can be composed dynamically based on values contained in variables
To compose the String SQL, concatenate the different SQL fragments (static and variable)
There are three ways to execute SQL statements:
-
By using the method executeQuery(<sql>) for queries using the statement SELECT that produces tuples as a result (returns an object of type ResultSet)
-
It is like a table that stores the result of the query
-
You have a number of query methods
-
It's like a cursor (in PL/SQL terminology)
-
-
By using the method executeUpdate(<sql>) for updates by using the sentences INSERT, DELETE, UPDATE, as well as DDL commands (CREATE, DROP, ALTER TABLE, ADD) and PL/SQL blocks (between begin and end blocks)
-
Returns an integer that indicates the number of rows modified by the command
-
With DDL commands it returns 0
-
-
By using the method execute(<sql>) that executes any SQL statement
-
If we used a QUERYReturn True
-
The ResultSet can be obtained by the method getResultSet()
-
-
If we used a UPDATEReturn False
-
The total number of modified rows can be obtained by using the method getUpdateCount()
-
-
After processing the result, the Statement by using the method close()
This method will also close the ResultSet associate, however, Sun recommended closing the ResultSet explicitly to avoid errors unwanted
The same Statement can be reused in the same connection to execute different statements
The interface ResultSet
The object ResultSet acts as a cursor within the results
The first time you read it points to the first result, but does not read it, so you have to read the first row moving forward using the method next()
The method next() returns True if you were able to move forward or False if you could not
To obtain the values of the ResultSet methods are used get that have the following format get<type>(<column>)
To name the columns we can either do it by name or by an index, which starts at 1, their numbering is given by the order in which it was entered in the SELECT
You have to pay attention to the dates, because they are kept as java.sql.Date, not as java.util.Date as you might expect
It should be close ResultSet although it is implicitly closes when you close or reuse the Statement that created it
When you have read a null SQL using one of the methods get<tipo>, it returns:
-
A value null Java for those methods that return Java objects (getString(), getBigDecimal(), getDate(), getTime(), getTimestamp(), getObject(), etc)
-
A value of 0 for those methods that return numeric types (getByte(), getShort(), getInt(), getLong(), getFloat(), getDouble() )
-
A value False for the method getBoolean()
To determine if a given value was null, you must first try to read the column and use the ResulSet wasNull() to know if it was null
Return True yes it was, False otherwise
The interface PreparedStatement
Every time you launch a Statement the database must interpret it and calculate a query plan
But when using PreparedStatement, can be executed multiple times, getting an increase in performance by having the query already analyzed and optimized
Not all databases support PreparedStatement, you have to read the documentation of it to know if they can be used
The objects PreparedStatement derived from the Statement obtained from the connection, using the prepareStatement(<sql>);
A PreparedStatement to launch precompiled SQL statements, you can parameterize one or more entries using the ?, whose values may be changed in different executions of the statement
Parameterize entries is useful when we do not know the values of certain SQL data types in the target database,
The parameterized value gets the correct value from the database driver, so we won't have to worry about the types
Before the SQL statement can be executed we will need to assign a value to the input parameters
To assign values use the methods set<type>(column, value); being the type compatible with the parameter
To run methods are used execute of Statement
In the example we have used a PreparedStatement for Oracle