MyBatis注解配置动态SQL
ＭｙＳＱＬ创建表
DROP TABLE IF EXISTS `tb_employee`;
CREATE TABLE `tb_employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`loginname` varchar(18) DEFAULT NULL,
`PASSWORD` varchar(18) DEFAULT NULL,
`NAME` varchar(18) DEFAULT NULL,
`SEX` char(2) DEFAULT NULL,
`AGE` int(11) DEFAULT NULL,
`phone` varchar(21) DEFAULT NULL,
`sal` double DEFAULT NULL,
`state` varchar(18) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
INSERT INTO `tb_employee` VALUES ('1', 'jack', '123456', '杰克', '男', '45', '15315153531', '13252', 'ACTIVE');
INSERT INTO `tb_employee` VALUES ('2', 'rose', '123456', '露丝', '女', '23', '1510223426', '33261', 'ACTIVE');
INSERT INTO `tb_employee` VALUES ('3', 'tom', '123456', '汤姆', '男', '36', '156556486', '8541', 'ACTIVE');
INSERT INTO `tb_employee` VALUES ('4', 'alice', '123456', '爱丽丝', '女', '18', '1565563486', '4631', 'ACTIVE');
MyBatis连接MySQL部分
db.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/test username=root password=mysql
mybatis-config.xml
<!-- XML 配置文件包含对 MyBatis 系统的核心设置 -->
<configuration>
<properties resource="db.properties"/>
<!-- 指定 MyBatis 所用日志的具体实现 -->
<settings>
<setting name="logImpl" value="LOG4J"/>
<!-- 要使延迟加载生效必须配置下面两个属性 -->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<environments default="mysql">
<!-- 环境配置，即连接的数据库。 -->
<environment id="mysql">
<!-- 指定事务管理类型，type="JDBC"指直接简单使用了JDBC的提交和回滚设置 -->
<transactionManager type="JDBC"/>
<!-- dataSource指数据源配置，POOLED是JDBC连接对象的数据源连接池的实现。 -->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- mappers告诉了MyBatis去哪里找持久化类的映射文件 -->
<mappers>
<mapper class="org.fkit.mapper.EmployeeMapper"/>
</mappers>
</configuration>
log4j.properties
log4j.rootLogger=ERROR, stdout log4j.logger.org.fkit.mapper.EmployeeMapper=DEBUG log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
实现映射类
public class Employee implements Serializable {
private Integer id; // 主键id
private String loginname; // 登录名
private String password; // 密码
private String name; // 真实姓名
private String sex; // 性别
private Integer age; // 年龄
private String phone; // 电话
private Double sal; // 薪水
private String state; // 状态
public Employee() {
super();
// TODO Auto-generated constructor stub
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLoginname() {
return loginname;
}
public void setLoginname(String loginname) {
this.loginname = loginname;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Double getSal() {
return sal;
}
public void setSal(Double sal) {
this.sal = sal;
}
public String getState() {
return state;
}
public void setState(String state) {
this.state = state;
}
@Override
public String toString() {
return "Employee [id=" + id + ", loginname=" + loginname
+ ", password=" + password + ", name=" + name + ", sex=" + sex
+ ", age=" + age + ", phone=" + phone + ", sal=" + sal
+ ", state=" + state + "]";
}
}
编写连接数据库的ｘｍｌ文件，和ｌｏｇ４ｊ．ｐｒｏｐｅｒｔｉｅｓ
实现这些类和接口
获取SqlSessionFactory和SQLSession
public class FKSqlSessionFactory {
private static SqlSessionFactory sqlSessionFactory=null;
static{
try {
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
public static SqlSessionFactory getSqlSessionFactory(){
return sqlSessionFactory;
}
}
实现方法接口
public interface EmployeeMapper {
@SelectProvider(type=EmployeeDynaSqlProvider.class,method="selectWhitParam")
List<Employee> selectWhitParam(Map<String, Object> params);
@InsertProvider(type=EmployeeDynaSqlProvider.class,method="insertEmployee")
@Options(useGeneratedKeys=true,keyProperty="id")
int insertEmployee(Employee emp);
@SelectProvider(type=EmployeeDynaSqlProvider.class,method="selectWhitById")
Employee selectWhitById(Map<String, Object> params);
@UpdateProvider(type=EmployeeDynaSqlProvider.class,method="updateEmployee")
void updateEmployee(Employee emp);
@DeleteProvider(type=EmployeeDynaSqlProvider.class,method="deleteEMployee")
void deleteEMployee(Map<String, Object> params);
}
实现动态SQL类
public class EmployeeDynaSqlProvider {
/**
* 查询数据
* @param param 查询条件
* @return sql
*/
public String selectWhitParam(final Map<String, Object> param){
return new SQL(){
{
SELECT ("*");
FROM ("tb_employee");
if (param.get("id")!=null) {
WHERE ("id=#{id}");
}
if (param.get("loginname")!=null) {
WHERE ("loginname=#{loginname}");
}
if (param.get("password")!=null) {
WHERE ("PASSWORD=#{password}");
}
if (param.get("name")!=null) {
WHERE ("name=#{name}");
}
if (param.get("sex")!=null) {
WHERE ("sex=#{sex}");
}
if (param.get("age")!=null) {
WHERE ("age=#{age}");
}
if (param.get("sal")!=null) {
WHERE ("sal=#{sal}");
}
if (param.get("state")!=null) {
WHERE ("state=#{state}");
}
if (param.get("id")!=null) {
WHERE ("id=#{id}");
}
}
}.toString();
}
/**
* 插入数据
* @param emp 员工对象
* @return sql
*/
public String insertEmployee(final Employee emp){
return new SQL(){
{
INSERT_INTO("tb_employee");
if (emp.getLoginname()!=null) {
VALUES("loginname","#{loginname}");
}
if (emp.getPassword()!=null) {
VALUES("PASSWORD","#{password}");
}
if (emp.getName()!=null) {
VALUES("name","#{name}");
}
if (emp.getSex()!=null) {
VALUES("sex","#{sex}");
}
if (emp.getAge()!=null) {
VALUES("age","#{age}");
}
if (emp.getSal()!=null) {
VALUES("sal","#{sal}");
}
if (emp.getState()!=null) {
VALUES("state","#{state}");
}
if (emp.getPhone()!=null) {
VALUES("phone","#{phone}");
}
}
}.toString();
}
/**
* 查询员工
* @param param 传入参数
* @return SQL
*/
public String selectWhitById(final Map<String, Object> param){
return new SQL(){
{
SELECT ("*");
FROM ("tb_employee");
if (param.get("id")!=null) {
WHERE ("id=#{id}");
}
if (param.get("loginname")!=null) {
WHERE ("loginname=#{loginname}");
}
if (param.get("password")!=null) {
WHERE ("PASSWORD=#{password}");
}
if (param.get("name")!=null) {
WHERE ("name=#{name}");
}
if (param.get("sex")!=null) {
WHERE ("sex=#{sex}");
}
if (param.get("age")!=null) {
WHERE ("age=#{age}");
}
if (param.get("sal")!=null) {
WHERE ("sal=#{sal}");
}
if (param.get("state")!=null) {
WHERE ("state=#{state}");
}
if (param.get("id")!=null) {
WHERE ("id=#{id}");
}
}
}.toString();
}
/**
* 修改信息
* @param emp 员工对象
* @return SQL
*/
public String updateEmployee(final Employee emp){
return new SQL(){
{
UPDATE("tb_employee");
if (emp.getLoginname()!=null) {
SET ("loginname = #{loginname}");
}
if (emp.getPassword()!=null) {
SET ("password = #{password}");
}
if (emp.getName()!=null) {
SET ("name = #{name}");
}
if (emp.getSex()!=null) {
SET ("sex = #{sex}");
}
if (emp.getAge()!=null) {
SET ("age = #{age}");
}
if (emp.getPhone()!=null) {
SET ("phone = #{phone}");
}
if (emp.getSal()!=null) {
SET ("sal = #{sal}");
}
if (emp.getState()!=null) {
SET ("state = #{state}");
}
WHERE("id=#{id}");
}
}.toString();
}
/**
* 删除数据
* @param param 传入参数
* @return sql
*/
public String deleteEMployee(final Map<String, Object> param){
return new SQL(){
{
DELETE_FROM("tb_employee");
if (param.get("id")!=null) {
WHERE("id = #{id}");
}
if (param.get("loginname")!=null) {
WHERE("loginname = #{loginname}");
}
if (param.get("password")!=null) {
WHERE("password = #{password}");
}
if (param.get("name")!=null) {
WHERE("name = #{name}");
}
if (param.get("id")!=null) {
WHERE("id = #{id}");
}
if (param.get("sex")!=null) {
WHERE("sex = #{sex}");
}
if (param.get("age")!=null) {
WHERE("age = #{age}");
}
if (param.get("phone")!=null) {
WHERE("phone = #{phone}");
}
if (param.get("sal")!=null) {
WHERE("sal = #{sal}");
}
if (param.get("state")!=null) {
WHERE("state = #{state}");
}
}
}.toString();
}
}
测试类
public class DynamicSQLTest {
public static void main(String[] args) throws Exception {
SqlSession session=FKSqlSessionFactory.getSqlSession();
DynamicSQLTest dy=new DynamicSQLTest();
EmployeeMapper emp=session.getMapper(EmployeeMapper.class);
//执行查询
//dy.selectWhitParamTest(emp);
//执行插入
//dy.insertEmployee(emp);
//执行修改
//dy.updateEmployee(emp);
//执行删除
dy.deleteEMployee(emp);
//提交，关闭
session.commit();
session.close();
}
public void selectWhitParamTest(EmployeeMapper emp){
//使用ＭＡＰ装载参数
Map<String,Object> param=new HashMap<String, Object>();
param.put("loginname", "jack");
param.put("password","123456");
List<Employee> list=emp.selectWhitParam(param);
System.out.println(list);
}
/**
* 插入数据
* @param empmapper
*/
public void insertEmployee(EmployeeMapper empmapper){
Employee emp=new Employee();
emp.setLoginname("angle");
emp.setPassword("123456");
emp.setSex("女");
emp.setAge(24);
emp.setPhone("1534689512");
emp.setSal(1000.2);
//emp.setState("ACTIVE");
empmapper.insertEmployee(emp);
System.out.println("插入成功，返回id:"+emp.getId());
}
public void updateEmployee(EmployeeMapper empMapper){
Map<String, Object> param=new HashMap<String, Object>();
param.put("id", 5);
Employee emp=empMapper.selectWhitById(param);
emp.setLoginname("angelBaby");
emp.setName("丽丽");
emp.setState("ACTIVE");
emp.setPhone("15102645895");
empMapper.updateEmployee(emp);
}
public void deleteEMployee(EmployeeMapper empMapper){
Map<String, Object> param=new HashMap<String, Object>();
param.put("loginname","angelBaby");
param.put("password", "123456");
empMapper.deleteEMployee(param);
}
}
