mybatis小例子
2018-08-08 本文已影响0人
cccccttttyyy
什么是Mybatis
MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。
建立Mybatis工程
在maven工程pom中添加
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
mybatis的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>
<typeAliases>#对pojo的路径起别名，供mapper的xml文件使用
<typeAlias alias="Student" type="com.tsrain.domain.Student"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>#com.mysql.jdbc.Driver
<property name="url" value="${url}"/>#jdbc:mysql://localhost:3306/person?serverTimezone=GMT%2B8
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/StudentMapper.xml"/>
</mappers>
environment主要体现了对事务和连接池的配置，mappers 元素则是包含一组 mapper 映射器
resource是mapper文件的相对地址，由于放在src层级下出现了找不到资源的情况，所以我放到了resource文件夹下。
映射器（mapper）类实现方式
通过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="org.mybatis.example.BlogMapper">#这个路径是将mapper的xml文件映射成一个文件，文件的路径在这设置。
<select id="selectBlog" resultType="Blog">
select * from Blog where id = #{id}
</select>
</mapper>
命名空间“org.mybatis.example.BlogMapper”中定义了一个名为“selectBlog”的映射语句，这样它就允许你使用指定的完全限定名“org.mybatis.example.BlogMapper.selectBlog”来调用映射语句
Mapper XML 文件中sql语句实现
<select id="selectPerson" parameterType="int" resultType="hashmap">
SELECT * FROM PERSON WHERE ID = #{id}
</select>
这个语句被称作 selectPerson，接受一个 int（或 Integer）类型的参数，并返回一个 HashMap 类型的对象，其中的键是列名，值便是结果行中的对应值，其他类似的语句如下。
<insert id="insertAuthor">
insert into Author (id,username,password,email,bio)
values (#{id},#{username},#{password},#{email},#{bio})
</insert>
<update id="updateAuthor">
update Author set
username = #{username},
password = #{password},
email = #{email},
bio = #{bio}
where id = #{id}
</update>
<delete id="deleteAuthor">
delete from Author where id = #{id}
</delete>
通过注解方式实现
package org.mybatis.example;
public interface BlogMapper {
@Select("SELECT * FROM blog WHERE id = #{id}")
Blog selectBlog(int id);
}
构建 SqlSessionFactory
从xml文件中构建，SqlSessionFactory 的实例可以通过 SqlSessionFactoryBuilder 获得。而 SqlSessionFactoryBuilder 则可以从 XML 配置文件或一个预先定制的 Configuration 的实例构建出 SqlSessionFactory 的实例。
String resource = "**/mybatis-config.xml";//mybatis配置文件路径
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
获取SqlSession
SqlSessionFactory是核心，有了 SqlSessionFactory ，就可以从中获得 SqlSession 的实例。SqlSession 完全包含了面向数据库执行 SQL 命令所需的所有方法。你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句。
SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = mapper.selectBlog(101);
} finally {
session.close();
}
完整的代码：
数据库中的简单表：
image.png
Pojo:
package com.tsrain.domain;
public class Student {
private int id;
private String name;
private int age;
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 void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
public int getAge() {
return age;
}
}
mapper文件 （放在resource的mapper文件夹下）
<?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.tsrain.domain.StudentMapper">
<!-- 替代，语句中的内容,简写 -->
<sql id="cols">
id,name,age
</sql>
<!-- 查询所有记录 -->
<select id="listAll" resultType="Student">
select <include refid="cols"/> from student
</select>
<!-- 查询一个用户 --> <!-- 别名替代，简写-->
<select id="getOne" parameterType="int" resultType="Student">
select <include refid="cols"/> from student where id= #{id}
</select>
<!-- 插入一个用户 -->
<insert id="insertOne" parameterType="Student" >
insert into student( <include refid="cols"/> ) values (#{id},#{name},#{age})
</insert>
<!-- 更新一个用户 -->
<insert id="updateOne" parameterType="Student" >
update student set name=#{name},age=#{age} where id=#{id}
</insert>
<!-- 删除一个用户 -->
<update id="deleteOne" parameterType="Student" >
delete from student where id= #{id}
</update>
</mapper>
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>
<typeAlias alias="Student" type="com.tsrain.domain.Student"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/person?serverTimezone=GMT%2B8"/>
<property name="username" value="root"/>
<property name="password" value="****"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/StudentMapper.xml"/>
</mappers>
</configuration>
测试代码：
package com.tsrain.demo;
import com.tsrain.domain.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.*;
import org.junit.*;
import java.io.*;
import java.util.List;
import java.util.UUID;
public class TestMybatis {
SqlSessionFactory sqlSessionFactory;
static String prefix = "com.tsrain.domain.StudentMapper.";
@Before
public void initFactory() throws IOException {
String resource = "configuration.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testListAll() {
SqlSession session = sqlSessionFactory.openSession();
List<Student> students = session.selectList(prefix + "listAll");
System.out.println(students.toString());
}
@Test
public void testQueryOne() {
SqlSession session = sqlSessionFactory.openSession();
Student student = session.selectOne(prefix + "getOne", 1);
System.out.println(student.toString());
}
//事务需要程序员处理
@Test
public void testInsertOne() {
Student u = new Student();
u.setId(3);
u.setName("HaHa");
u.setAge(18);
SqlSession session = sqlSessionFactory.openSession();
int count = session.insert(prefix + "insertOne", u);
session.commit();
System.out.println(count);
}
@Test
public void testUpdateOne() {
SqlSession session = sqlSessionFactory.openSession();
Student u = new Student();
u.setId(3);
u.setName("newup");
u.setAge(100);
int count = session.update(prefix + "updateOne", u);
session.commit();
System.out.println(count);
}
@Test
public void testDeleteOne() {
SqlSession session = sqlSessionFactory.openSession();
Student u = new Student();
u.setId(3);
int count = session.delete(prefix + "deleteOne", u);
session.commit();
System.out.println(count);
}
}
开始时由于mysql数据库无法连接成功，搜集了一份测试连接的代码,此代码与mybatis无关，是最基本的连接。
package com.tsrain.demo;
import java.sql.*;
public class ConMysqlTest {
public static void main(String[] args) {
//声明Connection对象
Connection con;
//驱动程序名
String driver = "com.mysql.jdbc.Driver";
//URL指向要访问的数据库名mydata
String url = "jdbc:mysql://localhost:3306/person?serverTimezone=GMT%2B8";
//MySQL配置时的用户名
String user = "root";
//MySQL配置时的密码
String password = "***";
//遍历查询结果集
try {
//加载驱动程序
Class.forName(driver);
//1.getConnection()方法，连接MySQL数据库！！
con = DriverManager.getConnection(url, user, password);
if (!con.isClosed())
System.out.println("Succeeded connecting to the Database!");
//2.创建statement类对象，用来执行SQL语句！！
Statement statement = con.createStatement();
//要执行的SQL语句
String sql = "select * from student";
//3.ResultSet类，用来存放获取的结果集！！
ResultSet rs = statement.executeQuery(sql);
System.out.println("-----------------");
System.out.println("执行结果如下所示:");
System.out.println("-----------------");
System.out.println(" 学号" + "\t" + " 姓名");
System.out.println("-----------------");
String name = null;
int id;
while (rs.next()) {
//获取stuname这列数据
name = rs.getString("name");
//获取stuid这列数据
id = rs.getInt("id");
//首先使用ISO-8859-1字符集将name解码为字节序列并将结果存储新的字节数组中。
//然后使用GB2312字符集解码指定的字节数组。
name = new String(name.getBytes("utf-8"), "utf-8");
//输出结果
System.out.println(id + "\t" + name);
}
rs.close();
con.close();
} catch (ClassNotFoundException e) {
//数据库驱动类异常处理
System.out.println("Sorry,can`t find the Driver!");
e.printStackTrace();
} catch (SQLException e) {
//数据库连接失败异常处理
System.out.println("Sorry,SQL exception!");
e.printStackTrace();
} catch (Exception e) {
// TODO: handle exception
System.out.println("Sorry,Other exception!");
e.printStackTrace();
} finally {
System.out.println("数据库操作结束。");
}
}
}
