MyBatis Generator setDistinct(true)

Question

I'm trying to use setDistinct(true) as it is described in the guide: http://mybatis.github.io/generator/generatedobjects/exampleClassUsage.html

I've written in this way:

testExample ae = new testExample();    
testExample.Criteria criteriatest = ae.createCriteria(); 
ae.setDistinct(true); 
criteriatest.andIDENTLAVEqualTo(Long.parseLong(cert.getCODINDIVID())); 
ae.or(criteriatest); 
List<test> listtest = testMapper.selectByExample(ae);

but the setDistinct(true) doesn't affect the results.

Where should I add the setDistinct line?

Answer 1

It looks like the link you referenced is for an extremely old version of MyBatis. On that page, it lists the following:

Version: 1.3.3-SNAPSHOT

The latest version is:

mybatis-3.3.0-SNAPSHOT

Grepping the 3.x code for setDistinct does not return anything: https://github.com/mybatis/mybatis-3/search?q=setDistinct

I'm surprised you don't get a compile-time error about the method not being found. Are you using version 1.3.3 (or 1.x)?

I would recommend doing the DISTINCT right in the query. Since MyBatis is generally a sort of a close-to-the-SQL-metal type of mapping framework, I think it's best to add it in the mapper file's query itself. Plus that way, you can choose specifically what to DISTINCT by. The setDistinct method does not seem to provide any way to specify the target.

For MyBatis 3, I think the analogous style of query would be this:

http://mybatis.github.io/mybatis-3/statement-builders.html

This seems to be analogous to a jOOQ-style DSL. It has a SELECT_DISTINCT method. I personally find it easier to code/read the pure SQL with some XML markup as needed for dynamic SQL in a mapper file, but this is certainly a viable option in MyBatis 3.

Edit:

So, I did some more digging, and the reason I couldn't find the code in the MyBatis3 git repo is because setDistinct is in the mybatis-generator code base.

I think part of the issue here may stem from what is part of Mybatis-Generator's description on GitHub:

MBG seeks to make a major impact on the large percentage of database operations that are simple CRUD (Create, Retrieve, Update, Delete).

So, it provides a way to do simple DISTINCTs, but with limited control.

The code resides in the addClassElements method of the ProviderSelectByExampleWithoutBLOBsMethodGenerator class. Searching for setDistinct won't show up on a Github search since it's an automatically generated setter.

This is the relevant code snippet:

boolean distinctCheck = true;
        for (IntrospectedColumn introspectedColumn : getColumns()) {
            if (distinctCheck) {
                method.addBodyLine("if (example != null && example.isDistinct()) {"); //$NON-NLS-1$
                method.addBodyLine(String.format("%sSELECT_DISTINCT(\"%s\");", //$NON-NLS-1$
                 builderPrefix,
                    escapeStringForJava(getSelectListPhrase(introspectedColumn))));
                method.addBodyLine("} else {"); //$NON-NLS-1$
                method.addBodyLine(String.format("%sSELECT(\"%s\");", //$NON-NLS-1$
                 builderPrefix,
                    escapeStringForJava(getSelectListPhrase(introspectedColumn))));
                method.addBodyLine("}"); //$NON-NLS-1$
            } else {
                method.addBodyLine(String.format("%sSELECT(\"%s\");", //$NON-NLS-1$
                 builderPrefix,
                    escapeStringForJava(getSelectListPhrase(introspectedColumn))));
            }

            distinctCheck = false;
        }

So, essentially, this looks like it's wrapping the SELECT_DISTINCT method I mentioned originally, and it attempts to introspect the columns and apply the DISTINCT to all of the ones it gets back.

Digging a bit deeper, it ultimately calls this code to get the columns:

/**
* Returns all columns in the table (for use by the select by primary key
* and select by example with BLOBs methods)
*
* @return a List of ColumnDefinition objects for all columns in the table
*/
    public List<IntrospectedColumn> getAllColumns() {
        List<IntrospectedColumn> answer = new ArrayList<IntrospectedColumn>();
        answer.addAll(primaryKeyColumns);
        answer.addAll(baseColumns);
        answer.addAll(blobColumns);

        return answer;
    }

So, this is definitely essentially an all-or-nothing DISTINCT (whereas Postgres itself allows DISTINCT on just certain columns).

Try moving the setDistinct to the very last line before you actually invoke the ae object. Perhaps subsequent calls are affecting the column set (although from the code, it doesn't seem like it should -- basically once the columns are set, the setDistinct should use them).

The other thing that would be interesting would be to see what SQL it is actually generating with and without setDistinct.

Check this link out for more detail on debug/logging:

http://mybatis.github.io/generator/reference/logging.html

I'd recommend perhaps trying out the XML-based mapper file definitions which interleave SQL with XML tags for dynamic-ness. IMO, it's much easier to follow than the code Mybatis Generator code snippet above. I suppose that's one of the main tradeoffs with a generator -- easier to create initially, but more difficult to read/maintain later.

For super-dynamic queries, I could see some more advantages, but then that sort of goes against their self-description of it being for simple CRUD operations.