DbUtil

DbUtil provides utility methods for interacting with a database, allowing you to query, insert, update, and delete data.

Methods

listDBConnections()

Description: Lists all database connections.

Returns: A list of all database connections as a DBConnection[].

Example: Search for database connection by name to get driver.

var dbs = DbUtil.listDBConnections()
for (let i = 0; i < dbs.size(); i++) {
    var db = dbs.get(i)
    if (db.getName().contains("Sample"))
        return db.getDriver().getJdbcDriver()
}

---

dbs()

Description: List all available Databases (DbExecutor objects) in the system in the autocomplete.

Example: Search for database connection with the help of the autocomplete

var dbExecutorObj = DbUtil.dbs().sample_db

Examples and further documentation can be found DbExecutor.

---

getDbExecutorByName(dbName: String)

Description: Object that provides database utility methods for querying, inserting, updating, and deleting data.

Parameters:

• dbName (type: String): The name of the database.

Returns: A DbExecutor object.

Examples and further documentation can be found DbExecutor.

---

queryValue(dbId: Integer, query: String)(Deprecated)

Description: Executes a query on the specified database and returns a single string value. It's used for retrieving specific data from a database.

Parameters:

• dbId (type: Integer): The identifier of the database to query.

• query (type: String): The SQL query to execute.

Returns: A string containing the result of the query.

Example: Get the email address of a user based on their username.

Use Case: Useful for retrieving user contact information from a database

var userEmail = DbUtil.queryValue(2, "SELECT email from demo501db.users where username like 'Samuel'")

---

insert(dbId: Integer, sql: String)(Deprecated)

Description: Inserts data into the specified database using an SQL insert statement and returns the number of affected rows. It's used for adding new data records to a database.

Parameters:

• dbId (type: Integer): The identifier of the database to insert into.

• sql (type: String): The SQL insert statement.

Returns: An Integer representing the number of rows affected by the insert operation.

Example: Insert a new product into the products database.

Use Case: Common in e-commerce systems for adding new items to the product catalog.

var newProductInsert = "INSERT INTO products (name, price) VALUES ('New Product', 50)";
var insertedRowCount = DbUtil.insert(2, newProductInsert);

---

update(dbId: Integer, sql: String)(Deprecated)

Description: Updates data in the specified database using an SQL update statement and returns the number of affected rows. It's used for modifying existing data records in a database.

Parameters:

• dbId (type: Integer): The identifier of the database to update.

• sql (type: String): The SQL update statement.

Returns: An Integer representing the number of rows affected by the update operation.

Example: Update the status of an order in the orders database.

Use Case: Common in order management systems to reflect the order's status changes.

var updateOrderStatus = "UPDATE orders SET status = 'Shipped' WHERE order_id = 1005";
var updatedRowCount = DbUtil.update(3, updateOrderStatus);

---

delete(dbId: Integer, sql: String)(Deprecated)

Description: Deletes data from the specified database using an SQL delete statement and returns the number of affected rows. It's used for removing data records from a database.

Parameters:

• dbId (type: Integer): The identifier of the database to delete from.

• sql (type: String): The SQL delete statement.

Returns: An Integer representing the number of rows affected by the delete operation.

Example: Delete expired tasks from the tasks database.

Use Case: Applicable in task management systems to clean up and remove completed or outdated tasks.

var deleteExpiredTasks = "DELETE FROM tasks WHERE status = 'Expired'";
var deletedRowCount = DbUtil.delete(4, deleteExpiredTasks);