@Entity
@Getter
@Setter
public class User {
  @Id
  @GeneratedValue
  private Integer id;
  private String firstname;
  private String lastname;
  @Column(nullable = false, unique = true) private String emailAddress;
}

@Entity
@Sql
@Getter
@Setter
public class UserInfo {
  private String firstName;
  private String lastName;
  private String emailAddress;
}

@Getter
@Setter
public class UserDTO {
  private String firstName;
  private String lastName;
  private String emailAddress;
}

public interface UserRepository extends EbeanRepository<User, Long> {
    @Query("where emailAddress = :emailAddress order by id desc")
    User findUserByEmailAddressEqualsOql(@Param("emailAddress") String emailAddress);

    @Query("select (firstname,lastname,address) fetch manager (lastname) where lastname = :lastname order by id desc")
    List<User> findByLastnameOql(@Param("lastname") String lastname);

    @Query(nativeQuery = true, value = "select * from user where email_address = :emailAddress order by id desc")
    User findUserByEmailAddressEquals(@Param("emailAddress") String emailAddress);

    @Query(nativeQuery = true, value = "select * from user where lastname = :lastname order by id desc")
    List<User> findUsersByLastnameEquals(@Param("lastname") String lastname);

    @Query(nativeQuery = true, value = "update user set email_address = :newEmail where email_address = :oldEmail")
    @Modifying
    int changeUserEmailAddress(@Param("oldEmail") String oldEmail, @Param("newEmail") String newEmail);

    @Query("delete from user where emailAddress = :emailAddress")
    @Modifying
    int deleteUserByEmailAddressOql(@Param("emailAddress") String emailAddress);

    @Query(nativeQuery = true, value = "delete from user where email_address = :emailAddress")
    @Modifying
    int deleteUserByEmailAddress(@Param("emailAddress") String emailAddress);

    @Query(name = "withManagerById")
    List<User> findByLastnameNamedOql(@Param("lastname") String lastname);
    
    List<User> findAllByEmailAddressAndLastname(@Param("emailAddress") String emailAddress, @Param("lastname") String lastname);
}

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ebean xmlns="http://ebean-orm.github.io/xml/ns/ebean">
    <entity class="org.springframework.data.ebean.sample.domain.User">
        <named-query name="withManagerById">
            <query>
                select (firstname,lastname,address)
                fetch manager (lastname)
                where lastname = :lastname order by id desc
            </query>
        </named-query>
        <raw-sql name="byEmailAddressEquals"> ```
repository.deleteUserByEmailAddressOql("yuanxuegui@163.com");
assertEquals(1, result12);

// test find one sql query
User result13 = repository.findUserByEmailAddressEquals("yuanxuegui@163.com");
assertNull(result13);
}

@Test
public void testFindByMethodName() {
    List<User> result1 = repository.findAllByEmailAddressAndFullNameLastName("yuanxuegui@163.com", "Yuan");
    assertEquals(1, result1.size());
    assertEquals("Yuan", result1.get(0).getFullName().getLastName());
    assertThat(result1, hasItem(user));
}

@Test
public void testFindByExample() {
    User u = new User();
    u.setEmailAddress("YUANXUEGUI");
    List<User> result1 = repository.findAll(Example.of(u, ExampleMatcher.matchingAll()
            .withIgnoreCase(true)
            .withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING)));
    assertEquals(1, result1.size());
    assertEquals("Yuan", result1.get(0).getFullName().getLastName());
    assertThat(result1, hasItem(user));

    List<User> result2 = repository.findAll(Example.of(u, ExampleMatcher.matchingAll()
            .withIgnoreCase(false)
            .withStringMatcher(ExampleMatcher.StringMatcher.EXACT)));
    assertEquals(0, result2.size());
}

@Test
public void testAuditable() {
    User u = repository.findUserByEmailAddressEqualsOql("yuanxuegui@163.com");
    assertEquals("test", u.getCreatedBy());
    assertEquals("test", u.getLastModifiedBy());
}

@Test
public void testDomainEvent() {
    user.changeEmail("yuanxuegui@126.com");
    repository.save(user);
    User u = repository.findOneByProperty("emailAddress", "yuanxuegui@126.com");
    assertNotNull(u);
    assertEquals("yuanxuegui@126.com", u.getEmailAddress());
}

@Data
@IncludeFields("emailAddress,fullName(lastName,firstName),age")
public class UserQuery {
    @ExprParam(expr = ExprType.CONTAINS)
    private String emailAddress;

    @ExprParam(name = "age", expr = ExprType.GE)
    private int ageStart;

    @ExprParam(name = "age", expr = ExprType.LE)
    private int ageEnd;
}

package org.springframework.data.ebean.querychannel;

import io.ebean.Query;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.HashMap;
import java.util.Map;

import static org.junit.Assert.assertEquals;

/**
 * @author Xuegui Yuan
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = SampleConfig.class)
public class EbeanQueryChannelServiceIntegrationTests {
    // Test fixture
    User user;
    @Autowired
    private EbeanQueryChannelService ebeanQueryChannelService;
    @Autowired
    private UserRepository repository;

    @Before
    public void initUser() {
        repository.deleteAll();
        user = new User("QueryChannel", "Test", "testquerychannel@163.com");
        user.setAge(29);
        user = repository.save(user);
    }


    @Test
    public void createSqlQueryMappingColumns() {
        String sql1 = "select first_name, last_name, email_address from user where last_name= :lastName";
        String sql2 = "select first_name as firstName, last_name as lastName, email_address as emailAddress from user where last_name= :lastName";
        Map<String, String> columnsMapping = new HashMap<>();
        columnsMapping.put("first_name", "firstName");
        columnsMapping.put("last_name", "lastName");

        Query<UserInfo> query1 =