myBaits SQL

最新推荐文章于 2024-08-10 23:14:03 发布

weixin_34010949

最新推荐文章于 2024-08-10 23:14:03 发布

阅读量59

点赞数

文章标签： python java

原文链接：https://my.oschina.net/dslcode/blog/1591219

版权

2019独角兽企业重金招聘Python工程师标准>>>

myBaits SQL

增：

void addService(Service service);

<insert id="addService" parameterType="com.dsl.entity.Service">
insert into service_DSL (service_id,status,account_id,cost_id,unix_host,os_username,login_passwd)
values (service_seq_DSL.nextval,1,#{account_id},#{cost_id},#{unix_host},#{os_username},#{login_passwd})
</insert>

void save(Cost cost);

<insert id="save" parameterType="com.dsl.entity.Cost">

   insert into cost_DSL values(cost_seq_DSL.nextval,
                        #{name,jdbcType=VARCHAR},
                        #{base_duration,jdbcType=INTEGER},
                        #{base_cost,jdbcType=DOUBLE},
                        #{unit_cost,jdbcType=DOUBLE},
                        #{status,jdbcType=CHAR},
                        #{descr,jdbcType=VARCHAR},
                        #{creatime,jdbcType=TIMESTAMP},
                        #{startime,jdbcType=TIMESTAMP},
                        #{cost_type,jdbcType=CHAR})
</insert>

void addAdmin(Admin admin);

<insert id="addAdmin" parameterType="com.dsl.entity.Admin">
   <selectKey keyProperty="admin_id" order="BEFORE" resultType="Integer">
       select admin_seq_DSL.nextval from dual
    </selectKey>
   insert into admin_info_DSL (admin_id,admin_code,password,name,telephone,email)
   values (#{admin_id},#{admin_code},#{password},#{name},#{telephone},#{email})
</insert>

int insertSelective(User record);

<insert id="insertSelective" parameterType="com.cn.hnust.pojo.User" >
insert into user_t
<trim prefix="(" suffix=")" suffixOverrides="," >
    <if test="id != null" >
      id,
    </if>
    <if test="userName != null" >
      user_name,
    </if>
    <if test="password != null" >
      password,
    </if>
    <if test="age != null" >
      age,
    </if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
    <if test="id != null" >
      #{id,jdbcType=INTEGER},
    </if>
    <if test="userName != null" >
      #{userName,jdbcType=VARCHAR},
    </if>
    <if test="password != null" >
      #{password,jdbcType=VARCHAR},
    </if>
    <if test="age != null" >
      #{age,jdbcType=INTEGER},
    </if>
</trim>
</insert>

删：

void delete(Integer id);

<delete id="delete" parameterType="Integer">
delete from cost_DSL where cost_id=#{id}
</delete>

改：

void modify(Cost cost);

<update id="modify" parameterType="com.dsl.entity.Cost">
   update cost_DSL set name=#{name},
                  cost_type=#{cost_type},
                  base_duration=#{base_duration,jdbcType=INTEGER},
                  base_cost=#{base_cost,jdbcType=DOUBLE},
                  unit_cost=#{unit_cost,jdbcType=DOUBLE},
                  descr=#{descr,jdbcType=VARCHAR}
    where cost_id=#{cost_id}
</update>

void modifyAccount(Account account);

<update id="modifyAccount" parameterType="com.dsl.entity.Account">
   update account_DSL
   <set>
      <if test='real_name!=null && !real_name.equals("")'>
         real_name=#{real_name,jdbcType=VARCHAR},
      </if>
      <if test='login_passwd!=null && !login_passwd.equals("")'>
         login_passwd=#{login_passwd,jdbcType=VARCHAR},
      </if>
      <if test='telephone!=null && !telephone.equals("")'>
         telephone=#{telephone,jdbcType=VARCHAR},
      </if>
      <if test='recommender_id!=null && !recommender_id.equals("")'>
         recommender_id=#{recommender_id,jdbcType=INTEGER},
      </if>
      <if test='occupation!=null && !occupation.equals("")'>
         occupation=#{occupation,jdbcType=VARCHAR},
      </if>
      <if test='mailaddress!=null && !mailaddress.equals("")'>
         mailaddress=#{mailaddress,jdbcType=VARCHAR},
      </if>
      <if test='zipcode!=null && !zipcode.equals("")'>
         zipcode=#{zipcode,jdbcType=VARCHAR},
      </if>
      <if test='qq!=null && !qq.equals("")'>
         qq=#{qq,jdbcType=VARCHAR},
      </if>
   </set> where account_id=#{account_id}
</update>

void resetPwd(Map<String,Object> map);

<update id="resetPwd" parameterType="hashmap">
   update admin_info_DSL set password=#{defaultPwd} where admin_id in
   <foreach collection="pwdList"
      item="pwd"
      open="("
      separator=","
      close=")">
      #{pwd}
   </foreach>
</update>

查：

Cost findById(Integer id);

<select id="findById" parameterType="Integer" resultType="com.dsl.entity.Cost">
select * from cost_DSL where cost_id=#{id}
</select>

List<Account> findByPage(AccountPage page);

<select id="findByPage" parameterType="com.dsl.entity.page.AccountPage" resultType="com.dsl.entity.Account">
   select * from(select a.*,row_number() over(order by account_id)p from account_DSL a
   <where>
      <if test='idcardNo!=null && !idcardNo.equals("")'>
         and idcard_no=#{idcardNo}
      </if>
      <if test='realName!=null && !realName.equals("")'>
         and real_name=#{realName}
      </if>
      <if test='loginName!=null && !loginName.equals("")'>
         and login_name=#{loginName}
      </if>
      <if test='status!=null && !status.equals("") && !status.equals("-1")'>
         and status=#{status}
      </if>
   </where>)where p between #{begin} and #{end}
</select>

List<Admin> findAdminByPage(AdminPage page);

<select id="findAdminByPage" parameterType="com.dsl.entity.page.AdminPage" resultMap="adminMap">
   select * from(select a.*,row_number() over(order by admin_id)p from admin_info_DSL a
   where admin_id in (
            select ai.admin_id from admin_info_DSL ai
            left join admin_role_DSL ar on ar.admin_id=ai.admin_id
            left join role_info_DSL ri on ri.role_id=ar.role_id
            left join role_module_DSL rm on rm.role_id=ri.role_id
            left join module_info_DSL mi on mi.module_id=rm.module_id
            <where>
               <if test='roleName!=null&&!roleName.equals("")'>
                  and ri.name like '%'||#{roleName}||'%'
               </if>
               <if test="moduleId!=null">
                  and mi.module_id=#{moduleId}
               </if>
            </where>
         )) where p between #{begin} and #{end}
</select>
<resultMap id="adminMap" type="com.dsl.entity.Admin">
   <id column="admin_id" property="admin_id"/>
   <collection select="findRoleByAdminId"
            column="admin_id"
            javaType="arraylist"
            ofType="com.dsl.entity.Role"
            property="roles">
   </collection>
</resultMap>
<select id="findRoleByAdminId" parameterType="Integer" resultType="com.dsl.entity.Role">
   select * from role_info_DSL where role_id in(select role_id from admin_role_DSL where admin_id=#{admin_id})
</select>

User selectByPrimaryKey(Integer id);

<resultMap id="BaseResultMap" type="com.cn.hnust.pojo.User" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="user_name" property="userName" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
</resultMap>
<sql id="Base_Column_List" >
id, user_name, password, age
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from user_t
where id = #{id,jdbcType=INTEGER}
</select>