<?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="cn.netbuffer.ssmbootstrap_table.dao.IUserDao">
	<cache type="org.mybatis.caches.ehcache.LoggingEhcache"/>
	<resultMap id="BaseResultMap" type="cn.netbuffer.ssmbootstrap_table.model.User">
		<id column="id" property="id" jdbcType="BIGINT" />
		<result column="name" property="name" jdbcType="VARCHAR" />
		<result column="sex" property="sex" jdbcType="VARCHAR" />
		<result column="age" property="age" jdbcType="INTEGER" />
		<result column="phone" property="phone" jdbcType="VARCHAR" />
		<result column="deliveryaddress" property="deliveryaddress"
			jdbcType="VARCHAR" />
		<result column="adddate" property="adddate" jdbcType="INTEGER" />
		<association property="card" javaType="cn.netbuffer.ssmbootstrap_table.model.Card" select="selectCardByUserId" column="id"/>
		<!--ofType指collection包含的元素的类型，此属性不可少。
		column属性指把上述的selectByPrimaryKey的select语句中的id列的值作为参数
		传递给将要引用到的下述的selectAddressByUserId的select语句，此属性不可少。
		引用的形式为：命名空间.select语句id-->
		<collection property="addresses" ofType="cn.netbuffer.ssmbootstrap_table.model.Address" column="id" select="selectAddressByUserId"/>
	</resultMap>
	<!-- useCache="false" 禁用缓存 -->
	<select id="selectCardByUserId"  resultType="cn.netbuffer.ssmbootstrap_table.model.Card"
		parameterType="java.lang.Long">
		select * from card where user_id = #{id}
	</select>
	
	<select id="selectWhen"  resultType="cn.netbuffer.ssmbootstrap_table.model.User"
		parameterType="int">
		select * from user
		<where>
			id=
			<choose>
				<when test="id!=null">
					#{id}
				</when>
				<otherwise>
					1
				</otherwise>
			</choose>
		</where>
	</select>
	
	<!-- <resultMap id="address" type="Address">
        <id property="userId" column="user_id" />
        <result property="province" column="province" />
        <result property="city" column="city" />
    </resultMap> -->
    <!-- resultMap="address" -->
    <select id="selectAddressByUserId"  resultType="cn.netbuffer.ssmbootstrap_table.model.Address"
		parameterType="java.lang.Long">
		select * from address
		where user_id = #{id}
	</select>
    
	<sql id="Base_Column_List">
		id, name, sex, age, phone, deliveryaddress, adddate
	</sql>
	<select id="selectByPrimaryKey" resultMap="BaseResultMap"
		parameterType="java.lang.Long">
		select
		<include refid="Base_Column_List" />
		from user
		where id = #{id,jdbcType=BIGINT}
	</select>
	
	<select id="selectByName" resultMap="BaseResultMap"
		parameterType="java.lang.String">
		select
		<include refid="Base_Column_List" />
		from user
		where name = #{name}
	</select>

	<select id="getUserListCount" resultType="java.lang.Long">
		select count(*) from
		user
	</select>
	<select id="selectList" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List" />
		from user order by adddate ${order} limit #{offset},#{limit}
	</select>
	
	<select id="selectListWithQuery" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List" />
		from user where name like '%${search}%' or fristPinyin(name)=#{search} order by adddate ${order} limit #{offset},#{limit}
	</select>	

	<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
		delete from user
		where id = #{id,jdbcType=BIGINT}
	</delete>
	
	<delete id="deleteById" parameterType="java.lang.Long">
		delete from user where id = #{id,jdbcType=BIGINT}
	</delete>
	
	<!-- http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html -->
	<insert id="insert" parameterType="cn.netbuffer.ssmbootstrap_table.model.User">
		<selectKey keyProperty="id" resultType="java.lang.Long" order="AFTER">
			select LAST_INSERT_ID() as id
		</selectKey>
		insert into user (name, sex,
		age, phone, deliveryaddress,
		adddate)
		values (#{name,jdbcType=VARCHAR},
		#{sex,jdbcType=VARCHAR},
		#{age,jdbcType=INTEGER},
		#{phone,jdbcType=VARCHAR},
		#{deliveryaddress,jdbcType=VARCHAR},
		#{adddate,jdbcType=INTEGER})
	</insert>
	<!--#{id,jdbcType=BIGINT}, -->
	<insert id="insertSelective" parameterType="cn.netbuffer.ssmbootstrap_table.model.User">
		insert into user
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="id != null">
				id,
			</if>
			<if test="name != null">
				name,
			</if>
			<if test="sex != null">
				sex,
			</if>
			<if test="age != null">
				age,
			</if>
			<if test="phone != null">
				phone,
			</if>
			<if test="deliveryaddress != null">
				deliveryaddress,
			</if>
			<if test="adddate != null">
				adddate,
			</if>
		</trim>
		<trim prefix="values (" suffix=")" suffixOverrides=",">
			<if test="id != null">
				#{id,jdbcType=BIGINT},
			</if>
			<if test="name != null">
				#{name,jdbcType=VARCHAR},
			</if>
			<if test="sex != null">
				#{sex,jdbcType=VARCHAR},
			</if>
			<if test="age != null">
				#{age,jdbcType=INTEGER},
			</if>
			<if test="phone != null">
				#{phone,jdbcType=VARCHAR},
			</if>
			<if test="deliveryaddress != null">
				#{deliveryaddress,jdbcType=VARCHAR},
			</if>
			<if test="adddate != null">
				#{adddate,jdbcType=INTEGER},
			</if>
		</trim>
	</insert>
	<update id="updateByPrimaryKeySelective" parameterType="cn.netbuffer.ssmbootstrap_table.model.User">
		update user
		<set>
			<if test="name != null">
				name = #{name,jdbcType=VARCHAR},
			</if>
			<if test="sex != null">
				sex = #{sex,jdbcType=VARCHAR},
			</if>
			<if test="age != null">
				age = #{age,jdbcType=INTEGER},
			</if>
			<if test="phone != null">
				phone = #{phone,jdbcType=VARCHAR},
			</if>
			<if test="deliveryaddress != null">
				deliveryaddress = #{deliveryaddress,jdbcType=VARCHAR},
			</if>
			<if test="adddate != null">
				adddate = #{adddate,jdbcType=INTEGER},
			</if>
		</set>
		where id = #{id,jdbcType=BIGINT}
	</update>
	<update id="updateByPrimaryKey" parameterType="cn.netbuffer.ssmbootstrap_table.model.User">
		update user
		set
		name = #{name,jdbcType=VARCHAR},
		sex = #{sex,jdbcType=VARCHAR},
		age =
		#{age,jdbcType=INTEGER},
		phone = #{phone,jdbcType=VARCHAR},
		deliveryaddress = #{deliveryaddress,jdbcType=VARCHAR},
		adddate =
		#{adddate,jdbcType=INTEGER}
		where id = #{id,jdbcType=BIGINT}
	</update>
	<select id="getNewData" resultType="java.lang.Integer">
		select count(id) from user
		where DATE_FORMAT(NOW(),'%Y-%m-%d')=FROM_UNIXTIME(adddate,'%Y-%m-%d')
	</select>
	<resultMap type="java.util.HashMap" id="dataMap">
		<result column="adddate" property="key" />
	</resultMap>
	<select id="getDataSum" resultType="java.util.Map">
		select count(id) num,FROM_UNIXTIME(adddate,'%Y-%m-%d') adddate from user
		group by FROM_UNIXTIME(adddate,'%Y-%m-%d')
	</select>
	
	<select id="getUsersByIds" resultType="cn.netbuffer.ssmbootstrap_table.model.User"
		parameterType="java.util.List">
		select * from user 
		<where>
			id in
			<foreach collection="list" item="id" open="(" close=")" separator=",">
				#{id,jdbcType=NUMERIC}
			</foreach>
		</where>
	</select>
	
	<select id="selectUserByName" resultType="cn.netbuffer.ssmbootstrap_table.model.User"
		parameterType="java.util.Map">
		select * from user 
		<where>
			<if test="param1!=null">and name=#{param1}</if>
			<if test="param2!=null">and sex=#{param2}</if>
		</where>
	</select>
	
</mapper>
<!--java -jar mybatis-generator-core-1.3.2.jar -configfile generatorConfig.xml 
	-overwrite -->