Java

• Connection
  To establish a connection with the data source you want to use. A data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver. You need a Connection object to create a Statement object;
  You can establish a connection in 2 ways:
  

  • DriverManager: This fully implemented class connects an application to a data source, which is specified by a database URL. When this class first attempts to establish a connection, it automatically loads any JDBC 4.0 drivers found within the class path. Note that your application must manually load any JDBC drivers prior to version 4.0.
  • DataSource: This interface is preferred over DriverManager because it allows details about the underlying data source to be transparent to your application. A DataSource object's properties are set so that it represents a particular data source. In addition to other advantages, DataSource objects can provide connection pooling and distributed transactions. This functionality is essential for enterprise database computing. In particular, it is integral to Enterprise JavaBeans (EJB) technology.
    See Connecting with DataSource Objects for more information. For more information about developing applications with the DataSource class, see the latest The Java EE Tutorial.
• Statement
  Is an interface that represents a SQL statement. You execute Statement objects, and they generate ResultSet objects; There are three different kinds of statements::
  

  • Statement: Used to implement simple SQL statements with no parameters.
  • PreparedStatement: (Extends Statement.) Used for precompiling SQL statements that might contain input parameters. A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
  • CallableStatement: (Extends PreparedStatement.) Used to execute stored procedures that may contain both input and output parameters. The interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.
• ResultSet
  Is a table of data representing a database result set. A table of data representing a database result set, which is usually generated by executing a statement that queries the database.
  Note: see also RowSet below.
• RowSet
  
  A JDBC RowSet object holds tabular data in a way that makes it more flexible and easier to use than a result set, adding these new capabilities:
  

  •  Function as JavaBeans Component (they have: Properties and JavaBeans Notification Mechanism);
  •  Add Scrollability or Updatability (Some DBMSs do not support result sets that can be scrolled or updated, so using  a RowSet object is a way to achieve this);

  For all RowSet objects, three events trigger notifications:
  

  •  A cursor movement
  •  The update, insertion, or deletion of a row
  •  A change to the entire RowSet contents

  The 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.
  
  There are 2 kinds of RowSet objects:
  

  • Connected RowSet object: uses a JDBC driver to make a connection to a relational database and maintains that connection throughout its life span (Only one of the standard RowSet implementations is a connected RowSet object: JdbcRowSe. All the other are of disconnected type);
  • 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 and disconnects from it, thus becoming "disconnected.". Disconnected RowSet objects have all the capabilities of connected RowSet objects plus they have the additional capabilities available only to disconnected RowSet objects. For example, not having to maintain a connection to a data source makes disconnected RowSet objects far more lightweight than a JdbcRowSet object or a ResultSet object;

  
  Standard reference RowSet implementations:
  Oracle has defined five RowSet interfaces (that extend the RowSet interface) for some of the more popular uses of a RowSet, and standard reference implementations are available for these RowSet interfaces:
  

  • JdbcRowSet (extends ResultSet, RowSet):  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;
  • CachedRowSet (extends ResultSet, RowSet):  defines the basic capabilities available to all disconnected RowSet objects. The other three (WebRowSet, JoinRowSet, FilteredRowSet) are extensions of the CachedRowSet interface, which provide more specialized capabilities;
    Main features:
    
    • All the capabilities of a JdbcRowSet object
    • Obtain a connection to a data source and execute a query
    • Read the data from the resulting ResultSet object and populate itself with that data
    • Manipulate data and make changes to data while it is disconnected
    • Reconnect to the data source to write changes back to it
    • Check for conflicts with the data source and resolve those conflicts
  • WebRowSet (extends ResultSet, RowSet, CachedRowSet)
    Main features:
    
    • All the capabilities of a CachedRowSet object
    • Write itself as an XML document
    • Read an XML document that describes a WebRowSet object
  • JoinRowSet (extends  ResultSet, RowSet, CachedRowSet, WebRowSet)
    Main features:
    
    • All the capabilities of a WebRowSet object (and therefore also those of a CachedRowSet object)
    • Form the equivalent of a SQL JOIN without having to connect to a data source
  • FilteredRowSet (extends  ResultSet, RowSet, CachedRowSet, WebRowSet)
    Main features:
    
    • All the capabilities of a WebRowSet object (and therefore also a CachedRowSet object)
    • 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.

• Connection getConnection(String url, Properties info) throws SQLException
  
  Attempts to establish a connection to the given database URL.
  Usage example:
  

  Connection connection = DriverManager.getConnection("jdbc:derby:yourDB;create=true");

  
  This URL String depends on the DBMS used [4]:
  

  • MySQL Connector/J connection URL examples:
       jdbc:mysql://localhost:3306/yourDBname
       jdbc:mysql://localhost:3306/
    NOTE: the first url is used to connect to a existing db; the second url is used to connect to the DBMS only and then you can create the DB with:
    

    String newDatabaseString = 
         "CREATE DATABASE IF NOT EXISTS yourDBname";
    statement.executeUpdate(newDatabaseString);

  • Java DB embebbed mode connection URL examples:
       jdbc:derby:yourDBname;
       jdbc:derby:yourDBname;create=true
    NOTE: the first url is used to connect to a existing db; the seconde url is used when you want to automatically create a DB when is doesn't exist.

  
  This method specifies the user name and password required to access the DBMS with a Properties object. Example:
  

  Properties connectionProps = new Properties(); 
  connectionProps.put("user", "yourUsername"); 
  connectionProps.put("password", "yourPassword");

  The DriverManager attempts to select an appropriate driver from the set of registered JDBC drivers.
  Installing a JDBC driver generally consists of copying the driver to your computer, then adding the location of it to your class path.
  Any JDBC 4.0 drivers that are found in your class path are automatically loaded, but in previous versions of JDBC, to obtain a connection, you first had to initialize your JDBC driver by calling the method Class.forName. This methods required an object that implements java.sql.Driver.
  Each JDBC driver contains one or more classes that implements the interface java.sql.Driver:
  

  • JavaDB embbebed mode JDBC driver is implemented by class
    org.apache.derby.jdbc.EmbeddedDriver 
    located in <Java DB installation directory>/lib/derby.jar
  • JavaDB Network mode JDBC driver is implemented by class
    org.apache.derby.jdbc.ClientDriver 
    located in TODO
  • MySQL JDBC driver (Connector/J) is implemented by class
    com.mysql.jdbc.Driver 
    located in <Connector/J installation directory>/mysql-connector-java-version-number.jar
    Example:
    C:\glassfish3\glassfish\domains\domain1\lib\mysql-connector-java-5.1.21-bin.jarMySQL drivers can be obtained here

• Java DB JDBC driver includes the implementation: org.apache.derby.jdbc.ClientDataSource;
• MySQL has the implementantion: com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

• A basic DataSource implementation produces standard Connection objects that are not pooled or used in a distributed transaction. This basic implementation of DataSource produces the same kind of connections that the DriverManager class produces.
• A DataSource implementation that supports connection pooling produces Connection objects that participate in connection pooling, that is, connections that can be recycled.
• A DataSource implementation that supports distributed transactions produces Connection objects that can be used in a distributed transaction, that is, a transaction that accesses two or more DBMS servers.
  Note: A DataSource implementantion class that supports distributed transactions typically also implements support for connection pooling. For example, a DataSource class provided by an EJB vendor almost always supports both connection pooling and distributed transactions.

//get a DataSource object through the JNDI naming service
Context ctx = new InitialContext();
DataSource datasource =
    (DataSource)ctx.lookup("jdbc/billingDB");

//get a Connection object through the DataSource object
Connection con = datasource.getConnection(
    "username","yourpassword");

try {
    Connection con =
    dataSource.getConnection(username, password);
    // ... code to use the pooled connection
} catch (Exception ex {
    // ... code to handle exceptions
} finally {
    if (con != null) con.close();
}

• Statement createStatement()or
  Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
  
  Creates a Statement object for sending SQL statements to the database.
  The first overload method uses the default values. The second overload is used when you want to be able to update/insert values trough a resultset, the parameters are:
  

  • resultSetType - the ways in which the cursor can be manipulated (this is not a DB cursor,just a pointer to to current row of the resultset): TYPE_FORWARD_ONLY (default); TYPE_SCROLL_INSENSITIVE; TYPE_SCROLL_SENSITIVE;
  • resultSetConcurrency - determines what level of update functionality is supported: CONCUR_READ_ONLY (default) or CONCUR_UPDATABLE;
  • resultSetHoldability - gives the application control over whether ResultSet objects (cursors) are closed when commit is called: HOLD_CURSORS_OVER_COMMIT or CLOSE_CURSORS_AT_COMMIT (The default value depends on the DBMS);

  Learn more here: Retrieving and Modifying Values from Result Sets
• PreparedStatement prepareStatement(String sql)
• Creates a PreparedStatement object for sending parameterized SQL statements to the database.
• CallableStatement prepareCall(String sql)
  
  Creates a CallableStatement object for calling database stored procedures.
• void setAutoCommit(boolean autoCommit)
  
  Sets this connection's auto-commit mode to the given state. Default is "true" and every statement is automatically commited, otherwise you need to use commit() manually.
• void commit()
• void rollback()
• Savepoint setSavepoint(String name)
• void setSchema(String schema)

• ResultSet executeQuery(String sql)
  
  Executes the given SQL statement, which returns a single ResultSet object.
• int executeUpdate(String sql)
  
  Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
• int[] executeBatch()
  
  Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.
  Statment objects have a list of commands that is associated with them. This list can contain only statements that produce an update count like: statements for updating, inserting, or deleting a row; DDL statements such as CREATE TABLE and DROP TABLE. It cannot, however, contain a statement that would produce a ResultSet object, such as a SELECT statement.
  Example:
  

  statement.addBatch(
          "INSERT INTO COFFEES "
          + "VALUES('Amaretto', 49, 9.99, 0, 0)");
  statement.addBatch(
          "INSERT INTO COFFEES "
          + "VALUES('Hazelnut', 49, 9.99, 0, 0)");
  
  int [] updateCounts = statement.executeBatch();
  

  It is also possible to have a parameterized batch update, learn how in Retrieving and Modifying Values from Result Sets

preparedStatement = connection.prepareStatement(""
        + "update " + dbName + ".COFFEES "
        + "set SALES = ? where COF_NAME = ? ");

preparedStatement.setInt(1, 1000);
preparedStatement.setString(2, "Espresso");
preparedStatement.executeUpdate();

//NOTE: no need to re-set the int value if you want to supply the same value
//it is retain from the previous set till another value is supplied
preparedStatement.setString(2, "Amaretto");
preparedStatement.executeUpdate();

Connection c = getConnection();
PreparedStatement ps = 
    c.prepareStatement(this.PS_INSERT_LIVRO, PreparedStatement.RETURN_GENERATED_KEYS);

ps.setString(1, livro.getAno());
ps.setString(2, livro.getTitulo());

ps.executeUpdate(); 

//get the generatedKey:
ResultSet generatedKeys = ps.getGeneratedKeys();
int generatedId = -1;
if (generatedKeys.next()) {
    generatedId = generatedKeys.getInt(1);
} else {
    System.out.println("UNABLE TO GET GENERATED ID");
}            

this.inserirAutores(generatedId, livro.getAutores());

• ResultSet usage example:
  

  statement = connection.createStatement(
          ResultSet.TYPE_SCROLL_SENSITIVE           
          ResultSet.CONCUR_UPDATABLE);
  ResultSet resultSet = statement.executeQuery(
          "SELECT * FROM myDB.COFFEES");
  
  resultSet.moveToInsertRow();
  
  resultSet.updateString("COF_NAME", coffeeName);
  resultSet.updateInt("SUP_ID", supplierID);
  resultSet.updateFloat("PRICE", price);
  resultSet.updateInt("SALES", sales);
  resultSet.updateInt("TOTAL", total);
  
  resultSet.insertRow();
  resultSet.beforeFirst();

• Cursor related methods (Note: this is not an SQL cursor! The ResultSet cursor is just a pointer to the current selected row):
  
  • void beforeFirst(): Moves the cursor to the front of this ResultSet object, just before the first row.
  • boolean absolute(int row): Moves the cursor to the given row number in this ResultSet object.
  • void afterLast(): Moves the cursor to the end of this ResultSet object, just after the last row.
  • boolean first(): Moves the cursor to the first row in this ResultSet object.
  • boolean isAfterLast(): Retrieves whether the cursor is after the last row in this ResultSet object.
  • boolean isBeforeFirst(): Retrieves whether the cursor is before the first row in this ResultSet object.
  • boolean isFirst(): Retrieves whether the cursor is on the first row of this ResultSet object.
  • boolean isLast(): Retrieves whether the cursor is on the last row of this ResultSet object.
  • boolean last(): Moves the cursor to the last row in this ResultSet object.
  • void moveToCurrentRow(): Moves the cursor to the remembered cursor position, usually the current row.
  • void moveToInsertRow(): Moves the cursor to the insert row.
  • boolean next(): Moves the cursor froward one row from its current position.
  • boolean previous(): Moves the cursor to the previous row in this ResultSet object.
  • boolean relative(int rows): Moves the cursor a relative number of rows, either positive or negative.
• Row related methods:
  
  • void deleteRow(): Deletes the current row from this ResultSet object and from the underlying database.
  • int getRow(): Retrieves the current row number.
  • void insertRow(): Inserts the contents of the insert row into this ResultSet object and into the database.
  • void refreshRow(): Refreshes the current row with its most recent value in the database.
  • void updateRow(): Updates the underlying database with the new contents of the current row of this ResultSet object.
  • boolean rowDeleted(): Retrieves whether a row has been deleted.
  • boolean rowInserted(): Retrieves whether the current row has had an insertion.
  • boolean rowUpdated(): Retrieves whether the current row has been updated.
• Column related methods:
  
  There are update methods for each data type like:
  

  • updateDouble(String columnLabel, double x)
    updateFloat(int columnIndex, float x);
  • updateInt(int columnIndex, int x)
    updateInt(String columnLabel, int x)
  • etc.

//Disabling Auto-Commit Mode:
connection.setAutoCommit(false);

//Manually commit a transaction (if setAutoCommit is disabled):
connection.commit();

//using savepoints:
Savepoint save1 = connection.setSavepoint();
connection.rollback(save1);

1. Establishing a connection.
   TODO
2. Create a statement.
   TODO
3. Execute the query.
   TODO
4. Process the ResultSet object.
   TODO
5. Close the connection.
   TODO