- 使用到的mysql语句;
- mybatis 日志配置;
- mybatis CRUD简单操作
1. 使用到的mysql语句
本次我们使用的是mysql数据库进行mybatis简单的crud操作,对于mysql的操作,我们自然可以使用Navicat进行图形化的操作,这里给出我用到的语句;
Mysql 数据类型:支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。具体可以参考:
Mysql数据类型:菜鸟教程-Mysql数据类型
--建库
CREATE DATABASE test;
--建表
CREATE TABLE IF NOT EXISTS student(
stu_id INT AUTO_INCREMENT,
stu_name VARCHAR(20) NOT NULL,
stu_age int,
stu_score double,
PRIMARY KEY ( stu_id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--可能会遇到的语句
--修改字段属性
ALTER TABLE student CHANGE stu_name VARCHAR(30);
--增加字段名称
ALTER TABLE student ADD COLUMN class_id int(4);
--字段重命名
ALTER TABLE student CHANGE score stu_score DOUBLE NOT NULL;
注解:
AUTO_INCREMENT : 每一个表只能由一个AUTO_INCREMENT列, 告知Mysql本列每当增加一行时,自动增量。从而该列可以作为主键值。如果一列被指定为AUTO_INCREMENT,如果我们使用INSERT语句插入了一个新值(唯一的),那么下一次增量就在这个值的基础上进行递增;
常见的Mysql引擎: mysql的引擎在我们对表进行操作时,都是通过mysql的引擎来完成的,常见的mysql引擎:
InnoDB: 事务处理引擎,不支持全文本搜索;
MyISAM:性能较高的引擎,支持全文本搜索,但是不支持事物;
MEMORY: 功能等同于MyISAM,但是由于数据库存储在内存(不是磁盘中)速度较快;
2. Mybatis 日志配置
2.1 首先我们回顾下java主流的日志框架:本文采用:common-logging + log4j
Java 日志框架:细说JAVA主流的日志工具库
2.2 Mybatis 的内置日志工厂提供日志功能,内置日志工厂将日志交给以下其中一种工具作代理:
- SLF4J
- Apache Commons Logging
- Log4j 2
- Log4j
- JDK logging
MyBatis 内置日志工厂基于运行时自省机制选择合适的日志工具。它会使用第一个查找得到的工具(按上文列举的顺序查找)。如果一个都未找到,日志功能就会被禁用。
如果想使用其它日志工具,你可以通过在 MyBatis 配置文件 mybatis-config.xml 里面添加一项 setting 来选择别的日志工具。
<configuration>
<settings>
...
<setting name="logImpl" value="LOG4J"/>
...
</settings>
</configuration>
logImpl 可选的值有:SLF4J、LOG4J、LOG4J2、JDK_LOGGING、COMMONS_LOGGING、STDOUT_LOGGING、NO_LOGGING
2.3 根据自己的mapper.xml映射文件中的命名空间值来配置mybatis日志
mapper.xml
<mapper namespace="com.mybatis3.dao.mapper">
<select id="selectStudent" resultType="com.mybatis3.model.Student">
select * from student where id = #{id}
</select>
</mapper>
log4j.properties文件:
# 全局日志配置
log4j.rootLogger=ERROR, stdout
# MyBatis 日志配置...
log4j.logger.com.mybatis3.dao.mapper=TRACE
# 控制台输出...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
3. Mybatis简单的CRUD操作
3.1 数据库表结构:
3.2 项目结构:
项目代码展示:
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>com.qian</groupId>
<artifactId>mybatis</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>mybatis</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<!-- <dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.5</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.12</version>
</dependency> -->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
</project>
model层
Student就是一个简单的JAVA BEAN对象,只是增加了几个简单的构造方法和重写了toString()方法;
dao层以及mapper.xml文件
IStudentDao.java
public interface IStudentDao {
//增
public int insertStudent(Student student);
//删
public boolean deleteStudentById(int id);
//改
public boolean updateStudent(Student student);
//查
public Student selectStudentById(int id);
public List<Student> selectAllStudents();
public List<Student> selectStudentByName(String name);
public Map<String, Student> selectStudentToMap(String mapKey);
public List<Student> selectAllStudentsByConditions(String name,int ageStart, int ageEnd);
}
StudentMapper.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.mybatis3.dao.mapper">
<!-- 解决字段冲突问题:主要是针对由返回结果的也就是insert语句 -->
<!-- 解决方式1: 使用别名 select stu_name name, -->
<!-- 解决方式2: restultMap查询结果行的映射处理:字段和属性不一致问题
数据库表student(stu_id,stu_name,stu_age,score)
javabean中的student[id, name, age, score] -->
<resultMap type="com.mybatis3.model.Student" id="studentResultMapper">
<id column="stu_id" property="id"/>
<result column="stu_name" property="name"/>
<result column="stu_age" property="age"/>
</resultMap>
<insert id="insertStudent" parameterType="com.mybatis3.model.Student">
insert into student(stu_name,stu_age,score)
values(#{name},#{age},#{score})
<!--返回刚插入的这条记录的id-->
<selectKey keyProperty="id" resultType="int">
select @@identity
<!-- select last_insert_id(); -->
</selectKey>
</insert>
<delete id="deleteStudentById">
<!-- 此处#{id}仅仅是一个占位符 -->
delete from student where stu_id = #{id};
</delete>
<update id="updateStudent" parameterType="com.mybatis3.model.Student">
update student set stu_name=#{name}, stu_age=#{age}, score=#{score}
where stu_id=#{id}
</update>
<!-- resultType将 id name age score自动转成Student
并非指最终结果集的类型而是指将查询出来DB中的每一条记录的字段封装成的类型 -->
<select id="selectStudentById" resultType="com.mybatis3.model.Student">
<!-- 使用别名解决字段冲突问题 -->
select stu_id id, stu_name name, stu_age age, score from student where stu_id=#{id};
</select>
<select id="selectAllStudents" resultType="com.mybatis3.model.Student" resultMap="studentResultMapper">
select stu_id, stu_name, stu_age, score from student
</select>
<!-- 模糊查询字符串拼接问题 -->
<select id="selectStudentByName" resultType="com.mybatis3.model.Student" resultMap="studentResultMapper">
<!-- #{xxx}为占位符:mybatis会进行动态拼接,使用的是PreparedStatement的setParameter来设置参数 -->
select stu_id, stu_name, stu_age, score from student where stu_name like '%' #{name} '%'
<!-- 为第一种的正式写法 -->
<!-- select id, name, age, score from student where name like concat('%' #{name} '%') -->
<!-- ${value}为字符串拼接:mybatis会进行硬编码拼接,使用的是Statement: sql注入,没有预编译效率低下 只能是value -->
<!-- select id, name, age, score from student where name like '%${value}%' -->
</select>
<select id="selectStudentToMap" resultType="com.mybatis3.model.Student" resultMap="studentResultMapper">
select stu_id, stu_name, stu_age, score from student
</select>
<!-- 传递多个参数,#{参数map的key} -->
<select id="selectAllStudentsByConditions" resultType="com.mybatis3.model.Student" resultMap="studentResultMapper">
select stu_id, stu_name, stu_age, score
from student
where stu_name like '%' #{name} '%'
and stu_age between #{ageStart} and #{ageEnd}
</select>
</mapper>
mybatis3.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">
<configuration>
<!-- 注册属性文件位置和 属性的值 -->
<!-- 在 properties 元素体内<properties>指定的属性首先被读取。
然后根据 properties 元素中的 resource 属性读取类路径下属性文件或根据 url 属性指定的路径读取属性文件,并覆盖已读取的同名属性。
最后读取作为方法参数传递的属性,并覆盖已读取的同名属性。
因此,通过方法参数传递的属性具有最高优先级,resource/url 属性中指定的配置文件次之,最低优先级的是 properties 属性中指定的属性。 -->
<properties resource="db-mysql/mysql.properties">
<!-- 开启为占位符指定一个默认值特性和三元排队特性 -->
<property name="org.apache.ibatis.parsing.PropertyParser.enable-default-value" value="true"/>
<!-- 过 properties 元素的子元素来传递 -->
<property name="dbname" value="test"/>
</properties>
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<environments default="jdbc">
<environment id="jdbc">
<!-- Mybatis支持两种事物:JDBC MANAGED(使用容器来管理事物例如spring) -->
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<!-- 使用properties中指定的属性值,优先权最低 -->
<property name="url" value="${jdbc.url}${dbname}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!-- 注册映射文件位置 MyBatis到哪里去找到这些定义 SQL 映射语句 -->
<mappers>
<!-- RESOURCE: 使用相对于类路径的资源引用 -->
<mapper resource="com/mybatis3/dao/StudentMapper.xml"/>
<!-- URL: 使用完全限定资源定位符(URL)
<mapper url="file:///var/mappers/StudentMappper.xml"/>
CLASS: 值为dao接口的全名:需要满足如下三个条件
(1) 映射文件的名字和dao层接口的名字一样 也就是映射文件名字为IStudentDao.xml;
(2) 映射文件放到dao层包下
(3) 在映射文件下<mapper namespace="com.mybatis3.dao.IStudent">
<mapper class="com.mybatis3.dao.IStudentDao"/>
PACKAGE:将包内的映射器接口实现全部注册为映射器
<package name="com.mybatis3.dao"/>
-->
</mappers>
</configuration>
SqlSessionUtil.java
主要是用于获取SqlSession和关闭SqlSession
package com.mybatis3.dao.utils;
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;
public class SqlSessionUtil {
// 线程安全 的 重量级对象,与数据库一一对应,所以一个应用由一个对象就可以,
private static SqlSessionFactory sqlSessionFactory = null;
/*SqlSession对象是对应于数据库的一次会话,一次会话以sqlsession创建开始,以close关闭;
*SqlSession对应于一次会话,所以它是线程不安全的,所以用时创建,用完马上关闭,关闭时它会检测事物是否
*提交,如果提交就直接关闭,如果没有提交就会回归
*/
static{
//获取sqlSession: 读取配置文件--> SqlSessionFactoryBuilder -> sqlSessionFactory -> sqlSession
String resource = "mybatis3.xml";
InputStream inputstream;
SqlSessionFactoryBuilder sqlSessionFactoryBuilder =null;
try {
inputstream = Resources.getResourceAsStream(resource);
sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
sqlSessionFactory = sqlSessionFactoryBuilder.build(inputstream);
} catch (IOException e) {
e.printStackTrace();
}finally{
//builder主要是用来创建Factory对象,只要创建完factory对象它就可以销毁,所以可以将它放到一个方法内;
if(sqlSessionFactoryBuilder!=null){
sqlSessionFactoryBuilder = null;
}
}
}
public static SqlSession getSqlSession(){
if(sqlSessionFactory!=null){
return sqlSessionFactory.openSession();
}
return null;
}
public static SqlSession getSqlSession(boolean autoCommit){
if(sqlSessionFactory!=null){
return sqlSessionFactory.openSession(autoCommit);
}
return null;
}
public static void closeSqlSession(SqlSession sqlSession){
if(sqlSession!= null){
sqlSession.close();
}
}
}
StudentDao.java
package com.mybatis3.dao;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import com.mybatis3.dao.utils.SqlSessionUtil;
import com.mybatis3.model.Student;
public class StudentDao implements IStudentDao {
private SqlSession sqlSession = null;
public int insertStudent(Student student) {
try{
sqlSession = SqlSessionUtil.getSqlSession();
sqlSession.insert("insertStudent",student);
sqlSession.commit();
}finally{
SqlSessionUtil.closeSqlSession(sqlSession);
}
return student.getId();
}
public boolean deleteStudentById(int id) {
try{
sqlSession = SqlSessionUtil.getSqlSession();
int resultCol = sqlSession.delete("deleteStudentById", id);
sqlSession.commit();
if(resultCol > 0){
return true;
}
}finally{
SqlSessionUtil.closeSqlSession(sqlSession);
}
return false;
}
public boolean updateStudent(Student student) {
try{
sqlSession = SqlSessionUtil.getSqlSession();
int resultCol = sqlSession.update("updateStudent", student);
sqlSession.commit();
if(resultCol > 0){
return true;
}
}finally{
SqlSessionUtil.closeSqlSession(sqlSession);
}
return false;
}
public Student selectStudentById(int id) {
try{
sqlSession = SqlSessionUtil.getSqlSession();
Student student = sqlSession.selectOne("selectStudentById", id);
return student;
}finally{
SqlSessionUtil.closeSqlSession(sqlSession);
}
}
public List<Student> selectAllStudents() {
try{
sqlSession = SqlSessionUtil.getSqlSession();
return sqlSession.selectList("selectAllStudents");
}finally{
SqlSessionUtil.closeSqlSession(sqlSession);
}
}
public List<Student> selectStudentByName(String name) {
try{
sqlSession = SqlSessionUtil.getSqlSession();
return sqlSession.selectList("selectStudentByName", name);
}finally{
SqlSessionUtil.closeSqlSession(sqlSession);
}
}
/**
* @param mapKey: 查询出的map所需要使用的key,这个key为数据库的字段名,也是最终每条map记录的key值
* 若数据库中mapKey的值不唯一,后面的结果会把前面的结果覆盖掉;
*/
public Map<String, Student> selectStudentToMap(String mapKey) {
try{
sqlSession = SqlSessionUtil.getSqlSession();
return sqlSession.selectMap("selectStudentToMap", mapKey);
}finally{
SqlSessionUtil.closeSqlSession(sqlSession);
}
}
public List<Student> selectAllStudentsByConditions(String name, int ageStart, int ageEnd) {
try{
//sqlSession.listXXX方法中没有传入一个数组的方法,所有只能封装成集合
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("name", name);
paramMap.put("ageStart", ageStart);
paramMap.put("ageEnd", ageEnd);
sqlSession = SqlSessionUtil.getSqlSession();
return sqlSession.selectList("selectAllStudentsByConditions",paramMap);
}finally{
SqlSessionUtil.closeSqlSession(sqlSession);
}
}
}
StudentService.java
serviec层,调用dao层的代码
package com.mybatis3.service;
import java.util.List;
import java.util.Map;
import com.mybatis3.dao.IStudentDao;
import com.mybatis3.model.Student;
public class StudentService implements IStudentService {
private IStudentDao studentDao;
public int addStudent(Student student) {
return studentDao.insertStudent(student);
}
public boolean removeStudentById(int id){
return studentDao.deleteStudentById(id);
}
//改
public boolean modifyStudent(Student student){
return studentDao.updateStudent(student);
}
//查
public Student findStudentById(int id){
return studentDao.selectStudentById(id);
}
public List<Student> findAllStudents(){
return studentDao.selectAllStudents();
}
public List<Student> findStudentByName(String name){
return studentDao.selectStudentByName(name);
}
public Map<String, Student> findStudentToMap(String mapKey){
return studentDao.selectStudentToMap(mapKey);
}
public IStudentDao getStudentDao() {
return studentDao;
}
public void setStudentDao(IStudentDao studentDao) {
this.studentDao = studentDao;
}
public List<Student> findAllStudents(String name, int ageStart, int ageEnd) {
return studentDao.selectAllStudentsByConditions(name, ageStart, ageEnd);
}
}
StudentTest.java
测试类
package com.qian.mybatis;
import java.util.List;
import java.util.Map;
import org.junit.Before;
import org.junit.Test;
import com.mybatis3.dao.StudentDao;
import com.mybatis3.model.Student;
import com.mybatis3.service.StudentService;
public class StudentTest {
private StudentService studentService=null;
@Before
public void before(){
studentService = new StudentService();
studentService.setStudentDao(new StudentDao());
}
@Test
public void testInsert(){
Student student = new Student();
student.setName("张三");
student.setAge(27);
student.setScore(95.5);
int id = studentService.addStudent(student);
System.out.println("id = "+id);
}
@Test
public void testDelete(){
boolean result = studentService.removeStudentById(6);
System.out.println(result);
}
@Test
public void testUpdate(){
Student student = new Student("LISI",23,99.9);
student.setId(11);
boolean result = studentService.modifyStudent(student);
System.out.println(result);
}
@Test
public void testFindStudentById(){
Student student = studentService.findStudentById(11);
System.out.println(student);
}
@Test
public void testFindAllStudents(){
List<Student> students = studentService.findAllStudents();
System.out.println(students);
}
@Test
public void testFindStudentByName(){
List<Student> students = studentService.findStudentByName("李四");
System.out.println(students);
}
@Test
public void testFindStudentToMap(){
Map<String,Student> students = studentService.findStudentToMap("name");
System.out.println(students);
}
@Test
public void testFindAllStudents2(){
List<Student> students = studentService.findAllStudents("张三", 22, 35);
System.out.println(students);
}
}
参考文献:
【1】:mybatis3官网参考文档
【2】:bjpowernodeMybatis教程