Naked JDBC in common

The JDBC™ API provides programmatic access to relational data from the Java™ programming language.

Using the JDBC API, applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source.

The JDBC API can also interact with multiple data sources in a distributed, heterogeneous environment.

---- ## Preconditions * Install JAVA SE SDK * Install your DBMS *(database management system)* * Install a JDBC driver *(connector)* from the Vendor of your database. ---- ## Establishing Connection You can establish connection either via `DriverManager` class or through the `DataSource` interface. ---- ### Using `DriverManager` `DriverManager` class connects an application to a data source which is specified by database URL. Properties connectionProps = new Properties(); connectionProps.put("user", this.userName); connectionProps.put("password", this.password); Connection conn = DriverManager.getConnection( "jdbc:" + this.dbms + "://" + this.serverName + ":" + this.portNumber + "/", connectionProps); A database connection URL is a string that your DBMS JDBC driver uses to connect to a database. `DriverManager` establishes a database connection with the Java DB Embedded Driver via connection URL e.g. `jdbc:mysql://localhost:3306/test_db` ---- ### Using `DataSource` (More preferred) `DataSource` interface is preferred over `DriverManager` because it allows details about the underlying data source to be transparent to your application. A `DataSource` class that supports distributed transactions, typically also implements support for *connection pooling* which allows connection recycling. With *connection pooling*, a pool of connections can be used over and over again, avoiding the expense of creating a new connection for every database access. ---- #### Advantages of `DataSource` Using Because of it's properties, a `DataSource` object is a better alternative than the `DriverManager` class for getting a connection. Programmers no longer have to hard code the driver name or JDBC URL in their applications, which makes them more portable. Also, `DataSource` properties make maintaining code much simpler. If there is a change, the system administrator can update data source properties and not be concerned about changing every application that makes a connection to the data source. For example, if the data source were moved to a different server, all the system administrator would have to do is set the serverName property to the new server name. ---- #### Pooling Connections Getting and using a pooled connection is similar to getting and using a regular connection. When someone acting as a system administrator has deployed a `ConnectionPoolDataSource` object and a `DataSource` object properly, an application uses that `DataSource` object to get a pooled connection. An application should, however, use a `finally` block to close the pooled connection. Cause either an exception will be thrown by a method in the `try` block, or it won't be thrown at all, the `finally` clause will be executed in any case. ---- #### Distributed Transactions To get a connection that can be used for *distributed transactions*, must use a `DataSource` object, that should be properly implemented to interact with xads and other `XADataSource` objects and deployed. Usually the connection used for the *distributed transaction* is a *pooled connection*, the application uses a `finally` block to close the connection. > Distributed transaction is no different from other code except that > it does not call the Connection methods `commit`, `rollback`, or `setAutoCommit(true)`. ** ---- ## Execute SQL Statements > `Statement` is an interface that represents a SQL statement. You execute `Statement` objects, and they generate `ResultSet` objects, which is a table of data representing a database result set. You need a `Connection` object to create a `Statement` object. String createQuery = "CREATE TABLE table_name( column1 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) )"; Statement stmt = con.createStatement(); stmt.executeUpdate(createQuery); * `Statement`: Used to implement simple SQL statements with no parameters. * `PreparedStatement`: Used for precompiling SQL statements that might contain input parameters. * `CallableStatement`: Used to execute stored procedures that may contain both input and output parameters. > If you want to execute a `Statement` object many times, it usually reduces execution time to use a `PreparedStatement` object instead. > This also means that when the `PreparedStatement` is executed, the DBMS can just run the `PreparedStatement` SQL statement without having to compile it first. **Use following `Statement` execute queries:** * `execute`: Returns `true` if the 1st object that query returns a `ResultSet`. Use it if query can return one or more `ResultObjects`. Retrieve those from the query by repeatedly calling `Statement.getResultSet`. * `executeQuery`: Returns one `ResultSet` object. Use it if u're using `SELECT` statement. * `executeUpdate`: Returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using `INSERT`, `DELETE`, or `UPDATE` SQL statements. * Also u can use `addBatch`/`clearBatch`...`executeBatch` for batch updates, which is more effective. It's also possible to have parametrized batch update. ---- ### Retrieving Data from Table The `ResultSet` interface provides methods for retrieving and manipulating the results of executed queries, and `ResultSet` objects can have different functionality and characteristics. String selectQuery = "SELECT * FROM table_name"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeUpdate(selectQuery); while (rs.next()) { String productName = rs.getString("NAME"); int supplierID = rs.getInt("ID"); float price = rs.getFloat("PRICE"); int total = rs.getInt("TOTAL"); System.out.println(productName + "\t" + supplierID + "\t" + price + "\t" + sales + "\t" + total); } > `ResultSet` cursor is not a database cursor, it's a pointer that points to one row of data in the `ResultSet` object. **`ResultSet` characteristics that u can pass during the `createStatement` invocation are:** * **type**(`TYPE_FORWARD_ONLY`, `TYPE_SCROLL_INSENSITIVE`, `TYPE_SCROLL_SENSITIVE`); * **concurrency**(`CONCUR_READ_ONLY`,`CONCUR_UPDATABLE`); * **cursor holdability**(`HOLD_CURSORS_OVER_COMMIT`,`CLOSE_CURSORS_AT_COMMIT`). > You can retrieve values using either the index number of the column or the alias or name of the column. > *(The column index is usually more efficient. Columns are numbered from 1)* ---- ### Using Prepared Statements The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. To use Values with `PreparedStatement` use question mark `?` in places where the values should be set: `String updateQuery = "UPDATE table_name SET column = ? WHERE statement = ?";` String updateQuery = "UPDATE pet SET sex = ? WHERE name = ?"; PreparedStatement updateSex = con.prepareStatement(updateQuery); // ... updateBirth.setString(1, "m"); updateBirth.setString(2, "Crumpy"); updateBirth.executeUpdate(); Using loop to set values usually is more effective: for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) { updateSales.setInt(1, e.getValue().intValue()); updateSales.setString(2, e.getKey()); // ... } ---- ### Using Transactions [Transactions]() are used when few values should be updated at the same time. A transaction is a set of one or more statements that is executed as a unit, so either all of the statements are executed, or none of the statements is executed. > If *auto-commit* mode is disabled, no SQL statements are committed until you call the method `COMMIT` explicitly. > All statements executed after the call to the method `COMMIT` are included in the current transaction and committed together as a unit. *auto-commit* can be disabled and enabled back after the transaction execution via using `con.setAutoCommit(value)` method: /* init statements code */ try { con.setAutoCommit(false); updateSales = con.createStatement(updateSalesQuery); updateTotal = con.createStatement(updateTotalQuery); ... updateSales.executeUpdate(); ... updateTotal.executeUpdate(); con.commit(); } catch (SQLException e ) { /* exception handling code */ } finally { /* closing statements code */ con.setAutoCommit(true); } > It is advisable(more effective) to disable the auto-commit mode only during the transaction mode. > This way, you avoid holding database locks for multiple statements, which increases the likelihood of conflicts with other users. Transactions can help to preserve the integrity of the data in a table. The method `rollback` aborts a transaction and restores values to what they were before the attempted update. The method `Connection.setSavepoint`, sets a `Savepoint` object within the current transaction. The `Connection.rollback` method is overloaded to take a `Savepoint` argument. ---- #### Transaction locks To avoid conflicts during a transaction, a DBMS uses locks, mechanisms for blocking access by others to the data that is being accessed by the transaction. (Note that in auto-commit mode, where each statement is a transaction, locks are held for only one statement.) **After a lock is set, it remains in force until the transaction is committed or rolled back.** How Locks are set is defined by *transaction isolation level*. Usually you do not need to do anything about it, it depends of your DBMS, however JDBS allows u to redefine it via using `Connection::get/setTransactionIsolation`. **Possible implications of wrong isolation level using:** * **dirty read** - accessing an updated value that has not been committed. If you read a value that is later rolled back, you will have read an invalid value. * **non-repeatable read** - occurs when transaction A retrieves a row, transaction B subsequently updates the row, and transaction A later retrieves the same row again. Transaction A retrieves the same row twice but sees different data. * **phantom read** - occurs when transaction A retrieves a set of rows satisfying a given condition, transaction B subsequently inserts or updates a row such that the row now meets the condition in transaction A, and transaction A later repeats the conditional retrieval. Transaction A now sees an additional row. This row is referred to as a *phantom*. ---- ## Using RowSet Objects A JDBC `RowSet` object holds tabular data in a way that makes it more flexible and easier to use than a result set. **Standart reference implementations of `RowSet`:** * `JdbcRowSet` - connected `RowSet` * `CachedRowSet` - represent basic capabilities of all disconnected `RowSet` objects * `WebRowSet` - `CachedRowSet` + read/write itself as XML * `JoinRowSet` - equivalent to `SQL JOIN` without having to connect to a data source. * `FilterRowSet` - Apply filtering criteria so that only selected data is visible. This is equivalent to executing a query on a `RowSet` object without having to use a query language or connect to a data source. All `RowSet` objects are derived from the `ResultSet` interface and therefore share its capabilities. **All `RowSet` objects are *JavaBeans* components, so they have the following:** * **Properties** - fields with getter and setter. * **JavaBeans Notification Mechanism** - registered components are notified when certain events occur *(cursor movement, update, insertion, deletion of a row, change to entire `RowSet`)* Notification of an event goes to all listeners, components that have implemented the `RowSetListener` interface and have had themselves added to the `RowSet` object's list of components to be notified when any of the three events occurs. If a driver for that DBMS does not add the ability to scroll or update result sets, you can use a `RowSet` object to do it. A `RowSet` object is scrollable and updatable by default, so by populating a `RowSet` object with the contents of a result set, you can effectively make the result set scrollable and updatable. ---- ### Kinds of RowSet Object > There are two kinds of RowSet objects: *Connected* & *Disconnected*. ---- #### Connected RowSet Objects A *connected* RowSet object uses a JDBC driver to make a connection to a relational database and maintains that connection throughout its life span. ---- #### Disconnected RowSet Objects A *disconnected* `RowSet` object makes a connection to a data source only to read in data from a `ResultSet` object or to write data back to the data source. After reading data from or writing data to its data source, the `RowSet` object disconnects from it, thus becoming "disconnected." During much of its life span, a *disconnected* `RowSet` object has no connection to its data source and operates independently. *Disconnected* `RowSet` objects have all the capabilities of *connected* `RowSet` objects plus they have the additional capabilities available only to *disconnected* `RowSet` objects. *Disconnected* `RowSet` objects far more lightweight than a `JdbcRowSet` object or a `ResultSet` object. *Disconnected* `RowSet` objects are also serializable, and the combination of being both serializable and lightweight makes them ideal for sending data over a network. ---- ### Using JdbcRowSet Objects `JdbcRowSet` - only one of the standard `RowSet` implementations that is *connected* `Rowset` object. Always being connected to a database, a `JdbcRowSet` object is most similar to a `ResultSet` object and is often used as a wrapper to make an otherwise non-scrollable and read-only `ResultSet` object scrollable and updatable. It maintains a connection to its data source, just as a `ResultSet` object does. > The big difference is that it has a set of properties and a listener notification mechanism that make it a JavaBeans component. **You can create a `JdbcRowSet` object in various ways:** * By using the reference implementation constructor that takes a `ResultSet` object: * By using the reference implementation constructor that takes a `Connection` object. * By using the reference implementation *default constructor*. * By using an instance of `RowSetFactory`, which is created from the class `RowSetProvider`. > `JdbcRowSet` and all other `RowSet` objects are scrollable and updatable by default unless otherwise specified. Which of these properties you set depends on how you are going to make a connection. The preferred way is to use a `DataSource object`, but it may not be practical for you to register a `DataSource` object with a *JNDI naming* service, which is generally done by a system administrator. Therefore, the code examples all use the `DriverManager` mechanism to obtain a connection, for which you use the url property and not the datasourceName property. ---- #### Using constructor that takes a `ResultSet` object The simplest way to create a `JdbcRowSet` object is to produce a `ResultSet` object and pass it to the `JdbcRowSetImpl` constructor. Doing this not only creates a `JdbcRowSet` object but also populates it with the data in the `ResultSet` object: stmt = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); rs = stmt.executeQuery("select * from COFFEES"); jdbcRs = new JdbcRowSetImpl(rs); ---- #### Using constructor that takes a `Connection` object The object jdbcRs contains no data until you specify a SQL statement with the method `setCommand`, then run the method `execute`. jdbcRs = new JdbcRowSetImpl(con); jdbcRs.setCommand("select * from COFFEES"); jdbcRs.execute(); ---- #### Using default constructor The object jdbcRs contains no data until you specify a SQL statement with the method `setCommand`, specify how the `JdbcResultSet` object connects the database, and then run the method `execute`. jdbcRs = new JdbcRowSetImpl(); jdbcRs.setCommand("select * from COFFEES"); jdbcRs.setUrl("jdbc:myDriver:myAttribute"); jdbcRs.setUsername(username); jdbcRs.setPassword(password); jdbcRs.execute(); ---- #### Using RowSetFactory constructor myRowSetFactory = RowSetProvider.newFactory(); jdbcRs = myRowSetFactory.createJdbcRowSet(); jdbcRs.setUrl("jdbc:myDriver:myAttribute"); jdbcRs.setUsername(username); jdbcRs.setPassword(password); jdbcRs.setCommand("select * from COFFEES"); jdbcRs.execute(); ---- ### Usage of `JdbcRowSet` Objects You update, insert, and delete a row in a `JdbcRowSet` object the same way you update, insert, and delete a row in an updatable `ResultSet` object. Similarly, you navigate a `JdbcRowSet` object the same way you navigate a scrollable `ResultSet` object. * To navigate `JdbcRowSet` its possible to use any of the `ResultSet` cursor movement methods like `next`, `previous`,`absolute`. * To update the column values u have to move the cursor to particular column, update value & then update row via `updateRow`. jdbcRs.absolute(3); jdbcRs.updateFloat("PRICE", 10.99f); jdbcRs.updateRow(); * To insert new row move to the cursor to the insert row, use the appropriate updater method to set a value for each column, and call the method `insertRow` method. jdbcRs.moveToInsertRow(); jdbcRs.updateString("COF_NAME", "HouseBlend"); jdbcRs.updateInt("SUP_ID", 49); jdbcRs.updateFloat("PRICE", 7.99f); ... jdbcRs.insertRow(); * To delete rows move cursor to the proper row and invoke `deleteRow`. ---- ## Closing Connection When you are finished using a `Statement`, call the method `Statement.close` to immediately release the resources it is using. When you call this method, its `ResultSet` objects are closed. Use *try-with-resources* during the `Statement` creation, to automatically close it, when the try block terminates: try (Statement stmt = con.createStatement()) { // ... } ## see Also * [Transactions Control in SQL](http://bloomlab.blogspot.com/2015/03/transactions-control-in-sql-postgresql.html) * [Oracle Java Tuts - JDBC Introduction](http://docs.oracle.com/javase/tutorial/jdbc/overview/index.html) * [Oracle Java Tuts - JDBC Basics](http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html) * [Tutorials point - JDBC](http://www.tutorialspoint.com/jdbc/) * [Jenkov - JDBC tutorial](http://tutorials.jenkov.com/jdbc/index.html)