mybatis

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm
jdbc.username=root
jdbc.password=root

 

 

<?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>
        <property name="jdbc.driver" value="com.mysql.jdbc.Driver"/>
    </properties> -->
    
    <!-- 
        <properties>:设置或引入资源文件
        resource:在类路径下访问资源文件
        url:在网络路径或磁盘路径下访问资源文件
     -->
    <properties resource="jdbc.properties"></properties>
    
    <settings>
        <!-- 将下划线映射成驼峰，user_name映射为userName -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!-- 开启延迟加载 -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 是否查询所有数据 -->
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>
    
    <typeAliases>
        <!-- 
            为类型设置类型别名
            type:Java 类型，若只设置type，默认的别名就是类型，且不区分大小写
         -->
        <!-- <typeAlias type="com.atguigu.bean.User" alias="u"/> -->
        <package name="com.atguigu.bean"/>
    </typeAliases>
    
    
    <!-- 
        <environments>:设置连接数据库的环境
        default:设置默认使用的数据库环境
     -->
    <environments default="mysql">
        <!-- 
            <environment>:设置某个具体的数据库的环境
            id:数据库环境的唯一标示
         -->
        <environment id="mysql">
            <!-- type="JDBC|MANAGED"，JDBC：使用JDBC原生的事务管理方式，即提交和回滚都需要手动处理 -->
            <transactionManager type="JDBC" />
            <!-- type="POOLED|UNPOOLED|JNDI" -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}" />
                <property name="url" value="${jdbc.url}" />
                <property name="username" value="${jdbc.username}" />
                <property name="password" value="${jdbc.password}" />
            </dataSource>
        </environment>
        
        <environment id="oracle">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/ssm" />
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <!-- 引入映射文件 -->
    <mappers>
        <!-- <mapper resource="EmpMapper.xml" />
        <mapper resource="DeptMapper.xml" /> -->
        <!-- 此种写法要求mapper接口和mapper映射文件必须在同一个包下 -->
        <package name="com.atguigu.mapper"/>
    </mappers>
</configuration>

 

<?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.atguigu.mapper.ParamMapper">
    
    <!-- void insertEmp(Emp emp); -->
    <!-- 
        useGeneratedKeys:可以使用自动生成的主键
        keyProperty:将自动生成的主键赋值给传递过来的参数的哪一个属性
     -->
    <insert id="insertEmp" useGeneratedKeys="true" keyProperty="eid">
        <!-- insert into emp values(null, #{ename}, #{age}, #{sex}) -->
        insert into emp values(null, '${ename}', '${age}', '${sex}')
    </insert>
    
    <!-- Emp getEmpByEid(String eid); -->
    <select id="getEmpByEid" resultType="Emp">
        <!-- select eid,ename,age,sex from emp where eid = #{eid} -->
        <!-- select eid,ename,age,sex from emp where eid = ${param} -->
        <!-- select eid,ename,age,sex from emp where eid = ${value} -->
        <!-- select eid,ename,age,sex from emp where eid = #{aaa}这个aaa是可以随意的，没有对应关系，但建议写传的形参名 -->
        select eid,ename,age,sex from emp where eid = #{eid}
    </select>
    
    <!-- Emp getEmpByEidAndEname(String eid, String ename); -->
    <select id="getEmpByEidAndEname" resultType="Emp">
        select eid,ename,age,sex from emp where eid = ${param1} and ename = '${param2}'
    </select>
    
    <!-- Emp getEmpByMap(Map<String, Object> map); -->
    <select id="getEmpByMap" resultType="emp">
        select eid,ename,age,sex from emp where eid = ${eid} and ename = '${ename}'
    </select>
    
    <!-- Emp getEmpByEidAndEnameByParam(@Param("eid")String eid, @Param("ename")String ename); 
    这个有点疑问
    -->
    <select id="getEmpByEidAndEnameByParam" resultType="Emp">
        select eid,ename,age,sex from emp where eid = #{eid} and ename = #{ename}
    </select>
    
    
</mapper>

 

package com.atguigu.mapper;

import java.util.Map;

import org.apache.ibatis.annotations.Param;

import com.atguigu.bean.Emp;

public interface ParamMapper {

    //添加员工信息
    void insertEmp(Emp emp);
    
    //根据eid获取员工信息
    Emp getEmpByEid(String eid);
    
    //根据eid和ename查询员工信息
    Emp getEmpByEidAndEname(String eid, String ename);
    
    //根据map查询员工信息
    Emp getEmpByMap(Map<String, Object> map);
    
    //根据eid和ename查询员工信息
    Emp getEmpByEidAndEnameByParam(@Param("eid")String eid, @Param("ename")String ename);
    
    
}

 

package com.atguigu.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.atguigu.bean.Emp;
import com.atguigu.mapper.EmpMapper;
import com.atguigu.mapper.ParamMapper;

public class TestParam {

    @Test
    public void testCRUD() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //SqlSession sqlSession = sqlSessionFactory.openSession();//需要手动处理事务
        SqlSession sqlSession = sqlSessionFactory.openSession(true);//自动处理事务
        ParamMapper mapper = sqlSession.getMapper(ParamMapper.class);
        /*Emp emp = new Emp(null, "admin", 23, "男");*/
        /*mapper.insertEmp(emp);
        System.out.println(emp.getEid());*/
        /*Emp emp = mapper.getEmpByEid("1");
        System.out.println(emp);*/
        /*Emp emp = mapper.getEmpByEidAndEname("1", "张三");
        System.out.println(emp);*/
        Map<String, Object> map = new HashMap<>();
        map.put("eid", "1");
        map.put("ename", "张三");
        Emp emp = mapper.getEmpByMap(map);
        /*Emp emp = mapper.getEmpByEidAndEnameByParam("1", "张三");*/
        System.out.println(emp);
    }
    
}

 

<?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.atguigu.mapper.EmpSelectMapper">
    
    <!-- Emp getEmpByEid(String eid); -->
    <select id="getEmpByEid" resultType="Emp">
        select eid,ename,age,sex from emp where eid = #{eid}
    </select>
    
    <!-- Integer getCount(); -->
    <select id="getCount" resultType="Integer">
        select count(eid) from emp
    </select>
    
    <!-- Emp getEmpMapByEid(String eid); -->
    <select id="getEmpMapByEid" resultType="java.util.HashMap">
        select eid,ename,age,sex from emp where eid = #{eid}
    </select>
    
    <!-- Map<String, Object> getAllEmpMap(); -->
    <select id="getAllEmpMap" resultType="Emp">
        select eid,ename,age,sex from emp
    </select>
    
    
    
</mapper>

 

package com.atguigu.mapper;

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

import org.apache.ibatis.annotations.MapKey;

import com.atguigu.bean.Emp;

public interface EmpSelectMapper {

    //根据eid查询一个员工信息
    Emp getEmpByEid(String eid);
    //获取所有的员工的数量
    Integer getCount();
    //以map集合获取一个员工信息
    Map<String, Object> getEmpMapByEid(String eid);
    //以map集合获取所有员工信息
    @MapKey("eid")//设置map的键，因为在查询时传出所有的员工信息，可以把员工信息作为值，但是必须设置键
    Map<String, Object> getAllEmpMap();
    
}

 

package com.atguigu.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Map;

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.atguigu.bean.Emp;
import com.atguigu.mapper.EmpMapper;
import com.atguigu.mapper.EmpSelectMapper;

public class TestSelect {

    @Test
    public void testSelect() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //SqlSession sqlSession = sqlSessionFactory.openSession();//需要手动处理事务
        SqlSession sqlSession = sqlSessionFactory.openSession(true);//自动处理事务
        EmpSelectMapper mapper = sqlSession.getMapper(EmpSelectMapper.class);
        //若查询出的数据有多条，则绝对不能将接口中该方法的返回值设置为Javabean
        /*Emp emp = mapper.getEmpByEid("3");
        System.out.println(emp);*/
        //获取员工总记录数
        /*Integer i = mapper.getCount();
        System.out.println(i);*/
        //以map获取单个emp
        /*Map<String, Object> map = mapper.getEmpMapByEid("6");
        System.out.println(map);*/
        //以map获取所有emp
        Map<String, Object> map = mapper.getAllEmpMap();
        System.out.println(map);
    }
    
}

 

<?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.atguigu.mapper.EmpMapper">
    
    <!-- Emp getEmpByEid(String eid); -->
    <select id="getEmpByEid" resultType="Emp">
        select eid,ename,age,sex from emp where eid = #{eid}
    </select>
    
    <!-- List<Emp> getAllEmp(); -->
    <select id="getAllEmp" resultType="Emp">
        select eid,ename,age,sex from emp
    </select>
    
    <!-- void addEmp(Emp emp); -->
    <insert id="addEmp">
        insert into emp values(null,#{ename},#{age},#{sex})
    </insert>
    
    <!-- void updateEmp(Emp emp); -->
    <update id="updateEmp">
        update emp set ename = #{ename}, age = #{age}, sex = #{sex} where eid = #{eid}
    </update>
    
    <!-- void deleteEmp(String eid); -->
    <delete id="deleteEmp">
        delete from emp where eid = #{eid}
    </delete>
    
</mapper>

 

package com.atguigu.mapper;

import java.util.List;

import com.atguigu.bean.Emp;

public interface EmpMapper {

    //根据eid查询一个员工信息
    Emp getEmpByEid(String eid);
    //获取所有的员工信息
    List<Emp> getAllEmp();
    //添加员工信息
    void addEmp(Emp emp);
    //修改员工信息
    void updateEmp(Emp emp);
    //删除员工信息
    Boolean deleteEmp(String eid);
    
}

 

package com.atguigu.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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.atguigu.bean.Emp;
import com.atguigu.mapper.EmpMapper;

public class TestCRUD {

    @Test
    public void testCRUD() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //SqlSession sqlSession = sqlSessionFactory.openSession();//需要手动处理事务
        SqlSession sqlSession = sqlSessionFactory.openSession(true);//自动处理事务
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        //测试：根据eid获取员工信息
        /*Emp emp = empMapper.getEmpByEid("3");
        System.out.println(emp);*/
        //测试：获取所有的员工信息
        /*List<Emp> list = empMapper.getAllEmp();
        System.out.println(list);*/
        //测试：添加员工信息
        /*empMapper.addEmp(new Emp(null, "admin", 23, "女"));
        sqlSession.commit();//提交事务*/
        //测试：修改员工信息
        empMapper.updateEmp(new Emp(6, "张二", 33, "女"));
        //测试：删除员工信息
        /*Boolean i = empMapper.deleteEmp("2");
        System.out.println("result:"+i);*/
        //select 字段名 from 表名 where 条件 group by 字段名 having 条件 order by 字段名 desc/asc limit index,pageSize
    }
    
}

 

<?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.atguigu.mapper.EmpDeptMapper">
    
    <!-- <resultMap type="Emp" id="empMap">
        <id column="eid" property="eid"/>
        <result column="ename" property="ename"/>
        <result column="age" property="age"/>
        <result column="sex" property="sex"/>
        <result column="did" property="dept.did"/>
        <result column="dname" property="dept.dname"/>
    </resultMap> -->
    
    <!-- 
        <resultMap>：自定义映射，处理复杂的表关系
        <id column="eid" property="eid"/>
        <id>：设置主键的映射关系，column设置字段名，property设置属性名
        <result column="ename" property="ename"/>
        <result>：设置非主键的映射关系，column设置字段名，property设置属性名
     -->
    <resultMap type="Emp" id="empMap">
        <id column="eid" property="eid"/>
        <result column="ename" property="ename"/>
        <result column="age" property="age"/>
        <result column="sex" property="sex"/>
        <association property="dept" javaType="Dept">
            <id column="did" property="did"/>
            <result column="dname" property="dname"/>
        </association>
    </resultMap>
    
    <!-- List<Emp> getAllEmp(); -->
    <select id="getAllEmp" resultMap="empMap">
        select e.eid,e.ename,e.age,e.sex,e.did,d.dname from emp e left join dept d on e.did = d.did
    </select>
    
    <!-- 
        <resultMap>：自定义映射，处理复杂的表关系
     -->
    <resultMap type="Emp" id="empMapStep">
        <id column="eid" property="eid"/>
        <result column="ename" property="ename"/>
        <result column="age" property="age"/>
        <result column="sex" property="sex"/>
        <!-- 
            select:分步查询的SQL的id，即接口的全限定名.方法名或namespace.SQL的id
            column:分步查询的条件，注意：此条件必须是从数据库查询过得
         -->
        <association property="dept" select="com.atguigu.mapper.DeptMapper.getDeptByDid" column="did"/>
    </resultMap>
    
    <!-- Emp getEmpStep(String eid); -->
    <select id="getEmpStep" resultMap="empMapStep">
        select eid,ename,age,sex,did from emp where eid = #{eid}
    </select>
    
    <resultMap type="Dept" id="deptMap">
        <id column="did" property="did"/>
        <result column="dname" property="dname"/>
        <!-- 
            <collection>:处理一对多和多对多的关系
            ofType：指集合中的类型，不需要指定javaType
         -->
        <collection property="emps" ofType="Emp">
            <id column="eid" property="eid"/>
            <result column="ename" property="ename"/>
            <result column="age" property="age"/>
            <result column="sex" property="sex"/>
        </collection>
    </resultMap>
    
    <!-- Dept getDeptEmpsByDid(String did); -->
    <select id="getDeptEmpsByDid" resultMap="deptMap">
        select d.did,d.dname,e.eid,e.ename,e.age,e.sex from dept d left join emp e on d.did = e.did where d.did = #{did}
    </select>
    
    <!-- List<Emp> getEmpListByDid(String did); -->
    <select id="getEmpListByDid" resultType="Emp">
        select eid,ename,age,sex from emp where did = #{did}
    </select>
    
    <resultMap type="Dept" id="deptMapStep">
        <id column="did" property="did"/>
        <result column="dname" property="dname"/>
        <collection property="emps" select="com.atguigu.mapper.EmpDeptMapper.getEmpListByDid" column="{did=did}" fetchType="eager"></collection>
    </resultMap>
    
    <!-- Dept getOnlyDeptByDid(String did); -->
    <select id="getOnlyDeptByDid" resultMap="deptMapStep">
        select did,dname from dept where did = #{did}
    </select>
    
</mapper>

 

package com.atguigu.mapper;

import java.util.List;

import com.atguigu.bean.Dept;
import com.atguigu.bean.Emp;

public interface EmpDeptMapper {

    List<Emp> getAllEmp();
    
    Emp getEmpStep(String eid);
    
    Dept getDeptEmpsByDid(String did);
    
    Dept getOnlyDeptByDid(String did);
    
    List<Emp> getEmpListByDid(String did);
    
}
package com.atguigu.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

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.atguigu.bean.Dept;
import com.atguigu.bean.Emp;
import com.atguigu.mapper.EmpDeptMapper;
import com.atguigu.mapper.EmpMapper;
import com.atguigu.mapper.EmpSelectMapper;

public class TestEmpDept {

    @Test
    public void testSelect() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //SqlSession sqlSession = sqlSessionFactory.openSession();//需要手动处理事务
        SqlSession sqlSession = sqlSessionFactory.openSession(true);//自动处理事务
        EmpDeptMapper mapper = sqlSession.getMapper(EmpDeptMapper.class);
        /*List<Emp> emp = mapper.getAllEmp();
        System.out.println(emp);*/
        /*Emp emp = mapper.getEmpStep("3");
        System.out.println(emp.getEname());
        System.out.println("=======================");
        System.out.println(emp.getDept());*/
        /*Dept dept = mapper.getDeptEmpsByDid("2");
        System.out.println(dept);*/
        Dept dept = mapper.getOnlyDeptByDid("1");
        System.out.println(dept.getDname());
        System.out.println("=======================");
        System.out.println(dept.getEmps());
    }
    
}