一、创建工程
1、创建mybatis工程,删除其下src目录
2、new Modual创建upgrade 子模块
3、mybatis配置pom.xml中集中声明版本,添加依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>vip.ycpower</groupId>
<artifactId>mybatis</artifactId>
<packaging>pom</packaging>
<version>1.0-SNAPSHOT</version>
<modules>
<module>dynamic</module>
</modules>
<name>mybatis</name>
<url>http://www.ycpower.vip/mybatis</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>10</maven.compiler.source>
<maven.compiler.target>10</maven.compiler.target>
<maven.compiler.compilerVersion>10</maven.compiler.compilerVersion>
<!-- 集中声明版本 -->
<mysql.driver.version>5.1.47</mysql.driver.version> <!-- ${mysql.driver.version} -->
<mybatis.version>3.4.6</mybatis.version> <!-- ${mybatis.version} -->
<log4j.version>1.2.17</log4j.version> <!-- ${log4j.version} -->
<slf4j.version>1.7.25</slf4j.version> <!-- ${slf4j.version} -->
<junit.version>4.12</junit.version> <!-- ${junit.version} -->
</properties>
<!-- 在 dependencyManagement 内部声明的依赖不会直接调入到当前模块中 -->
<!-- 在 dependencyManagement 内部声明的依赖主要是为了让子模块能够继承这些依赖 -->
<dependencyManagement>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>${junit.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.driver.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>${log4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${slf4j.version}</version>
<scope>test</scope>
</dependency>
<!-- Oracle 数据库驱动程序 -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>oracle-thin</artifactId>
<version>11.2.0.1.0</version>
<scope>system</scope>
<systemPath>D:/maven/non-repository/ojdbc6.jar</systemPath>
</dependency>
</dependencies>
</dependencyManagement>
</project>
4、修改子模块pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>mybatis</artifactId>
<groupId>vip.ycpower</groupId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>dynamic</artifactId>
<name>dynamic</name>
<url>http://www.ycpower.vip/mybatis/dynamic</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>10</maven.compiler.source>
<maven.compiler.target>10</maven.compiler.target>
<maven.compiler.compilerVersion>10</maven.compiler.compilerVersion>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>oracle-thin</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
</dependency>
</dependencies>
</project>
5、创建实体类human和数据库
- typer类型类型转换BooleanTypeHandler
package vip.ycpower.dynamic.type;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BooleanTypeHandler implements TypeHandler<Boolean> {
@Override
public void setParameter(PreparedStatement ps, int paramIndex , Boolean paramValue, JdbcType jdbcType) throws SQLException {
System.out.println( "【BooleanTypeHandler [ setParameter ] ==> " + paramIndex + " : " + paramValue + " 】");
ps.setString( paramIndex , paramValue ? "Y" : "N" );
}
@Override
public Boolean getResult(ResultSet resultSet, String columnLabel ) throws SQLException {
String value = resultSet.getString( columnLabel );
System.out.println( "【BooleanTypeHandler [ getResult ] ==> " + columnLabel + " : " + value + " 】");
return "Y".equalsIgnoreCase( value ) ;
}
@Override
public Boolean getResult(ResultSet resultSet, int columnIndex) throws SQLException {
String value = resultSet.getString( columnIndex );
System.out.println( "【BooleanTypeHandler [ getResult ] ==> " + columnIndex + " : " + value + " 】");
return "Y".equalsIgnoreCase( value ) ;
}
@Override
public Boolean getResult( CallableStatement callableStatement, int i) throws SQLException {
// 当执行的是 存储过程 或 调用 数据库函数时,通过该方法来处理类型转换问题
// 暂时先忽略
return null;
}
}
- HumanMapper接口定义增删改查方法
package vip.ycpower.dynamic.mapper;
import org.apache.ibatis.annotations.Param;
import vip.ycpower.dynamic.entity.Human;
import java.util.List;
public interface HumanMapper {
/**
*
* @param
* @return
*/
int persist(Human h);
int persistBach(List<Human> humanList);
/**
*
* @param id
* @return
*/
Human find(int id);
int update(Human h);
/**
*
* @param gender
* @return
*/
List<Human> query(@Param("gender") String gender,@Param("married") Boolean married);
List<Human> findByName(@Param("nameLike")String nameLike);
/**
*
* @param idList
* @return
*/
int removeBach(List<Integer> idList);
}
都统一在upgrade目录下
6、创建mybatis配置
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<!-- 为 org.malajava.dynamic.entity 包中的所有实体类取别名 ( 别名就是是 类名 )-->
<!-- 将来使用时就没有必要再指定包名了-->
<package name="vip.ycpower.dynamic.entity" />
</typeAliases>
<environments default="first">
<environment id="first">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/ycpower?useUnicode=true&characterEncoding=utf8&useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value=""/>
</dataSource>
</environment>
<environment id="second">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:ycpower"/>
<property name="username" value="itlaobing"/>
<property name="password" value="itlaobing"/>
</dataSource>
</environment>
</environments>
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql" />
<property name="Oracle" value="oracle" />
<property name="SQL Server" value="sqlserver" />
</databaseIdProvider>
<mappers>
<!-- 【强制】必须正确指定 映射器 所在的位置 -->
<mapper resource="vip/ycpower/dynamic/mapper/PandaMapper.xml"/>
<mapper resource="vip/ycpower/dynamic/mapper/HumanMapper.xml"/>
</mappers>
</configuration>
在vip/ycpower/dynamic/mapper目录下创建HumanMapper.xml
用于实现HumanMapper接口里的方法
<?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 必须跟 PandaMapper 接口的全限定名称一致 -->
<mapper namespace="vip.ycpower.dynamic.mapper.HumanMapper">
<!--
PreparedStatement ps = connection.preparedStatement( SQL , Statement.RETURN_GENERATED_KEYS ) ;
// useGeneratedKeys="true"主键是使用自动增长
PreparedStatement ps = connection.preparedStatement( SQL , Statement.NO_GENERATED_KEYS ) ; // useGeneratedKeys="false" ( 默认值 )
keyProperty="id"//主键使用数据库用的id
<when></when>标签用于判断设定的值是否等于相应值,是则赋予指定值
<otherwise></otherwise>不满足所有我when后赋予指定值
-->
<!-- int persist(Human p); -->
<insert id="persist" parameterType="Human" useGeneratedKeys="true" keyProperty="id" statementType="PREPARED">
INSERT INTO
t_humans
(
<if test=" gender != null and gender != '' "> gender,</if>
<if test="birthdate != null"> birthdate , </if>
<if test=" married != null"> married,</if>
name
)
VALUES
(
<if test=" gender != null and gender !='' ">
<choose>
<when test="gender=='男'.toString() or gender=='M'.toString() ">'男',</when>
<when test="gender=='女'.toString() or gender== 'F'.toString() ">'女',</when>
<otherwise>'未知‘,</otherwise>
</choose>
</if>
<if test="birthdate != null"> #{birthdate},</if>
<if test=" married != null">
<choose>
<when test="married == true ">'Y',</when>
<otherwise>'N',</otherwise>
</choose>
</if>
#{name}
)
</insert>
<!--int persistBach(List<Human> humanList);-->
<!-- parameterType输入映射:将Java的对象映射为SQL语句中列的值-->
<insert id="persistBach" parameterType="java.util.List" databaseId="mysql">
INSERT INTO t_humans
(name,gender,birthdate,married)
VALUES
<!-- -->
<foreach collection="list" open=" " item="h" close=" " separator=",">
(#{h.name},#{h.gender},#{h.birthdate},#{h.married,typeHandler= vip.ycpower.dynamic.type.BooleanTypeHandler})
</foreach>
</insert>
<insert id="persistBach" parameterType="list" databaseId="oracle">
<selectKey order="BEFORE" resultType="java.util.Integer" keyProperty="id">
SELECT nvl(max(id),0)+1 FROM t_humans
</selectKey>
INSERT ALL
INTO t_humans (id,name,gender,birthdate,married)
VALUES
<foreach collection="list" open=" " item="h" close=" " separator=",">
(h.id,#{h.name},#{h.gender},#{h.birthdate},#{h.married,typeHandler= vip.ycpower.dynamic.type.BooleanTypeHandler})
</foreach>
SELECT count(*) from dual
<!--SELECT count(*) from dual返回几条数据,INSERT就执行几次 -->
</insert>
<resultMap id="humanResultMap" type="Human">
<id column="id" property="id" />
<result column="name" property="name" />
<result column="gender" property="gender" />
<result column="birthdate" property="birthdate" />
<result column="married" property="married"/>
</resultMap>
<!-- Human find(int id);-->
<!--resultMap输出映射:将查询的结果映射为Java对象 -->
<select id="find" parameterType="int" resultMap="humanResultMap">
SELECT id , name , gender , birthdate,married FROM t_humans WHERE id = #{id}
</select>
<!--int update(Panda p);-->
<!-- set标签是SET子句,用于剔除最后一个字符后的逗号-->
<update id="update" parameterType="Human">
UPDATE t_humans
<set>
<if test=" name != null and name != '' "> name=#{name} ,</if>
<if test=" gender != null and gender != '' "> gender=#{gender},</if>
<if test="birthdate != null"> birthdate=#{birthdate} , </if>
</set>
WHERE id=#{id}
</update>
<sql id="baseQuery">
SELECT id,name ,gender,birthdate,married FROM t_humans
</sql>
<select id="query" resultMap="humanResultMap ">
<include refid="baseQuery" />
<where>
<if test="gender !=null and gender !='' ">
AND gender=#{gender}
</if>
<if test="married !=null">
AND married=
<choose>
<when test="married ==true">'Y'</when>
<otherwise>'N'</otherwise>
</choose>
</if>
<trim prefixOverrides="AND |OR "> </trim>
<!--用于去掉第一个满足条件前的AND或OR-->
</where>
</select>
<select id="findByName" parameterType="java.lang.String" resultMap="humanResultMap">
<include refid="baseQuery"/>
<bind name="name" value=" '%'+nameLike+'%'"/>
WHERE name LIKE #{name}
</select>
<!-- int removeBach(List<Integer> idList);-->
<delete id="removeBach" parameterType="java.util.List" >
DELETE FROM t_humans WHERE
<choose>
<when test="list !=null and list.size()>0">
id IN
<foreach collection="list" open="(" item="id" close=")" separator=",">
#{id}
</foreach>
</when>
<otherwise>1=2</otherwise>
</choose>
</delete>
</mapper>
7、创建测试类
package vip.ycpower.dynamic.test;
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 vip.ycpower.dynamic.entity.Human;
import vip.ycpower.dynamic.mapper.HumanMapper;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
public class HumanTest {
private SqlSession session;
private HumanMapper mapper;
private Calendar c;
public @Before void init()throws IOException {
//1、创建用于 创建 SqlSessionFactory 对象的 对象bulider
SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
//2、创建 SqlSessionFactory 对象factory
InputStream in= Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory=builder.build(in);
// 3、开启会话 ( 创建 SqlSession 对象 )
this.session=factory.openSession();
// 获取HumanMapper的一个对象 mapper
this.mapper=session.getMapper(HumanMapper.class);
//获取当前时间
c=Calendar.getInstance();
c.clear();
}
public @Test void findHuman(){
Human h=mapper.find(1);
System.out.println(h);
}
public @Test void persistHuman1(){
List<Human> humanList=new ArrayList<>();
Human h1=new Human();
h1.setName("张翠山");
h1.setGender("男");
c.set(1409,8,21);
h1.setBirthdate(c.getTime());
h1.setMarried(true);
Human h2=new Human();
h2.setName("范遥");
h2.setGender("男");
c.set(1409,1,23);
h2.setBirthdate(c.getTime());
h2.setMarried(false);
humanList.add(h1);
humanList.add(h2);
int count=mapper.persistBach(humanList);
System.out.println(count);
//5、 如果是 DML 操作,需要提交事务
session.commit();
}
public @Test void persistHuman2(){
Human h=new Human();
h.setName("殷素素");
h.setGender("F");
h.setMarried(true);
int count=mapper.persist(h);
System.out.println(count);
session.commit();
}
public @Test void persistHuman3(){
Human h=new Human();
h.setName("殷素素");
h.setGender("F");
h.setMarried(true);
int count=mapper.persist(h);
System.out.println(count);
session.commit();
}
public @Test void updateHuman(){
Human h=mapper.find(5);
h.setName("周芷若");
int count=mapper.update(h);
System.out.println(count);
session.commit();
}
public @Test void queryHuman1(){
List<Human> list=mapper.query("男",null);
for (Human x :list){
System.out.println(x);
}
}
public @Test void findByName(){
List<Human> list=mapper.findByName("素");
for (Human x : list){
System.out.println(list);
}
}
public @Test void deleteBach(){
List<Integer> idList=new ArrayList<>();
idList.add(7);
idList.add(8);
idList.add(9);
int count=mapper.removeBach(idList);
System.out.println(count);
session.commit();
}
// 6、关闭会话
public @After detory(){
if(session !=null){
session.close();
}
}
}