Mybatis项目测试笔记:
1.首先是新建一个java工程;
2导入8个jar包
asm-3.3.1.jar
cglib-2.2.2.jar
commons-logging-1.1.1.jar
log4j-1.2.16.jar
mysql-connector-java-5.1.5-bin.jar
slf4j-api-1.6.2.jar
slf4j-log4j12-1.6.2.jar
mybatis-3.1.1.jar
3创建数据库以及相应的user表:
4然后创建相关的javaBean---User
准备工作做完之后,准备要和数据库来连接;
5.在类路径sqlMapConfig.xml这个配置文件下;
注意如果是与spring整合的话,只需要把这个配置文件提取出来,另外还得加一个db.property;
<?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 type="cn.cic.mybatis.domain.User" alias="User"/>
<typeAlias type="cn.cic.mybatis.domain.Order" alias="Order"/>
<typeAlias type="cn.cic.mybatis.domain.Customer" alias="Customer"/>
</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/zhangping" />
<property name="username" value="root" />
<property name="password" value="1234" />
</dataSource>
</environment>
</environments>
<!-- 注册sql映射文件 -->
<mappers>
<mapper resource="cn/cic/mybatis/domain/UserMapper.xml" />
<mapper resource="cn/cic/mybatis/domain/BookMapper.xml" />
<mapper resource="cn/cic/mybatis/domain/OrderMapper.xml" />
<mapper resource="cn/cic/mybatis/domain/CustomerMapper.xml" />
</mappers>
</configuration>
6.然后编写相应的UserMapper.xml映射文件,这个东西里面的内容特别重要,甚至要自己定义mapper查询条件criteria和自定义mapper;
<?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">
<!-- 在当前映射文件中编写对于User进行操作的所有sql -->
<mapper namespace="cn.cic.mybatis.domain.User">
<sql id="allColumns">
userid,username,userage ,useraddress
</sql>
<!-- 在select标签中编写查询语句 -->
<!--
id:当前sql语句的一个唯一标识
parameterType:sql语句要接收的参数类型
resultType:当前sql执行完之后,框架负责包装的数据类型
-->
<select id="selectUserById" parameterType="int" resultType="User">
select
<include refid="allColumns"/>
from user where userid = #{userid}
</select>
</mapper>
7.然后测试一下:
public class MybatisTest {
SqlSessionFactory sessionFactory;
// 初始化会话工厂对象
@Before
public void initFacory() throws Exception {
String resource = "sqlMapConfig.xml";
// 读取框架核心配置文件sqlMapConfig.xml,返回输入流
InputStream is = Resources.getResourceAsStream(resource);
// 使用构建器对象创建一个会话工厂对象
sessionFactory = new SqlSessionFactoryBuilder().build(is);
}
// 根据id查询User
@Test
public void test1() throws Exception {
SqlSession session = sessionFactory.openSession();
// 使用命名空间+sql的Id唯一锁定执行的sql
User user = session.selectOne(
"cn.cic.mybatis.domain.User.selectUserById", 3);
System.out.println(user);
// 关闭Session
session.close();
}
8.
以上只是一个入门测试:
总结一句话就是:
首先建表,然后对应的那个PO类;
然后sqlMapConfig文件配置数据库连接,加载那个mapper.xml文件;
UserMapper.xml里面
<sql id="allColumns">
userid,username,userage ,useraddress
</sql>
<select id="selectUserById" parameterType="int" resultType="User">
select
<include refid="allColumns"/>
from user where userid = #{userid}
</select>
下面实现单表的增删改查;
这个主要是mapper的自定义写法要注意:
查询所有:
<sql id=”allColums”>
Userid, username, userage, useraddress
</sql>
<select id =”findAll” resutType=”user”>
Select <include refid=”allColums”> from user
</select>
根据userid删除信息:
<delete id =”deleteUserById” parameterType=”int” >
Delete from user where userid=#{userid}
//delete from user where userid = #{userid}
</delete>
根据ID更改信息:
<update id =”updateByUserId” parameterType=”user”>
Update user set
Username=#{username}
Userage =#{userage}
Useraddress=#{useraddress}
Where userid=#{userid}
</update>
添加数据:
<insert id =”insertUser” parameterType=”user”>
Insert into user (userid,username,userage,useraddress)
values (#{userid},#{username},#{userage},#{useraddress})
</insert>
》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》
下面研究模糊查询:
<sql id =”allcolums”>
Userid,username,userage,useraddress
</sql>
<select id="selectUsersByusernameLike" parameterType="User" resultType="User">
select <include refid="allColumns"/>
from user where username like '%${username}%'
</select>
模糊查询就是这个like ‘% %’里面写成这个${username}
动态查询是很复杂的
根据条件查询:
<select id =”findByCondition” parameterType=”User” resultType=”User”>
Select <include refid=”allColums”>
From user where 1=1
<if test =”userid!=null && userid!=’’”>
And userid=#{userid}
</if>
<if test =”username!=null && username!=’’”>
And username=#{username}
</if>
<if test=”userage!=null && userage!=’’”>
And userage=#{userage}
</if>
<if test =”useraddress !=null && address !=’’”>
And useraddress=#{useradress}
</if>
</select>
所以有两种写法:
关键还是得注意这个问题:那就是
From user
<where>
//然后就是
<if test=”id!=null ”>
And id=#{id}
</if>
</where>
动态更新语句:
对比一下:
<update id="updateUserByCondition" parameterType="User">
update user
<set>
<if test="username != null">
username = #{ username },
</if>
<if test="userage != null">
userage = #{userage},
</if>
<if test="useraddress != null">
useraddress = #{useraddress}
</if>
</set>
where id = #{id}
</update>
<!-- 批量插入数据 -->
<insert id="saveUserBatch" parameterType="map">
insert into user(username,userage,useraddress) values
<foreach collection="list" separator="," item="user">
(#{user.username},#{user.userage},#{user.useraddress})
</foreach>
</insert>
<!-- 根据Id集合一次查询多个数据 -->
<select id="selectUserByIdList" parameterType="map" resultType="User">
select * from user
<if test="list != null & list.size() > 0">
where id in
<foreach collection="list" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</if>
</select>