Mybatis中的Mapper映射Sql中传参类型(如Bean,基本类型,List...)与取值的方式 与foreach的几种用法!!

附加:mybatis配置文件

Mybatis中的Mapper映射Sql中传参类型(如Bean,基本类型,List...)与取值的方式 与foreach的几种用法!!
<?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>

    <!-- 加载Src下的配置文件   -->
    <properties resource="db.properties"/>
    <!--别名机制-->
    <typeAliases>
        <typeAlias type="com.cn.entity.User" alias="user"/>
    </typeAliases>

    <!-- 连接数据库   -->
    <environments default="mysql_development">

        <environment id="mysql_development">
            <transactionManager type="JDBC"/>

            <dataSource type="POOLED">
                <property name="driver" value="${driverClass}"/>
                <property name="url" value="${jdbcUrl}"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>


        <!--  连接Oracle数据库  -->
        <environment id="oracle_development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
                <property name="url" value="jdbc:oracle:thin:localhost:1521:orcl"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>


</environments>

    <!-- 加载映射文件   -->
    <mappers>
        <mapper resource="com/cn/entity/mybatisUserMapper.xml"/>
    </mappers>
    
</configuration>
View Code

附加:测试文件

Mybatis中的Mapper映射Sql中传参类型(如Bean,基本类型,List...)与取值的方式 与foreach的几种用法!!
package com.cn.test;
import com.cn.entity.User;
import com.cn.utils.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.Date;
import java.util.List;

public class TestApp {
    public static void main(String[] args) {
        try {
            /*
                加载资源的一种方式:
                InputStream in = Resources.getResourceAsStream("mybatis.xml");
                //创建SqlSessionFactory
                SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
                SqlSession sqlSession = sqlSessionFactory.openSession();
                System.out.println(sqlSession);
            */
            SqlSession sqlSession = MyBatisUtil.getSqlSession();
            User u = new User();
            u.setId(22);
            u.setAddress("湖南");
            u.setBirthday(new Date());
            u.setSex("女");
            u.setUsername("No");
            sqlSession.insert("com.cn.entity.User.add",u);

            List<User> list = sqlSession.selectList("com.cn.entity.User.findAll");
            for(User u1 :list){
                System.out.println(u1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
/*
* User{id=1, username=‘zhangsan‘, birthday=Mon Feb 05 00:00:00 CST 2018, sex=‘男‘, address=‘湖南‘}
User{id=2, username=‘rose‘, birthday=Thu Mar 08 00:00:00 CST 2018, sex=‘女‘, address=‘东莞‘}
User{id=3, username=‘black‘, birthday=Fri Feb 19 00:00:00 CST 2021, sex=‘男‘, address=‘深圳‘}
User{id=4, username=‘pink_girl‘, birthday=Sat Aug 24 00:00:00 CST 2019, sex=‘女‘, address=‘changsan‘}
User{id=8, username=‘heheh‘, birthday=Sat Aug 24 00:00:00 CST 2019, sex=‘女‘, address=‘huana‘}
User{id=22, username=‘No‘, birthday=Thu Jun 04 00:00:00 CST 2020, sex=‘女‘, address=‘湖南‘}*/
View Code

 

1 映射文件中传递的参数是 对象类型:取值的方式如下

<?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.cn.entity.User" >

<insert id="add" parameterType="user"> <!--传递的是一个对象类型,取值时直接写属性即可--> insert into user(id,username,birthday,sex,address) values (#{id},#{username},#{birthday},#{sex},#{address}); </insert> </mapper>

测试代码:

            SqlSession sqlSession = MyBatisUtil.getSqlSession();
            User u = new User();
            u.setId(22);
            u.setAddress("湖南");
            u.setBirthday(new Date());
            u.setSex("女");
            u.setUsername("No");
            sqlSession.insert("com.cn.entity.User.add",u);
            sqlSession.commit();

 

 

 


2 映射文件中传递的参数是 基本数据类型:取值的方式如下  , {}里面的变量名可以随意写

    <!--查询根据id-->
    <select id="findbyid"  parameterType="int" resultType="user">
        select * from user where id = #{id};
    </select>

测试代码

User u = sqlSession.selectOne("com.cn.entity.User.findbyid", 8);

 

 

 

 

3 映射文件中传递的参数是 Map:取值的方式如下  

 Map<String ,Object> map = new HashMap<String,Object>();
 map.put("keyname","1");
 map.put("key1","2");
User uu
= sqlSession.selectOne("com.cn.entity.User.findbymap",map);
    <!--  传入map时直接取map的key的名字即可  -->
    <select id="findbymap" parameterType="map" resultType="user">
          select * from user where id = #{keyname};
    </select>

 

 

 

 

 

 

 

 

4 映射文件中传递的参数是 List:取值的方式如下  

XML语句:

<select id="findByRidAndType" resultMap="entryResource" parameterType="list">
        SELECT
        *
        FROM
        t_entry_resource a
        WHERE
<foreach collection="list" index="index" item="entryResources" open="(" close=")" separator="or">
        <!--拼接的sql语句 相当与 select * from t_entry_resource where type = ? and resource_id = ? -->
( `type`=#{entryResources.type} and resource_id=#{entryResources.resourceId} ) </foreach> </select>

Mybatis中的Mapper映射Sql中传参类型(如Bean,基本类型,List...)与取值的方式 与foreach的几种用法!!

 

 

表结构

MySQL [test]> select * from t_entry_resource;
+----+-------------+------+----------+--------+--------+---------------------+
| id | resource_id | type | title    | banner | icon  | add_date            |
+----+-------------+------+----------+--------+--------+---------------------+
| 11 |          6  | 14   | 分类     | 1.jpg  | 2.jpg  | 2017-11-17 11:22:30 |
| 12 |          3  | 1    | 测试12   | 3.jpg  | 4.jpg  | 2017-11-17 11:22:30 |
| 13 |        653  | 1    | 测试34   | 5.jpg  | 6.jpg  | 2017-11-20 02:32:26 |
| 14 |          1  | 1    | 测试5    | 7.jpg  | 8.jpg  | 2017-11-20 02:32:51 |
| 15 |        3942 | 3    | 测试6    | 9.jpg  | 10.jpg | 2017-11-20 02:34:27 |
+----+-------------+------+----------+--------+--------+---------------------+

 

4. foreach的几种用法

list集合

(1) select count(*) from users id in (x1,x2,x3,...)
<select id="countByUserList" resultType="int" parameterType="list">    
select count(*) from users    
  <where>    
    id in    
    <foreach item="item" collection="list" separator="," open="(" close=")" index="">    
      #{item.id, jdbcType=NUMERIC}    
    </foreach>    
  </where>    
</select> 

 

(2) select count(*) from key_cols where col_a = ? AND col_b = ?

<select id="sel_key_cols" resultType="int">    
        select count(*) from key_cols where    
<foreach item="item" index="key" collection="map"  open="" separator="AND" close="">
        ${key} = #{item}
</foreach>    
</select>  

 

list集合中的对象

(3) select * from t_news n where n.tags like ? or n.tags like ?

<select id="selectTestForEach" parameterType="News" resultMap="NewsResultMapper">
  select * from t_news n where 
  <foreach collection="listTag" index="index" item="tag" open="" separator="or" close="">
            n.tags like  ‘%‘||#{tag}||‘%‘
  </foreach>
<select>

 

参考文献:

https://www.cnblogs.com/coderzhw/p/11094300.html

https://www.cnblogs.com/dflmg/p/6398033.html

http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html

 

Mybatis中的Mapper映射Sql中传参类型(如Bean,基本类型,List...)与取值的方式 与foreach的几种用法!!

上一篇:No mapping found for HTTP request with URI xxx/resources/js/jquery.min.js...


下一篇:Android取证工具