mybatis-config.xml
<?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">
<!-- MyBatis的全局配置文件 -->
<configuration>
<!--1.配置开发环境 -->
<environments default="develop">
<environment id="develop">
<!-- 将事务交给JDBC管理 mybatis会自动开启事务,但需要手动提交
MANAGED:自己手动管理事务-->
<transactionManager type="JDBC"></transactionManager>
<!-- 是否配置连接池信息,type的取值:
JNDI:已过时
POOLED:使用连接池(推荐) 可以减少连接创建次数,提高执行效率
UNPOOLED:不使用连接池
-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/yonghedb?characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<!--2.导入XxxMapper.xml文件 ,如果Mapper文件有多个,可以通过多个mapper标签导入
resource属性会直接到类目录下去找指定位置的文件
C:\Users\Administrator\Desktop\tts9Windows\tts9\workspace\CGB-MyBatis-01\target\classes
-->
<mappers>
<!-- <mapper resource="EmpMapper.xml"/> -->
<!-- <mapper class="cn.tedu.dao.EmpMapper"/> -->
<package name="cn.tedu.dao"/>
</mappers>
</configuration>
EmpMapper.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值应该保证唯一
在程序中通过[ namespace + id ]定位到要执行哪一条SQL语句
-->
<mapper namespace="cn.tedu.dao.EmpMapper">
<!-- 通过select insert update d elete 标签来存放要执行的SQL -->
<!--查询Emp表中所有员工信息 id 要求当前文件中的id值必须是独一无二的,不能重复
resultType属性:指定查询的结果要存放在哪个类型的对象中
-->
<select id="findAll" resultType="cn.tedu.pojo.Emp">
select * from emp
</select>
<insert id="insert">
insert into emp value(null,"赵云","快递",3000.0)
</insert>
<insert id="insert2">
insert into emp value(null,#{name},#{job},#{salary})
</insert>
<update id ="update">
update emp set job = #{job},salary = #{salary} where name = #{name}
</update>
<delete id="delete">
delete from emp where name = #{name}
</delete>
<select id="findAll2" resultType="cn.tedu.pojo.Emp">
select ${colStr} from emp
</select>
<!-- 动态SQL标签 <if> <where> <foreach> -->
<!-- 根据员工薪资区间查询员工信息 -->
<select id="findBySal" resultType="cn.tedu.pojo.Emp">
select * from emp
where 1=1
<if test="minSal != null">
and salary >= #{minSal}
</if>
<if test="maxSal != null">
and salary <= #{maxSal}
</if>
</select>
<!-- 根据员工薪资区间查询员工信息 <where> -->
<select id="findBySal2" resultType="cn.tedu.pojo.Emp">
select * from emp
<where>
<if test="minSal != null">
salary >= #{minSal}
</if>
<if test="maxSal != null">
and salary <= #{maxSal}
</if>
</where>
</select>
<!-- <foreach> collection open close item separator 属性 -->
<!-- 案例1:根据员工的ID批量删除员工信息 -->
<delete id="deleteByIds">
delete from emp where id in
<foreach collection="list" open="(" item="id" separator="," close=")">
#{id}
</foreach>
</delete>
<!-- 案例2:根据员工的ID批量更新员工信息 -->
<update id="updateByIds">
update emp set salary=salary + #{money}
where id in
<foreach collection="arrayIds" open="(" item="id" separator="," close=")">
#{id}
</foreach>
</update>
</mapper>
log4j.properties
# Global logging configuration
#log4j.rootLogger=DEBUG, stdout,LOGFILE
log4j.rootLogger=DEBUG, console,LOGFILE
# Console output...
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%5p [%t] %d{yyyy-MM-dd HH:mm:ss,SS} - %m%n
# File output...
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
#log4j.appender.LOGFILE.file=d:/myLog.log
log4j.appender.LOGFILE.file=./myLog.log
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=%5p [%t] %d{yyyy-MM-dd HH:mm:ss,SS} - %m%n
EmpMapper.java
package cn.tedu.dao;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import cn.tedu.pojo.Emp;
/**
* @author scXu:
* @version 创建时间:2021年4月19日 下午5:12:51
*/
//接口的全类名 cn.tedu.dao.EmpMapper
public interface EmpMapper {
/*练习01:查询emp表中的所有员工信息*/
@Select("select * from emp")
public List<Emp> findAll();
/*练习02:新增员工信息*/
@Insert("insert into emp value(null,'大山','厨师',6000.0)")
public void insert();
/*练习03:新增员工信息-有占位符*/
@Insert("insert into emp value(null,#{name},#{job},#{salary})")
public void insert2(Map map);
/*练习04:修改员工信息-有占位符*/
@Update("update emp set job=#{job} ,salary=#{salary} where name=#{name}")
public void update(Emp emp);
/*练习05:删除员工信息-有占位符*/
@Delete("delete from emp where name=#{name}")
public void delete(Emp emp);
}
EmpMapperImpl.java 模拟mybatis底层实现
package cn.tedu.dao;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import cn.tedu.pojo.Emp;
/**
* @author scXu:
* @version 创建时间:2021年4月19日 下午5:34:39
*/
public class EmpMapperImpl implements EmpMapper {
private SqlSession session = null;
//构造函数
public EmpMapperImpl(SqlSession session){
this.session = session;
}
/*查询所有的员工信息*/
@Override
public List<Emp> findAll(){
//获取当前类父接口的全类名-namesapce
String interfaceName = this.getClass().getInterfaces()[0].getName();
String methodName = Thread.currentThread().getStackTrace()[1].getMethodName();
//StackTraceElement[] st = Thread.currentThread().getStackTrace();
// for(StackTraceElement stackTrace : st ){
// System.out.println(stackTrace);
// }
List<Emp> list = session.selectList(interfaceName+"."+methodName);
return list;
}
@Override
public void insert() {
}
@Override
public void insert2(Map map) {
}
@Override
public void update(Emp emp) {
}
@Override
public void delete(Emp emp) {
}
}
MybatisTest
package cn.tedu;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
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 cn.tedu.dao.EmpMapper;
import cn.tedu.dao.EmpMapperImpl;
import cn.tedu.pojo.Emp;
/**
* @author scXu:
* @version 创建时间:2021年4月16日 上午9:50:48
*/
public class MybatisTest03{
SqlSession session = null;
@Before
public void before() throws Exception {
//1.读取Mybatis核心配置文件中的配置信息(mybatis-config-xml)
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//2.基于上面读取的配置信息获取SqlSessionFactory对象(工厂)
SqlSessionFactory fac = new SqlSessionFactoryBuilder().build(in);
//3.通过工厂对象打开与数据库的连接(即获取SqlSession对象)
//session = fac.openSession(true); //默认提交
session = fac.openSession(); // 默认是false,需要自己手动提交
}
/*测试mybatis 的mapper接口开发:查询员工表所有信息*/
@Test
public void findAll(){
//获取EmpMapper接口的子类(由mybatis提供)的对象实例
EmpMapper mapper = session.getMapper(EmpMapper.class);
//接口的全类名= namespace值,方法名=SQL标签的id值
List<Emp> list = mapper.findAll();
for(Emp emp : list){
System.out.println(emp);
}
}
/*测试mybatis 的mapper接口开发:新增员工信息*/
@Test
public void testInsert(){
EmpMapper mapper = session.getMapper(EmpMapper.class);
mapper.insert();
session.commit();
}
/*测试mybatis 的mapper接口开发:新增员工信息-带占位符*/
@Test
public void testInsert2(){
EmpMapper mapper = session.getMapper(EmpMapper.class);
Map map = new HashMap();
map.put("name","袁绍");
map.put("job","三公");
map.put("salary",998.0);
mapper.insert2(map);
session.commit();
}
/*练习04:修改员工信息-有占位符*/
@Test
public void update(){
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = new Emp();
emp.setJob("yundongyuan");
emp.setName("马云");
emp.setSalary(200.0);
mapper.update(emp);
session.commit();
}
}
pom.xml
<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>cn.tedu</groupId>
<artifactId>CGB-MyBatis-03</artifactId>
<version>0.0.1-SNAPSHOT</version>
<dependencies>
<!-- junit单元测试 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.9</version>
</dependency>
<!-- mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.8</version>
</dependency>
<!-- 整合log4j -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.6.4</version>
</dependency>
</dependencies>
</project>
Emp.java
package cn.tedu.pojo;
/**
* @author scXu:
* @version 创建时间:2021年4月13日 下午7:21:59
* Emp员工类
*/
public class Emp{
private int id;
private String name;
private String job;
private double salary;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public Emp() {}
public Emp(int id, String name, String job, double salary) {
this.id = id;
this.name = name;
this.job = job;
this.salary = salary;
}
@Override
public String toString() {
return "Emp [id=" + id + ", name=" + name + ", job=" + job + ", salary=" + salary + "]";
}
}