mybatis执行sql的两种方式

 

 

 

https://blog.csdn.net/sihai12345/article/details/70311886

 

1.javabean类+xml文件（写sql语句）+dao+dao的实现类+测试类

其实可以（.javabean类+xml文件（写sql语句）+测试类）

 

 

 

代码：

javabean

package com.pojo;

import java.io.Serializable;
import java.util.Date;

public class Student implements Serializable {

	private Integer xh;
	private String xm;
	private Date cs;

	public Date getCs() {
		return cs;
	}

	public void setCs(Date cs) {
		this.cs = cs;
	}

	public Integer getXh() {
		return xh;
	}

	public void setXh(Integer xh) {
		this.xh = xh;
	}

	public String getXm() {
		return xm;
	}

	public void setXm(String xm) {
		this.xm = xm;
	}

}

2.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.dao.StudentDAO">
      <sql id="fields">
          xh, xm, cs
      </sql>
 
       <!-- 查所有学生 -->
       <select id="findAll" resultType="stu">
            select  <include refid="fields"></include>
            from   student
       </select>
       
       <!-- 查学号小于10的学生 -->
       <select id="findStudentLess10"  resultType="stu">
            select <include refid="fields" />
            from student 
            where xh &lt;= 10
       </select>
       
       
         <!-- 查学号小于9的学生 -->
       <select id="findStudentLess9"  resultType="stu">
            select <include refid="fields" />
            from student 
            where xh  <![CDATA[ <= ]]> 9
       </select>
       
       <!-- 根据主键查找 -->
       <select id="findById" parameterType="int" resultType="stu">
            select <include refid="fields" />
            from  student
            where xh=#{value}
       </select>
       
       <!-- 模糊查询1 -->
       <select id="findLikeName1"  parameterType="string"  resultType="stu">
            select <include refid="fields" />
            from student
            where xm like #{value}
       </select>
       
         <!-- 模糊查询2 -->
       <select id="findLikeName2"  parameterType="string"  resultType="stu">
            select <include refid="fields" />
            from `student`
            where xm like '${value}'
       </select>
       
         <!-- 模糊查询3 -->
       <select id="findLikeName3"  parameterType="string"  resultType="stu">
            select <include refid="fields" />
            from student
            where xm like  concat(#{value},'%')
       </select>
       
        <!-- 模糊查询4 -->
       <select id="findLikeName4"  parameterType="string"  resultType="stu">
            select <include refid="fields" />
            from student
            where xm like   '${value}%'
       </select>
       
       <!-- 学号在什么间 -->
       <select id="findBeTween"  parameterType="map"  resultType="stu">
            select <include refid="fields" />
            from student
            where xh between #{minXh} and #{maxXh}
       </select>
       
       <!-- 查所有学生 -->
       <select id="count"  resultType="int">
             select count(*) from student
       </select>
       
       <!-- 根据主键删除 -->
       <delete id="delById" parameterType="int">
             delete from student where xh=#{value}
       </delete>
       
       <!-- 动态sql -->
       <select id="dongtai_query1"  parameterType="map" resultType="stu">
            select <include refid="fields" />
            from  student
            <where>
                <if test="xh!=null">
                    xh=#{xh}
                </if>
                
                <if test="xm!=null">
                    and xm like concat(#{xm},'%')
                </if>
                
                <if test="minCs!=null">
                    and cs>=concat(#{minCs},' 00:00:00.000')
                </if>
                
                <if test="maxCs!=null">
                   and cs &lt;=concat(#{maxCs}, ' 23:59:59.999')
                </if>
            </where>
            limit 10
       </select>
       
       <!-- 如果stu某个属性为null则会用null覆盖表中数据 -->
       <update id="updateById1"  parameterType="stu">
             update student 
             set xm=#{xm}, cs=#{cs}
             where xh=#{xh}
       </update>
       
         <!-- 如果stu某个属性为null则会不覆盖表中数据 -->
       <update id="updateById2"  parameterType="stu">
             update student 
             <set>
                  <if test="xm!=null">
                     xm=#{xm}
                  </if>
                  
                  <if test="cs!=null">
                    ,cs=#{cs}
                  </if>
             </set>
             where xh=#{xh}
       </update>
       
       
       <!-- 动态查询之循环 
          select * from student where xh in (1,2,...)
       -->
       <select id="dongtai_query2" parameterType="list" resultType="stu">
            select <include refid="fields" />
            from student
            where 
                <choose>
                    <when test="list==null or  list.size()==0">
                            1=2
                    </when>
                    
                    <otherwise>
                            xh in 
                            <foreach collection="list"  item="xh" open="("  close=")"  separator=",">
                                 ${xh}
                            </foreach>
                    </otherwise>
                </choose>
       </select>
       
       
       
       
 </mapper>       
 
 
 
 
 
 
 
 

 

测试（session.selectList("com.dao.StudentDAO.findAll");里面写namespace和sql语句对应的id）

package com.test;

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

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.chainsaw.Main;

import com.pojo.Student;

public class 查所有学生 {

	public static void main(String[] args) throws IOException {
		// 1.读取总的配置文件
		Reader reader = Resources.getResourceAsReader("mybatis.xml");
		// 2.创建sessionfactory
		SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);

		// 3.打开session
		SqlSession session = sessionFactory.openSession();

		// 4.增删改查crud
		List<Student>  list = session.selectList("com.dao.StudentDAO.findAll");
		for (Student s : list) {
			System.out.println(s.getXh()+"\t"+s.getXm());
		}
		// 5.提交事务
		session.commit();

		// 6.关闭session
		session.close();

	}

}