在INSERT 动态插入中使用if标签
插入数据库的记录中,不是每一个字段都有值,此时就可以使用if标签。
(1)条件:只有非空属性才插入。
(2)动态SQL。
数据库,建表
create table z_student(
id int not null auto_increment primary key,
name varchar(20) not null,
phone varchar(20) null,
email varchar(50) null,
sex tinyint(4) null comment '0女,1男',
locked tinyint null comment '状态,0正常,1锁定',
gmt_created datetime default CURRENT_TIMESTAMP comment '存入库的时间',
gmt_modified datetime default CURRENT_TIMESTAMP comment '修改时间',
deletes int(11) null
) comment '学生表'
insert into z_student values(null,'tom','130','xx@xx.com',1,0,null,null,0);
insert into z_student values(null,'tom2','130','xx@xx.com',1,0,null,null,0);
insert into z_student values(null,'tom3','130','xx@xx.com',1,0,null,null,0);
insert into z_student values(null,'tom4','130','xx@xx.com',1,0,null,null,0);
代码如下:
Student.java
package com.shrimpking.code03;
import java.util.Date;
/**
* @author user1
*/
public class Student
{
private int id;
private String name;
private String phone;
private String email;
private int sex;
private int locked;
private Date gmtCreated;
private Date gmtModified;
private int deletes;
public Student()
{
}
public Student(int id, String name, String phone, String email, int sex, int locked, Date gmtCreated, Date gmtModified, int deletes)
{
this.id = id;
this.name = name;
this.phone = phone;
this.email = email;
this.sex = sex;
this.locked = locked;
this.gmtCreated = gmtCreated;
this.gmtModified = gmtModified;
this.deletes = deletes;
}
public int getId()
{
return id;
}
public void setId(int id)
{
this.id = id;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public String getPhone()
{
return phone;
}
public void setPhone(String phone)
{
this.phone = phone;
}
public String getEmail()
{
return email;
}
public void setEmail(String email)
{
this.email = email;
}
public int getSex()
{
return sex;
}
public void setSex(int sex)
{
this.sex = sex;
}
public int getLocked()
{
return locked;
}
public void setLocked(int locked)
{
this.locked = locked;
}
public Date getGmtCreated()
{
return gmtCreated;
}
public void setGmtCreated(Date gmtCreated)
{
this.gmtCreated = gmtCreated;
}
public Date getGmtModified()
{
return gmtModified;
}
public void setGmtModified(Date gmtModified)
{
this.gmtModified = gmtModified;
}
public int getDeletes()
{
return deletes;
}
public void setDeletes(int deletes)
{
this.deletes = deletes;
}
@Override
public String toString()
{
return "Student{" + "id=" + id + ", name='" + name + '\'' + ", phone='" + phone + '\'' + ", email='" + email + '\'' + ", sex=" + sex + ", locked=" + locked + ", gmtCreated=" + gmtCreated + ", gmtModified=" + gmtModified + ", deletes=" + deletes + '}';
}
}
StudentMapper.java
package com.shrimpking.code03;
public interface StudentMapper
{
public int insertStudent(Student student);
}
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shrimpking.code03.StudentMapper">
<insert id="insertStudent" parameterType="Student">
insert into z_student
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="name != null">
name,
</if>
<if test="phone != null">
phone,
</if>
<if test="email != null">
email,
</if>
<if test="sex != null">
sex,
</if>
<if test="locked != null">
locked,
</if>
<if test="gmtCreated != null">
gmt_created,
</if>
<if test="gmtModified != null">
gmt_modified,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=INTEGER},
</if>
<if test="name != null">
#{name,jdbcType=VARCHAR},
</if>
<if test="phone != null">
#{phone,jdbcType=VARCHAR},
</if>
<if test="email != null">
#{email,jdbcType=VARCHAR},
</if>
<if test="sex != null">
#{sex,jdbcType=TINYINT},
</if>
<if test="locked != null">
#{locked,jdbcType=TINYINT},
</if>
<if test="gmtCreated != null">
#{gmtCreated,jdbcType=TIMESTAMP},
</if>
<if test="gmtModified != null">
#{gmtModified,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
</mapper>
mybatis.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 起别名 -->
<typeAliases>
<package name="com.shrimpking.code03"/>
</typeAliases>
<!-- 环境 -->
<environments default="development">
<environment id="development">
<!-- 默认事务管理 -->
<transactionManager type="JDBC"/>
<!-- 默认的数据库连接 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimeZone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="mysql123"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/shrimpking/code03/StudentMapper.xml"/>
</mappers>
</configuration>
StudentTest.java
package com.shrimpking.code03;
import com.shrimpking.utils.DaoUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.io.IOException;
import java.util.Date;
public class StudentTest
{
@Test
public void insert() throws IOException
{
SqlSession sqlSession = DaoUtils.getSqlSession("code03/mybatis.xml");
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student stu = new Student();
stu.setName("虾米");
stu.setPhone("18099999999");
stu.setEmail("zz@zz.com");
stu.setSex(1);
stu.setLocked(0);
stu.setGmtCreated(new Date());
stu.setDeletes(0);
int i = mapper.insertStudent(stu);
if(i > 0)
{
System.out.println("插入成功");
}
sqlSession.commit();
sqlSession.close();
}
}
DaoUtils.java
package com.shrimpking.utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.Reader;
/**
* @author user1
*/
public class DaoUtils
{
private static Reader reader;
private static SqlSessionFactory sqlSessionFactory;
public static SqlSession getSqlSession(String config) throws IOException
{
try
{
reader = Resources.getResourceAsReader(config);
//
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
}
catch (IOException e)
{
e.printStackTrace();
}
finally
{
reader.close();
}
return sqlSessionFactory.openSession();
}
}
运行截图