java spring thymeleaf jooq spelevaluationexception

Thymeleaf view can't find property/field of jOOQ join query

I am new to working with Spring, Thymeleaf and jOOQ. I want to display the results from the database lookup in the overview. When I just query select().from(CONFIGURATIONS) the Thymeleaf view works just fine. But when I want to join with the User table to add the name of the user to the results instead of just the id, I get an error.

This is the error in the webbrowser:

Mon Oct 10 16:04:17 CEST 2016 There was an unexpected error (type=Internal Server Error, status=500). Exception evaluating SpringEL expression: "config.name" (overview:37)

This is the error in Eclipse:

org.springframework.expression.spel.SpelEvaluationException: EL1008E:(pos 7): Property or field 'name' cannot be found on object of type 'org.jooq.impl.RecordImpl' - maybe not public?

I get that my view code is wrong, but I don't know what I should change "config.name" into to get the right property.field.

This is my controller:

@Controller
public class OverviewController
{
    public ArrayList configurationList = new ArrayList();

    @RequestMapping("/overview")
    public String showConfigurationList(Model model) 
    {
        model.addAttribute("configs", getConfigurations());
        return "overview";
    }

    public ArrayList getConfigurations()
    {
        try (Connection conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword)) 
        {
            DSLContext create = DSL.using(conn, SQLDialect.POSTGRES_9_5);
            Result<Record5<String, String, Timestamp, String, String>> result = create.select(CONFIGURATION.NAME, CONFIGURATION.VERSION, CONFIGURATION.DATE_MODIFIED, CONFIGURATION.AUTHOR_USER_ID, USER.NAME)
                    .from(USER).join(CONFIGURATION)
                    .on(CONFIGURATION.AUTHOR_USER_ID.equal(USER.ID)))
                    .fetch();
            /*Result<Record> result = create.select()
                    .from(CONFIGURATION)
                    .fetch();*/

            if(configurationList.isEmpty() == true)
            {
                for (Record5 r : result) {                  
                configurationList.add(r);
                }
            }               
        }             
        catch (Exception e) 
        {
            System.out.println("ERROR: " + e);          
        }
        return configurationList;
    }

And this is the Thymeleaf view:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">
<head>
  <title>Nazza Mediator - Overview</title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <link rel="stylesheet" href="webjars/bootstrap/3.3.7-1/css/bootstrap.min.css" />
    <script src="http://code.jquery.com/jquery.js" />
    <script src="webjars/bootstrap/3.3.7-1/js/bootstrap.min.js" />
</head>

<body>
<nav class="navbar navbar-default">
  <div class="container-fluid">
    <div class="navbar-header">
      <a class="navbar-brand" href="/">Nazza Mediator</a>
    </div>
    <ul class="nav navbar-nav">
      <li><a href="/">Home</a></li>
      <li class="active"><a href="/overview">Configuration Overview</a></li>
    </ul>
  </div>
</nav>

    <h3>Configuration Overview</h3>

    <table class="table table-hover">
      <tr>
        <th>NAME</th>
        <th>VERSION</th>
        <th>DATE MODIFIED</th>
        <th>AUTHOR</th>
      </tr>
      <tr th:each="config : ${configs}">
        <td th:text="${config.name}"></td>
        <td th:text="${config.version}"></td>
        <td th:text="${config.dateModified}"></td>
        <td th:text="${config.authorUserId}"></td>
      </tr>
    </table>
  </body>

</html>

Solution

I assume you have the jOOQ code generator up and running.

So, when you're using the first select

Result<Record> result = create.select()
                .from(CONFIGURATION)
                .fetch();

You'll actually end up with Result<ConfigurationRecord>and that one has an accessor getName which can be used by Thymeleaf.

If you write the join down and add the fields manually, you'll have a generic record, an implementation that only provides #get(String fieldName) and some others.

In your view you have to change the access to the values as follows

<td th:text="${config.get('NAME')}"></td>

You have to do this for every field.

But be careful, in your query you have two fields named "name", I guess you should alias one of them. For example, USER.NAME.as('userName') and use that in the call to get.

Let me know if that helps.

One more edit: Lukas Eder from jOOQ asked wether it is possible to use config['NAME']: This is not possible with the generic record, but only with the specific as well. Also, in all cases where you reference the field name, you gotta be careful, because it is case sensitive.