mybaties+Spring mvc学习之路笔记

1.入门程序

1，链接mysql数据库

SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configurationPUBLIC "-//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 mapperPUBLIC "-//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插入查询

@Testpublic 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;@Beforepublic void setUp() throws Exception {    String resource = "SqlMapConfig.xml";    InputStream inputStream = Resources.getResourceAsStream(resource);    sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);}@Testpublic 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.csdn.net/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;

测试方法

@Testpublic 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

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

test.java

@Testpublic 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

@Testpublic 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>

接口

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

测试

@Testpublic 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接口类

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

test测试

@Testpublic 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

@Testpublic 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

@Testpublic 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();    }