Mybatis入门篇
创建maven项目 pom导入jar包
< properties>
< project. build. sourceEncoding> UTF - 8 < / project. build. sourceEncoding>
< project. reporting. outputEncoding> UTF - 8 < / project. reporting. outputEncoding>
< java. version> 1.8 < / java. version>
< / properties>
< dependencies>
< dependency>
< groupId> org. projectlombok< / groupId>
< artifactId> lombok< / artifactId>
< version> 1.16 .20 < / version>
< / dependency>
< dependency>
< groupId> org. mybatis< / groupId>
< artifactId> mybatis< / artifactId>
< version> 3.4 .5 < / version>
< / dependency>
< dependency>
< groupId> mysql< / groupId>
< artifactId> mysql- connector- java< / artifactId>
< version> 5.1 .6 < / version>
< / dependency>
< dependency>
< groupId> log4j< / groupId>
< artifactId> log4j< / artifactId>
< version> 1.2 .12 < / version>
< / dependency>
< dependency>
< groupId> junit< / groupId>
< artifactId> junit< / artifactId>
< version> 4.10 < / version>
< / dependency>
< / dependencies>
日志log4j.properties配置
# Set root category priority to INFO and its only appender to CONSOLE .
#log4j. rootCategory= INFO , CONSOLE debug info warn error fatal
log4j. rootCategory= debug, CONSOLE , LOGFILE
# Set the enterprise logger category to FATAL and its only appender to CONSOLE .
log4j. logger. org. apache. axis. enterprise= FATAL , CONSOLE
# CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j. appender. CONSOLE = org. apache. log4j. ConsoleAppender
log4j. appender. CONSOLE . layout= org. apache. log4j. PatternLayout
log4j. appender. CONSOLE . layout. ConversionPattern= % d{ ISO8601 } % - 6 r [ % 15.15 t] % - 5 p % 30.30 c % x - % m\n
# LOGFILE is set to be a File appender using a PatternLayout.
log4j. appender. LOGFILE = org. apache. log4j. FileAppender
log4j. appender. LOGFILE . File= d\: axis. log
log4j. appender. LOGFILE . Append= true
log4j. appender. LOGFILE . layout= org. apache. log4j. PatternLayout
log4j. appender. LOGFILE . layout. ConversionPattern= % d{ ISO8601 } % - 6 r [ % 15.15 t] % - 5 p % 30.30 c % x - % m\n
SqlMapConfig.xml MyBatis核心文件配置
< configuration>
< ! -- 数据源配 -- >
< environments default = "mysql" >
< environment id= "mysql" >
< transactionManager type= "JDBC" > < / transactionManager>
< dataSource type= "POOLED" >
< property name= "driver" value= "com.mysql.jdbc.Driver" / >
< property name= "url" value= "数据库链接url" / >
< property name= "username" value= "用户名" / >
< property name= "password" value= "密码" / >
< / dataSource>
< / environment>
< / environments>
< ! -- 配置* . xml -- >
< mappers>
< mapper resource= "*.xml路径" / >
< / mappers>
< / configuration>
TestBo Entity实体类
import java. io. Serializable;
import java. math. BigDecimal;
import java. sql. Date;
import lombok. Data;
@Data
public class TestBo implements Serializable {
private static final long serialVersionUID = 1 L;
private Long id;
private String title;
private String love;
private String price;
private Date date;
private BigDecimal money;
}
方法一
*.xml sql 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= "mapper" >
< ! -- sql 配置查询 -- >
< sql id= "paramSql" >
uid, dr01u, dr02u, dr04u, dr05u, dr06u, DATE_FORMAT ( dr96u, '%Y-%m-%d' ) AS dr96u
< / sql>
< ! -- 查询数据 -- >
< select id= "selectByOneData" resultType= "com.ben.test.UserInfo" >
select
< include refid= "paramSql" / >
from dr_userinfo LIMIT 1
< / select>
< / mapper>
测试类
package com. ben. test;
import java. io. IOException;
import java. io. InputStream;
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 com. hm. user. entity. UserInfo;
public class MapperTest {
public static void main ( String[ ] args) throws IOException {
InputStream inputStream = Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ;
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder ( ) . build ( inputStream) ;
SqlSession session = sqlSessionFactory. openSession ( ) ;
UserInfo user = session. selectOne ( "mapper.selectByOneData" ) ;
System. err. println ( user) ;
session. close ( ) ;
in . close ( ) ;
}
}
方法二
*.xml sql 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= "userMapper" >
< select id= "selectByOneData" resultType= "com.hm.user.entity.UserInfo" >
select
< ! -- 通过注册* . xml获取 调用另一个xml中的< sql> < / sql> -- >
< include refid= "mapper.paramSql" / >
< ! -- uid, dr01u, dr02u, dr04u, dr05u, dr06u, DATE_FORMAT ( dr96u, '%Y-%m-%d' ) AS dr96u -- >
from dr_userinfo LIMIT 1
< / select>
< / mapper>
dao接口
package com. hm. user. dao;
import com. hm. user. entity. UserInfo;
public interface IUserDao {
UserInfo selectByOneData ( ) ;
}
dao接口的实现类
package com. hm. user. dao. impl;
import org. apache. ibatis. session. SqlSession;
import com. hm. user. dao. IUserDao;
import com. hm. user. entity. UserInfo;
public class UserDaoImpl implements IUserDao {
public SqlSession sqlSession;
public UserDaoImpl ( SqlSession sqlSession) {
this . sqlSession = sqlSession;
}
public UserInfo selectByOneData ( ) {
UserInfo user = sqlSession. selectOne ( "userMapper.selectByOneData" ) ;
return user;
}
}
测试类
package com. ben. test;
import java. io. InputStream;
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 org. junit. Before;
import org. junit. Test;
import com. hm. user. dao. IUserDao;
import com. hm. user. dao. impl. UserDaoImpl;
public class UserMapperTest {
public IUserDao userDao;
public SqlSession sqlSession;
@Before
public void setUp ( ) throws Exception {
InputStream inputStream = Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ;
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder ( ) . build ( inputStream ) ;
sqlSession = sqlSessionFactory. openSession ( ) ;
this . userDao = new UserDaoImpl ( sqlSession) ;
}
@Test
public void queryUserByOne ( ) throws Exception {
System. err. println ( this . userDao. selectByOneData ( ) ) ;
sqlSession. clearCache ( ) ;
System. err. println ( this . userDao. selectByOneData ( ) ) ;
}
}
方法三
*.xml sql 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= "com.hm.user.dao.IUserMapperDao" >
< select id= "selectById" resultType= "com.hm.user.entity.UserInfo" >
select
uid, dr01u, dr02u, dr04u, dr05u, dr06u, DATE_FORMAT ( dr96u, '%Y-%m-%d' ) AS dr96u
from dr_userinfo LIMIT 1
< / select>
< select id= "selectListData" parameterType= "Map" resultType= "com.hm.user.entity.UserInfo" >
select
uid, dr01u, dr02u, dr04u, dr05u, dr06u, DATE_FORMAT ( dr96u, '%Y-%m-%d' ) AS dr96u
from dr_userinfo
< where>
and dr99u= 0
< ! -- < if test= "userName != null and userName.trim() != ''" >
and dr01u = #{ userName}
< / if > -- >
< choose>
< when test= "dr04u != null and dr04u.trim() != ''" >
and dr04u = #{ dr04u}
< / when>
< when test= "dr05u != null and dr05u.trim() != ''" >
and dr05u = #{ dr05u}
< / when>
< otherwise>
and dr01u = '孟琬'
< / otherwise>
< / choose>
< / where>
< / select>
< update id= "updateData" parameterType= "com.hm.user.entity.TestBo" >
update dr_test
< ! -- set
title = #{ title} ,
love = #{ love} ,
price = #{ price} ,
date = NOW ( ) ,
money = #{ money}
where id= '19916142406778880' -- >
< trim prefix= "set" suffixOverrides= "," >
< if test= "title != null and title.trim() != ''" >
title = #{ title} ,
< / if >
< if test= "love != null and love.trim() != ''" >
love = #{ love} ,
< / if >
< if test= "price != null and price.trim() != ''" >
price = #{ price} ,
< / if >
< ! -- < if test= "money != null" > -- >
money = #{ money} ,
< ! -- < / if > -- >
date = NOW ( ) ,
< / trim>
< where>
and id= '19916142406778880'
< / where>
< / update>
< select id= "selectByTest" resultType= "com.hm.user.entity.TestBo" >
select * from dr_test
< where>
and id = '19916142406778880'
< / where>
limit 1
< / select>
< select id= "selectTestListData" parameterType= "Map" resultType= "com.hm.user.entity.TestBo" >
select * from dr_test
< where>
and id in
< foreach collection= "arr" index= "index" item= "a" open= "(" close= ")" separator= "," >
#{ a}
< / foreach>
< / where>
< / select>
< / mapper>
dao接口
package com. hm. user. dao;
import java. util. List;
import java. util. Map;
import com. hm. user. entity. TestBo;
import com. hm. user. entity. UserInfo;
public interface IUserMapperDao {
UserInfo selectById ( ) ;
List< UserInfo> selectListData ( Map< String, String> param) ;
Integer updateData ( TestBo user) ;
TestBo selectByTest ( ) ;
List< TestBo> selectTestListData ( Map< String, Long[ ] > param) ;
}
测试类
package com. ben. test;
import java. io. InputStream;
import java. math. BigDecimal;
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;
import org. junit. Before;
import org. junit. Test;
import com. hm. user. dao. IUserMapperDao;
import com. hm. user. entity. TestBo;
import com. hm. user. entity. UserInfo;
public class UserMappersTest {
public IUserMapperDao userDao;
@Before
public void setUp ( ) throws Exception {
String resource = "SqlMapConfig.xml" ;
InputStream inputStream = Resources. getResourceAsStream ( resource) ;
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder ( ) . build ( inputStream) ;
SqlSession sqlSession = sqlSessionFactory. openSession ( true ) ;
this . userDao = sqlSession. getMapper ( IUserMapperDao. class ) ;
}
@Test
public void queryTestData ( ) throws Exception {
Map< String, Long[ ] > param = new HashMap < String, Long[ ] > ( ) ;
Long[ ] arr = { 1 L, 2 L, 3 L} ;
param. put ( "arr" , arr) ;
List< TestBo> bo = this . userDao. selectTestListData ( param) ;
for ( TestBo testBo : bo) {
System. err. println ( testBo) ;
}
}
@Test
public void updateTestData ( ) throws Exception {
TestBo bo = this . userDao. selectByTest ( ) ;
System. err. println ( bo) ;
bo. setTitle ( null ) ;
bo. setLove ( "love" ) ;
bo. setPrice ( "" ) ;
BigDecimal bd = new BigDecimal ( 12 ) ;
bo. setMoney ( bd) ;
System. out. println ( this . userDao. updateData ( bo) ) ;
System. err. println ( this . userDao. selectByTest ( ) ) ;
}
@Test
public void queryUserList ( ) throws Exception {
Map< String, String> param = new HashMap < String, String> ( ) ;
param. put ( "dr04u" , " " ) ;
param. put ( "dr05u" , "092cad79" ) ;
List< UserInfo> list = this . userDao. selectListData ( param) ;
for ( UserInfo userInfo : list) {
System. err. println ( userInfo) ;
}
}
@Test
public void queryUserByOne ( ) {
System. err. println ( this . userDao. selectById ( ) ) ;
System. err. println ( this . userDao. selectById ( ) ) ;
}
}