how to make hibernate prefix N in SELECT queries?

Question

I'm using MSSQL, Hibernate, wildfly and my query is:

em.createQuery("select a from GeoDivType a where a.description = 'شسasфы' " , GeoDivType.class);

which produces the following:

Hibernate: select geodivtype0_.Id as Id1_69_, geodivtype0_.Description as Descript2_69_, geodivtype0_.Name as Name3_69_, geodivtype0_.parent as parent5_69_, geodivtype0_.version as version4_69_ from GeoDivType geodivtype0_ where geodivtype0_.Description='شسasва'

as you can see at the end there's Description='شسasва' without N prefix and if you execute the exact code in sql management studio you get nothing, but with Description=N'شسasва' I get my desired result.

I've tried adding these to my Persistence.xml

<property name="hibernate.connection.characterEncoding" value="utf8"/>
<property name="hibernate.connection.useUnicode" value="true"/>
<property name="hibernate.connection.charSet" value="UTF-8"/>

and this to my url:

useUnicode=yes;characterEncoding=UTF-8;

still nothing.

my fields are nvarchar(255) and there's no question mark for the unicoded characters, my problem is I can't SELECT them.

edit:

mapping of GeoDivType:

import java.io.Serializable;
import javax.persistence.*;

@Entity
@Table(name="GeoDivType")
public class GeoDivType implements Serializable {
    public GeoDivType() {
    }

    @Column(name="Id", nullable=false)  
    @Id 
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne( fetch=FetchType.LAZY)   
    @JoinColumns({ @JoinColumn(name="parent", referencedColumnName="Id") }) 
    private serp3.entity.actor.GeoDivType parent;

    @Column(name="Name", nullable=true, length=255) 
    private String name;

    @Column(name="Description", nullable=true, length=255, columnDefinition = "nvarchar(255)")
    private String description;

    @OneToMany(mappedBy="parent")   
        private java.util.List<serp3.entity.actor.GeoDivType> childs = new java.util.ArrayList<serp3.entity.actor.GeoDivType>();

    @ManyToMany()   
    @JoinTable(name="GeoDivType_GeoDivUsage", joinColumns={ @JoinColumn(name="GeoDivTypeId") }, inverseJoinColumns={ @JoinColumn(name="geoDivUsages") })    
        private java.util.List<serp3.entity.actor.GeoDivUsage> geoDivUsages = new java.util.ArrayList<serp3.entity.actor.GeoDivUsage>();

    private void setId(long value) {
        setId(new Long(value));
    }

    private void setId(Long value) {
        this.id = value;
    }

    public Long getId() {
        return id;
    }

    @Version
    @Column(name = "version")
    private Long version;

    public void setVersion(Long version) {
        this.version = version;
    }

    public Long getVersion() {
        return version;
    }

    @Override
    public int hashCode() {
        int hash = 7;
        hash = 67 * hash + java.util.Objects.hashCode(this.id);
        return hash;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj) {
            return true;
        }
        if (obj == null) {
            return false;
        }
        if (!(obj instanceof GeoDivType)) {
            return false;
        }
        final GeoDivType other = (GeoDivType) obj;
        if (!java.util.Objects.equals(this.getId(), other.getId())) {
            return false;
        }
        return true;
    }

    public void setName(String value) {
        this.name = value;
    }

    public String getName() {
        return name;
    }

    public void setDescription(String value) {
        this.description = value;
    }

    public String getDescription() {
        return description;
    }

    public void setChilds(java.util.List<serp3.entity.actor.GeoDivType> value) {
        this.childs = value;
    }

    public java.util.List<serp3.entity.actor.GeoDivType> getChilds() {
        return childs;
    }


    public void setParent(serp3.entity.actor.GeoDivType value) {
        this.parent = value;
    }

    public serp3.entity.actor.GeoDivType getParent() {
        return parent;
    }

    public void setGeoDivUsages(java.util.List<serp3.entity.actor.GeoDivUsage> value) {
        this.geoDivUsages = value;
    }

    public java.util.List<serp3.entity.actor.GeoDivUsage> getGeoDivUsages() {
        return geoDivUsages;
    }


    public String toString() {
        return String.valueOf(getId());
    }

}

my dialect:

import org.hibernate.dialect.SQLServer2012Dialect;
import org.hibernate.type.StandardBasicTypes;

import java.sql.Types;

public class MSSQL2012 extends SQLServer2012Dialect{
    public MSSQL2012() {

        registerColumnType(Types.CHAR, "nchar(1)");
        registerColumnType(Types.LONGVARCHAR, "nvarchar(max)" );
        registerColumnType(Types.VARCHAR, 4000, "nvarchar($l)");
        registerColumnType(Types.VARCHAR, "nvarchar(max)");
        registerColumnType(Types.CLOB, "nvarchar(max)" );
        registerColumnType(Types.NCHAR, "nchar(1)");
        registerColumnType(Types.LONGNVARCHAR, "nvarchar(max)");
        registerColumnType(Types.NVARCHAR, 4000, "nvarchar($l)");
        registerColumnType(Types.NVARCHAR, "nvarchar(max)");
        registerColumnType(Types.NCLOB, "nvarchar(max)");
        registerHibernateType(Types.NCHAR, StandardBasicTypes.CHARACTER.getName());
        registerHibernateType(Types.LONGNVARCHAR, StandardBasicTypes.TEXT.getName());
        registerHibernateType(Types.NVARCHAR, StandardBasicTypes.STRING.getName());
        registerHibernateType(Types.NCLOB, StandardBasicTypes.CLOB.getName() );
    }
}

in Persistence.xml

<property name="hibernate.dialect" value="serp3.util.dialect.MSSQL2012"/>

Answer 1

I found the answer. you have to use parameters:

javax.persistence.Query q3 = em.createQuery("select a from GeoDivType a where a.description = :param " , GeoDivType.class);
    q3.setParameter("param", "شسasва");

if you don't use parameters like:

javax.persistence.Query q3 = em.createQuery("select a from GeoDivType a where a.description = 'شسasва' " , GeoDivType.class);

it won't work.

Answer 2

Try adding columnDefinition = "nvarchar" to your description.

Or extend the SQLServerDialect class:

public class MySqlServerDialectNvarchar extends SQLServerDialect {

    public MySqlServerDialectNvarchar() {
         super();
         registerHibernateType(Types.NVARCHAR, 255, "string");
    }
}

and make a reference to your class in persistence.xml file:

 <property name="hibernate.dialect" value="..MySqlServerDialectNvarchar" />