Database Operations

trait Database {
  /** The configuration name for this database */
  def name: String
  /** The underlying JDBC data source for this database */
  def dataSource: DataSource
  /** The JDBC connection URL this database, i.e. `jdbc:...` */
  def url: String
  /** Get a JDBC connection from the underlying data source (autocommit enabled) */
  def getConnection(): Connection
  /** Get a JDBC connection with specified autocommit setting */
  def getConnection(autocommit: Boolean): Connection
  /** Execute a block of code, providing a JDBC connection (autocommit enabled) */
  def withConnection[A](block: Connection => A): A
  /** Execute a block of code, providing a JDBC connection with specified autocommit */
  def withConnection[A](autocommit: Boolean)(block: Connection => A): A
  /** Execute a block of code in the scope of a JDBC transaction */
  def withTransaction[A](block: Connection => A): A
  /** Execute a block of code in a transaction with specified isolation level */
  def withTransaction[A](isolationLevel: TransactionIsolationLevel)(block: Connection => A): A
  /** Shutdown this database, closing the underlying data source */
  def shutdown(): Unit
}

import play.api.db._
import java.sql.Connection
import javax.inject.Inject

class UserRepository @Inject()(db: Database) {
  // Simple connection usage
  def userExists(id: Long): Boolean = {
    db.withConnection { implicit connection =>
      val stmt = connection.prepareStatement("SELECT 1 FROM users WHERE id = ?")
      stmt.setLong(1, id)
      val rs = stmt.executeQuery()
      rs.next()
    }
  }
  
  // Transaction usage
  def transferFunds(fromId: Long, toId: Long, amount: BigDecimal): Unit = {
    db.withTransaction { implicit connection =>
      // Debit from source account
      val debitStmt = connection.prepareStatement(
        "UPDATE accounts SET balance = balance - ? WHERE user_id = ?"
      )
      debitStmt.setBigDecimal(1, amount.bigDecimal)
      debitStmt.setLong(2, fromId)
      debitStmt.executeUpdate()
      
      // Credit to destination account
      val creditStmt = connection.prepareStatement(
        "UPDATE accounts SET balance = balance + ? WHERE user_id = ?"
      )
      creditStmt.setBigDecimal(1, amount.bigDecimal)
      creditStmt.setLong(2, toId)
      creditStmt.executeUpdate()
    }
  }
  
  // Custom isolation level
  def generateReport(): String = {
    db.withTransaction(TransactionIsolationLevel.ReadCommitted) { implicit connection =>
      // Complex reporting queries with consistent read view
      val stmt = connection.prepareStatement("SELECT COUNT(*) FROM users")
      val rs = stmt.executeQuery()
      rs.next()
      s"Total users: ${rs.getInt(1)}"
    }
  }
}

public interface Database {
  /** @return the configuration name for this database */
  String getName();
  /** @return the underlying JDBC data source for this database */
  DataSource getDataSource();
  /** @return the JDBC connection URL this database, i.e. `jdbc:...` */
  String getUrl();
  /** Get a JDBC connection from the underlying data source (autocommit enabled) */
  Connection getConnection();
  /** Get a JDBC connection with specified autocommit setting */
  Connection getConnection(boolean autocommit);
  /** Execute a block of code, providing a JDBC connection */
  void withConnection(ConnectionRunnable block);
  /** Execute a block of code, providing a JDBC connection, returning a value */
  <A> A withConnection(ConnectionCallable<A> block);
  /** Execute a block of code with specified autocommit setting */
  void withConnection(boolean autocommit, ConnectionRunnable block);
  /** Execute a block of code with specified autocommit, returning a value */
  <A> A withConnection(boolean autocommit, ConnectionCallable<A> block);
  /** Execute a block of code in the scope of a JDBC transaction */
  void withTransaction(ConnectionRunnable block);
  /** Execute a block of code in a transaction with specified isolation level */
  void withTransaction(TransactionIsolationLevel isolationLevel, ConnectionRunnable block);
  /** Execute a block of code in transaction, returning a value */
  <A> A withTransaction(ConnectionCallable<A> block);
  /** Execute a block of code in transaction with isolation level, returning a value */
  <A> A withTransaction(TransactionIsolationLevel isolationLevel, ConnectionCallable<A> block);
  /** Shutdown this database, closing the underlying data source */
  void shutdown();
  /** Convert to Scala Database API */
  play.api.db.Database asScala();
  /** @deprecated Use asScala() instead */
  @Deprecated
  play.api.db.Database toScala();
}

import play.db.*;
import javax.inject.Inject;
import java.sql.*;
import java.util.Optional;

public class UserRepository {
    private final Database db;
    
    @Inject
    public UserRepository(Database db) {
        this.db = db;
    }
    
    // Simple connection usage returning a value
    public Optional<String> getUserName(long id) {
        return db.withConnection(connection -> {
            PreparedStatement stmt = connection.prepareStatement("SELECT name FROM users WHERE id = ?");
            stmt.setLong(1, id);
            ResultSet rs = stmt.executeQuery();
            return rs.next() ? Optional.of(rs.getString("name")) : Optional.empty();
        });
    }
    
    // Transaction usage without return value
    public void createUser(String name, String email) {
        db.withTransaction(connection -> {
            PreparedStatement stmt = connection.prepareStatement(
                "INSERT INTO users (name, email) VALUES (?, ?)"
            );
            stmt.setString(1, name);
            stmt.setString(2, email);
            stmt.executeUpdate();
        });
    }
    
    // Transaction with isolation level
    public int getUserCount() {
        return db.withTransaction(TransactionIsolationLevel.ReadCommited, connection -> {
            PreparedStatement stmt = connection.prepareStatement("SELECT COUNT(*) FROM users");
            ResultSet rs = stmt.executeQuery();
            rs.next();
            return rs.getInt(1);
        });
    }
}

/** Functional interface for connection-based operations returning a value */
public interface ConnectionCallable<A> {
  /** Execute operation with connection, potentially throwing SQLException */
  A call(Connection connection) throws SQLException;
}

/** Functional interface for connection-based operations returning void */
public interface ConnectionRunnable {
  /** Execute operation with connection, potentially throwing SQLException */
  void run(Connection connection) throws SQLException;
}

object Databases {
  /** Create a pooled database with the given driver and url */
  def apply(
    driver: String,
    url: String, 
    name: String = "default",
    config: Map[String, _ <: Any] = Map.empty
  ): Database
  
  /** Create an in-memory H2 database */
  def inMemory(
    name: String = "default",
    urlOptions: Map[String, String] = Map.empty,
    config: Map[String, _ <: Any] = Map.empty
  ): Database
  
  /** Run the given block with a database, cleaning up afterwards */
  def withDatabase[T](
    driver: String, 
    url: String, 
    name: String = "default", 
    config: Map[String, _ <: Any] = Map.empty
  )(block: Database => T): T
  
  /** Run the given block with an in-memory h2 database, cleaning up afterwards */
  def withInMemory[T](
    name: String = "default",
    urlOptions: Map[String, String] = Map.empty,
    config: Map[String, _ <: Any] = Map.empty
  )(block: Database => T): T
}

import play.api.db.Databases

// Create a temporary database for testing
val testDb = Databases.inMemory("test")
try {
  testDb.withConnection { implicit connection =>
    // Run tests
  }
} finally {
  testDb.shutdown()
}

// Use with automatic cleanup
Databases.withInMemory("test") { db =>
  db.withConnection { implicit connection =>
    // Database automatically cleaned up after this block
  }
}

// Create a persistent database
val prodDb = Databases(
  driver = "org.postgresql.Driver",
  url = "jdbc:postgresql://localhost/myapp",
  name = "production",
  config = Map(
    "username" -> "dbuser",
    "password" -> "dbpass"
  )
)

sealed abstract class TransactionIsolationLevel(val id: Int) {
  def asJava(): play.db.TransactionIsolationLevel
}

object TransactionIsolationLevel {
  case object ReadUncommitted extends TransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED)
  case object ReadCommited extends TransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED)
  case object RepeatedRead extends TransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ)
  case object Serializable extends TransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)
  
  def apply(id: Int): TransactionIsolationLevel
}

public enum TransactionIsolationLevel {
  ReadUncommitted(Connection.TRANSACTION_READ_UNCOMMITTED),
  ReadCommited(Connection.TRANSACTION_READ_COMMITTED),
  RepeatedRead(Connection.TRANSACTION_REPEATABLE_READ),
  Serializable(Connection.TRANSACTION_SERIALIZABLE);
  
  /** Get the JDBC constant value */
  public int getId();
  /** Convert to Scala API */
  public play.api.db.TransactionIsolationLevel asScala();
  /** Create from JDBC constant */
  public static TransactionIsolationLevel fromId(int id);
}