Sample Query Using NHibernate

The most common applications for example queries are composite queries, and we often need to provide several query options on the interface, and then return the results that match the criteria based on the user's input.

It is often necessary to deal with complex conditions when dealing with code directly, which makes the structure of logical judgment Statement complex because of uncertain combination conditions. For multiple optional parameters, the situation becomes more severe.

You can easily handle this problem by using a sample query.

At the time of the query, the collected query conditions are assigned to an object's properties, of course, the object's type is the entity object that needs to be queried.

For example, there is a user type in NHibernate, and we need to make a combination of its name and password, as defined by the user:

Namespace Demo.Dao.Domain
{
    User objects
    public class User
    {
        public virtual int UserId {set; get;}
        Public virtual string Username {set; get;}
        Public virtual string Password {set; get;}

        Public virtual address address {set; get;}
    }
}

In the interface, we provide input boxes for user input.

<p>
    <label for= "<%= this.tbxname%>" >
        User name:</label>
    <asp:textbox id= "Tbxname" runat= "Server" ></asp:TextBox>
</p>
<p>
    <label for= "<%= this.tbxpassword%>" >
        Password:</label>
    <asp:textbox id= "Tbxpassword" runat= "Server" ></asp:TextBox>
</p>
<p>
    <asp:button id= "btnsearch" runat= "server" text= "query" onclick= "btnSearch_Click"/>
</p>

In the Click event of a button, we combine the user's input into a sample object. Then submit the query to NHibernate.

protected void btnSearch_Click (object sender, EventArgs e)
{
    if (this. IsValid)
    {
        Demo.Dao.Domain.User Example
            = new Demo.Dao.Domain.User ()
                {
                    Username = This.tbxName.Text,
                    Password = This.tbxPassword.Text
                };
        ilist<demo.dao.domain.user> list = this. Userservice.getusers (example);

        This. Repeater1.datasource = list;
        This. Repeater1.databind ();

    }
}

In a service in the background, we use a sample query for processing.

The sample query type definition is defined in the namespace nhibernate.criterion, and the static method create creates a sample object.

First create a query condition object, and then use the Create method of Example to make a query sample object. The last is the actual query.

Public ilist<demo.dao.domain.user> getusers (Demo.Dao.Domain.User exampleuser)
{
    Create query criteria
    Global::nhibernate.icriteria criteria
        = this. Session.createcriteria<demo.dao.domain.user> ();
            
    Create Query Example
    Global::nhibernate.criterion.example Example
        = Global::nhibernate.criterion.example.create (Exampleuser);

    Set query criteria for using sample objects
    Criteria. ADD (example);

    Example. Enablelike (Global::nhibernate.criterion.matchmode.anywhere);

    ilist< demo.dao.domain.user> list = criteria. List<demo.dao.domain.user> ();

    return list;
}

For strings of type conditions, exact matches are used by default, but can be set by the enablelike of the example. NHibernate.Criterion.MatchMode is an enumeration that supports the following matching patterns.

The default string is an exact match and can be set by enablelike
Anywhere means any location match
End appears at the last position
Start appears in start position
Exact exact match, default setting
Example. Enablelike (Global::nhibernate.criterion.matchmode.anywhere);

When you enter a in the name input box, the SQL statement generated by NHibernate is as follows:

WHERE (This_. Username like @p0)

In addition to the powerful enablelike, several setup parameters are provided, which are more commonly used excludezeroes() method, which excludes properties that have a value of 0 in the property. For example, adding an age integer type attribute to the User also causes the query statement to become the following SQL

WHERE (This_. Username like @p0 and This_. Age = @p1)

Use the settings for exclude 0

Exclude a property with a value of 0
Example. Excludezeroes ();

Now the SQL becomes

WHERE (This_. Username like @p0)

However, there are two more settings to be careful of. The Excludenulls () method, which appears to exclude a property with a value of NULL, will invalidate the previous excludezeroes () by calling the ToString () method and then looking at the length of the string.

WHERE (This_. Username like @p0 and This_. Age = @p1)

The Excludenone () method, which indicates that all properties of the sample object need to be used, also invalidates excludezeroes ().

WHERE (This_. Username like @p0 and This_. Password is null and This_. Age = @p1)

Finally, there is a IgnoreCase () method that compares strings using a case-insensitive method, which is valid for Oracle and is not available for SQL Server.

WHERE (Lower (this_. Username) like Lower (@p0) and This_. Age = @p1)

Sample Query Using NHibernate