在之前我们为了查找sql,需要指定该sql的唯一标识。namespace+id;所有的方法名都叫做:selectList selectOne( ),但是我们习惯自己命名方法。
我们可以自己定义方法。
(1).定义一个相关的接口
package com.dao;
import com.entity.Student;
import java.util.List;
/**
* @program: mybatis02
* @description:
* @author: 秦王
* @create: 2021-12-03 19:48
**/
public interface StuDao {
public List<Student> selectAll();
public Student selectById(int id);
public int insertStu(Student student);
public int updateStu(Student student);
public int deleteStu(int id);
}
(2).映射文件
注意:namespace必须和接口所在的路径对应
<?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">
<!--namespace:命名空间:它的值现在可以随便写。
以后必须和dao接口对应。
-->
<mapper namespace="com.dao.StuDao">
<!--查找所有表中数据-->
<select id="selectAll" resultType="com.entity.Student">
select * from student
</select>
<!--查询单条数据-->
<select id="selectById" resultType="com.entity.Student">
select * from student where sid=#{sid}
</select>
<!--添加-->
<insert id="insertStu" >
insert into student values(null,#{sname},#{age},#{class_id})
</insert>
<!--修改-->
<update id="updateStu" >
update student set sname=#{sname},age=#{age},class_id=#{class_id} where sid=#{sid}
</update>
<!--删除-->
<delete id="deleteStu" >
delete from student where sid=#{sid}
</delete>
</mapper>
import com.dao.StuDao;
import com.entity.Student;
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 java.io.Reader;
import java.util.List;
/**
* @program: mybatis02
* @description:
* @author: 秦王
* @create: 2021-12-03 20:03
**/
public class TestStuDao {
private SqlSession session;
@Before
public void before() throws Exception{
//1.首先要读取mybatis的配置文件, 连接数据库
Reader reader = Resources.getResourceAsReader("mybatis.xml");
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(reader);
//2.获取Session对象 --》Connection对象
session=build.openSession();
}
/*查询所有*/
@Test
public void selectAll(){
StuDao stuDao = session.getMapper(StuDao.class);
List<Student> students = stuDao.selectAll();
System.out.println(students);
}
/*查询单条数据*/
@Test
public void selectById(){
StuDao stuDao = session.getMapper(StuDao.class);
Student student = stuDao.selectById(3);
System.out.println(student);
}
/*添加*/
@Test
public void insertStu(){
StuDao stuDao = session.getMapper(StuDao.class);
Student student = new Student();
student.setSname("白胡子");
student.setAge(72);
student.setClass_id(1);
int i = stuDao.insertStu(student);
System.out.println(i);
session.commit();
}
/*修改*/
@Test
public void updateStu(){
StuDao stuDao = session.getMapper(StuDao.class);
Student student = new Student(3,"黑胡子",50,1);
int i = stuDao.updateStu(student);
System.out.println(i);
session.commit();
}
/*删除*/
@Test
public void deleteStu(){
StuDao stuDao = session.getMapper(StuDao.class);
int i = stuDao.deleteStu(4);
System.out.println(i);
session.commit();
}
}
(3).特殊字符
有两种方法:第一种是转移字符;第二种是使用<![CDATA[ 特殊字符或SQL语句 ]]>。
<!--按照年龄查询范围 第一种方法:转移字符-->
<select id="selectAge" resultType="com.entity.Student">
select * from student where age>#{min} and age<#{max}
</select>
<!--第二种:使用<![CDATA[ 特殊字符或SQL语句 ]]>-->
<select id="selectByage" resultType="com.entity.Student">
<![CDATA[select * from student where age>#{min} and age<#{max}]]>
</select>
mybatis的优化
1.引入db属性文件。
1.1 定义一个数据库属性文件 格式 ××.properties
jdbc.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai
jdbc.driverName=com.mysql.cj.jdbc.Driver
jdbc.username=root // --->对应自己的username
jdbc.password= //密码对应自己设置的密码
2.引入日志文件。作用:更好的显示sql语句
2.1 引入日志jia包
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
2.2 引入日志的配置文件 log4j.properties
### 设置###
log4j.rootLogger = debug,stdout,D,E### 输出信息到控制抬 ###
log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n### 输出DEBUG 级别以上的日志到=E://logs/error.log ###
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
log4j.appender.D.File = D://logs/log.log
log4j.appender.D.Append = true
log4j.appender.D.Threshold = DEBUG
log4j.appender.D.layout = org.apache.log4j.PatternLayout
log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n### 输出ERROR 级别以上的日志到=E://logs/error.log ###
log4j.appender.E = org.apache.log4j.DailyRollingFileAppender
log4j.appender.E.File =D://logs/error.log
log4j.appender.E.Append = true
log4j.appender.E.Threshold = ERROR
log4j.appender.E.layout = org.apache.log4j.PatternLayout
log4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n
效果
3.解决列明和属性名不一致情况
3.1 第一种是给列起别名;让别名和属性名一致
<select id="selectById" resultType="com.ykq.entity.Order">
select order_id id,order_no no,order_price price from orders where order_id=#{id}
</select>
3.2 第二种方法是:使用resultMap标签 来完成属性和列的映射关系
<!--
id:唯一标识
type: 类型 ; 表与哪个实体类的映射
<id 主键的映射关系 column="列名" property="属性名"/>
<result 普通字段/>autoMapping=true 表示自动映射。默认true
-->
<resultMap id="My01" type="com.ykq.entity.Order" >
<!--id必写-->
<id property="id" column="order_id"/>
<result property="no" column="order_no"/>
<result property="price" column="order_price"/>
</resultMap>
<!--注意:使用了resultMap不能在使用resultType-->
<select id="selectById" resultMap="My01" >
select * from orders where order_id=#{id}
</select>