informat.jdbc Database Operations

Overview

Use the informat.jdbc object for database operations

INFO

To connect to databases like MYSQL, SQLSERVER, DB2, Oracle, etc., you need to download the corresponding JDBC drivers.
Directory: {Zixin installation directory}/instance/informat-biz/libs

createConnection

Create a database connection

javascript

informat.jdbc.createConnection(connection);

TIP

An exception will be thrown if the connection fails to create
When autoCommit is set to true, transactions will be automatically committed when executing update, insert, delete statements
If an error occurs during execution, it will be automatically rolled back
The created connection will be automatically closed after the script execution ends

Parameter	Type	Description
connection	ConnectionInfo	Database connection information

Return Value

Returns a database connection object Connection, which can be used for database CRUD operations

Example

javascript

const connection = informat.jdbc.createConnection({
  dburl: "jdbc:oracle:thin:@host/XE",
  dbuser: "user",
  dbpassword: "pass",
  driverClassName: "oracle.jdbc.driver.OracleDriver",
  autoCommit: false,
});
connection.select("select * form table", (row) => {
  console.log(row.getString(1));
});

Connection

select

Execute a query, an exception will be thrown if the query fails

javascript

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

javascript

// Query data where name equals tom in user table
connection.select(
  "select * from user where name=?",
  (rs) => {
    console.log(rs.getString("name"));
  },
  "tom"
);

Example 2 Using like

javascript

// Query data where name contains tom in user table
connection.select(
  ("select * from user where name like concat(" % ",?,") % ")",
  (rs) => {
    console.log(rs.getString("name"));
  },
  "tom"
);

Example 3 Using in

javascript

// Query data where name is tom or jerry in user table
connection.select(
  "select name from user where name in(?,?)",
  (rs) => {
    console.log(rs.getString("name"));
  },
  "tom",
  "jerry"
);

insert

Execute an insert, an exception will be thrown if the insert fails

javascript

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

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

Return Value

Type: Integer

If returnAutoGeneratedKeys is set to true and the operation is an insert, returns the auto-increment ID of the last inserted data

Example

javascript

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

update

Execute an update or delete, an exception will be thrown if the update or delete fails

javascript

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

UpdateDelete

javascript

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

javascript

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

commit

Commit a transaction, an exception will be thrown if the commit fails

javascript

connection.commit();

rollback

Rollback a transaction, an exception will be thrown if the rollback fails

javascript

connection.rollback();

callProcedure

Execute a stored procedure, suitable for scenarios with only IN parameters

javascript

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

Parameter	Type	Description
sql	`String`	Stored procedure call statement
args	`...Object`	IN parameters (passed in order, null supported)

TIP

callProcedure only supports IN parameters, does not return OUT parameter values
Suitable for scenarios where only operations need to be performed without getting return values
If you need to get return values from OUT/INOUT parameters, please use callProcedureFull

Example 1 - Stored procedure without parameters

javascript

var conn = informat.jdbc.systemConnection();
// Call a stored procedure without parameters
conn.callProcedure("CALL update_all_status()");

Example 2 - Stored procedure with IN parameters

javascript

var conn = informat.jdbc.systemConnection();
// Call a stored procedure with parameters, pass parameters in order
conn.callProcedure("CALL insert_user(?, ?, ?)", "tom", 25, "developer");

Example 3 - PostgreSQL type conversion

javascript

var conn = informat.jdbc.systemConnection();
// PostgreSQL requires explicit type conversion
conn.callProcedure("CALL update_user_status(?, CAST(? AS INTEGER))", "u001", 1);

callProcedureFull

Execute stored procedures, supporting IN, OUT, INOUT parameters and result set handling

javascript

connection.callProcedureFull(sql, params, handler);

Parameter	Type	Description
sql	`String`	Stored procedure call statement
params	List<ProcParam>	Parameter array (supports IN/OUT/INOUT)
handler(ResultSet)	`Function`	Result set callback function (required, handles returned result set data)

Note

handler parameter is required, used to handle result sets returned by stored procedures
If the stored procedure does not return a result set, the handler function body can be null, but the parameter must be provided
Return values from OUT/INOUT parameters are obtained through the Object returned by the method, result set data is handled through the handler

Parameter Object Structure

javascript

// IN parameter
{
    value: 'parameter value',     // Parameter value
    sqlType: 4,        // SQL type (required for PostgreSQL when parameter value is numeric, otherwise explicit type conversion is needed in SQL)
    isOut: false       // Mark as IN parameter
}

// OUT parameter (supported only by MySQL, Oracle, etc.; PostgreSQL does not support pure OUT)
{
    name: 'parameterName',      // Key name for return value (optional)
    sqlType: 4,        // SQL type (required, see type table below)
    isOut: true,       // Mark as OUT parameter
    isOutOnly: true    // true=pure OUT
}

// INOUT parameter
{
    name: 'paramName',  // Key name for return value (optional)
    value: initialValue,       // Input value (can be null)
    sqlType: 4,        // SQL type (required)
    isOut: true,       // Mark as INOUT
    isOutOnly: false   // false=INOUT
}

SQL Type Constants Table

Database Type	sqlType Value	Description
BIT	-7	Bit type
TINYINT	-6	Tiny integer (8 bits)
SMALLINT	5	Small integer (16 bits)
INTEGER	4	Integer (32 bits)
BIGINT	-5	Big integer (64 bits)
FLOAT	6	Float
REAL	7	Real (single precision float)
DOUBLE	8	Double (double precision float)
NUMERIC	2	Numeric
DECIMAL	3	Decimal
CHAR	1	Character (fixed length)
VARCHAR	12	Variable character (variable length)
LONGVARCHAR	-1	Long varchar
NCHAR	-15	Unicode character (fixed length)
NVARCHAR	-9	Unicode variable character (variable length)
LONGNVARCHAR	-16	Long unicode varchar
DATE	91	Date
TIME	92	Time
TIMESTAMP	93	Timestamp
TIME_WITH_TIMEZONE	2013	Time with timezone
TIMESTAMP_WITH_TIMEZONE	2014	Timestamp with timezone
BINARY	-2	Binary (fixed length)
VARBINARY	-3	Variable binary (variable length)
LONGVARBINARY	-4	Long binary data
BLOB	2004	Binary large object
CLOB	2005	Character large object
NCLOB	2011	Unicode character large object
BOOLEAN	16	Boolean
ROWID	-8	Row identifier
NULL	0	SQL NULL value
OTHER	1111	Other types (PostgreSQL cursors can use this type)
JAVA_OBJECT	2000	Java object
DISTINCT	2001	User-defined type
STRUCT	2002	SQL structured type
ARRAY	2003	SQL array type
REF	2006	SQL reference type
DATALINK	70	Data link type
REF_CURSOR	2012	Oracle cursor type
SQLXML	2009	XML type

Common Types Quick Reference

Numeric Types:

Integer: TINYINT(-6) SMALLINT(5) INTEGER(4) BIGINT(-5)
Decimal: FLOAT(6) DOUBLE(8) NUMERIC(2) DECIMAL(3)

Character Types:

Regular character: CHAR(1) VARCHAR(12) LONGVARCHAR(-1)
Unicode character: NCHAR(-15) NVARCHAR(-9) LONGNVARCHAR(-16)

Date and Time:

DATE(91) TIME(92) TIMESTAMP(93)

Binary:

BINARY(-2) VARBINARY(-3) BLOB(2004)

Special Types:

BOOLEAN(16) OTHER(1111) REF_CURSOR(2012)

Return Value

Type: Object, containing key-value pairs of all OUT/INOUT parameters:

If name is set, use name as the key
Otherwise, use out1, out2, etc. as keys

handler Callback Function

javascript

function(resultSet) {
    // resultSet is a ResultSet object
    // You can use methods like getString, getInt to retrieve data
}

Example 1 - MySQL: Basic INOUT Parameters

javascript

var conn = informat.jdbc.createConnection({
  dburl: "jdbc:mysql://localhost:3306/mydb",
  dbuser: "root",
  dbpassword: "password",
  driverClassName: "com.mysql.cj.jdbc.Driver",
});

var params = [
  { value: 1, isOut: false }, // IN parameter: status
  {
    name: "count",
    value: null,
    sqlType: 4, // INTEGER
    isOut: true,
    isOutOnly: false, // INOUT parameter
  },
];

var result = conn.callProcedureFull("CALL count_users_by_status(?, ?)", params, function (rs) {
  // If the stored procedure doesn't return a result set, the function body can be empty
});

console.log("User count:", result.count);

Example 2 - PostgreSQL: Multiple INOUT Parameters

javascript

var conn = informat.jdbc.systemConnection();

var params = [
  { value: "u001", isOut: false }, // IN parameter: user ID
  { value: 50, isOut: false }, // IN parameter: points to add
  {
    name: "totalPoints",
    value: null,
    sqlType: 4, // INTEGER
    isOut: true,
    isOutOnly: false,
  },
  {
    name: "message",
    value: null,
    sqlType: 12, // VARCHAR
    isOut: true,
    isOutOnly: false,
  },
];

var result = conn.callProcedureFull(
  "CALL update_user_points(?, CAST(? AS INTEGER), ?, ?)", // The second IN parameter doesn't have sqlType set, explicit type conversion is needed here
  params,
  null //  Can be set to null when there's no result set to process
);

console.log("Updated total points:", result.totalPoints);
console.log("Return message:", result.message);

Example 3 - Create User and Get Return Values

javascript

var conn = informat.jdbc.systemConnection();

// Generate unique ID
var recordId = informat.jdbc.nextRecordId();

var params = [
  { value: recordId, isOut: false },
  { value: "zhangsan", isOut: false },
  { value: "zhang@test.com", isOut: false },
  { value: 25, sqlType: 4, isOut: false },
  {
    name: "userId",
    value: null,
    sqlType: 12, // VARCHAR
    isOut: true,
    isOutOnly: false,
  },
  {
    name: "points",
    value: null,
    sqlType: 4, // INTEGER
    isOut: true,
    isOutOnly: false,
  },
  {
    name: "message",
    value: null,
    sqlType: 12, // VARCHAR
    isOut: true,
    isOutOnly: false,
  },
];

var result = conn.callProcedureFull("CALL register_user(?, ?, ?, ?, ?, ?, ?)", params, null);

console.log("Registration result:", result.message);
if (result.userId) {
  console.log("New user ID:", result.userId);
  console.log("Gift points:", result.points);
}

Example 4 - Process Returned Result Set

javascript

var conn = informat.jdbc.systemConnection();
var users = [];

var params = [
  { value: 1, sqlType: 4, isOut: false }, // IN parameter: status
  {
    name: "totalCount",
    value: null,
    sqlType: 4,
    isOut: true,
    isOutOnly: false,
  },
];

// Use handler to process each row in the result set
var result = conn.callProcedureFull("CALL get_users_with_count(?, ?)", params, function (rs) {
  // This function is called once for each row of data
  users.push({
    id: rs.getString("id"),
    username: rs.getString("username"),
    email: rs.getString("email"),
    age: rs.getInt("age"),
    points: rs.getInt("points"),
  });
});

console.log("Total users:", result.totalCount);
console.log("Found", users.length, "users:");
users.forEach(function (user, index) {
  console.log("  " + (index + 1) + ".", user.username, "- Age:", user.age, "- Points:", user.points);
});

Example 5 - Oracle: Use REF CURSOR

javascript

var conn = informat.jdbc.createConnection({
  dburl: "jdbc:oracle:thin:@localhost:1521/XE",
  dbuser: "user",
  dbpassword: "password",
  driverClassName: "oracle.jdbc.driver.OracleDriver",
});

var employees = [];

var params = [
  { value: "IT", isOut: false }, // IN parameter: department
  {
    name: "cursor",
    sqlType: 2012, // REF_CURSOR
    isOut: true,
    isOutOnly: true, // Oracle supports pure OUT
  },
];

conn.callProcedureFull("{CALL get_employees_by_dept(?, ?)}", params, function (rs) {
  // Cursor data is automatically processed in the handler
  employees.push({
    empId: rs.getString("emp_id"),
    empName: rs.getString("emp_name"),
    salary: rs.getDouble("salary"),
  });
});

console.log("Employee list:", employees);

Example 6 - Get Statistics (Multiple OUT Parameters)

javascript

var conn = informat.jdbc.systemConnection();

var params = [
  { value: 1, sqlType: 4, isOut: false }, // IN parameter: status
  {
    name: "totalCount",
    value: null,
    sqlType: 4, // INTEGER
    isOut: true,
    isOutOnly: false,
  },
  {
    name: "avgSalary",
    value: null,
    sqlType: 8, // DOUBLE
    isOut: true,
    isOutOnly: false,
  },
  {
    name: "totalPoints",
    value: null,
    sqlType: 4, // INTEGER
    isOut: true,
    isOutOnly: false,
  },
];

var result = conn.callProcedureFull("CALL get_user_statistics(?, ?, ?, ?)", params, null);

console.log("Statistics:");
console.log("  Total users:", result.totalCount);
console.log("  Average salary:", result.avgSalary);
console.log("  Total points:", result.totalPoints);

Example 7 - Using Indexes to Get Result Set Fields

javascript

var conn = informat.jdbc.systemConnection();
var data = [];

var params = [{ value: 1, sqlType: 4, isOut: false }];

conn.callProcedureFull("CALL get_users(?)", params, function (rs) {
  // Can use index (starting from 1) or field name
  data.push({
    id: rs.getString(1), // Column 1
    username: rs.getString(2), // Column 2
    email: rs.getString(3), // Column 3
    age: rs.getInt(4), // Column 4
  });
});

console.log("Retrieved data:", data.length, "records");

Example 8 - Handling Multiple Result Sets

javascript

var conn = informat.jdbc.createConnection({
  dburl: "jdbc:mysql://localhost:3306/mydb",
  dbuser: "root",
  dbpassword: "password",
  driverClassName: "com.mysql.cj.jdbc.Driver",
});
var topUsers = [];
var statsInfo = null;

var params = [{ value: 1, sqlType: 4, isOut: false }];

conn.callProcedureFull("CALL get_user_summary(?)", params, function (rs) {
  // Handler automatically processes multiple result sets
  // Can determine which result set it is by column count
  var colCount = rs.getColumnCount();

  if (colCount === 3) {
    // First result set: id, username, points
    topUsers.push({
      id: rs.getString("id"),
      username: rs.getString("username"),
      points: rs.getInt("points"),
    });
  } else if (colCount === 5) {
    // Second result set: statistics
    statsInfo = {
      totalCount: rs.getInt("total_count"),
      avgSalary: rs.getDouble("avg_salary"),
      totalPoints: rs.getInt("total_points"),
      maxAge: rs.getInt("max_age"),
      minAge: rs.getInt("min_age"),
    };
  }
});

console.log("Top users (top 5):", topUsers.length, "people");
topUsers.forEach(function (user, idx) {
  console.log("  " + (idx + 1) + ".", user.username, "- Points:", user.points);
});

if (statsInfo) {
  console.log("Statistics:");
  console.log("  Total:", statsInfo.totalCount);
  console.log("  Average salary:", statsInfo.avgSalary);
  console.log("  Total points:", statsInfo.totalPoints);
  console.log("  Age range:", statsInfo.minAge, "-", statsInfo.maxAge);
}

Example 9 - SQL Server: With Return Value and OUT Parameters

javascript

var conn = informat.jdbc.createConnection({
  dburl: "jdbc:sqlserver://localhost:1433;databaseName=mydb",
  dbuser: "sa",
  dbpassword: "password",
  driverClassName: "com.microsoft.sqlserver.jdbc.SQLServerDriver",
});

var params = [
  {
    name: "returnValue",
    value: null,
    sqlType: 4, // INTEGER (RETURN value)
    isOut: true,
    isOutOnly: true,
  },
  { value: "zhangsan", isOut: false }, // IN parameter: username
  { value: "password123", isOut: false }, // IN parameter: password
  {
    name: "userId",
    value: null,
    sqlType: 12, // VARCHAR
    isOut: true,
    isOutOnly: true,
  },
  {
    name: "roleId",
    value: null,
    sqlType: 4, // INTEGER
    isOut: true,
    isOutOnly: true,
  },
];

// SQL Server: Use {? = CALL ...} syntax to get RETURN value
var result = conn.callProcedureFull("{? = CALL sp_user_login(?, ?, ?, ?)}", params, function (rs) {
  // If the stored procedure returns a result set, handle it here
});

console.log("Return value:", result.returnValue); // 0=success, -1=fail
console.log("User ID:", result.userId);
console.log("Role ID:", result.roleId);

if (result.returnValue === 0) {
  console.log("Login successful");
} else {
  console.log("Login failed");
}

Database Differences

Parameter Type Support:

PostgreSQL: Only supports INOUT parameters, not pure OUT parameters. isOutOnly should be set to false. Supports returning result sets through cursors (sqlType: 1111)
MySQL: Supports three parameter types: IN, OUT, and INOUT. Supports returning multiple result sets
Oracle: Supports IN, OUT, and INOUT. Returns result sets through REF_CURSOR (sqlType: 2012)
SQL Server: Supports IN, OUT, and INOUT. Has a RETURN value (must be the first parameter)

SQL Syntax Differences:

PostgreSQL: CALL procedure_name(?, ?)
MySQL: CALL procedure_name(?, ?)
Oracle: {CALL procedure_name(?, ?)}
SQL Server: {CALL procedure_name(?, ?)} or {? = CALL procedure_name(?, ?)}

Type Conversion:

PostgreSQL: If sqlType is specified in params, there's no need to use CAST(? AS type) in SQL; the system automatically handles type conversion
Other databases: It's recommended to specify sqlType in params to avoid type conversion issues

Notes

Handler parameter is required: Even if the stored procedure doesn't return a result set, a handler function must be provided
OUT/INOUT parameters must set sqlType: Refer to the "SQL Type Constant Table" above for type values
Parameter naming: Set the name attribute for OUT/INOUT parameters to easily get return values
NULL value handling: The initial value of INOUT parameters can be null; need to properly handle returned null values in JavaScript
Multiple result sets handling: The handler function processes all result sets in order. You can determine the current result set through rs.getColumnCount() or column names
Cursor handling: Cursors in PostgreSQL and Oracle are automatically expanded to row-by-row data in the handler
Type specification advantage: Specifying sqlType in params simplifies SQL statements and avoids manual type conversion

tableConnection

Get Database Connection for Application Data Tables

javascript

informat.jdbc.tableConnection();

INFO

Using the application data table database connection allows querying, updating, and deleting application data tables.
If the connection fails to create, an exception will be thrown.
When using data table connections to execute queries, you need to convert data table identifiers to lowercase.

Return Value

Returns a database connection object Connection, which can be used to add, query, delete, and modify data tables in the database.

Example

For example, there is a data table tableDemo

Field identifier	Name
id	ID
name	Name
maxAge	Maximum age

Need to use the following SQL to execute the query

sql

select id, name, max_age from table_demo;

javascript

var ret = [];
var conn = informat.jdbc.tableConnection();
conn.select(`select id,name,create_time from task limit 10`, (rs) => {
  ret.push({
    id: rs.getString("id"),
    name: rs.getString("name"),
    createTime: rs.getTimestamp("create_time"),
  });
});
console.log("ret", ret);

If you want to query the adjacency table corresponding to an associated list field, the query format is:

Data table identifier$field identifier (camelCase to snake_case)

Example

javascript

var ret = [];
var conn = informat.jdbc.tableConnection();
var sql = `select a.id from task$report a 
		inner join task b on a.id=b.id`;
conn.select(sql, (rs) => {
  ret.push(rs.getString("id"));
});
console.log("ret", ret);

systemConnection

Get System Database Connection

javascript

informat.jdbc.systemConnection();

TIP

If the connection fails to create, an exception will be thrown. Using the system database connection allows querying, updating, and deleting all tables in the system.
Using the system database connection allows querying, updating, and deleting all data tables in the system. Note that you should not use this function unless it is absolutely necessary. The internal data tables of Zixin will change with version iterations, and directly accessing the underlying system data tables may cause application functionality exceptions after Zixin version updates.
Note that when using systemConnection to execute SQL statements, the appid in the table name should be obtained using informat.app.appId(). The appid will change during application distribution.

Return Value

Returns a database connection object Connection

Example

javascript

var conn = informat.jdbc.systemConnection();
var ret = [];
conn.select(`select id,name,create_time from v_croe0zft168y3_task limit 10`, (rs) => {
  ret.push({
    id: rs.getString("id"),
    name: rs.getString("name"),
    createTime: rs.getTimestamp("create_time"),
  });
});
console.log("ret", ret);

nextRecordId

Generate Unique ID for Records

javascript

informat.jdbc.nextRecordId();

Return Value Type String, returns a 13-digit string composed of lowercase letters and numbers, with the first character being a lowercase letter.

Example

CallReturn value

javascript

informat.jdbc.nextRecordId();

text

u5wxh9kl3mxvi

safesql

Generate safe SQL, generating complete safe SQL for sql with placeholder ?

javascript

informat.jdbc.safesql(sql, params);

Parameter	Type	Description
sql	`String`	Database statement
params	`...Object`	Input parameters

Return Value Type String, safe SQL

Example

CallReturn Value

javascript

const sql = `update tab set age=?,name=? where id=?`;
const params = [18, "Li Si", `'Zhang' or 1=1`];
const result = informat.jdbc.safesql(sql, params);

sql

update
  tab
set age  = 18,
    name = 'Li Si'
where id = ''
  'Zhang'
  ' or 1=1'

ResultSet

TIP

When columnIndex is used, the starting index is 1

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

informat.jdbc Database Operations ​

Overview ​

createConnection ​

Connection ​

select ​

insert ​

update ​

commit ​

rollback ​

callProcedure ​

callProcedureFull ​

tableConnection ​

systemConnection ​

nextRecordId ​

safesql ​

ResultSet ​

informat.jdbc Database Operations

Overview

createConnection

Connection

select

insert

update

commit

rollback

callProcedure

callProcedureFull

tableConnection

systemConnection

nextRecordId

safesql

ResultSet