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日志文件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
3..在src下创建Configuration.xml主配置文件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd" > 3 <configuration> 4 <environments default="development"> 5 <environment id="development"> 6 <transactionManager type="JDBC"/> 7 <dataSource type="POOLED"> 8 <property name="driver" value="oracle.jdbc.driver.OracleDriver"/> 9 <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"/> 10 <property name="username" value="system"/> 11 <property name="password" value="accp"/> 12 </dataSource> 13 </environment> 14 </environments> 15 <mappers> 16 <mapper resource="com/mapper/UserInfoMapper.xml"/> 17 </mappers> 18 </configuration>
4.在src下com.entity包下创建UserInfo.java
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package com.entity; 2 3 import java.util.ArrayList; 4 import java.util.List; 5 6 7 8 /** 9 * 用户信息表 10 * @author Dell 11 * 12 */ 13 public class UserInfo { 14 private Integer id; //编号 15 private String name; //姓名 16 private String password; //密码 17 private String telephone; //电话 18 private String isadmin; //是否是管理员 19 private List<Integer> ids=new ArrayList<Integer>(); 20 21 public UserInfo() { 22 } 23 24 public UserInfo(Integer id, String name, String password, String telephone, 25 String isadmin) { 26 this.id = id; 27 this.name = name; 28 this.password = password; 29 this.telephone = telephone; 30 this.isadmin = isadmin; 31 } 32 33 public UserInfo(String name, String password) { 34 this.name = name; 35 this.password = password; 36 } 37 38 public Integer getId() { 39 return id; 40 } 41 42 public void setId(Integer id) { 43 this.id = id; 44 } 45 46 public String getName() { 47 return name; 48 } 49 50 public void setName(String name) { 51 this.name = name; 52 } 53 54 public String getPassword() { 55 return password; 56 } 57 58 public void setPassword(String password) { 59 this.password = password; 60 } 61 62 public String getTelephone() { 63 return telephone; 64 } 65 66 public void setTelephone(String telephone) { 67 this.telephone = telephone; 68 } 69 70 public String getIsadmin() { 71 return isadmin; 72 } 73 74 public void setIsadmin(String isadmin) { 75 this.isadmin = isadmin; 76 } 77 78 79 public List<Integer> getIds() { 80 return ids; 81 } 82 83 public void setIds(List<Integer> ids) { 84 this.ids = ids; 85 } 86 87 @Override 88 public String toString() { 89 return "UserInfo [id=" + id + ", ids=" + ids + ", isadmin=" + isadmin 90 + ", name=" + name + ", password=" + password + ", telephone=" 91 + telephone + "]"; 92 } 93 94 95 96 97 98 }
5.在src下com.util包下创建MyBatisUtil.java
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
6.在src下com.mapper包下创建UserInfoMapper.java接口
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package com.mapper; 2 3 import java.util.List; 4 5 import com.entity.UserInfo; 6 /** 7 * 数据访问层接口 8 * @author pc 9 * 10 */ 11 public interface UserInfoMapper { 12 /** 13 * 1.根据用户信息查询某个用户 14 * @param userinfo 15 * @return 16 */ 17 UserInfo findNamePwd(UserInfo userinfo); 18 19 /** 20 * 2.添加用户信息 21 * @param userinfo 22 */ 23 void addUserInfo(UserInfo userinfo); 24 25 /** 26 * 3.查询所有 27 * @return 28 */ 29 List<UserInfo> findAll(UserInfo userinfo); 30 31 /** 32 * 4.删除 33 * @param id 34 */ 35 void deleteUser(int id); 36 /** 37 * 5.修改 38 * @param userinfo 39 */ 40 void updateUser(UserInfo userinfo); 41 42 /** 43 * 6.根据id查询 44 */ 45 UserInfo findId(int id); 46 }
7.在src下com.mapper包下创建UserInfoMapper.xml
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > 3 4 <mapper namespace="com.mapper.UserInfoMapper"> 5 <!-- 定义sql片段,该片段中不包含where,也就是定义公共sql部分 --> 6 <sql id="id_where"> 7 <if test="id!=null and id!=''"> 8 and id=#{id} 9 </if> 10 </sql> 11 12 <!-- 根据用户查询用户 --> 13 <select id="findNamePwd" parameterType="com.entity.UserInfo" resultType="com.entity.UserInfo"> 14 select * from userinfo 15 <!-- 动态sql --> 16 <where> 17 18 <!--name,password,这个参数是输入参数对象的 属性--> 19 <if test="name!=null and name!=''"> 20 and name=#{name} 21 </if> 22 <if test="password!=null and password!=''"> 23 and password=#{password} 24 </if> 25 </where> 26 27 </select> 28 29 <!-- 插入用户信息 --> 30 <insert id="addUserInfo" parameterType="com.entity.UserInfo"> 31 insert into userinfo values(seq_userinfo.nextval,#{name},#{password},#{telephone},#{isadmin}) 32 </insert> 33 34 35 <!-- 查询所有 --> 36 <select id="findAll" resultType="com.entity.UserInfo" parameterType="com.entity.UserInfo"> 37 select * from userinfo 38 <where> 39 <if test="ids!=null"> 40 <foreach collection="ids" item="id" open="and id in(" close=")" separator=","> 41 #{id} 42 </foreach> 43 </if> 44 </where> 45 </select> 46 47 <!-- 删除 --> 48 <delete id="deleteUser" parameterType="int"> 49 delete from userinfo 50 <!-- 动态sql中引用sql片段 --> 51 52 <where> 53 <!-- 引用sql片段的id,如果refid指定的不在本映射文件中,需要前面加namespace --> 54 <include refid="id_where"/> 55 <!-- 这里可以引用其他的sql片段 --> 56 </where> 57 where id=#{id} 58 </delete> 59 60 <!-- 修改 --> 61 <update id="updateUser" parameterType="com.entity.UserInfo"> 62 update userinfo set name=#{name},password=#{password},telephone=#{telephone},isadmin=#{isadmin} where id=#{id} 63 </update> 64 65 <!-- 根据id查询 --> 66 <select id="findId" parameterType="int" resultType="com.entity.UserInfo"> 67 select * from userinfo where id=#{id} 68 69 </select> 70 71 </mapper>
8.在src下com.test包下创建Test.java
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package com.test; 2 3 import java.util.ArrayList; 4 import java.util.List; 5 6 import org.apache.ibatis.session.SqlSession; 7 8 import com.entity.UserInfo; 9 import com.mapper.UserInfoMapper; 10 import com.util.MybatisUtil; 11 12 public class Test { 13 14 /** 15 * @param args 16 */ 17 public static void main(String[] args) { 18 SqlSession sqlSession=MybatisUtil.getSqlSession(false); 19 UserInfoMapper mapper=sqlSession.getMapper(UserInfoMapper.class); 20 UserInfo userinfo=new UserInfo("李杰", "123"); 21 // UserInfo info=mapper.findNamePwd(userinfo); 22 // if(info!=null){ 23 // System.out.println("登陆成功"); 24 // }else{ 25 // System.out.println("登陆失败"); 26 // 27 // } 28 29 List<Integer> ids=new ArrayList<Integer>(); 30 ids.add(22); 31 ids.add(23); 32 ids.add(24); 33 userinfo.setIds(ids); 34 35 List<UserInfo> userin=mapper.findAll(userinfo); 36 System.out.println(userin); 37 38 } 39 40 }