informat.datasource Data Source Related Operations

Overview

Use informat.datasource to perform operations related to data sources

getConnection

Get a database connection from the connection pool

informat.datasource.getConnection(moduleId, dataSourceKey, setting);

TIP

• Connection creation fails will throw an exception
• When autoCommit is set to true, transactions will be automatically committed when executing update, insert, and delete statements
• After the script execution is complete, you need to manually close the created connection

Parameter	Type	Description
moduleId	String	Data source module identifier
dataSourceKey	String	Data source identifier
setting	DataSourceConnnectionSetting	Data source connection setting

Return Value Database connection, type JDBCConnection

Example

const connection = informat.datasource.getConnection("dataSource", "db_informat", {
  autoCommit: true,
});
connection.select("select * form table", (row) => {
  console.log(row.getString(1));
});

// Close the connection
connection.close();

Connection

select

Execute query, if query fails will throw exception

connection.select(sql, handler, ...args);

Parameter	Type	Description
sql	String	Query SQL
handler(ResultSet)	Function	Query result callback function
args	...Object	Query parameters

Example 1

//查询 user 表中name等于tom的数据
connection.select(
  "select * from user where name=?",
  (rs) => {
    console.log(rs.getString("name"));
  },
  "tom"
);

Example 2 Using like

//查询 user 表中name包含tom的数据
connection.select(
  ("select * from user where name like concat(" % ",?,") % ")",
  (rs) => {
    console.log(rs.getString("name"));
  },
  "tom"
);

Example 3 Using in

//查询 user 表中name是tom或jerry的数据
connection.select(
  "select name from user where name in(?,?)",
  (rs) => {
    console.log(rs.getString("name"));
  },
  "tom",
  "jerry"
);

insert

Execute insert, if insert fails will throw exception

connection.insert(sql, returnAutoGeneratedKeys, ...args);

Parameter	Type	Description
sql	String	Query SQL
returnAutoGeneratedKeys	Boolean	Whether to return auto-generated primary key ID
args	...Object	Insert parameters

Return Value

Type String

If returnAutoGeneratedKeys is set to true and the operation is insert, it will return the auto-generated primary key ID of the last inserted data

Example

connection.insert("insert into user(name,age) values (?, ?)", true, "tom", 10);

update

Execute update or delete, if update or delete fails will throw exception

connection.update(sql, ...args);

Parameter	Type	Description
sql	String	Query SQL
args	...Object	Update parameters

Return Value Type Integer

Returns the number of records successfully updated or deleted

Example

Update

connection.update("update user set age=?", 10);

Delete

connection.update("delete from user where age=?", 10);

commit

Commit transaction, if commit transaction fails will throw exception

connection.commit();

rollback

Roll back transaction, if roll back transaction fails will throw exception

connection.rollback();

close

Close the database connection

connection.close();

ResultSet

TIP

columnIndex 为索引时，起始索引为 1

Method	Return Value	Description
getString(columnIndex)	String	Get String type column by index or name
getBoolean(columnIndex)	Boolean	Get Boolean type column by index or name
getByte(columnIndex)	Byte	Get Byte type column by index or name
getShort(columnIndex)	Short	Get Short type column by index or name
getInt(columnIndex)	Integer	Get Integer type column by index or name
getLong(columnIndex)	Long	Get Long type column by index or name
getFloat(columnIndex)	Float	Get Float type column by index or name
getDouble(columnIndex)	Double	Get Double type column by index or name
getBytes(columnIndex)	Byte[]	Get byte[] type column by index or name
getDate(columnIndex)	Date	Get Date type column by index or name
getTime(columnIndex)	Time	Get Time type column by index or name
getTimestamp(columnIndex)	Timestamp	Get Timestamp type column by index or name
getObject(columnIndex)	Object	Get Object type column by index or name
getBigDecimal(columnIndex)	BigDecimal	Get BigDecimal type column by index or name
getColumnCount()	Integer	Returns the number of columns in the result set
getColumnName(columnIndex)	String	Returns the name of the column at the specified index
getColumnTypeName(columnIndex)	String	Returns the type name of the column at the specified index