JDBC Integration and Plain SQL

// JDBC Backend implementation
object JdbcBackend extends DatabaseComponent {
  type Database = DatabaseDef
  type Session = SessionDef
  
  // Database definition
  abstract class DatabaseDef {
    def withSession[T](f: Session => T): T
    def withTransaction[T](f: Session => T): T
    def withDynSession[T](f: => T): T
    def withDynTransaction[T](f: => T): T
    def createSession(): Session
    def close(): Unit
  }
  
  // Session definition  
  trait SessionDef extends Closeable {
    def conn: java.sql.Connection
    def metaData: java.sql.DatabaseMetaData
    def capabilities: Set[Capability]
    def withPreparedStatement[T](sql: String)(f: java.sql.PreparedStatement => T): T
    def withStatement[T](f: java.sql.Statement => T): T
    
    // Transaction management
    def rollback(): Unit
    def commit(): Unit
    def isAutoCommit: Boolean
    def setAutoCommit(autoCommit: Boolean): Unit
  }
}

// Database factory methods
object Database {
  def forURL(url: String, driver: String = null, user: String = null, password: String = null, 
             executor: AsyncExecutor = null): Database
  def forDataSource(ds: javax.sql.DataSource, executor: AsyncExecutor = null): Database  
  def forConfig(path: String, config: Config = null, executor: AsyncExecutor = null): Database
  def forDriver(driver: java.sql.Driver, url: String, user: String = null, password: String = null,
                executor: AsyncExecutor = null): Database
  def forName(name: String, executor: AsyncExecutor = null): Database  // JNDI lookup
}

import scala.slick.driver.H2Driver.simple._
import scala.slick.jdbc.JdbcBackend.Database

val database = Database.forURL("jdbc:h2:mem:test1", driver="org.h2.Driver")

// Session management
database withSession { implicit session =>
  // Database operations within session
  users.ddl.create
  users += (1, "John")
}

// Transaction management  
database withTransaction { implicit session =>
  // All operations in transaction
  users += (1, "John")
  orders += (1, 1, "Product")  
  // Automatic rollback on exception
}

// Manual session management
val session = database.createSession() 
try {
  users.ddl.create()(session)
  users += (1, "John")(session)
} finally {
  session.close()
}

// Core JDBC type mapping
trait JdbcType[T] extends TypedType[T] {
  def sqlType: Int
  def sqlTypeName(sym: Option[FieldSymbol]): String
  def getValue(r: ResultSet, idx: Int): T
  def setValue(v: T, p: PreparedStatement, idx: Int): Unit
  def updateValue(v: T, r: ResultSet, idx: Int): Unit
  def hasLiteralForm: Boolean
  def valueToSQLLiteral(value: T): String
}

// Standard type mappings (available implicitly)
implicit val booleanJdbcType: JdbcType[Boolean]
implicit val byteJdbcType: JdbcType[Byte]  
implicit val shortJdbcType: JdbcType[Short]
implicit val intJdbcType: JdbcType[Int]
implicit val longJdbcType: JdbcType[Long]
implicit val floatJdbcType: JdbcType[Float]
implicit val doubleJdbcType: JdbcType[Double]
implicit val stringJdbcType: JdbcType[String]
implicit val bigDecimalJdbcType: JdbcType[BigDecimal]
implicit val dateJdbcType: JdbcType[java.sql.Date]
implicit val timestampJdbcType: JdbcType[java.sql.Timestamp] 
implicit val blobJdbcType: JdbcType[java.sql.Blob]
implicit val clobJdbcType: JdbcType[java.sql.Clob]

// Optional type mappings
implicit def optionJdbcType[T](implicit base: JdbcType[T]): JdbcType[Option[T]]

// Mapped column type factory
object MappedColumnType {
  def base[T, U](to: T => U, from: U => T)(implicit tm: JdbcType[U]): JdbcType[T]
}

import java.util.UUID

// UUID mapping to String
implicit val uuidColumnType = MappedColumnType.base[UUID, String](
  uuid => uuid.toString,
  str => UUID.fromString(str)
)

// Enum mapping
sealed trait Status
case object Active extends Status
case object Inactive extends Status
case object Pending extends Status  

implicit val statusColumnType = MappedColumnType.base[Status, String](
  {
    case Active => "active"
    case Inactive => "inactive" 
    case Pending => "pending"
  },
  {
    case "active" => Active
    case "inactive" => Inactive
    case "pending" => Pending
  }
)

// JSON mapping (requires JSON library)
import play.api.libs.json._

implicit val jsonColumnType = MappedColumnType.base[JsValue, String](
  json => Json.stringify(json),
  str => Json.parse(str)
)

// Usage in tables
class Users(tag: Tag) extends Table[(UUID, String, Status, JsValue)](tag, "users") {
  def id = column[UUID]("id", O.PrimaryKey)
  def name = column[String]("name")
  def status = column[Status]("status") 
  def metadata = column[JsValue]("metadata")
  def * = (id, name, status, metadata)
}

// Static SQL query execution
class StaticQuery[P, R](query: String, pconv: SetParameter[P], rconv: GetResult[R]) extends Invoker[R] {
  def apply(param: P): StaticQueryInvoker[R]
  def list(param: P)(implicit session: Session): List[R]
  def first(param: P)(implicit session: Session): R
  def firstOption(param: P)(implicit session: Session): Option[R]
  def execute(param: P)(implicit session: Session): Int
}

// Factory methods
object StaticQuery {
  def apply[R](query: String)(implicit rconv: GetResult[R]): StaticQuery[Unit, R]
  def apply[P, R](query: String)(implicit pconv: SetParameter[P], rconv: GetResult[R]): StaticQuery[P, R]
  
  // Update queries
  def update[P](query: String)(implicit pconv: SetParameter[P]): StaticQuery[P, Int]
  def updateNA(query: String): StaticQuery[Unit, Int]
}

// SQL interpolation support
implicit class SQLInterpolation(val sc: StringContext) extends AnyVal {
  def sql[R](param: Any*)(implicit rconv: GetResult[R]): StaticQuery[Unit, R]
  def sqlu(param: Any*): StaticQuery[Unit, Int] // Update queries
}

import scala.slick.driver.H2Driver.simple._
import scala.slick.jdbc.{StaticQuery => Q, GetResult}

// Simple query without parameters
val allUsers = sql"SELECT id, name FROM users".as[(Int, String)]

Database.forURL("...") withSession { implicit session =>
  val users: List[(Int, String)] = allUsers.list
}

// Parameterized queries  
def userById(id: Int) = sql"SELECT id, name, email FROM users WHERE id = $id".as[(Int, String, Option[String])]
def usersByStatus(status: String) = sql"SELECT * FROM users WHERE status = $status".as[(Int, String, String)]

Database.forURL("...") withSession { implicit session =>
  val user: Option[(Int, String, Option[String])] = userById(1).firstOption
  val activeUsers: List[(Int, String, String)] = usersByStatus("active").list
}

// Update queries
def updateUserName(id: Int, newName: String) = sqlu"UPDATE users SET name = $newName WHERE id = $id"
def deleteUser(id: Int) = sqlu"DELETE FROM users WHERE id = $id" 

Database.forURL("...") withSession { implicit session =>
  val rowsUpdated = updateUserName(1, "John Doe").execute
  val rowsDeleted = deleteUser(2).execute
}

// Result extraction trait
trait GetResult[T] {
  def apply(rs: PositionedResult): T
}

// Standard GetResult instances
implicit val GetBoolean: GetResult[Boolean]
implicit val GetByte: GetResult[Byte]
implicit val GetInt: GetResult[Int]
implicit val GetLong: GetResult[Long] 
implicit val GetFloat: GetResult[Float]
implicit val GetDouble: GetResult[Double]
implicit val GetString: GetResult[String]
implicit val GetDate: GetResult[java.sql.Date]
implicit val GetTimestamp: GetResult[java.sql.Timestamp]
implicit val GetBigDecimal: GetResult[BigDecimal]

// Option support
implicit def GetOption[T](implicit base: GetResult[T]): GetResult[Option[T]]

// Tuple support (up to 22 elements)
implicit def GetTuple2[T1, T2](implicit g1: GetResult[T1], g2: GetResult[T2]): GetResult[(T1, T2)]
// ... up to GetTuple22

// ResultSet navigation with position tracking
class PositionedResult(val rs: ResultSet) {
  var pos = 0
  
  // Basic value extraction
  def nextBoolean(): Boolean
  def nextByte(): Byte
  def nextShort(): Short  
  def nextInt(): Int
  def nextLong(): Long
  def nextFloat(): Float
  def nextDouble(): Double
  def nextString(): String
  def nextDate(): java.sql.Date
  def nextTimestamp(): java.sql.Timestamp
  def nextBigDecimal(): BigDecimal
  def nextBlob(): java.sql.Blob
  def nextClob(): java.sql.Clob
  
  // Optional value extraction
  def nextBooleanOption(): Option[Boolean]
  def nextIntOption(): Option[Int]
  def nextStringOption(): Option[String]
  // ... other option methods
  
  // Generic extraction
  def nextObject(): AnyRef
  def skip(): Unit
  def restart(): Unit
  
  // Utility methods
  def <<[T](implicit f: GetResult[T]): T = f(this)
}

// Case class result extraction
case class User(id: Int, name: String, email: Option[String], createdAt: java.sql.Timestamp)

implicit val getUserResult = GetResult(r => 
  User(r.nextInt(), r.nextString(), r.nextStringOption(), r.nextTimestamp())
)

// Alternative using <<  operator
implicit val getUserResult2 = GetResult(r => 
  User(r.<<, r.<<, r.<<, r.<<)
)

// Usage
val query = sql"SELECT id, name, email, created_at FROM users"
Database.forURL("...") withSession { implicit session =>
  val users: List[User] = query.as[User].list
}

// Complex extraction with computed fields
case class UserSummary(id: Int, displayName: String, hasEmail: Boolean)

implicit val getUserSummaryResult = GetResult { r =>
  val id = r.nextInt()
  val firstName = r.nextString() 
  val lastName = r.nextStringOption()
  val email = r.nextStringOption()
  
  UserSummary(
    id = id,
    displayName = firstName + lastName.map(" " + _).getOrElse(""),
    hasEmail = email.isDefined
  )
}

val summaryQuery = sql"SELECT id, first_name, last_name, email FROM users"
val summaries: List[UserSummary] = summaryQuery.as[UserSummary].list

// Parameter setting trait
trait SetParameter[T] {
  def apply(value: T, params: PositionedParameters): Unit
}

// Standard SetParameter instances  
implicit val SetBoolean: SetParameter[Boolean]
implicit val SetInt: SetParameter[Int] 
implicit val SetLong: SetParameter[Long]
implicit val SetString: SetParameter[String]
implicit val SetDate: SetParameter[java.sql.Date]
implicit val SetTimestamp: SetParameter[java.sql.Timestamp]
// ... other standard types

// Option support
implicit def SetOption[T](implicit base: SetParameter[T]): SetParameter[Option[T]]

// Tuple support
implicit def SetTuple2[T1, T2](implicit s1: SetParameter[T1], s2: SetParameter[T2]): SetParameter[(T1, T2)]
// ... up to SetTuple22

// Parameter setting with position tracking
class PositionedParameters(val ps: PreparedStatement) {
  var pos = 0
  
  // Basic parameter setting
  def setBoolean(value: Boolean): Unit
  def setInt(value: Int): Unit
  def setLong(value: Long): Unit 
  def setString(value: String): Unit
  def setDate(value: java.sql.Date): Unit
  def setTimestamp(value: java.sql.Timestamp): Unit
  def setBigDecimal(value: BigDecimal): Unit
  def setNull(sqlType: Int): Unit
  
  // Generic parameter setting
  def setObject(value: AnyRef): Unit
  def skip(): Unit
  def restart(): Unit
  
  // Utility method
  def >>[T](value: T)(implicit f: SetParameter[T]): Unit = f(value, this)
}

// Custom parameter for enum
implicit val setUserStatus = SetParameter[Status] { (value, params) =>
  params.setString(value match {
    case Active => "active"
    case Inactive => "inactive" 
    case Pending => "pending"
  })
}

// Custom parameter for case class
case class DateRange(start: java.sql.Date, end: java.sql.Date)

implicit val setDateRange = SetParameter[DateRange] { (range, params) =>
  params.setDate(range.start)
  params.setDate(range.end)
}

// Usage in queries
def usersByStatusAndDate(status: Status, dateRange: DateRange) = 
  sql"""SELECT * FROM users 
        WHERE status = $status 
        AND created_at BETWEEN ${dateRange.start} AND ${dateRange.end}""".as[User]

// Batch insert with plain SQL
def insertUsersBatch(users: List[User]) = {
  val insertSQL = "INSERT INTO users (name, email, status) VALUES (?, ?, ?)"
  
  Database.forURL("...") withSession { implicit session =>
    session.withPreparedStatement(insertSQL) { ps =>
      users.foreach { user =>
        ps.setString(1, user.name)
        ps.setString(2, user.email.orNull)
        ps.setString(3, user.status.toString.toLowerCase)
        ps.addBatch()
      }
      ps.executeBatch()
    }
  }
}

// Batch update
def updateUserStatusBatch(updates: List[(Int, Status)]) = {
  val updateSQL = "UPDATE users SET status = ? WHERE id = ?"
  
  Database.forURL("...") withSession { implicit session =>
    session.withPreparedStatement(updateSQL) { ps =>
      updates.foreach { case (id, status) =>
        ps.setString(1, status.toString.toLowerCase)
        ps.setInt(2, id)
        ps.addBatch()
      }
      ps.executeBatch()
    }
  }
}

import scala.slick.util.CloseableIterator

// Stream large result sets
def streamAllUsers(): CloseableIterator[User] = {
  val query = sql"SELECT id, name, email, created_at FROM users ORDER BY id"
  
  Database.forURL("...") withSession { implicit session =>
    query.as[User].iterator // Returns CloseableIterator
  }
}

// Usage
val userIterator = streamAllUsers()
try {
  userIterator.foreach { user =>
    // Process user without loading all into memory
    processUser(user)
  }
} finally {
  userIterator.close()
}

// Build queries dynamically with plain SQL
def searchUsers(nameFilter: Option[String], statusFilter: Option[Status], limit: Int): List[User] = {
  val conditions = List(
    nameFilter.map(name => s"name LIKE '%$name%'"),
    statusFilter.map(status => s"status = '${status.toString.toLowerCase}'")
  ).flatten
  
  val whereClause = if (conditions.nonEmpty) {
    "WHERE " + conditions.mkString(" AND ")
  } else ""
  
  val query = s"""
    SELECT id, name, email, created_at 
    FROM users 
    $whereClause 
    ORDER BY name 
    LIMIT $limit
  """
  
  Database.forURL("...") withSession { implicit session =>
    StaticQuery.queryNA[User](query).list
  }
}

// Call stored procedures
def callStoredProcedure(userId: Int): List[Order] = {
  Database.forURL("...") withSession { implicit session =>
    session.withPreparedStatement("CALL get_user_orders(?)") { ps =>
      ps.setInt(1, userId)
      val rs = ps.executeQuery()
      val result = new scala.collection.mutable.ListBuffer[Order]
      
      while (rs.next()) {
        result += Order(
          id = rs.getInt("id"),
          userId = rs.getInt("user_id"), 
          product = rs.getString("product"),
          quantity = rs.getInt("quantity")
        )
      }
      result.toList
    }
  }
}

// Function calls  
def getUserCount(): Int = {
  Database.forURL("...") withSession { implicit session =>
    sql"SELECT count_users()".as[Int].first
  }
}

// Advanced connection configuration
val database = Database.forURL(
  url = "jdbc:postgresql://localhost:5432/mydb",
  user = "username", 
  password = "password",
  driver = "org.postgresql.Driver"
)

// Using connection pool
import com.zaxxer.hikari.HikariDataSource

val dataSource = new HikariDataSource()
dataSource.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb")
dataSource.setUsername("username")
dataSource.setPassword("password") 
dataSource.setMaximumPoolSize(20)

val database = Database.forDataSource(dataSource)

// Configuration file approach
// application.conf:
// mydb {
//   url = "jdbc:postgresql://localhost:5432/mydb"
//   driver = "org.postgresql.Driver"  
//   user = "username"
//   password = "password"
//   connectionPool = "HikariCP"
//   maximumPoolSize = 20
// }

val database = Database.forConfig("mydb")