【mybatis】学习笔记 3动态语句 foreach generator使用【小心生成系统中的数据库如uesr表 country表】...

最新推荐文章于 2023-09-14 15:32:14 发布

aoduanrou3097

最新推荐文章于 2023-09-14 15:32:14 发布

阅读量201

点赞数

文章标签： java 数据库

原文链接：http://www.cnblogs.com/cznczai/p/11157673.html

版权

动态sql入门：

小心mysql没有打开！！！这坑我一个晚上

代码

UserMapper.java

package com.mapper;
import java.util.List;
 
import com.bean.User;
public interface UserMapper {
    public  List<User> selectUserListByUser(User user);
}

xml文件：这是没有加if 的情况

<?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.mapper.UserMapper">
 
    <select id="selectUserListByUser" parameterType="User" resultType="com.bean.User">
    select * from user where u_cid = #{u_cid} and u_sex = #{u_sex} and u_username like "%"#{u_username}"%"
    
    </select>
</mapper>

升级

<?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.mapper.UserMapper">
 
    <select id="selectUserListByUser" parameterType="User" resultType="com.bean.User">
    select * from user where 
    <if test="u_cid!=null and u_cid!=''">
    u_cid = #{u_cid} 
    </if>
    <if test="u_sex!=null and u_sex!=''">
    and u_sex = #{u_sex}
    </if>
    <if test="u_username!=null and u_username!=''">
    and u_username like "%"#{u_username}"%"
    </if>
    </select>
</mapper>

Test:

@Test
    public void test1() {// ctrl shift o
        User u = new User();
        u.setU_username("王");
        u.setU_cid(1);
        u.setU_sex("1");
        List<User> selectAll = mapper.selectUserListByUser(u);
        for (User user : selectAll) {
            System.out.println(user);
        }
    }

第二步加入where 标签会删去开头的 and 但不能删去结尾的 and

<mapper namespace="com.mapper.UserMapper">
 
    <select id="selectUserListByUser" parameterType="User" resultType="com.bean.User">
    select * from user 
    <where >
    <if test="u_cid!=null and u_cid!=''">
    u_cid = #{u_cid} 
    </if>
    <if test="u_sex!=null and u_sex!=''">
    and u_sex = #{u_sex}
    </if>
    <if test="u_username!=null and u_username!=''">
    and u_username like "%"#{u_username}"%"
    </if>
    </where>
    </select>
</mapper>

以及trim 版本 overrides 删除那个标签！！！ prefix 代替作用

<mapper namespace="com.mapper.UserMapper">
 
    <select id="selectUserListByUser" parameterType="User" resultType="com.bean.User">
    select * from user 
    <trim prefix="where" suffixOverrides="and">
    <if test="u_cid!=null and u_cid!=''">
    u_cid = #{u_cid} and
    </if>
    <if test="u_sex!=null and u_sex!=''">
     u_sex = #{u_sex} and
    </if>
    <if test="u_username!=null and u_username!=''">
     u_username like "%"#{u_username}"%" and
    </if>
    </trim>
    </select>
</mapper>

where跟 trim 的作用是避开 where 因为一个语句为空直接连接and 而报错的情况

Update操作
set 标签处理拼接时出现（，where）这种情况所以采用set标签

<?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.mapper.UserMapper">
 
    <update id="updataSet" parameterType="User">
    update user 
        <set>
            <if test="u_username!='' and u_username !=null">
            u_username = #{u_username},
            </if>
            <if test="u_password!='' and u_password !=null">
            u_password = #{u_password},
            </if>
            <if test="u_cid!='' and u_cid !=null">
            u_cid = #{u_cid}
            </if>
        </set>
        where u_id = #{u_id}
    </update>
</mapper>

@Test
    public void test1() {// ctrl shift o
        User u = new User();
        u.setU_id(5);
        u.setU_username("王");
        u.setU_cid(1);
        u.setU_password("aaa");
        
        mapper.updataSet(u);
        ss.commit();//记得提交事务
        
    }

public void updataSet(User user);
Foreach in（1，3，5）就需要这种
public List selectUserListByids(Integer[] ids);

@Test
    public void test1() {// ctrl shift o
        Integer arr[] = {1,3,5,6};
        List<User> selectAll = mapper.selectUserListByids(arr);
        for (User user : selectAll) {
            System.out.println(user);
        }
    }

item！！！ open ！！！ close ！！！ separator！！！注意collection是小写！！！

<?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.mapper.UserMapper">
<select id="selectUserListByids" resultType="User">
select * from user where u_id in
    <foreach collection="array" item="id" open="(" close=")" separator=",">
    #{id}
    </foreach>
</select>
</mapper>

ArrayList

public  List<User> selectUserListByids(List i);
@Test
    public void test1() {// ctrl shift o
        List<Integer> i = new ArrayList<Integer>();
        i.add(1);
        i.add(1);
        List<User> selectAll = mapper.selectUserListByids(i);
        for (User user : selectAll) {
            System.out.println(user);
        }
    }

<?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.mapper.UserMapper">
<select id="selectUserListByids" resultType="User">
select * from user where u_id in
    <foreach collection="list" item="id" open="(" close=")" separator=",">
    #{id}
    </foreach>
</select>
</mapper>

封装类进行查询

@Test
    public void test1() {// ctrl shift o
    
        UserVo uservo = new UserVo();
        List<Integer> l = new ArrayList<Integer>();
        l.add(1);
        l.add(2);
        uservo.setL(l );
        List<User> selectAll = mapper.selectUserListByUserVo(uservo);
        for (User user : selectAll) {
            System.out.println(user);
        }
    }

<?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.mapper.UserMapper">
<select id="selectUserListByUserVo" parameterType="UserVo" resultType="User">
    select * from user where u_id in 
    <foreach collection="l" item="id" open="(" close=")" separator="," >
    #{id}
    </foreach>
 
</select>
</mapper>

public List selectUserListByUserVo(UserVo uservo);

package com.bean;
 
import java.util.List;
 
public class UserVo extends User{
    private List<Integer> l;
 
    public List<Integer> getL() {
        return l;
    }
 
    public void setL(List<Integer> l) {
        this.l = l;
    }
 
    @Override
    public String toString() {
        return "UserVo [l=" + l + "]";
    }
}

SQL标签解决重复片段如select * from....

<?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.mapper.UserMapper">
<sql id="select">
select * from user
</sql>
<select id="selectUserListByUserVo" parameterType="UserVo" resultType="User">
    <include refid="select"/>
     where u_id in 
    <foreach collection="l" item="id" open="(" close=")" separator="," >
    #{id}
    </foreach>
</select>
</mapper>

Generator

github地址 http://www.mybatis.org/generator/configreference/commentGenerator.html
自动生成bean类 xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration  PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"  "http://www.mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>  
<!--配置数据库连接的包  配置驱动 --> 
 
<classPathEntry location="C:\Users\DELL\Desktop\编程\javaee\work\WebContent\WEB-INF\lib\mysql-connector-java-8.0.15.jar" />   
    <context id="MyGenerator" targetRuntime="MyBatis3">  
    
    <!-- 去掉生成文件中的注释 -->
        <commentGenerator>
            <property name="suppressAllComments" value="true" />
            <property name="suppressDate" value="true" />
        </commentGenerator>
        
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
            connectionURL="jdbc:mysql://localhost:3306/ssm_mybatis?serverTimezone=UTC"
            userId="root" 
            password="88888888">
        </jdbcConnection>     
           
        <!-- java 数据类型转换 -->
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false" />
        </javaTypeResolver>        
        
        <!-- javabean配置 targetPackage 输入包名 targetProject 输出路径 -->
        <javaModelGenerator targetPackage="com.bean"
            targetProject="src">    
            <!--enableSubPackages是否开启子包名字 ，是否在包名后边加上scheme名称 -->
            <property name="enableSubPackages" value="false" />      
            <!--在set方法中 去掉空格 -->
            <property name="trimStrings" value="true" />
        </javaModelGenerator> <!-- mapper.xml配置 -->
        
        <sqlMapGenerator targetPackage="com.mapper"
            targetProject="src">
            <property name="enableSubPackages" value="false" />
        </sqlMapGenerator>
        <!-- java接口的路径 -->
        
        <javaClientGenerator type="XMLMAPPER"
            targetPackage="com.mapper"
            targetProject="src">
            <property name="enableSubPackages" value="true" />
        </javaClientGenerator>
         
        <!-- 数据库中的表 -->
        <!-- 数据库名 + 表名 + 导入名称 -->
        <table schema="ssm_mybatis" tableName="user" domainObjectName="user"/>
        <table schema="ssm_mybatis" tableName="country1" domainObjectName="country1" /> 
        
    </context>
</generatorConfiguration>

java

package com.test;
 
import java.io.File;
import java.util.ArrayList;
import java.util.List;
 
import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;
 
public class Generator {
    public static void main(String[] args) throws Exception {
 
        List<String> warnings = new ArrayList<String>();
        boolean overwrite = true;
        File configFile = new File("src/generatorConfig.xml");
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(configFile);
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
    }
}

部分代码自己生成！！！

package com.test;
 
import java.io.IOException;
import java.io.InputStream;
 
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.Test;
 
import com.mapper.country1Mapper;
 
 
public class mybatisTest {
    private static SqlSessionFactory ssf;
    static {
        try {
            InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
            SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
            ssf = ssfb.build(in);
            in.close();
 
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    private static SqlSession ss = ssf.openSession();
    private static country1Mapper mapper = ss.getMapper(country1Mapper.class);
 
    @Test
    public void test1() {
        mapper.selectByPrimaryKey(1);
    }
    }

https://blog.csdn.net/chszs/article/details/8125828
https://blog.csdn.net/a15920804969/article/details/79107852
https://blog.csdn.net/dear_alice_moon/article/details/73208116