JDBC is an application programming interface (API) for the programming language Java, which defines how a client may access a database
An object has information such as the location of the database server, the name of the database, the network protocol to use to communicate with the server etc. Its the alternative for DriverManager class
Physical connection to the database created using the DataSource.getConnection() method. Connection is closed once the query is executed. But its too costly to create every time. Hence come the ConnectionPool concept
When application closes a pooled connection, the connection is returned to a pool of reusable connections. The next when connection is requested with getConnection, connection from the pool will be returned hence avoids creating a new physical connection every time. Improves performance
DataSource which implemented ConnectionPoolDataSource can support pooled connection
DataSource which implemented XADataSource can support distributed transaction (in EJB)
Interface that represents a SQL statement. Connection object is needed to create a Statement object. Used to implement simple SQL statements with no parameters
Methods - execute, executeQuery, executeUpdate
Preferred If we have to execute a Statement object many times
How it works differenly than Statement ?
PreparedStatement is given a SQL statement when it is created & sent to the DB where it is compiled.
As a result, the PreparedStatement object contains not just a SQL statement, but a SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first
Advantages
- Precompilation and DB-side caching of the SQL statement
- Prevention of SQL injection attacks by builtin escaping of quotes and other special characters
Thumb Rule - Statement for DDL and PreparedStatment for DML
Methods executeUpdate() for create, drop, insert, update, delete executeQuery() for the select query
NOTE If we don't use ? (question mark) PreparedStatement also is not safe from SQL Injection attacks
Statement
PreparedStatement stmt = conn.createStatement("INSERT INTO students VALUES('" + user + "')");
stmt.execute();
PreparedStatement
PreparedStatement stmt = conn.prepareStatement("INSERT INTO student VALUES(?)");
stmt.setString(1, user);
stmt.execute();
User Input
Robert'); DROP TABLE students; --
Represents the results from execution of Statement. Data in ResultSet is accessed using Cursor
Use close() method in Statement to release the resource. ResultSet objects also gets closed when close() is called