### Naive Logging with Timing and Results

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

This example demonstrates a naive logging setup using `jdbc/with-logging` to print SQL, parameters, timing information, and results to `*out*`. It calculates timing by subtracting the start time (from the first logger) from the end time.

```clojure
(def lds (jdbc/with-logging ds
 #_=>            (fn [sym sql-params] 
 #_=>              (prn sym sql-params)
 #_=>              (System/currentTimeMillis))
 #_=>            (fn [sym state result] 
 #_=>              (prn sym
 #_=>                   (- (System/currentTimeMillis) state)
 #_=>                   (if (map? result) result (count result))))))
#'dev/lds
dev=> (sql/find-by-keys lds :foo {:name "Person"})
next.jdbc/execute! ["SELECT * FROM foo WHERE name = ?" "Person"]
next.jdbc/execute! 813 1
[#:FOO{:NAME "Person"}]
dev=>
```

--------------------------------

### Configure Database Spec and Datasource

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Set up your database specification and datasource using next.jdbc. This example uses an in-memory H2 database.

```clojure
(def db-spec {:dbtype "h2:mem" :dbname "example"}) ; assumes H2 driver in deps.edn

(def ds (jdbc/get-datasource db-spec))
```

--------------------------------

### Select! with a Function

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Example of using `next.jdbc.plan/select!` with a custom function to transform each row of the query result.

```clojure
user=> (into [] (map f) (jdbc/plan ...))
```

--------------------------------

### REPL Session: Database Setup and Operations

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Demonstrates connecting to an H2 database, creating a table, inserting data, and querying it within a Clojure REPL session.

```clojure
> clj
Clojure 1.12.5
user=> (require '[next.jdbc :as jdbc])
nil
user=> (def db {:dbtype "h2" :dbname "example"})
#'user/db
user=> (def ds (jdbc/get-datasource db))
#'user/ds
user=> (jdbc/execute! ds ["\ncreate table address (\n  id int auto_increment primary key,\n  name varchar(32),\n  email varchar(255)\n)"])
[#:next.jdbc{:update-count 0}]
user=> (jdbc/execute! ds ["\ninsert into address(name,email)\n  values('Sean Corfield','sean@corfield.org')"])
[#:next.jdbc{:update-count 1}]
user=> (jdbc/execute! ds ["select * from address"])
[#:ADDRESS{:ID 1, :NAME "Sean Corfield", :EMAIL "sean@corfield.org"}]
user=>
```

--------------------------------

### Find by keys with LIKE and IN clauses

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Demonstrates using `find-by-keys` with `LIKE` for pattern matching and `IN` for multiple value checks. These examples show how to construct more flexible WHERE clauses.

```clojure
(sql/find-by-keys ds :address ["email LIKE ?" "%.beer"])
```

```clojure
(jdbc/execute! ds ["SELECT * FROM address WHERE email LIKE ?" "%.beer"])
```

```clojure
(sql/find-by-keys ds :address ["name IN (?,?)" "Stella" "Waldo"])
```

```clojure
(jdbc/execute! ds ["SELECT * FROM address WHERE name IN (?,?)" "Stella" "Waldo"])
```

--------------------------------

### Select-one! with a Function

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Example of using `next.jdbc.plan/select-one!` with a custom function to transform the first row of a query result.

```clojure
user=> (reduce (fn [_ row] (reduced (f row))) nil (jdbc/plan ...))
```

--------------------------------

### Using `with-options` for Default Settings

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Wrap a datasource with `with-options` to apply default settings to subsequent operations. This example uses `rs/as-unqualified-lower-maps` to ensure results are maps with unqualified lowercase keys.

```clojure
(require '[next.jdbc.result-set :as rs])
nil
(def ds-opts (jdbc/with-options ds {:builder-fn rs/as-unqualified-lower-maps}))
#'user/ds-opts
(jdbc/execute-one! ds-opts ["insert into address(name,email)
  values('Someone Else','some@elsewhere.com')
"] {:return-keys true})
{:id 4}
(jdbc/execute-one! ds-opts ["select * from address where id = ?" 4])
{:id 4, :name "Someone Else", :email "some@elsewhere.com"}

```

--------------------------------

### Select! with Vector of Column Names

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Example of using `next.jdbc.plan/select!` with a vector of column names to extract specific columns from query results.

```clojure
user=> (into [] (map #(select-keys % cols)) (jdbc/plan ...))
```

--------------------------------

### Process Result Set with `plan` and `transduce`

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Use `jdbc/plan` with `transduce` for efficient data processing. This example maps each row to the product of its unit price and count, then sums these values.

```clojure
user=> (transduce
         (map #(* (:unit_price %) (:unit_count %)))
         +
         0
         (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
14.67M
```

--------------------------------

### Execute SQL Operations

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Examples of executing SQL operations using functions defined by HugSQL. These calls interact with the provided datasource.

```clojure
(create-characters-table ds)
;;=> [#:next.jdbc{:update-count 0}]
(insert-character ds {:name "Westley", :specialty "love"})
;;=> 1
```

--------------------------------

### Create Pooled Datasource with JDBC URL

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Construct a JDBC URL using next.jdbc.connection/jdbc-url to include extra parameters, then create a pooled datasource. This example shows how to pass :useSSL false to the URL and :username/:password to the pool.

```clojure
(connection/->pool com.zaxxer.hikari.HikariDataSource ; or com.mchange.v2.c3p0.ComboPooledDataSource
                   {:jdbcUrl
                    (connection/jdbc-url {:dbtype "mysql" :dbname "thedb" :useSSL false})
                    :username "dbuser" :password "secret"})
```

--------------------------------

### Get Tables and Views Metadata using on-connection

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

This snippet demonstrates using the `on-connection` macro to abstract away connection management when retrieving database metadata. It automatically handles connection reuse or creation and closing.

```clojure
(on-connection [con ds]
  (-> (.getMetaData con) ; produces java.sql.DatabaseMetaData
      ;; return a java.sql.ResultSet describing all tables and views:
      (.getTables nil nil nil (into-array ["TABLE" "VIEW"]))
      (rs/datafiable-result-set ds opts)))
```

--------------------------------

### Select-one! with Vector of Column Names

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Example of using `next.jdbc.plan/select-one!` to extract specific columns from the first row of a query result.

```clojure
user=> (reduce (fn [_ row] (reduced (select-keys row cols))) nil (jdbc/plan ...))
```

--------------------------------

### Compose Transforms with `transduce`

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Combine multiple transformations using `comp` with `transduce` and `jdbc/plan`. This example first extracts unit price and count, then multiplies them, and finally sums the results.

```clojure
user=> (transduce
         (comp (map (juxt :unit_price :unit_count))
               (map #(apply * %)))
         +
         0
         (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
14.67M
```

--------------------------------

### Record-Based Builder Function Example

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/result-set-builders.md

An example of a record-based builder function that can be used to extend next-jdbc's result processing capabilities. This snippet is referenced in the documentation as an illustration of custom builder implementation.

```clojure
(defrecord RecordBasedBuilder [rs cols rsmeta] 
  clojure.lang.ILookup
  (valAt [this key]
    (valAt this key nil))
  (valAt [this key default-val]
    (case key
      :rs rs
      :cols cols
      :rsmeta rsmeta
      default-val)))

(defn record-based-builder-fn
  [rs opts]
  (let [^java.sql.ResultSet rs rs
        cols (vec (mapv #(.getName %) (.getMetaData rs)))
        rsmeta (.getMetaData rs)]
    (->RecordBasedBuilder rs cols rsmeta)))

```

--------------------------------

### Custom Column Reader with Clob Expansion

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/result-set-builders.md

An example of a custom column reader, `clob-column-reader`, that expands `java.sql.Clob` values into strings. This is used with `as-maps-adapter`.

```clojure
    {:builder-fn (result-set/as-maps-adapter
                  result-set/as-maps
                  result-set/clob-column-reader)}
```

--------------------------------

### Read BLOB Data as byte[]

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/tips-and-tricks.md

Provides an example for reading BLOB data as a byte array. Note that BLOB uses 1-based indexing for bytes.

```clojure
(.getBytes v 1 (.length v)) ; BLOB has 1-based byte index!
```

--------------------------------

### Query with Qualified Maps

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Example of querying data using a datasource and a HugSQL function that returns qualified hash maps due to a custom builder function.

```clojure
;; add require next.jdbc.result-set :as rs to your ns

(character-by-id ds {:id 1})
;;=> #:CHARACTERS{:ID 1, :NAME "Westley", :SPECIALTY "love", :CREATED_AT #inst "2019-09-27T18:52:54.413000000-00:00"}
```

--------------------------------

### Batch Insertion with insert-multi! and :batch true (Vector of Hash Maps)

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Performs a batch insertion using `execute-batch!` when `:batch true` is specified, starting from a vector of hash maps. Note that key/generated key return behavior may vary by database.

```clojure
(sql/insert-multi! ds :address
  [{:name "Stella", :email "stella@artois.beer"}
   {:name "Waldo", :email "waldo@lagunitas.beer"}
   {:name "Aunt Sally", :email "sour@lagunitas.beer"}]
  {:batch true})
```

```clojure
(jdbc/execute-batch! ds
                     "INSERT INTO address (name,email) VALUES (?,?)"
                     [["Stella" "stella@artois.beer"]
                      ["Waldo" "waldo@lagunitas.beer"]
                      ["Aunt Sally" "sour@lagunitas.beer"]]
                     {:return-keys true :return-generated-keys true})
```

--------------------------------

### Querying JSON/JSONB Columns

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/tips-and-tricks.md

Shows how to retrieve data from 'json' and 'jsonb' columns, which are automatically converted back to Clojure data structures. Also includes an example of querying based on a value within a JSON field.

```clojure
(sql/get-by-id ds :demo 1)
=> #:demo{:id 1,
          :doc_json
          {:some-key "some val",
           :nested {:a 1},
           :vector [1 2 3],
           :null-val nil},
          :doc_jsonb
          {:some-key "some val",
           :nested {:a 1},
           :vector [1 2 3],
           :null-val nil}}

(sql/get-by-id ds :demo 2)
=> #:demo{:id 2,
          :doc_json [{:a 1} nil 2 "lalala" []],
          :doc_jsonb [{:a 1} nil 2 "lalala" []]}

;; Query by value of JSON field 'some-key'
(sql/query ds [(str "select id, doc_jsonb::json->'nested' as foo"
                    "  from demo where doc_jsonb::json->>'some-key' = ?")
               "some val"])
=> [{:demo/id 1, :foo {:a 1}}]
```

--------------------------------

### Get Tables and Views Metadata from Connection

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Use this snippet to retrieve a list of tables and views from a database connection. It requires an active `java.sql.Connection` and processes the `ResultSet` using `rs/datafiable-result-set`.

```clojure
(with-open [con (p/get-connection ds opts)]
  (-> (.getMetaData con) ; produces java.sql.DatabaseMetaData
      ;; return a java.sql.ResultSet describing all tables and views:
      (.getTables nil nil nil (into-array ["TABLE" "VIEW"]))
      (rs/datafiable-result-set ds opts)))
```

--------------------------------

### Create DataSource and Execute SQL with next.jdbc

Source: https://github.com/seancorfield/next-jdbc/blob/develop/README.md

Demonstrates creating a DataSource, obtaining a Connection, and executing SQL statements using `execute!` and `plan`. Resources are managed using `with-open`.

```clojure
(let [my-datasource (jdbc/get-datasource {:dbtype "..." :dbname "..." ...})]
  (with-open [connection (jdbc/get-connection my-datasource)]
    (jdbc/execute! connection [...])
    (reduce my-fn init-value (jdbc/plan connection [...]))
    (jdbc/execute! connection [...])))
```

--------------------------------

### Setting Parameters on a Prepared Statement

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/prepared-statements.md

Shows how to create a prepared statement and then set its parameters using `set-parameters`. This is useful when you have the prepared statement object and need to bind parameters before execution.

```clojure
;; assuming require next.jdbc.prepare :as p
(with-open [con (jdbc/get-connection ds)
            ps  (jdbc/prepare con ["..."])]
  (jdbc/execute-one! (p/set-parameters ps [...])))
```

--------------------------------

### Executing with Prepared Statement and Options

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/prepared-statements.md

Demonstrates how to execute a prepared statement with options. Use `nil` or `[]` as the second argument when passing a prepared statement or statement to `execute-one!` if you need to provide an options map.

```clojure
(with-open [con (jdbc/get-connection ds)]
  (with-open [ps (jdbc/prepare con ["..."])]
    (jdbc/execute-one! ps nil {...})))
  (with-open [stmt (jdbc/statement con)]
    (jdbc/execute-one! stmt nil {...})))
```

--------------------------------

### Add next.jdbc to project.clj or build.boot

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Include this dependency in your `project.clj` or `build.boot` file to add next.jdbc to your project.

```clojure
[com.github.seancorfield/next.jdbc "1.3.1118"]
```

--------------------------------

### Transaction with Rollback on Exception

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/transactions.md

If an exception is thrown within the `with-transaction` block, the transaction will automatically roll back. This example demonstrates a conditional rollback.

```clojure
(jdbc/with-transaction [tx my-datasource]
  (jdbc/execute! tx ...)
  (when ... (throw ...)) ; will rollback
  (jdbc/execute! tx ...))
```

--------------------------------

### Basic Transaction with Commit

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/transactions.md

Use `with-transaction` to group operations. If no exception is thrown, the transaction will commit. This example assumes `my-datasource` is a valid datasource.

```clojure
(jdbc/with-transaction [tx my-datasource]
  (jdbc/execute! tx ...)
  (jdbc/execute! tx ...))
```

--------------------------------

### Find by keys with pagination

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Implement pagination using `:offset` and `:fetch` for SQL standard pagination, or `:limit` for MySQL/SQLite compatibility. These options add `OFFSET`/`FETCH` or `LIMIT`/`OFFSET` clauses to the query.

```clojure
(sql/find-by-keys ds :address :all {:order-by [:id] :offset 5 :fetch 10})
```

```clojure
(jdbc/execute! ds ["SELECT * FROM address ORDER BY id OFFSET ? ROWS FETCH NEXT ? ROWS ONLY" 5 10])
```

--------------------------------

### Using `prep/statement` for Non-Prepared SQL

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

When a database does not support `PreparedStatement` for certain operations, use `prep/statement` to create a plain `java.sql.Statement` for executing SQL strings without parameters.

```clojure
(require '[next.jdbc.prepare :as prep])

(with-open [con (jdbc/get-connection ds)]
  (jdbc/execute! (prep/statement con) ["...just a SQL string..."])
  (jdbc/execute! con ["...some SQL..." "and" "parameters"]) ; uses PreparedStatement
  (into [] (map :column) (jdbc/plan (prep/statement con) ["..."])))
```

--------------------------------

### Using Predefined Naming Options (snake-kebab)

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Utilize predefined options like `jdbc/snake-kebab-opts` for sophisticated camel-snake-kebab transformations on table and column names. These options handle both insertion and result set transformations, converting database-cased names to Clojure keywords.

```clojure
;; transforms :my-table to my_table as above but will also transform
;; column names; in addition, it will perform the reverse transformation
;; on any results, e.g., turning MySQL's :GENERATED_KEY into :generated-key
(sql/insert! ds :my-table {:some "data"} jdbc/snake-kebab-opts)
```

--------------------------------

### Execute DDL Commands with next.jdbc

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/migration-from-clojure-java-jdbc.md

This function demonstrates how to execute a list of DDL commands using next.jdbc, handling both direct SQL Connections and other connectable types.

```clojure
(defn do-commands [connectable commands]
  (if (instance? java.sql.Connection connectable)
    (with-open [stmt (next.jdbc.prepare/statement connectable)]
      (run! #(.addBatch stmt %) commands)
      (into [] (.executeBatch stmt)))
    (with-open [conn (next.jdbc/get-connection connectable)]
      (do-commands conn commands))))
```

--------------------------------

### Define HugSQL Database Functions

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Define standard SQL functions from a .sql file. The :adapter option specifies the HugSQL adapter for next.jdbc.

```clojure
(hugsql/def-db-fns "db/example.sql"
                   {:adapter (adapter/hugsql-adapter-next-jdbc)})
```

--------------------------------

### Reuse Connection with `with-open`

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Use `with-open` and `next.jdbc/get-connection` to create and reuse a connection for multiple SQL operations, avoiding the overhead of creating a new connection for each operation.

```clojure
(with-open [con (jdbc/get-connection ds)]
  (jdbc/execute! con ...)
  (jdbc/execute! con ...)
  (into [] (map :column) (jdbc/plan con ...)))
```

--------------------------------

### Component Integration with HikariCP Connection Pool

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Integrates HikariCP connection pooling with the Component library. The `connection/component` function creates a Component-compatible entity that can be started and stopped, and invoked to obtain the DataSource.

```clojure
(ns my.data.program
  (:require [com.stuartsierra.component :as component]
            [next.jdbc :as jdbc]
            [next.jdbc.connection :as connection])
  (:import (com.zaxxer.hikari HikariDataSource)))

;; HikariCP requires :username instead of :user in the db-spec:
(def ^:private db-spec {:dbtype "..." :dbname "..." :username "..." :password "..."})

(defn -main [& args]
  ;; connection/component takes the same arguments as connection/->pool:
  (let [ds (component/start (connection/component HikariDataSource db-spec))]
    (try
      ;; "invoke" the data source component to get the javax.sql.DataSource:
      (jdbc/execute! (ds) ...)
      (jdbc/execute! (ds) ...)
      ;; can pass the data source component around other code:
      (do-other-stuff ds args)
      (into [] (map :column) (jdbc/plan (ds) ...))
      (finally
        ;; stopping the component will close the connection pool:
        (component/stop ds)))))
```

--------------------------------

### Processing Database Metadata with next.jdbc

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/migration-from-clojure-java-jdbc.md

Demonstrates how to retrieve and process database metadata, specifically table information, using next.jdbc. This approach mirrors the underlying Java DatabaseMetaData calls.

```clojure
(with-open [con (p/get-connection ds opts)]
  (-> (.getMetaData con) ; produces java.sql.DatabaseMetaData
      (.getTables nil nil nil (into-array ["TABLE" "VIEW"]))
      (rs/datafiable-result-set ds opts)))
```

--------------------------------

### Import Connection Pooling Classes

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Import necessary classes for HikariCP or c3p0 when using next.jdbc. No imports are needed for the 'hikari-cp' library.

```clojure
(ns my.main
  (:require [next.jdbc :as jdbc]
            [next.jdbc.connection :as connection])
  (:import (com.zaxxer.hikari HikariDataSource)
           ;; or:
           (com.mchange.v2.c3p0 ComboPooledDataSource PooledDataSource)))
```

--------------------------------

### Custom Table and Column Naming Functions

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Define custom functions for transforming table and column names, for example, to enforce snake_case. This is useful when database schema names differ from Clojure keyword conventions. The function receives the keyword's string representation.

```clojure
(defn snake-case [s] (str/replace s "-" "_"))

(sql/insert! ds :my-table {:some "data"} {:table-fn snake-case})
```

--------------------------------

### Collect Unique Products with `into` and `map`

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Use `jdbc/plan` combined with `into` and `map` to collect unique values from a specific column, such as product names, into a set.

```clojure
user=> (into #{
             } 
             (map :product)
             (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
#{"apple" "banana" "cucumber"}
```

--------------------------------

### Get Record by Primary Key with next-jdbc

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Retrieve a single row from a table by its primary key using `get-by-id`. It accepts the table name, primary key value, and optionally the primary key column name and options. Returns `nil` if no row matches.

```clojure
(sql/get-by-id ds :address 2)
;; equivalent to
(sql/get-by-id ds :address 2 {})
;; equivalent to
(sql/get-by-id ds :address 2 :id {})
;; equivalent to
(jdbc/execute-one! ds ["SELECT * FROM address WHERE id = ?" 2])
```

--------------------------------

### Wrap Datasource for SQL and Parameter Logging

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Use `jdbc/with-logging` with a single logger function to log the SQL and parameters before each database operation. The logger receives the operation symbol and a vector of SQL and parameters.

```clojure
(let [log-ds (jdbc/with-logging ds my-sql-logger)]
  (jdbc/execute! log-ds ["some SQL" "and" "params"])
  ...
  (jdbc/execute! log-ds ["more SQL" "other" "params"])))
```

--------------------------------

### Execute DDL with Bind Parameters in Oracle

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/tips-and-tricks.md

When executing DDL statements in Oracle that contain SQL entities starting with a colon (e.g., :new, :old), use `next.jdbc.prepare/statement` to create a `Statement` object and execute it to avoid 'Missing IN or OUT parameter' errors. Ensure the statement is closed after execution, preferably using `with-open`.

```clojure
;; Assuming 'ds' is your database connection details
(with-open [stmt (prepare/statement ds "CREATE TABLE my_table (id NUMBER)")]
  (jdbc/execute! stmt))
```

--------------------------------

### Process Rows for Side-Effects with `run!`

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Use `jdbc/plan` with `run!` to process rows for side-effects, such as printing the product name, without returning a computed result.

```clojure
user=> (run! #(println (:product %))
             (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
apple
banana
cucumber
nil
```

--------------------------------

### Batching Parameters from a Sequence

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/prepared-statements.md

A more concise way to batch parameters from a sequence using `run!` and `addBatch`.

```clojure
(with-open [con (jdbc/get-connection ds)
            ps  (jdbc/prepare con ["insert into status (id,name) values (?,?)"])]
  (run! #(.addBatch (p/set-parameters ps %))
        [[1 "Approved"] [2 "Rejected"] [3 "New"]])
  (.executeBatch ps)) ; returns int[]
```

--------------------------------

### Batch Execution with Batch Size Option

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/prepared-statements.md

Executes batched parameters in partitions using the `:batch-size` option to manage large sequences.

```clojure
(jdbc/execute-batch! ds
                     "insert into status (id,name) values (?,?)"
                     [[1 "Approved"] [2 "Rejected"] [3 "New"]]
                     {:batch-size 100})
```

--------------------------------

### Add HugSQL Dependencies

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Include these dependencies in your project to use HugSQL with next.jdbc. Check HugSQL documentation for the latest versions.

```clojure
com.layerware/hugsql-core {:mvn/version "0.5.3"}
com.layerware/hugsql-adapter-next-jdbc {:mvn/version "0.5.3"}
```

--------------------------------

### Managing Options with Explicit Connections

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

When explicitly managing connections or transactions, use `jdbc/with-options` to apply options to connections and transactions separately. This ensures options are correctly scoped.

```clojure
(with-open [con (jdbc/get-connection ds)]
  (let [con-opts (jdbc/with-options con some-options)]
    (jdbc/execute! con-opts ...) ; auto-committed

    (jdbc/with-transaction [tx con-opts] ; will commit or rollback this group:
      (let [tx-opts (jdbc/with-options tx (:options con-opts))]
        (jdbc/execute! tx-opts ...)
        (jdbc/execute! tx-opts ...)
        (into [] (map :column) (jdbc/plan tx-opts ...))))

    (jdbc/execute! con-opts ...))) ; auto-committed
```

--------------------------------

### Wrap Datasource for SQL, Parameter, and Result Logging

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Use `jdbc/with-logging` with two logger functions to log SQL, parameters, and operation results. The second logger receives the operation symbol, the state from the first logger, and the result or exception.

```clojure
(let [log-ds (jdbc/with-logging ds my-sql-logger my-result-logger)]
  (jdbc/execute! log-ds ["some SQL" "and" "params"])
  ...
  (jdbc/execute! log-ds ["more SQL" "other" "params"])))
```

--------------------------------

### Create Table with next-jdbc

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Use `execute-one!` to run DDL statements like CREATE TABLE. It returns a map indicating the update count.

```clojure
user=> (jdbc/execute-one! ds ["\ncreate table invoice (\n  id int auto_increment primary key,\n  product varchar(32),\n  unit_price decimal(10,2),\n  unit_count int unsigned,\n  customer_id int unsigned\n)"])
#:next.jdbc{:update-count 0}
```

--------------------------------

### Define HugSQL SQLvec Functions

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Define functions that produce SQL and parameters for use with jdbc/execute!. This is useful for development and advanced usage.

```clojure
(hugsql/def-sqlvec-fns "db/example.sql"
                       {:adapter (adapter/hugsql-adapter-next-jdbc)})
```

--------------------------------

### Manual Batching of Parameters

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/prepared-statements.md

Manually setting parameters, adding them to a batch, and executing the batch. Requires explicit Java interop.

```clojure
;; assuming require next.jdbc.prepare :as p
(with-open [con (jdbc/get-connection ds)
            ps  (jdbc/prepare con ["insert into status (id,name) values (?,?)"])]
  (p/set-parameters ps [1 "Approved"])
  (.addBatch ps)
  (p/set-parameters ps [2 "Rejected"])
  (.addBatch ps)
  (p/set-parameters ps [3 "New"])
  (.addBatch ps)
  (.executeBatch ps)) ; returns int[]
```

--------------------------------

### Add next.jdbc to deps.edn

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Include this dependency in your `deps.edn` file to add next.jdbc to your project.

```clojure
com.github.seancorfield/next.jdbc {:mvn/version "1.3.1118"}
```

--------------------------------

### Per-object SettableParameter via metadata

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/prepared-statements.md

Demonstrates how to extend the SettableParameter protocol on a per-object basis using metadata.

```clojure
(with-meta obj {'next.jdbc.prepare/set-parameter (fn [v ps i]...)})
```

--------------------------------

### Enable Date/Time Conversions with next.jdbc.date-time

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Require the `next.jdbc.date-time` namespace to enable automatic conversions for `java.util.Date` and `java.time` types when interacting with SQL databases. This is useful when the JDBC driver does not handle these conversions automatically.

```clojure
user=> (require '[next.jdbc.date-time])
; nil
```

--------------------------------

### Using `execute-batch!` Helper Function

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/prepared-statements.md

Utilizes the `execute-batch!` helper function for simplified batch execution without manual boilerplate.

```clojure
(with-open [con (jdbc/get-connection ds)
            ps  (jdbc/prepare con ["insert into status (id,name) values (?,?)"])]
  (jdbc/execute-batch! ps [[1 "Approved"] [2 "Rejected"] [3 "New"]))
;; or:
(jdbc/execute-batch! ds
                     "insert into status (id,name) values (?,?)"
                     [[1 "Approved"] [2 "Rejected"] [3 "New"]]
                     ;; options hash map required here to disambiguate
                     ;; this call from the 2- & 3-arity calls
                     {})
```

--------------------------------

### Reduce Result Set with `plan` and `reduce`

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Use `jdbc/plan` to prepare a query without executing it. The returned reducible collection is then processed by `reduce` to perform the SQL execution and computation. The connection is automatically closed upon completion of the reduction.

```clojure
user=> (reduce
         (fn [cost row] 
           (+ cost (* (:unit_price row)
                      (:unit_count row))))
         0
         (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
14.67M
```

--------------------------------

### Select Multiple Rows with next-jdbc

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Use `jdbc/plan` or `plan/select!` to retrieve multiple rows from a query. You can specify a transducer to transform the results, such as mapping to a specific key or collecting into a set.

```clojure
user=> (require '[next.jdbc.plan :as plan])
nil
```

```clojure
user=> (into #{} 
             (map :product) 
             (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
#{"apple" "banana" "cucumber"}
```

```clojure
user=> (plan/select! ds
                     :product
                     ["select * from invoice where customer_id = ?" 100]
                     {:into #{}}) ; product a set, rather than a vector
#{"apple" "banana" "cucumber"}
```

```clojure
user=> (into []
             (map #(select-keys % [:id :product :unit_price :unit_count :customer_id]))
             (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
```

```clojure
user=> (plan/select! ds
                     [:id :product :unit_price :unit_count :customer_id]
                     ["select * from invoice where customer_id = ?" 100])
```

```clojure
user=> (into []
             (map #(select-keys % [:invoice/id :invoice/product
                                   :invoice/unit_price :invoice/unit_count
                                   :invoice/customer_id]))
             (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
```

```clojure
user=> (plan/select! ds
                     [:invoice/id :invoice/product
                      :invoice/unit_price :invoice/unit_count
                      :invoice/customer_id]
                     ["select * from invoice where customer_id = ?" 100])
```

```clojure
user=> (into []
             (map #(select-keys % [:foo/id :bar/product
                                   :quux/unit_price :wibble/unit_count
                                   :blah/customer_id]))
             (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
```

```clojure
user=> (plan/select! ds
                     [:foo/id :bar/product
                      :quux/unit_price :wibble/unit_count
                      :blah/customer_id]
                     ["select * from invoice where customer_id = ?" 100])
```

--------------------------------

### Transaction with Existing Connection

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

When `with-transaction` is given an existing `Connection` object, it sets up a transaction on that connection. The transaction will commit or rollback based on the execution outcome, and the connection remains open.

```clojure
(with-open [con (jdbc/get-connection ds)]
  (jdbc/execute! con ...) ; auto-committed

  (jdbc/with-transaction [tx con] ; will commit or rollback this group:
    ;; note: tx is bound to the same Connection object as con
    (jdbc/execute! tx ...)
    (jdbc/execute! tx ...)
    (into [] (map :column) (jdbc/plan tx ...)))

  (jdbc/execute! con ...)) ; auto-committed
```

--------------------------------

### Batch Insertion with insert-multi! and :batch true (Vector of Vectors)

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Performs a batch insertion using `execute-batch!` when `:batch true` is specified. Note that key/generated key return behavior may vary by database.

```clojure
(sql/insert-multi! ds :address
  [:name :email]
  [["Stella" "stella@artois.beer"]
   ["Waldo" "waldo@lagunitas.beer"]
   ["Aunt Sally" "sour@lagunitas.beer"]]
  {:batch true})
```

```clojure
(jdbc/execute-batch! ds
                     "INSERT INTO address (name,email) VALUES (?,?)"
                     [["Stella" "stella@artois.beer"]
                      ["Waldo" "waldo@lagunitas.beer"]
                      ["Aunt Sally" "sour@lagunitas.beer"]]
                     {:return-keys true :return-generated-keys true})
```

--------------------------------

### Require HugSQL Namespaces

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Add these requires to your namespace to use HugSQL's core and next.jdbc adapter functionalities.

```clojure
[hugsql.core :as hugsql]
[hugsql.adapter.next-jdbc :as adapter]
[next.jdbc :as jdbc]
```

--------------------------------

### SQL Table Definition for JSON/JSONB

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/tips-and-tricks.md

Defines a sample PostgreSQL table named 'demo' with columns for storing both JSON and JSONB data types.

```sql
create table demo (
  id          serial primary key,
  doc_jsonb   jsonb,
  doc_json    json
)
```

--------------------------------

### Find by keys with ordering

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/friendly-sql-functions.md

Sort the query results using the `:order-by` option, which takes a vector of column names or column-alias pairs with sort directions.

```clojure
(sql/find-by-keys ds :address
                  {:name "Stella" :email "stella@artois.beer"}
                  {:order-by [[:id :desc]]})
```

```clojure
(jdbc/execute! ds ["SELECT * FROM address WHERE name = ? AND email = ? ORDER BY id DESC"
                   "Stella" "stella@artois.beer"])
```

--------------------------------

### Create HikariCP Pooled Datasource with DataSource Properties

Source: https://github.com/seancorfield/next-jdbc/blob/develop/doc/getting-started.md

Create a HikariCP pooled datasource, specifying database connection details and additional properties for the underlying DataSource, such as socket timeout.

```clojure
(connection/->pool com.zaxxer.hikari.HikariDataSource
                   {:dbtype "postgres" :dbname "thedb" :username "dbuser" :password "secret"
                    :dataSourceProperties {:socketTimeout 30}})
```