sqlFactory.sql().SELECT("name","age").FROM("student").WHERE("age>10").build();//==> SELECT name,age FROM student WHERE age>10sqlFactory.sql().SELECT("name","age").FROM("student").WHERE("name='小红'").build();//==> SELECT name,age FROM student WHERE name='小红'
WHERE()关键字生成WHERE 1=1语句,动态sql构建如下
SQLsql=sqlFactory.sql().SELECT("name","age").FROM("student").WHERE();if(true){sql.AND("age > 10");}if(false){sql.AND("age < 8");}//生成sql=>SELECT name,age FROM student WHERE 1 = 1 AND age > 10
4.2 使用操作符方法
FastSQL提供了一些操作符方便SQL的构建,比如:
sqlFactory.sql().SELECT("name","age").FROM("student").WHERE("age").lt("10").AND("name").eq("'小明'").build();//生成sql=> SELECT name,age FROM student WHERE age < 10 AND name = '小明'
sqlFactory.sql().SELECT("name","age").FROM("student").WHERE("age").lt().byType(10).AND("name").eq().byType("小明").build();//==>SELECT name,age FROM student WHERE age < 10 AND name = '小明'
方法
说明
byType(Object)
根据类型生成相应字符串 ,如 byType(1)生成1 ,byType("1")生成'1'
eqByType(Object)
使用 = 连接根据类型生成相应的字符串
4.3 使用连接查询/排序
查询不及格的成绩
sqlFactory.sql().SELECT("s.name","c.subject_name","c.score_value").FROM("score c").LEFT_JOIN_ON("student s","s.id=c.student_id").WHERE("c.score_value<60").ORDER_BY("c.score_value").build();/*生成sql =>SELECT s.name, c.subject,c.score_valueFROM score cLEFT OUTER JOIN student s ON (s.id = c.student_id)WHERE c.score_value < 60ORDER BY c.score_value*/
4.4 分组查询
查询每个学生总分数
sqlFactory.sql().SELECT("s.name","sum(c.score_value) total_score").FROM("score c").LEFT_JOIN_ON("student s","s.id=c.student_id").GROUP_BY("s.name").build()/*生成sql==>SELECT s.name, sum(c.score_value) total_scoreFROM score cLEFT OUTER JOIN student s ON (s.id = c.student_id)GROUP BY s.name*/
4.5 IN语句
由于Jdbc规范不支持IN参数绑定,FastSQL提供了几种IN语句直接拼接的方式:
//1.使用字符串sqlFactory.sql().SELECT("*").FROM("student").WHERE("name").IN("('小明','小红')").build();//2.使用集合(List,Set等)sqlFactory.sql().SELECT("*").FROM("student").WHERE("name").IN(Lists.newArrayList("小明","小红")).build();//3.使用数组sqlFactory.sql().SELECT("*").FROM("student").WHERE("name").IN(newObject[]{"小明","小红"}).build();//4.使用可变参数(最简洁)sqlFactory.sql().SELECT("*").FROM("student").WHERE("name").IN_var("小明","小红").build();//生成sql==> SELECT * FROM student WHERE name IN ('小明','小红')
4.6 使用$_$()方法进行子查询
查询大于平均分的成绩(可以使用 $_$()方法)
sqlFactory.sql().SELECT("*").FROM("score").WHERE("score_value >").$_$(sqlFactory.sql().SELECT("avg(score_value)").FROM("score")).build();//生成sql==>//SELECT * FROM score//WHERE score_value > ( SELECT avg(score_value) FROM score )
带有IN的子查询
sqlFactory.sql().SELECT("*").FROM("score").WHERE().AND("score").IN().$_$(sqlFactory.sql().SELECT("DISTINCT score_value").FROM("score")).build();//生成sql==> SELECT * FROM score WHERE 1 = 1 AND score IN (SELECT DISTINCT score_value FROM score)
注:复杂sql推荐使用字符串直接构建:
Stringsql="SELECT * FROM score WHERE 1 = 1 AND score IN (SELECT DISTINCT score_value FROM score)";sqlFactory.sql().str(sql);
4.7 AND和OR结合使用
如果查询年龄大于10岁,并且名字是小明或小红
sqlFactory.sql().SELECT("*").FROM("student").WHERE("age>10").AND("(name='小明' OR name='小红')")//手动添加括号.build();//或者sqlFactory.sql().SELECT("*").FROM("student").WHERE("age>10").AND().$_$("name='小明' OR name='小红'")//$_$ 生成左右括号.build();
//使用列sqlFactory.sql().INSERT_INTO("student","id","name","age").VALUES("21","'Lily'","12").build();//=>INSERT INTO student (id,name,age) VALUES (21,'Lily',12)//不使用列sqlFactory.sql().INSERT_INTO("student").VALUES("21","'Lily'","12").build();//=>INSERT INTO student VALUES (21,'Lily',12)
修改
SET(String...items) :SET关键字
sqlFactory.sql().UPDATE("student").SET("name = 'Jack'","age = 9").WHERE("name = 'Mike'").build();//=> UPDATE student SET name = 'Jack',age = 9 WHERE name = 'Mike'
构建删除语句
sqlFactory.sql().DELETE_FROM("student").WHERE("id=12").build();//=>DELETE FROM student WHERE id=12
请发表评论