Examples of com.avaje.ebean.RawSql

com.avaje.ebean.RawSql

4;Sql indicates to that this bean // is based on RawSql rather than a table @Entity @Sql public class OrderAggregate { @OneToOne Order order; Double totalAmount; Double totalItems; // getters and setters ...

Example 1:

 String sql = " select order_id, o.status, c.id, c.name, sum(d.order_qty*d.unit_price) as totalAmount" + " from o_order o" + " join o_customer c on c.id = o.kcustomer_id " + " join o_order_detail d on d.order_id = o.id " + " group by order_id, o.status "; RawSql rawSql = RawSqlBuilder.parse(sql) // map the sql result columns to bean properties .columnMapping("order_id", "order.id").columnMapping("o.status", "order.status") .columnMapping("c.id", "order.customer.id") .columnMapping("c.name", "order.customer.name") // we don't need to map this one due to the sql column alias // .columnMapping("sum(d.order_qty*d.unit_price)", "totalAmount") .create(); Query<OrderAggregate> query = Ebean.find(OrderAggregate.class); query.setRawSql(rawSql).where().gt("order.id", 0).having().gt("totalAmount", 20); List<OrderAggregate> list = query.findList();

Example 2:

The following example uses a FetchConfig().query() so that after the initial RawSql query is executed Ebean executes a secondary query to fetch the associated order status, orderDate along with the customer name.

 String sql = " select order_id, 'ignoreMe', sum(d.order_qty*d.unit_price) as totalAmount " + " from o_order_detail d" + " group by order_id "; RawSql rawSql = RawSqlBuilder.parse(sql).columnMapping("order_id", "order.id") .columnMappingIgnore("'ignoreMe'").create(); Query<OrderAggregate> query = Ebean.find(OrderAggregate.class); query.setRawSql(rawSql).fetch("order", "status,orderDate", new FetchConfig().query()) .fetch("order.customer", "name").where() .gt("order.id", 0).having().gt("totalAmount", 20).order().desc("totalAmount").setMaxRows(10);

Note that lazy loading also works with object graphs built with RawSql.


  private SqlLimitResponse buildSql(String selectClause, OrmQueryRequest<?> request, CQueryPredicates predicates, SqlTree select) {


    SpiQuery<?> query = request.getQuery();


    RawSql rawSql = query.getRawSql();
    if (rawSql != null) {
      return rawSqlHandler.buildSql(request, predicates, rawSql.getSql());
    }


    BeanPropertyAssocMany<?> manyProp = select.getManyProperty();


    boolean useSqlLimiter = false;

View Full Code Here

        Dnode cm = columnMappings.get(j);
        String column = cm.getAttribute("column");
        String property = cm.getAttribute("property");
        rawSqlBuilder.columnMapping(column, property);
      }
      RawSql rawSql = rawSqlBuilder.create();


      DeployNamedQuery namedQuery = new DeployNamedQuery(name, rawSql);
      deployDesc.add(namedQuery);
    }
  }

View Full Code Here

      pstmt = txn.getConnection().prepareStatement("select id, name, billing_address_id from o_customer");
      
      // ResultSet will be closed by Ebean
      ResultSet resultSet = pstmt.executeQuery();
      
      RawSql rawSql = new RawSql(resultSet, "id", "name", "billingAddress.id");
      
      List<Customer> list = Ebean.find(Customer.class)
        .setRawSql(rawSql)
        // also test a secondary query join 
        .fetch("billingAddress", new FetchConfig().query())

View Full Code Here

  @Test
  public void test() {


    ResetBasicData.reset();


    RawSql rawSql = RawSqlBuilder
        .parse("select r.id, r.name from o_customer r where r.id >= ? and r.name like ?")
        .columnMapping("r.id", "id").columnMapping("r.name", "name").create();


    Query<Customer> query = Ebean.find(Customer.class);
    query.setRawSql(rawSql);

View Full Code Here

  @Test
  public void test_unparsed() {


    ResetBasicData.reset();


    RawSql rawSql = RawSqlBuilder
        .unparsed("select r.id, r.name from o_customer r where r.id >= ? and r.name like ?")
        .columnMapping("r.id", "id")
        .columnMapping("r.name", "name").create();


    Query<Customer> query = Ebean.find(Customer.class);

View Full Code Here

        + " from o_order o"
        + " join o_customer c on c.id = o.kcustomer_id "
        + " join o_order_detail d on d.order_id = o.id "
        + " group by order_id, o.status, c.id, c.name ";


    RawSql rawSql = RawSqlBuilder.parse(sql).columnMapping("order_id", "order.id")
        .columnMapping("o.status", "order.status").columnMapping("c.id", "order.customer.id")
        .columnMapping("c.name", "order.customer.name")
        // .columnMapping("sum(d.order_qty*d.unit_price)", "totalAmount")
        .create();

View Full Code Here

    
      ResetBasicData.reset();
      
      
      // try valid query where spaces in the formula ...
      RawSql rawSql = 
          RawSqlBuilder
              .parse("select r.id, r.name || 'hello' as name from o_customer r ")
              .create();
                  
      Query<Customer> query = Ebean.find(Customer.class);

View Full Code Here

  @Test
  public void test() {


    ResetBasicData.reset();


    RawSql rawSql = RawSqlBuilder
        .parse(
            "select c.customer_id, count(*) as totalContacts from contact c  group by c.customer_id")
        .columnMapping("c.customer_id", "customer.id").create();


    Query<CustomerAggregate> query = Ebean.find(CustomerAggregate.class);

View Full Code Here

        + " from o_order o"
        + " join o_customer c on c.id = o.kcustomer_id "
        + " join o_order_detail d on d.order_id = o.id "
        + " group by order_id, o.status, c.id, c.name ";


    RawSql rawSql = RawSqlBuilder.parse(sql).columnMapping("order_id", "order.id")
        .columnMapping("o.status", "order.status").columnMapping("c.id", "order.customer.id")
        .columnMapping("c.name", "order.customer.name")
        // .columnMapping("sum(d.order_qty*d.unit_price)", "totalAmount")
        .create();

View Full Code Here

  @Test
  public void test() {


    ResetBasicData.reset();


    RawSql rawSql0 = RawSqlBuilder.parse(" select  distinct  r.id, r.name from o_customer r ")
        .create();


    Query<Customer> query0 = Ebean.find(Customer.class);
    query0.setRawSql(rawSql0);
    query0.where().ilike("name", "r%");


    RawSql rawSql = RawSqlBuilder.parse(" select  distinct  r.id, r.name from o_customer r ")
        .columnMapping("r.id", "id").columnMapping("r.name", "name").create();


    Query<Customer> query = Ebean.find(Customer.class);
    query.setRawSql(rawSql);
    query.where().ilike("name", "r%");

View Full Code Here

0 1

TOP

Related Classes of com.avaje.ebean.RawSql

com.avaje.ebeaninternal.server.deploy.BeanDescriptorManager

com.avaje.ebeaninternal.server.query.CQueryBuilder

com.avaje.ebeaninternal.server.rawsql.TestRawSqlParsing

com.avaje.tests.inheritance.TestInheritanceRawSql

com.avaje.tests.rawsql.TestRawSqlAsKeyword

com.avaje.tests.rawsql.TestRawSqlCustomerAggregate

com.avaje.tests.rawsql.TestRawSqlNamedParams

com.avaje.tests.rawsql.TestRawSqlOrmQuery

com.avaje.tests.rawsql.TestRawSqlOrmQueryDistinct

com.avaje.tests.rawsql.TestRawSqlOrmWrapper

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.