一、IDEA环境下Mybatis和JDBC的对比
1、以JDBC为例
(1)新建查询
右键,选择“新建查询”
(2)新建user表
输入代码,点击运行
create table users(
id int primary key auto_increment,
name varchar(40),
password varchar(40),
email varchar(60),
birthday date
)character set utf8 collate utf8_general_ci;
(3)插入数据
insert into users(name,password,email,birthday) values('lzh','123456','lzh@qq.com','1999-10-10');
insert into users(name,password,email,birthday) values('xh','123456','xh@qq.com','2000-04-27');
insert into users(name,password,email,birthday) values('lm','123456','lm@qq.com','2001-02-13');
(4)创建IDEA项目
(5)新建包
(6)DatebaseLnk代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
class DatebaseLink {
static final String driverName="org.gjt.mm.mysql.Driver";
static final String dbUrl="jdbc:mysql://localhost:3306/test1";
static final String userName="root";
static final String password="123456";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName(driverName);
System.out.println("连接数据库...");
conn = DriverManager.getConnection(dbUrl,userName,password);
System.out.println(" 实例化Statement对象...");
stmt = (Statement) conn.createStatement();
String sql;
sql = "SELECT id, name, password, email FROM users";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String password = rs.getString("password");
String email = rs.getString("email");
System.out.print("ID: " + id);
System.out.print(", 姓名: " + name);
System.out.print(", 密码: " +password);
System.out.print(", 邮箱: " +email);
System.out.print("\n");
}
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(stmt!=null) stmt.close();
}catch(SQLException se2){
}
try{
if(conn!=null) conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
(7)引入JDBC的jar包
点击第一个选项 从相关目录下导入jar包
(8)运行结果
2、以Mybatis为例
(1)创建项目
用IDEA新建项目,new-peoject Java Version 对于JDK选择8,点击next 选择新建Sring Web项目 创建成功
(2)新建查询
create table student(
no int primary key auto_increment,
name varchar(40),
age int
)character set utf8 collate utf8_general_ci;
(3)新建student表
insert into student(no,name,age) values('1','小明','19');
insert into student(no,name,age) values('2','李华','21');
insert into student(no,name,age) values('3','小红','20');
insert into student(no,name,age) values('4','张三','19');
insert into student(no,name,age) values('5','李四','20');
结果:
(4)配置文件
application.properties
server.port=8080
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456
mybatis.mapper-locations=classpath:mapper
(5)代码实现
项目src-main-java-com下分别创建包:controller、entity、mapper、service,用来实现控制层、实体层、映射层、业务层
创建entity实体类Student
package com.example.mybatistest.entity;
public class Student {
private int no;
private String name;
private int age;
public int getNo() {
return no;
}
public void setNo(int no) {
this.no = no;
}
public void setName(String name) {
this.name = name;
}
public String getName() {
return name;
}
public void setAge(int age) {
this.age = age;
}
public int getAge() {
return age;
}
@Override
public String toString() {
return "Student{" +
"no=" + no +
", name='" + name + '\'' +
", age='" + age + '\'' +
'}';
}
}
创建Mapper映射操作StudentMapper类:
package com.example.mybatistest.mapper;
import com.example.mybatistest.entity.Student;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface StudentMapper {
public List<Student> findAllStudent();
List<Student> findStudentByno(int no);
}
创建Mapper映射对应的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.example.mybatistest.mapper.StudentMapper">
<resultMap id="result" type="com.example.mybatistest.entity.Student">
<result column="no" jdbcType="INTEGER" property="no" />
<result column="name" jdbcType="VARCHAR" property="name" />
<result column="age" jdbcType="INTEGER" property="age" />
</resultMap>
<select id="findAllStudent" resultType="com.example.mybatistest.entity.Student">
select * from student;
</select>
<select id="findStudentByno" resultType="com.example.mybatistest.entity.Student">
select * from student where no=#{no};
</select>
</mapper>
创建service业务StudentService类:
package com.example.mybatistest.service;
import com.example.mybatistest.entity.Student;
import com.example.mybatistest.mapper.StudentMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class StudentService {
@Autowired(required = false)
public StudentMapper studentMapper;
public List<Student> findAllStudent() {
return studentMapper.findAllStudent();
}
public List<Student> findStudentByno(int no) {
return studentMapper.findStudentByno(no);
}
}
创建 controller控制层UserController类:
(6)总体架构
(7)测试结果
运行: 打开浏览器,输入http://localhost:8080/Student/getAllStudent/ 输入http://localhost:8080/Student/getStudentByno/2
(8)完整功能
以上为spring boot 整合mybatis实现的Student读取,接下来是添加StudentMapper类的增加、更新和删除方法,配置StudentMapper.xml文件,添加StudentService和StudentController相关功能
(1)完整StudentMapper类
package com.example.mybatistest.mapper;
import com.example.mybatistest.entity.Student;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface StudentMapper {
public List<Student> findAllStudent();
public List<Student> findStudentByno(int no);
public List<Student> findStudentByname(String name);
public int insertStudent(Student student);
public int updateStudent(Student student);
public int deleteStudent(Student student);
}
(2)完整StudentService类
package com.example.mybatistest.service;
import com.example.mybatistest.entity.Student;
import com.example.mybatistest.mapper.StudentMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class StudentService {
@Autowired(required = false)
public StudentMapper studentMapper;
public List<Student> findAllStudent() {
return studentMapper.findAllStudent();
}
public List<Student> findStudentByno(int no) {
return studentMapper.findStudentByno(no);
}
public List<Student> findStudentByname(String name){
return studentMapper.findStudentByname(name);
}
public Student insertStudent(Student student){
studentMapper.insertStudent(student);
return student;
}
public int updateStudent(Student student){
return studentMapper.updateStudent(student);
}
public int deleteStudent(Student student){
return studentMapper.deleteStudent(student);
}
}
(3)完整StudentController类
package com.example.mybatistest.controller;
import com.example.mybatistest.entity.Student;
import com.example.mybatistest.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/Student")
public class StudentController {
@Autowired
private StudentService studentService;
@RequestMapping("/getAllStudent")
public List<Student> findAll(){
return studentService.findAllStudent();
}
@RequestMapping("/getStudentByno/{no}")
public List<Student> findUserByStudentId(@PathVariable int no){
return studentService.findStudentByno(no);
}
@RequestMapping("/getStudentByname/{name}")
public List<Student> findStudentByname(@PathVariable String name){
return studentService.findStudentByname(name);
}
@RequestMapping("/insertStudent")
public Student insertStudent(Student student){
return studentService.insertStudent(student);
}
@RequestMapping("/updateStudent")
public int updateStudent(Student student){
return studentService.updateStudent(student);
}
@RequestMapping("/deleteStudent")
public int deleteStudent(Student student){
return studentService.deleteStudent(student);
}
}
(4)完整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.example.mybatistest.mapper.StudentMapper">
<resultMap id="result" type="com.example.mybatistest.entity.Student">
<result column="no" jdbcType="INTEGER" property="no" />
<result column="name" jdbcType="VARCHAR" property="name" />
<result column="age" jdbcType="INTEGER" property="age" />
</resultMap>
<select id="findAllStudent" resultType="com.example.mybatistest.entity.Student">
select * from student;
</select>
<select id="findStudentByno" resultType="com.example.mybatistest.entity.Student">
select * from student where no=#{no};
</select>
<select id="findStudentByname" resultType="com.example.mybatistest.entity.Student">
select * from student where name=#{name};
</select>
<insert id="insertStudent" parameterType="com.example.mybatistest.entity.Student" keyProperty="no" useGeneratedKeys="true">
insert into student(name,age) values (#{name},#{age});
</insert>
<update id="updateStudent" parameterType="com.example.mybatistest.entity.Student">
update student set name=#{name},age=#{age} where no=#{no};
</update>
<delete id="deleteStudent" parameterType="com.example.mybatistest.entity.Student">
delete from where no=#{no};
</delete>
</mapper>
(9)运行测试
(i)按姓名查找,浏览器输入:http://localhost:8080/Student/getStudentByname/小红 (ii)修改一信息: http://localhost:8080/Student/updateStudent?no=5&name=小豪&age=18 查看结果http://localhost:8080/Student/getStudentByno/5 (iii)插入一条信息: http://localhost:8080/Student/insertStudent?name=小白&age=19
二、总结
MyBatis是一个支持普通SQL查询,存储过程和高级映射的优秀持久层框架,使用更加的便利。本次作业难度对于我来说感觉较大,特别是代码部分,中间为寻找解决办法花费了不少时间。
|