Spring Mybatis 基本使用过程(推荐)

<dependency>
    <groupid>org.mybatis</groupid>
    <artifactid>mybatis</artifactid>
    <version>3.5.11</version>
</dependency>

<?xml version="1.0" encoding="utf-8" ?>
<!doctype configuration
        public "-//mybatis.org//dtd mapper 3.0/en"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
<!--        <setting name="logimpl" value="stdout_logging"/>&lt;!&ndash; 开启mybatis的日志输出 &ndash;&gt;-->
        <setting name="mapunderscoretocamelcase" value="true"/><!-- 开启驼峰式自动映射 a_big => abig -->
    </settings>
    <typealiases>
        <typealias alias="goods" type="com.jojo.pojo.goods"/><!-- 单独设置别名 -->
        <package name="com.jojo.pojo"/><!-- 批量设置别名, com.jojo.pojo包下的所有类名的别名为类的首字母小写-->
    </typealiases>
    <environments default="development">
        <environment id="development">
            <transactionmanager type="jdbc"/>  <!-- 自动开启事务 -->
            <datasource type="pooled"><!-- mybatis维护连接池 -->
                <property name="driver" value="com.mysql.cj.jdbc.driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis-example"/>
                <property name="username" value="root"/>
                <property name="password" value="a12345678"/>
            </datasource>
        </environment>
    </environments>
    <mappers>
        <!-- 指定mapper xml文件的位置 -->
        <mapper resource="mappers/goodsmapper.xml"/>
    </mappers>
</configuration>

<?xml version="1.0" encoding="utf-8" ?>
<!doctype mapper
        public "-//mybatis.org//dtd mapper 3.0/en"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jojo.mapper.goodsmapper"><!-- 对应mapper的全限定符 -->
<!--    这里写sql语句 -->
    <insert id="insert" usegeneratedkeys="true" keycolumn="id" keyproperty="id">
        insert into goods (name) value(#{name})
    </insert>
    <update id="update">
        update goods set name=#{name} where id=#{id}
    </update>
    <delete id="delete">
        delete from goods where id = #{id}
    </delete>
    <select id="selectbyid" resulttype="goods">
        select * from goods where id = #{id}
    </select>
    <select id="selectall" resulttype="goods">
        select * from goods
    </select>
</mapper>

import lombok.data;
@data//lombook插件的@data标签可以自动生成get和set以及tostring方法
public class goods {
    private integer id;
    private string name;
}

public interface goodsmapper {
    int insert(goods goods);
    int update(goods goods);
    int delete(integer id);
    goods selectbyid(integer id);
    list<goods> selectall();
}

public class mybatistest {
    @test
    public void test() throws ioexception {
        //1.读取外部配置文件
        inputstream ips = resources.getresourceasstream("mybatis-config.xml");
        //2.创建sqlsessionfactory
        sqlsessionfactory sqlsessionfactory = new sqlsessionfactorybuilder().build(ips);
        //3.根据sqlsessionfactory创建sqlsession
        sqlsession sqlsession = sqlsessionfactory.opensession();
        //4.获取接口的代理对象，调用代理对象的方法就会查找mapper接口的方法
        goosdmapper mapper = sqlsession.getmapper(goosdmapper.class);
        goods goods = mapper.querybyid(1);
        system.out.println(goods);
        //5.提交事务和释放资源
        //sqlsession.commit();
        sqlsession.close();
    }
}

<!-- #{id} = 使用占位符？，防止sql注入攻击，但不能替代表名表项-->
<!-- ${id} = 不使用占位符？，不能防止sql注入攻击，但可以替代表名表项-->
<select id="querybyid" resulttype="com.jojo.pojo.employee">
    select emp_id empid,emp_name empname, emp_salary empsalary from t_emp where emp_id = #{id}
</select>
<delete id="deletebyid">
    delete from t_emp where emp_id = #{id} <!-- 传入integer类型，id可以改写成任意字符串-->
</delete>
<select id="querybysalary" resulttype="com.jojo.pojo.employee">
    select emp_id empid,emp_name empname, emp_salary empsalary from t_emp where empsalary = #{salary} <!-- 传入double类型，salary可以改写成任意字符串-->
</select>
<insert id="insertemp">
    insert into t_emp (emp_name, emp_salary) values (#{empname},#{empsalary});<!-- 传入对象时，要写传入对象的属性 -->
</insert>
<select id="querybynameandsalary" resulttype="com.jojo.pojo.employee">
    select emp_id empid,emp_name empname, emp_salary empsalary from t_emp where empsalary = #{a} and empname = #{b} <!-- 传入两个基本类型，根据接口中的@param("名称")来指定-->
</select>
<select id="querybynameandsalary" resulttype="com.jojo.pojo.employee">
    select emp_id empid,emp_name empname, emp_salary empsalary from t_emp where empsalary = #{arg0} and empname = #{arg1} <!-- 法2：传入两个基本类型，可以根据顺序来取arg0...arg1...-->
</select>
<select id="querybynameandsalary" resulttype="com.jojo.pojo.employee">
    select emp_id empid,emp_name empname, emp_salary empsalary from t_emp where empsalary = #{param1} and empname = #{param2} <!-- 法3：传入两个基本类型，可以根据顺序来取param1...param2...-->
</select>
<insert id="insertempmap">
    insert into t_emp (emp_name, emp_salary) values (#{name},#{salary});<!-- 传入map时，要写传入map的key -->
</insert>

<select id="querynamebyid" resulttype="string"><!-- resulttype指定返回的类型，写类的全限定符或者mybatis提供的别名(在mybatis官网查)-->
    select emp_name from t_emp where emp_id = #{id} 
</select>
<select id="querybyid" resulttype="employee"> <!-- resulttype指定返回的为对象时，select的行需要起别名来与类的属性完全一致-->
    select emp_id empid,emp_name empname, emp_salary empsalary from t_emp where emp_id = #{id} 
</select>
<select id="querybyid" resulttype="employee"><!-- resulttype指定返回的为对象时，开启驼峰映射(mapunderscoretocamelcase)后，select的行不再需要起别名来与类的属性完全一致-->
    select * from t_emp where emp_id = #{id}  
</select>
<select id="selectempnameandmaxsalary" resulttype="map"> <!-- resulttype返回的值没有未定义类时，可以用map接值，map的每一项的key对应一个列名 -->
    select emp_name 员工姓名, emp_salary 员工工资, (select avg(emp_salary) from t_emp) 部门平均工资 from t_emp where emp_salary=(select max(emp_salary) from t_emp)
</select>
<select id="querynamesbysalary" resulttype="string"><!--如果返回类型时list<string>，那么指定string即可-->
    select emp_name from t_emp where emp_salary > #{ salary};
</select>
<select id="queryall" resulttype="employee"><!--如果返回类型时list<employee>，那么指定employee即可-->
    select * from t_emp;
</select>
<insert id="insertemp" usegeneratedkeys="true" keycolumn="emp_id" keyproperty="empid"><!-- 主键自增长型：插入时，获取插入的id放在empid中 -->
    insert into t_emp (emp_name, emp_salary) value(#{empname},#{empsalary});
</insert>
<insert id="insertteacher">
    <selectkey order="before" resulttype="string" keyproperty="tid">
        select replace(uuid(),'-',''); <!-- 插入前由数据库生成uuid并放在tid中-->
    </selectkey>
    insert into teacher (t_id,t_name) value (#{tid},#{tname})
</insert>

public class a {
    private integer id;
    private string aname;
    private integer bid;
    private b b;
}
public class b {
    private integer bid;
    private string bname;
}

<resultmap id="amap" type="a">
    <!-- a的主键 id标签-->
    <id column="a_id" property="aid"/>
    <!-- order的普通列 custom标签-->
    <result column="a_name" property="aname"/>
    <result column="b_id" property="bid"/>
    <!-- 给第二层对象属性赋值 -->
    <association property="b" javatype="b">
        <id column="b_id" property="bid"/>
        <result column="b_name"  property="bname"></result>
    </association>
</resultmap>
<select id="queryabyid" resultmap="amap">
    select * from t_a ta join t_b tb on ta.b_id = tb.b_id where ta.a_id = #{id};
</select>

<settings>
	<!-- 开启驼峰式自动映射 a_big => abig -->
    <setting name="mapunderscoretocamelcase" value="true"/>
    <!-- 开启自动映射 a_big => abig -->
    <setting name="automappingbehavior" value="full"/>
</settings>

<resultmap id="amap" type="a">
    <!-- a的主键 id标签-->
    <id column="a_id" property="aid"/>
    <!-- 给第二层对象属性赋值 -->
    <association property="b" javatype="b">
        <id column="b_id" property="bid"/>
    </association>
</resultmap>
<select id="queryabyid" resultmap="amap">
    select * from t_a ta join t_b tb on ta.b_id = tb.b_id where ta.a_id = #{id};
</select>

public class a {
    private integer id;
    private string aname;
    private integer bid;
    private list<b> blist;
}
public class b {
    private integer bid;
    private string bname;
}

<resultmap id="amap" type="a">
    <id column="a_id" property="aid"/>
    <result column="a_name" property="aname"/>
     <result column="b_id" property="bid"/>
    <!--针对list<a>属性使用collection -->
    <collection property="blist" oftype="b">
        <id column="b_id" property="bid"></id>
        <result column="b_name" property="bname"/>
    </collection>
</resultmap>
<select id="queryalist" resultmap="amap">
    select * from t_a ta join t_b tb on ta.customer_id = tb.customer_id
</select>

<settings>
	<!-- 开启驼峰式自动映射 a_big => abig -->
    <setting name="mapunderscoretocamelcase" value="true"/>
    <!-- 开启自动映射 a_big => abig -->
    <setting name="automappingbehavior" value="full"/>
</settings>

<resultmap id="amap" type="a">
    <id column="a_id" property="aid"/>
    <!--针对list<a>属性使用collection -->
    <collection property="blist" oftype="b">
        <id column="b_id" property="bid"></id>
    </collection>
</resultmap>
<select id="queryalist" resultmap="amap">
    select * from t_a ta join t_b tb on ta.customer_id = tb.customer_id
</select>