Querydsl generated sql query wrong sql type (nvarchar instead of varchar)

420 views Asked by At

Querydsl is generating a stored procedure with a nvarchar parameter for the column, but the table's column type is varchar. This way MS-SQLServer is not able to use its index, which remarkably increases query execution time.

The metadata which is used by Querydsl is generated using Querydsl's MetaDataSerializer (ant target).

How do I force Querydsl to use a varchar stored procedure parameter for the column?


DB table:
TableX with three columns: id (int), ColA (varchar) and ColB (nvarchar)

Generated metadata:

public class QTableX extends com.mysema.query.sql.RelationalPathBase<QTableX> {

    private static final long serialVersionUID = 1142355320;

    public static final QTableX TableX= new QTableX("TableX");

    public final StringPath id = createNumber("id", Integer.class);    

    public final StringPath colA= createString("ColA");

    public final StringPath colB= createString("ColB");

    public QTableX(String variable) {
        super(QTableX.class, forVariable(variable), "dbo", "TableX");
    }

    public QTableX(Path<? extends QTableX> path) {
        super((Class)path.getType(), path.getMetadata(), "dbo", "TableX");
    }

    public QTableX(PathMetadata<?> metadata) {
        super(QTableX.class, metadata, "dbo", "TableX");
    }
}

Querydsl:

SQLQuery query = new SQLQueryImpl(new SQLServerTemplates());
    query.from(QTableX.TableX).where(QTableX.TableX.colA.eq("FieldName")
        .and(QTableX.TableX.colB.like(".1.")));

generated sql:

declare @p1 int
set @p1=NULL

-- @P0 should be varchar (not nvarchar)
exec dbo.sp_prepare @p1 output,N'@P0 nvarchar(4000),@P1 nvarchar(4000)',N'select TableX.id
    from TableX TableX
    where TableX.ColA =  @P0  and TableX.ColB like  @P1  escape ''\''',1

exec dbo.sp_execute @p1, N'FieldName', N'.1.'
0

There are 0 answers