个人中心
退出
Mybatis中数据库的增删改查和动态Sql
数据库的增删改查：
马克- to-win：马克 java社区：防盗版实名手机尾号： 73203
马克-to-win：以下例子的配置文件中出现了一些mybatis特有的动态Sql的标签，比如trim，foreach或set标签。动态Sql是为了应对更多的情况。trim标签的功能是可以在自己包含的内容前加上某些前缀（prefix属性），也可以在自己包含的内容后加上某些后缀（suffix属性），可以把包含内容的首部某些内容删掉（prefixOverrides属性），也可以把包含内容的尾部的某些内容删掉（suffixOverrides属性）。
例 1.2
package com.mapper;
import com.Register;
import java.util.List;
import java.util.Map;
public interface RegisterMapper {
public int insertBatch(List list);
public List<Register> selectAll();
int deleteByPrimaryKey(Integer id);
int insert(Register record);
int insertSelective(Register record);
Register selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(Register record);
int updateByPrimaryKey(Register record);
List<Register> selectPageList(Map map);
List<Register> selectList(Map map);
int selectCount(Map map);
}
RegisterMapper.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.mapper.RegisterMapper">
<select id="selectAll" resultType="com.Register">
select * from register;
</select>
<!--
生成的结果是insert into register(Id,Name,Age) values (?,?,?) , (?,?,?) -->
<insert id="insertBatch" parameterType="java.util.List">
insert into register(Id,Name,Age)
values
<foreach collection="list" item="item" index="index" separator="," >
(#{item.id},#{item.name},#{item.age})
</foreach>
</insert>
<resultMap id="BaseResultMap" type="com.Register">
<id column="Id" jdbcType="INTEGER" property="id" />
<result column="Name" jdbcType="VARCHAR" property="name" />
<result column="Age" jdbcType="INTEGER" property="age" />
</resultMap>
<sql id="Base_Column_List">
Id, Name, Age
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from register
where Id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from register
where Id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.Register">
insert into register (Id, Name, Age
)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}
)
</insert>
<insert id="insertSelective" parameterType="com.Register">
insert into register
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
Id,
</if>
<if test="name != null">
Name,
</if>
<if test="age != null">
Age,
</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="age != null">
#{age,jdbcType=INTEGER},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.Register">
update register
<set>
<if test="name != null">
Name = #{name,jdbcType=VARCHAR},
</if>
<if test="age != null">
Age = #{age,jdbcType=INTEGER},
</if>
</set>
where Id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.Register">
update register
set Name = #{name,jdbcType=VARCHAR},
Age = #{age,jdbcType=INTEGER}
where Id = #{id,jdbcType=INTEGER}
</update>
<select id="selectList" parameterType="java.util.Map" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from
test.register
<where>
<if test="id != null">
and Id= #{id,jdbcType=INTEGER}
</if>
<if test="name != null">
and Name= #{name,jdbcType=VARCHAR}
</if>
<if test="age != null">
and Age= #{age,jdbcType=INTEGER}
</if>
</where>
</select>
<select id="selectCount" parameterType="java.util.Map" resultType="int">
select count(1) from test.register
<where>
<if test="id != null">
and Id= #{id,jdbcType=INTEGER}
</if>
<if test="name != null">
and Name= #{name,jdbcType=VARCHAR}
</if>
<if test="age != null">
and Age= #{age,jdbcType=INTEGER}
</if>
</where>
</select>
</mapper>
mybatis-config.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>
<typeAlias
alias="Register"
type="com.Register" />
</typeAliases>
<environments default="myEnv">
<environment id="myEnv">
<transactionManager type="jdbc"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test"/>
<property name="username" value="root"/>
<property name="password" value="1234"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/mapper/RegisterMapper.xml" />
</mappers>
</configuration>
测试主程序:
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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 com.Register;
import com.mapper.RegisterMapper;
public class TestMark_to_win {
private SqlSession sqlSession = null;
private RegisterMapper registerMapper = null;
public static void main(String[] args) {
TestMark_to_win tmw = new TestMark_to_win();
tmw.processData();
}
public void processData() {
sqlSession = getSession();
registerMapper = sqlSession.getMapper(RegisterMapper.class);
dataInsert();
dataUpdate();
dataSelect();
dataDelete();
if (null != sqlSession) {
sqlSession.commit();
sqlSession.close();
}
}
private void dataDelete() {
System.out.println(registerMapper.deleteByPrimaryKey(51));
}
private void dataUpdate() {
Register register = new Register();
register.setId(50);
register.setName("bbb");
System.out.println("registerMapper.updateByPrimaryKeySelective 的返回值是 "+registerMapper.updateByPrimaryKeySelective(register));
Register registerU = new Register();
registerU.setId(52);
registerU.setName("bbb2");
/*下面的话，去掉就会出现问题，Age列就会变成NULL*/
// registerU.setAge(80);
System.out.println("registerMapper.updateByPrimaryKey 的返回值是 "+registerMapper.updateByPrimaryKey(registerU));
}
private void dataInsert() {
Register register = new Register();
register.setAge(80);
register.setId(50);
register.setName("aaa");
registerMapper.insert(register);
Register registerSel = new Register();
registerSel.setId(49);
registerMapper.insertSelective(registerSel);
// 批量插入
Register r1 = new Register();
r1.setAge(80);
r1.setId(51);
r1.setName("aaa");
Register r2 = new Register();
r2.setAge(80);
r2.setId(52);
r2.setName("aaa");
List<Register> l = new ArrayList<Register>();
l.add(r1);
l.add(r2);
registerMapper.insertBatch(l);
}
private void dataSelect() {
Register register = registerMapper.selectByPrimaryKey(50);
System.out.println(register.toString());
List<Register> list = registerMapper.selectAll();
System.out.println("select all begin");
for (Register r : list) {
System.out.println(r.toString());
}
System.out.println("selectList begin");
Map map = new HashMap();
map.put("name", "aaa");
map.put("age", 23);
List<Register> listS = registerMapper.selectList(map);
for (Register r : listS) {
System.out.println(r.toString());
}
System.out.println("selectCount begin");
Map mapC = new HashMap();
mapC.put("name", "aaa");
mapC.put("age", 23);
int num = registerMapper.selectCount(mapC);
System.out.println("selectCount result is "+num);
}
private SqlSession getSession() {
try {
InputStream is = Resources
.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(is);
return sqlSessionFactory.openSession();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
分类导航