SSM[mybaits] CURD

CRUD xml操作

[com.gphper.dao.IUserDAo]
package com.gphper.dao;

import com.gphper.domain.User;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface IUserDAo {
    /**
     * 查询操作
     * @return
     */
    List<User> findAll();

    /**
     * 新增操作
     * @param user
     */
    void saveUser(User user);

    /**
     * 更新
     * @param user
     */
    void updateUser(User user);

    /**
     * 删除
     * @param uid
     */
    void deleteUser(Integer uid);

    /**
     * 根据id查询
     * @param uid
     * @return
     */
    User findById(Integer uid);

    /**
     * 根据名称模糊查询
     * @param name
     * @return
     */
    List<User> findByName(String name);

    /**
     * 查询条数
     * @return
     */
    int findCount();
}

[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.gphper.dao.IUserDAo">
    <!--  配置查询所有  -->
    <select id="findAll" resultType="com.gphper.domain.User">
        select * from user
    </select>

    <!--  新增操作  -->
    <insert id="saveUser" parameterType="com.gphper.domain.User">
        <!-- 获取新增数据id order表示先执行还是后执行 -->
        <selectKey keyProperty="id" keyColumn="id" resultType="java.lang.Integer" order="AFTER">
            select last_insert_id();
        </selectKey>
        insert into user(name,password,gender,number) values (#{name},#{password},#{gender},#{number})
    </insert>

    <!--  更新操作  -->
    <update id="updateUser" parameterType="com.gphper.domain.User">
        update user set name=#{name},password=#{password} where id=#{id}
    </update>

    <!--  删除操作 由于只有一个参数，所以id可以叫任何名字 -->
    <delete id="deleteUser" parameterType="java.lang.Integer">
        delete from user where id = #{id}
    </delete>

    <select id="findById" parameterType="java.lang.Integer" resultType="com.gphper.domain.User">
        select * from user where id = #{id}
    </select>

    <select id="findByName" parameterType="java.lang.String" resultType="com.gphper.domain.User">
        select * from user where name like #{name}
    </select>

    <select id="findCount" resultType="java.lang.Integer">
        select count(id) from user
    </select>
</mapper>
[Test测试]
package com.gphper;

import com.gphper.dao.IUserDAo;
import com.gphper.domain.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest {

    protected InputStream in;
    protected SqlSession sqlSession;
    protected  IUserDAo userDao;

    @Before
    public void init() throws Exception{
        //读取配置文件
        in = Resources.getResourceAsStream("MysqlMapConf.xml");
        // 创建SqlSessionFactory 工厂
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory build = sqlSessionFactoryBuilder.build(in);
        // 使用工厂创建SqlSession 对象
        sqlSession = build.openSession();
        // 使用session对象创建Dao接口的代理对象
        userDao = sqlSession.getMapper(IUserDAo.class);
    }

    @After
    public void destory() throws IOException {
        sqlSession.commit();
        //释放资源
        sqlSession.close();
        in.close();
    }
    @Test
    public void select() {
        List<User> users = userDao.findAll();
        for(User user:users){
            System.out.println(user);
        }
    }

    @Test
    public void add() {
        User user = new User();
        user.setName("cjp");
        user.setPassword("1234567890");
        user.setGender("男");
        userDao.saveUser(user);
    }

    @Test
    public void update() {
        User user = new User();
        user.setName("cjpjp");
        user.setPassword("1234");
        user.setId(2);
        userDao.updateUser(user);
    }

    @Test
    public void delete() {
        userDao.deleteUser(2);
    }

    @Test
    public void findById() {
        User u =userDao.findById(3);
        System.out.println(u);
    }

    @Test
    public void findByName(){
        List<User> users = userDao.findByName("%小%");
        for (User u : users){
            System.out.println(u);
        }
    }

    @Test
    public void findCount(){
        int num = userDao.findCount();
        System.out.println(num);
    }
}

OGNL 表达式 自定义条件对象

[QueryVo.java]
package com.gphper.domain;
public class QueryVo {
    private User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

<select id="findByQueryVo" parameterType="java.lang.String" resultType="com.gphper.domain.User">
    select * from user where name like #{user.name}
</select>

[test]
@Test
public void findByVo() {
    QueryVo vo = new QueryVo();
    User u = new User();
    u.setName("%c%");
    vo.setUser(u);

    List<User> users = userDao.findByQueryVo(vo);
    for(User user:users){
        System.out.println(user);
    }
}

查询结果中的字段和返回数据类型pojo中的属性不一致时

1. 直接通过sql语句起别名的方式转换
2. 使用 resultMap

   <resultMap id="userMap" type="com.gphper.domain.User">
       <!--  先对应主键  -->
       <id property="userId" column="id"></id>
       <!--  再对应非主键  -->
       <result property="userName" column="username"></result>
   </resultMap>
   
   <!--  配置查询所有  -->
   <select id="findAll" resultMap="userMap">
       select * from user
   </select>

有用的配置标签

• properties

  driver=com.mysql.jdbc.Driver
  url=jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=utf-8
  username=root
  password=123456

  [MysqlMapConf.xml]

  <properties resource="jdbcConfig.properties">
  </properties>
  
  <environments default="development">
      <environment id="development">
          <transactionManager type="JDBC"/>
          <!--配置数据源：创建Connection对象-->
          <dataSource type="POOLED">
              <!--driver：驱动内容-->
              <property name="driver" value="${driver}"/>
              <!--连接数据库的url-->
              <property name="url" value="${url}"/>
              <!--用户名-->
              <property name="username" value="${username}"/>
              <!--密码-->
              <property name="password" value="${password}"/>
          </dataSource>
      </environment>
  </environments>

• typeAliases 为domain起别名，且别名不区分大小写

  <typeAliases>
      <typeAlias type="com.gphper.domain.User" alias="user"></typeAlias>
  </typeAliases>

  也可以使用 package标签

  <typeAliases>
      <package name="com.gphper.domain"/>
  </typeAliases>

  在xml中使用 com.gphper.domain.User 时，可以直接使用 user 替代

• 简写mapper的方式，使用package

  <mappers>
      <package name="com/gphper/dao"/>
  </mappers>