0
# Database Operations
1

2
Core database connectivity providing connection management, transaction support, and resource handling for both Scala and Java APIs.
3

4
## Capabilities
5

6
### Database Interface (Scala)
7

8
Primary interface for database operations in Scala.
9

10
```scala { .api }
11
trait Database {
12
  /** The configuration name for this database */
13
  def name: String
14
  /** The underlying JDBC data source for this database */
15
  def dataSource: DataSource
16
  /** The JDBC connection URL this database, i.e. `jdbc:...` */
17
  def url: String
18
  /** Get a JDBC connection from the underlying data source (autocommit enabled) */
19
  def getConnection(): Connection
20
  /** Get a JDBC connection with specified autocommit setting */
21
  def getConnection(autocommit: Boolean): Connection
22
  /** Execute a block of code, providing a JDBC connection (autocommit enabled) */
23
  def withConnection[A](block: Connection => A): A
24
  /** Execute a block of code, providing a JDBC connection with specified autocommit */
25
  def withConnection[A](autocommit: Boolean)(block: Connection => A): A
26
  /** Execute a block of code in the scope of a JDBC transaction */
27
  def withTransaction[A](block: Connection => A): A
28
  /** Execute a block of code in a transaction with specified isolation level */
29
  def withTransaction[A](isolationLevel: TransactionIsolationLevel)(block: Connection => A): A
30
  /** Shutdown this database, closing the underlying data source */
31
  def shutdown(): Unit
32
}
33
```
34

35
**Usage Examples:**
36

37
```scala
38
import play.api.db._
39
import java.sql.Connection
40
import javax.inject.Inject
41

42
class UserRepository @Inject()(db: Database) {
43
  // Simple connection usage
44
  def userExists(id: Long): Boolean = {
45
    db.withConnection { implicit connection =>
46
      val stmt = connection.prepareStatement("SELECT 1 FROM users WHERE id = ?")
47
      stmt.setLong(1, id)
48
      val rs = stmt.executeQuery()
49
      rs.next()
50
    }
51
  }
52
  
53
  // Transaction usage
54
  def transferFunds(fromId: Long, toId: Long, amount: BigDecimal): Unit = {
55
    db.withTransaction { implicit connection =>
56
      // Debit from source account
57
      val debitStmt = connection.prepareStatement(
58
        "UPDATE accounts SET balance = balance - ? WHERE user_id = ?"
59
      )
60
      debitStmt.setBigDecimal(1, amount.bigDecimal)
61
      debitStmt.setLong(2, fromId)
62
      debitStmt.executeUpdate()
63
      
64
      // Credit to destination account
65
      val creditStmt = connection.prepareStatement(
66
        "UPDATE accounts SET balance = balance + ? WHERE user_id = ?"
67
      )
68
      creditStmt.setBigDecimal(1, amount.bigDecimal)
69
      creditStmt.setLong(2, toId)
70
      creditStmt.executeUpdate()
71
    }
72
  }
73
  
74
  // Custom isolation level
75
  def generateReport(): String = {
76
    db.withTransaction(TransactionIsolationLevel.ReadCommitted) { implicit connection =>
77
      // Complex reporting queries with consistent read view
78
      val stmt = connection.prepareStatement("SELECT COUNT(*) FROM users")
79
      val rs = stmt.executeQuery()
80
      rs.next()
81
      s"Total users: ${rs.getInt(1)}"
82
    }
83
  }
84
}
85
```
86

87
### Database Interface (Java)
88

89
Primary interface for database operations in Java.
90

91
```java { .api }
92
public interface Database {
93
  /** @return the configuration name for this database */
94
  String getName();
95
  /** @return the underlying JDBC data source for this database */
96
  DataSource getDataSource();
97
  /** @return the JDBC connection URL this database, i.e. `jdbc:...` */
98
  String getUrl();
99
  /** Get a JDBC connection from the underlying data source (autocommit enabled) */
100
  Connection getConnection();
101
  /** Get a JDBC connection with specified autocommit setting */
102
  Connection getConnection(boolean autocommit);
103
  /** Execute a block of code, providing a JDBC connection */
104
  void withConnection(ConnectionRunnable block);
105
  /** Execute a block of code, providing a JDBC connection, returning a value */
106
  <A> A withConnection(ConnectionCallable<A> block);
107
  /** Execute a block of code with specified autocommit setting */
108
  void withConnection(boolean autocommit, ConnectionRunnable block);
109
  /** Execute a block of code with specified autocommit, returning a value */
110
  <A> A withConnection(boolean autocommit, ConnectionCallable<A> block);
111
  /** Execute a block of code in the scope of a JDBC transaction */
112
  void withTransaction(ConnectionRunnable block);
113
  /** Execute a block of code in a transaction with specified isolation level */
114
  void withTransaction(TransactionIsolationLevel isolationLevel, ConnectionRunnable block);
115
  /** Execute a block of code in transaction, returning a value */
116
  <A> A withTransaction(ConnectionCallable<A> block);
117
  /** Execute a block of code in transaction with isolation level, returning a value */
118
  <A> A withTransaction(TransactionIsolationLevel isolationLevel, ConnectionCallable<A> block);
119
  /** Shutdown this database, closing the underlying data source */
120
  void shutdown();
121
  /** Convert to Scala Database API */
122
  play.api.db.Database asScala();
123
  /** @deprecated Use asScala() instead */
124
  @Deprecated
125
  play.api.db.Database toScala();
126
}
127
```
128

129
**Usage Examples:**
130

131
```java
132
import play.db.*;
133
import javax.inject.Inject;
134
import java.sql.*;
135
import java.util.Optional;
136

137
public class UserRepository {
138
    private final Database db;
139
    
140
    @Inject
141
    public UserRepository(Database db) {
142
        this.db = db;
143
    }
144
    
145
    // Simple connection usage returning a value
146
    public Optional<String> getUserName(long id) {
147
        return db.withConnection(connection -> {
148
            PreparedStatement stmt = connection.prepareStatement("SELECT name FROM users WHERE id = ?");
149
            stmt.setLong(1, id);
150
            ResultSet rs = stmt.executeQuery();
151
            return rs.next() ? Optional.of(rs.getString("name")) : Optional.empty();
152
        });
153
    }
154
    
155
    // Transaction usage without return value
156
    public void createUser(String name, String email) {
157
        db.withTransaction(connection -> {
158
            PreparedStatement stmt = connection.prepareStatement(
159
                "INSERT INTO users (name, email) VALUES (?, ?)"
160
            );
161
            stmt.setString(1, name);
162
            stmt.setString(2, email);
163
            stmt.executeUpdate();
164
        });
165
    }
166
    
167
    // Transaction with isolation level
168
    public int getUserCount() {
169
        return db.withTransaction(TransactionIsolationLevel.ReadCommited, connection -> {
170
            PreparedStatement stmt = connection.prepareStatement("SELECT COUNT(*) FROM users");
171
            ResultSet rs = stmt.executeQuery();
172
            rs.next();
173
            return rs.getInt(1);
174
        });
175
    }
176
}
177
```
178

179
### Functional Interfaces (Java)
180

181
Functional interfaces for connection-based operations.
182

183
```java { .api }
184
/** Functional interface for connection-based operations returning a value */
185
public interface ConnectionCallable<A> {
186
  /** Execute operation with connection, potentially throwing SQLException */
187
  A call(Connection connection) throws SQLException;
188
}
189

190
/** Functional interface for connection-based operations returning void */
191
public interface ConnectionRunnable {
192
  /** Execute operation with connection, potentially throwing SQLException */
193
  void run(Connection connection) throws SQLException;
194
}
195
```
196

197
### Database Factory Methods (Scala)
198

199
Utility methods for creating databases programmatically.
200

201
```scala { .api }
202
object Databases {
203
  /** Create a pooled database with the given driver and url */
204
  def apply(
205
    driver: String,
206
    url: String, 
207
    name: String = "default",
208
    config: Map[String, _ <: Any] = Map.empty
209
  ): Database
210
  
211
  /** Create an in-memory H2 database */
212
  def inMemory(
213
    name: String = "default",
214
    urlOptions: Map[String, String] = Map.empty,
215
    config: Map[String, _ <: Any] = Map.empty
216
  ): Database
217
  
218
  /** Run the given block with a database, cleaning up afterwards */
219
  def withDatabase[T](
220
    driver: String, 
221
    url: String, 
222
    name: String = "default", 
223
    config: Map[String, _ <: Any] = Map.empty
224
  )(block: Database => T): T
225
  
226
  /** Run the given block with an in-memory h2 database, cleaning up afterwards */
227
  def withInMemory[T](
228
    name: String = "default",
229
    urlOptions: Map[String, String] = Map.empty,
230
    config: Map[String, _ <: Any] = Map.empty
231
  )(block: Database => T): T
232
}
233
```
234

235
**Usage Examples:**
236

237
```scala
238
import play.api.db.Databases
239

240
// Create a temporary database for testing
241
val testDb = Databases.inMemory("test")
242
try {
243
  testDb.withConnection { implicit connection =>
244
    // Run tests
245
  }
246
} finally {
247
  testDb.shutdown()
248
}
249

250
// Use with automatic cleanup
251
Databases.withInMemory("test") { db =>
252
  db.withConnection { implicit connection =>
253
    // Database automatically cleaned up after this block
254
  }
255
}
256

257
// Create a persistent database
258
val prodDb = Databases(
259
  driver = "org.postgresql.Driver",
260
  url = "jdbc:postgresql://localhost/myapp",
261
  name = "production",
262
  config = Map(
263
    "username" -> "dbuser",
264
    "password" -> "dbpass"
265
  )
266
)
267
```
268

269
### Transaction Isolation Levels
270

271
Enumeration of supported transaction isolation levels.
272

273
```scala { .api }
274
sealed abstract class TransactionIsolationLevel(val id: Int) {
275
  def asJava(): play.db.TransactionIsolationLevel
276
}
277

278
object TransactionIsolationLevel {
279
  case object ReadUncommitted extends TransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED)
280
  case object ReadCommited extends TransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED)
281
  case object RepeatedRead extends TransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ)
282
  case object Serializable extends TransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)
283
  
284
  def apply(id: Int): TransactionIsolationLevel
285
}
286
```
287

288
```java { .api }
289
public enum TransactionIsolationLevel {
290
  ReadUncommitted(Connection.TRANSACTION_READ_UNCOMMITTED),
291
  ReadCommited(Connection.TRANSACTION_READ_COMMITTED),
292
  RepeatedRead(Connection.TRANSACTION_REPEATABLE_READ),
293
  Serializable(Connection.TRANSACTION_SERIALIZABLE);
294
  
295
  /** Get the JDBC constant value */
296
  public int getId();
297
  /** Convert to Scala API */
298
  public play.api.db.TransactionIsolationLevel asScala();
299
  /** Create from JDBC constant */
300
  public static TransactionIsolationLevel fromId(int id);
301
}
302
```
303

304
## Error Handling
305

306
- **SQLException**: Thrown by JDBC operations for database-specific errors
307
- **IllegalArgumentException**: Thrown for invalid transaction isolation levels
308
- **Configuration errors**: Thrown during database initialization for invalid configuration
309

310
## Resource Management
311

312
All `withConnection` and `withTransaction` methods automatically handle resource cleanup:
313
- Connections are automatically closed
314
- Transactions are automatically committed on success or rolled back on exception
315
- Statement and ResultSet resources should still be managed manually or with try-with-resources