说明
复制《mybatis-crul(增删改查)》代码。删除UserMapper.xml文件
第一步:修改mybatis-config.xml
<!-- 配置映射文件 -->
<mappers>
<mapper class="com.mybatis.mapper.UserMapper"/>
</mappers>
第二步:创建动态Sql类
package com.mybatis.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;
import com.mybatis.pojo.User;
public class UserSqlProvider {
public String selectByCondition(User user) {
SQL sql=new SQL();
sql.SELECT("*");
sql.FROM("user");
if (user.getUsername()!=null) {
sql.WHERE("username like concat('%',#{username},'%')");
}
if (user.getAge()!=null) {
sql.OR();
sql.WHERE("age = #{age}");
}
System.out.println("sql语句:"+sql.toString());
return sql.toString();
}
public String selectTotalByCondition(User user) {
SQL sql=new SQL();
sql.SELECT("count(*)");
sql.FROM("user");
if (user.getUsername()!=null) {
sql.WHERE("username like concat('%',#{username},'%')");
}
if (user.getAge()!=null) {
sql.OR();
sql.WHERE("age = #{age}");
}
System.out.println("sql语句:"+sql.toString());
return sql.toString();
}
public String updateUserByNotNull(User user) {
SQL sql=new SQL();
sql.UPDATE("user");
sql.SET("id=#{id}");
if (user.getUsername()!=null) {
sql.SET("username=#{username}");
}
if (user.getPassword()!=null) {
sql.SET("password=#{password}");
}
if (user.getAge()!=null) {
sql.SET("age=#{age}");
}
sql.WHERE("id=#{id}");
return sql.toString();
}
public String deleteByIds(@Param("ids")Integer[] ids) {
SQL sql=new SQL();
sql.DELETE_FROM("user");
StringBuffer sb=new StringBuffer();
sb.append("id in (");
for (int i = 0; i < ids.length; i++) {
sb.append("#{ids["+i+"]},");
}
sb.deleteCharAt(sb.length()-1);
sb.append(")");
sql.WHERE(sb.toString());
return sql.toString();
}
public String insertByBatch(@Param("users")List<User> users) {
SQL sql=new SQL();
StringBuffer sb=new StringBuffer();
for (int i = 0; i < users.size(); i++) {
sb.append("(#{users["+i+"].username},#{users["+i+"].password},#{users["+i+"].age}),");
}
sb.deleteCharAt(sb.length()-1);
sql.INSERT_INTO("user(username,password,age) values"+sb.toString());
return sql.toString();
}
}
第三步:修改UserMapper.java
package com.mybatis.mapper;
import java.util.List;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;
import com.mybatis.pojo.User;
public interface UserMapper {
@SelectProvider(type =UserSqlProvider.class ,method = "selectByCondition")
List<User> selectByCondition(User user);
@SelectProvider(type =UserSqlProvider.class ,method = "selectTotalByCondition" )
Long selectTotalByCondition(User user);
@UpdateProvider(type = UserSqlProvider.class,method = "updateUserByNotNull")
int updateUserByNotNull(User user);
@DeleteProvider(type = UserSqlProvider.class,method = "deleteByIds")
int deleteByIds(@Param("ids")Integer[] ids);
@InsertProvider(type = UserSqlProvider.class,method = "insertByBatch")
int insertByBatch(@Param("users")List<User> users);
}
第四步:测试代码
package com.mybatis.test;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.mybatis.mapper.UserMapper;
import com.mybatis.pojo.User;
import com.mybatis.util.MyBatisUtil;
public class UserMapperTest {
@Test
public void testSelectByCondition() {
SqlSession session = MyBatisUtil.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User conditionUser = new User();
conditionUser.setAge(30);
List<User> list = userMapper.selectByCondition(conditionUser);
for (User user : list) {
System.out.println(user);
}
session.close();
}
@Test
public void testSelectTotalByCondition() {
SqlSession session = MyBatisUtil.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User conditionUser = new User();
conditionUser.setAge(30);
Long total = userMapper.selectTotalByCondition(conditionUser);
System.out.println(total);
session.close();
}
@Test
public void testUpdateUserByNotNull() {
SqlSession session = MyBatisUtil.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User conditionUser = new User();
conditionUser.setId(3);
userMapper.updateUserByNotNull(conditionUser);
session.commit();
session.close();
}
@Test
public void testDeleteByIds() {
SqlSession session = MyBatisUtil.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
Integer[] ids = { 47, 48, 49, 50 };
userMapper.deleteByIds(ids);
session.commit();
session.close();
}
@Test
public void testInsertByBatch() {
SqlSession session = MyBatisUtil.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
List<User> list=new ArrayList<User>();
User user=null;
for (int i = 0; i < 10; i++) {
user=new User(null,"admin_"+i,"enen",23);
list.add(user);
}
int row = userMapper.insertByBatch(list);
System.out.println("受影响行数:"+row);
session.commit();
session.close();
}
}