boxlang-database-access

BoxLang 数据库访问

BoxLang 是一个 AI 原生软件生产力平台，适用于构建、现代化改造和运行应用程序，让开发者与 AI Agent 协同工作。

概述

BoxLang 通过 JDBC 提供一流的数据库访问能力。数据源在 boxlang.json 或 Application.bx 中配置。查询使用 queryExecute()（脚本语法）或 bx:query（标签语法），并内置参数化功能以防止 SQL 注入。

数据源配置

在 boxlang.json 中

{
    "datasources": {
        "mainDB": {
            "driver": "mysql",
            "host": "${DB_HOST:localhost}",
            "port": "${DB_PORT:3306}",
            "database": "${DB_NAME:myapp}",
            "username": "${DB_USER}",
            "password": "${DB_PASS}",
            "options": {
                "maximumPoolSize": 10,
                "minimumIdle": 2,
                "connectionTimeout": 30000
            }
        },
        "readReplica": {
            "driver": "mysql",
            "host": "replica.example.com",
            "port": 3306,
            "database": "myapp",
            "username": "${DB_READ_USER}",
            "password": "${DB_READ_PASS}"
        }
    }
}

在 Application.bx 中

class {
    // Default datasource
    this.datasource = "mainDB"

    // Define datasources in application scope
    this.datasources = {
        mainDB: {
            driver   : "mysql",
            host     : server.system.environment.DB_HOST ?: "localhost",
            port     : 3306,
            database : "myapp",
            username : server.system.environment.DB_USER,
            password : server.system.environment.DB_PASS
        }
    }
}

将 BIT 列作为布尔值处理（v1.18+）

BoxLang 在查询对象中将 BIT 值表示为 1/0。全局 Java 标志 GenericJDBCDriver.representBitAsBoolean（默认值为 false）可将其切换为真正的布尔值。可通过 Java 代码或模块启用该标志，目前还没有对应的 boxlang.json 设置。

基本查询

queryExecute() — 脚本风格（首选）

// SELECT — returns a query object
var users = queryExecute(
    "SELECT id, name, email, created_at FROM users WHERE status = :status ORDER BY name",
    { status: "active" },
    { datasource: "mainDB" }
)

// Iterate results
for ( var user in users ) {
    writeOutput( "#user.name# — #user.email#<br>" )
}

// Access by column name
var count = users.recordCount
var firstUser = users.name[1]   // 1-indexed

// Convert to array of structs
var userArray = users.toArray()

bx:query — 标签风格

bx:query name="users" datasource="mainDB" {
    writeOutput( "SELECT id, name, email FROM users" )
    writeOutput( " WHERE status = " )
    bx:queryparam value="active" cfsqltype="cf_sql_varchar"
    writeOutput( " ORDER BY name" )
}

参数化查询（防止 SQL 注入）

始终使用参数 — 切勿直接将变量插入 SQL：

// CORRECT — parameterized
var user = queryExecute(
    "SELECT * FROM users WHERE email = :email",
    { email: form.email }
)

// CORRECT — with explicit type
var result = queryExecute(
    "SELECT * FROM orders WHERE user_id = :userId AND total > :minAmount",
    {
        userId    : { value: session.userId, cfsqltype: "cf_sql_integer" },
        minAmount : { value: 100.00,         cfsqltype: "cf_sql_decimal" }
    }
)

// WRONG — never do this (SQL injection risk)
var result = queryExecute( "SELECT * FROM users WHERE name = '#form.name#'" )

常见 SQL 类型

BoxLang 类型	SQL 类型
cf_sql_integer	INT
cf_sql_bigint	BIGINT
cf_sql_decimal	DECIMAL/NUMERIC
cf_sql_varchar	VARCHAR
cf_sql_char	CHAR
cf_sql_longvarchar	TEXT
cf_sql_date	DATE
cf_sql_timestamp	DATETIME/TIMESTAMP
cf_sql_boolean	BOOLEAN/TINYINT
cf_sql_blob	BLOB/BYTEA

INSERT、UPDATE、DELETE

// INSERT
var result = queryExecute(
    "INSERT INTO users (name, email, status, created_at) VALUES (:name, :email, :status, :now)",
    {
        name   : arguments.name,
        email  : arguments.email,
        status : "active",
        now    : { value: now(), cfsqltype: "cf_sql_timestamp" }
    },
    { result: "insertResult", datasource: "mainDB" }
)
var newId = insertResult.generatedKey

// UPDATE
queryExecute(
    "UPDATE users SET name = :name, updated_at = :now WHERE id = :id",
    {
        name : arguments.name,
        now  : { value: now(), cfsqltype: "cf_sql_timestamp" },
        id   : { value: arguments.id, cfsqltype: "cf_sql_integer" }
    }
)

// DELETE
queryExecute(
    "DELETE FROM users WHERE id = :id AND status = :status",
    {
        id     : { value: arguments.id, cfsqltype: "cf_sql_integer" },
        status : "inactive"
    }
)

查询结果处理

// Record count
var total = users.recordCount

// Column list
var cols = users.columnList   // "id,name,email"

// Access specific row/column
var firstName = users.name[1]   // row 1, column "name"

// Convert to array of structs
var userList = users.toArray()
userList.each( (row) -> processUser( row ) )

// Filter a query result
var activeUsers = queryFilter( users, (row) -> row.status == "active" )

// Sort a query result
var sorted = querySort( users, (a, b) -> a.name.compare( b.name ) )

// Create a query programmatically
var q = queryNew( "id,name,email", "integer,varchar,varchar" )
queryAddRow( q, { id: 1, name: "Ada", email: "[email protected]" } )
queryAddRow( q, { id: 2, name: "Grace", email: "[email protected]" } )

// Execute a function on each row
queryEach( users, (row) -> {
    sendEmail( row.email )
})

// Map query to array
var names = queryMap( users, (row) -> row.name.uCase() )

事务

// Wraps all queries in a single atomic transaction
bx:transaction {
    queryExecute(
        "INSERT INTO orders (user_id, total) VALUES (:userId, :total)",
        { userId: session.userId, total: cartTotal }
    )

    var orderId = queryExecute(
        "SELECT LAST_INSERT_ID() AS id"
    ).id[1]

    cart.items.each( (item) -> {
        queryExecute(
            "INSERT INTO order_items (order_id, product_id, qty, price) VALUES (:orderId, :productId, :qty, :price)",
            { orderId: orderId, productId: item.id, qty: item.qty, price: item.price }
        )
    })

    // Explicit commit (optional — auto-commits at end of block)
    bx:transaction action="commit"
}

// Rollback on error
try {
    bx:transaction {
        performTransfer( fromId, toId, amount )
    }
} catch ( any e ) {
    bx:transaction action="rollback"
    throw( e )
}

存储过程

bx:storedproc procedure="sp_GetUserStats" datasource="mainDB" {
    bx:procparam type="in"  cfsqltype="cf_sql_integer" value=userId
    bx:procparam type="in"  cfsqltype="cf_sql_date"    value=startDate
    bx:procparam type="out" cfsqltype="cf_sql_integer" variable="totalOrders"
    bx:procparam type="out" cfsqltype="cf_sql_decimal" variable="totalRevenue"
    bx:procresult name="orderList" resultset=1
}

writeOutput( "Total orders: #totalOrders#" )
writeOutput( "Revenue: #totalRevenue#" )

动态构建查询（安全模式）

function buildUserSearch( struct filters={} ) {
    var sql    = "SELECT id, name, email FROM users WHERE 1=1"
    var params = {}
    var i      = 1

    if ( filters.keyExists( "status" ) && len( filters.status ) ) {
        sql &= " AND status = :status"
        params.status = filters.status
    }

    if ( filters.keyExists( "role" ) && len( filters.role ) ) {
        sql &= " AND role = :role"
        params.role = filters.role
    }

    if ( filters.keyExists( "search" ) && len( trim( filters.search ) ) ) {
        sql &= " AND (name LIKE :search OR email LIKE :search)"
        params.search = "%" & trim( filters.search ) & "%"
    }

    sql &= " ORDER BY name LIMIT :limit OFFSET :offset"
    params.limit  = filters.limit  ?: 20
    params.offset = filters.offset ?: 0

    return queryExecute( sql, params )
}

查询缓存

// Cache query results for 5 minutes
var products = queryExecute(
    "SELECT * FROM products WHERE active = 1",
    {},
    { cachedWithin: createTimeSpan( 0, 0, 5, 0 ) }
)

多个数据源

// Override datasource per query
var legacyData = queryExecute(
    "SELECT * FROM legacy_users",
    {},
    { datasource: "legacyDB" }
)

var analyticsData = queryExecute(
    "SELECT * FROM events WHERE date > :since",
    { since: dateAdd( "d", -30, now() ) },
    { datasource: "analyticsDB" }
)

查询对象查询（QoQ）

传入 { dbtype: "query" } 作为选项参数，即可对内存中的查询对象运行 SQL，而无需访问数据库。本地查询变量会成为表数据源。

基本 QoQ

// Load data from the database once
var users = queryExecute( "SELECT * FROM users" )

// Now query the in-memory result — no DB roundtrip
var activeUsers = queryExecute(
    "SELECT * FROM users WHERE active = :active ORDER BY lastName",
    { active: true },
    { dbtype: "query" }
)

跨多个内存查询对象进行 JOIN

var users  = queryExecute( "SELECT * FROM users" )
var orders = queryExecute( "SELECT * FROM orders" )

// Join two in-memory queries
var report = queryExecute(
    "
        SELECT u.firstName, u.lastName, COUNT(o.id) AS orderCount
        FROM users u
        LEFT JOIN orders o ON u.id = o.userId
        GROUP BY u.firstName, u.lastName
        ORDER BY orderCount DESC
    ",
    {},
    { dbtype: "query" }
)

QoQ 聚合函数

var orders = queryExecute( "SELECT * FROM orders" )

var summary = queryExecute(
    "
        SELECT
            COUNT(*)   AS totalOrders,
            SUM(total) AS totalRevenue,
            AVG(total) AS avgValue,
            MIN(total) AS minOrder,
            MAX(total) AS maxOrder
        FROM orders
    ",
    {},
    { dbtype: "query" }
)
writeOutput( "Revenue: #summary.totalRevenue#" )

无需额外数据库调用的复杂筛选

var allProducts = queryExecute( "SELECT * FROM products" )

// Apply user-controlled search in memory — safe from SQL injection
// because we're not hitting the DB, and params are still typed
var filtered = queryExecute(
    "SELECT * FROM products WHERE category = :cat AND price < :maxPrice ORDER BY name",
    { cat: url.category, maxPrice: url.maxPrice },
    { dbtype: "query" }
)

适用 QoQ 的场景：

• 无需再次往返数据库，即可筛选或排序已加载的数据集
• 联接来自不同数据源的两个结果集
• 对内存中的数据应用聚合函数（COUNT、SUM、AVG）
• 对已在内存中的结果进行分页

参考资料

• 数据库 / JDBC
• QueryExecute
• 数据源配置
• 防止 SQL 注入