Keycloak how to access DB from protocol mapper

Question

I use a protocol mapper to enrich the token with data from a custom database. How should I deploy the driver to use in the protocol mapper? If II copy the driver in /standalone/lib/ext keycloak folder the when the mapper is executing I get the error

SQL exception occuredjava.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@.......:1521/CASDB

where should the driver be placed? Is it really necessary to deploy the driver as keycloak module?

my protocol mapper code

@Override
protected void setClaim(IDToken token, ProtocolMapperModel mappingModel, UserSessionModel userSession, KeycloakSession keycloakSession, ClientSessionContext clientSessionCtx) {
    String field = mappingModel.getConfig().get(FIELD_NAME);
    String type = mappingModel.getConfig().get(TYPE);

    String value = "Test " + type;

    System.out.println(">>>>>>>>>>>> " + type);

    try {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        Connection con = DriverManager.getConnection(
                "jdbc:oracle:thin:@....:1521/CASDB",
                "....",
                "....");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM CUSTOMER where CUSTOMER_ID = .....");

        rs.close();
        stmt.close();
        con.close();
    } catch (SQLException e) {
        System.out.println("SQL exception occured" + e);
    } finally {

    }


    OIDCAttributeMapperHelper.mapClaim(token, mappingModel, value);
}

the error is

12:54:40,579 ERROR [org.keycloak.services.error.KeycloakErrorHandler] (default task-6) Uncaught server error: java.lang.NoClassDefFoundError: oracle/jdbc/driver/OracleDriver
at com.betex.keycloak.mapper.UserAttributeMapper.setClaim(UserAttributeMapper.java:72)
at org.keycloak.protocol.oidc.mappers.AbstractOIDCProtocolMapper.transformUserInfoToken(AbstractOIDCProtocolMapper.java:71)
at org.keycloak.protocol.oidc.TokenManager.lambda$transformUserInfoAccessToken$8(TokenManager.java:716)
at java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
at java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:175)
at java.util.ArrayList.forEach(ArrayList.java:1257)
at java.util.stream.SortedOps$RefSortingSink.end(SortedOps.java:390)
at java.util.stream.Sink$ChainedReference.end(Sink.java:258)
at java.util.stream.Sink$C

But if test the connection via wildfly datasource it works ok

Answer 1

I found the solution creating a XADatasource on wildfly server and obtaining it via JNDI inside the code of protocol mapper. Where the DatasourceName is the name defined in your wildfly datasource config

Sample code :

InitialContext initialContext = new InitialContext();
Context context = (Context) initialContext.lookup("java:jboss");
DataSource DATASOURCE = (DataSource) context.lookup("/datasources/DatasourceName");