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日志文件

log4j.properties

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

 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>
Configuration.xml

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

 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 }
UserInfo.java
5.在src下com.util包下创建MyBatisUtil.java
MyBatisUtil.java

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

 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 }
UserInfoMapper.java

7.在src下com.mapper包下创建UserInfoMapper.xml

 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>
UserInfoMapper.xml

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

 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 }
Test.java
posted @ 2016-08-25 00:00  红酒人生  阅读(849)  评论(0)  编辑  收藏  举报