mybaties+Spring mvc学习之路笔记

1.入门程序

1，链接mysql数据库

SqlMapConfig.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>
    <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/db_test?
                   characterEncoding=utf8" />
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/User.xml" />
        <mapper resource="mapper/UserMapper.xml" />
    </mappers>
</configuration>

2， User.xml 用来管理sql语句

<?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">
<!-- namespace对sql进行分类化管理。对sql进行隔离，很多张表 -->
<mapper namespace="test">
    <!-- 映射文件中配置多个sql语句 -->
    <!-- 通过select执行数据库查询 --><!--id用来标识sql，称为statement的id 将sql语句封装到 
    mappedStatement对象中，这个id就是statement的id -->
    <select id="findUserById" resultType="model.User" parameterType="int">
    <!-- #{id}:其中的id表示接受 输入的参数，参数名称就是id，如果输入的参数类型是简单类型#{}中的参数可
    任意，可以是value或者其他名称 -->
        select * from tp_user1 where id = #{id}
    </select>
    <!-- 在这里出现了查询汉字字符串失败，查英文字母可以，改编码格
    式"jdbc:mysql://localhost:3306/db_test?characterEncoding=utf8" -->
    <!-- ${value}这样拼接字符串有可能造成sql注入 ，如果是简单基本类型的话括号里面必须写value-->
    <select id="findUserByName" resultType="model.User" parameterType="java.lang.String" >
        select * FROM tp_user1 where username like '%${value}%'
    </select>
    <!-- 插入用户操作 -->
    <select id="insertUser" parameterType="model.User">
        <!-- 获取刚插入记录的主键，适用于自增主键， keyPeroperty主键的名字 order相对于insert语句 resultType返回结果的类型 -->
        <!--<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
            select LAST_INSERT_ID()
        </selectKey> -->
        <!-- 非自增 ，根据mysql的uuid 生成主键  ,id必须写上在sql中 
        执行过程，通过uuid()生成主键，然后插入到sql中
        -->
        <selectKey keyProperty="id" order="BEFORE" resultType="java.lang.String">
            select uuid()
        </selectKey>
    insert into tp_user1 (username,password) value(#{username},#{password})
    </select>
</mapper>

3，代码执行

findUserById查询

String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder()
                .build(inputStream);
SqlSession session = sessionFactory.openSession();
User user = session.selectOne("test.findUserById", 1);
System.out.println(user);
session.close();

findUserByName模糊查询

String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder()
                .build(inputStream);
SqlSession session = sessionFactory.openSession();
List<User> user=session.selectList("test.findUserByName", "小明");
System.out.println(user);
session.close();

insertUser插入查询

@Test
public void insertuserTest() throws IOException {

    String resource = "SqlMapConfig.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder()
                    .build(inputStream);
    SqlSession session = sessionFactory.openSession();
    User user=new User();
    user.setUsername("夏东海");
    user.setPassword("asd123");
    session.insert("test.insertUser", user);
    //这里不提交是不行的
    session.commit();
    session.close();
}

原始dao开发

UserDao.java接口

public interface UserDao {
    //查询用户
    public abstract User findUserById(int id) throws Exception;
    //插入用户
    public abstract void insertUser(User user) throws Exception;
    //删除用户
    public abstract void deleteUser(int id) throws Exception;
    //更新用户
    public abstract void updateUser(User user) throws Exception;

}

UserDaoImp.java实现类

public class UserDaoImp implements UserDao {


    private SqlSessionFactory sqlSessionFactory;


    public UserDaoImp(SqlSessionFactory sqlSessionFactory) {
        this.sqlSessionFactory=sqlSessionFactory;
    }

    public User findUserById(int id) throws Exception {
        //线程不安全需要下载方法中。
        SqlSession session = sqlSessionFactory.openSession();
        User user=session.selectOne("test.findUserById", id);
        session.close();
        return user;
    }

    public void insertUser(User user) throws Exception {
        SqlSession session = sqlSessionFactory.openSession();
        session.insert("test.insertUser", user);
        session.commit();
        session.close();
    }

    public void deleteUser(int id) throws Exception {
        SqlSession session = sqlSessionFactory.openSession();
        session.delete("test.deleteUser", id);
        session.commit();
        session.close();

    }

    public void updateUser(User user) throws Exception {
        SqlSession session = sqlSessionFactory.openSession();
        session.delete("test.updateUser", user);
        session.commit();
        session.close();
    }

}

test.java

private SqlSessionFactory sqlSessionFactory;
@Before
public void setUp() throws Exception {
    String resource = "SqlMapConfig.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
}

@Test
public void test() throws Exception {
    UserDao userDao=new UserDaoImp(sqlSessionFactory);
    User user=userDao.findUserById(1);
    System.out.println("---------"+user);
}

mapper代理方式开发

原始的dao开发，模板代码较多。statement属于硬编码，查询参数是object类型编译阶段无法识别错误。

UserMapper.java

public interface UserMapper {

    public User findUserById(int id) throws Exception;
    /**
     * 返回类型是model.User
     * 系统内部会自动选择是selectOne或者是selectList 
     * 参数只能一个？
     *不是我们可以用bean里面套用bean的方式
     * @param name
     * @return
     * @throws Exception
     */

    public List<User> findUserByName(String name) throws Exception;
}

@Test
public void testFindUserById() throws Exception{

SqlSession  sqlSession=sqlSessionFactory.openSession();

UserMapper mapper = sqlSession.getMapper(UserMapper.class);

User user=mapper.findUserById(1);

System.out.println(user);

sqlSession.close();

}

熟悉SqlMapConfig.xml属性

1， properties

<properties resource="db.properties">
    <!-- properties中定义的property的标签定义的属性首先被读取 -->
    <!-- 然后读取 properties中的 resource元素或者是url对应的文件，并且覆盖文件中同名的属性-->
    <!-- 最后读取Mapper.xml中的parameterType传递的属性 -->
    <!-- 最好不要在properties中写property，写在properties文件中 -->
    <!-- <property name="" value=""/> -->
</properties>

2，settings

<!-- 全局设置，性能优化 -->
<settings>
    <setting name="cacheEnabled" value="true"/>
</settings> 

3，TypeAlias别名
parameterType和resultType的类型，为啥int类型和java.lang.Integer的效果是一致的。因为int就是一个默认的别名。

• 默认别名

还有一些bean类型的参数需要自定义别名

• 自定义别名

<!-- 自定义别名 -->
<typeAliases>
    <!-- 单个定义别名 -->
    <!-- <typeAlias type="model.User" alias="user"></typeAlias> -->
    <!-- 批量定义 name制定包名，别名是类名（首字母大写或者小写） （常用）-->
    <package name="model"/>
</typeAliases>

4，typeHandlers类型处理器

• 默认定义好的

• 自定义类型转换
  一般默认的都已经够用了。参考 http://blog.youkuaiyun.com/likewindy/article/details/51350995

5，mapper映射文件

    <mappers>
        <!-- 加载单个文件 -->
        <!-- <mapper resource="mapper/User.xml" />
        <mapper resource="mapper/UserMapper.xml" />
        <mapper url="某个文件的磁盘路径"/> -->
        <!-- 通过mapper接口加载单个映射文件
        需要遵守一些规范：需要将mapper接口和mapper.xmlwen文件名字保持一致，并且在一个目录中
        前提是要使用mapper代理的方式
         -->
        <!-- <mapper class="mapper.UserMapper"/> -->
        <!-- 批量加载多个文件 
        name要写指定mapper接口的包名，mybaties会自动扫描包名下的mapper接口进行加载
        规范是：mapper接口的包名和mapper.xml文件名字相同，且在同一个目录下
        前提是要使用mapper代理的方式（常用）
        -->
        <package name="mapper"/>

    </mappers>

parameterType和resultType类型

1， 输入参数parametertype使用包装类，进行综合信息查询

UserMapper.xml

<!-- 用户的综合信息查询 -->  
<select id="findUserByList" parameterType="model.UserMapVo" resultType="model.UserCustom">
        SELECT * FROM tp_user1 WHERE username LIKE '%${userCustom.username}%' AND  
        PASSWORD LIKE '%${userCustom.password}%'
        <!-- 这里的列名必须和pojo中映射的属性值相同，如果有一个相同对象会被创建，如果没有一个相同对象
        将不会被创建 -->
        <!-- SELECT id ,username username_ ,password FROM tp_user1 WHERE username LIKE '%${userCustom.username}%' AND PASSWORD LIKE '%${userCustom.password}%' -->
</select>

UserMapper.java接口

//用户信息综合查询
public List<UserCustom> findUserByList(UserMapVo userMapVo)throws Exception;

测试方法

@Test
public void testFindUserByList() throws Exception{

    SqlSession  sqlSession=sqlSessionFactory.openSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    UserMapVo userMapVo=new UserMapVo();
    UserCustom userCustom=new UserCustom();
    userCustom.setUsername("夏东");
    userCustom.setPassword("123");
    userMapVo.setUserCustom(userCustom);    
    List<UserCustom> userList=mapper.findUserByList(userMapVo);
    System.out.println(userList);
    sqlSession.close();
}

2，输入参数parametertype使用hashMap，进行综合信息查询

UserMapper.xml

<!-- 综合信息查询之hashmap -->
<select id="findUserByHashMap" parameterType="hashMap" resultType="user">
    SELECT id,username ,password FROM tp_user1 WHERE username LIKE '%${username}%' 
    AND PASSWORD LIKE '%${password}%'
</select>

UserMapper.java

//用户综合信息查询之haspMap
public List<User> findUserByHashMap(HashMap<String, String> hashMap) throws Exception;

test.java

@Test
public void testFindUserByHashMap() throws Exception{
    SqlSession  sqlSession=sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    HashMap<String, String> hashMap=new HashMap<String, String>();
    hashMap.put("username", "夏东");
    hashMap.put("password", "123");
    List<User> userList=mapper.findUserByHashMap(hashMap);
    System.out.println(userList);
    sqlSession.close();
}

3， 输出结果映射resultType输出类型

• 简单类型和pojo对象或者是list

  UserMapper.xml

<!-- 用户的综合信息查询 -->  
    <select id="findUserByList" parameterType="model.UserMapVo" 
    resultType="model.UserCustom">
        *<!-- 这里的列名必须和pojo中映射的属性值相同，如果有一个相同对象会被创建，如果没有一个相同对象
        将不会被创建 -->*
        SELECT id ,username username_ ,password FROM tp_user1 WHERE username LIKE '%${userCustom.username}%' AND PASSWORD LIKE '%${userCustom.password}%'
</select>

<!-- 用户综合信息查询总数 查询结果是一行且是一列的时候才能使用简单类型进行映射-->
<!-- 输出结果可以是pojo对象也可以是List，不同点在于UserMapper.java接口的返回值类型不同，根据返回值的类型不同，动态代理内部自动调用selectone或selectList -->
<select id="findUserCount" parameterType="model.UserMapVo" resultType="int">
     SELECT count(*) FROM tp_user1 WHERE username LIKE '%${userCustom.username}%' AND 
     PASSWORD LIKE '%${userCustom.password}%'  
</select>

UserMap.java

//查询用户总数    
public int findUserCount(UserMapVo userMapVo) throws Exception;

test.java

@Test
public void testFindUserCount() throws Exception{
    SqlSession  sqlSession=sqlSessionFactory.openSession(); 
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    UserMapVo userMapVo=new UserMapVo();
    UserCustom userCustom=new UserCustom();
    userCustom.setUsername("夏东");
    userCustom.setPassword("123");
    userMapVo.setUserCustom(userCustom);
    int count=mapper.findUserCount(userMapVo);
    System.out.println(count);  
    sqlSession.close();
}

3，输出参数是hashMap
xml

<!-- 输出类型是hashMap -->
    <select id="findUserByResultHashMap" parameterType="model.UserMapVo" 
    resultType="hashMap">
    SELECT * FROM tp_user1 WHERE username LIKE '%${userCustom.username}%' AND 
    PASSWORD LIKE '%${userCustom.password}%'
</select>

接口

//查询输出类型是hashmap
public List<HashMap<String, String>> findUserByResultHashMap(UserMapVo userMapVo) throws Exception;

测试

@Test
public void testFindUserResultHashMap() throws Exception{   
    SqlSession  sqlSession=sqlSessionFactory.openSession();     
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    UserMapVo userMapVo=new UserMapVo();
    UserCustom userCustom=new UserCustom();
    userCustom.setUsername("夏东");
    userCustom.setPassword("123");
    userMapVo.setUserCustom(userCustom);
    List<HashMap<String, String>> userList=mapper.findUserByResultHashMap(userMapVo);       
    System.out.println(userList);       
    sqlSession.close();
}

4，输出结果resultMap映射

xml

<!-- 返回结果类型user -->
<resultMap type="user" id="userResultMap">
<!-- id是唯一标识列,查询出的列名和user类的属性的映射关系 -->
<id column="id_" property="id"/>
<!-- result是普通列,查询出的列名和user类的属性的映射关系  -->
<result column="username_" property="username"/>    
</resultMap>

<!-- 输出映射使用resultmMap  总结：resultType只有查询到的列名和类型属性名相同才能映射成功，resultMap如果查询的列名和属性名不同可以定义resultMap做一个转换-->
<select id="findUserByResultMap" resultMap="userResultMap" 
    parameterType="java.lang.String">
    select id id_,username username_ ,password FROM tp_user1 where username like     '%${value}%'
</select>

java接口类

//查询用户信息，输出类型使用resultMap
public List<User> findUserByResultMap(String name) throws Exception;

test测试

@Test
public void testFindUserResultMap() throws Exception{   
    SqlSession  sqlSession=sqlSessionFactory.openSession();     
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> userList=mapper.findUserByResultMap("夏东");
    System.out.println(userList);
    sqlSession.close();
}

动态sql

1，

<select id="findUserByList" parameterType="model.UserMapVo" 
resultType="model.UserCustom">
    SELECT * FROM tp_user1
    <!-- where标签会自动过滤前面的“and” -->
        <where>
            <if test="userCustom!=null">
                <!--当符合下面条件时，条件将不会被拼接到where语句后面-->
                <if test="userCustom.username!=null and userCustom.username!=''">
                    and username LIKE '%${userCustom.username}%'
                </if>

                <if test="userCustom.password!=null and userCustom.password!=''">
                    and password LIKE '%${userCustom.password}%'
                </if>
            </if>
        </where>
</select>

test

@Test
public void testFindUserByList() throws Exception{
    SqlSession  sqlSession=sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    UserMapVo userMapVo=new UserMapVo();
    UserCustom userCustom=new UserCustom();
//  userCustom.setUsername("夏东");
    userCustom.setPassword("123");
    userMapVo.setUserCustom(userCustom);
    List<UserCustom> userList=mapper.findUserByList(userMapVo);
    System.out.println(userList);
    sqlSession.close();
}

2，代码片段的抽取

xml

<!-- 定义sql片段，可以重复引用 -->
<sql id="find_user_where">
<!-- 这里不需要where，因为引用的时候我们需要引用多个 -->
    <if test="userCustom!=null">
        <if test="userCustom.username!=null and userCustom.username!=''">
                and username LIKE '%${userCustom.username}%'
            </if>

            <if test="userCustom.password!=null and userCustom.password!=''">
               and password LIKE '%${userCustom.password}%'
            </if>
        </if>
    </sql>
    <!-- 引用 -->
<select id="findUserByList" parameterType="model.UserMapVo" resultType="model.UserCustom">
SELECT * FROM tp_user1
    <where>
        <!-- 如果代码片段不在同一个文件中，要加上namespace -->
        <!-- 如果写成</include>会报错 -->
        <include refid="find_user_where"/>
    </where>
</select>       

test

@Test
public void testFindUserByList() throws Exception{
    SqlSession  sqlSession=sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    UserMapVo userMapVo=new UserMapVo();
    UserCustom userCustom=new UserCustom();
    userCustom.setUsername("夏东");
    userCustom.setPassword("123");
    userMapVo.setUserCustom(userCustom);
    List<UserCustom> userList=mapper.findUserByList(userMapVo);
    System.out.println(userList);
    sqlSession.close();
}

3，foreach 传入多个参数

<sql id="find_user_where">
<!-- 这里不需要where，因为引用的时候我们需要引用多个 -->
        <if test="userCustom!=null">
            <if test="userCustom.username!=null and userCustom.username!=''">
                and username LIKE '%${userCustom.username}%'
            </if>

            <if test="userCustom.password!=null and userCustom.password!=''">
                and password LIKE '%${userCustom.password}%'
            </if>
        </if>
        <!-- collection是定义的集合，item是每个参数接受，open是拼接的开始位置，close是结束的标识，separator是中间的分割符号 -->
<!--    <foreach collection="ids" item="items_id" open="AND id IN (" close=")" separator=",">
    #{items_id} 
</foreach>-->
 <foreach collection="ids" item="id" open="AND (" close=")" separator="or">
    id=#{id}
  </foreach>            
</sql>

test测试代码

    //综合信息查询
    @Test
    public void testFindUserByList() throws Exception{

        SqlSession  sqlSession=sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        UserMapVo userMapVo=new UserMapVo();
        UserCustom userCustom=new UserCustom();
        userCustom.setUsername("夏东");
        userCustom.setPassword("123");
        List<Integer> ids=new ArrayList<Integer>();
        ids.add(11);
        ids.add(13);
        ids.add(15);
        userMapVo.setIds(ids);
        userMapVo.setUserCustom(userCustom);
        List<UserCustom> userList=mapper.findUserByList(userMapVo);
        System.out.println(userList);
        sqlSession.close();
    }