mybatids的常用标签和动态查询

基于spring+mybatis实现动态查询：

applicationContext.xml      spring配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                       http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
                            http://www.springframework.org/schema/context
                            http://www.springframework.org/schema/context/spring-context-2.5.xsd">


    <!--开启自动扫描-->
    <context:component-scan base-package="com.dao"/>

    <!--载入配置文件-->
    <context:property-placeholder location="classpath:c3p0.properties"/>

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <property name="driverClass" value="${driverClass}"/>
        <property name="jdbcUrl" value="${jdbcUrl}"/>
        <property name="user" value="${user}"/>


        <!--初始化和最大最小连接数-->
        <property name="initialPoolSize" value="${initialPoolSize}"/>
        <property name="maxPoolSize" value="${max_size}"/>
        <property name="minPoolSize" value="${min_size}"/>

        <property name="acquireIncrement" value="${acquireIncrement}"/>
        <property name="acquireRetryAttempts" value="${acquireRetryAttempts}"/>
        <property name="idleConnectionTestPeriod" value="${idleConnectionTestPeriod}"/>
        <property name="maxIdleTime" value="${maxIdleTime}"/>
        <property name="acquireRetryDelay" value="${acquireRetryDelay}"/>
        <property name="autoCommitOnClose" value="${autoCommitOnClose}"/>
    </bean>


    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="mapperLocations" value="classpath:mapper/studentMapper.xml"/>
        <property name="typeAliasesPackage" value="com.model"/>
    </bean>
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage"  value="com.dao"/>
    </bean>

    <bean id="sqlSessionTemplate"
          class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg index="0" ref="sqlSessionFactory"/>
    </bean>

    <!--配置jdbcDataSource事物管理器 -->
    <bean id="transactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>


</beans>

 

 

 

mapper映射文件

 

<?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">

    <resultMap id="StudentMap" type="com.model.Student">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="qq" property="qq"/>
        <result column="admission_time" property="admissionTime"/>
        <result column=" graduate_school" property=" graduateSchool"/>
        <result column="daily_num" property="dailyNum"/>
        <result column="daily_url" property="dailyUrl"/>
        <result column="declaration" property="declaration"/>
        <result column="elder" property="elder"/>
        <result column="knew_from" property="knewFrom"/>
        <result column="create_at" property="create_at"/>
        <result column="update_at" property="update_at"/>
    </resultMap>

    <select id="selectById" parameterType="int" resultMap="StudentMap">
             SELECT * FROM student
             <where>
                 <if test="id!=null">
                     id=#{id}
                 </if>
             </where>
    </select>
    <!--查询所有-->
    <select id="findAll" parameterType="com.model.Student" resultMap="StudentMap">
             SELECT * FROM student
    </select>
    <!--根据列查询 名字/学号-->
    <select id="selectByColumn"   resultMap="StudentMap">
             SELECT * FROM student
        <trim prefix="WHERE" prefixOverrides="AND |OR ">
            <if test="name!=null">
                name = #{name}
            </if>
            <if test="dailyNum!=null">
                AND dailyNum = #{dailyNum}
            </if>
        </trim>
    </select>
    <!--根据列查询 所有列名-->
    <select id="selectByStudent" parameterType="com.model.Student" resultMap="StudentMap">
            SELECT *FROM student
        <trim prefix="WHERE" prefixOverrides="AND|OR">
            <if test="id!=null and id!=0">
                  AND id = #{id}
            </if>
            <if test="name!=null">
                  AND name = #{name}
            </if>
            <if test="qq!=null">
                  AND qq = #{qq}
            </if>
            <if test="type!=null">
                  AND type = #{type}
            </if>
            <if test="admissionTime!=null">
                  AND admissionTime = #{admissionTime}
            </if>
            <if test="graduateSchool!=null">
                  AND graduateSchool = #{graduateSchool}
            </if>
            <if test="dailyNum!=null">
                  AND dailyNum = #{dailyNum}
            </if>
            <if test="dailyUrl!=null">
                  AND dailyUrl = #{dailyUrl}
            </if>
            <if test="declaration!=null">
                  AND declaration = #{declaration}
            </if>
            <if test="elder!=null">
                  AND elder = #{elder}
            </if>
            <if test="knewFrom!=null">
                  AND knewFrom = #{knewFrom}
            </if>
            <if test="create_at!=null and create_at!=0">
                  AND create_at = #{create_at}
            </if>
            <if test="update_at!=null and update_at!=0">
                  AND update_at = #{update_at}
            </if>
        </trim>
    </select>
    <!--插入单条数据-->
    <insert id="insertStudent" parameterType="com.model.Student" useGeneratedKeys="true">
          INSERT INTO student
          (id,name,qq,type,admission_time,graduate_school,
           daily_num,daily_url,declaration,elder,knew_from,create_at,update_at)
          values
          (#{id},#{name},#{qq},#{type},#{admissionTime},#{graduateSchool},
           #{dailyNum},#{dailyUrl},#{declaration},#{elder},#{knewFrom},#{createAt},#{updateAt})
        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
                 --select LAST_INSERT_ID()
                 select @@IDENTITY AS ID
        </selectKey>
    </insert>
    <!--创建=插入数据-->
    <insert id="createStudent" parameterType="com.model.Student" useGeneratedKeys="true">
             INSERT INTO student
            (id,name,qq,type,admission_time,graduate_school,daily_num,
             daily_url,declaration,elder,knew_from,create_at,update_at)
             values
             (#{id},#{name},#{qq},#{type},#{admissionTime},#{graduateSchool},
             #{dailyNum},#{dailyUrl},#{declaration},#{elder},#{knewFrom},#{create_at},#{update_at})
    </insert>
    <!--插入多条数据-->
    <insert id="insertStudentList" parameterType="com.model.Student"  useGeneratedKeys="true">
        INSERT INTO student
        (id,name,qq,type,admission_time,graduate_school,daily_num,daily_url,
        declaration,elder,knew_from,create_at,update_at)
        values
        <foreach  item="item" collection="list" separator="," index = "index">
           (#{item.id},#{item.name},#{item.qq},#{item.type},#{item.admissionTime},
            #{item.graduateSchool},#{item.dailyNum},#{item.dailyUrl},#{item.declaration},
            #{item.elder},#{item.knewFrom},#{item.create_at},#{item.update_at})
        </foreach>
    </insert>
    <!--修改数据-->
    <update id="updateStudent" parameterType="com.model.Student">
         UPDATE student
         SET
         name=#{name},type=#{type},admission_time=#{admissionTime},graduate_school=#{graduateSchool},
             daily_num=#{dailyNum},daily_url=#{dailyUrl},declaration=#{declaration},
             elder=#{elder},knew_from=#{knewFrom},create_at=#{create_at},update_at=#{update_at}
         WHERE
         id=#{id}
    </update>
    <!--删除单条数据-->
    <delete id="deleteStudent" parameterType="int"  >
        DELETE FROM student WHERE id = #{id}
    </delete>
</mapper>

实体类：

 

 

package com.model;

public class Student {
    private int id;
    private String name;
    private long qq;
    private String type;
    private String admission_time;
    private String graduate_school;
    private String daily_num;
    private String daily_url;
    private String declaration;
    private String elder;
    private String knew_from;
    private long create_at;
    private long update_at;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public long getQq() {
        return qq;
    }

    public void setQq(long qq) {
        this.qq = qq;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getAdmission_time() {
        return admission_time;
    }

    public void setAdmission_time(String admission_time) {
        this.admission_time = admission_time;
    }

    public String getGraduate_school() {
        return graduate_school;
    }

    public void setGraduate_school(String graduate_school) {
        this.graduate_school = graduate_school;
    }

    public String getDaily_num() {
        return daily_num;
    }

    public void setDaily_num(String daily_num) {
        this.daily_num = daily_num;
    }

    public String getDaily_url() {
        return daily_url;
    }

    public void setDaily_url(String daily_url) {
        this.daily_url = daily_url;
    }

    public String getDeclaration() {
        return declaration;
    }

    public void setDeclaration(String declaration) {
        this.declaration = declaration;
    }

    public String getElder() {
        return elder;
    }

    public void setElder(String elder) {
        this.elder = elder;
    }



    public String getKnew_from() {
        return knew_from;
    }

    public void setKnew_from(String knew_from) {
        this.knew_from = knew_from;
    }

    public long getCreate_at() {
        return create_at;
    }

    public void setCreate_at(long create_at) {
        this.create_at = create_at;
    }

    public long getUpdate_at() {
        return update_at;
    }

    public void setUpdate_at(long update_at) {
        this.update_at = update_at;
    }
@Override
    public String toString() {
        return "Student{"+
                "id"+id+
                ",name='"+name+'\''+
                ",qq"+qq+
                ",type"+type+
                ",admission_time"+admission_time+
                ",graduate_school"+graduate_school+
                ",daily_num"+daily_num+
                ",daily_url"+daily_url+
                ",declaration"+declaration+
                ",elder"+elder+
                ",knew_from"+knew_from+
                ",create_at="+create_at+
                ",update_at="+update_at+
                '}';

}
}

接口

 

 

package com.dao;

import com.model.Student;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface StudentDao {
    //查询所有
    public List<Student> findAll();
    //根据id查询
    Student selectById(@Param("id") int id);
    //    根据参数查询 名字/学号
    public List<Student> selectByColumn(@Param("name") String name,@Param("dailyNum") int daily_num);
    //    根据对象属性传值查询
    public List<Student> selectByStudent(Student student);
    //修改
    int updateStudent(Student student);
    //删除
    int deleteStudent(int id);
    //插入
    int insertStudent(Student student);
    //创建=插入
    void createStudent(Student student);
    //批量插入
    int insertStudentList(List<Student>list);


}

service

 

 

package com.service;

import com.dao.StudentDao;
import com.model.Student;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class StudentService extends SqlSessionDaoSupport implements StudentDao {
    @Autowired
    public void setSessionFactory(SqlSessionFactory sqlSessionFactory){
        super.setSqlSessionFactory(sqlSessionFactory);
    }
    public Student selectById(int id) {
        return this.getSqlSession().selectOne("mapper.studentMapper.selectById",id);
    }
    @Override
    public List<Student> findAll() {
        return this.getSqlSession().selectList("mapper.studentMapper.findAll");
    }
    public List<Student> selectByColumn(String name,int dailyNum) {
        return this.getSqlSession().selectList("mapper.studentMapper.selectByColumn");
    }

    public List<Student> selectByStudent(Student student) {
        return this.getSqlSession().selectList("mapper.studentMapper..selectByStudent");
    }


    @Override
    public int updateStudent(Student student) {
        return this.getSqlSession().update("mapper.studentMapper.updateStudent",student);
    }
    @Override
    public int deleteStudent(int id) {
        return this.getSqlSession().delete("mapper.studentMapper.deleteStudent",id);
    }
    @Override
    public int insertStudent(Student student) {
        return this.getSqlSession().insert("mapper.studentMapper.insertStudent",student);

    }
    @Override
    public void createStudent(Student student) {
        this.getSqlSession().insert("mapper.studentMapper.createStudent",student);
    }
    @Override
    public int insertStudentList(List<Student> list) {
        return this.getSqlSession().insert("mapper.studentMapper.insertStudentList",list);
    }
}

感谢大家观看！