myBatis配置和测试（含模糊查询MAP）
myBatis配置和测试
SQL增删改需要提交事务commit
- 工具类 myBatisUtils
- 配置文件 ybatis-3-config.xml
- 返回类型 user
- 接口 UserDao
- sql绑定配置文件 userDao.xml
- 测试类 TEST
- maven过滤配置
可能会遇到的问题
- 配置文件没有注册
- 绑定接口错误。
- 方法名不对
- 返回类型不对
- Maven导出资源问题
模糊查询
1.Java代码执行的时候，传递通配符% %
List userList = mapper.getuserLike("%李%");
select * from mybatis.user where name like #{value}
2.在sql拼接中使用通配符!
List userList = mapper.getuserLike("李");
select * from mybatis.user where name like %"#{value}"%"
工具类
package utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
/**
* @author liu
*/
public class myBatisUtils {
private static SqlSessionFactory sqlSessionFactory =null;
static {
//mybatis第一步，获取sqlSessionFactory对象
try {
String resource = "mybatis-config.xml";
InputStream ips = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(ips);
} catch (IOException e) {
e.printStackTrace();
}
}
//既然有了sqlSessionFactory，顾名思义，可以创造SqlSession的实例
//SqlSession完全包含了面向数据库的所有sql命令和方法
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
mybatis-3-config.xml
<?xml version="1.0" encoding="UTF8" ?>
返回类型 user
package pojo;
public class User {
private int id;
private String name;
private String pwd;
public User(int id, String name, String pwd) {
this.id = id;
this.name = name;
this.pwd = pwd;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", pwd='" + pwd + '\'' +
'}';
}
}
做一个接口提交sql
package dao;
import pojo.User;
import java.util.List;
import java.util.Map;
public interface UserDao {
//查所有用户
List runSQL();
//查某个用户
List SQLgetID(int id);
//查某个用户
List SQLgetID2(Map map);
//insert一个用户
int addUser(User user);
//update修改用户
int updateUser(User user);
//如果对象参数很多，可以用万能的map去处理
int updateUser2(Map map);
//delete 删除用户
int deleteUser(int id);
}
userDao.xml
- Map传递参数，直接在sql中取出key即可!
- 对象传递参数，直接在sql中取对象的属性即可!
- 只有一个基本类型参数的情况下，可以直接在sql中取到!
SQL绑定接口和方法
注意sql语句的标签更改
<?xml version="1.0" encoding="UTF-8" ?>
insert into myBatis.user(id,name,pwd) value(#{id},#{name},#{password})
update myBatis.user set name=#{name},pwd=#{password} where id=#{id};
update myBatis.user set name=#{USERname},pwd=#{USERpassword} where id=#{USERid};
delete from myBatis.user where id=#{id};
测试
package dao;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.User;
import utils.myBatisUtils;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class daotest {
@Test
public void test01() {
//第一步：获得sqlSession对象
SqlSession sqlSession1 = myBatisUtils.getSqlSession();
//方式一：getMapper
UserDao mapper = sqlSession1.getMapper(UserDao.class);
List userList = mapper.runSQL();
for (User user : userList) {
System.out.println(user);
}
//关闭sqlSession
sqlSession1.close();
}
@Test
public void testSelect() {
//第一步：获得sqlSession对象
SqlSession sqlSession2 = myBatisUtils.getSqlSession();
UserDao mapper = sqlSession2.getMapper(UserDao.class);
List users = mapper.SQLgetID(2);
System.out.println(users);
//关闭sqlSession
sqlSession2.close();
}
@Test
public void testSelect2() {
//第一步：获得sqlSession对象
SqlSession sqlSession2 = myBatisUtils.getSqlSession();
UserDao mapper = sqlSession2.getMapper(UserDao.class);
Map map = new HashMap<>();
map.put("USERid",3);
map.put("USERname","ti");
List users = mapper.SQLgetID2(map);
System.out.println(users);
//关闭sqlSession
sqlSession2.close();
}
@Test
public void testadd() {
//第一步：获得sqlSession对象
SqlSession sqlSession3 = myBatisUtils.getSqlSession();
UserDao mapper = sqlSession3.getMapper(UserDao.class);
int num = mapper.addUser(new User(5, "ttt", "654"));
if (num > 0) {
//增删改需要提交事务
sqlSession3.commit();
//关闭sqlSession
sqlSession3.close();
}
}
@Test
public void updateUser() {
//第一步：获得sqlSession对象
SqlSession sqlSession4 = myBatisUtils.getSqlSession();
UserDao mapper = sqlSession4.getMapper(UserDao.class);
int num = mapper.updateUser(new User(4, "ttt", "654321"));
if (num > 0) {
//增删改需要提交事务
sqlSession4.commit();
//关闭sqlSession
sqlSession4.close();
}
}
@Test
public void updateUser2() {
//第一步：获得sqlSession对象
SqlSession sqlSession4 = myBatisUtils.getSqlSession();
UserDao mapper = sqlSession4.getMapper(UserDao.class);
Map map = new HashMap();
map.put("USERpassword","1234567");
map.put("USERid",3);
int num = mapper.updateUser2(map);
if (num > 0) {
//增删改需要提交事务
sqlSession4.commit();
//关闭sqlSession
sqlSession4.close();
}
}
@Test
public void deleteUser() {
//第一步：获得sqlSession对象
SqlSession sqlSession5 = myBatisUtils.getSqlSession();
UserDao mapper = sqlSession5.getMapper(UserDao.class);
int num = mapper.deleteUser(4);
if (num > 0) {
//增删改需要提交事务
sqlSession5.commit();
//关闭sqlSession
sqlSession5.close();
}
}
}
maven会过滤，需要加上配置