Skip to content

DasClient SqlBuilder使用手册

Jump to bottom
He, Jiehui edited this page Oct 24, 2019 · 12 revisions

简介

虽然DasClient中基于实体的API已经可以满足大部分需求，但某些情况下，用户还是需要自定义一些SQL。传统的方式是用户自己拼SQL语句，这种做法低效，易出错。DAS组件中的SqlBuilder提供了一个方便的，尽可能符合SQL语法的API来帮助用户更好的创建SQL语句。

SqlBuilder的功能主要包括3大块：

  1. 拼接SQL片段

  2. 构建语句

  3. 生成参数 SqlBuilder特点：

  4. 链式接口。大多数方法的返回值就是SqlBuilder本身，方便连贯的编写

  5. 条件拼接。很多时候根据参数值，会选择拼接或者不拼接特定语句，普通的做法是用if/else来判断，SqlBuilder提供了更好的办法来处理这种情况，会大幅简化代码长度

  6. 智能消除：并在消除部分语句时，对表达式和逻辑操作符和括号做智能判断，保证语法准确性

和实体类配合使用：

  1. SqlBuilder自身不提供用于生成WHERE条件中表达式的方法
  2. 表达式可以通过AbstractColumn接口中的方法生成。改类有两个子类：
    1. 实体类中的TableDefinition子类里面定义的ColumnDefinition
    2. 通过自定义Table对象生成的Column对象
  3. 如果需要强制指定表的分片，可以直接调用TableDefinition或Table的inShard或shardValue方法。需要注意的是
    1. TableDefinition是不可变更对象。在其上调用这些方法会创建新的实例。类似String的做法
    2. Table是用户自定义对象，在其上调用这些方法会改变原有对象

样例

下面列出一些样例。让大家快速建立SqlBuilder怎么用的印象，样例中import了SqlBuilder中的静态方法。

import static com.ppdai.das.client.SqlBuilder.*;

//查询
SqlBuilder builder = selectAllFrom(p).where(p.PeopleID.eq(j+1)).into(Person.class);
Person pk = dao.queryObject(builder);
 
builder = selectAllFrom(p).where(p.PeopleID.eq(j+1)).into(Person.class).withLock();
Person pk = dao.queryObject(builder);
 
SqlBuilder builder = select(p.Name).from(p).where().allOf(p.PeopleID.eq(k+1), p.Name.eq("test")).into(String.class);
String name = dao.queryObject(builder);
 
SqlBuilder builder = select(p.PeopleID, p.CountryID, p.CityID).from(p).where(p.PeopleID.eq(k+1)).into(Person.class);
Person pk = dao.queryObject(builder);
 
//插入
SqlBuilder builder = insertInto(p, p.Name, p.CountryID, p.CityID).values(p.Name.of("Jerry" + k), p.CountryID.of(k+100), p.CityID.of(k+200));
assertEquals(1, dao.update(builder));

//更新
SqlBuilder builder = update(Person.PERSON).set(p.Name.eq("Tom"), p.CountryID.eq(100), p.CityID.eq(200)).where(p.PeopleID.eq(k+1));
assertEquals(1, dao.update(builder));
 
//删除
SqlBuilder builder = deleteFrom(p).where(p.PeopleID.eq(k+1));
assertEquals(1, dao.update(builder));

注意

缺省情况下SqlSever的查询会在FROM 表名后面加WITH （NOLOCK）。如果不希望读到可能出现的脏数据，请调用SqlBuilder的withLock()方法来标明不为查询添加WITH （NOLOCK）子句。

API使用说明

创建

SqlBuilder的创建很简单，直接即可:

new SqlBuilder();

CRUD创建

为了方便语句生成，可以直接调用SqlBuilder的静态CRUD方法来创建实例。建议直接静态引入这些方法：

import static com.ppdai.das.client.SqlBuilder.*;

select

添加SELECT 列名集合

public static SqlBuilder select(Object...columns)

示例

select("A","B","C").build()
select(PERSON.PeopleID, PERSON.Name).build();
select(PERSON.allColumns()).from(PERSON).build());
结果：
SELECT A, B, C；
SELECT person.PeopleID, person.Name
SELECT person.PeopleID, person.Name, person.CityID, person.ProvinceID, person.CountryID, person.DataChange_LastTime FROM person

selectAll

添加SELECT *

public static SqlBuilder selectAll()

示例

selectAll();

结果

SELECT *

selectCount

添加SELECT count(1)

public static SqlBuilder selectCount()

示例

selectCount()

结果

SELECT count(1)

selectDistinct

添加SELECT DISTINCT 列名集合

public static SqlBuilder selectDistinct(Object...columns)

示例

selectDistinct(PERSON.PeopleID)

结果

SELECT DISTINCT person.PeopleID

selectTop

添加SELECT TOP 列名集合

public static SqlBuilder selectTop(int count, Object...columns)

示例

selectTop(3, PERSON.PeopleID, PERSON.Name)

结果

SELECT TOP 3 person.PeopleID, person.Name

selectAllFrom

添加SELECT table的所有列

public static SqlBuilder selectAllFrom(TableDefinition table)

示例

selectAllFrom(PERSON)

结果

SELECT person.PeopleID, person.Name, person.CityID, person.ProvinceID, person.CountryID, person.DataChange_LastTime FROM person

insertInto

添加INSERT INTO table （列名集合）

public static SqlBuilder insertInto(TableDefinition table, ColumnDefinition...columns)

示例

insertInto(p, p.CityID, p.CountryID)

结果

INSERT INTO person (person.CityID, person.CountryID)

update

添加UPDATE table

public static SqlBuilder update(TableDefinition table)

示例

update(Person.PERSON)

结果

UPDATE person

deleteFrom

添加DELETE FROM table

public static SqlBuilder deleteFrom(Object table)

示例

deleteFrom(Person.PERSON)

结果

DELETE FROM person

基本添加方法

用于添加任意的SQL片段

append

添加多个SQL片段

public SqlBuilder append(Object...segs)

示例

builder.append("ABC");
assertEquals("ABC", builder.build());
 
builder.append("ABC", "DEF");
assertEquals("ABC DEF", builder.build());

builder.append("ABC", select(PERSON.CityID).from(PERSON));
assertEquals("ABC SELECT person.CityID FROM person", builder.build());

appendTemplate

添加SQL模板（带参数占位符？的SQL语句）和参数

public SqlBuilder appendTemplate(String template, Parameter...parameters)

示例

appendTemplate("LIMIT ?, ?", Parameter.integerOf("", start), Parameter.integerOf("", count));

appendBatchTemplate

添加用于批处理的SQL模板（带参数占位符？的SQL语句）和参数定义

public SqlBuilder appendBatchTemplate(String template, ParameterDefinition...parameterDefinitions)

示例

ParameterDefinition start = xxx;
ParameterDefinition count = xxx;
appendBatchTemplate("LIMIT ?, ?", start, count);

appendWhen

在reqired为真时才添加后继的SQL片段

public SqlBuilder appendWhen(boolean required, Object...segs)

示例

builder.appendWhen(true, "ABC", "DEF");
assertEquals("ABC DEF", builder.build());

builder.appendWhen(false, "ABC", "DEF");

assertEquals("", builder.build());

appendTemplateWhen

在reqired为真时才添加SQL模板（带参数占位符？的SQL语句）和参数

public SqlBuilder appendTemplateWhen(boolean required, String template, Parameter...parameters)

示例

appendTemplateWhen(true, "ABC ?", Parameter.integerOf("aaa", 111));

appendBatchTemplateWhen

在reqired为真时才添加用于批处理的SQL模板（带参数占位符？的SQL语句）和参数定义

public SqlBuilder appendBatchTemplateWhen(boolean required, String template, ParameterDefinition...parameterDefinitions)

示例

SqlBuilder builder = new SqlBuilder();
builder.appendBatchTemplateWhen(true, "ABC ?", ParameterDefinition.integerVar("aaa"));
assertEquals("ABC ?", builder.build(ctx));

appendWith

添加多个SQL片段，并用给定的separator分隔

public SqlBuilder appendWith(Text separator, Object...segs)

示例

builder.appendWith(text("|"), "ABC", "DEF", "XYZ");
assertEquals("ABC | DEF | XYZ", builder.build());

appendWithWhen

在reqired为真时，添加多个SQL片段，并用给定的separator分隔

public SqlBuilder appendWithWhen(boolean required, Text separator, Object...segs)

示例

builder.appendWithWhen(true, text("|"), "ABC", "DEF", "XYZ");
assertEquals("ABC | DEF | XYZ", builder.build());

builder.appendWithWhen(false, text("|"), "ABC", "DEF", "XYZ");
assertEquals("", builder.build());```
### appendPlaceHolder
添加指定个数的参数占位符‘？’，并用‘，’隔开

`public SqlBuilder appendPlaceHolder(int count)`

示例

```java
builder.appendPlaceHolder(5);
assertEquals("?, ?, ?, ?, ?", builder.build());

appendPlaceHolderWhen

在reqired为真时，添加指定个数的参数占位符‘？’，并用‘，’隔开

public SqlBuilder appendPlaceHolderWhen(boolean required, int count)

示例

builder.appendPlaceHolderWhen(true, 5);
assertEquals("?, ?, ?, ?, ?", builder.build());
 
builder.appendPlaceHolderWhen(false, 5);
assertEquals("", builder.build());

常用SQL元素添加方法

用于添加常用的SQL片段 增删改查相关

from

添加FROM 表名集合

public SqlBuilder from(Object...tables)

示例

new SqlBuilder().from("ABC").build();
new SqlBuilder().from(PERSON).build();
new SqlBuilder().from(PERSON.as("p")).build();

结果

FROM ABC
FROM person
FROM person p

values

添加VALUES（‘，’号分隔的片段集合）

public SqlBuilder values(Object...segs)

示例

new SqlBuilder().values(p.CityID.of(100), p.CountryID.of(200)).build();

结果

VALUES (?, ?)

set

添加SET（‘，’号分隔的片段集合）

public SqlBuilder set(Object...segs)

示例

new SqlBuilder().set(p.Name.eq("Tom"), p.CountryID.eq(100)  ).build();

结果

SET person.Name = ?, person.CountryID = ?

where

添加WHERE和多个条件片段

public SqlBuilder where(Object...conditions)

示例

new SqlBuilder().select(p.PeopleID).from(p, PERSON).where(p.CityID.equal(PERSON.CityID), AND, p.PeopleID.greaterThan(PERSON.CountryID)).build();

结果

SELECT p.PeopleID FROM person p, person WHERE p.CityID = person.CityID AND p.PeopleID > person.CountryID

groupBy

添加GROUP BY 列名集合

public SqlBuilder groupBy(Object...columns)

示例

new SqlBuilder().groupBy(p.CountryID, p.DataChange_LastTime).build();

结果

GROUP BY p.CountryID, p.DataChange_LastTime

having

添加HAVING 条件集合

public SqlBuilder having(Object...conditions)

示例

new SqlBuilder().having(p.CityID.equal(nameNV), AND, p.PeopleID.greaterThan(nameV)).build();

结果

HAVING p.CityID = T.nameNV AND p.PeopleID > T.nameV

orderBy

添加ORDER BY 列名集合

public SqlBuilder orderBy(Object...columns)

示例

new SqlBuilder().orderBy(p.CountryID, nameNV).build();
new SqlBuilder().orderBy(p.CountryID.desc(), nameNV.desc()).build();

结果

ORDER BY p.CountryID ASC, T.nameNV ASC
ORDER BY p.CountryID DESC, T.nameNV DESC

表join相关

join

join

public SqlBuilder join(Object...segs)

示例

PersonDefinition p = PERSON.as("p");
new SqlBuilder().select(p.PeopleID, p.Name, p.CityID).from(p).join().build();

结果

SELECT p.PeopleID, p.Name, p.CityID FROM person p JOIN

innerJoin

innerJoin

public SqlBuilder innerJoin(Object...segs)

示例

new SqlBuilder().select(p.PeopleID, p.Name, p.CityID).from(p).innerJoin(PERSON.as("t")).build();

结果

SELECT p.PeopleID, p.Name, p.CityID FROM person p INNER JOIN person t

fullJoin

fullJoin

public SqlBuilder fullJoin(Object...segs)

示例

new SqlBuilder().select(p.PeopleID, p.Name, p.CityID).from(p).fullJoin(PERSON.as("t")).build();	

结果

SELECT p.PeopleID, p.Name, p.CityID FROM person p FULL JOIN person t

leftJoin

leftJoin

public SqlBuilder leftJoin(Object...segs)

示例

new SqlBuilder().select(p.PeopleID, p.Name, p.CityID).from(p).leftJoin(PERSON.as("t")).build();	

结果

SELECT p.PeopleID, p.Name, p.CityID FROM person p LEFT JOIN person t

rightJoin

rightJoin

public SqlBuilder rightJoin(Object...segs)

示例

new SqlBuilder().select(p.PeopleID, p.Name, p.CityID).from(p).rightJoin(PERSON.as("t")).build();

结果

SELECT p.PeopleID, p.Name, p.CityID FROM person p RIGHT JOIN person t

crossJoin

crossJoin

public SqlBuilder crossJoin(Object...segs)

示例

new SqlBuilder().select(p.PeopleID, p.Name, p.CityID).from(p).crossJoin(PERSON.as("t")).build();

结果

SELECT p.PeopleID, p.Name, p.CityID FROM person p CROSS JOIN person t

on

on

public SqlBuilder on(Object...conditions)

示例

new SqlBuilder().select(p.PeopleID, p.Name, p.CityID, nameNV, nameV).from(p).join(t).on(p.CityID.equal(t.CityID)).build();

结果

SELECT p.PeopleID, p.Name, p.CityID, T.nameNV, T.nameV FROM person p JOIN test T ON p.CityID = t.CityID

using

using

public SqlBuilder using(AbstractColumn column)

示例

new SqlBuilder().select(p.PeopleID, p.Name, p.CityID, nameNV, nameV).from(p).join(t).using(p.CityID).build();

结果

SELECT p.PeopleID, p.Name, p.CityID, T.nameNV, T.nameV FROM person p JOIN test T USING (CityID)

表达式相关

leftBracket

添加'('

public SqlBuilder leftBracket()

示例

new SqlBuilder().leftBracket().build();

结果

(

rightBracket

添加')'

public SqlBuilder rightBracket()

示例

new SqlBuilder().rightBracket().build();

结果

)

bracket

添加(片段)

public SqlBuilder bracket(Object...segs)

示例

new SqlBuilder().bracket("AAA").build();

结果

(AAA)

and

添加AND

public SqlBuilder and()

示例

new SqlBuilder().append(expression("aaa")).and().append(expression("bbb")).build();

结果

aaa AND bbb

and(Expression exp)

添加AND 表达式

public SqlBuilder and(Expression exp)

示例

new SqlBuilder().append(expression("aaa")).and(expression("bbb")).build();

结果

aaa AND bbb

allOf

添加多个表达式，用AND连接

public SqlBuilder allOf(Expression...exps)

示例

new SqlBuilder().allOf(expression("aaa"), expression("bbb"), expression("ccc")).build();	

结果

aaa AND bbb AND ccc

or

添加OR

public SqlBuilder or()

示例

new SqlBuilder().append(expression("aaa")).or().append(expression("bbb")).build();

结果

aaa OR bbb

or(Expression exp)

添加OR 表达式

public SqlBuilder or(Expression exp)

示例

new SqlBuilder().append(expression("aaa")).or(expression("bbb")).build();

结果

aaa OR bbb

anyOf

添加多个表达式，用OR连接

public SqlBuilder anyOf(Expression...exps)

示例

new SqlBuilder().anyOf(expression("aaa"), expression("bbb"), expression("ccc")).build();

结果

aaa OR bbb OR ccc

not

添加NOT

public SqlBuilder not()

示例

new SqlBuilder().not().build();

结果

NOT

not(Expression exp)

添加NOT 表达式

public SqlBuilder not(Expression exp)

示例

new SqlBuilder().not(expression("aaa")).build();

结果

NOT aaa

特殊SQL

includeAll

添加1 = 1 AND

public SqlBuilder includeAll()

示例

new SqlBuilder().includeAll().build();
new SqlBuilder().includeAll().append(expression("aaa").when(false)).build();

结果

1=1 AND
1=1

excludeAll

添加1<>1 OR

public SqlBuilder excludeAll()

示例

new SqlBuilder().excludeAll().build();
new SqlBuilder().excludeAll().append(expression("aaa").when(false)).build();

结果

1<>1 OR
1<>1

条件语句IN 用于向WHERE语句中的IN条件语句，有两种方式

AbstractColumn.in(java.util.List<?>)

在where方法中添加IN子句

public AbstractColumn.in(java.util.List<?>)

示例

new SqlBuilder().where(Person.PERSON.CountryID.in(Arrays.asList(0,2,3)));

结果

WHERE CountryID in (?,?,?) ，实参[0, 2, 3]

appendTemplate

在appendTemplate方法中添加IN子句 如例所示，在SQL的in子句中只需要传入一个?占位符。DAS会根据传入的实参长度自动处理。

public SqlBuilder appendTemplate()

示例

new SqlBuilder().appendTemplate("where CountryID in(?)",  Parameter.integerOf("", Arrays.asList(0,2,3)));

结果

WHERE CountryID In (?,?,?) ，实参[0, 2, 3]

分页

注意，分页不是标准SQL语法，请按照实际数据库选择具体的实现

limit

MySql特定分页，指定开始记录和范围大小的实际值

public SqlBuilder limit(int start, int count)

示例

new SqlBuilder().limit(10, 20);

limit

MySql特定分页，指定开始记录和范围大小的参数定义

public SqlBuilder limit(ParameterDefinition start, ParameterDefinition count)

示例

new SqlBuilder().limit(ParameterDefinition.integerVar("start"), ParameterDefinition.integerVar("count"));

limit(int count)

MySql取头count个记录，count为范围的实际值

public SqlBuilder limit(int count)

示例

new SqlBuilder().limit(10);

limit(ParameterDefinition count)

MySql取头count个记录，count为参数定义

public SqlBuilder limit(ParameterDefinition count)

示例

new SqlBuilder().limit(ParameterDefinition.integerVar("count"));

offset

MS SqlServer特定分页，指定开始记录和范围大小的实际值

public SqlBuilder offset(int start, int count)

示例

new SqlBuilder().offset(10, 20);

offset

MS SqlServer特定分页，指定开始记录和范围大小

public SqlBuilder offset(ParameterDefinition start, ParameterDefinition count)

示例

new SqlBuilder().offset(ParameterDefinition.integerVar("start"), ParameterDefinition.integerVar("count"));

top

MS SqlServer取头几条数据，count为范围的实际值

public SqlBuilder top(int start)

示例

new SqlBuilder().top(10);

top(ParameterDefinition start)

MS SqlServer取头几条数据，count为参数定义

public SqlBuilder top(ParameterDefinition start)

示例

new SqlBuilder().top(ParameterDefinition.integerVar("count"));

atPage

通用的分页方法，pageNo从1开始

public SqlBuilder atPage(int pageNo, int pageSize)

示例

builder.atPage(range.getPageNo(), range.getPageSize());

辅助方法

withLock

标记是否当数据库为SqlServer时，在SELECT FROM 表名后面添加WITH (NOLOCK) 如果数据库是mysql则无效

public SqlBuilder withLock()

示例

builder = selectAllFrom(p).where().allOf(p.PeopleID.eq(j+1)).into(Person.class).withLock();

hints

获得Hints对象

public Hints hints()

示例

new SqlBuilder().hints();

into

设置要映射的实体类，包括表实体或查询实体

public <T> SqlBuilder into(Class<T> clazz)

示例

new SqlBuilder().into(Person.class)

intoMap

设置查询结果为Map

public <T> SqlBuilder intoMap()

示例

new SqlBuilder().select(p.PeopleID, p.Name, p.CityID).from(p).where().allOf(p.PeopleID.eq(k+1)).intoMap();

intoObject

设置查询结果为简单类型，对应单列查询

public <T> SqlBuilder intoObject()

示例

builder.select(p.PeopleID, p.Name, p.CityID).from(p).where().allOf(p.PeopleID.eq(k+1)).intoMap();

内部使用方法

以下为DasClient内部使用方法，这里简要介绍一下，用户可以不用关心

//获取被映射的类：
public Class<?> getEntityType()	
//生成缺省的SQL语句	测试用，无分库分表处理
public String build()	
//生成最终SQL语句
public String build(BuilderContext context)
//生成参数定义
public List<ParameterDefinition> buildDefinitions()
//生成参数
public List<Parameter> buildParameters()

综合示例

SqlBuilder用于DasClient的接口中，主要在query和update方法里

假设有张表为Person，结构如下

@Table(name="person")
public class Person {
    public static final PersonDefinition PERSON = new PersonDefinition();
    
	public static class PersonDefinition extends TableDefinition {
	    public final ColumnDefinition PeopleID;
	    public final ColumnDefinition Name;
	    public final ColumnDefinition CityID;
	    public final ColumnDefinition ProvinceID;
	    public final ColumnDefinition CountryID;
	    public final ColumnDefinition DataChange_LastTime;
        public PersonDefinition as(String alias) {return _as(alias);}
        public PersonDefinition inShard(String shardId) {return _inShard(shardId);}
        public PersonDefinition shardBy(String shardValue) {return _shardBy(shardValue);}
        public PersonDefinition() {
            super("person");
            setColumnDefinitions(
                    PeopleID = column("PeopleID", JDBCType.INTEGER),
                    Name = column("Name", JDBCType.VARCHAR),
                    CityID = column("CityID", JDBCType.INTEGER),
                    ProvinceID = column("ProvinceID", JDBCType.INTEGER),
                    CountryID = column("CountryID", JDBCType.INTEGER),
                    DataChange_LastTime = column("DataChange_LastTime", JDBCType.TIMESTAMP)
                    );
        }
	}
    @Id
	@Column(name="PeopleID")
    @GeneratedValue(strategy = GenerationType.AUTO)
	private Integer peopleID;
	
	@Column(name="Name")
	private String name;
	
	@Column(name="CityID")
	private Integer cityID;
	
	@Column(name="ProvinceID")
	private Integer provinceID;
	
	@Column(name="CountryID")
	private Integer countryID;
	
	@Column(name="DataChange_LastTime")
	private Timestamp dataChange_LastTime;
	public Integer getPeopleID() {
		return peopleID;
	}
	public void setPeopleID(Integer peopleID) {
		this.peopleID = peopleID;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getCityID() {
		return cityID;
	}
	public void setCityID(Integer cityID) {
		this.cityID = cityID;
	}
	public Integer getProvinceID() {
		return provinceID;
	}
	public void setProvinceID(Integer provinceID) {
		this.provinceID = provinceID;
	}
	public Integer getCountryID() {
		return countryID;
	}
	public void setCountryID(Integer countryID) {
		this.countryID = countryID;
	}
	public Timestamp getDataChange_LastTime() {
		return dataChange_LastTime;
	}
	public void setDataChange_LastTime(Timestamp dataChange_LastTime) {
		this.dataChange_LastTime = dataChange_LastTime;
	}
}

包含4条记录，主键从1到4：

query

    @Test
    public void testQuery() throws Exception {
        PersonDefinition p = Person.PERSON;
        List<Integer> pks = new ArrayList<>();
        for (int k = 0; k < TABLE_MODE; k++)
            pks.add(k+1);
        SqlBuilder builder = new SqlBuilder();
        builder.selectAllFrom(p).where().allOf(p.PeopleID.in(pks)).orderBy(p.PeopleID.asc()).into(Person.class);
        List<Person> plist = dao.query(builder);
        assertEquals(4, plist.size());
        for (int k = 0; k < TABLE_MODE; k++) {
            Person pk = plist.get(k);
            assertNotNull(p);
            assertEquals(k+1,  pk.getPeopleID().intValue());
            assertEquals("test",  pk.getName());
        }
    }

insert

    @Test
    public void testInsertBuilder() throws Exception {
        PersonDefinition p = Person.PERSON;
        for (int k = 0; k < TABLE_MODE; k++) {
            SqlBuilder builder = new SqlBuilder();
            builder.insertInto(p, p.Name, p.CountryID, p.CityID).values(p.Name.of("Jerry" + k), p.CountryID.of(k+100), p.CityID.of(k+200));
            assertEquals(1, dao.update(builder));
            Person pk = new Person();
            pk.setName("Jerry" + k);
            List<Person> pl = dao.queryBySample(pk);
            assertEquals(1,  pl.size());
            pk = pl.get(0);
            assertNotNull(pk.getPeopleID());
            assertEquals("Jerry" + k, pk.getName());
            assertEquals(k+100, pk.getCountryID().intValue());
            assertEquals(k+200, pk.getCityID().intValue());
        }
    }

update

    @Test
    public void testUpdateBuilder() throws Exception {
        PersonDefinition p = Person.PERSON;
        for (int k = 0; k < TABLE_MODE; k++) {
            SqlBuilder builder = new SqlBuilder();
            builder.update(Person.PERSON).set(p.Name.eq("Tom"), p.CountryID.eq(100), p.CityID.eq(200)).where(p.PeopleID.eq(k+1));
            assertEquals(1, dao.update(builder));
            Person pk = new Person();
            pk.setPeopleID(k + 1);
            pk = dao.queryByPk(pk);
            
            assertEquals("Tom", pk.getName());
            assertEquals(100, pk.getCountryID().intValue());
            assertEquals(200, pk.getCityID().intValue());
        }
    }

delete

    @Test
    public void testDeleteBuilder() throws Exception {
        PersonDefinition p = Person.PERSON;
        for (int k = 0; k < TABLE_MODE; k++) {
            SqlBuilder builder = new SqlBuilder();
            builder.deleteFrom(p).where(p.PeopleID.eq(k+1));
            assertEquals(1, dao.update(builder));
            Person pk = new Person();
            pk.setPeopleID(k + 1);
            assertNull(dao.queryByPk(pk));
        }
    }
Clone this wiki locally