Driver. Provides the API for registering and connecting drivers based on JDBC (every driver class must implement it). Generally used only by the DriverManager class
DriverManager. To makes a connection with a driver.
Connection. Provides methods for creating statements and managing connections with a data source and their properties.
Statement. Object used for executing a static SQL statement and returning the results it produces.
ResultSet. Object used for retrieving and updating the results of a query.
To use the JDBC API with a particular DBMS, you need a JDBC driver to establish the communication between Java and the database. JDBC drivers are divided into four types:
Type 1: JDBC-ODBC (Open Database Connectivity) Bridge
Type 2: Native-API, partly Java driver
Type 3: Network-protocol, all-Java driver
Type 4: Native-protocol, all-Java driver
All JDBC drivers implement four JDBC interfaces:
The DriverManager class tracks the loaded JDBC drivers and creates the database connections. Before JDBC 4.0, the Driver class was loaded with this code:
Class.forName("com.jw.client.JWDriver");
From JDBC 4.0 this is done automatically. This registers the driver with the DriverManager. This way, when a program creates a database connection with the DriverManager.getConnection() method, the DriverManager, in turn, calls the Driver.connect() method. Every JDBC driver must implement the java.sql.Driver interface. So, the JDBC driver's connect() method checks whether the driver URL is correct, and then, returns the Connection within its connect() method.
When working with JDBC, the first thing you need to do is to establish a connection with a data source (like a DBMS). One way to this is to use the class DriverManager.
DriverManager connects an application to a data source by using a database URL. When this class first attempts to establish a connection, it automatically loads any JDBC 4.0 drivers found within the classpath.
A database URL varies depending on the DBMS used. For example, for MySQL it looks like this:
jdbc:mysql://localhost:3306/data
Where localhost is the name of the server that is hosting the database, 3306 is the port number and data is the name of the database.
In previous versions of JDBC, to get a connection, you first had to initialize the JDBC driver by calling the method Class.forName(). Since JDBC 4.0, drivers that are found in your class path are automatically loaded.
To connect to a data source using DriverManager, we need to get a Connection object with something like the following snippet:
Properties props = new Properties();
props.put("user", userName);
props.put("password", password);
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/data");
To process an SQL statement with JDBC you have to:
In code, it looks like this:
String sql = "SELECT id, name FROM users WHERE id = ?";
List<User> users = new ArrayList<>();
try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test?user=admin&password=admin12345");
PreparedStatement ps = con.prepareStatement(sql);) {
ps.setInt(1, 1001);
try (ResultSet rs = ps.executeQuery();) {
while(rs.next()) {
users.add(new User(rs.getInt("id"), rs.getString("name")));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
First, a connection with a MySQL database is established.
With a Connection object, you create a Statement object. There are three different kinds of statements:
To execute a query, call an execute method from Statement such as:
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.With a ResultSet object, you can access the data. It acts as a cursor, pointing to one row of data and positioned before the first row at the beginning. Then you call, for example, the method next() to move the cursor forward by one row and you can get the data with getter methods that either take the column index (the first column is 1) or the column name. There are getter methods for a lot of types, for example:
int getInt(int columnIndex);
int getInt(String columnName);
long getLong(int columnIndex);
long getLong(String columnName);
String getString(int columnIndex);
String getString(String columnName);
BigDecimal getBigDecimal(int columnIndex);
BigDecimal getBigDecimal(String columnName);
When you are finished, call the method Statement.close() to immediately release the resources it's using. When you call this method, its ResultSet objects are also closed. If you're not going to need the connection anymore, you should also close the connection with Connection.close(). You can use a try-with-resources statement to automatically close Connection, Statement, and ResultSet objects, regardless of whether an SQLException has been thrown. Or you can close them manually in the finally block like this:
try {
Connection con = ...
Statement stmt = ...
// Do something with stmt
} catch(Exception e) {
e.printStackTrace();
} finally {
if (stmt != null) { stmt.close(); }
if (con != null) { con.close(); }
}