JDBI - 1.0
Extension ID
com.castsoftware.java.jdbi
What’s new?
See JDBI - 1.0 - Release Notes for more information.
Description
This extension provides support for JDBI v2 and v3, Fluent API (Core API) and Declarative API (SQL Object API). JDBI (Java Database Interface) is an open source Java library (Apache license) that uses lambda expressions and reflection to provide a friendlier, higher level interface than JDBC to access the database.
In what situation should you install this extension?
If your Java application uses JDBI API to access a relational database.
Technology support
| Framework | Version | Supported |
|---|---|---|
| JDBI | v2 and v3 up to 3.39.1 |  |
AIP Core compatibility
| AIP Core release | Supported |
|---|---|
| 8.3.x | |
Download and installation instructions
For JEE applications using JDBI, the extension will be automatically installed by CAST Console. This is in place since october 2023.
For upgrade, if the Extension Strategy is not set to Auto update, you can manually install the new release using the Application -Extensionsinterface.
What results can you expect?
Once the analysis/snapshot generation has completed, you can view the results in the normal manner. The following objects and links will be displayed in CAST Enlighten:
Objects
The following object type is created and displayed in CAST Enlighten:
| Icon | Description | When is this object created ? |
|---|---|---|
|
JDBI SQL Query | an object is created for each SQL query found and resolved in a JDBI method call |
 |
JDBI Unknown SQL Query | an object is created for each unresolved SQL query found in a JDBI method call |
Links
| Link Type | Caller type | Callee type | Supported Jdbi Methods and Annotations |
|---|---|---|---|
callLink |
Java method | JDBI SQL Query | |
| callLink | Declarative API annotated Interface method | JDBI SQL Query | |
| useLink | JDBI SQL Query | Table, View | Created by SQL Analyzer when DDL source files are analyzed |
| callLink | JDBI SQL Query | Procedure | |
| useLink | JDBI SQL Query | Missing Table | Created by Missing tables and procedures for JEE extension when the object is not analyzed. |
| callLink | JDBI SQL Query | Missing Procedure |
Code Examples
Fluent API
Fluent API Examples
list ResultIterable API to perform Select operation
Select Operation
public void whenException_thenTransactionIsRolledBack() {
Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
jdbi.useHandle(handle -> {
List<Map<String, Object>> list = handle.select("SELECT * FROM PROJECT_14").mapToMap().list();
assertEquals(0, list.size());
});
}
execute Handle API to perform Insert operation
Insert Operation
public void whenException_thenTransactionIsRolledBack() {
Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
jdbi.useHandle(handle -> {
try {
handle.useTransaction(h -> {
h.execute("create table PROJECT_14 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))");
h.execute("INSERT INTO PROJECT_14 (NAME, URL) VALUES ('tutorials', 'https://github.com/eugenp/tutorials')");
List<Map<String, Object>> list = handle.select("SELECT * FROM PROJECT_14").mapToMap().list();
assertTrue(h.isInTransaction());
assertEquals(1, list.size());
throw new Exception("rollback");
});
} catch (Exception ignored) {}
});
}
createUpdate Update API to perform Update operation
Update Operation
public void whenParameters_thenReplacement() {
Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
jdbi.useHandle(handle -> {
Update update = handle.createUpdate("UPDATE PROJECT_10 SET url = 'github.com/spring/REST-With-Spring' WHERE name = 'REST with Spring'");
int numRowsUpdated = update.execute();
});
}
createUpdate Update API to perform Delete operation
Delete Operation
public void whenParameters_thenReplacement() {
Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
jdbi.useHandle(handle -> {
Update delete = handle.createUpdate("DELETE FROM PROJECT_10 WHERE NAME = 'tutorials'");
delete_rows = delete.execute();
assertEquals(1, delete_rows);
});
}
Declarative API
Declarative API Examples
Jdbi SQL Object extension Interface
package com.jdbi.declartive.users;
import org.jdbi.v3.sqlobject.config.RegisterBeanMapper;
import org.jdbi.v3.sqlobject.statement.SqlQuery;
import org.jdbi.v3.sqlobject.statement.SqlUpdate;
import org.jdbi.v3.sqlobject.statement.SqlCall;
import com.jdbi.declartive.users.User;
@RegisterBeanMapper(User.class)
public interface UserDao {
@SqlUpdate("CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT)")
void createTable();
@SqlUpdate("UPDATE users SET name = :name, age = :age WHERE id = :id")
void updateUser(@BindBean User user);
@SqlUpdate("INSERT INTO users (id, name, age) VALUES (:id, :name, :age)")
void insert(@BindBean User user);
@SqlQuery("SELECT * FROM users")
List<User> getAll();
@SqlCall("{call delete_user(:id)}")
int deleteUser(int id);
@SqlCall("{call GETUSER(:id)}")
int getUser(int id);
}
JdbiDeclarativeExample class to call JDBI Interface methods
package com.jdbi.declartive.users;
import org.jdbi.v3.core.Jdbi;
public class JdbiDeclarativeExample {
public static void main(String[] args) {
// Create a Jdbi instance and configure it to connect to your database
Jdbi jdbi = Jdbi.create("jdbc:mysql://localhost:3306/mydatabase", "username", "password");
// Define your data access object (DAO) interface
UserDao userDao = jdbi.onDemand(UserDao.class);
CompanyDao companyDao = jdbi.onDemand(UserDao.class);
// Use the DAO to perform database operations
userDao.createTable();
companyDao.createCompanyTable();
// Insert a new user
User user = new User(1, "John Doe", 25);
userDao.insert(user);
Company company = new Company(1,"John_company");
companyDao.insert_company_details(company);
company = new Company(2,"anu_company");
companyDao.insert_company_details(company);
companyDao.deleteCompany(1);
user = new User(1, "John Doe", 26);
userDao.updateUser(user);
user = new User(2, "Dev", 25);
userDao.insert(user);
userDao.getUser(2);
userDao.deleteUser(2);
// Retrieve all users
List<User> users = userDao.getAll();
for (User u : users) {
System.out.println(u.getName());
}
}
}
Select Operation
@SqlQuery("SELECT * FROM users")
List<User> getAll();
Insert Operation
@SqlUpdate("INSERT INTO users (id, name, age) VALUES (:id, :name, :age)")
void insert(@BindBean User user);
Update Operation
@SqlUpdate("UPDATE users SET name = :name, age = :age WHERE id = :id")
void updateUser(@BindBean User user);
Delete Operation
@SqlCall("{call delete_user(:id)}")
int deleteUser(int id);
Unresolved Case
Limitations
- Unknown object will be created if evaluation fails to resolve the necessary parameter