> For the complete documentation index, see [llms.txt](https://hanfak.gitbook.io/workspace/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://hanfak.gitbook.io/workspace/languages/java/advanced/jdbc.md).

# 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
  *

### Links

* <http://www.java67.com/2018/03/jdbc-difference-between.html>

## 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

```java
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

## Links

* <https://self-learning-java-tutorial.blogspot.co.uk/2014/10/jdbc.html>
* <https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html>
* <https://www.tutorialspoint.com/jdbc/index.htm>
* <https://www.javatpoint.com/java-jdbc>
* <https://www.journaldev.com/2681/jdbc-tutorial>
* <http://tutorials.jenkov.com/jdbc/index.html>
* <https://youtu.be/KgXq2UBNEhA> MarcoCode: JDBC Tutorial - Nice & Easy
  * <https://github.com/marcobehlerjetbrains/jdbc-tutorial>
  *