Mybatis入門編

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>

ログログ4 j.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} %-6r [%15.15t] %-5p %30.30c %x - %m


# 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} %-6r [%15.15t] %-5p %30.30c %x - %m

Sql MapConfig.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 = 1L;

	private Long id;//   
	private String title;//  
	private String love;//  
	private String price;//  
	private Date date;//     
	private BigDecimal money;//   
}

方法1
*.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 {
	
	/**
	 * 1、  SqlMapConfig.xml   Mapper.xml sql  
	 * 2、     
	 * @author [hm]
	 * @date 2019 11 29    11:21:57
	 */
	public static void main(String[] args) throws IOException {
		// 1.   SqlMapConfig.xml     
		InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
		// 2.   SqlSessionFactory   
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		// 3.       SqlSession   
		SqlSession session = sqlSessionFactory.openSession();
		// 4.  Sql      Mapper.xml     
		UserInfo user = session.selectOne("mapper.selectByOneData");
		System.err.println(user);
		// 6.    
		session.close();
		in.close();
	}
}

方法2
*.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>

ダオインターフェース

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;

/**
 * 1、  SqlMapConfig.xml   UserMapper.xml sql  
 * 2、  UserDaoImpl    IUserDao  
 * 3、    
 */
public class UserMapperTest {

	public IUserDao userDao;
    public SqlSession sqlSession;

    @Before
    public void setUp() throws Exception {
        //      SqlMapConfig.xml   
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //   SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream );
        //   sqlSession
        sqlSession = sqlSessionFactory.openSession();
        this.userDao = new UserDaoImpl(sqlSession);
    }
    
    @Test
    public void queryUserByOne() throws Exception {
        System.err.println(this.userDao.selectByOneData());
        //mybatis        ，  sqlSession.clearCache()、  、  、        mybatis  
        sqlSession.clearCache();
        System.err.println(this.userDao.selectByOneData());
    }
}

方法3
*.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>

ダオインターフェース

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;

/**
 * 1、  SqlMapConfig.xml   Mapper.xml sql  
 * 2、  Mapper.xml       Dao  
 * 		 
 * 3、    
 */
public class UserMappersTest {

	public IUserMapperDao userDao;

    @Before
    public void setUp() throws Exception {
        //       
        String resource = "SqlMapConfig.xml";
        //       
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //   sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //   sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        // 1.          （namespace）   mapper      
        // 2.      statement id   mapper          
        // 3. Statement resultType   mapper           
        // 4. statement parameterType   mapper           （   ）
        this.userDao = sqlSession.getMapper(IUserMapperDao.class);
    }
    
    @Test
    public void queryTestData() throws Exception {
    	Map<String, Long[]> param = new HashMap<String, Long[]>();
    	Long[] arr = {1L, 2L, 3L};
    	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");
//    	param.put("userName", "  ");
    	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());
    }
}