JDBC

Connecting to a database

Database pooling

Statements (prepapred, statements)

Prepared STatement

allows you to write a parameterized query
PreparedStatement is a class in java.sql package and allows Java programmers to execute SQL queries by using the JDBC package
Prepared Statement queries are pre-compiled on the database and their access plan will be reused to execute further queries which allow them to execute much quicker than normal queries generated by Statement object.
Benefits of prepared statement:
- prevents sql injections
  - all values passed in to it, will be escaped automatically
- better performance
- allows you to write a dynamic and parametric query.
  - it's like a template, where you can set the values using specific methods
  - Much faster than using string concat to construct the sql
- More readable
Limitation
- doesn't allow multiple values for one placeholder (?) who makes it tricky to execute SQL query with IN clause

Executing statements

Getting/reading results

Use of iterator pattern

Storing in object

try with resources to close connections

Transactions

A simplified way of doing commits

import java.sql.Connection;

Connection connection = dataSource.getConnection(); // (1)

try (connection) {
    connection.setAutoCommit(false); // (2)
    // execute some SQL statements...
    connection.commit(); // (3)

} catch (SQLException e) {
    connection.rollback(); // (4)
}

1 - You need a connection to the database to start transactions. in most enterprise-y applications you will have a data source configured and get connections from that

Be aware that some dbPooling libraries may turn automcommit by default

2 - This is the only way to 'start' a database transaction in Java, even though the name might sound a bit off. setAutoCommit(true) makes sure that every single SQL statement automatically gets wrapped in its own transaction and setAutoCommit(false) is the opposite: You are the master of the transaction(s) and you’ll need to start calling commit and friends. Do note, the autoCommit flag is valid for the whole time your connection is open, which means you only need to call the method once, not repeatedly

3 - Let’s commit our transaction, all the sql statements

4 - Or, rollback our changes, if there was an exception.

Other

java.sql.Date only has the date portion no time
- need to be careful when using java.util.Date as it contains time and date