8.MyBatos的动态Sql

1.创建javaWeb项目MyBatis_dynamicSQL并在WebRoot下的WEB-INF下的lib下添加如下jar文件

cglib-nodep-2.1_3.jar

log4j-1.2.17.jar

mybatis-3.2.3.jar

ojdbc14.jar

创建如下userinfo.sql

SQL> create table userinfo
(id number(4),
name varchar2(50),
password varchar2(20
telephone varchar2(15),
isadmin varchar2(5));

SQL> --4.2 用户表序列
SQL> create sequence seq_userinfo;

SQL> alter table userinfo add constraint pk_userinfo_id primary key(id);

SQL> insert into userinfo values(seq_userinfo.nextval,'holly','123','134518024
','是');

SQL> commit;

2.在src下创建log4j.properties日志文件

3..在src下创建Configuration.xml主配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd" >
<configuration>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
         <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
         <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"/>
         <property name="username" value="system"/>
         <property name="password" value="accp"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="com/mapper/UserInfoMapper.xml"/>
  </mappers>
</configuration>

4.在src下com.entity包下创建UserInfo.java

package com.entity;

import java.util.ArrayList;
import java.util.List;



/**
 * 用户信息表
 * @author Dell
 *
 */
public class UserInfo {
    private Integer id; //编号
    private String name; //姓名
    private String password; //密码
    private String telephone; //电话
    private String isadmin; //是否是管理员
    private List<Integer> ids=new ArrayList<Integer>();
    
    public UserInfo() {
    }

    public UserInfo(Integer id, String name, String password, String telephone,
            String isadmin) {
        this.id = id;
        this.name = name;
        this.password = password;
        this.telephone = telephone;
        this.isadmin = isadmin;
    }

    public UserInfo(String name, String password) {
        this.name = name;
        this.password = password;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }

    public String getIsadmin() {
        return isadmin;
    }

    public void setIsadmin(String isadmin) {
        this.isadmin = isadmin;
    }
    

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }

    @Override
    public String toString() {
        return "UserInfo [id=" + id + ", ids=" + ids + ", isadmin=" + isadmin
                + ", name=" + name + ", password=" + password + ", telephone="
                + telephone + "]";
    }


    
    

}

5.在src下com.util包下创建MyBatisUtil.java

6.在src下com.mapper包下创建UserInfoMapper.java接口

package com.mapper;

import java.util.List;

import com.entity.UserInfo;
/**
 * 数据访问层接口
 * @author pc
 *
 */
public interface UserInfoMapper {
    /**
     * 1.根据用户信息查询某个用户
     * @param userinfo
     * @return
     */
    UserInfo findNamePwd(UserInfo userinfo);
    
    /**
     * 2.添加用户信息
     * @param userinfo
     */
    void addUserInfo(UserInfo userinfo);
    
    /**
     * 3.查询所有
     * @return
     */
    List<UserInfo> findAll(UserInfo userinfo);
    
    /**
     * 4.删除
     * @param id
     */
    void deleteUser(int id);
    /**
     * 5.修改
     * @param userinfo
     */
    void updateUser(UserInfo userinfo);
    
    /**
     * 6.根据id查询
     */
    UserInfo findId(int id);
}

7.在src下com.mapper包下创建UserInfoMapper.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.mapper.UserInfoMapper">
  <!-- 定义sql片段，该片段中不包含where，也就是定义公共sql部分 -->
  <sql id="id_where">
     <if test="id!=null and id!=''">
        and id=#{id}
     </if>
  </sql>

  <!-- 根据用户查询用户 -->
  <select id="findNamePwd" parameterType="com.entity.UserInfo" resultType="com.entity.UserInfo">
    select * from userinfo 
    <!-- 动态sql -->
    <where>
       
       <!--name，password，这个参数是输入参数对象的 属性-->
      <if test="name!=null and name!=''">
            and name=#{name} 
      </if>
      <if test="password!=null and password!=''">
            and password=#{password} 
      </if>
    </where>
  
  </select>
  
  <!-- 插入用户信息 -->
  <insert id="addUserInfo" parameterType="com.entity.UserInfo">
     insert into userinfo values(seq_userinfo.nextval,#{name},#{password},#{telephone},#{isadmin})
  </insert>

  
  <!-- 查询所有 -->
  <select id="findAll" resultType="com.entity.UserInfo" parameterType="com.entity.UserInfo">
    select * from userinfo
    <where>
      <if test="ids!=null">
         <foreach collection="ids" item="id" open="and id in(" close=")" separator=",">
            #{id}
         </foreach>
      </if>
    </where>
  </select>
  
   <!-- 删除 -->
   <delete id="deleteUser" parameterType="int">
     delete from userinfo
     <!-- 动态sql中引用sql片段 -->
     
     <where>
        <!-- 引用sql片段的id，如果refid指定的不在本映射文件中，需要前面加namespace -->
        <include refid="id_where"/>
        <!-- 这里可以引用其他的sql片段 -->
     </where>
      where id=#{id}
   </delete>
   
   <!-- 修改 -->
   <update id="updateUser" parameterType="com.entity.UserInfo">
     update userinfo set name=#{name},password=#{password},telephone=#{telephone},isadmin=#{isadmin} where id=#{id}
   </update>
   
   <!-- 根据id查询 -->
   <select id="findId" parameterType="int" resultType="com.entity.UserInfo">
    select * from userinfo where id=#{id}
    
   </select>
  
</mapper>

8.在src下com.test包下创建Test.java

package com.test;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.entity.UserInfo;
import com.mapper.UserInfoMapper;
import com.util.MybatisUtil;

public class Test {

    /**
     * @param args
     */
    public static void main(String[] args) {
        SqlSession sqlSession=MybatisUtil.getSqlSession(false);
        UserInfoMapper mapper=sqlSession.getMapper(UserInfoMapper.class);
        UserInfo userinfo=new UserInfo("李杰", "123");
//        UserInfo info=mapper.findNamePwd(userinfo);
//        if(info!=null){
//            System.out.println("登陆成功");
//        }else{
//            System.out.println("登陆失败");
//            
//        }
        
        List<Integer> ids=new ArrayList<Integer>();
        ids.add(22);
        ids.add(23);
        ids.add(24);
        userinfo.setIds(ids);
        
        List<UserInfo> userin=mapper.findAll(userinfo);
        System.out.println(userin);

    }

}