提示：要求，实现一对多关系，及歌手与歌曲之间数据库的增删改查操作，包含连接查询与聚合分组查询。 附加多参数、多返回值的xml处理操作
xml与注解实现数据库的增删改查操作
一、相同前奏–主配置文件配置
pom.xml的导包就不说了，开局必备
接着就是configuration.xml配置properties、typeAliases、plugins、environments、mappers
properties：引用外部的properties文件
<properties resource="jdbc.properties"></properties>
typeAliases：配置实体类类名的简写模式
<typeAliases>
<package name="com.mypro.entity"/>
</typeAliases>
plugins：配置插件，如分页助手（需要先在pom.xml中导包）
<plugins>
<!-- 分页助手 -->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- 设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库-->
<property name="helperDialect" value="Mysql"/>
<property name="rowBoundsWithCount" value="true"/>
</plugin>
</plugins>
environments：数据库连接池
<environments default="development">
<environment id="development">
<transactionManager type="jdbc"></transactionManager>
<dataSource type="POOLED">
<!--引用properties文件内的配置信息 -->
<property name="driver" value="${jdbc.classname}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
environments：数据库连接池
<mappers>
<!-- 注解： 这个表示导入的mapper接口文件-->
<!-- <package name="com.mypro.mapper"/>-->
<!-- 或 -->
<!-- <mapper class="com.mypro.mapper.SongMapper"></mapper>-->
<!-- <mapper class="com.mypro.mapper.SingerMapper"></mapper>-->
<!-- xml： 这个表示导入的.xml的文件 -->
<mapper resource="com/mypro/mapper/SingerMapper.xml"></mapper>
<mapper resource="com/mypro/mapper/SongMapper.xml"></mapper>
</mappers>
二、xml与注解的再次同步
在前面的基础上，再创建需要实现一对多关系的两个实体类，并实现DbUtil类
public class DbUtil {
public static SqlSession getSession(){
try{
//读取configuration主配置文件，获取SqlSession对象
Reader reader = Resources.getResourceAsReader("configuration.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(reader);
SqlSession session = factory.openSession();
return session;
}catch (IOException e){
e.printStackTrace();
}
return null;
}
}
注意：SqlSession等一系列方法需要导入maven依赖。其中SqlSession(相当于替代了jdbc的connection+PreparedStatement)
三、xml与注解的区别
3.1.xml
3.1.1xml文件配置，写增删改查sql语句的地方
创建resources，在其中创建两个实体类分别对应的xml文件（SingerMapper.xml<一>与SongMapper.xml<多>）
SingerMapper.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包下的DeptMapper接口中的sql操作 -->
<mapper namespace="com.mypro.mapper.SingerMapper">
<!--一对多配置 fetchType 建议使用 lazy 自己是1，要配置多的 collection-->
<resultMap id="SingerMap" type="Singer">
<!-- 列名与属性名一致的可以省略配置-->
<id column="id" property="id" javaType="java.lang.Integer"></id>
<collection property="songs" column="id" javaType="java.util.List" fetchType="lazy"
select="com.mypro.mapper.SongMapper.queryBySingerid"></collection>
</resultMap>
<select id="queryAll" resultType="Singer">
select * from singer
</select>
<!-- 用了resultMap能显示出一对多关系的数据，即通过singer查到songs-->
<select id="querySingle" resultMap="SingerMap" parameterType="INTEGER">
select * from singer where id = #{id}
</select>
<select id="queryJoin" resultType="java.util.HashMap">
SELECT *FROM singer JOIN songs ON singer.id = songs.singerid
</select>
<!-- ,#{songs}注意 外键不能放进去，因为数据库内不存在-->
<insert id="add" parameterType="Singer">
insert into singer values(#{id}, #{sname},#{birth})
</insert>
<!-- parameterType中不能有多余空格-->
<delete id="delete" parameterType="INTEGER">
delete from singer where id = #{id}
</delete>
<update id="update" parameterType="Singer">
update singer set sname = #{sname}, birth=#{birth} where id = #{id}
</update>
</mapper>
SongMapper.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.mypro.mapper.SongMapper">
<resultMap id="SongMap" type="Song">
<id column="id" property="id" javaType="java.lang.Integer"></id>
<association property="singer" column="singerid" javaType="Singer" fetchType="eager"
select = "com.mypro.mapper.SingerMapper.querySingle"></association>
</resultMap>
<select id ="queryAll" resultType="Song">
select *from songs
</select>
<!-- 实现一对多方法-->
<select id ="querySingle" resultMap="SongMap" parameterType="INTEGER">
select *from songs where id = #{id}
</select>
<!-- java.lang.integer不支持，只能用INTEGER-->
<select id ="queryBySingerid" resultType="Song" parameterType="INTEGER">
select *from songs where singerid = #{singerid}
</select>
<!-- 聚合分组 某歌手对应的歌曲有多少 -->
<select id="queryCountSongs" resultType="java.util.HashMap">
select singerid, count(*) from songs group by singerid
</select>
<insert id="add" parameterType="Song">
insert into songs(id,stitle,singerid) values (#{id},#{stitle},#{singerid})
</insert>
<delete id="delete" parameterType="INTEGER">
delete from songs where id = #{id}
</delete>
<update id="update" parameterType="Song">
update songs set stitle = #{stitle}, singerid = #{singerid} where id = #{id}
</update>
</mapper>
3.1.2Mapper接口方法
SingerMapper.java文件
package com.mypro.mapper;
import com.mypro.entity.Singer;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
public interface SingerMapper {
/* xml方法实现 */
// 查询
public List<Singer> queryAll();
public Singer querySingle(Integer id);
public List<HashMap<String, Object>> queryJoin();
// 增删改
public Boolean add(Singer singer);
public Boolean delete(Integer id);
public Integer update(Singer singer);
// /*
// 注解实现
// */
// @Select(value ="select *from singer")
// @Results(value = {
// @Result(id = true,column = "id", property = "id", javaType = Integer.class),
// @Result(column = "id", property = "songs",
// javaType = List.class,
// many = @Many(select = "com.mypro.mapper.SongMapper.queryBySingerid", fetchType = FetchType.LAZY))
// })
// public List<Singer> queryAll();
// @Select(value="select *from singer where id = #{id}")
// @ResultType(value = Singer.class)
// public Singer querySingle(Integer id);
//
// @Insert(value=" insert into singer values(#{id}, #{sname},#{birth})")
// public Boolean add(Singer singer);
//
// @Delete(value="delete from singer where id = #{id}")
// public Boolean delete(Integer id);
//
// @Update(value = "update singer set sname = #{sname}, birth=#{birth} where id = #{id}")
// public Integer update(Singer singer);
}
SongMapper.java文件
package com.mypro.mapper;
import com.mypro.entity.Singer;
import com.mypro.entity.Song;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
public interface SongMapper {
/* xml方法实现 */
// 查询
public List<Song> queryAll();
public Song querySingle(Integer id);
public List<Song> queryBySingerid(Integer singerid); // 实现一对多方法
public List<HashMap<String, Integer>> queryCountSongs(Integer id);
// 增删改
public Boolean add(Song song);
public Integer update(Song song);
public Boolean delete(Integer id);
/* 注解实现*/
// @Select(value = "select *from songs")
// //定义一个ResultMap配置
// @Results(id = "SongMap",value = {
// //针对每个列的配置
// @Result(id = true,column = "id",property = "id",javaType = Integer.class),
// @Result(column = "singerid", property = "singerid", javaType = Integer.class),
// //one 配置多对一
// @Result(column = "singerid",property = "singer",javaType = Singer.class,
// one = @One(select = "com.mypro.mapper.SingerMapper.querySingle",fetchType = FetchType.EAGER))
// })
// public List<Song> queryAll();
//后续的查询方法 使用上一个查询方法里定义的ResultMap
// @Select(value = "select * from songs where id = #{id}")
// @ResultMap(value = "SongMap")
// public Song querySingle(Integer id);
// @Select(value = "select *from songs where singerid = #{singerid}")
@ResultMap(value = "SongMap")
// public List<Song> queryBySingerid(Integer singerid);
//
// @Select(value = "select *from songs where singerid = #{singerid}")
@ResultMap(value = "SongMap")
// public List<HashMap<String, Integer>> queryCountSongs(Integer id);
//
// @Insert(value = " insert into songs(id,stitle,singerid) values (#{id},#{stitle},#{singerid})")
@ResultMap(value = "SongMap")
// public Boolean add(Song song);
// @Delete(value = " delete from songs where id = #{id}")
@ResultMap(value = "SongMap")
// public Boolean delete(Integer id);
// @Update(value = "update songs set stitle = #{stitle}, singerid = #{singerid} where id = #{id}")
@ResultMap(value = "SongMap")
// public Integer update(Song song);
}
注意：mapper.xml中的id名对应mapper.java中的方法名
3.2注解
3.2.1注解只需要在mapper.java接口中配置方法就可以
具体代码在上面3.1.2下面的注释代码中。@### 3.1.2Mapper接口方法
四、方法的调用与检验
主要在于getSession方法以及getMapper方法的调用，还有session的关闭
package com.mypro.xml;
import com.mypro.dbutil.DbUtil;
import com.mypro.entity.Singer;
import com.mypro.entity.Song;
import com.mypro.mapper.SingerMapper;
import com.mypro.mapper.SongMapper;
import org.apache.ibatis.session.SqlSession;
import java.util.HashMap;
import java.util.List;
public class ComplexSelect {
public static void main(String[] args) {
SqlSession session = DbUtil.getSession();
SingerMapper singerMapper = session.getMapper(SingerMapper.class);
SongMapper songMapper = session.getMapper(SongMapper.class);
// 一对多查询 -- 通过singer查询songs
Singer singer = singerMapper.querySingle(1);
System.out.println(singer);
System.out.println(singer.getSongs());
没用resultMap一对多关系
// List<Singer> singers = singerMapper.queryAll();
// for(Singer s:singers){
// System.out.println(s);
// System.out.println(s.getSongs());
// }
//
多对一查询 -- 通过songs查询singer
// Song songs= songMapper.querySingle(1);
// System.out.println(songs);
// System.out.println(songs.getSinger());
聚合分组查询
// List<HashMap<String, Integer>> list = songMapper.queryCountSongs(1);
// System.out.println(list);
// 连接查询
List<HashMap<String, Object>> list = singerMapper.queryJoin();
System.out.println(list);
session.commit();
session.close();
}
}
注意： 增删改操作，必须要调用session.commit()方法才会生效！
特殊情况：在书写sql语句时，参数为多个，或返回值为多个的情况，应做如下处理
参数为多个：
//多个条件
//方法有多个参数时,mybatis会自动将多个参数包装成Map集合，使用注解去声明key
public List<Emp> queryByJobAndDeptno(@Param("job") String job, @Param("deptno") Integer deptno);
//将多个条件数据再组成一个新类型对象
// public List<Emp> queryEmpByMgrAndSal(NewMsg ms);
//模糊查询 like
public List<Emp> queryByLike(String like);
//排序 列 升序降序？
public List<Emp> queryByDeptnoWithOrder(@Param("deptno") int deptno,@Param("colname") String colname,@Param("type") String type);
<select id="queryByJobAndDeptno" resultType="Emp" parameterType="java.util.Map">
select * from emp where job=#{job} and deptno=#{deptno}
</select>
<!-- <select id="queryEmpByMgrAndSal" resultType="Emp" parameterType="MgrAndSal">-->
<!-- select * from emp where mgr=#{mgr} and sal>#{sal}-->
<!-- </select>-->
<select id="queryByLike" resultType="Emp" parameterType="java.lang.String">
select * from emp where ename like #{like}
</select>
<!--数据拼接 都使用 #{}
文字性质拼接 使用${} -->
<select id="queryByDeptnoWithOrder" resultType="Emp" parameterType="java.util.Map">
select * from emp where deptno = #{deptno} order by ${colname} ${type}
</select>
返回值为多个：
//查询多个列(每行结果为 列名：数据 组成的map集合)
public List<Map<String,Object>> queryEnameJobSal();
<select id="queryEnameJobSal" resultType="java.util.Map">
select ename,job,sal from emp
</select>
总结
1、 xml与注解可以同时使用，但不能同时对同一个方法进行，并且xml优先级高于注解。因此最好在使用xml时，注释掉有关xml的一切，尤其是主配置文件中的mappers。
2、一对多关系中SingerMapper.xml的resultMap一对多配置为collection ，而多对一配置为association。
408
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
