- 使用到的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教程