The [java.sql](https://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html) API is horrible, [ResultSet.wasNull()](https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#wasNull--) ?, enough said. Hibernate is black magic that generates some truly
Write an interface or abstract class with methods that make sense for accessing your database, annotate the methods with
SQL, and on compilation an annotation processor will generate the required java.sql API code to execute your query and
return what you wanted. This code is guaranteed to be the fastest code possible because hand written code would look
the same, just more error prone and harder to maintain. The annotation processor also checks that the SQL queries are
valid, have all the right bind parameters, and can bind the result columns to all the correct fields on the result object.
If anything is wrong it's a compile error pointing you to the exact problem.
Example:
```java
@JdbcMapper.Mapper(jndiName = "java:/comp/env/jdbc/testPool") // omit jndiName and you must send in a java.sql.Connection
public interface PersonDAO extends Closeable { // Closeable is optional but must have a 'void close()' method to use cachePreparedStatements or jndiName
@JdbcMapper.SQL("INSERT INTO person (person_no, birth_date, last_name, first_name) VALUES ({personNo}, {birthDate}, {firstName}, {lastName})")
int insertPerson(long personNo, Date birthDate, String firstName, String lastName);
@JdbcMapper.SQL("UPDATE person SET first_name = {firstName} WHERE person_no = {personNo}")
int setFirstName(String firstName, long personNo); // returning int will return number of rows modified, can also return void
@JdbcMapper.SQL("SELECT first_name FROM person WHERE person_no = {personNo}")
String getFirstName(long personNo) throws SQLException; // can map directly to simple types
@JdbcMapper.SQL("SELECT person_no, first_name, last_name, birth_date FROM person WHERE person_no = {personNo}")
Person getPerson(long personNo) throws SQLException; // or multiple fields, set methods, or constructor parameters on a POJO
@JdbcMapper.SQL("SELECT person_no, first_name, last_name, birth_date FROM person WHERE last_name = {lastName}")
List<Person> getPeople(String lastName) throws SQLException; // all rows in any Collection<T> (like Set<T>, LinkedList<T> etc), T[], ResultSetIterable<T> or Stream<T> (java8+) works too
System.out.println(qm.executeUpdate("INSERT INTO person (person_no, birth_date, last_name, first_name) VALUES (?, ?, ?, ?)", 0, null, "First", "Person")); // 1
System.out.println(qm.executeUpdate("INSERT INTO person (person_no, birth_date, last_name, first_name) VALUES (?, ?, ?, ?)", 1, null, "First", "Person")); // 1
System.out.println(qm.executeUpdate("UPDATE person SET first_name = ? WHERE person_no = ?", "Second", 1)); // 1
// can map directly to simple types
System.out.println(qm.toObject("SELECT first_name FROM person WHERE person_no = ?", String.class, 0)); // First
System.out.println(qm.toObject("SELECT first_name FROM person WHERE person_no = ?", String.class, 1)); // Second
// or multiple fields, set methods, or constructor parameters on a POJO
System.out.println(qm.toObject("SELECT person_no, first_name, last_name, birth_date FROM person WHERE person_no = ?", String.class, 0)); // Person{personNo=0,birthDate=null,firstName=First,lastName=Person}
System.out.println(qm.toObject("SELECT person_no, first_name, last_name, birth_date FROM person WHERE person_no = ?", String.class, 1)); // Person{personNo=1,birthDate=null,firstName=Second,lastName=Person}
// instead of toCollection can use toList, toArray, toResultSetIterable, toStream (java8+)
System.out.println(qm.toCollection("SELECT person_no, first_name, last_name, birth_date FROM person WHERE last_name = ?", new ArrayList<String>(), String.class, "Person")); // [Person{personNo=0,birthDate=null,firstName=First,lastName=Person}, Person{personNo=1,birthDate=null,firstName=Second,lastName=Person}]
}
```
ResultSet (multiple rows) to Object/Collection Mapping
All decisions as to which ResultSet method(s) to call are based on the Java type being mapped to, because we have no
knowledge of any database schema. These mappings rarely if ever need changed, they can be overridden with QueryMapper
but not currently at compile-time with JdbcMapper.
If you are thinking 'shut up and show me the code already' refer to [ResultSetUtil.java](https://github.com/moparisthebest/JdbcMapper/blob/master/common/src/main/java/com/moparisthebest/jdbc/util/ResultSetUtil.java)
which contains the implementations actually called.
For the purposes of this mapping, consider 'rs' an instance of ResultSet, and 'index' an int index of a ResultSet column.
### numeric primitives
if the SQL value is NULL, 0 is returned for these, and no exception is thrown
If this does not throw an SQLException, it is returned directly
If SQLException is thrown, then we try to compare as a String:
```java
String bool = rs.getString(index);
boolean ret = ResultSetUtil.TRUE.equals(bool);
if (!ret && !ResultSetUtil.FALSE.equals(bool))
throw new SQLException(String.format("Implicit conversion of database string to boolean failed on column '%d'. Returned string needs to be '%s' or '%s' and was instead '%s'.", index, ResultSetUtil.TRUE, ResultSetUtil.FALSE, bool));
return ret;
```
The returned string MUST be either TRUE or FALSE (or null, for Object Boolean) or an exception will be thrown
### Misc Objects
For all of these, when SQL NULL is returned, it maps to null
For all of these, when SQL NULL is returned, it maps to null. All of the [ResultSet.getDate/Timestamp/etc](https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#getTimestamp-int-java.util.Calendar-)
functions optionally take a Calendar object which is used to construct a time value if the database doesn't store
timezone information. I am not going to show the variants that take Calendar here. For QueryMapper, methods are
overloaded to take the Calendar values, for JdbcMapper, if the abstract method takes a Calendar object that is not mapped
in the query, that is used.
In the Java 8 java.time code below that uses `ZoneId.systemDefault()`, where a Calendar object is sent in,
`calendar.getTimeZone().toZoneId()` is used instead.
##### java.sql.Date
```java
return rs.getDate(index);
```
##### java.sql.Time
```java
return rs.getTime(index);
```
##### java.sql.Timestamp
```java
return rs.getTimestamp(index);
```
##### java.util.Date
```java
java.sql.Timestamp ts = rs.getTimestamp(index);
return ts == null ? null : new java.util.Date(ts.getTime());
This explains how specific java types map to specific PreparedStatement calls, this can be different between JdbcMapper and QueryMapper because of the
different information available. With JdbcMapper we have type information regardless of the value, so a String is a String even if you send in null. With
QueryMapper if the value is null, we have no idea if that was supposed to be a Date or a String or what.
If you are thinking 'shut up and show me the code already' refer to [PreparedStatementUtil.java](https://github.com/moparisthebest/JdbcMapper/blob/master/common/src/main/java/com/moparisthebest/jdbc/util/PreparedStatementUtil.java#L26) for the runtime mapping, and [JdbcMapperProcessor.java](https://github.com/moparisthebest/JdbcMapper/blob/master/jdbcmapper/src/main/java/com/moparisthebest/jdbc/codegen/JdbcMapperProcessor.java#L918) for the compile-time mapping, which should end up being identical where possible.
For the purposes of this mapping, consider 'ps' an instance of PreparedStatement, 'index' an int index of a PreparedStatement column, and 'o' as the Object being mapped to the PreparedStatement column.
### Misc Objects
##### String / Number / Boolean / primitives
```java
ps.setObject(index, o);
```
##### null
This only applies at runtime, in which case we don't have a type, we always have a type at compile-time.
```java
ps.setObject(index, o);
```
##### java.lang.Enum (any enum)
```java
ps.setObject(index, o.name());
```
##### byte[]
```java
ps.setBlob(index, new ByteArrayInputStream(o));
```
##### java.sql.Ref
```java
ps.setRef(index, o);
```
##### java.sql.Blob / java.io.InputStream
```java
ps.setBlob(index, o);
```
##### String as Blob
Where `s` is the String, and `charset` is the character set to convert the String to bytes with,
if not provided, charset defaults to UTF-8:
```java
ps.setBlob(index, s == null ? null : new ByteArrayInputStream(s.getBytes(charset)));
```
At runtime using QueryMapper, you signal you want this by wrapping s with `PreparedStatementUtil.wrapBlob(s)` or `PreparedStatementUtil.wrapBlob(s, charset)`
At compile-time using JdbcMapper, you signal you want this in the SQL like `{blob:s}` or `{blob:utf-8:s}` any charset supported by your java works
##### java.io.File
```java
try {
ps.setBlob(index, new FileInputStream(o)); // todo: does this close this or leak a file descriptor?
} catch (FileNotFoundException e) {
throw new SQLException("File to Blob FileNotFoundException", e);
}
```
This will likely change in the near future to read file to byte[] and behave like byte[] from above, since we probably
can't count on the FileInputStream being properly closed...
##### java.sql.Clob / java.io.Reader
```java
ps.setClob(index, o);
```
##### String as Clob
Where `s` is the String:
```java
ps.setClob(index, s == null ? null : new StringReader(s));
```
At runtime using QueryMapper, you signal you want this by wrapping s with `PreparedStatementUtil.wrapClob(s)`
At compile-time using JdbcMapper, you signal you want this in the SQL like `{clob:s}`
