Mybatis+mysql动态分页查询数据案例——配置映射文件(HouseDaoMapper.xml)
2023-09-14 08:57:40 时间
<?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="houseDao" > <resultMap id="BaseResultMap" type="house" > <id column="ID" property="id" jdbcType="INTEGER" /> <result column="TITLE" property="title" jdbcType="VARCHAR" /> <result column="DESCRIPTION" property="description" jdbcType="VARCHAR" /> <result column="PRICE" property="price" jdbcType="REAL" /> <result column="PUBDATE" property="pubdate" jdbcType="DATE" /> <result column="FLOORAGE" property="floorage" jdbcType="INTEGER" /> <result column="CONTACT" property="contact" jdbcType="VARCHAR" /> <!-- 开始映射外键 --> <!-- 映射用户表 --> <association property="users" column="user_id" select="selectUsers"/> <!-- 映射类型表 --> <association property="types" column="type_id" select="selectTypes"/> <!-- 映射街道表 --> <association property="street" column="street_id" select="selectStreet"/> </resultMap> <!-- 关联用户表 --> <resultMap id="usersMapper" type="users" > <id column="ID" property="id" jdbcType="INTEGER" /> <result column="NAME" property="name" jdbcType="VARCHAR" /> <result column="PASSWORD" property="password" jdbcType="VARCHAR" /> <result column="TELEPHONE" property="telephone" jdbcType="VARCHAR" /> <result column="USERNAME" property="username" jdbcType="VARCHAR" /> <result column="ISADMIN" property="isadmin" jdbcType="VARCHAR" /> </resultMap> <!-- 关联街道表 --> <resultMap id="streetMapper" type="street" > <id column="ID" property="id" /> <result column="NAME" property="name" jdbcType="VARCHAR" /> <association property="district" column="district_id" select ="selectDirstrict"/> </resultMap> <!-- 关联区县表 --> <resultMap id="districtDaoMapper" type="district" > <id column="ID" property="id"/> <result column="NAME" property="name"/> </resultMap> <!-- 在根据区县id查询一遍区县表 --> <select id="selectDirstrict" resultMap="districtDaoMapper"> select * form district where id=#{district_id} </select> <!--关联类型表 --> <resultMap id="typeMapper" type="types" > <id column="ID" property="id"/> <result column="NAME" property="name" jdbcType="VARCHAR" /> </resultMap> <!-- 用户表 --> <select id="selectUsers" resultMap="usersMapper"> select * from users where id=#{user_id} </select> <!-- 街道表 --> <select id="selectStreet" resultMap="streetMapper"> select * from street where id=#{street_id} </select> <!-- 类型表 --> <select id="selectTypes" resultMap="typeMapper"> select * from types where id=#{type_id} </select> <sql id="Base_Column_List" > ID, USER_ID, TYPE_ID, TITLE, DESCRIPTION, PRICE, PUBDATE, FLOORAGE, CONTACT, STREET_ID </sql> <!--根据id查询房屋信息 --> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> from house where ID = #{id,jdbcType=INTEGER} </select> <!-- 根据id删除房屋信息 --> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > delete from house where ID = #{id,jdbcType=INTEGER} </delete> <!-- 添加房屋信息 --> <insert id="insert" parameterType="house" > insert into house ( USER_ID, TYPE_ID, TITLE, DESCRIPTION, PRICE, PUBDATE, FLOORAGE, CONTACT, STREET_ID ) values ( #{users.id,jdbcType=INTEGER}, #{types.id,jdbcType=INTEGER}, #{title,jdbcType=VARCHAR}, #{description,jdbcType=VARCHAR}, #{price,jdbcType=REAL}, #{pubdate,jdbcType=DATE}, #{floorage,jdbcType=INTEGER}, #{contact,jdbcType=VARCHAR}, #{street.id,jdbcType=INTEGER} ) </insert> <!-- 根据id修改房屋信息 --> <update id="updateByPrimaryKey" parameterType="house" > update house set USER_ID = #{users.id,jdbcType=INTEGER}, TYPE_ID = #{types.id,jdbcType=INTEGER}, TITLE = #{title,jdbcType=VARCHAR}, DESCRIPTION = #{description,jdbcType=VARCHAR}, PRICE = #{price,jdbcType=REAL}, PUBDATE = #{pubdate,jdbcType=DATE}, FLOORAGE = #{floorage,jdbcType=INTEGER}, CONTACT = #{contact,jdbcType=VARCHAR}, STREET_ID = #{street.id,jdbcType=INTEGER} where ID = #{id,jdbcType=INTEGER} </update> <!-- 动态查询房屋信息的总记录数 --> <select id="reCount" parameterType="houseC" resultType="Integer"> select count(0) from house h <where> <if test="priceBegin!=null"> and h.price > #{priceBegin} </if> <if test="priceEnd!=null"> and h.price <![CDATA[<]]> #{priceEnd} </if> <!-- h.street_id是数据库的字段名 --> <if test="street!=null"> and h.street_id = #{street.id} </if> <!-- h.type_id是数据库的字段名 --> <if test="types!=null"> and h.type_id = #{types.id} </if> <if test="floorageBegin!=null"> and h.floorage > #{floorageBegin} </if> <if test="floorageEnd!=null"> and h.floorage <![CDATA[<]]> #{floorageEnd} </if> </where> </select> <!-- 分页动态查询房屋信息 --> <select id="getHouseInfoByDymanic" parameterType="hashmap" resultMap="BaseResultMap"> select * from house h <where> <if test="priceBegin!=null"> and h.price > #{priceBegin} </if> <if test="priceEnd!=null"> and h.price <![CDATA[<]]> #{priceEnd} </if> <if test="street!=null"> and h.street_id = #{street.id} </if> <if test="types!=null||!types==null"> and h.type_id = #{types.id} </if> <if test="floorageBegin!=null"> and h.floorage > #{floorageBegin} </if> <if test="floorageEnd!=null"> and h.floorage <![CDATA[<]]> #{floorageEnd} </if> </where> limit #{stratRow},#{endRow} </select> <!-- 查询全部的房屋信息 --> <select id="getHouseInfo" resultType="house"> select * from house </select> <!-- 分页查询全部的房屋信息 --> <select id="getHousePage" parameterType="hashmap" resultMap="BaseResultMap"> select * from house limit #{startRow},#{endRow} </select> </mapper>
相关文章
- mysql 启动报错--发现系统错误2,系统找不到指定的文件。
- FlinkCDC读取MySQL并写入Kafka案例(com.alibaba.ververica)
- 【MySQL】批量删除mysql中数据库中的表
- 【MySql】mysql 字段个数的限制
- Mybatis+mysql动态分页查询数据案例——工具类(MybatisUtil.java)
- Mybatis+mysql动态分页查询数据案例——Mybatis的配置文件(mybatis-config.xml)
- Mybatis+mysql动态分页查询数据案例——配置映射文件(HouseDaoMapper.xml)
- mysql 内外链接
- Mysql 12 复制1
- 01 MySQL锁概述
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
- 大叔经验分享(50)hue访问mysql(librdbms)
- mysql 有报错 ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists
- MySQL · BUG分析 · Rename table 死锁分析
- mysql 重新整理——索引优化一个简单的案例 [十一]
- PHP连接MySQL数据库的三种方式(mysql、mysqli、pdo)
- Mysql 如果有多个可选条件怎么加索引_MySQL|mysql-索引
- Linux重置Mysql密码_解决MySQL for Linux错误 ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost
- Ansible roles角色实战案例:httpd nginx memcached mysql
- 【安装包】MySQL客户端
- error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
- 【项目实战】WebFlux整合r2dbc-mysql实战
- ACID_MySQL事务的四大特性详解(MySQL高频面试题)
- 【MySQL案例】HA: GTID_MODE配置不一致
- MySQL Study之--Mysql无法启动“mysql.host”
- linux下MySQL安装登录及操作
- 安全测试===Mysql 注入技巧学习 MySQL注入技巧(1)
- mysql对GIS空间数据的支持,包括创建空间索引
- Mysql报错:Can't connect to local MySQL server through socket '/tmp/mysql.sock'
- Mysql之修改mysql的视图定义者
- 【高可用MySQL解决方案】centos7配置mysql主从复制
- Mysql报错:Got fatal error 1236 from master when reading data from binary log: ‘Could not find first lo