动态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 ListselectUserListByids(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:UsersDELLDesktop编程javaeeworkWebContentWEB-INFlibmysql-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
小心生成系统中的数据库 如uesr表 country表冲突
导致搜索失败！！！！
记得加 www.
https://blog.csdn.net/ITBigGod/article/details/82691295