动态sql片段 resultMap map多个参数 if拼接注解 where set 分页查询

最新推荐文章于 2024-08-26 20:09:59 发布

原创最新推荐文章于 2024-08-26 20:09:59 发布 · 1.6k 阅读

CC 4.0 BY-SA版权

本文介绍了如何在MyBatis中使用命名空间、resultMap、map参数、if标签、where标签、set标签进行动态SQL拼接，并展示了分页查询的实现。文章通过一个具体的例子，演示了从配置文件到DAO接口再到Mapper XML的全过程，包括实体类、接口声明、XML配置和测试类的编写。

2018年1月12日 23:08:17

要掌握在某类的配置文件写命名空间到dao包下类dao

要掌握在配置文件里取类型别名 resultMap别名

要掌握

resultType全查询

resultMap全查询

map多个参数查询

if拼接

注解

where

set

分页查询

模糊查询有个关键字'%',String,'%'

创建数据库的语句找不到类沾不了

项目WebRoot\WEB-INF\lib里粘贴四个包添加依赖

mysql-connector-java-5.1.0-bin.jar

mybatis-3.2.2.jar

mybatis-3.2.2-sources.jar

log4j-1.2.16.jar

然后src里粘贴

database.properties

mybatis-config.xml

log4j.properties

工具包src\com\bdqn\titls创建工具类返回sqlsession的session 以后要换成用单例模式

package com.bdqn.titls;

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;

public class SqlSessionUtils {
private static SqlSessionFactory sqlSessionFactory;
static{
try {

//加载配置文件获得流
InputStream ipsInputStream=Resources.getResourceAsStream("mybatis-config.xml");

//工厂通过new 工厂builder().build流获得
sqlSessionFactory=new SqlSessionFactoryBuilder().build(ipsInputStream);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static SqlSession getSession(){
//不自动提交事物 false 以后由程序员commit手动提交异常的话就回滚
SqlSession session=sqlSessionFactory.openSession(false);
return session;
}
public static void closeSession(SqlSession session){
if (session!=null) {
session.close();
}
}
}

实体包src\com\bdqn\entity建立实体类实现序列化接口各种标配方法

package com.bdqn.entity;

import java.io.Serializable;

public class User implements Serializable{
private int uId;
//private String uName;
private String uname;//如果这个类属性名和表名不一样
private String uPwd;
private String uPhone;
private String uAddress;
private String uBirthday;
public User() {
super();
}
// public User(int uId, String uName, String uPwd, String uPhone,
// String uAddress, String uBirthday) {
// super();
// this.uId = uId;
// this.uName = uName;
// this.uPwd = uPwd;
// this.uPhone = uPhone;
// this.uAddress = uAddress;
// this.uBirthday = uBirthday;
// }
// @Override
// public String toString() {
// return "User [uId=" + uId + ", uName=" + uName + ", uPwd=" + uPwd
// + ", uPhone=" + uPhone + ", uAddress=" + uAddress
// + ", uBirthday=" + uBirthday + "]";
// }
public int getuId() {
return uId;
}
public void setuId(int uId) {
this.uId = uId;
}
// public String getuName() {
// return uName;
// }
// public void setuName(String uName) {
// this.uName = uName;
// }
public String getuPwd() {
return uPwd;
}
public void setuPwd(String uPwd) {
this.uPwd = uPwd;
}
public String getuPhone() {
return uPhone;
}
public void setuPhone(String uPhone) {
this.uPhone = uPhone;
}
public String getuAddress() {
return uAddress;
}
public void setuAddress(String uAddress) {
this.uAddress = uAddress;
}
public String getuBirthday() {
return uBirthday;
}
public void setuBirthday(String uBirthday) {
this.uBirthday = uBirthday;
}

public User(int uId, String uname, String uPwd, String uPhone,
String uAddress, String uBirthday) {
super();
this.uId = uId;
this.uname = uname;
this.uPwd = uPwd;
this.uPhone = uPhone;
this.uAddress = uAddress;
this.uBirthday = uBirthday;
}
@Override
public String toString() {
return "User [uId=" + uId + ", uName=" + uname + ", uPwd=" + uPwd
+ ", uPhone=" + uPhone + ", uAddress=" + uAddress
+ ", uBirthday=" + uBirthday + "]";
}
public String getuname() {
return uname;
}
public void setuname(String uname) {
this.uname = uname;
}
}

src\com\bdqn\dao包粘贴Mapper.xml配置文件叫做UserMapper.xml

配置src下的配置文件取类型别名加载这个配置文件的时候顺带配置UserMapper.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>

<properties resource="database.properties"/>

<settings>
<setting name="logImpl" value="LOG4J" />
</settings>

<typeAliases>
<typeAlias type="com.bdqn.entity.User" alias="User"></typeAlias>
</typeAliases>

<environments default="development">
<environment id="development">

<transactionManager type="JDBC"></transactionManager>

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


<mappers>
<mapper resource="com/bdqn/dao/UserMapper.xml"/>
</mappers>
</configuration>

src\com\bdqn\dao的dao接口声明抽象方法

import com.bdqn.entity.User;

public interface UserDao {

//全查询
List<User> selectAll();

//用类型别名查
List<User> selectAllLeiBieMing();

//两个参数使用map
List<User> selectMap(Map<String, String> map);

//if拼接查询
List<User> selectIf(Map<String, String> map);

//注解查询表列名表列类型参数
List<User> selecctZhuShi(@Param("uPwd")String uPwd,@Param("uAddress")String uAddress);

//where
List<User> selecctWhere(@Param("uPwd")String uPwd,@Param("uAddress")String uAddress);

//set逗号
int updateUserSet(User user);

//分页查询
List<User> selectFenYe(@Param("PageIndex")Integer BPageIndex,@Param("PageSize")Integer BPageSize);
}

src\com\bdqn\dao包配置UserMapper.xml配置文件

<?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.bdqn.dao.UserDao">

<select id="selectAll" resultType="User">
select * from user
</select>

<resultMap type="User" id="MapUser">

<result property="uId" column="uId"/>
<result property="uname" column="uName"/>
<result property="uPwd" column="uPwd"/>
<result property="uPhone" column="uPhone"/>
<result property="uAddress" column="uAddress"/>
<result property="uBirthday" column="uBirthday"/>
</resultMap>


<select id="selectAllLeiBieMing" resultMap="MapUser">
select * from user
</select>

<select id="selectMap" resultMap="MapUser" parameterType="Map">
select * from user where uPwd=#{uPwd} and uAddress=#{uAddress}
</select>

<select id="selectIf" resultMap="MapUser" parameterType="Map">
select * from user where
<if test="uPwd!=null">
uPwd=#{uPwd}
</if>
<if test="uAddress!=null">

and uAddress=#{uAddress}
</if>
</select>


<select id="selecctZhuShi" resultMap="MapUser">
select * from user where
<if test="uPwd!=null">
uPwd=#{uPwd}
</if>
<if test="uAddress!=null">

and uAddress=#{uAddress}
</if>
</select>


<select id="selecctWhere" resultMap="MapUser">
select * from user <where>
<if test="uPwd!=null">
and uPwd=#{uPwd}
</if>
<if test="uAddress!=null">

and uAddress=#{uAddress}
</if>
</where>
</select>

<update id="updateUserSet" parameterType="User">
update user <set>

<if test="uPwd!=null">uPwd=#{uPwd},</if>
<if test="uPhone!=null">uPhone=#{uPhone},</if>
<if test="uBirthday!=null">uBirthday=#{uBirthday},</if>
</set>
<where>
uId=#{uId}
</where>
</update>

<select id="selectFenYe" resultMap="MapUser">
select * from user limit #{PageIndex},#{PageSize}
</select>
</mapper>

src\com\bdqn\test测试类做测试

package com.bdqn.test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import com.bdqn.dao.UserDao;
import com.bdqn.entity.User;
import com.bdqn.titls.SqlSessionUtils;

public class Test {
public static void main(String[] args) {
//原来的全查询
// List<User> list=SqlSessionUtils.getSession().getMapper(UserDao.class).selectAll();
// for (User user : list) {
// System.out.println(user.toString());
// }
//这个方法报错是可能是因为表列名和

//类型别名以后的查询
// List<User> list=SqlSessionUtils.getSession().getMapper(UserDao.class).selectAllLeiBieMing();
// for (User user : list) {
// System.out.println(user.toString());
// }

//map多个参数查询
// Map<String, String> map=new HashMap<String, String>();
// map.put("uPwd", "456");
// map.put("uAddress", "guangzhoubaiyun");
// List<User> list=SqlSessionUtils.getSession().getMapper(UserDao.class).selectMap(map);
// for (User user : list) {
// System.out.println(user.toString());
// }

//if拼接
// Map<String, String> map=new HashMap<String, String>();
// map.put("uPwd", "987");
// map.put("uAddress", "guangzhoubeihai");
// List<User> list=SqlSessionUtils.getSession().getMapper(UserDao.class).selectIf(map);
// for (User user : list) {
// System.out.println(user.toString());
// }

//注解注意参数一定要符合dao的类型参数参数
// List<User> list=SqlSessionUtils.getSession().getMapper(UserDao.class).selecctZhuShi("678","guangzhouzhujiang");
// for (User user : list) {
// System.out.println(user.toString());
// }

//where 注意参数一定要符合dao的类型参数参数
// List<User> list=SqlSessionUtils.getSession().getMapper(UserDao.class).selecctZhuShi("321","guangzhounanning");
// for (User user : list) {
// System.out.println(user.toString());
// }

//set
// SqlSession session=SqlSessionUtils.getSession();
// try {
// User user=new User();
// user.setuId(7);
// user.setuPwd("666");
// user.setuPhone("13799999999");
// user.setuBirthday("1996-10-10");
// int num=session.getMapper(UserDao.class).updateUserSet(user);
// session.commit();
// System.out.println(num);
// } catch (Exception e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// session.rollback();
// }

//分页查询
List<User> list=SqlSessionUtils.getSession().getMapper(UserDao.class).selectFenYe(2, 4);
for (User user : list) {
System.out.println(user.toString());
}
}
}

动态sql片段 resultMap map多个参数 if拼接 注解 where set 分页查询

动态sql片段 resultMap map多个参数 if拼接注解 where set 分页查询