MyBatis对表的增删改查操作
主要有两种方式:基于XML实现和基于注解实现。
完整项目结构:
工具类:MyBatisUtil-------用于获取 sqlsession
1 package me.gacl.test; 2 3 import java.io.InputStream; 4 import org.apache.ibatis.session.SqlSession; 5 import org.apache.ibatis.session.SqlSessionFactory; 6 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 7 8 public class MyBatisUtil { 9 10 /** 11 * 获取SqlSessionFactory 12 * @return SqlSessionFactory 13 */ 14 15 public static SqlSessionFactory getSqlSessionFactory(){ 16 String resource = "conf.xml"; 17 InputStream is = MyBatisUtil.class.getResourceAsStream(resource); 18 SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is); 19 return factory; 20 } 21 22 /** 23 * 获取SqlSession 24 * @return SqlSesstion 25 * @throws 26 */ 27 public static SqlSession getSqlSession(){ 28 return getSqlSessionFactory().openSession(); 29 } 30 31 /** 32 * 获取SqlSession 33 * @param isAutoCommit 34 * true 表示创建的SqlSession对象在执行完SQL之后会自动提交事务 35 * false 表示创建的SqlSession对象在执行完SQL之后不会自动提交事务,这时就需要我们手动调用sqlSession.commit()提交事务 36 * @return SqlSession 37 */ 38 public static SqlSession getSqlSession(boolean isAutoCommit){ 39 return getSqlSessionFactory().openSession(isAutoCommit); 40 } 41 }
一、基于XML实现:(新建方法)
思路总结:
在 userMapper.xml 文件中进行增删改查实现,
在测试类中映射xml文件中的sql(包名.文件名前缀.方法id): String statement = "me.gacl.mapping.userMapper.addUser";
当新增成功,返回的retResult为1:int retResult = sqlSession.insert(statement,user);
代码实现:
conf.xml----配置数据库
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 <environments default="development"> 7 <environment id="development"> 8 <transactionManager type="JDBC"/> 9 <!-- 配置数据库连接信息 --> 10 <dataSource type="POOLED"> 11 <property name="driver" value="com.mysql.jdbc.Driver" /> 12 <property name="url" value="jdbc:mysql://localhost:3306/mybatis" /> 13 <property name="username" value="root" /> 14 <property name="password" value="liyangkun123" /> 15 </dataSource> 16 </environment> 17 </environments> 18 <mappers> 19 <mapper resource = "me/gacl/mapping/userMapper.xml" ></mapper> 21 </mappers> 22 23 </configuration>
数据库驱动:<property name="driver" value="com.mysql.jdbc.Driver" />
<mapper />==<mapper></mapper>
userMapper.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,namespace的值习惯上设置成包名+sql映射文件名,这样就能够保证namespace的值是唯一的 5 例如namespace="me.gacl.mapping.userMapper"就是me.gacl.mapping(包名)+userMapper(userMapper.xml文件去除后缀)--> 6 7 <mapper namespace="me.gacl.mapping.userMapper"> 8 9 <!-- 在select标签中编写查询的SQL语句, 设置select标签的id属性为getUser,id属性值必须是唯一的,不能够重复使用parameterType属性指明查询时 10 使用的参数类型,resultType属性指明查询返回的结果集类型resultType="me.gacl.domain.User"就表示将查询结果封装成一个User类的对象返回 11 User类就是users表所对应的实体类--> 12 13 <!-- 根据id查询得到一个user对象 --> 14 <select id="getUser" parameterType="int" resultType="me.gacl.domain.User"> 15 select * from users where id=#{id} 16 </select> 17 18 <!-- 新增用户(Create) --> 19 <insert id="addUser" parameterType="me.gacl.domain.User"> 20 insert into users(name,age)values(#{name},#{age}) 21 </insert> 22 23 <!-- 修改用户(Update) --> 24 <update id="updateUser" parameterType="me.gacl.domain.User"> 25 update users set name=#{name},age=#{age} where id=#{id} 26 </update> 27 28 <!-- 删除用户(Delete) --> 29 <delete id="deleteUser" parameterType="int"> 30 delete from users where id=#{id} 31 </delete> 32 33 <!-- 查询所有用户 --> 34 <select id="getAllUsers" resultType="me.gacl.domain.User"> 35 insert * from users 36 </select> 37 38 39 </mapper>
测试类:TestCRUDByXmlMapper----单元测试验证
1 package me.gacl.test; 2 3 import org.apache.ibatis.session.SqlSession; 4 import org.junit.Test; 5 6 import me.gacl.domain.User; 7 8 public class TestCRUDByXmlMapper { 9 10 11 12 @Test 13 public void testAdd(){ 14 SqlSession sqlSession = MyBatisUtil.getSqlSession(); 15 String statement = "me.gacl.mapping.userMapper.addUser"; 16 User user = new User(); 17 user.setName("用户孤傲苍狼"); 18 user.setAge(20); 19 20 int retResult = sqlSession.insert(statement, user); 21 sqlSession.close(); 22 System.out.println(retResult); 23 } 24 25 @Test 26 public void testUpdate(){ 27 SqlSession sqlSession = MyBatisUtil.getSqlSession(); 28 String statement = "me.gacl.mapping.userMapper.updateUser"; 29 User user = new User(); 30 user.setId(2); 31 user.setName("zhu"); 32 user.setAge(25); 33 34 int retResult = sqlSession.update(statement, user); 35 sqlSession.close(); 36 System.out.println(retResult); 37 } 38 39 @Test 40 public void testdeleteUser(){ 41 SqlSession sqlSession = MyBatisUtil.getSqlSession(true); 42 String statement = "me.gacl.mapping.userMapper.deleteUser"; 43 44 int retResult =sqlSession.delete(statement,1); 45 sqlSession.close(); 46 System.out.println(retResult); 47 } 48 }
SqlSession sqlSession = MyBatisUtil.getSqlSession(true);//获取session(内有链接数据库的配置),连上数据库,才能进行操作。
二、基于注解实现:(新建方法)
写一个接口类定义sql映射,并且我们不用写接口实现,而是由MyBatis动态构建,我们只需要直接拿来使用即可。
接下来将接口类注册到配置文件中(conf.xml) :
借鉴大神代码:
<!-- 注册UserMapper映射接口-->
<mapper class="me.gacl.mapping.UserMapperI"/>
下一步测试类测试:
注意在新增之前要获得 sqlsession并且open,操作之后要close。
创建接口实例,
获取操作对象,修改操作信息
然后调用增删改查方法执行(借用大神代码)
代码实现:
UserMapper.java----增删改查接口
1 package me.gacl.mapping; 2 3 import java.util.List; 4 5 import org.apache.ibatis.annotations.Delete; 6 import org.apache.ibatis.annotations.Insert; 7 import org.apache.ibatis.annotations.Select; 8 import org.apache.ibatis.annotations.Update; 9 10 import me.gacl.domain.User; 11 12 public interface UserMapperI { 13 14 15 @Insert("insert into users(name,age) value(#{name},#{age})") 16 public int add(User user); 17 18 @Delete("delete from users where id=#{id}") 19 public int deleteById(int id); 20 21 @Select("select * from users where id=#{id}") 22 public User selectById(int id); 23 24 @Update("update users set name=#{name},age=#{age} where id=#{id}") 25 public int updateById(User user); 26 27 @Select("select * from users") 28 public List<User> getAll(); 29 }
注册到conf.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> <!-- 引用db.properties配置文件 --> <properties resource="me/gacl/test/db.properties" /> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <!-- 配置数据库连接信息 --> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/mybatis" /> <property name="username" value="root" /> <property name="password" value="liyangkun123" /> </dataSource> </environment> </environments> <mappers> <mapper resource = "me/gacl/mapping/userMapper.xml" ></mapper> <mapper class="me.gacl.mapping.UserMapperI" /> </mappers> </configuration>
resource和class为包+名完整路径
TestCRUDByAnnotationMapper.java------测试类
1 package me.gacl.test; 2 3 import java.util.List; 4 5 import org.apache.ibatis.session.SqlSession; 6 import org.junit.Test; 7 8 import me.gacl.domain.User; 9 import me.gacl.mapping.UserMapperI; 10 import me.gacl.test.MyBatisUtil; 11 12 public class TestCRUDByAnnotationMapper { 13 14 @Test 15 public void testAdd(){ 16 SqlSession sqlSession = MyBatisUtil.getSqlSession(true); 17 UserMapperI mapper = sqlSession.getMapper(UserMapperI.class); 18 User user = new User(); 19 user.setName("用户xdp"); 20 user.setAge(29); 21 int add = mapper.add(user); 22 23 sqlSession.close(); 24 System.out.println(add); 25 } 26 27 @Test 28 public void testDelete(){ 29 SqlSession sqlSession = MyBatisUtil.getSqlSession(true); 30 UserMapperI mapper = sqlSession.getMapper(UserMapperI.class); 31 int delete = mapper.deleteById(1); 32 sqlSession.close(); 33 System.out.println(delete); 34 } 35 36 @Test 37 public void testUpdate(){ 38 SqlSession sqlSession = MyBatisUtil.getSqlSession(true); 39 UserMapperI mapper = sqlSession.getMapper(UserMapperI.class); 40 User user = new User(); 41 user.setId(2); 42 user.setName("Linda"); 43 user.setAge(23); 44 45 int update = mapper.updateById(user); 46 sqlSession.close(); 47 System.out.println(update); 48 } 49 50 @Test 51 public void testSelect(){ 52 SqlSession sqlSession = MyBatisUtil.getSqlSession(true); 53 UserMapperI mapper = sqlSession.getMapper(UserMapperI.class); 54 User user = mapper.selectById(2); 55 sqlSession.close(); 56 System.out.println(user); 57 58 } 59 60 @Test 61 public void testSelectAll(){ 62 SqlSession sqlSession = MyBatisUtil.getSqlSession(true); 63 UserMapperI mapper = sqlSession.getMapper(UserMapperI.class); 64 List<User> user = mapper.getAll(); 65 66 sqlSession.close(); 67 System.out.println(user); 68 69 } 70 }
注意事项:MyBatisUtil.java,conf.xml, 放到 util 包中时,调用出错,放到同一个包中,没有问题。
大神总结链接:http://www.cnblogs.com/xdp-gacl/p/4262895.html