Search code examples
javapojojooq

How can I fetch database rows in Pojo without using JOOQ code generation?

I'm trying to use JOOQ without code generation. I have a dao class that looks like this

public class FilesDao { 
public List<FilePojo> getAllFiles() {
    DataSource dataSource = DataSourceFactory.getTestiDataSource();
    List<FilePojo> filePojos = new ArrayList<>();
    try (Connection con = dataSource.getConnection()) {
        DSLContext create = DSL.using(con, SQLDialect.MARIADB);
        filePojos = create.select(field("tiedosto.id"), field("tiedosto.nimi"), field("tiedosto.koko_tavua"),
                field("tiedosto.sisalto"), field("tiedosto.hlo_id"))
                .from(table("tiedosto"))
                .where(field("minioupload").eq((byte) 0))
                .fetch().into(FilePojo.class);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return filePojos;
   }
}

and a Pojo class that looks like this

import javax.persistence.Column;
import javax.persistence.Table;

@Table(name="tiedosto")
public class FilePojo {

@Column(name = "id")
private Integer id;

@Column(name = "hlo_id")
private Integer customerId;

@Column(name = "koko_tavua")
private Integer fileSize;

@Column(name = "nimi")
private String fileName;

@Column(name = "sisalto")
private byte[] content;}
//Getters setters omitted

When I try to read from the table using a main method like this

public class App {
public static void main(String[] args) {
    FilesDao mydao = new FilesDao();
    List<FilePojo> myList = mydao.getAllFiles();
    for (FilePojo filePojo : myList) {
        System.out.println("==========================================" + "\n" +
                filePojo.getId() + " " +
                filePojo.getCustomerId() + " " +
                filePojo.getFileName() + " " +
                filePojo.getFileSize() + " " +
                filePojo.getContent() + " " +
                "==========================================");
    }
  }
}

The output is as follows enter image description here

I can see that the SQL query is running fine and listing all the matched rows, but pojo is returning null values. What am I doing wrong here? Can someone please point me to the right direction? I'd really appreciate any sort of help.

Solution

  • I'm undecided whether this is a bug or a feature. You're using the plain SQL templating API when you should probably be using the identifier building API. When you write

    field("tiedosto.id")

    Then, jOOQ (possibly erroneously) thinks that your column is named `tiedosto.id`, with a period in the name. When it should really be qualified as `tiedosto`.`id`. There are a few possible fixes:

    Keep using the plain SQL templating API

    But then, don't qualify the name:

    field("id")

    Use the identifier building API

    field(name("tiedosto", "id"))

    Use the code generator

    This should always be your preferred option, of course.