Read the original article：How does a relational database store and access BigInt type data?
Requirement Description
How to correctly access and store BigInt type data in a relational database to ensure that its precision is not lost?
Background Knowledge
ValueType: Used to represent the allowed data field types. The specific type of interface parameters depends on their functions. The type supports bigint (value type is an integer of any length). When using bigint, please note the following points:
- When the field type is bigint, the type in the SQL statement for creating the table should be: UNLIMITED INT.
- The bigint type currently does not support comparison operations, and does not support the following predicates: between, notBetween, greaterThanlessThan, greaterThanOrEqualTo, lessThanOrEqualTo, orderByAsc, orderByDesc.
- When writing data to a bigint field, you need to specify the data type as BigInt by using the BigInt() method or by adding 'n' at the end of the data, such as 'let data = BigInt(1234)' or 'let data = 1234n'.
- If data of the number type is written into a bigint field, the return type of the data when queried will be number, not bigint.
Implementation Steps
- When creating a table in the database, declare the BigInt data type as: UNLIMITED INT.
- Construct data and insert it into the table, using BigInt() to generate BigInt type data.
- Query the database and use resultSet.getValue() to retrieve BigInt type data.
Code Snippet / Configuration
The complete example code is as follows:
import { relationalStore } from "@kit.ArkData";
import { common } from "@kit.AbilityKit";
import { BusinessError, systemDateTime } from "@kit.BasicServicesKit";
@Entry
@Component
struct BigintRdbDemo {
@State message: string | undefined = undefined;
private context = this.getUIContext().getHostContext() as common.UIAbilityContext;
private promptAction = this.getUIContext().getPromptAction();
private storeConfig: relationalStore.StoreConfig = {
name: "BigintRdbDemo.db",
securityLevel: relationalStore.SecurityLevel.S1,
};
store: relationalStore.RdbStore | undefined = undefined;
build() {
Column({ space: 10 }) {
Text(this.message || 'Hello World!');
Button('Initializing Database Tables')
.width(150)
.type(ButtonType.ROUNDED_RECTANGLE)
.backgroundColor('#0a59f7')
.onClick(() => {
relationalStore.getRdbStore(this.context, this.storeConfig, (err, store) => {
if (err) {
console.error(`Failed to get RdbStore. Code:${err.code}, message:${err.message}`);
this.promptAction.showToast({ message: 'Failed to initialize the database' });
return;
}
console.info('Succeeded in getting RdbStore.');
this.store = store;
// Step 1: Declare the BigInt data type as UNLIMITED INT when creating the table.
const sqlCreateTable =
'CREATE TABLE IF NOT EXISTS EMPLOYEE (ID INTEGER PRIMARY KEY AUTOINCREMENT, IDENTITY UNLIMITED INT)';
store.executeSql(sqlCreateTable) // Create a data table to facilitate subsequent calls to the insert interface for data insertion.
.then(() => {
this.promptAction.showToast({ message: 'Database table initialized successfully.' });
})
.catch((err: BusinessError) => {
this.promptAction.showToast({ message: 'Failed to initialize the database table' });
console.error(`Failed to executeSql. Code:${err.code}, message:${err.message}`);
});
});
});
Button('Inserting bigInt type data')
.width(150)
.type(ButtonType.ROUNDED_RECTANGLE)
.backgroundColor('#0a59f7')
.onClick(() => {
let time = systemDateTime.getTime(true);
let dataList = new Array<relationalStore.ValuesBucket>();
for (let index = 0; index < 10; index++) {
let data: relationalStore.ValuesBucket = {
// Step 2: Construct data and insert it into the table, using BigInt() to generate bigint type data.
"IDENTITY": BigInt(time * 10000 + index)
};
dataList.push(data);
}
this.store?.batchInsert("EMPLOYEE", dataList, (err, ret) => {
if (err) {
this.promptAction.showToast({ message: 'Failed to insert data.' });
console.error(`insertData() failed, err.message: ${err.message}, err.code: ${err.code}`);
return;
}
this.promptAction.showToast({ message: 'Data inserted successfully.' });
console.info(`insertData() finished: ${ret}`);
});
});
Button('Querying bigInt type data')
.width(150)
.type(ButtonType.ROUNDED_RECTANGLE)
.backgroundColor('#0a59f7')
.onClick(() => {
this.store?.querySql("select * from EMPLOYEE limit 1", (err, resultSet) => {
if (err) {
this.promptAction.showToast({ message: 'Failed to query data.' });
console.error(`Query failed, code is ${err.code},message is ${err.message}`);
return;
}
// resultSet is a cursor for a set of data records, initially pointing to the -1st record by default, with valid data starting from 0.
while (resultSet.goToNextRow()) {
const id = resultSet.getLong(resultSet.getColumnIndex("ID"));
// Step 3: Query the database and use resultSet.getValue() to retrieve BigInt type data.
const identity = resultSet.getValue(resultSet.getColumnIndex("IDENTITY"));
this.message = identity?.toString();
console.info(`id=${id}, identity=${identity}`);
}
this.promptAction.showToast({ message: 'Data query succeeded.' });
// Release dataset memory
resultSet.close();
});
});
}
.justifyContent(FlexAlign.Center)
.height('100%')
.width('100%')
.padding(40)
}
}
Test Results 
Limitations or Considerations
- This example supports API Version 20 Release and later versions.
- This example supports HarmonyOS 6.0.0 Release SDK and later versions.
- This example requires DevEco Studio 6.0.0 Release or later for building and running.
Top comments (0)