mybatis的SQL语句构建器
-
SQLProvider.java
package com.qfedu.test; import com.qfedu.pojo.User; import org.apache.ibatis.jdbc.SQL; import org.junit.Test; public class SQLProvider { public String getAllUser(){ return new SQL() .SELECT("uid") .SELECT("username", "password") .SELECT("age", "addr") .FROM("user") // .WHERE("uid = 1") // .OR() // .WHERE("username = 'zhangsan'") .toString(); } /** * VALUES(column, values)方法,第一个参数为数据库的表里的列名,第二个参数为要填充的数据 * 特别注意,要给每个value添加单引号,否则生成的sql语句中的值会被认为是sql表中的字段 * @return */ public String saveUser(){ return new SQL() .INSERT_INTO("user") .VALUES("username", "'shixiaopeng'") .VALUES("password", "'999999'") .VALUES("age", "'20'") .VALUES("addr", "'北京'") .toString(); } /** * VALUES(column, values)方法,第一个参数为数据库的表里的列名,第二个参数为要填充的数据 * 特别注意:给每个value填充变量值的时候,可以使用#{value }的方式, * 其中value可以是对象的属性名,也可以是map中的key * @param u * @return */ public String saveUsers(User u){ return new SQL() .INSERT_INTO("user") .VALUES("username", "#{username}") .VALUES("password", "#{password}") .VALUES("age", "#{age}") .VALUES("addr", "#{addr}") .toString(); } }
-
IUserDaoProvicer.java
package com.qfedu.test; import com.qfedu.pojo.User; import org.apache.ibatis.annotations.InsertProvider; import org.apache.ibatis.annotations.Options; import org.apache.ibatis.annotations.Select; import org.apache.ibatis.annotations.SelectProvider; import org.apache.ibatis.jdbc.SQL; import java.util.List; public interface IUserDaoProvider { /** * type:类型为SQLProvider的构建器的类对象 * method:方法执行该类对象中的具体方法 * @return */ @SelectProvider(type = SQLProvider.class, method = "getAllUser") List<User> getAllUsers(); /** * type:类型为SQLProvider的构建器的类对象 * method:方法执行该类对象中的具体方法 * @return */ @InsertProvider(type = SQLProvider.class, method = "saveUser") int saveUser(); /** * type:类型为SQLProvider的构建器的类对象 * method:方法执行该类对象中的具体方法 * Options选项可以设置额外的参数: * keyProperty主键属性,useGeneratedKeys是否使用生成策略 * @return */ @InsertProvider(type = SQLProvider.class, method = "saveUsers") @Options(useGeneratedKeys = true, keyProperty = "uid") int saveUsers(User u); }
-
TestSQLProvider.java
package com.qfedu.test; import com.qfedu.pojo.User; import com.qfedu.util.SessionUtils; import org.apache.ibatis.session.SqlSession; import org.junit.After; import org.junit.Before; import org.junit.Test; import java.util.List; public class TestSQLProvider { private SqlSession session = null; @Before public void setUp() { session = SessionUtils.getSession(); } @After public void tearDown() { SessionUtils.closeSession(session); } @Test public void testGetAllUsers() { IUserDaoProvider mapper = session.getMapper(IUserDaoProvider.class); List<User> list = mapper.getAllUsers(); for (User u : list) { System.out.println(u); } } @Test public void testSaveUser(){ IUserDaoProvider mapper = session.getMapper(IUserDaoProvider.class); int result = mapper.saveUser(); System.out.println(result); } @Test public void testSaveUsers(){ IUserDaoProvider mapper = session.getMapper(IUserDaoProvider.class); User u = new User(); u.setUsername("litao"); u.setPassword("000000"); u.setAge(20); u.setAddr("wuhan"); System.out.println(mapper.saveUsers(u));; } @Test public void testSQL(){ SQLProvider sp = new SQLProvider(); System.out.println(sp.getAllUser()); } }
-
运行结果
DEBUG [main] - ==> Preparing: SELECT uid, username, password, age, addr FROM user DEBUG [main] - ==> Parameters: TRACE [main] - <== Columns: uid, username, password, age, addr TRACE [main] - <== Row: 1, wudong, 888888, 18, 广州 TRACE [main] - <== Row: 2, leijiangnan, 888888, 18, 广州 TRACE [main] - <== Row: 3, chenggang, 888888, 18, shenzhen TRACE [main] - <== Row: 4, jinchengjie, 888888, 18, 武汉 TRACE [main] - <== Row: 5, yuanhongwu, 888888, 18, 武汉 TRACE [main] - <== Row: 6, zhengzhenxiang, 888888, 18, 北京 TRACE [main] - <== Row: 7, lishushu, 888888, 18, 北京 TRACE [main] - <== Row: 8, lixiaolong, 888888, 18, 北京 TRACE [main] - <== Row: 9, shixiaopeng, 999999, 20, ?? TRACE [main] - <== Row: 10, litao, 000000, 20, wuhan DEBUG [main] - <== Total: 10 User(uid=1, username=wudong, password=888888, age=18, addr=广州) User(uid=2, username=leijiangnan, password=888888, age=18, addr=广州) User(uid=3, username=chenggang, password=888888, age=18, addr=shenzhen) User(uid=4, username=jinchengjie, password=888888, age=18, addr=武汉) User(uid=5, username=yuanhongwu, password=888888, age=18, addr=武汉) User(uid=6, username=zhengzhenxiang, password=888888, age=18, addr=北京) User(uid=7, username=lishushu, password=888888, age=18, addr=北京) User(uid=8, username=lixiaolong, password=888888, age=18, addr=北京) User(uid=9, username=shixiaopeng, password=999999, age=20, addr=??) User(uid=10, username=litao, password=000000, age=20, addr=wuhan) Process finished with exit code 0
代码缥缈隐千锋 上下左右各不同 不识老郑真面目 只缘弹幕漫屏中 ----犹撸代码半遮面的老郑