mybatis CRUD操作
一、添加数据
IUserDao.java
public interface IUserDao {
/**
* 查询全部
* @return
*/
List<User> findAll();
/**
* 添加用户
* @param user
*/
void saveUser(User user);
}
IUserDao.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.ect.dao.IUserDao">
<select id="findAll" resultType="com.ect.domain.User">
SELECT * FROM user
</select>
<insert id="saveUser" parameterType="com.ect.domain.User">
INSERT INTO user(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address});
</insert>
</mapper>
测试类:
@Test
public void testSaveUser() throws IOException {
//1.设置用户属性
User user = new User();
user.setUsername("zhouzhou");
user.setBirthday(new Date());
user.setSex("男");
user.setAddress("上海");
//2.获取配置文件
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
//3.获取SqlSessionFactory对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//4.获取sqlSession对象
SqlSession sqlSession = factory.openSession();
//5.获取Iuser代理对象
IUserDao userDao = sqlSession.getMapper(IUserDao.class);
//6.执行添加用户方法
userDao.saveUser(user);
//7.提交事务
sqlSession.commit();
//8.释放资源
sqlSession.close();
is.close();
}
二、更新数据
IUserDao.java
/**
* 更新用户
* @param user
*/
void updateUser(User user);
IUserDao.xml
<update id="updateUser" parameterType="com.ect.domain.User">
UPDATE user SET username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} WHERE id=#{id}
</update>
测试类:
@Test
public void testUpdateUser(){
User user = new User();
user.setUsername("zhouzhou");
user.setBirthday(new Date());
user.setAddress("天津");
user.setSex("女");
user.setId(60);
//执行更新操作
userDao.updateUser(user);
//提交事务
sqlSession.commit();
}
三、删除数据
IUserDao.java
/**
* 删除用户
* @param uid
*/
void deleteUser(Integer uid);
IUserDao.xml
<delete id="deleteUser" parameterType="java.lang.Integer">
DELETE FROM user WHERE id=#{id}
</delete>
测试类:
@Test
public void testDeleteUser(){
//执行删除操作
userDao.deleteUser(60);
//提交事务
sqlSession.commit();
}
四、通过id查数据
IUserDao.java
/**
* 通过id查询用户
*/
User findById(Integer uid);
IUserDao.xml
<select id="findById" resultType="com.ect.domain.User" parameterType="java.lang.Integer">
SELECT * FROM user WHERE id=#{id}
</select>
测试类:
@Test
public void testFindById(){
User user = userDao.findById(59);
System.out.println(user);
}
五、模糊查询
IUserDao.java
/**
* 模糊查询
*/
List<User> findOne(String username);
IUserDao.xml
<select id="findOne" resultType="com.ect.domain.User" parameterType="java.lang.String">
SELECT * FROM user WHERE username like #{username}
</select>
测试类:
@Test
public void testFindOne(){
List<User> users = userDao.findOne("%王%");
for (User user : users) {
System.out.println(user);
}
}
六、保存数据细节(返回最后插入的id)
IUserDao.xml
<insert id="saveUser" parameterType="com.ect.domain.User">
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
SELECT last_insert_id();
</selectKey>
INSERT INTO user(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address});
</insert>
七、使用Queryvo作为查询条件
主要用于多对象查询
QueryVo.java
public class QueryVo {
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
IUserDao.java
/**
* 使用ognl QueryVo查询
*/
List<User> findByVo(QueryVo vo);
IUserDao.xml
<select id="findByVo" parameterType="com.ect.domain.QueryVo" resultType="com.ect.domain.User">
SELECT * FROM user WHERE username like #{user.username}
</select>
测试类:
@Test
public void testFindByVo(){
QueryVo vo = new QueryVo();
User user = new User();
user.setUsername("%王%");
vo.setUser(user);
List<User> byVo = userDao.findByVo(vo);
for (User user1 : byVo) {
System.out.println(user1);
}
}
八、如果数据库属性和实体类属性不一致
方法一,设置查询字段别名
方法二, 配置文件
IUserDao.xml
<resultMap id="userMap" type="com.ect.domain.User">
<id property="userId" column="id"></id>
<result property="usrname" column="username"></result>
</resultMap>
<select id="findAll" resultMap="userMap">
SELECT * FROM user
</select>