# ktorm-transaction

### ktorm transaction example

ref:
[ktorm introduction](https://www.ktorm.org/en/transaction-management.html)
[example from discussion](https://github.com/kotlin-orm/ktorm/issues/65)

    // private val ds: DataSource 
    //  database=Database.connect(ds)
    private val database by inject()
    
    database.useTransaction(isolation = TransactionIsolation.READ_COMMITTED){
        
        //database in DAO must the same with the one that useTransaction
        LogUserActionDAO.updateContentById(33,"hello");
        }

### ktorm connection pool

test:

maxConnectionPool count=1

track following database configuration and check whether connection in class(not singleton) will be released to pool after "useTransaction"

    private val applicationFormDao: ApplicationFormDao by inject()
    private val ds: DataSource by inject()
    private val database = Database.connect(ds)

    fun fakeInsert() {
        database.useTransaction {
            applicationFormDao.insertFake()
        }
    }

#### 1st and 2nd post of useTransaction



same : datasource(\@9723)

different :

connector different(\@9701 and \@10180)

database (\)

#### 1st and 2nd trail in DAO(singleton)



same database/datasource/connector

### connection with pool

:::info
Connect with a Pool
Ktorm doesn’t limit you, you can use any connection pool you like, such as DBCP, C3P0 or Druid. The connect function provides an overloaded version which accepts a DataSource parameter, you just need to create a DataSource object and call that function with it:

val dataSource = SingleConnectionDataSource() // Any DataSource implementation is OK.
val database = Database.connect(dataSource)
Now, <font color="red">Ktorm will obtain connections from the DataSource when necessary, then return them to the pool after they are not useful.</font> This avoids the performance costs of frequent connection creation.

Connection pools are applicative and effective in most cases, we highly recommend you manage your connections in this way.
:::

[ref](https://www.ktorm.org/en/connect-to-databases.html#Connect-with-a-Pool)

lsof -i -P -n | grep {port#}


### sum user limit-> custom 合併 column 計算

    suspend fun getSumAmountAndTransactionsCountByUsernameAndCurrencyAndTxnTypeAndPaymentWayAndCreateDate(
        userName: String,
        currency: String,
        transactionType: Int,
        paymentWay: String,
        startDateTime: LocalDateTime,
        endDateTime: LocalDateTime
    ): SumAmountsAndTransactions = withContext(databaseDispatcher) {
        val receivedAmountSum = sum(BalanceAuditTable.receivedAmount).aliased("sumReceivedAmount")
        val amountSum = sum(BalanceAuditTable.amount).aliased("sumAmount")
        val transactionsCount = count(BalanceAuditTable.id).aliased("transactions_count")
        val dateRange = startDateTime..endDateTime
        database.from(BalanceAuditTable)
            .select(receivedAmountSum, amountSum, transactionsCount)
            .where(
                (BalanceAuditTable.username eq userName)
                    and (BalanceAuditTable.currency eq currency)
                    and (BalanceAuditTable.transactionType eq transactionType)
                    and (BalanceAuditTable.currencyNetwork eq paymentWay)
                    and (BalanceAuditTable.status inList listOf(1, 2, 3, 10))
                    and (BalanceAuditTable.createDateTime between dateRange)
            ).mapNotNull { row ->
                SumAmountsAndTransactions(
                    sumReceivedAmount = if (row[receivedAmountSum] != null) BigDecimal(row[receivedAmountSum].toString()) else BigDecimal(0),
                    sumAmount = if (row[amountSum] != null) BigDecimal(row[amountSum].toString()) else BigDecimal(0),
                    transactionsCount = if (row[transactionsCount] != null) row[transactionsCount] else 0
                )
            }.single()
    }