This documentation is not maintained. Please refer to doc.castsoftware.com/technologies to find the latest updates.

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

JDBIv2 and v3 up to 3.39.1(tick)

AIP Core compatibility

AIP Core release

Supported

8.3.x(tick)

Supported DBMS servers

DBMSSupported
CSS/PostgreSQL      (tick)

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 updateyou can manually install the new release using the Application - Extensions interface.


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:

IconDescriptionWhen is this object created ?

JDBI SQL Queryan objects is created for each SQL query found and resolved in a JDBI method call 
Link TypeCaller typeCallee typeSupported Jdbi Methods and Annotations

callLink

Java methodJDBI SQL Query
v2 APIs
Handle APIs

org.skife.jdbi.v2.Handle.insert
org.skife.jdbi.v2.Handle.update
org.skife.jdbi.v2.Handle.execute

Batch APIs

org.skife.jdbi.v2.Batch.execute
org.skife.jdbi.v2.PreparedBatch.execute
org.skife.jdbi.v2.PreparedBatch.executeAndGenerateKeys

Call APIs

org.skife.jdbi.v2.Call.invoke

Script APIs

org.skife.jdbi.v2.Script.execute
org.skife.jdbi.v2.Script.executeAsSeparateStatements

Update APIs

org.skife.jdbi.v2.Update.execute
org.skife.jdbi.v2.Update.executeAndReturnGeneratedKeys

Query APIs

org.skife.jdbi.v2.Query.list
org.skife.jdbi.v2.Query.first

v3 APIs
Handle APIs

org.jdbi.v3.core.Handle.execute

Batch APIs

org.jdbi.v3.core.statement.Batch.execute
org.jdbi.v3.core.statement.PreparedBatch.execute
org.jdbi.v3.core.statement.PreparedBatch.executeAndGetModCount
org.jdbi.v3.core.statement.PreparedBatch.executeAndReturnGeneratedKeys
org.jdbi.v3.core.statement.PreparedBatch.executePreparedBatch

Call APIs

org.jdbi.v3.core.statement.Call.invoke

Script APIs

org.jdbi.v3.core.statement.Script.execute
org.jdbi.v3.core.statement.Script.executeAsSeparateStatements

Update APIs

org.jdbi.v3.core.statement.Update.execute
org.jdbi.v3.core.statement.Update.executeAndReturnGeneratedKeys

Query APIs

org.jdbi.v3.core.statement.Query.execute
org.jdbi.v3.core.result.ResultIterable.filter
org.jdbi.v3.core.result.ResultIterable.findFirst
org.jdbi.v3.core.result.ResultIterable.findOne
org.jdbi.v3.core.result.ResultIterable.findOnly
org.jdbi.v3.core.result.ResultIterable.first
org.jdbi.v3.core.result.ResultIterable.forEach
org.jdbi.v3.core.result.ResultIterable.forEachWithCount
org.jdbi.v3.core.result.ResultIterable.iterator
org.jdbi.v3.core.result.ResultIterable.list
org.jdbi.v3.core.result.ResultIterable.map
org.jdbi.v3.core.result.ResultIterable.of
org.jdbi.v3.core.result.ResultIterable.one
org.jdbi.v3.core.result.ResultIterable.reduce
org.jdbi.v3.core.result.ResultIterable.stream
org.jdbi.v3.core.result.ResultIterable.useIterator
org.jdbi.v3.core.result.ResultIterable.useStream
org.jdbi.v3.core.result.ResultIterable.withIterator
org.jdbi.v3.core.result.ResultIterable.withStream

callLinkDeclarative API annotated Interface methodJDBI SQL Query
v2 APIs
  • @SqlQuery - org.skife.jdbi.v2.sqlobject.SqlQuery
  • @SqlUpdate - org.skife.jdbi.v2.sqlobject.SqlUpdate
  • @SqlBatch - org.skife.jdbi.v2.sqlobject.SqlBatch
  • @SqlCall - org.skife.jdbi.v2.sqlobject.SqlCall
v3 APIs
  • @SqlQuery - org.jdbi.v3.sqlobject.statement.SqlQuery

  • @SqlUpdate - org.jdbi.v3.sqlobject.statement.SqlUpdate

  • @SqlBatch - org.jdbi.v3.sqlobject.statement.SqlBatch

  • @SqlCall - org.jdbi.v3.sqlobject.statement.SqlCall

  • @SqlScript - org.jdbi.v3.sqlobject.statement.SqlScript

useLinkJDBI SQL QueryTable, ViewCreated by SQL Analyzer when DDL source files are analyzed
callLinkJDBI SQL QueryProcedure
useLinkJDBI SQL QueryMissing TableCreated by Missing tables and procedures for JEE extension when the object is not analyzed.
callLinkJDBI SQL QueryMissing 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());
        });
}

Click to enlarge

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) {}
        });
}

Click to enlarge

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();
        });
}

Click to enlarge

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);
        });
}

Click to enlarge

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);  

Limitations

  • Object will not be created if evaluation fails to resolve the necessary parameter