基于mapper
- spring.xml配置
<!-- sqlSessonFactory的配置 -->
<bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 配置数据库连接池 -->
<property name="dataSource" ref="datasource"></property>
<!-- 它表示我们的Mapper文件存放的位置，当我们的Mapper文件跟对应的Mapper接口处于同一位置的时候可以不用指定该属性的值。 -->
<property name="mapperLocations" value="classpath:com/ws/mapper/*Mapper.xml" />
</bean>
<!-- 配置扫描包，加载mapper代理对象 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.ws.dao"></property>
<property name="sqlSessionFactory" ref="sqlSessionFactoryBean"></property>
</bean>
- 基础的CURD
<?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">
<!-- 扫描loginDao类 -->
<mapper namespace="com.ws.dao.CustomDao">
<!-- id dao层方法名 -->
<!-- resultType 结果集 -->
<!-- parameterType 参数类型 -->
<!-- 分页查询 -->
<select id="findAllCustom" parameterType="com.ws.bean.Num" resultType="com.ws.bean.Mycus">
select * from mycus limit #{current},#{length}
<!--注意：结果字段名与属性名保持一致，区分大小写-->
</select>
<!-- 分组 -->
<select id="count" resultType="Integer">
select count(*) from mycus
<!-- 注意：结果字段名与属性名保持一致，区分大小写 -->
</select>
<!--添加 -->
<insert id="addCus" parameterType="com.ws.bean.Mycus">
insert into mycus values(null,#{cname},#{csex},#{ctel},#{ctel1},#{ccard})
</insert>
<!-- 通过id查找 -->
<select id="findCusById" parameterType="java.lang.String" resultType="com.ws.bean.Mycus">
select * from mycus where cid=#{cid}
</select>
<update id="uppCus" parameterType="com.ws.bean.Mycus" >
update mycus set cname=#{cname},csex=#{csex},ctel=#{ctel},ctel1=#{ctel1},ccard=#{ccard} where cid=#{cid}
</update>
<select id="findAllCus" resultType="com.ws.bean.Mycus">
select * from mycus
</select>
<delete id="delete" parameterType="int">
delete from t_user where user_id=#{id}
</delete>
</mapper>
- 添加自增主键
<insert id="addDj" parameterType="com.ws.bean.Mydj" useGeneratedKeys="true" >
<!--selectKey 会将 SELECT LAST_INSERT_ID()的结果放入到传入的mydj的主键里面，
keyColumn 对应的表中的主键的属性名，这里是 mydj 中的mid，因为它跟数据库的主键对应
order AFTER 表示 SELECT LAST_INSERT_ID() 在insert执行之后执行,多用与自增主键，
BEFORE 表示 SELECT LAST_INSERT_ID() 在insert执行之前执行，这样的话就拿不到主键了，这种适合那种主键不是自增的类型
resultType 主键类型 -->
<selectKey keyColumn="mid" keyProperty="mid" order="AFTER" resultType="java.lang.Integer">
SELECT LAST_INSERT_ID()
</selectKey>
insert into mydj values(null,#{mdate},#{eid},#{cid},#{hid},#{mimg},#{myj},#{myzj},#{mflag},#{mbegintime});
</insert>
- 一对多查询
<select id="findDjByMid" resultMap="ASD" parameterType="int">
select * from mydj,myhouse,mycus,myemp
where mydj.hid=myhouse.hid and mydj.cid=mycus.cid and mydj.eid=myemp.eid
and mydj.mid=#{mid}
</select>
//resultMap type:结果集
<resultMap type="com.ws.bean.Mydj" id="ASD">
<id property="mid" column="mid"/>
<result property="mdate" column="mdate"/>
<result property="mbegintime" column="mbegintime"/>
<association property="house" javaType="com.ws.bean.Myhouse">
<id property="hid" column="hid"/>
<result property="haddress" column="haddress"/>
<result property="hfh" column="hfh"/>
<association property="area" javaType="com.ws.bean.Myarea">
<id property="aid" column="aid"/>
</association>
</association>
<association property="cus" javaType="com.ws.bean.Mycus">
<id property="cid" column="cid"/>
<result property="cname" column="cname"/>
<result property="ctel" column="ctel"/>
</association>
<association property="emp" javaType="com.ws.bean.Myemp">
<id property="eid" column="eid"/>
<result property="erealname" column="erealname"/>
</association>
</resultMap>
- dao层
public interface DjrzDao {
Mydj findDjByMid(Integer mid);
}
- 对应的实体类(有无参，get，set省略)
public class Mydj {
private int mid;// 编号
private Date mdate;// 登记时间
private int eid;// 经办人编号（员工编号）
private int cid;// 客户编号
private int hid;// 房屋编号
private String mimg;// 合同图片
private double myj;// 押金
private double myzj;// 预收租金
private int mflag;// 正在出租中（已出租）为0 退租（未出租）为1
private Date mbegintime;// 下次收租日期
private Myhouse house ; /*mapper中对应的属性名要一致！*/
private Mycus cus; /*mapper中对应的属性名要一致！*/
private Myemp emp ; /*mapper中对应的属性名要一致！*/
}
- Mydj中house对应的实体类
public class Myhouse {
private int hid;
private String haddress;
private String hfh;
private Myarea area;
在resultMap中，house表链接外键，所以得在实体类中加个对象，不然查不出来
基于注解
- 基础的CURD
public interface CustomDao {
//分页
@Select("select * from mycus limit #{current},#{length}")
public List<Mycus> findAllCustom(@Param("current")Integer current , @Param("length")Integer length) ;
//分组
@Select("select count(*) from mycus")
public int count();
//添加
@Insert("insert into mycus values(null,#{cname},#{csex},#{ctel},#{ctel1},#{ccard})")
public Integer addCus(Mycus mycus);
//修改之前通过id查询
@Select("select * from mycus where cid=#{cid}")
Mycus findCusById(String cid);
//修改
@Update("update mycus set cname=#{cname},csex=#{csex},ctel=#{ctel},ctel1=#{ctel1},ccard=#{ccard} where cid=#{cid}")
int uppCus(Mycus mycus);
}
- 动态SQL
@Select("<script>"
+ "select * from myemp a, mydept s,myjs d "
+ "WHERE a.eid=s.pid and a.eid=d.jid "
+ "<if test=\"eflag ==0\">and eflag=0 </if><if test=\"eflag ==1\">and eflag=1 </if>"
+ "limit #{start},#{lenth}"
+ "</script>")
List<Myemp> findAllEmp(@Param("start")int start,@Param("lenth")int lenth,@Param("eflag")String eflag);
- 一对多查询
@Select("select * from mybs,myemp,myhouse where mybs.eid=myemp.eid and mybs.hid=myhouse.hid limit #{current},#{length}")
@Results({
@Result(column="hid",property="house",
one=@One(select="com.ws.dao.MyhouseDao.findAllHouse")),
@Result(column="eid",property="emp",
one=@One(select="com.ws.dao.MyempDao.findallemp"))
})
- MyhouseDao中的方法
@Select("select * from myhouse a, mysort s,myarea d WHERE a.hid=s.sid and a.hid=d.aid limit #{start},#{lenth} ")
ArrayList<Myhouse> findAllHousePD(@Param("start")int start,@Param("lenth") int lenth);
- MyempDao中的方法
@Select("<script>select * from myemp a left join mydept b on a.eid=b.pid left join myjs c on a.eid=c.jid <if test=\"eflag ==0\">where eflag=0 </if><if test=\"eflag ==1\">where eflag=1 </if> limit #{start},#{lenth}</script>")
List<Myemp> findAllEmp(@Param("start")int start,@Param("lenth")int lenth,@Param("eflag")String eflag);
通过多次一对一的调用实现一对多的查询
191
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
