Mybatis入门

Mybatis框架搭建

1.导包

Mybatis核心包
Mybatis依赖包
数据库驱动包

2.核心配置文件mybatis-config.xml

	<?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 resource="jdbc.properties"/>
	    <!-- 别名-->
	    <typeAliases>
	        <package name="cn.itsource.model"></package>
	    </typeAliases>
	    <!--环境-->
	    <environments default="development">
	        <environment id="development">
	            <transactionManager type="JDBC"/>
	            <dataSource type="POOLED">
	                <property name="driver" value="${db.driver}"/>
	                <property name="url" value="${db.url}"/>
	                <property name="username" value="${db.username}"/>
	                <property name="password" value="${db.password}"/>
	            </dataSource>
	        </environment>
	    </environments>
	    <mappers>
	        <mapper resource="cn/itsource/model/sql.xml"></mapper>
	    </mappers>
	</configuration>

3.日志配置文件log4j.properties

	log4j.rootLogger=debug, stdout
	#log4j.rootLogger=NONE
	log4j.logger.cn.itsource=TRACE
	log4j.appender.stdout=org.apache.log4j.ConsoleAppender
	log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
	log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m%n

4.配置文件sql.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="Product">
	    <sql id="insertFiled">
	        productName,
	        dir_id,
	        salePrice,
	        supplier,
	        brand,
	        cutoff,
	        costPrice
	    </sql>
	    <select id="selectOne" parameterType="long" resultType="Product">
	        select * from product where id = #{id}
	    </select>
	    <select id="selectAll" resultType="Product">
	        select * from product
	    </select>
	    <!--useGeneratedKeys 是否自增-->
	    <!--keyColumn  数据库id-->
	    <!--keyProperty 实体类属性id-->
	    <insert id="save" useGeneratedKeys="true" keyColumn="id" keyProperty="id" parameterType="Product">
	        insert into product (
	        <include refid="insertFiled"/>
	        )
	        values (
	            #{productName},
	            #{dir_id},
	            #{salePrice},
	            #{supplier},
	            #{brand},
	            #{cutoff},
	            #{costPrice}
	        )
	    </insert>
	    <insert id="batchSave" parameterType="list">
	        insert into product (
	        <include refid="insertFiled" />
	        )
	        values
	        <!--separator 分隔符-->
	        <foreach collection="list" item="item" separator=",">
	            (
	                #{item.productName},
	                #{item.dir_id},
	                #{item.salePrice},
	                #{item.supplier},
	                #{item.brand},
	                #{item.cutoff},
	                #{item.costPrice}
	            )
	        </foreach>
	    </insert>
	    <!--修改-->
	    <update id="update" parameterType="Product">
	        update product
	        <!-- <set>:自动加，或减，-->
	        <set>
	            <if test="productName!=null and productName!=''">
	                productName =#{productName},
	            </if>
	            <if test="dir_id!=null">
	                dir_id =#{dir_id},
	            </if>
	            <if test="salePrice!=null">
	                salePrice =#{salePrice},
	            </if>
	            <if test="supplier!=null">
	                supplier=#{supplier},
	            </if>
	            <if test="brand!=null and productName!=''">
	                brand=#{brand},
	            </if>
	
	            <if test="cutoff!=null">
	                cutoff=#{cutoff},
	            </if>
	            <if test="costPrice!=null">
	                costPrice=#{costPrice}
	            </if>
	        </set>
	        where id=#{id}
	    </update>
	    <delete id="batchDelete" parameterType="list">
	        delete from product where id in
	        <foreach collection="list" item="item" separator="," open="(" close=")" index="index">
	            #{item}
	        </foreach>
	    </delete>
	</mapper>

测试

	public class TestMybatis {
	//查询单条
    @Test
    public void testSelectOne() throws Exception{
        SqlSession sqlSession = BaseUtil.getSqlSession();
        Product product = sqlSession.selectOne("Product.selectOne", 1L);
        System.err.println(product);
        sqlSession.close();
    }
    //查询全部
    @Test
    public void testSelestList(){
        SqlSession sqlSession = null;
        try {
            sqlSession = BaseUtil.getSqlSession();
            // 4.通过SQLsession 操作数据库
            //  String statement  找到命名空间的sql语句, Object parameter  参数
            List<Product> list = sqlSession.selectList("Product.selectAll");
            list.forEach(item -> System.err.println(item));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.关闭会话
            sqlSession.close();
        }
    }
    //批量保存
    @Test
    public void batchSave() throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = BaseUtil.getSqlSession();
            List<Product> list=new ArrayList<>();
            for (int i = 0; i <10 ; i++) {
                Product p=new Product();
                p.setProductName("产品"+i);
                list.add(p);
            }
            sqlSession.insert("Product.batchSave",list);
            sqlSession.commit();
        } catch (Exception e) {
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            // 5.关闭会话
            sqlSession.close();
        }
    }
    //修改
    @Test
    public void update() throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = BaseUtil.getSqlSession();
            Product product=new Product();
            product.setProductName("xxxx");
            product.setId(1L);
            sqlSession.update("Product.update",product);
            sqlSession.commit();
        } catch (Exception e) {
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            // 5.关闭会话
            sqlSession.close();
        }
    }
    //批量删除
    @Test
    public void batchDelete() throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = BaseUtil.getSqlSession();
            List<Integer> list = Arrays.asList(2,3,4,5,6);
            sqlSession.delete("Product.batchDelete",list);
            sqlSession.commit();
        } catch (Exception e) {
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            // 5.关闭会话
            sqlSession.close();
        }
    }
}