1、介绍
（1）mybatis的优势：
是一款持久层框架，支持定制化SQL、存储过程以及高级映射，避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。可以使用简单的XML或注解来配置和映射原生类型、接口和java的POJO为数据库中的记录
MyBatis是apache的一个开源项目，对jdbc操作数据库的过程进行了封装，开发者只需关注sql本身，而不需要去处理注册驱动、创建连接、创建执行者对象、手动设置参数等复杂的代码。可以通过xml或注解的方式将要执行的各种statement配置起来，最终由mybatis框架执行sql并将结果映射成java对象返回。
（2）传统的jdbc的不足：
public class JdbcDemo {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 通过驱动管理类获取数据库链接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu_mangement", "root", "root");
// 定义sql语句 ?表示占位符
String sql = "select * from student where studentno = ?";
// 获取预处理statement
preparedStatement = connection.prepareStatement(sql);
// 设置参数，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值
preparedStatement.setString(1, "201811");
// 向数据库发出sql执行查询，查询出结果集
resultSet = preparedStatement.executeQuery();
// 遍历查询结果集
while (resultSet.next()) {
System.out.println(resultSet.getString("studentno") + " " + resultSet.getString("sname"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
201811 zhai
数据库连接创建、释放频繁造成系统资源浪费，从而影响系统性能，使用数据库连接池可解决此问题。
sql语句在代码中硬编码，造成代码不易维护，实际应用中sql变化的可能较大，sql变动需要改变java代码。
使用preparedStatement向占有位符号传参数存在硬编码，因为sql语句的where条件不一定，可能多也可能少，修改sql还要修改代码，不易维护。
对结果集解析存在硬编码（查询列名），sql变化导致解析代码变化，系统不易维护，如果能将数据库记录封装成pojo对象解析比较方便。
（3）mybatis框架：
2、mybatis入门程序
（1）导包：
核心包、依赖包、数据库驱动包
（2）书写pojo：
public class Student implements Serializable {
private static final long serialVersionUID = 1L;
private String studentno;
private String sname;
private String sex;
private String birthday;
private String classno;
private String point;
private String phone;
private String email;
public String getStudentno() {
return studentno;
}
public void setStudentno(String studentno) {
this.studentno = studentno;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "Student [studentno=" + studentno + ", sname=" + sname + ", sex="
+ sex + ", birthday=" + birthday + ", classno=" + classno
+ ", point=" + point + ", phone=" + phone + ", email=" + email
+ "]";
}
public String getClassno() {
return classno;
}
public void setClassno(String classno) {
this.classno = classno;
}
public String getPoint() {
return point;
}
public void setPoint(String point) {
this.point = point;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
（3）核心配置文件：
<?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>
<!-- 和spring整合后 environments配置将废除 -->
<environments default="development">
<environment id="development">
<!-- 使用jdbc事务管理 -->
<transactionManager type="JDBC" />
<!-- 数据库连接池 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/student" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
</configuration>
（4）日志文件（在控制台打印信息）：
# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
（5）书写Student.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="test">
<select id="findStudentById" parameterType="Integer" resultType="pers.zhb.pojo.Student">
select * from student where studentno = #{v}
</select>
</mapper>
在核心配置文件中引入该文件：
<mappers>
<mapper resource="sqlmap/student.xml"/>
</mappers>
（6）书写测试类：
public class Test {
public void testMybatis() throws Exception {
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行Sql语句
Student student = sqlSession.selectOne("test.findStudentById", 201811);
System.out.println(student);
}
public static void main(String args []){
Test test=new Test();
try {
test.testMybatis();
} catch (Exception e) {
e.printStackTrace();
}
}
}
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 2094411587.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7cd62f43]
DEBUG [main] - ==> Preparing: select * from student where studentno = ?
DEBUG [main] - ==> Parameters: 201811(Integer)
DEBUG [main] - <== Total: 1
Student [studentno=201811, sname=zhai, sex=男, birthday=1998-11-11, classno=tx171, point=890, phone=1234567890, email=null]
（7）模糊查询：
<select id="findStudentBysname" parameterType="String" resultType="pers.zhb.pojo.Student">
select * from student where sname like '%${value}%'
</select>
public void testfindUserBysname() throws Exception {
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行Sql语句
List<Student> students = sqlSession.selectList("test.findStudentBysname", "zh");
for (Student student : students) {
System.out.println(student);
}
}
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 1555690610.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@5cb9f472]
DEBUG [main] - ==> Preparing: select * from student where sname like '%zh%'
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 4
Student [studentno=201811, sname=zhai, sex=男, birthday=1998-11-11, classno=tx171, point=890, phone=1234567890, email=null]
Student [studentno=201812, sname=zhai2, sex=男, birthday=1998-11-11, classno=tx171, point=893, phone=19837372533, email=null]
Student [studentno=201813, sname=zhai3, sex=男, birthday=1998-11-11, classno=tx171, point=892, phone=19837372534, email=null]
Student [studentno=201814, sname=zhai3, sex=男, birthday=1998-11-11, classno=tx101, point=892, phone=19837372534, email=null]
上面的方式不能防止sql注入，可以更改配置文件，如下：
<select id="findStudentBysname" parameterType="String" resultType="pers.zhb.pojo.Student">
select * from student where sname like "%"#{value}"%"
</select>
（8）添加：
<insert id="insertStudent" parameterType="pers.zhb.pojo.Student">
insert into student (studentno,sname,sex,phone)
values (#{studentno},#{sname},#{sex},#{phone})
</insert>
public void testInsertStudent() throws Exception {
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行Sql语句
Student student=new Student();
student.setStudentno("2020319");
student.setSname("ma");
student.setPhone("18733356671");
student.setSex("男");
int i = sqlSession.insert("test.insertStudent", student);
sqlSession.commit();
}
最后需要提交。
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 152134087.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@91161c7]
DEBUG [main] - ==> Preparing: insert into student (studentno,sname,sex,phone) values (?,?,?,?)
DEBUG [main] - ==> Parameters: 2020319(String), ma(String), 男(String), 18733356671(String)
DEBUG [main] - <== Updates: 1
DEBUG [main] - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@91161c7]
（9）更新：
<update id="updateStudentById" parameterType="pers.zhb.pojo.Student">
update student
set sname = #{sname}
where studentno = #{studentno}
</update>
public void testUpdateUserById() throws Exception {
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
Student student=new Student();
student.setStudentno("2020319");
student.setSname("ma2011");
int i = sqlSession.update("test.updateStudentById", student);
sqlSession.commit();
}
10、删除
<delete id="deleteStudentById" parameterType="Integer">
delete from student
where studentno = #{value}
</delete>
public void testDelete() throws Exception {
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
sqlSession.delete("test.deleteStudentById", 2020319);
sqlSession.commit();
}
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 152134087.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@91161c7]
DEBUG [main] - ==> Preparing: delete from student where studentno = ?
DEBUG [main] - ==> Parameters: 2020319(Integer)
DEBUG [main] - <== Updates: 1
DEBUG [main] - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@91161c7]