Mybatis
1.创建一个maven工程
2.导入相对应的依赖
mybatis的依赖:
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.3</version>
</dependency>
数据库链接的依赖:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>
3.再resources下创建一个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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/shixun?serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="111111"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="StudentMapper.xml"/>
</mappers>
</configuration>
在配置类中最主要的两个属性:
driver: 表示驱动名称(不同版本的数据库不一样)
5.0之前的数据库版本不用加cj
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
5.0以后的版本需要加cj
<property name="driver" value="com.mysql.jdbc.Driver"/>
url:数据的的链接地址(同上不同版本数据库的链接不一样)
属性的含义:
jdbc:表示链接驱动
mysql:表示数据库
localhost:表示地址
3306:数据库的端口名称
shixun:数据库的表名(根据数据库的表不一样自己指定)
5.0之前的数据库版本不需要加时区
<property name="url" value="jdbc:mysql://localhost:3306/shixun/>
5.0以后的版本需要加需要增加时区
<property name="url" value="jdbc:mysql://localhost:3306/shixun?serverTimezone=UTC"/>
为了以后的修改,我们把一些变量分离开来,因此我们需要创建一个properties文件
在resources目录下创建一个文件名为:db.properties;
db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/shixun?serverTimezone=UTC
db.username=root
db.password=111111
在mybatis配置文件中将这个配置文件引入:
<properties resource="db.properties"/>
加入以后对配置文件进行一个更改,此处的value里面的属性要和配置文件的属性名对应上
<dataSource type="POOLED">
<property name="driver" value="${db.driver}"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.username}"/>
<property name="password" value="${db.password}"/>
</dataSource>
4.去测试数据库是否链接成功
在主方法里面去的main函数进行测试
String resource = "mybatis.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory =
new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
System.out.println(sqlSession);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V3s9O850-1688179746272)(C:\Users\27219\AppData\Roaming\Typora\typora-user-images\image-20230630161630031.png)]
此时表示数据库已经连接成功
5.数据库建表
1.宿舍表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS `room`;
CREATE TABLE `room` (
`r_id` int NOT NULL,
`r_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`r_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `room` VALUES (1, '尖刀');
INSERT INTO `room` VALUES (2, '实验');
SET FOREIGN_KEY_CHECKS = 1;
2.学生表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int NOT NULL,
`s_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`age` int NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
`r_id` int NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `student` VALUES (1, 'admin', 15, '2023-06-06', 1);
INSERT INTO `student` VALUES (2, 'test', 65, '2023-06-07', 1);
INSERT INTO `student` VALUES (3, 'boos', 15, '3916-11-25', 2);
SET FOREIGN_KEY_CHECKS = 1;
6.mybatis和数据库进行交互
创建两个实体类分别对应两张表
1.学生表
package com.pojo;
import java.sql.Date;
public class Student {
private Integer id;
private String name;
private Integer age;
private Date birth;
private Integer rid;
private Room room;
public Student(Integer id, String name, Integer age, Date birth) {
this.id = id;
this.name = name;
this.age = age;
this.birth = birth;
}
public Student(Integer id, String s_name, Integer age, Date birth, Integer rid) {
this.id = id;
this.name = s_name;
this.age = age;
this.birth = birth;
this.rid = rid;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", s_name='" + name + '\'' +
", age=" + age +
", birth=" + birth +
", room=" + room +
'}';
}
public Student() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
public Room getRoom() {
return room;
}
public void setRoom(Room room) {
this.room = room;
}
public Integer getRid() {
return rid;
}
public void setRid(Integer rid) {
this.rid = rid;
}
}
2.宿舍表
package com.pojo;
public class Room {
private Integer id;
private String name;
public Room(Integer id, String name) {
this.id = id;
this.name = name;
}
public Room() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Room{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
创建Mapper文件
在rescourse下创建文件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.mapper.StudentMapper">
</mapper>
在mapper文件中编写对应的增删改查的语句
<select id="getAll" resultType="com.pojo.Student">
select * from student;
</select>
id:理解为一个方法
resultType:相对应的实体类
编写main函数:
List<Student> studentList = sqlSession.selectList("getAll");
Iterator<Student> iterator = studentList.iterator();
while (iterator.hasNext()){
System.out.println(iterator.next());
}
此时由于数据库的字段名和实体类的属性名不一致导致不能进行映射
<resultMap id="StudentEntityMap" type="com.pojo.Student">
<id column="id" property="id"></id>
<result column="s_name" property="name"></result>
<result column="age" property="age"></result>
<result column="birth" property="birth"></result>
<result column="r_id" property="rid"></result>
</resultMap>
***id:***表示唯一标识,使用是直接进行使用
type:表示当前数据库的字段名和实体类的属性名进行映射
表示的是这张表所对应的主键
column:数据库的字段名
property:实体类的属性名
将对应的结果集进行映射
<select id="getAll" resultType="com.pojo.Student" resultMap="StudentEntityMap">
select * from student;
</select>
根据id进行查询:
List<Student> student = sqlSession.selectList("getById", 3);
Iterator<Student> iterator1 = student.iterator();
while (iterator1.hasNext()) {
System.out.println(iterator1.next());
}
<select id="getById" resultType="com.pojo.Student" resultMap="StudentEntityMap">
select * from student where id = #{id};
</select>
插入数据:
int insert = sqlSession.insert("ins",new Student(3,"text",15,new Date(2016,10,25),2));
sqlSession.commit();
sqlSession.close();
System.out.println(insert);
<insert id="ins" parameterType="com.pojo.Student">
insert into student values(#{id},#{name},#{age},#{birth},#{rid});
</insert>
删除数据:
int del = sqlSession.delete("del", 3);
sqlSession.commit();
sqlSession.close();
System.out.println(del);
<delete id="del">
delete from student where id = #{id};
</delete>
多表联查:
List<Student> student = sqlSession.selectList("getByIdAndRoom", 3);
Iterator<Student> iterator1 = student.iterator();
while (iterator.hasNext()) {
System.out.println(iterator1.next());
}
<resultMap id="SR" type="com.pojo.Student">
<id column="id" property="id"></id>
<result column="s_name" property="name"></result>
<result column="age" property="age"></result>
<result column="birth" property="birth"></result>
<result column="r_id" property="rid"></result>
<result column="r_id" property="room.id"></result>
<result column="r_name" property="room.name"></result>
</resultMap>
<select id="getByIdAndRoom" resultType="com.pojo.Student" resultMap="SR">
select s.id,s.age,s.s_name,s.birth,r.r_id,r.r_name from student s left join room r
on s.r_id = r.r_id where s.id = #{id};
</select>
```## Mybatis
####
#### 1.创建一个maven工程
#### 2.导入相对应的依赖
mybatis的依赖:
```java
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.3</version>
</dependency>
数据库链接的依赖:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>
3.再resources下创建一个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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/shixun?serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="111111"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="StudentMapper.xml"/>
</mappers>
</configuration>
在配置类中最主要的两个属性:
driver: 表示驱动名称(不同版本的数据库不一样)
5.0之前的数据库版本不用加cj
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
5.0以后的版本需要加cj
<property name="driver" value="com.mysql.jdbc.Driver"/>
url:数据的的链接地址(同上不同版本数据库的链接不一样)
属性的含义:
jdbc:表示链接驱动
mysql:表示数据库
localhost:表示地址
3306:数据库的端口名称
shixun:数据库的表名(根据数据库的表不一样自己指定)
5.0之前的数据库版本不需要加时区
<property name="url" value="jdbc:mysql://localhost:3306/shixun/>
5.0以后的版本需要加需要增加时区
<property name="url" value="jdbc:mysql://localhost:3306/shixun?serverTimezone=UTC"/>
为了以后的修改,我们把一些变量分离开来,因此我们需要创建一个properties文件
在resources目录下创建一个文件名为:db.properties;
db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/shixun?serverTimezone=UTC
db.username=root
db.password=111111
在mybatis配置文件中将这个配置文件引入:
<properties resource="db.properties"/>
加入以后对配置文件进行一个更改,此处的value里面的属性要和配置文件的属性名对应上
<dataSource type="POOLED">
<property name="driver" value="${db.driver}"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.username}"/>
<property name="password" value="${db.password}"/>
</dataSource>
4.去测试数据库是否链接成功
在主方法里面去的main函数进行测试
String resource = "mybatis.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory =
new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
System.out.println(sqlSession);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HbeaSM1Y-1688179747381)(C:\Users\27219\AppData\Roaming\Typora\typora-user-images\image-20230630161630031.png)]
此时表示数据库已经连接成功
5.数据库建表
1.宿舍表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS `room`;
CREATE TABLE `room` (
`r_id` int NOT NULL,
`r_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`r_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `room` VALUES (1, '尖刀');
INSERT INTO `room` VALUES (2, '实验');
SET FOREIGN_KEY_CHECKS = 1;
2.学生表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int NOT NULL,
`s_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`age` int NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
`r_id` int NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `student` VALUES (1, 'admin', 15, '2023-06-06', 1);
INSERT INTO `student` VALUES (2, 'test', 65, '2023-06-07', 1);
INSERT INTO `student` VALUES (3, 'boos', 15, '3916-11-25', 2);
SET FOREIGN_KEY_CHECKS = 1;
6.mybatis和数据库进行交互
创建两个实体类分别对应两张表
1.学生表
package com.pojo;
import java.sql.Date;
public class Student {
private Integer id;
private String name;
private Integer age;
private Date birth;
private Integer rid;
private Room room;
public Student(Integer id, String name, Integer age, Date birth) {
this.id = id;
this.name = name;
this.age = age;
this.birth = birth;
}
public Student(Integer id, String s_name, Integer age, Date birth, Integer rid) {
this.id = id;
this.name = s_name;
this.age = age;
this.birth = birth;
this.rid = rid;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", s_name='" + name + '\'' +
", age=" + age +
", birth=" + birth +
", room=" + room +
'}';
}
public Student() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
public Room getRoom() {
return room;
}
public void setRoom(Room room) {
this.room = room;
}
public Integer getRid() {
return rid;
}
public void setRid(Integer rid) {
this.rid = rid;
}
}
2.宿舍表
package com.pojo;
public class Room {
private Integer id;
private String name;
public Room(Integer id, String name) {
this.id = id;
this.name = name;
}
public Room() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Room{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
创建Mapper文件
在rescourse下创建文件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.mapper.StudentMapper">
</mapper>
在mapper文件中编写对应的增删改查的语句
<select id="getAll" resultType="com.pojo.Student">
select * from student;
</select>
id:理解为一个方法
resultType:相对应的实体类
编写main函数:
List<Student> studentList = sqlSession.selectList("getAll");
Iterator<Student> iterator = studentList.iterator();
while (iterator.hasNext()){
System.out.println(iterator.next());
}
此时由于数据库的字段名和实体类的属性名不一致导致不能进行映射
<resultMap id="StudentEntityMap" type="com.pojo.Student">
<id column="id" property="id"></id>
<result column="s_name" property="name"></result>
<result column="age" property="age"></result>
<result column="birth" property="birth"></result>
<result column="r_id" property="rid"></result>
</resultMap>
***id:***表示唯一标识,使用是直接进行使用
type:表示当前数据库的字段名和实体类的属性名进行映射
表示的是这张表所对应的主键
column:数据库的字段名
property:实体类的属性名
将对应的结果集进行映射
<select id="getAll" resultType="com.pojo.Student" resultMap="StudentEntityMap">
select * from student;
</select>
根据id进行查询:
List<Student> student = sqlSession.selectList("getById", 3);
Iterator<Student> iterator1 = student.iterator();
while (iterator1.hasNext()) {
System.out.println(iterator1.next());
}
<select id="getById" resultType="com.pojo.Student" resultMap="StudentEntityMap">
select * from student where id = #{id};
</select>
插入数据:
int insert = sqlSession.insert("ins",new Student(3,"text",15,new Date(2016,10,25),2));
sqlSession.commit();
sqlSession.close();
System.out.println(insert);
<insert id="ins" parameterType="com.pojo.Student">
insert into student values(#{id},#{name},#{age},#{birth},#{rid});
</insert>
删除数据:
int del = sqlSession.delete("del", 3);
sqlSession.commit();
sqlSession.close();
System.out.println(del);
<delete id="del">
delete from student where id = #{id};
</delete>
多表联查:
List<Student> student = sqlSession.selectList("getByIdAndRoom", 3);
Iterator<Student> iterator1 = student.iterator();
while (iterator.hasNext()) {
System.out.println(iterator1.next());
}
<resultMap id="SR" type="com.pojo.Student">
<id column="id" property="id"></id>
<result column="s_name" property="name"></result>
<result column="age" property="age"></result>
<result column="birth" property="birth"></result>
<result column="r_id" property="rid"></result>
<result column="r_id" property="room.id"></result>
<result column="r_name" property="room.name"></result>
</resultMap>
<select id="getByIdAndRoom" resultType="com.pojo.Student" resultMap="SR">
select s.id,s.age,s.s_name,s.birth,r.r_id,r.r_name from student s left join room r
on s.r_id = r.r_id where s.id = #{id};
</select>