FIN 5.3.1 中文文档

外观

sql ​

原文: https://finproducts.atlassian.net/wiki/spaces/JIDD3/pages/34793947262/FIN+5.3.1+sql 中文整理基于 FIN 5.3.1

导读： 关系数据库访问

以下为 API 参考，保留英文以便对照源码；目录已列出（英文标题便于检索）；概述已尽量中译。

目录（导读） ​

  • mixins
  • classes
  • errs
  • docs
  • Overview
  • Connections
    • Connections in Java
  • Statements
    • Escape Sequences
  • Queries
    • sql::Row
  • SQL/Fantom Type Mapping
  • Test Setup
  • Slot Details
    • index
    • make
    • name
    • sqlType
    • toStr
    • type
    • col
    • cols
    • get
    • set
    • trap
    • autoCommit
    • close
    • commit
    • isClosed
    • meta
    • open
    • rollback
    • sql
    • withPrepare
    • checkLinger
    • execute
    • linger
    • log
    • maxConns
    • onClose
    • onOpen
    • password
    • timeout
    • uri
    • username
    • driverName
    • driverVersion
    • driverVersionStr
    • maxColName
    • maxTableName
    • productName
    • productVersion
    • productVersionStr
    • tableExists
    • tableRow
    • tables
    • executeBatch
    • limit
    • prepare
    • query
    • queryEach
    • queryEachWhile

API 正文（英文） ​

errs ​

SqlErrSqlErr indicates an error from the SQL database driver.

docs ​

Overview ​

The sql pod provides a standardized, low level API to work with relational databases. Its basic goals in life:

  • Connections: manage interaction with the database
  • Statements: execute SQL statements
  • Model: model meta-data and relational tables

Connections ​

Connections are managed by the SqlConn class. To open and close connections to the database, simply call the SqlConn.open and SqlConn.close methods.

A pool of Connections is managed by the SqlConnPool class. To use a connection created by this pool, use the SqlConnPool.execute method.

Connections in Java ​

When running in a Java VM, Fantom uses JDBC under the covers. Using MySQL as an example, follow these steps to open a connection in the JVM:

  1. Ensure your JDBC driver is installed and available via the system class path. The best place to stick it is in the "jre/lib/ext" directory. You can use fan -version to locate your JRE directory. The driver is packaged up as something like "mysql-connector-j-9.0.0.jar" or "postgresql-42.7.3.jar".

  2. Ensure the JDBC class is loaded into memory. The simplest way to preload the class is to ensure the classname is defined in "etc/sql/config.props" :

    java.drivers=java.drivers=com.mysql.cj.jdbc.Driver,org.postgresql.Driver

  3. Open a SqlConn instance using the JDBC URL:

    db := SqlConn.open("jdbc:mysql://localhost:3306/fantest", "fantest", "fantest")

If using Microsoft SQL Server:

  1. Assuming you are running Java 1.6 or higher, then make sure you put only "sqljdbc4.jar" into your classpath (do not put "sqljdbc.jar" in the path)
  2. Classname is "com.microsoft.sqlserver.jdbc.SQLServerDriver" (for java.drivers in etc/sql/config.props)
  3. JDBC URL format is "jdbc:sqlserver://{host};database={name}"

Statements ​

SQL statements are created using the SqlConn.sql method. After a statement has been created, it can be executed immediately by calling Statement.execute or it can be prepared for later execution by calling Statement.prepare.

For example, to create a table in MySQL:

db.sql("create table Books (
        id integer auto_increment not null,
        title varchar(128) not null,
        author varchar(128) not null,
        year integer,
        primary key (id))").execute

Prepared statements can be parameterized by including named parameters in the SQL text. For example:

addBook := db.sql("insert into Books (title, author, year)
                   values (@title, @author, @year)").prepare

This statement can then be executed multiple times with different parameters.

addBook.execute(["title":"David Copperfield", "author":"Charles Dickens", "year":1850])
addBook.execute(["title":"Hard Times", "author":"Charles Dickens", "year":1854])
addBook.execute(["title":"The Jungle Book", "author":"Rudyard Kipling", "year":1894])
addBook.execute(["title":"Captains Courageous", "author":"Rudyard Kipling", "year":1897])

Escape Sequences ​

When calling Statement.prepare, the strings \@ and \\ in the Statement's SQL are escaped into @ and \ respectively.

Examples:

In mysql, to refer to the user variable @v1 in a prepared Statement:

select name, \@v1 from foo where id = @id;

In postgres, to use the JSONB operator @> in a prepared Statement:

select * from foo where data \@> '{"x": 99}'::jsonb;

There is an old, undocumented syntax for referring to mysql user variables, using the escape sequence @@v1, for example:

select name, @@v1 from foo where id = @id;

This syntax is deprecated and is no longer supported by default. If you have existing code that uses this undocumented feature, you must add the following entry to "etc/sql/config.props":

deprecatedEscape=true

Queries ​

The result of an SQL query is always a relational table described by fixed columns with zero or more rows. Fantom supports two different mechanisms for retrieving query results: as a list of Rows or by iterating the rows with a closure.

To fetch as a list of rows:

stmt := db.sql("select title, year from Books
                where author = @author and year > @year").prepare
dickensNovels := stmt.query(["author":"Charles Dickens", "year":1850])
kiplingNovels := stmt.query(["author":"Rudyard Kipling", "year":1890])

Or to iterate through the rows:

lastPublished := 0
stmt.queryEach(["author":"Charles Dickens", "year":0]) |Row row|
{
  lastPublished = lastPublished.max(row->year)
}

sql::Row ​

The rows for a given query result all share a list of sql::Cols which describes the meta-data. You can use access the column of each row using the get(Col) or with dynamic invoke:

// using dynamic invoke
dickensNovels.each |Row book| { echo("${book->title}, ${book->year}") }

// using col
title := row.col("title")
year  := row.col("year")
dickensNovels.each |Row book| { echo("${book[title]}, ${book[year]}") }

If you have a large query result, using get(Col) provides a little better performance.

SQL/Fantom Type Mapping ​

The following type specifies the mapping of SQL types to Fantom types:

SQL Type       Fantom Type
--------       -----------
CHAR           sys::Str
VARCHAR        sys::Str
LONGVARCHAR    sys::Str
TEXT[]         sys::List of sys::Str

BIT            sys::Bool

TINYINT        sys::Int
SMALLINT       sys::Int
INTEGER        sys::Int
BIGINT         sys::Int

REAL           sys::Float
FLOAT          sys::Float
DOUBLE         sys::Float

DECIMAL        sys::Decimal
NUMERIC        sys::Decimal

BINARY         sys::Buf
VARBINARY      sys::Buf
LONGVARBINARY  sys::Buf

TIMESTAMP      sys::DateTime
DATE           sys::Date
TIME           sys::Time

BYTEA          sys::Buf

catch-all      sys::Str

Test Setup ​

The unit test sql::SqlTest runs automatically against both mysql and postgres. To run this test, both of these DBMS systems must be installed locally and running on the default port, and they each must have the following one-time configuration applied.

For msyql, setup the fantest database and user account via:

mysql -u root -p
mysql> create user fantest identified by 'fantest';
mysql> create database fantest;
mysql> grant all privileges on fantest.* to fantest;

For postgres, setup the fantest database and user account via:

psql -U postgres
postgres=# create role fantest with login superuser password 'fantest';
postgres=# alter role fantest with login;
postgres=# create schema authorization fantest;

In addition, both JDBC drivers must be installed, and "etc/sql/config.props" must have a reference to the classpath of both drivers, e.g.

java.drivers=java.drivers=com.mysql.cj.jdbc.Driver,org.postgresql.Driver

Col ​

const class Col : Obj

Col models a column of a relational table. Columns are accessed from rows with Row.col and Row.cols

Constructors

makeConstruct a column for the given meta-data.

Fields

indexZero based index of the column in the query result.
nameName of the column.
sqlTypeThe type of the column as defined by the SQL database.
typeType of the column.

Methods

toStrReturn name.

Slot Details ​

index ​

const Int index

Zero based index of the column in the query result.

make ​

new make(Int index, Str name, Type type, Str sqlType)

Construct a column for the given meta-data.

name ​

const Str name

Name of the column.

sqlType ​

const Str sqlType

The type of the column as defined by the SQL database. See type mapping.

toStr ​

virtual override Str toStr()

Return name.

type ​

const Type type

Type of the column. See type mapping.

Row ​

class Row : Obj

Row models a row of a relational table. See pod-doc.

Methods

colGet a column by name.
colsGet a read-only list of the columns.
getGet column value.
setSet a column value.
toStrDump the cells separated by a comma.
trapTrap is used to get or set a column by name.

Slot Details ​

col ​

Col? col(Str name, Bool checked := true)

Get a column by name. If not found and checked is true then throw ArgErr, otherwise return null.

cols ​

Col[] cols()

Get a read-only list of the columns.

get ​

@Operator Obj? get(Col col)

Get column value. See type mapping.

set ​

@Operator Void set(Col col, Obj? val)

Set a column value. See type mapping.

toStr ​

virtual override Str toStr()

Dump the cells separated by a comma.

trap ​

virtual override Obj? trap(Str name, Obj?[]? args := null)

Trap is used to get or set a column by name.

SqlConn ​

mixin SqlConn

SqlConn manages a connection to a relational database. See pod-doc.

Fields

autoCommitIf auto-commit is true then each statement is executed and committed as an individual transaction.

Methods

closeClose the database connection.
commitCommit all the changes made inside the current transaction.
isClosedReturn if close has been called.
metaGet the database meta-data
openOpen a connection to the database specified by the given JDBC uri and username/password credentials.
rollbackUndo any changes made inside the current transaction.
sqlCreate a statement for this database.
withPreparewithPrepare creates a prepared Statement, passes the Statement to the callback, and then closes the prepared Statement.

Slot Details ​

autoCommit ​

abstract Bool autoCommit

If auto-commit is true then each statement is executed and committed as an individual transaction. Otherwise statements are grouped into transaction which must be closed via commit or rollback.

close ​

abstract Bool close()

Close the database connection. Closing a connection already closed is a no-op. This method is guaranteed to never throw an exception. Return true if the connection was closed successfully or false if closed abnormally.

Do not close connections that were created by SqlConnPool. The pool handles that for you.

commit ​

abstract Void commit()

Commit all the changes made inside the current transaction.

isClosed ​

abstract Bool isClosed()

Return if close has been called.

meta ​

abstract SqlMeta meta()

Get the database meta-data

open ​

static SqlConn open(Str uri, Str? username, Str? password)

Open a connection to the database specified by the given JDBC uri and username/password credentials. Raise exception if connection cannot be established. See pod-doc.

rollback ​

abstract Void rollback()

Undo any changes made inside the current transaction.

sql ​

abstract Statement sql(Str sql)

Create a statement for this database.

withPrepare ​

Obj? withPrepare(Str sql, |Statement->Obj?| f)

withPrepare creates a prepared Statement, passes the Statement to the callback, and then closes the prepared Statement.

The method returns the value returned by the callback.

SqlConnPool ​

const class SqlConnPool : Obj

SqlConnPool manages a pool of reusable SQL connections

Constructors

makeIt-block construtor

Fields

autoCommitautoCommit sets the autoCommit field on a connection just after it is opened by the pool.
lingerTime to linger an idle connection before closing it.
logLogger
maxConnsMax number of simultaneous connections to allow before blocking threads
passwordConnection password
timeoutMax time to block waiting for a connection before raising TimeoutErr
uriConnection URI
usernameConnection username

Methods

checkLingerClose idle connections that have lingered past the linger timeout.
closeClose all connections and raise exception on any new executes
executeAllocate a SQL connection inside the given callback.
isClosedReturn if close has been called.
onCloseonClose is invoked just before a connection is closed by the pool.
onOpenonOpen is invoked just after a connection is opened by the pool.

Slot Details ​

autoCommit ​

Bool autoCommit

autoCommit sets the autoCommit field on a connection just after it is opened by the pool.

If auto-commit is true then each statement is executed and committed as an individual transaction. Otherwise statements are grouped into transaction which must be closed via SqlConn.commit or SqlConn.rollback.

checkLinger ​

Void checkLinger()

Close idle connections that have lingered past the linger timeout.

close ​

Void close()

Close all connections and raise exception on any new executes

execute ​

Void execute(|SqlConn| f)

Allocate a SQL connection inside the given callback. If a connection cannot be acquired before timeout elapses then a TimeoutErr is raised. Do not close the connection inside the callback.

isClosed ​

Bool isClosed()

Return if close has been called.

linger ​

const Duration linger := 5min

Time to linger an idle connection before closing it. An external actor must call checkLinger periodically to close idle connetions.

log ​

const Log log := Log.get("sqlPool")

Logger

make ​

new make(|This|? f)

It-block construtor

maxConns ​

const Int maxConns := 10

Max number of simultaneous connections to allow before blocking threads

onClose ​

protected virtual Void onClose(SqlConn c)

onClose is invoked just before a connection is closed by the pool.

onOpen ​

protected virtual Void onOpen(SqlConn c)

onOpen is invoked just after a connection is opened by the pool.

password ​

const Str? password

Connection password

timeout ​

const Duration timeout := 30sec

Max time to block waiting for a connection before raising TimeoutErr

uri ​

const Str uri

Connection URI

username ​

const Str? username

Connection username

SqlErr ​

const class SqlErr : Err

SqlErr indicates an error from the SQL database driver.

Constructors

make

Slot Details ​

make ​

new make(Str? msg, Err? cause := null)

SqlMeta ​

class SqlMeta : Obj

SqlMeta provides access to database meta-data

Methods

driverNameName of connection driver to database
driverVersionVersion of of connection driver to database as "major.minor"
driverVersionStrVersion of of connection driver to database as free-form string
maxColNameMax number of chars in column name or null if no known limit
maxTableNameMax number of chars in table name or null if no known limit
productNameName of database product
productVersionProduct version of database as "major.minor"
productVersionStrProduct version of database as free-form string
tableExistsDoes the specified table exist in the database?
tableRowGet a column meta-data for for the specified table as a prototype row instance.
tablesList the tables in the database.

Slot Details ​

driverName ​

Str driverName()

Name of connection driver to database

driverVersion ​

Version driverVersion()

Version of of connection driver to database as "major.minor"

driverVersionStr ​

Str driverVersionStr()

Version of of connection driver to database as free-form string

maxColName ​

Int? maxColName()

Max number of chars in column name or null if no known limit

maxTableName ​

Int? maxTableName()

Max number of chars in table name or null if no known limit

productName ​

Str productName()

Name of database product

productVersion ​

Version productVersion()

Product version of database as "major.minor"

productVersionStr ​

Str productVersionStr()

Product version of database as free-form string

tableExists ​

Bool tableExists(Str tableName)

Does the specified table exist in the database?

tableRow ​

Row tableRow(Str tableName)

Get a column meta-data for for the specified table as a prototype row instance.

tables ​

Str[] tables()

List the tables in the database.

Statement ​

class Statement : Obj

Statement is an executable statement for a specific database. A statement may be executed immediately or prepared and executed later with parameters. See pod-doc.

Fields

limitMaximum number of rows returned when this statement is executed.
sqlThe SQL text used to create this statement.

Methods

closeClose the statement.
executeExecute a SQL statement and if applicable return a result
executeBatchExecute a batch of commands on a prepared Statement.
preparePrepare this statement by compiling for efficient execution.
queryExecute the statement and return the resulting List of Rows.
queryEachExecute the statement.
queryEachWhileExecute the statement.

Slot Details ​

close ​

Void close()

Close the statement.

execute ​

Obj execute([Str:Obj]? params := null)

Execute a SQL statement and if applicable return a result:

  • If the statement is a query or procedure which produces a result set, then return Row[]
  • If the statement is an insert and auto-generated keys are supported by the connector then return Int[] or Str[] of keys generated
  • Return an Int with the update count

executeBatch ​

Int?[] executeBatch([Str:Obj]?[] paramsList)

Execute a batch of commands on a prepared Statement. If all commands execute successfully, returns an array of update counts.

For each element in the array, if the element is non-null, then it represents an update count giving the number of rows in the database that were affected by the command's execution.

If a given array element is null, it indicates that the command was processed successfully but that the number of rows affected is unknown.

If one of the commands in a batch update fails to execute properly, this method throws a SqlErr that wraps a java.sql.BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch, consistent with the underlying DBMS -- either always continuing to process commands or never continuing to process commands.

limit ​

Int? limit

Maximum number of rows returned when this statement is executed. If limit is exceeded rows are silently dropped. A value of null indicates no limit.

prepare ​

This prepare()

Prepare this statement by compiling for efficient execution. Return this.

query ​

Row[] query([Str:Obj]? params := null)

Execute the statement and return the resulting List of Rows. The Cols are available from List.of.fields or on type.fields of each row instance.

queryEach ​

Void queryEach([Str:Obj]? params, |Row| eachFunc)

Execute the statement. For each row in the result, invoke the specified function eachFunc.

queryEachWhile ​

Obj? queryEachWhile([Str:Obj]? params, |Row->Obj?| eachFunc)

Execute the statement. For each row in the result, invoke the specified function eachFunc. If the function returns non-null, then break the iteration and return the resulting object. Return null if the function returns null for every item.

sql ​

const Str sql

The SQL text used to create this statement.