Examples of jodd.db.oom.sqlgen.DbSqlBuilder

jodd.db.oom.sqlgen.DbSqlBuilder
Nice SQL query generator that provides some automatic query generation.
Query is built by appending 'chunks' - parts of the query. These chunks may be very simple, as a simple string. However, the main reason of existence of this class are chunks that auto-generate part of the query based on provided domain object. They can be used to easily create some most common queries in an efficient way.
Although it is not necessary, use of {@link jodd.db.oom.meta.DbId} annotation boost the functionalityof this query builder.
Some chunks deals with relations between tables, so they must be aware of foreign key names. Here the naming convention is used, and even users might have their own foreign key naming convention.
Furthermore, if all queries are generated using just sql builder, it is possible to use dialects for various database types.


  /**
   * Finds all countries.
   */
  protected List<Country> findAllCountriesSortedByName() {
    DbSqlBuilder dbsql = sql("select $C{c.*} from $T{Country c} order by $c.name");
    return query(dbsql).list(Country.class);
  }

View Full Code Here

    }


    int from = (page - 1) * pageSize;


    String pageSql = buildPageSql(sql, from, pageSize);
    DbSqlBuilder dbsql = sql(pageSql);


    DbOomQuery query = query(dbsql);
    query.setMaxRows(pageSize);
    query.setFetchSize(pageSize);
    query.setMap(params);

View Full Code Here

   * generate selecting foreign entity for given parent entity.
   * <p>
   * However, it is highly recommended to use template sql instead!!!
   */
  public void loadAnswers(Question q) {
    DbSqlBuilder dbb = DbEntitySql.findForeign(Answer.class, q);
    dbb._(" order by ").refId("Answer_");
    List<Answer> answers = query(dbb).autoClose().list(Answer.class);
    q.setAnswers(answers);
  }

View Full Code Here

    }
  }


  @Test
  public void testAliasNoAlias() {
    DbSqlBuilder st;


    st = sql("$T{Boy} $Boy.id $C{Boy.id}");
    assertEquals("BOY BOY.ID BOY.ID", st.generateQuery());


    st = sql("$T{Boy b} $b.id $C{b.id}");
    assertEquals("BOY b b.ID b.ID", st.generateQuery());
  }

View Full Code Here

  }




  @Test
  public void testTables() {
    DbSqlBuilder st;


    // 1
    st = sql("$T{Boy}");
    assertEquals("BOY", st.generateQuery());


    st = sql("$T{BadBoy}");
    assertEquals("BOY", st.generateQuery());


    st = sql("$T{Boy b}");
    assertEquals("BOY b", st.generateQuery());


    st = sql("$T{BadBoy b}");
    assertEquals("BOY b", st.generateQuery());


    // 2
    st = sql("$T{b}").use("b", new Boy());
    assertEquals("BOY", st.generateQuery());
    st = sql("$T{b b}").use("b", new Boy());
    assertEquals("BOY b", st.generateQuery());


    st = sql("$T{b}").use("b", new BadBoy());
    assertEquals("BOY", st.generateQuery());


    // 3
    st = sql("$T{b}").use("b", Boy.class);
    assertEquals("BOY", st.generateQuery());


    st = sql("$T{b}").use("b", BadBoy.class);
    assertEquals("BOY", st.generateQuery());
  }

View Full Code Here

  }




  @Test
  public void testManyTables() {
    DbSqlBuilder st = sql("$T{Boy, Girl girl}");
    assertEquals("BOY, GIRL girl", st.generateQuery());
  }

View Full Code Here

    assertEquals("BOY, GIRL girl", st.generateQuery());
  }


  @Test
  public void testColumns1() {
    DbSqlBuilder st;


    // 1
    st = sql("$T{Boy} | $C{Boy.id} | $C{Boy.*}");
    assertEquals("BOY | BOY.ID | BOY.GIRL_ID, BOY.ID, BOY.NAME", st.generateQuery());


    st = sql("$T{BadBoy} | $C{BadBoy.ajdi} | $C{BadBoy.*} | $C{BadBoy.+}");
    assertEquals("BOY | BOY.ID | BOY.ID, BOY.GIRL_ID, BOY.NAME | BOY.ID", st.generateQuery());


    // 2
    st = sql("$T{b b} | $C{b.id}").use("b", new Boy());
    assertEquals("BOY b | b.ID", st.generateQuery());


    st = sql("$T{b bb} | $C{bb.id} | $C{bb.*}").use("b", new Boy());
    assertEquals("BOY bb | bb.ID | bb.GIRL_ID, bb.ID, bb.NAME", st.generateQuery());


    st = sql("$T{b bb} | $C{bb.ajdi} | $C{bb.*} | $C{bb.+}").use("b", new BadBoy());
    assertEquals("BOY bb | bb.ID | bb.ID, bb.GIRL_ID, bb.NAME | bb.ID", st.generateQuery());


    // 3
    st = sql("$T{b b} | $C{b.id}").use("b", Boy.class);
    assertEquals("BOY b | b.ID", st.generateQuery());


    st = sql("$T{b bb} | $C{bb.id} | $C{bb.*}").use("b", Boy.class);
    assertEquals("BOY bb | bb.ID | bb.GIRL_ID, bb.ID, bb.NAME", st.generateQuery());


    st = sql("$T{b bb} | $C{bb.ajdi} | $C{bb.*}").use("b", BadBoy.class);
    assertEquals("BOY bb | bb.ID | bb.ID, bb.GIRL_ID, bb.NAME", st.generateQuery());


    st = sql("$T{b bb} | $C{bb.ajdi} | $C{bb.*} | $C{bb.+}").use("b", BadBoy.class);
    assertEquals("BOY bb | bb.ID | bb.ID, bb.GIRL_ID, bb.NAME | bb.ID", st.generateQuery());


  }

View Full Code Here

  }




  @Test
  public void testColumns2() {
    DbSqlBuilder st;


    // 1
    st = sql("$T{Boy} | $C{Boy.id} | $C{Boy.*}");
    assertEquals("BOY | BOY.ID as BOY$ID | BOY.GIRL_ID as BOY$GIRL_ID, BOY.ID as BOY$ID, BOY.NAME as BOY$NAME",
        st.aliasColumnsAs(TABLE_NAME).generateQuery());
    assertEquals("BOY | BOY.ID as Boy$ID | BOY.GIRL_ID as Boy$GIRL_ID, BOY.ID as Boy$ID, BOY.NAME as Boy$NAME",
        st.reset().aliasColumnsAs(TABLE_REFERENCE).generateQuery());
    assertEquals("BOY | BOY.ID as col_0_ | BOY.GIRL_ID as col_1_, BOY.ID as col_2_, BOY.NAME as col_3_",
        st.reset().aliasColumnsAs(COLUMN_CODE).generateQuery());


    st = sql("$T{BadBoy BadBoy} | $C{BadBoy.ajdi} | $C{BadBoy.*}");
    assertEquals("BOY BadBoy | BadBoy.ID as BOY$ID | BadBoy.ID as BOY$ID, BadBoy.GIRL_ID as BOY$GIRL_ID, BadBoy.NAME as BOY$NAME",
        st.aliasColumnsAs(TABLE_NAME).generateQuery());
    assertEquals("BOY BadBoy | BadBoy.ID as BadBoy$ID | BadBoy.ID as BadBoy$ID, BadBoy.GIRL_ID as BadBoy$GIRL_ID, BadBoy.NAME as BadBoy$NAME",
        st.reset().aliasColumnsAs(TABLE_REFERENCE).generateQuery());
    assertEquals("BOY BadBoy | BadBoy.ID as col_0_ | BadBoy.ID as col_1_, BadBoy.GIRL_ID as col_2_, BadBoy.NAME as col_3_",
        st.reset().aliasColumnsAs(COLUMN_CODE).generateQuery());


  }

View Full Code Here

  }




  @Test
  public void testColumns3() {
    DbSqlBuilder st;


    st = sql("$C{b.id,b.name} from $T{b b}").use("b", Boy.class);
    assertEquals("b.ID, b.NAME from BOY b", st.generateQuery());


    st = sql("$C{    a.ajdi ,  a.nejm     } from $T{b a}").use("b", BadBoy.class);
    assertEquals("a.ID, a.NAME from BOY a", st.generateQuery());


    st = sql("$C{b.id,b.name} from $T{b b}").use("b", Boy.class);
    assertEquals("b.ID as BOY$ID, b.NAME as BOY$NAME from BOY b", st.aliasColumnsAs(TABLE_NAME).generateQuery());
    st = sql("$C{    a.ajdi ,  a.nejm     } from $T{b a}").use("b", BadBoy.class);
    assertEquals("a.ID as BOY$ID, a.NAME as BOY$NAME from BOY a", st.aliasColumnsAs(TABLE_NAME).generateQuery());




    st = sql("$b.id as d, $C{b.name} from $T{b b}").use("b", Boy.class).aliasColumnsAs(TABLE_NAME);
    assertEquals("b.ID as d, b.NAME as BOY$NAME from BOY b", st.generateQuery());
    st = sql("$a.ajdi as d, $C{    a.nejm     } from $T{b a}").use("b", BadBoy.class).aliasColumnsAs(TABLE_NAME);
    assertEquals("a.ID as d, a.NAME as BOY$NAME from BOY a", st.generateQuery());


    st = sql("$C{b.*} from $T{b b}").use("b", Boy.class).aliasColumnsAs(TABLE_NAME);
    assertEquals("b.GIRL_ID as BOY$GIRL_ID, b.ID as BOY$ID, b.NAME as BOY$NAME from BOY b", st.generateQuery());
    st = sql("$C{a.*} from $T{b a}").use("b", BadBoy.class);
    assertEquals("a.ID, a.GIRL_ID, a.NAME from BOY a", st.generateQuery());


    st = sql("$C{a.*} from $T{BadBoy a}");
    assertEquals("a.ID, a.GIRL_ID, a.NAME from BOY a", st.generateQuery());
    st = sql("$C{BadBoy.ajdi} from $T{BadBoy BadBoy}");
    assertEquals("BadBoy.ID from BOY BadBoy", st.generateQuery());
    st = sql("$C{BadBoy.ajdi} from $T{BadBoy}");
    assertEquals("BOY.ID from BOY", st.generateQuery());
  }

View Full Code Here

    assertEquals("BOY.ID from BOY", st.generateQuery());
  }


  @Test
  public void testColumns4() {
    DbSqlBuilder st;
    
    st = sql("$T{b b} | $C{b.[name]} | $C{b.[id,name]} | $C{b.[id,name,girlId]}").use("b", Boy.class);
    assertEquals("BOY b | b.NAME | b.ID, b.NAME | b.GIRL_ID, b.ID, b.NAME", st.generateQuery());
    
    st = sql("$T{b b} | $C{b.[  name  ]} | $C{b.[  id ,  name    ]}").use("b", Boy.class);
    assertEquals("BOY b | b.NAME | b.ID, b.NAME", st.generateQuery());
    
    st = sql("$T{b b} | $C{b.[id,name]} | $C{b.[name,id]}").use("b", Boy.class);
    assertEquals("BOY b | b.ID, b.NAME | b.ID, b.NAME", st.generateQuery());
    
    st = sql("$T{b b} | $C{b.[+,nejm]} | $C{b.[ajdi,nejm]}").use("b", BadBoy.class);
    assertEquals("BOY b | b.ID, b.NAME | b.ID, b.NAME", st.generateQuery());
    
  }

View Full Code Here

0 1 2

TOP

Related Classes of jodd.db.oom.sqlgen.DbSqlBuilder

com.uphea.service.AnswerService

com.uphea.service.CountryCache

jodd.db.oom.DbNoTableTest

jodd.db.oom.DbOomQuery

jodd.db.oom.DbOomTest

jodd.db.oom.DbSqlBuilderTest

jodd.db.oom.DbSqlTemplateTest

jodd.db.oom.DbSqlTemplateWithPrefixTest

jodd.db.oom.sqlgen.chunks.ColumnsSelectChunk

jodd.db.oom.sqlgen.chunks.ColumnValueChunk

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.