### Run ScalikeJDBC Website Locally

Source: https://scalikejdbc.org/documentation/1.x/contribution

This snippet provides the commands to set up and run the ScalikeJDBC website locally for debugging and development. It requires cloning the repository, installing dependencies using Bundler, and starting the Middleman server.

```shell
git clone [your forked repository].
cd scalikejdbc.github.io
gem install bundler
bundle install
bundle exec middleman server
```

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

### Install dbconsole via batch file

Source: https://scalikejdbc.org/documentation/setup

Download and install the dbconsole utility on Windows using the provided batch file.

```Batch
https://git.io/dbcon.bat
```

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

### dbconsole Usage Example

Source: https://scalikejdbc.org/documentation/2.x/dbconsole

Demonstrates how to launch the dbconsole for a specific sandbox database. It shows the sbt console startup and interaction with Scala code for database operations.

```bash
$ dbconsole sandbox
```

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

### Setup PostgreSQL with ScalikeJDBC

Source: https://scalikejdbc.org/documentation/configuration

Demonstrates how to set up PostgreSQL connection pools using ScalikeJDBC's DBs.setupAll(). It also shows how to perform read-only operations and close all connections.

```scala
import scalikejdbc._
import scalikejdbc.config._

// DBs.setup/DBs.setupAll loads specified JDBC driver classes.
DBs.setupAll()
// DBs.setup()
// DBs.setup("legacy")
// // Unlike DBs.setupAll(), DBs.setup() doesn't load configurations under global settings automatically
// DBs.loadGlobalSettings()

// loaded from "db.default.*"
val memberIds = DB readOnly { implicit session =>
  sql"select id from members".map(_.long(1)).list.apply()
}
// loaded from "db.legacy.*"
val legacyMemberIds = NamedDB("legacy") readOnly { implicit session =>
  sql"select id from members".map(_.long(1)).list.apply()
}

// wipes out ConnectionPool
DBs.closeAll()
```

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

### Install dbconsole via curl

Source: https://scalikejdbc.org/documentation/setup

Download and install the dbconsole utility on Mac OS X and Linux using curl.

```Shell
curl -L https://git.io/dbcon | sh
```

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

### dbconsole for Windows

Source: https://scalikejdbc.org/documentation/2.x/setup

This provides the URL to download the dbconsole executable for Windows systems. dbconsole is a simple tool for connecting to databases via JDBC.

```Batch
http://git.io/dbcon.bat
```

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

### Add ScalikeJDBC Testing Support

Source: https://scalikejdbc.org/documentation/2.x/setup

This snippet includes the ScalikeJDBC testing support library, along with the core library, H2 driver, and logback. Using `scalikejdbc-test` is recommended for improving application testing.

```Scala
libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"        % "2.5.2",
  "org.scalikejdbc" %% "scalikejdbc-test"   % "2.5.2"   % "test",
  "com.h2database"  %  "h2"                 % "2.2.224",
  "ch.qos.logback"  %  "logback-classic"    % "1.5.6"
)
```

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

### Add ScalikeJDBC Typesafe Config Reader

Source: https://scalikejdbc.org/documentation/2.x/setup

This snippet adds the `scalikejdbc-config` library, which is useful if you are using `application.conf` for your settings. It includes the core library, H2 driver, and logback.

```Scala
libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"         % "2.5.2",
  "org.scalikejdbc" %% "scalikejdbc-config"  % "2.5.2",
  "com.h2database"  %  "h2"                  % "2.2.224",
  "ch.qos.logback"  %  "logback-classic"     % "1.5.6"
)
```

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

### Initializing ScalikeJDBC Connection Pool

Source: https://scalikejdbc.org/documentation/configuration

Provides examples of initializing a ScalikeJDBC `ConnectionPool` using `ConnectionPool.singleton` and `ConnectionPool.add`. It also shows how to configure pool settings like initial size, max size, and validation query.

```Scala
import scalikejdbc._

// after loading JDBC drivers
ConnectionPool.singleton(url, user, password)
ConnectionPool.add("foo", url, user, password)

val settings = ConnectionPoolSettings(
  initialSize = 5,
  maxSize = 20,
  connectionTimeoutMillis = 3000L,
  validationQuery = "select 1 from dual")

// all the connections are released, old connection pool will be abandoned
ConnectionPool.add("foo", url, user, password, settings)
```

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

### Import ScalikeJDBC in Application

Source: https://scalikejdbc.org/documentation/2.x/setup

This code snippet demonstrates the necessary import statement to use ScalikeJDBC functionalities within your Scala application.

```Scala
import scalikejdbc._
```

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

### Configuration File for scalikejdbc-config

Source: https://scalikejdbc.org/documentation/1.x/configuration

Provides an example of an `application.conf` file for `scalikejdbc-config`, demonstrating how to define JDBC driver, URL, user, password, and connection pool settings for default and legacy configurations.

```HOCON
# JDBC settings
db.default.driver="org.h2.Driver"
db.default.url="jdbc:h2:file:./db/default"
db.default.user="sa"
db.default.password=""

# Connection Pool settings
db.default.poolInitialSize=5
db.default.poolMaxSize=7
db.default.poolConnectionTimeoutMillis=1000
db.default.poolValidationQuery="select 1 as one"

db.legacy.driver="org.h2.Driver"
db.legacy.url="jdbc:h2:file:./db/db2"
db.legacy.user="foo"
db.legacy.password="bar"
```

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

### Setup ScalikeJDBC with Testing Support

Source: https://scalikejdbc.org/documentation/setup

Include the ScalikeJDBC core library, the testing support module, H2 database driver, and Logback classic for testing purposes.

```Scala
libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"        % "4.3.5",
  "org.scalikejdbc" %% "scalikejdbc-test"   % "4.3.5"   % "test",
  "com.h2database"  %  "h2"                 % "2.2.224",
  "ch.qos.logback"  %  "logback-classic"    % "1.5.6"
)
```

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

### Configure ScalikeJDBC with Environment

Source: https://scalikejdbc.org/documentation/2.x/configuration

Demonstrates how to configure ScalikeJDBC for different environments (e.g., development, production) using environment prefixes or nested configurations. It utilizes `DBsWithEnv` for environment-specific setups.

```Scala
DBsWithEnv("development").setupAll()
DBsWithEnv("prod").setup('sandbox)
```

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

### Production Sandbox Environment Configuration

Source: https://scalikejdbc.org/documentation/configuration

Defines the database configuration for a 'sandbox' environment within the production setup, using an H2 database.

```properties
prod {
  db {
    sandbox {
      driver="org.h2.Driver"
      url="jdbc:h2:file:./are-you-sure-in-production"
      user="user"
      password="pass"
    }
  }
}
```

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

### Download dbconsole for Mac/Linux

Source: https://scalikejdbc.org/documentation/2.x/setup

This command downloads and executes the dbconsole script using curl for Linux and Mac OS X systems. dbconsole is a simple tool for connecting to databases via JDBC.

```Shell
curl -L http://git.io/dbcon | sh
```

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

### Setup Core ScalikeJDBC Library

Source: https://scalikejdbc.org/documentation/setup

Add the ScalikeJDBC core library, H2 database driver, and Logback classic for logging to your project's build.sbt file.

```Scala
libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"       % "4.3.5",
  "com.h2database"  %  "h2"                % "2.2.224",
  "ch.qos.logback"  %  "logback-classic"   % "1.5.6"
)
```

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

### ScalikeJDBC: Insert, Update, Delete, and Return Keys

Source: https://scalikejdbc.org/documentation/1.x/operations

Demonstrates performing insert, update, and delete operations on the database. Includes examples for returning generated keys after an insert and using both SQL interpolation and QueryDSL for these operations.

```Scala
import scalikejdbc._, SQLInterpolation._

DB localTx { implicit session =>
  sql"insert into emp (id, name, created_at) values (${id}, ${name}, ${DateTime.now})"
    .update.apply()
  val id = sql"insert into emp (name, created_at) values (${name}, current_timestamp)"
    .updateAndReturnGeneratedKey.apply()
  sql"update emp set name = ${newName} where id = ${id}".update.apply()
  sql"delete emp where id = ${id}".update.apply()
}

val column = Emp.column
DB localTx { implicit s =>
  withSQL {
    insert.into(Emp).namedValues(
      column.id -> id,
      column.name -> name,
      column.createdAt -> DateTime.now)
   }.update.apply()

  val id: Long = withSQL {
    insert.into(Empy).namedValues(column.name -> name, column.createdAt -> sqls.currentTimestamp)
  }.updateAndReturnGeneratedKey.apply()

  withSQL { update(Emp).set(column.name -> newName).where.eq(column.id, id) }.update.apply()
  withSQL { delete.from(Emp).where.eq(column.id, id) }.update.apply()
}
```

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

### Configure ScalikeJDBC Mapper Generator Plugin

Source: https://scalikejdbc.org/documentation/2.x/setup

This section shows how to set up the `scalikejdbc-mapper-generator` sbt plugin for reverse engineering Scala code from a database. It includes adding the plugin to `project/plugins.sbt` and configuring database and generator settings in `build.sbt` and `project/scalikejdbc.properties`.

```Scala
// Don't forget adding your JDBC driver
libraryDependencies += "org.hsqldb" % "hsqldb" % "2.3.2"

addSbtPlugin("org.scalikejdbc" %% "scalikejdbc-mapper-generator" % "2.5.2")
```

```Scala
scalikejdbcSettings
```

```Properties
# ---
# jdbc settings

jdbc.driver=org.h2.Driver
jdbc.url=jdbc:h2:file:./db/hello
jdbc.username=sa
jdbc.password=
jdbc.schema=

# ---
# source code generator settings

generator.packageName=models
# generator.lineBreak: LF/CRLF
generator.lineBreak=LF
# generator.template: interpolation/queryDsl
generator.template=queryDsl
# generator.testTemplate: specs2unit/specs2acceptance/ScalaTestFlatSpec
generator.testTemplate=specs2unit
generator.encoding=UTF-8
# When you're using Scala 2.11 or higher, you can use case classes for 22+ columns tables
generator.caseClassOnly=true
# Set AutoSession for implicit DBSession parameter's default value
generator.defaultAutoSession=true
# Use autoConstruct macro (default: false)
generator.autoConstruct=false
# joda-time (org.joda.time.DateTime) or JSR-310 (java.time.ZonedDateTime java.time.OffsetDateTime)
generator.dateTimeClass=org.joda.time.DateTime
```

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

### Build Sub-queries with Aggregation

Source: https://scalikejdbc.org/documentation/2.x/query-dsl

Demonstrates constructing complex queries involving subqueries, aliasing, aggregation (`sum`), grouping, filtering groups (`having`), and ordering. This example finds preferred clients based on total order amounts.

```scala
import SQLSyntax.{ sum, gt }
val x = SubQuery.syntax("x").include(o, p)
val preferredClients: List[(Int, Int)] = withSQL {
  select(sqls"${x(o).accountId} id", sqls"${sum(x(p).price)} as amount")
    .from { select.all(o, p).from(Order as o).innerJoin(Product as p).on(o.productId, p.id).as(x) }
    .groupBy(x(o).accountId)
    .having(gt(sum(x(p).price), 300))
    .orderBy(sqls"amount")
  }.map(rs => (rs.int("id"), rs.int("amount"))).list.apply()

// select x.account_id id, sum(x.price) as amount 
// from (select o.*, p.* from orders o inner join products p on o.product_id = p.id) x
// group by x.account_id
// having sum(x.price) > ?
// order by amount
```

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

### ScalikeJDBC Configuration (HOCON)

Source: https://scalikejdbc.org/documentation/2.x/configuration

Provides examples of database configuration settings using HOCON format for the scalikejdbc-config module. It includes settings for driver, URL, user, password, and connection pool parameters for different database configurations.

```HOCON
# JDBC settings
db.default.driver="org.h2.Driver"
db.default.url="jdbc:h2:file:./db/default"
db.default.user="sa"
db.default.password=""
# Connection Pool settings
db.default.poolInitialSize=10
db.default.poolMaxSize=20
db.default.connectionTimeoutMillis=1000

# Connection Pool settings
db.default.poolInitialSize=5
db.default.poolMaxSize=7
db.default.poolConnectionTimeoutMillis=1000
db.default.poolValidationQuery="select 1 as one"
db.default.poolFactoryName="commons-dbcp"

db.legacy.driver="org.h2.Driver"
db.legacy.url="jdbc:h2:file:./db/db2"
db.legacy.user="foo"
db.legacy.password="bar"

# MySQL example
db.default.driver="com.mysql.jdbc.Driver"
db.default.url="jdbc:mysql://localhost/scalikejdbc"
```

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

### Add ScalikeJDBC Core Library and Dependencies

Source: https://scalikejdbc.org/documentation/2.x/setup

This snippet shows how to add the core ScalikeJDBC library, an H2 database driver, and a logback implementation to your build.sbt file. It's essential for basic ScalikeJDBC usage. Note that `scalikejdbc-interpolation` is not compatible with Scala 2.9.

```Scala
libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"       % "2.5.2",
  "com.h2database"  %  "h2"                % "2.2.224",
  "ch.qos.logback"  %  "logback-classic"   % "1.5.6"
)
```

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

### dbconsole Installation Script (Windows)

Source: https://scalikejdbc.org/documentation/2

This provides the URL to download the dbconsole installation script for Windows systems. dbconsole is an extended sbt console for connecting to databases.

```Batch
http://git.io/dbcon.bat

```

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

### ScalikeJDBC Reverse Engineering sbt Plugin Setup

Source: https://scalikejdbc.org/documentation/setup

Configure sbt plugins for ScalikeJDBC reverse engineering, including the JDBC driver and the mapper generator plugin.

```Scala
// Don't forget adding your JDBC driver
libraryDependencies += "org.hsqldb" % "hsqldb" % "2.3.2"

addSbtPlugin("org.scalikejdbc" %% "scalikejdbc-mapper-generator" % "4.3.5")
```

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

### dbconsole Installation Script

Source: https://scalikejdbc.org/documentation/1

Shows how to install the dbconsole tool using a curl command for Mac OS X and Linux systems. It also provides the URL for the Windows batch script.

```Shell
curl -L http://git.io/dbcon | sh
```

```Batch
http://git.io/dbcon.bat
```

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

### ScalikeJDBC Basic Setup and Operations

Source: https://scalikejdbc.org/documentation/index

This snippet demonstrates the initial setup of ScalikeJDBC, including JDBC driver initialization and connection pool configuration. It shows how to create a table, insert data, and retrieve it using both raw SQL and a custom ORM-like mapping.

```Scala
import scalikejdbc._

// initialize JDBC driver & connection pool
Class.forName("org.h2.Driver")
ConnectionPool.singleton("jdbc:h2:mem:hello", "user", "pass")

// ad-hoc session provider on the REPL
implicit val session: DBSession = AutoSession

// table creation, you can run DDL by using #execute as same as JDBC
sql"""
create table members (
  id serial not null primary key,
  name varchar(64),
  created_at timestamp not null
)
""".execute.apply()

// insert initial data
Seq("Alice", "Bob", "Chris") foreach {
  name =>
    sql"insert into members (name, created_at) values (${name}, current_timestamp)".update.apply()
}

// for now, retrieves all data as Map value
val entities: List[Map[String, Any]] = sql"select * from members".map(_.toMap).list.apply()

// defines entity object and extractor
import java.time._
case class Member(id: Long, name: Option[String], createdAt: ZonedDateTime)
object Member extends SQLSyntaxSupport[Member] {
  override val tableName = "members"
  def apply(rs: WrappedResultSet) = new Member(
    rs.long("id"), rs.stringOpt("name"), rs.zonedDateTime("created_at"))
}

// find all members
val members: List[Member] = sql"select * from members".map(rs => Member(rs)).list.apply()

// use paste mode (:paste) on the Scala REPL
val m = Member.syntax("m")
val name = "Alice"
val alice: Option[Member] = withSQL {
  select.from(Member as m).where.eq(m.name, name)
}.map(rs => Member(rs)).single.apply()
```

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

### ScalikeJDBC NamedAutoSession Example

Source: https://scalikejdbc.org/documentation/2.x/auto-session

Demonstrates how to use NamedAutoSession with a named database connection in ScalikeJDBC.

```Scala
def findById(id: Long)(implicit session: DBSession = NamedAutoSession('named)) =
  sql"select id, name from members where id = ${id}"
```

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

### ScalikeJDBC: Basic Setup and CRUD Operations

Source: https://scalikejdbc.org/documentation/2

Demonstrates initializing ScalikeJDBC, creating a table, inserting data, and retrieving data using both raw SQL and a type-safe entity mapping. It shows how to use `AutoSession` for ad-hoc operations and defines a `Member` entity with a custom extractor.

```Scala
import scalikejdbc._

// initialize JDBC driver & connection pool
Class.forName("org.h2.Driver")
ConnectionPool.singleton("jdbc:h2:mem:hello", "user", "pass")

// ad-hoc session provider on the REPL
implicit val session = AutoSession

// table creation, you can run DDL by using #execute as same as JDBC
sql"""
create table members (
  id serial not null primary key,
  name varchar(64),
  created_at timestamp not null
)
""".execute.apply()

// insert initial data
Seq("Alice", "Bob", "Chris") foreach {
  name =>
    sql"insert into members (name, created_at) values (${name}, current_timestamp)".update.apply()
}

// for now, retrieves all data as Map value
val entities: List[Map[String, Any]] = sql"select * from members".map(_.toMap).list.apply()

// defines entity object and extractor
import org.joda.time._
case class Member(id: Long, name: Option[String], createdAt: DateTime)
object Member extends SQLSyntaxSupport[Member] {
  override val tableName = "members"
  def apply(rs: WrappedResultSet) = new Member(
    rs.long("id"), rs.stringOpt("name"), rs.jodaDateTime("created_at"))
}

// find all members
val members: List[Member] = sql"select * from members".map(rs => Member(rs)).list.apply()

// use paste mode (:paste) on the Scala REPL
val m = Member.syntax("m")
val name = "Alice"
val alice: Option[Member] = withSQL {
  select.from(Member as m).where.eq(m.name, name)
}.map(rs => Member(rs)).single.apply()
```

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

### SQL query with embedded SQLSyntax

Source: https://scalikejdbc.org/documentation/2.x/sql-interpolation

Presents an example of a generated SQL query where an SQLSyntax fragment, like 'desc', has been directly embedded.

```SQL
select id , name from members limit 10 order by id desc
```

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

### ScalikeJDBC AutoSession Usage Examples

Source: https://scalikejdbc.org/documentation/2.x/auto-session

Shows how the findById method with AutoSession can be called directly or within a transaction block, demonstrating its flexibility.

```Scala
findById(id) // borrows a read-only session and gives it back
DB localTx { implicit session => findById(id) } // using implicit session
```

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

### dbconsole Command-Line Help

Source: https://scalikejdbc.org/documentation/2.x/dbconsole

Displays the help information for the dbconsole command, outlining its general options and usage for connecting to a database profile.

```bash
$ dbconsole -h
```

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

### Scala Column References for Insert/Update Queries

Source: https://scalikejdbc.org/documentation/2.x/sql-interpolation

Provides an example of using `Member.column` to reference column names directly in insert and update SQL statements.

```Scala
val c = Member.column
sql"insert into ${Member.table} (${c.name}, ${c.birthday}) values (${name}, ${birthday})"
  .update.apply()
```

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

### Delete Records with ScalikeJDBC

Source: https://scalikejdbc.org/documentation/1.x/query-dsl

Shows how to delete records from the 'Member' table based on a specific ID using ScalikeJDBC. The example targets members with ID 123.

```Scala
withSQL {
  delete.from(Member).where.eq(Member.column.id, 123)
}.update.apply()

// delete from members where id = ?
```

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

### Setting Up and Using scalikejdbc-config in Scala

Source: https://scalikejdbc.org/documentation/1.x/configuration

Demonstrates how to set up and use `scalikejdbc-config` to manage database connections. It shows how to load configurations using `DBs.setupAll()`, query data using default or named connections, and close all connection pools.

```Scala
import scalikejdbc._, SQLInterpolation._
import scalikejdbc.config._

DBs.setupAll()
// DBs.setup()
// DBs.setup('legacy)

// loaded from "db.default.*"
val memberIds = DB readOnly { implicit session =>
  sql"select id from members".map(_.long(1)).list.apply()
}
// loaded from "db.legacy.*"
val legacyMemberIds = NamedDB('legacy) readOnly {
  sql"select id from members".map(_.long(1)).list.apply()
}

// wipes out ConnectionPool
DBs.closeAll()
```

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

### Build Query with IN Clause

Source: https://scalikejdbc.org/documentation/2.x/query-dsl

Provides an example of using the `in` clause to filter records based on a list of values. This is a common way to specify multiple possible matches for a column.

```scala
val inClauseResults = withSQL {
  select.from(Order as o).where.in(o.id, Seq(1, 2, 3))
}.map(Order(o)).list.apply()

// select * from orders o where o.id in (?, ?, ?)
```

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

### dbconsole Usage Help

Source: https://scalikejdbc.org/documentation/1.x/dbconsole

Displays the help information for the dbconsole command, outlining its general options and usage for connecting to a database profile.

```Shell
$ dbconsole -h
```

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

### Add ScalikeJDBC Testing Support

Source: https://scalikejdbc.org/documentation/1.x/setup

Include the `scalikejdbc-test` library in your build dependencies for improved application testing. This is highly recommended for better testing practices.

```Scala
libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"               % "1.7.7",
  "org.scalikejdbc" %% "scalikejdbc-interpolation" % "1.7.7",
  "org.scalikejdbc" %% "scalikejdbc-test"          % "1.7.7"   % "test",
  "com.h2database"  %  "h2"                        % "1.4.177",
  "ch.qos.logback"  %  "logback-classic"           % "1.1.2"
)

```

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

### Basic SQL Interpolation Example

Source: https://scalikejdbc.org/documentation/1.x/sql-interpolation

Demonstrates the basic usage of SQLInterpolation in ScalikeJDBC, embedding a Scala variable directly into an SQL query string. This method is safe from SQL injection.

```Scala
import scalikejdbc._, SQLInterpolation._

val id = 123
val member = sql"select id, name from members where id = ${id}"
    .map(rs => Member(rs)).single.apply()
```

```SQL
select id, name from members where id = ?
```

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

### Download and Run dbconsole

Source: https://scalikejdbc.org/documentation/1.x/setup

Download and run the dbconsole utility using curl on Mac OS X and Linux, or by accessing the provided batch file on Windows. This tool allows you to connect to your database via JDBC.

```Shell
curl -L http://git.io/dbcon | sh

```

```Batch
http://git.io/dbcon.bat

```

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

### Update Records with ScalikeJDBC

Source: https://scalikejdbc.org/documentation/1.x/query-dsl

Illustrates how to update records in the 'Member' table using ScalikeJDBC. This example updates the 'name' to 'Chris' and 'updated_at' to the current time for the member with ID 2.

```Scala
withSQL {
  update(Member).set(
    Member.column.name -> "Chris",
    Member.column.updatedAt -> DateTime.now
  ).where.eq(Member.column.id, 2)
}.update.apply()

// update members set name = ?, updated_at = ? 
// where id = ?
```

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

### Manually Define Columns in ScalikeJDBC SQLSyntaxSupport

Source: https://scalikejdbc.org/documentation/2.x/sql-interpolation

Shows how to manually define the column names for an entity in ScalikeJDBC's SQLSyntaxSupport when automatic detection is not desired or when dealing with multi-database setups.

```scala
object GroupMember extends SQLSyntaxSupport[GroupMember] {
  override val tableName = "groups_members"
  override val columns = Seq("id", "name", "group_id")
}
```

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

### Initialize Connection Pool (ScalikeJDBC)

Source: https://scalikejdbc.org/documentation/2.x/configuration

Shows how to initialize a ScalikeJDBC connection pool using ConnectionPool.singleton() or ConnectionPool.add(). It also illustrates how to configure advanced settings like initial size, max size, connection timeout, and validation query.

```Scala
import scalikejdbc._

// after loading JDBC drivers
ConnectionPool.singleton(url, user, password)
ConnectionPool.add('foo, url, user, password)

val settings = ConnectionPoolSettings(
  initialSize = 5,
  maxSize = 20,
  connectionTimeoutMillis = 3000L,
  validationQuery = "select 1 from dual")

// all the connections are released, old connection pool will be abandoned
ConnectionPool.add('foo, url, user, password, settings)
```

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

### Setup ScalikeJDBC with Typesafe Config Reader

Source: https://scalikejdbc.org/documentation/setup

Add the ScalikeJDBC core library, the config reader module, H2 database driver, and Logback classic for projects using application.conf for settings.

```Scala
libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"         % "4.3.5",
  "org.scalikejdbc" %% "scalikejdbc-config"  % "4.3.5",
  "com.h2database"  %  "h2"                  % "2.2.224",
  "ch.qos.logback"  %  "logback-classic"     % "1.5.6"
)
```

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

### Build Group By Queries

Source: https://scalikejdbc.org/documentation/2.x/query-dsl

Provides an example of constructing queries with `groupBy` and aggregate functions like `count`. It also shows how to filter groups using `having` and handle nullable foreign keys.

```scala
import sqls.count
withSQL {
  select(o.accountId, count).from(Order as o)
    .where.isNotNull(o.accountId)
    .groupBy(o.accountId)
}.map(rs => (rs.int(1), rs.int(2))).list.apply()

// select o.account_id, count(1) from orders o
// where o.account_id is not null
// group by o.account_id
```

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

### Configure ScalikeJDBC for Multiple Environments

Source: https://scalikejdbc.org/documentation/configuration

Shows how to manage different database configurations for various environments like 'development' and 'prod' using ScalikeJDBC.

```scala
DBsWithEnv("development").setupAll()
DBsWithEnv("prod").setup("sandbox")
```

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

### Add ScalikeJDBC Dependencies

Source: https://scalikejdbc.org/documentation/1

This snippet shows how to add ScalikeJDBC, its interpolation module, an H2 database driver, and a logback-classic implementation to your sbt build file. These are the essential dependencies for getting started with ScalikeJDBC.

```sbt
libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"               % "1.7.7",
  "org.scalikejdbc" %% "scalikejdbc-interpolation" % "1.7.7",
  "com.h2database"  %  "h2"                        % "1.4.177",
  "ch.qos.logback"  %  "logback-classic"           % "1.1.2"
)

```

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

### ScalikeJDBC SQL placeholder example

Source: https://scalikejdbc.org/documentation/2.x/sql-interpolation

Illustrates the SQL query generated by ScalikeJDBC's SQL interpolation, showing how embedded values are represented as placeholders, ensuring protection against SQL injection.

```SQL
select id, name from members where id = ?
```

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

### ScalikeJDBC Reverse Engineering Configuration Properties

Source: https://scalikejdbc.org/documentation/setup

Configure database connection settings, package name, line break, template, test template, encoding, case class generation, default AutoSession, autoConstruct, and dateTimeClass for ScalikeJDBC reverse engineering.

```Properties
# ---
# jdbc settings

jdbc.driver=org.h2.Driver
jdbc.url=jdbc:h2:file:./db/hello
jdbc.username=sa
jdbc.password=
jdbc.schema=

# ---
# source code generator settings

generator.packageName=models
# generator.lineBreak: LF/CRLF
generator.lineBreak=LF
# generator.template: interpolation/queryDsl
generator.template=queryDsl
# generator.testTemplate: specs2unit/specs2acceptance/ScalaTestFlatSpec
generator.testTemplate=specs2unit
generator.encoding=UTF-8
# When you're using Scala 2.11 or higher, you can use case classes for 22+ columns tables
generator.caseClassOnly=true
# Set AutoSession for implicit DBSession parameter's default value
generator.defaultAutoSession=true
# Use autoConstruct macro (default: false)
generator.autoConstruct=false
# joda-time (org.joda.time.DateTime) or JSR-310 (java.time.ZonedDateTime java.time.OffsetDateTime java.time.LocalDateTime)
generator.dateTimeClass=java.time.ZonedDateTime
```

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

### ScalikeJDBC: Batch Operations

Source: https://scalikejdbc.org/documentation/1.x/operations

Demonstrates how to perform batch inserts using `batch` and `batchByName`, which utilize `java.sql.PreparedStatement#executeBatch()`. This is efficient for inserting multiple rows. Examples are provided for both SQL interpolation and QueryDSL.

```Scala
import scalikejdbc._, SQLInterpolation._

DB localTx { implicit session =>
  val batchParams: Seq[Seq[Any]] = (2001 to 3000).map(i => Seq(i, "name" + i))
  sql"insert into emp (id, name) values (?, ?)".batch(batchParams: _*).apply()
}

val column = Emp.column
DB localTx { implicit session =>
  val batchParams: Seq[Seq[Any]] = (2001 to 3000).map(i => Seq(i, "name" + i))
  withSQL {
    insert.into(Emp).namedValues(column.id -> sqls.?, column.name -> sqls.?)
  }.batch(batchParams: _*).apply()
```

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

### Connect to Sandbox Database with dbconsole

Source: https://scalikejdbc.org/documentation/1.x/dbconsole

Demonstrates how to connect to a 'sandbox' database using the dbconsole command and interact with it using Scala and SQL.

```Shell
$ dbconsole sandbox
```

```Scala
import scalikejdbc._
import scalikejdbc.StringSQLRunner._

tables
describe("iris")
"select count(1) from iris".run
"select count(1) from iris".as[Long]
"select iris_id from iris".asList[Int]
"select species, count(1) from iris group by species".run
"insert into iris values (151, 0.1, 0.2, 0.3, 0.4, 'xxx');".run.asOption[Boolean]
DB localTx { implicit session =>
  "insert into iris values (152, 0.1, 0.2, 0.3, 0.4, 'yyy');".run
  throw new RuntimeException
}
"select count(1) from iris".as[Long]
```

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

### Global Settings Configuration for scalikejdbc-config

Source: https://scalikejdbc.org/documentation/1.x/configuration

Details how to configure global settings for ScalikeJDBC using `scalikejdbc-config`, including enabling logging for SQL time, setting log levels, and configuring warning thresholds and log levels for performance monitoring.

```HOCON
# Global settings
scalikejdbc.global.loggingSQLAndTime.enabled=true
scalikejdbc.global.loggingSQLAndTime.logLevel=info
scalikejdbc.global.loggingSQLAndTime.warningEnabled=true
scalikejdbc.global.loggingSQLAndTime.warningThresholdMillis=1000
scalikejdbc.global.loggingSQLAndTime.warningLogLevel=warn
```

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

### ScalikeJDBC: Map One-to-Optional-One Outer Join

Source: https://scalikejdbc.org/documentation/2.x/one-to-x

Illustrates mapping a one-to-optional-one relationship using an outer join with ScalikeJDBC's `one` and `toOptionalOne` APIs. This example fetches groups and their optional owners.

```Scala
case class Owner(id: Long, name: String)
case class Group(id: Long, name: String,
  ownerId: Option[Long] = None, owner: Option[Owner] = None)

// companion objects must be defined

val (g, o) = (Group.syntax, Owner.syntax)

val groups: Seq[Group] = 
  withSQL { 
    select.from(Group as g).leftJoin(Owner as o).on(g.ownerId, o.id)
  }
  .one(Group(g))
  .toOptionalOne(Owner.opt(o))
  .map { (group, owner) => group.copy(owner = owner) }
  .list
  .apply()
```

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

### ScalikeJDBC: Map One-to-One Inner Join

Source: https://scalikejdbc.org/documentation/2.x/one-to-x

Shows how to map a one-to-one relationship using an inner join with ScalikeJDBC's `one` and `toOne` APIs. This example fetches groups and their associated owners.

```Scala
case class Owner(id: Long, name: String)
case class Group(id: Long, name: String,
  ownerId: Long, owner: Option[Owner] = None)

// companion objects must be defined

val (g, o) = (Group.syntax, Owner.syntax)

val groups: Seq[Group] = 
  withSQL {
    select
      .from(Group as g)
      .innerJoin(Owner as o).on(g.ownerId, o.id)
  }
  .one(Group(g))
  .toOne(Owner(o))
  .map { (group, owner) => group.copy(owner = Some(owner)) }
  .list
  .apply()
```

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

### ScalikeJDBC: Map One-to-Many Relationship

Source: https://scalikejdbc.org/documentation/2.x/one-to-x

Demonstrates how to map a one-to-many relationship using ScalikeJDBC's `one` and `toMany` APIs. This example fetches groups and their associated members, handling optional members.

```Scala
case class Member(id: Long, name: String)
case class Group(id: Long, name: String, members: Seq[Member] = Nil)

object Group extends SQLSyntaxSupport[Group] {
  override val tableName = "groups"
  def apply(g: SyntaxProvider[Group])(rs: WrappedResultSet): Group = apply(g.resultName)(rs)
  def apply(g: ResultName[Group])(rs: WrappedResultSet): Group = new Group(rs.get(g.id), rs.get(g.name))
}

object Member extends SQLSyntaxSupport[Member] {
  override val tableName = "members"
  def apply(m: SyntaxProvider[Member])(rs: WrappedResultSet): Member = apply(m.resultName)(rs)
  def apply(m: ResultName[Member])(rs: WrappedResultSet): Member = 
    new Member(rs.get(m.id), rs.get(m.name))

  def opt(m: SyntaxProvider[Member])(rs: WrappedResultSet): Option[Member] = 
    rs.longOpt(m.resultName.id).map(_ => Member(m)(rs))
}

val (g, m) = (Group.syntax, Member.syntax)

val groups: Seq[Group] = 
  withSQL { select.from(Group as g).leftJoin(Member as m).on(g.id, m.groupId) }
   .one(Group(g))
   .toMany(Member.opt(m))
   .map { (group, members) => group.copy(members = members) }
   .list
   .apply()
```

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

### Build Sub-queries with Aggregation

Source: https://scalikejdbc.org/documentation/query-dsl

Demonstrates constructing complex queries involving subqueries, aliasing, aggregation (`sum`), grouping, filtering groups (`having`), and ordering. This example finds preferred clients based on total order amounts.

```scala
import SQLSyntax.{ sum, gt }
val x = SubQuery.syntax("x").include(o, p)
val preferredClients: List[(Int, Int)] = withSQL {
  select(sqls"${x(o).accountId} id", sqls"${sum(x(p).price)} as amount")
    .from { select.all(o, p).from(Order as o).innerJoin(Product as p).on(o.productId, p.id).as(x) }
    .groupBy(x(o).accountId)
    .having(gt(sum(x(p).price), 300))
    .orderBy(sqls"amount")
  }.map(rs => (rs.int("id"), rs.int("amount"))).list.apply()

// select x.account_id id, sum(x.price) as amount 
// from (select o.*, p.* from orders o inner join products p on o.product_id = p.id) x
// group by x.account_id
// having sum(x.price) > ?
// order by amount
```

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

### Generating Insert Statements with Column References in Scala

Source: https://scalikejdbc.org/documentation/1.x/sql-interpolation

Provides an example of a `create` method that uses `#column` references within an insert statement to dynamically include column names and retrieve the generated key.

```Scala
object Member extends SQLSyntaxSupport[Member] {
  def create(name: String, birthday: LocalDate)(implicit s: DBSession = AutoSession): Member = {
    val id = sql"insert into ${table} (${column.name}, ${column.birthday}) values (${name}, ${birthday})"
      .updateAndReturnGeneratedKey.apply()
    Member(id, name, birthday)
  }
}
```

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

### SQL Query with Result Aliases in Scala

Source: https://scalikejdbc.org/documentation/1.x/sql-interpolation

Provides an example of constructing a SQL query using `m.result.id` and `m.resultName.id` to generate aliased column names in the SQL statement, improving readability and preventing conflicts.

```Scala
val ids: List[Long] = sql"select ${m.result.id} from ${Member.as(m)} where ${m.gourpId} = 1"
  .map(rs => rs.long(m.resultName.id)).list.apply()
```

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

### Configuration using Typesafe Config for scalikejdbc-config

Source: https://scalikejdbc.org/documentation/configuration

Demonstrates how to configure ScalikeJDBC using Typesafe Config in an `application.conf` file. It includes settings for database drivers, URLs, credentials, and connection pool parameters for default and legacy configurations.

```Conf
# JDBC settings
db.default.driver="org.h2.Driver"
db.default.url="jdbc:h2:file:./db/default"
db.default.user="sa"
db.default.password=""

# Connection Pool settings
db.default.poolInitialSize=5
db.default.poolMaxSize=7
# poolConnectionTimeoutMillis defines the amount of time a query will wait to acquire a connection
# before throwing an exception. This used to be called `connectionTimeoutMillis`.

db.default.poolConnectionTimeoutMillis=1000
db.default.poolValidationQuery="select 1 as one"
db.default.poolFactoryName="commons-dbcp2"

db.legacy.driver="org.h2.Driver"
db.legacy.url="jdbc:h2:file:./db/db2"
db.legacy.user="foo"
db.legacy.password="bar"

# MySQL example
db.default.driver="com.mysql.jdbc.Driver"
db.default.url="jdbc:mysql://localhost/scalikejdbc"
```

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

### ScalikeJDBC Query DSL: Basic Select with Join

Source: https://scalikejdbc.org/documentation/query-dsl

Demonstrates a basic select query using ScalikeJDBC's Query DSL, joining multiple tables (Order, Product, Account) with conditions, ordering, limit, and offset. It shows how to map the results to a Scala case class.

```Scala
val o = Order.syntax("o")
val p = Product.syntax("p")
val a = Account.syntax("a")

val orders: List[Order] = withSQL {
  select
    .from(Order as o)
    .innerJoin(Product as p).on(o.productId, p.id)
    .leftJoin(Account as a).on(o.accountId, a.id)
    .where.eq(o.productId, 123)
    .orderBy(o.id).desc
    .limit(4)
    .offset(0)
  }.map(Order(o, p, a)).list.apply()
```

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

### ScalikeJDBC: Create New Record

Source: https://scalikejdbc.org/documentation/reverse-engineering

Demonstrates the process of creating a new record in the database using the `create()` method provided by the ORM model. It takes model attributes as parameters.

```Scala
val created = Member.create(name = "MyString", createdAt = null)
created should not beNull
```