mybatis 一二事（一）

 1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE configuration 3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 5 <configuration> 6     <!-- 和spring整合后 environments配置将废除--> 7     <environments default="development"> 8         <environment id="development"> 9             <transactionManager type="JDBC" />10             <dataSource type="POOLED">11                 <property name="driver" value="com.mysql.jdbc.Driver" />12                 <property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8" />13                 <property name="username" value="root" />14                 <property name="password" value="root" />15             </dataSource>16         </environment>17     </environments>18     19     <!-- 配置mapper映射文件 -->20     <mappers>21         <mapper resource="sqlmap/User.xml"/>22     </mappers>23 </configuration>

 1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper 3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 5 <!-- namespace命名空间特殊作用： 如果使用mapper动态代理方法，这里就需要配置mapper接口地址--> 6  7 <mapper namespace="test"> 8     <!-- 根据用户id查询一条记录（返回单条记录） --> 9     <!-- 10     select标签表示sql查询，内容会封装到Mapped Statement中。11     可以将这个select标签称为一个Statement12     id：Statement的id，用于标识select中定义的 sql，id是在同一个命名空间中不允许重复13     #{}：表示一个占位符，避免sql注入14     parameterType：表示输入参数的类型15     resultType：表示输出 结果集单条记录映射的java对象类型，select查询的字段名和resultType中属性名一致，才能映射成功。16     #{value}：value表示parameter输入参数的变量，如果输入参数是简单类型，使用#{}占位符，变量名可以使用value或其它的名称 17     18      -->19     <select id="findUserById" parameterType="int" resultType="com.mybatis001.bean.User">20         21         SELECT * FROM USER WHERE id = #{id}22     23     </select>24     25     26     <!-- 查询用户列表（返回list集合） -->27     <!-- 28     不管结果集查询一条还是多条，resultType指定结果集单条记录映射的java对象类型29     ${}：表示sql拼接，相当于sql字符串拼接，无法避免sql注入30     ${value}：value表示parameter输入参数的变量，如果输入参数是简单类型，使用${}拼接符，变量名必须使用value31     ${value}直接 将value获取到拼接在sql中，value值不加任何修饰32      -->33     <select id="findUserList" parameterType="java.lang.String" resultType="com.mybatis001.bean.User" >34         select * from user where name like '%${value}%'35     </select>36     37     38     <!-- 添加用户39     parameterType：如果parameterType指定 是pojo，在#{}中指定 pojo的属性名获取该pojo的属性值 40      -->41     <insert id="insertUser" parameterType="com.mybatis001.bean.User">42     43     <!-- 44     keyProperty：将主键设置到pojo中哪个属性中45     order：selectKey中sql执行的时机46     resultType:selectKey中sql执行的结果类型47     LAST_INSERT_ID:是insert后获取自增主键值 48      -->49      <!-- 这样的写法，在执行后，会再user这个entity中设置id的值 -->50         <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">51             select LAST_INSERT_ID()52         </selectKey>53         54         insert into user(name,age,sex) values(#{name},#{age},#{sex})55     </insert>56     57     <!-- 根据主键删除用户 -->58     59     <delete id="deleteUser" parameterType="int">60        delete from user where id=#{id}61     </delete>62     63     <!-- 根据主键用户更新64     更新传入输入参数见容：id和更新的信息65      -->66     <update id="updateUser" parameterType="com.mybatis001.bean.User">67        update user set name=#{name},age=#{age},sex=#{sex}  where id=#{id}68     </update>69     70 71 </mapper>

 1 package com.mybatis001.bean; 2  3 public class User { 4  5     private int id; 6     private String name; 7     private int age; 8     private String sex; 9     10     public User() {11         super();12     }13 14     public User(String name, int age, String sex) {15         super();16         this.name = name;17         this.age = age;18         this.sex = sex;19     }20     21     public int getId() {22         return id;23     }24     public void setId(int id) {25         this.id = id;26     }27     public String getName() {28         return name;29     }30     public void setName(String name) {31         this.name = name;32     }33     public int getAge() {34         return age;35     }36     public void setAge(int age) {37         this.age = age;38     }39     public String getSex() {40         return sex;41     }42     public void setSex(String sex) {43         this.sex = sex;44     }45     46     @Override47     public String toString() {48         return "User [id=" + id + ", name=" + name + ", age=" + age + ", sex="49                 + sex + "]";50     }51     52 }

 1 package com.mybatis001.dao; 2  3 import java.util.List; 4  5 import com.mybatis001.bean.User; 6  7 public interface UserDao { 8      9     public User findUserById(int id) throws Exception;10     11     public List<User> findUserList(String name) throws Exception;12     13     public Integer insertUser(User user) throws Exception;14     15     public void deleteUser(int id) throws Exception;16     17     public void updateUser(User user) throws Exception;18     19 }

 1 package com.mybatis001.dao.impl; 2  3 import java.util.List; 4  5 import org.apache.ibatis.session.SqlSession; 6 import org.apache.ibatis.session.SqlSessionFactory; 7  8 import com.mybatis001.bean.User; 9 import com.mybatis001.dao.UserDao;10 11 public class UserDaoImpl implements UserDao {12 13     // 注入SqlSessionFactory14     private SqlSessionFactory sqlSessionFactory;15 16     public UserDaoImpl(SqlSessionFactory sqlSessionFactory) {17         this.sqlSessionFactory = sqlSessionFactory;18     }19 20     @Override21     public User findUserById(int id) throws Exception {22 23         // 根据SqlSessionFactory创建SqlSession24 25         SqlSession sqlSession = sqlSessionFactory.openSession();26 27         // 通过sqlSession查询用户信息(发起数据库操作)28         // 第一个参数statement：指定mapper映射文件中statement的id，指定 时需要前边加上statement所属的命名空间29         // 第二个参数parameter，指定 输入参数30         // selectOne返回的是单条记录，如果select返回多条记录(list集合)，使用selectOne会报错31         // 根据映射文件中的resultType指定输出类型32         User user = sqlSession.selectOne("test.findUserById", id);33         return user;34     }35 36     @Override37     public List<User> findUserList(String name) throws Exception {38         SqlSession sqlSession = sqlSessionFactory.openSession();39         List<User> userList = sqlSession.selectList("test.findUserList", name);40         return userList;41     }42 43     @Override44     public Integer insertUser(User user) throws Exception {45         SqlSession sqlSession = sqlSessionFactory.openSession();46         int lastUserId = sqlSession.insert("test.insertUser", user);47         48         sqlSession.commit();49         sqlSession.close();50         return lastUserId;51     }52 53     @Override54     public void deleteUser(int id) throws Exception {55         SqlSession sqlSession = sqlSessionFactory.openSession();56         sqlSession.delete("test.deleteUser", id);57         sqlSession.commit();58         sqlSession.close();59     }60 61     @Override62     public void updateUser(User user) throws Exception {63         SqlSession sqlSession = sqlSessionFactory.openSession();64         sqlSession.update("test.updateUser", user);65         sqlSession.commit();66         sqlSession.close();67     }68 69 }

 1 package com.test.mybatis001; 2  3  4 import java.io.InputStream; 5 import java.util.List; 6  7 import org.apache.ibatis.io.Resources; 8 import org.apache.ibatis.session.SqlSessionFactory; 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder;10 import org.junit.Before;11 import org.junit.Test;12 13 import com.mybatis001.bean.User;14 import com.mybatis001.dao.UserDao;15 import com.mybatis001.dao.impl.UserDaoImpl;16 17 public class UserDaoImplTest {18 19     // 会话工厂20     private SqlSessionFactory sqlSessionFactory;21 22     @Before23     public void setUp() throws Exception {24         // 加载配置文件25         String resource = "SqlMapConfig.xml";26         InputStream inputStream = Resources.getResourceAsStream(resource);27 28         // 根据mytais的配置创建SqlSessionFactory29         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);30     }31 32     @Test33     public void testFindUserById() throws Exception {34         UserDao userDao = new UserDaoImpl(sqlSessionFactory);35         User user = userDao.findUserById(1001);36 //        System.out.println(user.toString());37         38         List<User> userList = userDao.findUserList("lee");39         40         if (!userList.isEmpty()) {41             for (User u : userList) {42                 System.out.println(u.toString());43             }44         }45     }46     47     @Test48     public void insertUser() throws Exception {49         UserDao userDao = new UserDaoImpl(sqlSessionFactory);50         51         User u1 = new User("nathan.lee.salvatore", 18, "男");52         User u2 = new User("nathan", 22, "男");53         User u3 = new User("leechenxiang", 20, "男");54         User u4 = new User("李晨翔", 19, "男");55         56         userDao.insertUser(u1);57         userDao.insertUser(u2);58         userDao.insertUser(u3);59         userDao.insertUser(u4);60     }61 62     @Test63     public void deleteUser() throws Exception {64         UserDao userDao = new UserDaoImpl(sqlSessionFactory);65         userDao.deleteUser(1013);66     }67     68     @Test69     public void updateUser() throws Exception {70         UserDao userDao = new UserDaoImpl(sqlSessionFactory);71         User user = new User("李斯涵", 1, "女");72         user.setId(1001);73         userDao.updateUser(user);74     }75 }