本篇将要演示 SQL 模板的使用，目前包含 INSERT 模板、SELECT 模板、UPDATE 模板、DELETE模板。
为了演示SQL模板接入（参考 JPA接入 中的准备工作），需要如下准备：
SQL模板配置包含了SQL模板规则，SQL模板定义，SQL模板参数，SQL关系配置。具体配置可至GitHub，查看 flea-sql-template.xml
相关配置可查看 ：
<param id="insert" name="SQL模板參數" desc="用于定义SQL模板中的替换参数">
<!-- 表名 -->
<property key="table" value="student" />
<!-- 这两个不填，表示表的字段全部使用-->
<property key="columns" value="stu_name, stu_age, stu_sex, stu_state" />
<property key="values" value=":stuName:, :stuAge:, :stuSex:, :stuState:" />
</param>
<relation id="insert" templateId="insert" paramId="insert" name="SQL关系"/>
JPA方式接入SQL模板：
@Test
public void testInsertSqlTemplateFromJPA() throws Exception{
IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");
Student student = new Student();
student.setStuName("王老五");
student.setStuAge(35);
student.setStuSex(1);
student.setStuState(1);
int ret = studentSV.insert("insert", student);
LOGGER.debug("result = {}", ret);
}
运行结果：
新增数据：
JDBC方式接入SQL模板：
@Test
public void testInsertSqlTemplateFromJDBC() throws Exception {
FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");
Student student = new Student();
student.setStuName("钱老六");
student.setStuAge(30);
student.setStuSex(1);
student.setStuState(1);
int ret = FleaJDBCHelper.insert("insert", student);
LOGGER.debug("result = {}", ret);
}
运行结果：
新增数据：
相关配置可查看 ：
<param id="select" name="SQL模板參數" desc="用于定义SQL模板中的替换参数; 如需查询全部，则设置key=columns的属性值为 *，即可">
<!-- 表名 -->
<property key="table" value="student" />
<!-- SELECT 显示列 -->
<property key="columns" value="*" />
<!-- WHERE 子句 , 出现 xml不能直接识别的需要转义，如 >, < 等-->
<property key="conditions" value="stu_name LIKE :stuName: AND stu_sex = :stuSex: AND stu_age >= :minAge: AND stu_age <= :maxAge:" />
</param>
<relation id="select" templateId="select" paramId="select" name="SQL关系"/>
JPA方式接入SQL模板：
@Test
public void testQuerySqlTemplateFromJPA() throws Exception {
IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");
Student student = new Student();
student.setStuName("%老%");
student.setStuSex(1);
student.put("minAge", 20);
student.put("maxAge", 40);
LOGGER.debug("Student List = {}", studentSV.query("select", student));
}
运行结果：
JDBC方式接入SQL模板：
@Test
public void testQuerySqlTemplateFromJDBC() throws Exception {
FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");
Student student = new Student();
student.setStuName("%老%");
student.setStuSex(1);
student.put("minAge", 20);
student.put("maxAge", 40);
LOGGER.debug("Student List = {}", FleaJDBCHelper.query("select", student));
}
运行结果：
相关配置可查看 ：
<param id="update" name="SQL模板參數" desc="用于定义SQL模板中的替换参数">
<!-- 表名 -->
<property key="table" value="student" />
<!-- SET 子句 -->
<property key="sets" value="stu_name = :stuName, stu_age = :stuAge" />
<!-- WHERE 子句 , 出现 xml不能直接识别的需要转义，如 >, < 等-->
<property key="conditions" value="stu_name LIKE :sName: AND stu_state = :stuState: AND stu_sex = :stuSex: AND stu_age >= :minAge: AND stu_age <= :maxAge:" />
</param>
<relation id="update" templateId="update" paramId="update" name="SQL关系"/>
JPA方式接入SQL模板：
@Test
public void testUpdateSqlTemplateFromJPA() throws Exception {
IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");
Student student = new Student();
student.setStuName("王老五1");
student.setStuAge(40);
student.setStuState(1);
student.setStuSex(1);
student.put("sName", "%王老五%");
student.put("minAge", 20);
student.put("maxAge", 40);
LOGGER.debug("Result = {}", studentSV.update("update", student));
}
运行结果：
JDBC方式接入SQL模板：
@Test
public void testUpdateSqlTemplateFromJDBC() throws Exception {
FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");
Student student = new Student();
student.setStuName("钱老六1");
student.setStuAge(35);
student.setStuState(1);
student.setStuSex(1);
student.put("sName", "%钱老六%");
student.put("minAge", 20);
student.put("maxAge", 40);
LOGGER.debug("Result = {}", FleaJDBCHelper.update("update", student));
}
运行结果：
相关配置可查看 ：
<param id="delete" name="SQL模板參數" desc="用于定义SQL模板中的替换参数">
<!-- 表名 -->
<property key="table" value="student" />
<!-- WHERE 子句 -->
<property key="conditions" value="stu_name LIKE :stuName: AND stu_state = :stuState: AND stu_sex = :stuSex: AND stu_age >= :minAge: AND stu_age <= :maxAge:" />
</param>
<relation id="delete" templateId="delete" paramId="delete" name="SQL关系"/>
JPA方式接入SQL模板：
@Test
public void testDeleteSqlTemplateFromJPA() throws Exception {
IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");
Student student = new Student();
student.setStuName("%王老五%");
student.setStuState(1);
student.setStuSex(1);
student.put("minAge", 20);
student.put("maxAge", 40);
LOGGER.debug("Result = {}", studentSV.delete("delete", student));
}
运行结果：
JDBC方式接入SQL模板：
@Test
public void testDeleteSqlTemplateFromJDBC() throws Exception {
FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");
Student student = new Student();
student.setStuName("%钱老六%");
student.setStuState(1);
student.setStuSex(1);
student.put("minAge", 20);
student.put("maxAge", 40);
LOGGER.debug("Result = {}", FleaJDBCHelper.delete("delete", student));
}
运行结果：
当前数据库数据如下：
相关配置可查看 ：
<param id="select_1" name="SQL模板參數" desc="用于定义SQL模板中的替换参数; 如需查询全部，则设置key=columns的属性值为 *，即可">
<!-- 表名 -->
<property key="table" value="student" />
<!-- SELECT 显示列 -->
<property key="columns" value="*" />
<!-- WHERE 子句 , 出现 xml不能直接识别的需要转义，如 >, < 等-->
<property key="conditions" value="stu_name LIKE :stuName: AND stu_sex = :stuSex: AND stu_age >= :minAge: AND stu_age <= :maxAge: ORDER BY stu_id DESC LIMIT :pageStart:, :pageCount:" />
</param>
<relation id="select_1" templateId="select" paramId="select_1" name="SQL关系"/>
JPA方式接入SQL模板：
@Test
public void testQueryPageSqlTemplateFromJPA() throws Exception {
IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");
Student student = new Student();
student.setStuName("%张三%");
student.setStuSex(1);
student.put("minAge", 18);
student.put("maxAge", 20);
int pageNum = 1; // 第一页
int pageCount = 5; // 每页5条记录
student.put("pageStart", (pageNum - 1) * pageCount);
student.put("pageCount", pageCount);
List<Student> studentList = studentSV.query("select_1", student);
LOGGER.debug("Student List = {}", studentList);
LOGGER.debug("Student Count = {}", studentList.size());
}
运行结果：
JDBC方式接入SQL模板：
@Test
public void testQueryPageSqlTemplateFromJDBC() throws Exception {
FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");
Student student = new Student();
student.setStuName("%李四%");
student.setStuSex(1);
student.put("minAge", 18);
student.put("maxAge", 20);
int pageNum = 1; // 第一页
int pageCount = 5; // 每页5条记录
student.put("pageStart", (pageNum - 1) * pageCount);
student.put("pageCount", pageCount);
List<Map<String, Object>> studentList = FleaJDBCHelper.query("select_1", student);
LOGGER.debug("Student List = {}", studentList);
LOGGER.debug("Student Count = {}", studentList.size());
}
运行结果：
相关配置可查看 ：
<param id="select_2" name="SQL模板參數" desc="用于定义SQL模板中的替换参数; 如需查询全部，则设置key=columns的属性值为 *，即可">
<!-- 表名 -->
<property key="table" value="student" />
<!-- SELECT 显示列 -->
<property key="columns" value="count(*)" />
<!-- WHERE 子句 , 出现 xml不能直接识别的需要转义，如 >, < 等-->
<property key="conditions" value="stu_name LIKE :stuName: AND stu_sex = :stuSex: AND stu_age >= :minAge: AND stu_age <= :maxAge:" />
</param>
<relation id="select_2" templateId="select" paramId="select_2" name="SQL关系"/>
JPA方式接入SQL模板：
@Test
public void testQueryCountSqlTemplateFromJPA() throws Exception {
IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");
Student student = new Student();
student.setStuName("%张三%");
student.setStuSex(1);
student.put("minAge", 18);
student.put("maxAge", 20);
LOGGER.debug("Student Count = {}", studentSV.querySingle("select_2", student));
}
运行结果：
JDBC方式接入SQL模板：
@Test
public void testQueryCountSqlTemplateFromJDBC() throws Exception {
FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");
Student student = new Student();
student.setStuName("%李四%");
student.setStuSex(1);
student.put("minAge", 18);
student.put("maxAge", 20);
LOGGER.debug("Student Count = {}", FleaJDBCHelper.querySingle("select_2", student));
}
运行结果：
相关配置可查看 ：
<param id="select_3" name="SQL模板參數" desc="用于定义SQL模板中的替换参数; 如需查询全部，则设置key=columns的属性值为 *，即可">
<!-- 表名 -->
<property key="table" value="student" />
<!-- SELECT 显示列 -->
<property key="columns" value="sum(stu_age)" />
<!-- WHERE 子句 , 出现 xml不能直接识别的需要转义，如 >, < 等-->
<property key="conditions" value="1=1" />
</param>
<relation id="select_3" templateId="select" paramId="select_3" name="SQL关系"/>
JPA方式接入SQL模板：
@Test
public void testQuerySumSqlTemplateFromJPA() throws Exception {
IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");
LOGGER.debug("Student Age = {}", studentSV.querySingle("select_3", new Student()));
}
运行结果：
JDBC方式接入SQL模板：
@Test
public void testQuerySumSqlTemplateFromJDBC() throws Exception {
FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");
LOGGER.debug("Student Age = {}", FleaJDBCHelper.querySingle("select_3", new Student()));
}
运行结果：
上述单个结果查询，展示了count和sum，其他avg，max，min等相关内容可以移步 GitHub， 查看 StudentSqlTemplateTest