admin 管理员组文章数量: 886993
Mybatis(Ibatis)基础
Mybatis配置第一步:导入jdbc的jar(mysql-connector-5.0.3-bin.jar)
第二步:导入mybatis的jar(mybatis-3.2.8.jar)
第三步:创建数据库、表
mysql> create database mybatis;
Query OK, 1 row affected (0.01 sec)
mysql> use mybatis;
Database changed
mysql>create table tb_user(
id int(11) primary key auto_increment,
name varchar(18) Default null,
sex char(2) default null,
age int(11) default null
);
第四步:持久化对象POJO--User.java
public class User {
//用户标识
private Integer id;
//用户名称
private String name;
//用户年龄
private Integer age;
//用户性别
private String sex;
//无参构造器
public User() {
// TODO Auto-generated constructor stub
super();
}
//有参构造器
public User(String name, String sex, int age) {
// TODO Auto-generated constructor stub
super();
this.name = name;
this.sex = sex;
this.age = age;
}
//省略setter和getter方法
}
第五步:UserMapper.xml映射文件(命名规则:POJO+Mapper.xml)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis//DTD Mapper 3.0//EN" "
mapper.dtd">
<!-- namespace的命名规则为:包名+SQL映射文件名,这样能够保证不重复使用 -->
<mapper namespace="anlemusic.mapper.UserMapper">
<!-- id也必须是唯一的,parameterType插入时使用的参数类型(也就是pojo的地
址),useGeneratedKeys="true",表示使用自动增长策略 -->
<insert id="save" parameterType="anlemusic.domain.User" useGeneratedKeys="true">
<!-- #{xxxx}表示取参数中对象xxxx的属性值 -->
INSERT INTO
TB_USER(name,sex,age)
VALUES(#{name},#{sex},#{age})
</insert>
</mapper>
第六步:数据库连接池
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis//DTD Config 3.0//EN"
".dtd">
<!-- XML配置文件包含对MyBatis系统的核心设置 -->
<configuration>
<!-- 指定MyBatis所用日志的具体实现,可在控制台可见,还需要配置log4j.properties文件 -->
<settings>
<setting name="logImpl" value="LOG4J" />
</settings>
<!-- 环境配置,链接的数据库 -->
<environments default="mysql">
<environment id="mysql">
<!-- 指定事务管理类型,type="JDBC"值直接简单使用了JDBC的提交和回滚设置 -->
<transactionManager type="JDBC" />
<!-- dataSource值数据源配置,POOLED是JDBC链接对象的数据源连接池的实现 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis"
/>
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<!-- mappers告诉了Mybatis去哪里找持久化了的映射文件 -->
<mappers>
<mapper resource="anlemusic/cn/mapper/UserMapper.xml" />
</mappers>
</configuration>
第七步:Test.java的实现增、删改查
public static void main(String[] args) throws IOException {
// 读取mybatis-config.xml文件
InputStream inputStream = Resources
.getResourceAsStream("mybatis-config.xml");
// 初始化Mybatis,创建sqlSessionFactory实例
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(inputStream);
// 创建Session实例
SqlSession session = sqlSessionFactory.openSession();
User user = new User("admin", "2", 26);
// 插入数据namespace+id
session.insert("anlemusic.mapper.UserMapper.save", user);
// 提交事务
sessionmit();
// 关闭session
session.close();
}
+----+-------+------+------+
| id | name | sex | age |
+----+-------+------+------+
| 1 | admin | 2 | 26 |由此可见,添加成功了
深入Mybitas
SqlSessionFactory:它是单个数据库映射关系经过编译后的内存镜像,该实例可以通过
SqlSessionFactoryBuilder对象从配置文件中来获得SqlSessionFactory的实例,且以此为核心
常用方法:SqlSession openSession 创建SqlSession对象
SqlSession:它类似Connection,该实例不可被共享,不能放在一个类的静态字段甚至实例字段中,开启
之后,应该确保使用finally块来关闭它
常用方法:int insert(String statement)
namespace+id,返回SQL所影响的行数(如:插入了一行,则影响一行)
int insert(String statement,Object parameter)
namespace+id,插入时所需要的参数,返回SQL所影响的行数
int update(String statement)
namespace+id,返回SQL所影响的行数
int update(String statement,Object parameter)
namespace+id,更新时所需要的参数,返回SQL所影响的行数
int delete(String statement)
namespace+id,返回SQL所影响的行数
int delete(String statement,Object parameter)
namespace+id,删除时所需要的参数,返回SQL所影响的行数
<T> T selectOne(String statement)
namespace+id,返回SQL语句查询结果的泛型对象(本方法只支持一条返回结果)
<T> T selectOne(String statement,Object parameter)
namespace+id,查询时需要的参数,返回SQL语句查询结果的泛型对象(本方法只支持一条返回结果)
<E> List<E> selectList(String statement)
namespace+id,返回SQL语句查询结果的泛型对象的集合
<E> List<E> selectList(String statement,Object parameter)
namespace+id,查询时需要的参数,返回SQL语句查询结果的泛型对象的集合
<E> List<E> selectList(String statement,Object parameter,RowBounds
rowBounds)
namespace+id,查询时需要的参数,RowBounds对象用于分页,内含有两个属性:offset当前页数,limit当前页显示
多少条数据,返回SQL语句查询结果的泛型对象的集合
<K,V> Map<K,V> selectMap(String statement,String mapKey)
namespace+id,返回数据的其中一个列名,返回MAP对象集合
<K,V> Map<K,V> selectMap(String statement,Object parameter,String
mapKey)
namespace+id,查询时需要的参数,返回数据的其中一个列名,返回MAP对象集合
<K,V> Map<K,V> selectMap(String statement,Object parameter,String
mapKey,RowBounds rowBounds)
namespace+id,查询时需要的参数,返回数据的其中一个列名,RowBounds对象用于分页,返回MAP对象集合
void select(String statement,ResultHandler handler)
namespace+id,ResultHandler对象用来处理查询返回复杂的结果集,通常用于多表查询
void select(String statement,Object parameter,ResultHandler handler)
namespace+id,查询时需要的参数,ResultHandler对象用来处理查询返回复杂的结果集,通常用于多表查询
void select(String statement,Object parameter,RowBounds
rowBounds,ResultHandler handler)
namespace+id,查询时需要的参数,RowBounds对象用于分页,ResultHandler对象用来处理查询返回复杂的结果集
,通常用于多表查询
void commit()
提交事务
void rollback()
回滚事务
void close()
关闭SqlSession对象
Connection getConnection()
获取JDBC的数据库连接对象
<T> T getMapper(Class<T> type)
返回mapper接口的代理对象,该对象关联了SqlSession对象,type是Mapper的接口类型,可通过该方法操作数据库
Mybatis配置文件详解:
Mybatis的配置文件包含了影响Mybatis行为的信息,文档的结构
configuration 顶层配置
settings 设置
properties 属性
typeAliases 类型命名
typeHandlers 类型处理器
objectFactory 对象工厂
plugins 插件
environments 环境
environment 环境变量
transactionManager 事务管理器
dataSource 数据源
property 参数属性
databaseIdProvider 数据库厂商标识
mappers 映射器
settings 设置
<settings>
<setting name="设置参数" value="有效值" />
</settings>
参数 描述 有效值
默认值
cacheEnabled 映射器中配置的缓存全局开关 true|false
true
lazyLoadingEnabled 延迟加载全局开关,开则延迟 true|false
false
aggressiveLazyLoading 启用则延迟加载对象完整加载,反之按需加载 true|false
true
multipleResultSetsEnab 是否允许单一语句返回多结果集(需要兼容驱动) true|false
true
useColumnLabel 使用列标签代替列名 true|false
true
useGeneratedKeys 允许JDBC支持自动生成主键(需要驱动兼容) true|false
false
autoMappingBehavior 指定Mybatis应如何自动映射列到字段或属性 NONE取消自动映射|PARTIAL
只会自动映射没有定义映射的结果集|FULL会自动映射复杂的结果集 PARTIAL
autoMappingUnknown ColumnBehavior 找不到Mapping时候 NONE:什么都不做|WARNING输出警告|
FAILING映射失败,抛出SqlSessionException NONE
defaultExecutorType 配置默认的执行器 SIMPLE普通的执行器|REUSE执行器会
重用预处理语句|BATCH执行器将重用语句并执行批量更新 SIMPLE
defaultStatementTimeout 设置超时时间,驱动等待数据库响应秒数 interger
没有设置
defaultFetchSize 默认返回的结果集大小 interger
没有设置
safeRowBoundsEnabled 允许在嵌套语句中使用分页 true|false
false
mapUnderscoreToCameICase 是否开启自动驼峰式命名规则映射 true|false
false
localCacheScope 利用缓存机制,防止循环引用和加上重复嵌套查询 SESSION缓存会话中
执行的所有查询|STATEMENT本地会话仅用在语句执行上,对相同SqlSession的不同调用将不会共享数据
SESSION
jdbcTypeForNull 没有为参数提供特定的JDBC类型时,某些驱动需要指定列的类型
NULL|VARCHAR|OTHER OTHER
lazyLoadTriggerMethods 指定那个对象的方法触发一次延迟加载 方法名的list集合
equals.clone,hashCode,toString
defaultScriptingLanguage 指定动态SQL生成的默认语言 一个类型别名或者
完全限定名 org.apache.ibatis.scripting.xmltagsXMLDynamicLanguageDriver
callSettersOnNulls 指定结果集中值为null时是否调用映射对象的setter true|false
false
logPrefix 指定MyBatis增加到日志名称的前缀 String
没有设置
logImpl 指定MuBatis所用日志的具体实现，未定义时将自动寻找 SLF4J|
LOG4J|LOG4J2|JDK_LOGGING|COMMONS_LOGGING|STDOUT_LOGGING|NO_LOGGING
proxyFactory 指定MyBatis创建具有延迟加载能力的对象所用到的代理工具 CGLIB|
JAVASSIST JAVASSIST(MyBatis 3.3 or above)
properties 属性(灵活配置)
db.properties的java属性文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mybatis
username=root
password=root
<properties resource="db.properties" />
<dataSource type="POOLED">
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</dataSource>
typeAliases 类型命名(为java类型设置短的名字,存在意义仅用于来减少完全限定名的冗余)
mybatis-config.xml设置后
<typeAliases>
<typeAlias alias="user" type="anlemusic.domain.User"/>
</typeAliases>
UserMapper.xml可用
<insert id="save" parameterType="user" useGeneratedKeys="true">
或者注解法:在User.java上注解
@Alias("users")//不写则是user
public class User
mybatis-config.xml中
<typeAliases>
<!-- 指定包名,没有注解的情况下会用首字母小写的非限定类名作为别名 -->
<package name="anlemusic.domain"/>
</typeAliases>
Mybatis默认的别名
别名 映射的类型
_byte byte
_long long
_short short
_int int
_integer int
_double double
_float float
_boolean boolean
string String
byte Byte
long Long
short Short
int Integer
integer Integer
double Double
float Float
boolean Boolean
date Date
bigdecimal BigDecimal
object Object
map Map
hashmap HashMap
list List
arraylist ArrayList
collection Collection
iterator Iterator
typeHandlers 类型处理器
<typeHandlers>
<typeHandler handler="" javaType="" jdbcType="" />
</typeHandlers>
handler(类型处理器) javaType(java类型) jdbcType(jdbc类型
)
BooleanTypeHandler java.lang.Boolean,boolean 数据库兼容的
Boolean
ByteTypeHandler java.lang.Byte,byte 数据库兼容的
NUMERIC|BYTE
ShortTypeHandler java.lang.Short,short 数据库兼容的
NUMERIC|SHORT INTEGER
IntegerTypeHandler java.lang.Integer,int 数据库兼容的
NUMERIC|INTEGER
LongTypeHandler java.lang.Long,long 数据库兼容的
NUMERIC|LONG INTEGER
FloatTypeHandler java.lang.Float,float 数据库兼容的
NUMERIC|FLOAT
DoubleTypeHandler java.lang.Double,double 数据库兼容的
NUMERIC|DOUBLE
BigDecimalTypeHandler java.math.BigDecimal 数据库兼容的
NUMERIC|DECIMAL
StringTypeHandler java.lang.String CHAR、VARCHAR
ClobReaderTypeHandler java.io.Reader 无
ClobTypeHandler java.lang.String CLOB、LONGVARCHAR
NStringTypeHandler java.lang.String NVARCHAR、NCHAR
NClobTypeHandler java.lang.String NCLOB
BlobInputStreamTypeHandler java.io.InputStream 无
ByteArrayTypeHandler byte[] 数据库兼容的字节
流类型
BlobTypeHandler byte[]
BLOB,LONGVARBINARY
DateTypeHandler java.util.Date TIMESTAMP
DateOnlyTypeHandler java.util.Date DATE
TimeOnlyTypeHandler java.util.Date TIME
SqlTimestampTypeHandler java.sql.Timestamp TIMESTAMP
SqlDateTypeHandler java.sql.Date DATE
SqlTimeTypeHandler java.sql.Time TIME
ObjectTypeHandler Any OTHER或未指定类型
EnumTypeHandler Enumeration Type VARCHAR,任何兼容
的字符串类型,存储枚举的名称
EnumOrdinalTypeHandler Enumeration Type 任何兼容的NUMERIC
或DOUBLE类型,存储枚举的索引
objectFactory 对象工厂
//在MyBatis配置文件中配置自定义的对象工厂
<objectFactory type="anlemusic.tools.ZjFactory">
<property name="someProperty" value="100" />
</objectFactory>
public class ZjFactory extends DefaultObjectFactory {
//自定义工厂对象
@Override//处理默认构造方法
public <T> T create(Class<T> type) {
// TODO Auto-generated method stub
return super.create(type);
}
@Override//处理带参的构造方法
public <T> T create(Class<T> type, List<Class<?>> constructorArgTypes,
List<Object> constructorArgs) {
// TODO Auto-generated method stub
return super.create(type, constructorArgTypes, constructorArgs);
}
@Override//可以用来配置ObjectFactory
public void setProperties(Properties properties) {
// TODO Auto-generated method stub
super.setProperties(properties);
}
@Override//判断类型
public <T> boolean isCollection(Class<T> type) {
// TODO Auto-generated method stub
return super.isCollection(type);
}
}
environments 环境
environment 环境变量
<!-- 环境配置,链接的数据库 -->
<environments default="mysql">//默认的default="development" 默认环境一定要匹
配定义的其中一个环境ID
<environment id="mysql">//默认的id="development" 环境ID可随意命名,
建议简介而有意义
<!-- 指定事务管理类型,type="JDBC"值直接简单使用了JDBC的提交和回滚设置 -->
<transactionManager type="JDBC" />//比如,type="JDBC" 有两种类型的事务
管理器JDBC(使用了jdbc提交和回滚设置)、MANAGED(什么都不做,将管理事务交给容器)
<!-- MANAGED配置,如果使用Spring+Mybatis配置,则无需配置事务管理器,因为
Spring会覆盖前面的配置 -->
//<transactionManager type="MANAGED">
// <property name="closeConnection" value="false" />
//</transactionManager>
<!-- dataSource值数据源配置,POOLED是JDBC链接对象的数据源连接池的实现 -->
<dataSource type="POOLED">//比如,type="POOLED|UNPOOLED|JNDI"
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
</environments>
POOLED:利用数据库池,将jdbc链接对象组织起来,避免了创建新的链接和实例时需要的验证等
时间
全部属性:driver 这是JDBC驱动的Java类的完
全限定名
url 这是JDBC的数据库连接URL地
址
username 登陆数据库的用户名
password 登陆数据库的密码
defaultTransactionIsoIationLevel 默认的链接事务隔离级别
poolMaximumActiveConnections 在任意时间可以存在的活动
链接数量,默认是10
poolMaximumIdleConnections 任意时间可能存在的空闲连
接数
poolMaximumCheckoutTime 在被强制返回之前,池中链接
被检出时间,默认20秒
poolTimeToWait 链接时常超过20s,会给连接
池打印日志,并尝试获取新的链接
poolPingQuery 发送到数据库的侦测查询,用
来检验链接是否正常工作,并准备接受请求
poolPingEnabled 是否启用侦测查询,如开启,
必须使用一个可执行的SQL语句,设置poolPingQuery属性,最好是快的sql语句
poolPingConnectionsNotUsedFor 配置poolPingQuery使用的频
率,上述为true时,默认为0,每一时刻都被侦测
UNPOOLED:每次请求时打开和关闭连接,对性能没有要求的可以使用此
全部属性:driver 这是JDBC驱动的Java类的完
全限定名
url 这是JDBC的数据库连接URL地
址
username 登陆数据库的用户名
password 登陆数据库的密码
defaultTransactionIsoIationLevel 默认的链接事务隔离级别
JNDI:这个适用于EJB或者应用服务器这类容器中使用
全部属性:initial_context 用来在InitialContext中寻
找上下文
data_source 引用数据源实例位置的上下
文路径,配置了上述,则会在其返回的上下文中寻找,反之则在InitialContext中寻找
<dataSource type="JNDI">
<property name="initial_context" value="java:/comp/env" />
<property name="data_source" value="javads" />
</dataSource>
mappers 映射器
<!-- 使用类路径查找资源文件 -->
<mappers>
<mapper resource="anlemusic/cn/mapper/UserMapper.xml" />
</mappers>
<!-- 使用使用本地资源查找 -->
<mappers>
<mapper url="file:///C:/mapper/UserMapper.xml" />
</mappers>
<!-- 使用接口类 -->
<mappers>
<mapper class="anlemusic.dao.UserMapper" />
</mappers>
<!-- 使用包名 -->
<mappers>
<package name="anlemusic.mapper" />
</mappers>
深入Mapper XML映射文件
SQL映射文件常用的元素: select------映射查询语句
insert------映射插入语句
update------映射更新语句
delete------映射删除语句
sql---------可被其他语句引用的可重用语句块
ceche-------给定命名空间的缓存配置
cache-ref---其他命名空间缓存配置的引用
resultMap---最复杂也是最强大的元素,用来描述如何从数据库结果集中加
载对象
parameterMap(已废弃,老式风格的参数映射)
select元素属性描述:
id 引用这条语句所用的唯一标识符号
parameterType 传入参数类的完全限定名或别名,默认是unset(依赖驱动).不配置的情况下
可通过TypeHandler推断出具体传入的语句参数
resultType 返回参数类的完全限定名或别名,如是集合类型,则使用集合包含的类型.还
可以配置resultMap,但不能同时配置
resultMap 外部resultMap的命名引用,结果集映射.返回时还可以配置resultType,但
不能同时配置
flushCache 默认是false,打开则任何语句被调用,都会导致本地缓存或者二级缓存被清
空
useCache 在select元素当中默认是true,将会导致本条语句的结果被二级缓存
timeout 抛出异常之前等待数据库返回请求结果的秒数,默认unset(依赖驱动)
fechSize 尝试影响返回的结果行数,默认为unset(依赖驱动)
statementType 默认值为:PREPARED(PreparedStatement),还有STATEMENT(Statement)、
CALLABLE(CallableStatement).JDBC的使用方式
resultSetType 结果集的类型,FORWARD_ONLY、SCROLL_SENSITIVE、SCROLL_INSENSITIVE,
默认为unset(依赖驱动)
databaseld 如果配置了databaseidProvider,Mybatis会加载所有的不带databaseid或
匹配当前databaseid的语句,如果带或者不带的都有,则不带的会被忽略
resultOrdered 默认为false,适用于嵌套结果,如果为true,则假设包含了嵌套结果集或是
分组了
resultSets 这个设置对多结果集的情况适用,它将列出语句执行后返回的结果集,并给
每个结果集一个名称,名称用逗号分开
insert、update、delete:大多数元素的属性和select一致,特有属性描述如下:
useGeneratedKeys (insert和update)默认为false,使用JDBC的getGeneratedKeys方法来获取
主键//比如Mysql|SQL Server自动生成主键,则可以useGeneratedKeys=true
keyProperty (insert和update)默认为unset,使用JDBC的getGeneratedKeys方法的返回
值或者通过insert语句的selectKey子元素设置它的键值.希望得到多个生成的列,也可以是逗号分隔的属性名称
列表
KeyColumn (insert和update)通过生成的键值设置表中的列名
Oracle不支持自动生成主键的JDBC驱动来说,可调用下面的方式
<insert id="ins">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select
SEQUENCE_TB_USER.nextval as id from dual
</selectKey>
insert into
TB_USER(id,username,password,email,address) values
(#{id}, #{username}, #{password}, #{email}, #{address})
</insert>
selectKey查找主键的属性:
keyProperty 设置的目标属性,一般设置为id,多列时用逗号隔开id1,id2
keyColumn 匹配属性返回结果集中的列名称,多列则逗号分开
resultType 结果的类型,多列则可以使用一个包含期望属性的Object或者一个Map
order 可以被设置为BEFORE或者AFTER,如果前者,则首先选择主键,反之首先插入
语句
statementType 默认值为:PREPARED(PreparedStatement),还有STATEMENT(Statement)、
CALLABLE(CallableStatement).JDBC的使用方式
SQL的使用:
<sql id="userCoulumns">select * from tb_user where id='1'</sql>
<select id="sele" resultType="user">
<include refid="userCoulumns"></include>
</select>
一般会写一个工厂类型:
/** 自己的工厂类型 **/
public class SessionFactorys {
private static SqlSessionFactory sqlSessionFactory = null;
// 初始化创建sessionFactory对象
static {
try {
// 读取mybatis-config.xml配置文件
InputStream inputStream = Resources
.getResourceAsStream("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(inputStream);
} catch (Exception e) {
// TODO: handle exception
System.out.println("创建会话工厂失败");
}
}
// 获取SqlSession对象的静态方法
public static SqlSession getSqlSession() {
return sqlSessionFactory.openSession();
}
// 获取SqlSessionFactory静态方法
public static SqlSessionFactory getSqlSessionFactory() {
return sqlSessionFactory;
}
// 获取session,并关闭
public static void close(SqlSession session) {
// TODO Auto-generated method stub
sessionmit();
session.close();
System.out.println("已关闭");
}
}//可利用此增删改查
ResultMaps:告诉Mybatis将从结果集中取出的数据转换成开发者所需要的对象
创建俩张表
create table tb_clazz(
id int primary key auto_increment,
code varchar(18)
)
insert into tb_clazz(code) values('1');
insert into tb_clazz(code) values('2');
create table tb_student(
id int primary key auto_increment,
name varchar(18),
sex char(3),
age int,
clazz_id int,
foreign key (clazz_id) references tb_clazz(id)
);
例一、map类型
xml
<select id="sele" resultType="map"> //返回类型map
select * from tb_user
</select>
java
List<Map<String,Object>> usersUser = session
.selectList("anlemusic.mapper.UserMapper.sele");
for (Map<String, Object> map : usersUser) {
System.out.println(map);
}
结果:
{id=1, sex=2, age=26, name=admin}
{id=2, sex=2, age=26, name=admin}
{id=3, sex=2, age=26, name=admin}
{id=4, sex=2, age=26, name=admin}
{id=5, sex=2, age=26, name=admin}
{id=6, sex=2, age=26, name=admin}
例二、一般不建议用map集合,下面使用resultMap试试,还有更复杂的情况
create table tb_user(
user_id INT PRIMARY KEY AUTO_INCREMENT,
user_name varchar(12),
user_ses varchar(12),
user_age int
)
List<User> usersUser = session
.selectList("anlemusic.mapper.UserMapper.sele");
for (User user : usersUser) {
System.out.println(user.toString());
}
<resultMap id="userResultMap" type="user"><!-- id唯一标识符号、type实际返回的类型 -->
<id property="id" column="user_id"></id><!-- id数据库表的主键,column表示数据库表
的列名, property表示数据库列映射到返回类型的属性-->
<result property="name" column="user_name" /><!-- id为主键列,result为普通列 -->
<result property="sex" column="user_sex" /><!-- 即使数据库的参数和pojo不一致,一样
可以对应并查询出来 -->
<result property="age" column="user_age" />
</resultMap>
<select id="sele" resultMap="userResultMap">
select * from tb_user
</select>
例三、多表查询,返回的对象不一致
前言
association元素属性如下:
column 表示数据库表的列名,一般为外键
property 返回类型的Student的属性名clazz
javaType java代表的类型,如anlemusic.domain.User
select 表示执行一条查询语句,将查询到的语句,封装到property中去
collection元素属性如下:
property 返回类型Clazzs的属性名Student
javaType 该属性对应的类型名称,ArrayList
ofType 表示集合当中的类型,Students
column 使用id作为参数进行之后的select语句
select 表示执行一条查询语句,将查询到的语句,封装到property中去
第一步:xml
<!-- 查询所有学生信息,返回对象不但包含student对象,还有clazz对象,故用resultMap去映射返回类型,这时,
就会跳向resultMap且其id为studentResultMap的语句 -->
<select id="selectStudent" resultMap="studentResultMap">
select * from tb_student
</select>
<!-- 激活下 -->
<resultMap id="studentResultMap" type="student">
<id property="id" column="id"></id>
<result property="name" column="name" />
<result property="sex" column="sex" />
<result property="age" column="age" />
<!-- 此处用到了association对象 -->
<association property="clazz" column="clazz_id" javaType="clazz"
select="selectClazzWithId" />
<!-- 激活下 -->
</resultMap>
<!-- 根据班级id查询班级 -->
<select id="selectClazzWithId" resultType="clazz">
select * from tb_clazz
where id = #{clazz_id}
</select>
第二步:java
Clazz.java
private Integer id;
private String code;
Student.java
private Integer id;
private String name;
private String sex;
private Integer age;
private Clazz clazz;
Tests.java
SqlSession session = SessionFactorys.getSqlSession();
List<Student> students = session
.selectList("anlemusic.mapper.UserMapper.selectStudent");
for (Student student : students) {
System.out.println(student);
}
查询后
Student [id=1, name=name, sex=2, age=18, clazz=Clazz [id=1, code=1536]]
Student [id=2, name=name, sex=2, age=18, clazz=Clazz [id=2, code=1535]]
Student [id=3, name=name, sex=2, age=18, clazz=Clazz [id=1, code=1536]]
Student [id=4, name=name, sex=2, age=18, clazz=Clazz [id=2, code=1535]]
反过来,如果查询一个班级的人数呢？
第一步:xml
<resultMap id="clazzsResultMap" type="clazzs">
<id property="id" column="id"></id>
<result property="code" column="code" />
<collection property="student" column="id" javaType="ArrayList"
ofType="students" select="selectstudentWithId" />
</resultMap>
<select id="selectstudentWithId" resultType="students">
select * from tb_student where clazz_id = #{ss}
</select>
<select id="selectStudent" resultMap="clazzsResultMap">
select * from tb_clazz
</select>
第二步:java
Students.java
private Integer id;
private String name;
private String sex;
Clazzs.java
private Integer id;
private String code;
private List<Students> student;
private Integer age;
Tests.java
List<Clazzs> students = session
.selectList("anlemusic.mapper.UserMapper.selectStudent");
for (Clazzs student : students) {
System.out.println(student);
}
查询后
Clazzs [id=1, code=1536, students=[Students [id=1, name=name, sex=2, age=18], Students [id=3,
name=name, sex=2, age=18]]]
Clazzs [id=2, code=1535, students=[Students [id=2, name=name, sex=2, age=18], Students [id=4,
name=name, sex=2, age=18]]]
Clazzs [id=3, code=1534, students=[]]
Clazzs [id=4, code=1533, students=[]]
*************************************************一对一关联
*************************************************
首先创建两个表:人和身份证号
create table tb_card(
id int primary key auto_increment,
code varchar(18)
);
insert into tb_card values(null,'421125199702020311');
create table tb_person(
id int primary key auto_increment,
name varchar(18),
sex char(18),
age int,
card_id int unique,
foreign key (card_id) references tb_card(id)
);
insert into tb_person values('javk','男',23,1);
CardMappers.xml
<mapper namespace="anlemusic.mapper.CardMappers">
<!-- 用来查询card的id -->
<select id="selectCardById" parameterType="int" resultType="card">
select * from tb_card where id = #{id}
</select>
</mapper>
PersonMappers.xml
<mapper namespace="anlemusic.mapper.PersonMappers">
<select id="selectPersonById" parameterType="int" resultMap="PersonMapper">
select * from tb_person where id = #{id}
</select>
<!-- 映射peson对象 -->
<resultMap id="PersonMapper" type="person">
<id property="id" column="id"></id>
<result property="name" column="name" />
<result property="sex" column="sex" />
<result property="age" column="age" />
<association property="card" column="card_id" javaType="card"
select="anlemusic.mapper.CardMappers.selectCardById" />
</resultMap>
</mapper>
PersonMappers接口
public interface PersonMappers {
/**
* 根据id查询Person 方法名和参数必须和xml文件中的id和parameterType属性一致
*
* @param id
* @return Person对象
* **/
Person selectPersonById(Integer id);
}
Test.java
PersonMappers per = session.getMapper(PersonMappers.class);
Person pers = per.selectPersonById(2);
System.out.println(pers);
System.out.println(pers.getCard());
Person [id=2, name=javks, sex=nv, age=23, card_id=null, card=Card [id=2,
code=421125199702020312]]
Card [id=2, code=421125199702020312]
*************************************************一对多关联|多对一关联
*************************************************
首先创建两个表学生和班级,多个学生对于一个班级,一个班级对应多个学生,都是一对多的关系
create table tb_clazz(
id int primary key auto_increment,
code varchar(18),
name varchar(18)
);
insert into tb_clazz(code,name) values('1','1536');
insert into tb_clazz(code,name) values('2','1535');
create table tb_student(
id int primary key auto_increment,
name varchar(18),
sex char(13),
age int,
clazz_id int,
foreign key (clazz_id) references tb_clazz(id)
);
insert into tb_student values(null,'name','2',18,1);
因为本实例需要懒加载,故除了Mybatis还需要添加两个包:cglib-2.2.2.jar、asm-3.3.1.jar
mybatis-config.xml中
<settings>
<!-- 日志类型 -->
<setting name="logImpl" value="LOG4J" />
<!-- 延迟查询,延迟加载全局开关,开则延迟,启用则延迟加载对象完整加载,反之按需加载
-->
<setting name="lazyLoadingEnabled" value="true" />
<setting name="aggressiveLazyLoading" value="false" />
</settings>
Student.java
private Integer id;
private String name;
private String sex;
private Integer age;
private Clazz clazzs;
Clazz.java
private Integer id;
private String code;
private String name;
private List<Student> students;//以上tostring皆无需带形参(clazzs|students)
UserMapper.xml中
<mapper namespace="anlemusic.mapper.UserMapper">
<!-- 根据id查询班级信息返回resultMap -->
<select id="selectClazzById" parameterType="int" resultMap="clazzResultMap">
select * from tb_clazz where id=#{id}
</select>
<resultMap id="clazzResultMap" type="clazz">
<id property="id" column="id" />
<result property="code" column="code" />
<result property="name" column="name" />
<!-- 一对多关联:collection fetchType="lazy" 表示懒加载 -->
<collection property="students" column="id" javaType="ArrayList"
ofType="student"
select="anlemusic.mapper.StudentMapper.selectStudentByClazzId"
fetchType="lazy">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="sex" column="sex" />
<result property="age" column="age" />
</collection>
</resultMap>
</mapper>
对应的接口java
public interface UserMapper {
/**
* 根据id查询班级信息
* **/
Clazz selectClazzById(Integer id);
}
StudentMappers.xml中
<mapper namespace="anlemusic.mapper.StudentMapper">
<!-- 根据id查询学生信息,多表链接,返回resultMap -->
<select id="selectStudentById" parameterType="int" resultMap="studentResultMap">
select * from tb_clazz c,tb_student s where c.id = s.clazz_id and s.id = #{id}
</select>
<!-- 映射studentResultMap对象 -->
<resultMap id="studentResultMap" type="student">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="sex" column="sex" />
<result property="age" column="age" />
<!-- 多对一关联映射:association -->
<association property="clazzs" javaType="clazz">
<id property="id" column="id" />
<result property="code" column="code" />
<result property="name" column="name" />
</association>
</resultMap>
<!-- 根据班级id查询学生信息,返回resultMap -->
<select id="selectStudentByClazzId" parameterType="int"
resultMap="studentResultMap">
select * from tb_student where clazz_id = #{id}
</select>
</mapper>
对应的接口java
public interface StudentMapper {
/**
* 根据id查询学生信息
* **/
Student selectStudentById(Integer id);
}
Test.java中
UserMapper userMapper = session.getMapper(UserMapper.class);
Clazz clazz = userMapper.selectClazzById(1);
System.out.println(clazz.toString());
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.selectStudentById(1);
System.out.println(student.toString());
Clazz [id=1, code=1, name=1536, students=[Student [id=1, name=name, sex=2, age=18, clazz=Clazz
[id=1, code=null, name=name, students=null]], Student [id=3, name=name, sex=2, age=1, clazz=Clazz
[id=3, code=null, name=name, students=null]], Student [id=4, name=name, sex=2, age=12,
clazz=Clazz [id=4, code=null, name=name, students=null]]]]
Student [id=1, name=1536, sex=2, age=18, clazz=Clazz [id=1, code=1, name=1536, students=null]]
*************************************************多对多关联
*************************************************
因为本实例需要懒加载,故除了Mybatis还需要添加两个包:cglib-2.2.2.jar、asm-3.3.1.jar
首先创建三张表:用户、商品、订单；还有中间表一张
create table tb_user(
id int primary key auto_increment,
username varchar(18),
loginname varchar(19),
password varchar(18),
phone varchar(18),
address varchar(18)
);
create table tb_article(
id int primary key auto_increment,
name varchar(18),
price double,
remark varchar(18)
);
create table tb_order(
id int primary key auto_increment,
code varchar(32),
total double,
user_id int,
foreign key (user_id) references tb_user(id)
);
create table tb_item(
order_id int,
article_id int,
amount int,
primary key (order_id,article_id),
foreign key (order_id) references tb_order(id),
foreign key (article_id) references tb_article(id)
);
插入信息
UserMapper.xml
<mapper namespace="anlemusic.mapper.UserMapper">
<!-- 根据用户id查询订单 -->
<select id="selectUserById" parameterType="int" resultMap="userResultMap">
select * from tb_user where id=#{id}
</select>
<resultMap id="userResultMap" type="user">
<id property="id" column="id" />
<result property="username" column="username" />
<result property="loginname" column="loginname" />
<result property="password" column="password" />
<result property="phone" column="phone" />
<result property="address" column="address" />
<!-- 一对多关联:collection fetchType="lazy" 表示懒加载 -->
<collection property="orders" column="id" javaType="ArrayList"
ofType="order"
select="anlemusic.mapper.OrderMapper.selectOrderByUserId"
fetchType="lazy">
<id property="id" column="id" />
<result property="code" column="code" />
<result property="total" column="total" />
<result property="age" column="age" />
</collection>
</resultMap>
</mapper>
public interface UserMapper {
User selectUserById(Integer id);
}
private Integer id;// id
private String username;// 用户名
private String loginname;// 登陆名
private String password;// 密码
private String phone;// 电话
private String address;// 收货地址
private List<Order> orders;// 用户与订单为一对多的关系
OrderMapper.xml
<mapper namespace="anlemusic.mapper.OrderMapper">
<select id="selectOrderById" parameterType="int" resultMap="orderResultMap">
select u.*,o.id as oid,CODE,total,user_id from tb_user u,tb_order o
where u.id = o.user_id and o.id = #{id}
<!-- select tb_user.*,tb_order.* from tb_user,tb_order where tb_user.id
= tb_order.user_id and tb_order.id=1; -->
</select>
<resultMap id="orderResultMap" type="order">
<id property="id" column="oid" />
<result property="code" column="code" />
<result property="total" column="total" />
<!-- 多对一关联:association fetchType="lazy" 表示懒加载 -->
<association property="user" javaType="user">
<id property="id" column="id" />
<result property="username" column="username" />
<result property="loginname" column="loginname" />
<result property="password" column="password" />
<result property="phone" column="phone" />
<result property="address" column="address" />
</association>
<!-- 多对多关联:collection -->
<collection property="articles" column="oid" javaType="ArrayList"
ofType="article"
select="anlemusic.mapper.ArticleMapper.selectArticleByOrderId"
fetchType="lazy">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="price" column="price" />
<result property="remark" column="remark" />
</collection>
</resultMap>
<!-- 根据用户id查询订单 -->
<select id="selectOrderByUserId" parameterType="int" resultType="order">
select * from tb_order where user_id=#{id}
</select>
</mapper>
public interface OrderMapper {
Order selectOrderById(Integer id);
}
private Integer id;// id
private String code;// 订单编号
private Double total;// 订单总金额、
private User user;// 订单与用户为一对一的关系
private List<Article> articles;// 订单和商品是多对多,一个订单可以对应多个
ArticleMapper.xml
<mapper namespace="anlemusic.mapper.ArticleMapper">
<!-- 根据id查询班级信息返回resultMap -->
<select id="selectArticleByOrderId" parameterType="int"
resultType="article">
select * from tb_article where id in (SELECT article_id from
tb_item where order_id = #{id})
</select>
</mapper>
private Integer id;// 商品id,主键
private String name;// 商品名称
private Double price;// 商品价格
private String remark;// 商品描述
private List<Order> orders;// 商品和订单的关系,多对多
Test.java
OrderMapper orderMapper = session.getMapper(OrderMapper.class);
Order order = orderMapper.selectOrderById(1);
System.out.println(order);
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = userMapper.selectUserById(1);
System.out.println(user);
##########################################动态SQL包含的元素:if、choose(when、otherwise)、where、
set、foreach、bind##########################################
条件判断、情况判断、与where关键词相关的查找语句、set主要用于多判断更新、循环查找、创建变量绑定到
上下文
create table tb_employee(
id int(11) PRIMARY key auto_increment,
loginname varchar(18),
password varchar(18),
name varchar(18) default null,
sex char(2) default null,
age int(11) default null,
phone varchar(21),
sal double,
state varchar(18)
);
##########################################if##########################################
xml
<mapper namespace="anlemusic.mapper.EmployeeMapper">
<select id="selectEmployeeByIdLike" resultType="employee">
select * from tb_employee where state='32'
<if test="id !=null">
//多个的时候,<if test="loginname != null and password !=null>
and id=#{id}
</if>
</select>
</mapper>
java
public interface EmployeeMapper {
List<Employee> selectEmployeeByIdLike(HashMap<String, Object> hashMap);
}
test.java
EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
HashMap<String,Object> hashMap= new HashMap<String, Object>();
hashMap.put("id", 1);
//注释之后出现多条
List<Employee> employees = employeeMapper.selectEmployeeByIdLike(hashMap);
for (Employee employee : employees) {
System.out.println(employee.toString());
}
##########################################choose(when、otherwise)
##########################################
<select id="selectEmployeeByIdLike" parameterType="hashmap"
resultType="employee">
select * from tb_employee where state = '32'
<!-- 如果传入了id 就根据id查询 没有就根据loginname和password查询,否则查询等于s的
数据 -->
<choose>
<when test="id!=null">
and id = #{id}
</when>
<when test="loginname!=null and password != null">
and loginname=#{loginname} and password =#{password}
</when>
<otherwise>
and sex='s'
</otherwise>
</choose>
</select>
##########################################where##########################################
<select id="selectEmployeeByIdLike" parameterType="hashmap"
resultType="employee">
select * from tb_employee
<!-- where知道只有在一个以上的if条件有值的情况下才会插入where语句,而且若开头或者
结尾为and或者or,则去掉 -->
<where>
<if test="state!=null">
state=#{state}
</if>
<if test="id!=null">
and id=#{id}
</if>
<if test="loginname !=null and password !=null">
and loginname = #{loginname} and password=#{password}
</if>
</where>
</select>
##########################################set##########################################
接口方法
void updateEmployeeIfNecessary(Employee employee);
一般方法
EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
Employee employee = new Employee(1, "l", "p", "n", "s", 3, "21", 3.2,
"s");
employeeMapper.updateEmployeeIfNecessary(employee);
<update id="updateEmployeeIfNecessary" parameterType="employee">
update tb_employee
<!-- set会动态前置set关键词,同时消除无关的逗号,利用这个更新数据库 -->
<set>
<if test="loginname!=null">loginname=#{loginname},</if>
<if test="password!=null">password=#{password},</if>
<if test="name!=null">name=#{name},</if>
<if test="sex!=null">sex=#{sex},</if>
<if test="phone!=null">phone=#{phone},</if>
<if test="sal!=null">sal=#{sal},</if>
<if test="state!=null">state=#{state},</if>
</set>
where id = #{id}
</update>
##########################################foreach##########################################
接口方法
List<Employee> selectEmployeeIn(List<Integer> list);
一般方法
EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
List<Integer> ids= new ArrayList<Integer>();
ids.add(1);ids.add(3);
List<Employee> employees = employeeMapper.selectEmployeeIn(ids);
for (Employee employee : employees) {
System.out.println(employee);
}
<!-- collection类型、open打开时、separator拼接符号、close关闭符号 -->
<select id="selectEmployeeIn" resultType="employee">
select * from
tb_employee where id in
<foreach item="item" index="index" collection="list" open="("
separator="," close=")">
#{item}
</foreach>
</select>
##########################################bind##########################################
接口方法
List<Employee> selectEmployeeLikeName(Employee employee);
一般方法
EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setLoginname("o");
List<Employee> employees = employeeMapper.selectEmployeeLikeName(employee);
for (Employee employeess : employees) {
System.out.println(employeess);
}
<!-- 将parameter.getLoginname()的值绑定到pattern上 -->
<select id="selectEmployeeLikeName" resultType="employee">
<bind name="pattern" value="'%' + _parameter.getLoginname()+'%'" />
select * from
tb_employee where loginname like #{pattern}
</select>
Mybatis缓存机制:一级缓存(SqlSession级别的缓存,同SqlSession中数据共享)、二级缓存(mapper级别的缓存,
多个SqlSession数据共享)
一级缓存是Session级别的缓存(默认开启)示例如下:
UsersMapper.xml
<mapper namespace="anlemusic.mapper.UsersMapper">
<!-- 根据用户id查询订单 -->
<select id="selectUserById" parameterType="int" resultType="user">
select * from tb_user where id=#{id}
</select>
</mapper>
UsersMapper.java
public interface UsersMapper {
User selectUserById(Integer id);
}
UsersMapper usersMapper = session.getMapper(UsersMapper.class);
User user = usersMapper.selectUserById(1);
System.out.println(user.toString());
User users = usersMapper.selectUserById(1);
System.out.println(users.toString());
仅仅查询了一次
==> Preparing: select * from tb_user where id=?
[DEBUG]2017年-12月-17日 18:22:28,807 位置:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug
(BaseJdbcLogger.java:139)
==> Parameters: 1(Integer)
[DEBUG]2017年-12月-17日 18:22:28,823 位置:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug
(BaseJdbcLogger.java:139)
<== Total: 1
User [id=1, username=javk, loginname=java, password=123456, phone=12321231231, address=guangz,
orders=null]
User [id=1, username=javk, loginname=java, password=123456, phone=12321231231, address=guangz,
orders=null]
换成2后,查询了两次
==> Preparing: select * from tb_user where id=?
[DEBUG]2017年-12月-17日 18:22:52,987 位置:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug
(BaseJdbcLogger.java:139)
==> Parameters: 1(Integer)
[DEBUG]2017年-12月-17日 18:22:53,002 位置:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug
(BaseJdbcLogger.java:139)
<== Total: 1
User [id=1, username=javk, loginname=java, password=123456, phone=12321231231, address=guangz,
orders=null]
[DEBUG]2017年-12月-17日 18:22:53,003 位置:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug
(BaseJdbcLogger.java:139)
==> Preparing: select * from tb_user where id=?
[DEBUG]2017年-12月-17日 18:22:53,004 位置:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug
(BaseJdbcLogger.java:139)
==> Parameters: 2(Integer)
[DEBUG]2017年-12月-17日 18:22:53,004 位置:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug
(BaseJdbcLogger.java:139)
<== Total: 1
User [id=2, username=jk, loginname=jasa, password=123456, phone=12321231231, address=hg,
orders=null]
二级缓存是mapper级别的缓存(手动开启)示例如下:
mybatis-ehcache-1.0.2.jar
ehcache-core-2.6.5.jar
slf4j-api-1.6.1.jar
导入二级缓存包
mybatis-config.xml
<!-- 开启二级缓存 -->
<setting name="cacheEnabled" value="true" />
UsersMapper.xml
<mapper namespace="anlemusic.mapper.UsersMapper">
<!-- 开启二级缓存,创建了LRU缓存,并每隔60秒刷新,最大储存对象为512个对象,而且返回的对象只能
是可读的 -->
<cache eviction="LRU" flushInterval="60000" size="512" readOnly="true" />
<!-- 根据用户id查询订单 -->
<select id="selectUserById" parameterType="int" resultType="user">
select * from tb_user where id=#{id}
</select>
</mapper>
eviction 收回策略,默认为LRU
LRU(最近最少使用的策略) 移除最长时间不被使用的对象
FIFO(先进先出策略) 按照对象进入缓存的顺序来移除它们
SOFT(软引用策略) 移除基于垃圾回收器状态和软引用规则的对象
WEAK(弱引用策略) 更积极地移除基于垃圾收集器状态和弱引用规则的对象
flushInterval 刷新间隔,单位毫秒
size 缓存条目,可以设置为任意正整数,默认为1024
readOnly 只读,设置为true,只读,设置为false,可读写,默认为false
还需要让映射的java对象实现java.io.Serializable接口的序列化和反序列化操作(不论父类或子类)
public class User implements Serializable
public interface UsersMapper extends Serializable{
User selectUserById(Integer id);
void deleteUserById(Integer id);
}
<mapper namespace="anlemusic.mapper.UsersMapper">
<!-- 开启二级缓存,创建了LRU缓存,并每隔60秒刷新,最大储存对象为512个对象,而且返回的对象只能
是可读的 -->
<cache eviction="LRU" flushInterval="60000" size="512" readOnly="true" />
<select id="selectUserById" parameterType="int" resultType="user">
select * from tb_user where id=#{id}
</select>
<delete id="deleteUserById" parameterType="int">
delete from tb_user
where id = #{id}
</delete>
</mapper>
SqlSession session = SessionFactorys.getSqlSession();
UsersMapper usersMapper = session.getMapper(UsersMapper.class);
User user = usersMapper.selectUserById(1);
System.out.println(user);
usersMapper.deleteUserById(2);
// commit提交
sessionmit();
// 删除再查询
User users = usersMapper.selectUserById(1);
System.out.println(users);
SessionFactorys.close(session);//还可以不同session共享数据
###############################################Mybatis注解
###############################################
常用的Annotation注解:
使用注解时,一定要对应操作,否则select和其他操作的注解执行方法不一样
Select 映射查询的SQL语句
SelectProvider Select语句的动态映射,允许指定一个类名和一个方法在
执行时返回运行的查询语句.参数:type类的完全限定名称、method该类的方法名
Insert 映射插入的SQL语句
InsertProvider Insert语句的动态映射,允许指定一个类名和一个方法在
执行时返回运行的查询语句.参数:type类的完全限定名称、method该类的方法名
Update 映射更新的SQL语句
UpdateProvider Update语句的动态映射,允许指定一个类名和一个方法在
执行时返回运行的查询语句.参数:type类的完全限定名称、method该类的方法名
Delete 映射删除的SQL语句
DeleteProvider Delete语句的动态映射,允许指定一个类名和一个方法在
执行时返回运行的查询语句.参数:type类的完全限定名称、method该类的方法名
Result 列和属性上的单独映射.参数:id(布尔值,是否被用于主
键映射)、column、property、javaType、jdbcType、typeHandler、one(单独的联系,和association相似)、
many(对集合而言,和collection相似)
Results 多个结果映射(Result)列表
Options 附加配置选项,参数:
boolean useCache()、
boolean flushCache()、
ResultSetType resultSetType()、
StatementType statementType()、
int fetchSize()、
int timeout()、
boolean useGeneratedKeys()、
String keyProperty()、String keyColumn();
One 复杂类型的单独属性值映射,必须指定select属性,表示
已映射的SQL语句的完全限定名称
Many 复杂类型的单独属性值映射,必须指定select属性,表示
已映射的SQL语句的完全限定名称
Param 当映射器需要多个参数时,这个注解可以被应用于映射器
方法参数来给每个参数取一个名字,@Param("id")代表#{id}
增删改查实例:
第一步,映射接口或包名
<mapper class="anlemusic.mapper.UsersMapper" />
<package name="anlemusic.mapper" /> //需添加
javassist-3.17.1-GA.jar
第二步,书写接口类
public interface UsersMapper {
/** 查询 **/
@Select("select * from tb_user where id=#{id}")
@Results({ @Result(id = true, column = "id", property = "id"),
@Result(column = "username", property = "username"),
@Result(column = "loginname", property = "loginname"),
@Result(column = "password", property = "password"),
@Result(column = "phone", property = "phone"),
@Result(column = "address", property = "address") })
User selectUserById(@Param("id") Integer id);
/** 删除 **/
@Delete("delete from tb_user where id = #{id}")
int deleteUserById(@Param("id") Integer id);
/** 查询全部 **/
@Select("select * from tb_user")
List<User> selectAllUser();
/** 增加 **/
@Insert("insert into tb_user values(#{id},#{username},#{loginname},#{password},#
{phone},#{address})")
@Options(useGeneratedKeys = true, keyProperty = "#id")
int saveUser(User user);
/** 改 **/
@Update("update tb_user set username=#{username},loginname=#{loginname},password=#
{password},phone=#{phone},address=#{address} where id=#{id}")
int modifyUser(User user);
}
第三步,实行增删改查
UsersMapper usersMapper = session.getMapper(UsersMapper.class);
//增加
User user = new User(null, "usernames", "用户名", "密码",
"电话号码", "手机号码");
System.out.println(usersMapper.saveUser(user));
//删除
usersMapper.deleteUserById(3);
//改
User s = new User(1, "用户名", "登陆名", "密码", "电话", "收获地址");
usersMapper.modifyUser(s);
//查
System.out.println(usersMapper.selectUserById(1));
List<User> ss = new ArrayList<User>();
ss = usersMapper.selectAllUser();
for (User user2 : ss) {
System.out.println(user2);
}
==> Preparing: insert into tb_user values(?,?,?,?,?,?)
<== Updates: 1
==> Preparing: delete from tb_user where id = ?
<== Updates: 1
==> Preparing: update tb_user set username=?,loginname=?,password=?,phone=?,address=? where id=?
<== Updates: 1
==> Preparing: select * from tb_user where id=?
User [id=1, username=用户名, loginname=登陆名, password=密码, phone=电话, address=收获地址]
==> Preparing: select * from tb_user
User [id=1, username=用户名, loginname=登陆名, password=密码, phone=电话, address=收获地址]
User [id=2, username=usernames, loginname=loginname, password=password, phone=dianhua,
address=shouhdiz]
User [id=4, username=usernames, loginname=用户名, password=密码, phone=电话号码, address=手机号码
]
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%注解法一对一%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
mybatis-config.xml
<!-- mappers告诉了Mybatis去哪里找持久化了的映射文件 -->
<mappers>
<package name="anlemusic.mapper" />
</mappers>
Person.java映射类
public class Person {
private Integer id;
private String name;
private String sex;
private Integer age;
private Card card;
}
Card.java映射类
public class Card {
private Integer id;
private String code;
}
PersonMapper.java接口类
public interface PersonMapper {
/** 根据id查询 **/
@Select("select * from tb_person where id = #{id}")
@Results({
@Result(id = true, column = "id", property = "id"),
@Result(column = "name", property = "name"),
@Result(column = "sex", property = "sex"),
@Result(column = "age", property = "age"),
@Result(column = "card_id", property = "card", one = @One(select =
"anlemusic.mapper.CardMapper.selectCardById", fetchType = FetchType.EAGER))
})//列名称,映射类名称,对一查询地址带上接口+方法名,是否延迟加载
Person selectPersonById(Integer id);
}
CardMapper.java接口类
package anlemusic.mapper;
import org.apache.ibatis.annotations.Select;
import anlemusic.domain.Card;
public interface CardMapper {
/** 根据id查询card的参数 **/
@Select("select * from tb_card where id = #{id}")
Card selectCardById(Integer id);
}
Tests测试类
PersonMapper personMapper = session.getMapper(PersonMapper.class);
Person person = personMapper.selectPersonById(1);
System.out.println(person);
破旧的控制台
Person [id=1, name=javk, sex=nan, age=23, card=Card [id=1, code=421125199702020311]]
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%注解法一对多%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Student.java映射类
public class Student{
private Integer id;
private String name;
private String sex;
private Integer age;
private Clazz clazzs;
}
Clazz.java映射类
public class Clazz{
private Integer id;
private String code;
private String name;
private List<Student> students;
}
StudentMapper.java接口类
public interface StudentMapper {
/** 根据班级id查询所有班级学生 **/
@Select("select * from tb_student where clazz_id = #{id}")
@Results({ @Result(id = true, column = "id", property = "id"),
@Result(column = "name", property = "name"),
@Result(column = "sex", property = "sex"),
@Result(column = "age", property = "age"), })
List<Student> selectClazzById(Integer id);
}
ClazzMapper.java接口类
public interface ClazzMapper {
/** 根据id查询班级信息 **/
@Select("select * from tb_clazz where id = #{id}")
@Results({
@Result(id = true, column = "id", property = "id"),
@Result(column = "code", property = "code"),
@Result(column = "name", property = "name"),
@Result(column = "id", property = "students", many = @Many(select =
"anlemusic.mapper.StudentMapper.selectClazzById", fetchType = FetchType.LAZY)) })
Clazz selectById(Integer id);
}
Tests测试类
ClazzMapper clazzMapper = session.getMapper(ClazzMapper.class);
Clazz clazz = clazzMapper.selectById(1);
System.out.println(clazz);
List<Student> student = clazz.getStudents();
for (Student student2 : student) {
System.out.println(student2);
}
破旧的控制台
Clazz [id=1, code=1, name=1536]
Student [id=1, name=name, sex=2, age=18, clazzs=null]
Student [id=3, name=name, sex=2, age=1, clazzs=null]
Student [id=4, name=name, sex=2, age=12, clazzs=null]
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%注解法多对多%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
/** 这是一个用户类 **/
public class User implements Serializable {
private static final long serialVersionUID = -5662130226863639970L;
private Integer id;// id
private String username;// 用户名
private String loginname;// 登陆名
private String password;// 密码
private String phone;// 电话
private String address;// 收货地址
}
/** 这是一个订单表 **/
public class Order implements Serializable {
private static final long serialVersionUID = 7703472760206539047L;
private Integer id;// id
private String code;// 订单编号
private Double total;// 订单总金额、
private User user;// 订单与用户为一对一的关系
private List<Article> articles;// 订单和商品是多对多,一个订单可以对应多个
}
/** 这是一个商品的表 **/
public class Article implements Serializable {
private static final long serialVersionUID = 8726800702685715564L;
private Integer id;// 商品id,主键
private String name;// 商品名称
private Double price;// 商品价格
private String remark;// 商品描述
private List<Order> orders;// 商品和订单的关系,多对多
}
public interface OrderMapper {
/** 根据商品id查询商品 **/
@Select("select * from tb_order where id = #{id}")
@Results({ @Result(id = true, column = "id", property = "id"),
@Result(column = "code", property = "code"),
@Result(column = "total", property = "total"),
@Result(column = "user_id", property = "user",one=@One
(select="anlemusic.mapper.UserMapper.selectUserById",fetchType=FetchType.EAGER)),
@Result(column = "id", property = "articles",many=@Many
(select="anlemusic.mapper.ArticleMapper.selectByOrderId",fetchType=FetchType.LAZY)) })
Order selectOrderById(Integer id);
}
public interface UserMapper {
/**根据用户id查询用户**/
@Select("select * from tb_user where id = #{id}")
User selectUserById(Integer id);
}
public interface ArticleMapper {
/** 根据订单id查询多个商品 **/
@Select("select * from tb_article where id in(select article_id from tb_item where
order_id = #{id})")
List<Article> selectByOrderId(Integer id);
}
OrderMapper orderMapper = session.getMapper(OrderMapper.class);
Order order = orderMapper.selectOrderById(1);
System.out.println(order);
List<Article> student = order.getArticles();
for (Article student2 : student) {
System.out.println(student2);
}
Order [id=1, code=bhbhbhbh, total=122.3, 用户为=User [id=1, username=用户名, loginname=登陆名,
password=密码, phone=电话, address=收获地址], articles=[Article [id=1, name=javk, price=12.3,
remark=guangz, orders=null], Article [id=2, name=javk, price=15.3, remark=gwe, orders=null],
Article [id=3, name=javk, price=11.3, remark=gwe, orders=null]]]
Article [id=1, name=javk, price=12.3, remark=guangz, orders=null]
Article [id=2, name=javk, price=15.3, remark=gwe, orders=null]
Article [id=3, name=javk, price=11.3, remark=gwe, orders=null]
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%注解法动态SQL%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
SelectProvider、InsertProvider、UpdateProvider、DeleteProvider来帮助我们构建动态SQL语句,Mybatis提
供了一个SQL工具类org.apache.ibatis.jdbc.SQL
该类不使用字符串拼接的方式,并会以适合的空格前缀和后缀来构造SQL语句,该类常用方法如下:
public T SELECT(String columns) 开始或追加select子句,通常是逗号分
隔的列表的列
public T FROM(String table) 启动或追加from子句,通常是表名
public T JOIN(String join) 向join添加一个新的查询条件,通常为
表,也包括连接返回的结果集
public T INNER_JOIN(String join) 同JOIN,连接方式是内链接
public T LEFT_OUTER_JOIN(String join) 同JOIN,链接方式是左外连接
public T RIGHT_OUTER_JOIN(String join) 同JOIN,链接方式是右外连接
public T WHERE(String conditions) 追加一个新的where子句条件,可以多
次调用
public T OR() 使用or拆分当前where子句条件,可多
次调用
public T AND() 使用and拆分当前where子句条件,可多
次调用
public T GROUP_BY(String columns) 追加一个新的Group by子句元素
public T HAVING(String conditions) 追加一个新的Having字句条件
public T ORDER_BY(String columns) 追加一个ORDER BY子句元素
public T INSERT_INTO(String tableName) 启动insert语句插入到指定表
public T VALUES(String columns, String values) 追加的insert语句,第一个参数是要插
入的列,第二个参数是要插入的值
public T DELETE_FROM(String table) 启动DELETE语句,并制定表删除
public T UPDATE(String table) 启动一个更新语句,并指定表更新
public T SET(String sets) 追加一个更新语句set列表
实例:动态SQL查询
public class Employee implements Serializable{
private static final long serialVersionUID = 5487271870862334461L;
private Integer id;
private String loginname;
private String password;
private String name;
private String sex;
private Integer age;
private String phone;
private Double sal;
private String state;
}
public interface EmployeeMapper {
/** 动态查询,拼接的类+方法名称 **/
@SelectProvider(type=EmployeeDynaSqlProvider.class,method="selectWhitParam")
List<Employee> selectWhitParam(Map<String,Object> param);
}
public class EmployeeDynaSqlProvider {
public String selectWhitParam(final Map<String, Object> param) {
// TODO Auto-generated method stub
return new SQL() {
{
SELECT("*");
FROM("tb_employee");
if (param.get("id") != null) {
WHERE("id=#{id}");
}
if (param.get("loginname") != null) {
WHERE("loginname=#{loginname}");
}
if (param.get("password") != null) {
WHERE("password=#{password}");
}
if (param.get("name") != null) {
WHERE("name=#{name}");
}
if (param.get("sex") != null) {
WHERE("sex=#{sex}");
}
if (param.get("age") != null) {
WHERE("age=#{age}");
}
if (param.get("phone") != null) {
WHERE("phone=#{phone}");
}
if (param.get("sal") != null) {
WHERE("sal=#{sal}");
}
if (param.get("state") != null) {
WHERE("state=#{state}");
}
}
}.toString();
}
}
EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
Map<String, Object> param = new HashMap<String, Object>();
param.put("state", "s");
List<Employee> list = employeeMapper.selectWhitParam(param);
for (Employee employee : list) {
System.out.println(employee);
}
Employee [id=1, loginname=l, password=p, name=n, sex=s, age=12, phone=21, sal=3.2, state=s]
注释param.put("state", "s");后
Employee [id=1, loginname=l, password=p, name=n, sex=s, age=12, phone=21, sal=3.2, state=s]
Employee [id=2, loginname=loginname, password=password, name=name, sex=s, age=12, phone=122122,
sal=23.0, state=32]
Employee [id=3, loginname=loginname, password=password, name=name, sex=s, age=12, phone=12212,
sal=3.0, state=3]
也可以用Employee传递参数,改一下三个地方即可
Employee employee = new Employee(null, null, null, null, null, null,null, null, "s");
List<Employee> list = employeeMapper.selectWhitParams(employee);
@SelectProvider(type = EmployeeDynaSqlProvider.class, method = "selectWhitParams")
List<Employee> selectWhitParams(Employee employee);
if (employee.getId() != null) {WHERE("id=#{id}");}
实例:动态SQL插入
public String insertWhitParam(final Employee employee) {
// TODO Auto-generated method stub
return new SQL() {
{
INSERT_INTO("tb_employee");
if (employee.getLoginname() != null) {
VALUES("loginname", "#{loginname}");
}
if (employee.getPassword() != null) {
VALUES("password", "#{password}");
}
if (employee.getName() != null) {
VALUES("name", "#{name}");
}
if (employee.getSex() != null) {
VALUES("sex", "#{sex}");
}
if (employee.getAge() != null) {
VALUES("age", "#{age}");
}
if (employee.getPhone() != null) {
VALUES("phone", "#{phone}");
}
if (employee.getSal() != null) {
VALUES("sal", "#{sal}");
}
if (employee.getState() != null) {
VALUES("state", "#{state}");
}
}
}.toString();
}
实例:动态SQL更改
{
UPDATE("tb_employee");
if (employee.getLoginname() != null) {
SET("loginname = #{loginname}");
}
if (employee.getPassword() != null) {
SET("password = #{password}");
}
if (employee.getName() != null) {
SET("name = #{name}");
}
if (employee.getSex() != null) {
SET("sex = #{sex}");
}
if (employee.getAge() != null) {
SET("age = {age}");
}
if (employee.getPhone() != null) {
SET("phone = #{phone}");
}
if (employee.getSal() != null) {
SET("sal = #{sal}");
}
if (employee.getState() != null) {
SET("state = #{state}");
}
WHERE(" id = #{id} ");
}
}.toString();
实例:动态SQL删除
return new SQL() {
{
DELETE_FROM("tb_employee");
if (param.get("id") != null) {
WHERE("id=#{id}");
}
if (param.get("loginname") != null) {
WHERE("loginname=#{loginname}");
}
if (param.get("password") != null) {
WHERE("password=#{password}");
}
if (param.get("name") != null) {
WHERE("name=#{name}");
}
if (param.get("sex") != null) {
WHERE("sex=#{sex}");
}
if (param.get("age") != null) {
WHERE("age=#{age}");
}
if (param.get("phone") != null) {
WHERE("phone=#{phone}");
}
if (param.get("sal") != null) {
WHERE("sal=#{sal}");
}
if (param.get("state") != null) {
WHERE("state=#{state}");
}
}
}.toString();
//至此,Mybatis已经完成
本文标签： Mybatis(Ibatis)基础
版权声明：本文标题：Mybatis(Ibatis)基础 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/jishu/1732357510h1534860.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论