创建POJO类
POJO, Plain Ordinary Java Object 简单的Java对象 ,只含有属性、Getter/Setter、构造方法等 ,仅用于表示一条数据记录,没有其他作用。
在 MyBatis里,POJO类对应于一条数据记录
比如,Student类与student表是对应的
1.属性 <-->列名
2.属性类型<-->列值类型
package my;
public class Student
{
public Integer id;
public String name;
public Boolean sex;
public String cellphone;
public Integer getId()
{
return id;
}
public void setId(Integer id)
{
this.id = id;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public Boolean getSex()
{
return sex;
}
public void setSex(Boolean sex)
{
this.sex = sex;
}
public String getCellphone()
{
return cellphone;
}
public void setCellphone(String cellphone)
{
this.cellphone = cellphone;
}
}
public static void main(String[] args) throws Exception
{
// 创建 SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 使用简化的try-with-resources语法,等效于try..finally语句
try (SqlSession session = sqlSessionFactory.openSession())
{
Student row = session.selectOne("af.test.getStudent", 20190001);
if(row != null)
System.out.println("找到一条记录");
}
System.out.println("Exit");
}
MyBatis 在查询后,通过反射技术将查询的结果映射为一个POJO对象。
Student row = session.selectOne("af.test.getStudent", 20190001);
注意:
1.表的列名的规范命名,因为列名最终要映射为Setter方法名,所以命名必须规范。
示例:numLike timeExpired
2.列的类型应该对应
比如,列值为bigint,POJO属性可以为Long或String
3.POJO属性类型建议使用包装类
例如,Long, Integer, Boolean, Double
查询多条记录
MyBatis 提供的查询方法主要分为两类
- selectOne () : 查询单条记录
如果结果有一条记录,返回这条记录 。如果结果没有记录,返回null。 如果结果有2条或以上记录,抛出异常 。所以,调用者需要确信本查询不会返回多条记录。
- selectList () : 查询多条记录
返回一个List,包含查到的结果 。List的长度可以为0,表示没有查到记录。
1.修改mybatis-mapper.xml
在Mapper XML可以指定多条查询,每一条语句的ID应该不同
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="af.test">
<select id="getStudent" resultType="my.Student">
select * from student where id = #{id}
</select>
<select id="getStudent2" resultType="my.Student">
select * from student where sex=#{sex}
</select>
</mapper>
2.测试
package my;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class Test
{
public static void main(String[] args) throws Exception
{
// 创建 SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 使用简化的try-with-resources语法,等效于try..finally语句
// 取得单条记录 (0条或1条)
try (SqlSession session = sqlSessionFactory.openSession())
{
Student row = session.selectOne("af.test.getStudent", 20190001);
if (row == null)
System.out.println("没有记录!");
else
System.out.println("找到1条记录");
}
System.out.println("***********************");
// 得得多条记录 (0条或N条)
try (SqlSession session = sqlSessionFactory.openSession())
{
List<Student> rows = session.selectList("af.test.getStudent2", 0);
System.out.println("记录数: " + rows.size());
}
System.out.println("Exit");
}
}
结果:
指定查询参数
在 MyBatis 的 Mapper XML 中 用 #{var} 来指定一个参数, 然后在代码里调用时,可以传入真实的参数值。
- 当 SQL中不带参数时: 使用 selectOne ( statement ) , selectList ( statement )
- 当 SQL中带1个或多个参数时: 使用 selectOne(statement, parameter) , selectList (statement, parameter)
参考官方文档: Parameter Object, which can be a primitive (auto-boxed or wrapper), a JavaBean, a POJO or a Map
- 在写SQL语句时,不要加单引号 ,因为它用的是PreparedStatement
select * from student where sex=#{sex} and name like #{name}
//不能写作:
select * from student where sex=#{sex} and name like '#{name}'
1.修改mybatis-mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="af.test">
<select id="getStudent" resultType="my.Student">
select * from student where
id = #{id}
</select>
<select id="getStudent2" resultType="my.Student">
select * from student where
sex=#{sex}
</select>
<select id="getStudent3" resultType="my.Student">
select * from student where sex=0
</select>
<select id="getStudent4" resultType="my.Student">
select * from student where sex=#{sex} and name like #{name}
</select>
</mapper>
2.测试
package my;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class Test
{
public static void main(String[] args) throws Exception
{
// 创建 SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 使用简化的try-with-resources语法,等效于try..finally语句
// 不需要指定参数的情况
try (SqlSession session = sqlSessionFactory.openSession())
{
List<Student> rows = session.selectList("af.test.getStudent3");
System.out.println("记录数: " + rows.size());
}
System.out.println("*****************");
// 指定多个参数的情况
try (SqlSession session = sqlSessionFactory.openSession())
{
Map<String, Object> params = new HashMap<>();
params.put("sex", 0);
params.put("name", "%穆%");
List<Student> rows = session.selectList("af.test.getStudent4", params);
System.out.println("记录数: " + rows.size());
}
System.out.println("Exit");
}
}
3.结果
返回Map记录
一般来说,要求定义一个POJO类,与表完全一致,然后返回一个POJO,但是有时候不想定义POJO,或者不方便定义,例如多表查询时返回的结果。
如果不定义POJO类,也可以用HashMap来接收一行记录。 其中,resultType为 hashmap( resultType 里是小写的hashmap, 这在 MyBatis里叫做别名 Alias )注意不要写错,表示将结果转成HashMap。
<select id="getStudent" resultType="hashmap">
select * from student where id = #{id}
</select>
查询并取得单条记录:
Map map = session.selectOne("af.test.getStudent", 20190001);
查询并取得多条记录:
List<Map> rows = session.selectList("af.test.getStudent2", 0);
内部实现的大致流程: 查询,取得多行结果,结果中包含列名和列值,有名字也值,所以可以存到HashMap里。于是,每一行结果转成了一个HashMap对象。
1.修改mybatis-mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="af.test">
<select id="getStudent" resultType="hashmap">
select * from student where id = #{id}
</select>
<select id="getStudent2" resultType="hashmap">
select * from student where sex=#{sex}
</select>
</mapper>
2.测试
package my;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class Test
{
public static void main(String[] args) throws Exception
{
// 创建 SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 使用简化的try-with-resources语法,等效于try..finally语句
try (SqlSession session = sqlSessionFactory.openSession())
{
Map map = session.selectOne("af.test.getStudent", 20190001);
System.out.println("haha");
}
try (SqlSession session = sqlSessionFactory.openSession())
{
List<Map> rows = session.selectList("af.test.getStudent2", 0);
System.out.println("记录数: " + rows.size());
}
System.out.println("Exit");
}
}
3.结果
每一行结果转成了一个HashMap对象
常见问题
在XML里,规定特殊字符需要转义
比如,在XML里定义一条SQL:
select * from student where id <= 20190006
在mybatis-mapper.xml里面需要这样写
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="af.test">
<select id="getStudent" resultType="my.Student">
SELECT * FROM student WHERE id >= #{from} AND id<= #{to}
</select>
</mapper>
package my;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class Test
{
public static void main(String[] args) throws Exception
{
// 创建 SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 使用简化的try-with-resources语法,等效于try..finally语句
try (SqlSession session = sqlSessionFactory.openSession())
{
HashMap<String, Object> para=new HashMap<>();
para.put("from", 20190003);
para.put("to",20190006);
List<Student> row=session.selectList("af.test.getStudent", para);
for(Student sss : row)
{
System.out.println(sss.id+","+sss.name);
}
}
System.out.println("Exit");
}
}
结果
输出日志
在项目里添加MyBatis 日志输出,以辅助调试,在日志里能看到:内部构造的SQL语句、参数值、返回值
- 添加日志支持:
1.添加 log4j-1.2.17.jar 到 lib下
2.添加 log4j.properties 到 src 下
# C,R is name of the following appenders
log4j.logger.af.test=DEBUG,C,R
log4j.appender.C=org.apache.log4j.ConsoleAppender
# Set the appender threshold to INFO
log4j.appender.C.Threshold=DEBUG
log4j.appender.C.layout=org.apache.log4j.PatternLayout
log4j.appender.C.layout.ConversionPattern=%-d{yyyy-MM-dd HH:mm:ss} %-5p [XYZ] %c %x - %m%n
# logging to file, rolling
# - change the log file's name and path, located in tomcat path
log4j.appender.R=org.apache.log4j.RollingFileAppender
log4j.appender.R.Threshold=DEBUG
log4j.appender.R.File=c:/mylogs/mybatis-example.log
log4j.appender.R.MaxFileSize=4000KB
# Keep one backup file
log4j.appender.R.MaxBackupIndex=1
log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%-d{yyyy-MM-dd HH:mm:ss} %-5p %c %x - %m%n
3.修改 log4j 配置,添加Mapper的namespace
log4j.logger.x.y.z=DEBUG,C,R x.y.z对应Mapper的namespace
返回结果时控制台显示如下