mybatis简单使用

1.数据库配置 jdbc.properties

mysql.jdbc.url=jdbc:mysql://10.202.13.167:3307/td_basic?useUnicode=true&characterEncoding=utf8&allowMultiQueries=truemysql.jdbc.username=td_basicmysql.jdbc.password=sf123456maxActive=15initialSize=1maxWait=60000minIdle=1validationQuery=SELECT 'x'  testWhileIdle=true  testOnBorrow=false  testOnReturn=falsetimeBetweenEvictionRunsMillis=25000minEvictableIdleTimeMillis=300000removeAbandoned=true  removeAbandonedTimeout=18000  logAbandoned=true

2.加入配置文件

   <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">        <property name="locations">            <list>                <value>classpath:jdbc.properties</value>            </list>        </property>    </bean>

3.dao.mapper，和工厂的配置，工厂包括了数据库的连接设置以及xml文件扫描路径的配置

<?xml version="1.0" encoding="UTF-8" ?><beans xmlns="http://www.springframework.org/schema/beans"       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"       xsi:schemaLocation="    http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">        <property name="driverClassName" value="com.mysql.jdbc.Driver" />        <property name="url" value="${mysql.jdbc.url}" />        <property name="username" value="${mysql.jdbc.username}" />        <property name="password" value="${mysql.jdbc.password}" />        <property name="maxActive" value="${maxActive}" />        <property name="initialSize" value="${initialSize}" />        <property name="maxWait" value="${maxWait}" />        <property name="minIdle" value="${minIdle}" />        <property name="validationQuery" value="${validationQuery}" />        <property name="testWhileIdle" value="${testWhileIdle}" />        <property name="testOnBorrow" value="${testOnBorrow}" />        <property name="testOnReturn" value="${testOnReturn}" />        <!-- 配置连接闲置时间，单位是毫秒  申请连接时检查连接是否闲置超时时用到该参数 -->        <property name="timeBetweenEvictionRunsMillis" value="${timeBetweenEvictionRunsMillis}" />        <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->        <property name="minEvictableIdleTimeMillis" value="${minEvictableIdleTimeMillis}" />        <!-- 打开removeAbandoned功能 对于长时间不使用的连接强制关闭 -->        <!--property name="removeAbandoned" value="${removeAbandoned}" /-->        <!-- 1800秒，也就是连接超过30分钟未关闭则开始关闭连接 -->        <!--property name="removeAbandonedTimeout" value="${removeAbandonedTimeout}" /-->        <!-- 关闭abanded连接时输出错误日志 -->        <!--property name="logAbandoned" value="${logAbandoned}" /-->    </bean>    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">        <property name="mapperLocations" value="classpath*:/sqlmap/*" />        <property name="dataSource" ref="dataSource" />        <!-- <property name="typeHandlers">            <array>                <bean class="com.sf.ccsp.basic.svc.client.dao.demo.CryptTypeHandler" />            </array>        </property> -->    </bean>    <!-- ScanMapperFiles -->    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">        <property name="basePackage" value="com.sf.demo.server.dao.mapper"/>        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>    </bean></beans>

4创建实体类：

public class OperatorInformation implements Serializable {    private int id;    private String emp_name;    private String organization_id;    private String role_id;    private Date create_time;    private Date update_time;    public int getId() {        return id;    }    public String getEmp_name() {        return emp_name;    }    public String getOrganization_id() {        return organization_id;    }    public String getRole_id() {        return role_id;    }    public Date getCreate_time() {        return create_time;    }    public Date getUpdate_time() {        return update_time;    }    public void setEmp_name(String emp_name) {        this.emp_name = emp_name;    }    public void setId(int id) {        this.id = id;    }    public void setOrganization_id(String organization_id) {        this.organization_id = organization_id;    }    public void setRole_id(String role_id) {        this.role_id = role_id;    }    public void setCreate_time(Date create_time) {        this.create_time = create_time;    }    public void setUpdate_time(Date update_time) {        this.update_time = update_time;    }}

5.创建mapperdao

public interface OperatorInformationMapper {    List<OperatorInformation> getList();    void insertInfo(OperatorInformation information);    void deleteInfo(int id);    void updateInfo(OperatorInformation operatorInformation);    List<BothFind> findBoth(int id);}

6。xml实现dao，mapper为接口类地址，resultmap为对应实体类，column为在数据库中的属性，proverty为在实体类中的属性。具体增删改查的id名为mapper接口类中的方法名，一对一关联为association标签，一对多用collection，可嵌套。

<?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.sf.demo.server.dao.mapper.OperatorInformationMapper">    <resultMap id="OperatorInformationMap" type="com.sf.demo.server.dao.domain.OperatorInformation">        <result column="create_time" property="create_time" jdbcType="TIMESTAMP"/>        <result column="update_time" property="update_time" jdbcType="TIMESTAMP"/>        <result column="id" property="id" jdbcType="INTEGER"/>        <result column="emp_code" property="emp_name" jdbcType="VARCHAR"/>        <result column="organization_id" property="organization_id" jdbcType="VARCHAR" />        <result column="role_id" property="role_id" jdbcType="VARCHAR" />    </resultMap>    <select id="getList" resultMap="OperatorInformationMap">        select * from td_emp_info    </select>    <insert id="insertInfo" parameterType="com.sf.demo.server.dao.domain.OperatorInformation"            useGeneratedKeys="true" keyProperty="id">        insert into td_emp_info(id,create_time,update_time,emp_code,organization_id,role_id)        values (#{id,jdbcType=INTEGER},#{create_time,jdbcType=TIMESTAMP},#{update_time,jdbcType=TIMESTAMP},#{emp_name,jdbcType=VARCHAR},#{organization_id,jdbcType=VARCHAR},#{role_id,jdbcType=VARCHAR})    </insert>    <delete id="deleteInfo" parameterType="INTEGER">        delete from td_emp_info where id=#{id}    </delete>    <update id="updateInfo" parameterType="com.sf.demo.server.dao.domain.OperatorInformation">        update td_emp_info set organization_id=#{organization_id} where id=#{id};    </update>    <resultMap id="ContainerInformation" type="com.sf.demo.server.dao.domain.ContainerInformation">        <result column="id" property="id" jdbcType="INTEGER"/>        <result column="container_id" property="container_id" jdbcType="VARCHAR"/>    </resultMap><resultMap id="tdContainer" type="com.sf.demo.server.dao.domain.TDContainerInformation">    <result column="id" property="id" jdbcType="INTEGER"/>    <result column="container_id" property="container_id" jdbcType="VARCHAR"/></resultMap>    <resultMap id="bothfind" type="com.sf.demo.server.dao.domain.BothFind">        <result column="id" property="id" jdbcType="INTEGER"/>        <result column="organization_id" property="organization_id" jdbcType="VARCHAR"/>        <association property="information"  javaType="com.sf.demo.server.dao.domain.ContainerInformation" resultMap="ContainerInformation"/>        <collection property="tdContainerInformationList"  ofType="com.sf.demo.server.dao.domain.TDContainerInformation" resultMap="tdContainer"/>    </resultMap>    <select id="findBoth" resultMap="bothfind"    parameterType="INTEGER">        select * from (td_emp_info c, td_container_rel_waybill t) LEFT JOIN  td_container_info d ON d.container_id="0" where c.id=t.id and c.id=#{id};    </select></mapper>

7，调用：通过注解方法，调用接口方法传入相关参数即可

  @Autowired    OperatorInformationMapper operatorInformationMapper;//   @Transactional    @Override    public List<BothFind> getOperatorInformation() {       // add user information        OperatorInformation information=new OperatorInformation();        information.setId(111);        information.setEmp_name("zzzz4");        information.setOrganization_id("11");        information.setEmp_name("0");        information.setCreate_time(new Date(System.currentTimeMillis()));        information.setUpdate_time(new Date(System.currentTimeMillis()));        //operatorInformationMapper.insertInfo(information);        System.out.println("add success");        //delete user information        //        operatorInformationMapper.deleteInfo(1161);        //        System.out.println("delete success");        //update information//        information.setOrganization_id("22");//        operatorInformationMapper.updateInfo(information);        //        System.out.println("update success");        List<BothFind> operatorInformationList=new ArrayList<BothFind>();      //  List<OperatorInformation> operatorInformationList=new ArrayList<OperatorInformation>();        try {            operatorInformationList = operatorInformationMapper.findBoth(11);           // operatorInformationList = operatorInformationMapper.getList();            logger.debug("success", operatorInformationList);        } catch (Exception e) {            logger.error("error", e);        }        return operatorInformationList;    }

foreach使用：

    <select id="countCrateGroupNumByList" resultMap="postCountMapper">    select post_no, count(*) as count from tdop_crate_task  <where>    task_no in    <foreach item="taskNo" collection="crateNoList" separator="," open="(" close=")" index="">      #{taskNo, jdbcType=VARCHAR}    </foreach>     and slice_id=#{sliceId}   </where>  group by post_no</select>

    List<CratePostandCountResp> countCrateGroupNumByList(@Param("crateNoList")List<String> crateNoList,@Param("sliceId") String sliceId);

foreach属性

属性 描述
item 循环体中的具体对象。支持属性的点路径访问，如item.age,item.info.details。 具体说明：在list和数组中是其中的对象，在map中是value。 该参数为必选。

collection 要做foreach的对象，
作为入参时，List<>.对象默认用list代替作为键，数组对象有array代替作为键，Map对象用map代替作为键。 当然在作为入参时可以使用@Param(“keyName”)来设置键，设置keyName后，list,array,map将会失效。 除了入参这种情况外，还有一种作为参数对象的某个字段的时候。举个例子： 如果User有属性List ids。入参是User对象，那么这个collection = “ids” 如果User有属性Ids ids;其中Ids是个对象，Ids有个属性List id;入参是User对象，那么collection = “ids.id” 上面只是举例，具体collection等于什么，就看你想对那个元素做循环。 该参数为必选。

separator 元素之间的分隔符，例如在in()的时候，separator=”,”会自动在元素中间用“,“隔开，避免手动输入逗号导致sql错误，如in(1,2,)这样。该参数可选。
open foreach代码的开始符号，一般是(和close=”)”合用。常用在in(),values()时。该参数可选。
close foreach代码的关闭符号，一般是)和open=”(“合用。常用在in(),values()时。该参数可选。
index 在list和数组中,index是元素的序号，在map中，index是元素的key，该参数可选。