GIST NOTES 15 - Java JDBC
[DISCLAIMER: This is solely for non-commercial use. I don't claim ownership of this content. This is a crux of all my readings studies and analysis. Some of them are excerpts from famous books on the subject. Some of them are my contemplation upon experiments with direct hand coded code samples using IDE or notepad.
I've created this mainly to reduce an entire book into few pages of critical content that we should never forget. Even after years, you don't need to read the entire book again to get back its philosophy. I hope these notes will help you to replay the entire book in your mind once again.]
JDBC - Java DB Connectivity
ODBC - Open DB Connectivity
A Relational Database Overview
A database is a means of storing information in such a way that information can be retrieved from it. In simplest terms, a relational database is one that presents information in tables with rows and columns. A table is referred to as a relation in the sense that it is a collection of objects of the same type (rows). Data in a table can be related according to common keys or concepts, and the ability to retrieve related data from a table is the basis for the term relational database. A Database Management System (DBMS) handles the way data is stored, maintained, and retrieved. In the case of a relational database, a Relational Database Management System (RDBMS) performs these tasks. DBMS as used in this book is a general term that includes RDBMS.
Integrity Rules
Relational tables follow certain integrity rules to ensure that the data they contain stay accurate and are always accessible. First, the rows in a relational table should all be distinct. If there are duplicate rows, there can be problems resolving which of two possible selections is the correct one. For most DBMSs, the user can specify that duplicate rows are not allowed, and if that is done, the DBMS will prevent the addition of any rows that duplicate an existing row.
A second integrity rule of the traditional relational model is that column values must not be repeating groups or arrays. A third aspect of data integrity involves the concept of a null value. A database takes care of situations where data may not be available by using a null value to indicate that a value is missing. It does not equate to a blank or zero. A blank is considered equal to another blank, a zero is equal to another zero, but two null values are not considered equal.
When each row in a table is different, it is possible to use one or more columns to identify a particular row. This unique column or group of columns is called a primary key. Any column that is part of a primary key cannot be null; if it were, the primary key containing it would no longer be a complete identifier. This rule is referred to as entity integrity.
>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. See Using Prepared Statements for more information.
CallableStatement: (Extends PreparedStatement.) Used to execute stored procedures that may contain both input and output parameters. See Stored Procedures for more information.
>Executing Queries
To execute a query, call an execute method from Statement such as the following:
execute: Returns true if the first object that the query returns is a ResultSet object. Use this method if the query could return one or more ResultSet objects. Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResultSet.
executeQuery: Returns one ResultSet object.
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.
For example, CoffeeTables.viewTable executed a Statement object with the following code:
ResultSet rs = stmt.executeQuery(query);
>Using the DriverManager Class
Connecting to your DBMS with the DriverManager class involves calling the method DriverManager.getConnection. The following method, JDBCTutorialUtilities.getConnection, establishes a database connection:
public Connection getConnection() throws SQLException {
Connection conn = null;
Properties connectionProps = new Properties();
connectionProps.put("user", this.userName);
connectionProps.put("password", this.password);
if (this.dbms.equals("mysql")) {
conn = DriverManager.
getConnection("jdbc:" + this.dbms + "://" + this.serverName +
":" + this.portNumber + "/", connectionProps);
} else if (this.dbms.equals("derby")) {
conn = DriverManager.
getConnection("jdbc:" + this.dbms + ":" + this.dbName + ";create=true", connectionProps);
}
System.out.println("Connected to database");
return conn;
}
The method DriverManager.getConnection establishes a database connection. This method requires a database URL, which varies depending on your DBMS. The following are some examples of database URLs:
MySQL: jdbc:mysql://localhost:3306/, where localhost is the name of the server hosting your database, and 3306 is the port number
Java DB: jdbc:derby:testdb;create=true, where testdb is the name of the database to connect to, and create=true instructs the DBMS to create the database.
Note: This URL establishes a database connection with the Java DB Embedded Driver. Java DB also includes a Network Client Driver, which uses a different URL.
This method specifies the user name and password required to access the DBMS with a Properties object.
Note:
Typically, in the database URL, you also specify the name of an existing database to which you want to connect. For example, the URL jdbc:mysql://localhost:3306/mysql represents the database URL for the MySQL database named mysql. The samples in this tutorial use a URL that does not specify a specific database because the samples create a new database.
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 of type java.sql.Driver. Each JDBC driver contains one or more classes that implements the interface java.sql.Driver. The drivers for Java DB are org.apache.derby.jdbc.EmbeddedDriver and org.apache.derby.jdbc.ClientDriver, and the one for MySQL Connector/J is com.mysql.jdbc.Driver. See the documentation of your DBMS driver to obtain the name of the class that implements the interface java.sql.Driver.
Any JDBC 4.0 drivers that are found in your class path are automatically loaded. (However, you must manually load any drivers prior to JDBC 4.0 with the method Class.forName.)
The method returns a Connection object, which represents a connection with the DBMS or a specific database. Query the database through this object.
Transaction Processing
----------------------
>Use connection.setAutoCommit(false), connection.commit() and connection.rollback() methods to do transaction processing
>The method begins by creating a Savepoint with the following statement:
Savepoint save1 = con.setSavepoint();
The method checks if the new price is greater than the maximumPrice value. If so, the method rolls back the transaction with the following statement:
con.rollback(save1);
Consequently, when the method commits the transaction by calling the Connection.commit method, it will not commit any rows whose associated Savepoint has been rolled back; it will commit all the other updated rows.
---
>clob: character large object
>blob: binary large object
>nclob: national character large object
>RowSet/JdbcSet/CachedSet/.... these sets allow to scroll up and down, update data, join tables, offline/online operations to DB, caching and such facilities are provided on top of ResultSet; RowSet also provides event mechanism with listener model to listen on data changes on a particular row
>>Stemps involved in JDBC connection
1.Establishing a connection.
2.Create a statement.
3.Execute the query.
4.Process the ResultSet object.
5.Close the connection.
No comments:
Post a Comment