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.

    
  }


  @Test
  public void testReferencesAndEscapes() {
    DbSqlBuilder st;


    assertEquals("...$foo...", new DbSqlBuilder("...\\$foo...").generateQuery());
    assertEquals("$foo", new DbSqlBuilder("\\$foo").generateQuery());
    assertEquals("...\\$foo...", new DbSqlBuilder("...\\\\\\$foo...").generateQuery());
    assertEquals("\\$foo", new DbSqlBuilder("\\\\\\$foo").generateQuery());
    assertEquals("$f", new DbSqlBuilder("\\$f").generateQuery());




    st = sql("$T{BadBoy b} x$b.ajdi=2").aliasColumnsAs(TABLE_NAME);
    assertEquals("BOY b xb.ID=2", st.generateQuery());


    st = sql("$T{BadBoy b} $b.ajdi+2").aliasColumnsAs(TABLE_NAME);
    assertEquals("BOY b b.ID+2", st.generateQuery());


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


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


    st = sql("$C{b.ajdi} $T{Boy Boy, BadBoy b} - \\$$Boy.id $b.ajdi").aliasColumnsAs(TABLE_NAME);
    assertEquals("b.ID as BOY$ID BOY Boy, BOY b - $Boy.ID b.ID", st.generateQuery());


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


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


    st = sql("$C{b.*} $T{Boy Boy, BadBoy  b} - $b.ajdi 'foo\\$'").aliasColumnsAs(TABLE_NAME);
    assertEquals("b.ID as BOY$ID, b.GIRL_ID as BOY$GIRL_ID, b.NAME as BOY$NAME BOY Boy, BOY b - b.ID 'foo$'", st.generateQuery());


    st = sql("$T{BadBoy  b} - $b.ajdi=2,$b.ajdi<$b.ajdi").aliasColumnsAs(TABLE_NAME);
    assertEquals("BOY b - b.ID=2,b.ID<b.ID", st.generateQuery());
  }

View Full Code Here

  }




  @Test
  public void testMatch() {
    DbSqlBuilder st;


    Boy boy = new Boy();
    boy.id = 1;
    boy.girlId = 3;
    st = sql("$T{boy boy} where $M{boy=boy}").use("boy", boy);
    assertEquals("BOY boy where (boy.GIRL_ID=:boy.girlId and boy.ID=:boy.id)", st.generateQuery());
    Map<String, ParameterValue> map = st.getQueryParameters();
    assertEquals(2, map.size());
    assertEquals(Integer.valueOf(1), map.get("boy.id").getValue());
    assertEquals(Integer.valueOf(3), map.get("boy.girlId").getValue());




    boy.id = 4;
    boy.girlId = 1;
    st = sql("$T{boy b} where $M{b=boy}").use("boy", boy);
    assertEquals("BOY b where (b.GIRL_ID=:boy.girlId and b.ID=:boy.id)", st.generateQuery());
    map = st.getQueryParameters();
    assertEquals(2, map.size());
    assertEquals(Integer.valueOf(4), map.get("boy.id").getValue());
    assertEquals(Integer.valueOf(1), map.get("boy.girlId").getValue());


    BadBoy badBoy = new BadBoy();
    st = sql("$T{boy b} where $M{b=boy}").use("boy", badBoy);
    assertEquals("BOY b where (1=1)", st.generateQuery());
    map = st.getQueryParameters();
    assertNull(map);


    st = sql("$T{boy b} where $M{b=boy}").use("boy", badBoy);
    assertEquals("BOY b where (1=1)", st.generateQuery());
    map = st.getQueryParameters();
    assertNull(map);




    badBoy.ajdi = Integer.valueOf(4);
    badBoy.girlId = Integer.valueOf(1);
    st = sql("$T{boy b} where $M{b=boy}").use("boy", badBoy);
    assertEquals("BOY b where (b.ID=:boy.ajdi and b.GIRL_ID=:boy.girlId)", st.generateQuery());
    map = st.getQueryParameters();
    assertEquals(2, map.size());
    assertEquals(Integer.valueOf(4), map.get("boy.ajdi").getValue());
    assertEquals(Integer.valueOf(1), map.get("boy.girlId").getValue());


    badBoy.ajdi = null;
    st = sql("$T{boy b} where $M{b=boy.*}").use("boy", badBoy);
    assertEquals("BOY b where (b.ID=:boy.ajdi and b.GIRL_ID=:boy.girlId and b.NAME=:boy.nejm)", st.generateQuery());
    map = st.getQueryParameters();
    assertEquals(3, map.size());
    assertEquals(Integer.valueOf(1), map.get("boy.girlId").getValue());
  }

View Full Code Here

  }




  @Test
  public void testJoin() {
    DbSqlBuilder st = sql("select $C{bb.*}, $C{bg.+} from $T{BadGirl bg} join $T{Boy bb} on $bg.+=bb.GIRL_ID");
    assertEquals("select bb.GIRL_ID, bb.ID, bb.NAME, bg.ID from GIRL bg join BOY bb on bg.ID=bb.GIRL_ID", st.generateQuery());
  }

View Full Code Here

    dbOom.registerType(Girl.class);
  }


  @Test
  public void testTablePrefixSuffix() {
    DbSqlBuilder st;


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


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

View Full Code Here

    Girl girl_condition = new Girl();
    girl_condition.speciality = "swim";


    String tableRef = "ggg";


    DbSqlBuilder dsb = sql()
        ._("select * from ")
        .table(girl, tableRef)
        ._(" where ")
        .match(tableRef, "conditionRef")
        .use("conditionRef", girl_condition);


    q = new DbOomQuery(dsb);


    list = q.list(Girl.class);


    assertEquals(1, list.size());
    checkGirl1((Girl) list.get(0));    // swim


    dsb = sql()
        ._("update ")
        .table(girl, tableRef)
        .set(tableRef, girl)
        ._(" where ")
        .match(tableRef, "conditionRef")
        .use("conditionRef", girl_condition);


    q = new DbOomQuery(dsb);


    assertEquals(1, q.executeUpdate());


    girl_condition.speciality = "run";


    dsb = sql()
        ._("select * from ")
        .table(girl, tableRef)
        ._(" where ")
        .match(tableRef, "conditionRef")
        .use("conditionRef", girl_condition);


    q = new DbOomQuery(dsb);
    list = q.list(Girl.class);


    assertEquals(1, list.size());
    assertEquals(1, ((Girl) list.get(0)).id);
    assertEquals("run", ((Girl) list.get(0)).speciality);    // run


    // go back to swim


    girl.speciality = "swim";
    girl_condition.speciality = "run";


    dsb = sql()
        ._("update ")
        .table(girl, tableRef)
        .set(tableRef, girl)
        ._(" where ")
        .match(tableRef, "conditionRef")
        .use("conditionRef", girl_condition);


    q = new DbOomQuery(dsb);


    assertEquals(1, q.executeUpdate());




    // ---------------------------------------------------------------- etc


    badGirl = new BadGirl();
    badBoy = new BadBoy();
    DbSqlBuilder dt = sql("select $C{g.*, b.*} from $T{g}, $T{b} where $M{g=g} and $M{b=b}").use("g", badGirl).use("b", badBoy);
    q = new DbOomQuery(dt);
    list = q.list(BadBoy.class, BadGirl.class);
    assertEquals(3, list.size());


    dt = sql("select $C{g.*, b.*} from $T{g}, $T{b} where $M{g=g} and $M{b=b}").use("g", badGirl).use("b", badBoy);

View Full Code Here

    Girl girl_condition = new Girl();
    girl_condition.speciality = "swim";


    String tableRef = "ggg";


    DbSqlBuilder dsb = sql()._("update ").table(girl, tableRef).set(tableRef, girl)._("where ").
               match(tableRef, "conditionRef").use("conditionRef",girl_condition);


    String sql = dsb.generateQuery();


    Map<String, ParameterValue> params = dsb.getQueryParameters();
    assertEquals(2, params.size());


    assertEquals("piano", params.get("girl.speciality").getValue());
    assertEquals("swim", params.get("conditionRef.speciality").getValue());

View Full Code Here

    assertEquals(Integer.valueOf(11), row[0]);
    assertEquals("ANNA", row[1]);




    // two
    DbSqlBuilder dbSqlBuilder = DbSqlBuilder
        .sql("select $g.id + 10 as $C{Bean1.sum}, UCASE($g.name) as $C{Bean1.bigName}, $C{g.*} from $T{Girl g} where $g.id=1")
        .aliasColumnsAs(ColumnAliasType.COLUMN_CODE);


    assertEquals(
        "select g.ID + 10 as col_0_, UCASE(g.NAME) as col_1_, g.ID as col_2_, g.NAME as col_3_, g.SPECIALITY as col_4_ from GIRL g where g.ID=1",
        dbSqlBuilder.generateQuery());


    dbSqlBuilder.reset();


    assertEquals(
        "select g.ID + 10 as Bean1$SUM, UCASE(g.NAME) as Bean1$BIG_NAME, g.ID, g.NAME, g.SPECIALITY from GIRL g where g.ID=1",
        dbSqlBuilder.generateQuery());


    dbSqlBuilder.reset();


    q = new DbOomQuery(dbSqlBuilder);
    row = q.find(Bean1.class, Girl.class);


    Bean1 bean1 = (Bean1) row[0];
    Girl girl = (Girl) row[1];


    assertNotNull(bean1);
    assertEquals(11, bean1.getSum().intValue());
    assertEquals("ANNA", bean1.getBigName());


    assertNotNull(girl);
    assertEquals("Anna", girl.name);
    assertEquals("swim", girl.speciality);
    assertEquals(1, girl.id);




    // three
    dbSqlBuilder = DbSqlBuilder.sql(
        "select $g.id + 10 as $C{Bean1.sum}, UCASE($g.name) as $C{Bean1.bigName}, $C{g.*} from $T{Girl g} where $g.id=1");
    assertEquals(
        "select g.ID + 10 as Bean1$SUM, UCASE(g.NAME) as Bean1$BIG_NAME, g.ID, g.NAME, g.SPECIALITY from GIRL g where g.ID=1",
        dbSqlBuilder.generateQuery());


    dbSqlBuilder.reset();


    q = new DbOomQuery(dbSqlBuilder);
    row = q.find(Bean1.class, Girl.class);


    bean1 = (Bean1) row[0];

View Full Code Here

    dbOom.registerType(BadBoy.class);
  }


  @Test
  public void testTable() {
    DbSqlBuilder s;


    // [1]
    s = sql().table("Boy");
    assertEquals("BOY", s.generateQuery());
    assertNotNull(s.getTableDescriptor("Boy"));


    s = sql().table("Boy", null);
    assertEquals("BOY", s.generateQuery());
    assertNotNull(s.getTableDescriptor("Boy"));


    s = sql().table("Boy", "bbb");
    assertEquals("BOY bbb", s.generateQuery());
    assertNotNull(s.getTableDescriptor("bbb"));




    // [2]
    s = sql().table("BadBoy");
    assertEquals("BOY", s.generateQuery());
    assertNotNull(s.getTableDescriptor("BadBoy"));


    s = sql().table("BadBoy", null);
    assertEquals("BOY", s.generateQuery());
    assertNotNull(s.getTableDescriptor("BadBoy"));


    s = sql().table("BadBoy", "bbb");
    assertEquals("BOY bbb", s.generateQuery());
    assertNotNull(s.getTableDescriptor("bbb"));




    // [3]
    s = sql().table(Boy.class);
    assertEquals("BOY Boy", s.generateQuery());
    assertNotNull(s.getTableDescriptor("Boy"));


    s = sql().table(Boy.class, null);
    assertEquals("BOY", s.generateQuery());
    assertNotNull(s.getTableDescriptor("Boy"));


    s = sql().table(Boy.class, "bbb");
    assertEquals("BOY bbb", s.generateQuery());
    assertNotNull(s.getTableDescriptor("bbb"));




    // [4]
    s = sql().table(BadBoy.class);
    assertEquals("BOY BadBoy", s.generateQuery());
    assertNotNull(s.getTableDescriptor("BadBoy"));


    s = sql().table(BadBoy.class, null);
    assertEquals("BOY", s.generateQuery());
    assertNotNull(s.getTableDescriptor("BadBoy"));


    s = sql().table(BadBoy.class, "bbb");
    assertEquals("BOY bbb", s.generateQuery());
    assertNotNull(s.getTableDescriptor("bbb"));




    // [5]
    s = sql().table("bbb").use("bbb", Boy.class);
    assertEquals("BOY", s.generateQuery());
    assertNotNull(s.getTableDescriptor("bbb"));


    s = sql().table("bbb", null).use("bbb", Boy.class);
    assertEquals("BOY", s.generateQuery());
    assertNotNull(s.getTableDescriptor("bbb"));


    s = sql().table("bbb", "x").use("bbb", Boy.class);
    assertEquals("BOY x", s.generateQuery());
    assertNotNull(s.getTableDescriptor("x"));


    // [6]
    s = sql().table("Boy bbb");
    assertEquals("BOY bbb", s.generateQuery());
    assertNotNull(s.getTableDescriptor("bbb"));


    s = sql().table("BadBoy bbb");
    assertEquals("BOY bbb", s.generateQuery());
    assertNotNull(s.getTableDescriptor("bbb"));


  }

View Full Code Here


  @Test
  public void testInsert() {
    Boy b = new Boy();


    DbSqlBuilder dbc = sql().insert("Boy", b);
    assertEquals("insert into BOY (GIRL_ID, ID) values (:boy.girlId, :boy.id)", dbc.generateQuery());
    assertEquals(2, dbc.getQueryParameters().size());
    assertEquals(Integer.valueOf(0), dbc.getQueryParameters().get("boy.girlId").getValue());
    assertEquals(Integer.valueOf(0), dbc.getQueryParameters().get("boy.id").getValue());


    dbc = sql().insert(Boy.class, b);
    assertEquals("insert into BOY (GIRL_ID, ID) values (:boy.girlId, :boy.id)", dbc.generateQuery());
    assertEquals(2, dbc.getQueryParameters().size());
    assertEquals(Integer.valueOf(0), dbc.getQueryParameters().get("boy.girlId").getValue());
    assertEquals(Integer.valueOf(0), dbc.getQueryParameters().get("boy.id").getValue());


    dbc = sql().insert(b);
    assertEquals("insert into BOY (GIRL_ID, ID) values (:boy.girlId, :boy.id)", dbc.generateQuery());
    assertEquals(2, dbc.getQueryParameters().size());
    assertEquals(Integer.valueOf(0), dbc.getQueryParameters().get("boy.girlId").getValue());
    assertEquals(Integer.valueOf(0), dbc.getQueryParameters().get("boy.id").getValue());


  }

View Full Code Here


  @Test
  @SuppressWarnings({"unchecked"})
  public void testValue() {
    Boy b = new Boy();
    DbSqlBuilder dbc = new DbSqlBuilder();
    assertEquals(":zzz", dbc.value("zzz", Integer.valueOf(b.girlId)).generateQuery());
    assertEquals(1, dbc.getQueryParameters().size());
    assertEquals(Integer.valueOf(0), dbc.getQueryParameters().get("zzz").getValue());


    assertEquals(":zzz :p0", dbc._().value(Integer.valueOf(3)).generateQuery());
    assertEquals(2, dbc.getQueryParameters().size());
    assertEquals(Integer.valueOf(3), dbc.getQueryParameters().get("p0").getValue());


    assertEquals(":zzz :p0 :p1", dbc._().valueRef("val").use("val", Integer.valueOf(7)).generateQuery());
    assertEquals(3, dbc.getQueryParameters().size());
    assertEquals(Integer.valueOf(3), dbc.getQueryParameters().get("p0").getValue());
    assertEquals(Integer.valueOf(7), dbc.getQueryParameters().get("p1").getValue());


    dbc = new DbSqlBuilder();
    List l = new ArrayList();
    l.add("v1");
    l.add(Integer.valueOf(2));
    assertEquals(":zzz0, :zzz1", dbc.value("zzz", l).generateQuery());
    assertEquals("v1", dbc.getQueryParameters().get("zzz0").getValue());
    assertEquals(Integer.valueOf(2), dbc.getQueryParameters().get("zzz1").getValue());


  }

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.