1.数据库表
(1) 创建 mysql 数据库表
CREATE TABLE `t_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_name` varchar(50) DEFAULT NULL,
`pass_word` varbinary(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
2.相关操作
1.查询一条记录
//根据id获取一条学生记录
User getUserById(Integer id);
<select id="getUserById" resultType="com.limi.entity.User">
select * from t_user where id = #{id}
</select>
@Test
public void test1() throws IOException {
//1.mybatis 主配置文件
String config = "mybatis-config.xml";
//2.读取配置文件
InputStream in = Resources.getResourceAsStream(config);
//3.创建 SqlSessionFactory 对象,目的是获取 SqlSession
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//4.获取 SqlSession,SqlSession 能执行 sql 语句
SqlSession session = factory.openSession();
//5.执行数据库操作
User user = session.selectOne("com.limi.dao.UserDao.getUserById", 1);
//6.输出查询结果
System.out.println(user);
//7.关闭 SqlSession,释放资源
session.close();
}
2.查询多条记录
//获取所有学生记录
List<User> getAllUser();
<select id="getAllUser" resultType="com.limi.entity.User">
<!--要执行的 sql 语句-->
select * from t_user
</select>
@Test
public void test1() throws IOException {
//1.mybatis 主配置文件
String config = "mybatis-config.xml";
//2.读取配置文件
InputStream in = Resources.getResourceAsStream(config);
//3.创建 SqlSessionFactory 对象,目的是获取 SqlSession
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//4.获取 SqlSession,SqlSession 能执行 sql 语句
SqlSession session = factory.openSession();
//5.执行数据库操作
List<User> userList = session.selectList("com.limi.dao.UserDao.getAllUser");
//6.输出查询结果
userList.forEach(user -> System.out.println(user));
//7.关闭 SqlSession,释放资源
session.close();
}
3.增加一条记录
//插入一条学生记录
int insertUser(User user);
<!--如果你的数据库支持自动生成主键的字段(比如 MySQL 和 SQL Server),
那么你可以设置 useGeneratedKeys=”true”,然后再把 keyProperty 设置为目标属性就 OK 了。-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into t_user(user_name, pass_word)
values(#{userName},#{passWord})
</insert>
@Test
public void test1() throws IOException {
//1.mybatis 主配置文件
String config = "mybatis-config.xml";
//2.读取配置文件
InputStream in = Resources.getResourceAsStream(config);
//3.创建 SqlSessionFactory 对象,目的是获取 SqlSession
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//4.获取 SqlSession,SqlSession 能执行 sql 语句
SqlSession session = factory.openSession();
//5.执行数据库操作
int rows = session.insert("com.limi.dao.UserDao.insertUser",new User(0, "newUser1", "123456"));
//6.输出查询结果
System.out.println("增加记录行数: "+ rows);
//7.提交事务
session.commit();
//8.关闭 SqlSession,释放资源
session.close();
}
4.修改一条记录
//根据id更新一条学生记录
int updateUserById(User user);
<update id="updateUserById">
update t_user
set user_name = #{userName}, pass_word = #{passWord}
where id = #{id}
</update>
@Test
public void test1() throws IOException {
//1.mybatis 主配置文件
String config = "mybatis-config.xml";
//2.读取配置文件
InputStream in = Resources.getResourceAsStream(config);
//3.创建 SqlSessionFactory 对象,目的是获取 SqlSession
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//4.获取 SqlSession,SqlSession 能执行 sql 语句
SqlSession session = factory.openSession();
//5.执行数据库操作
int rows = session.insert("com.limi.dao.UserDao.updateUserById",new User(4, "eason_update", "update"));
//6.输出查询结果
System.out.println("修改记录行数: "+ rows);
//7.提交事务
session.commit();
//8.关闭 SqlSession,释放资源
session.close();
}
5.删除一条记录
//根据id删除一条学生记录
int deleteUserById(Integer id);
<delete id="deleteUserById">
delete from t_user
where id = #{id}
</delete>
@Test
public void test1() throws IOException {
//1.mybatis 主配置文件
String config = "mybatis-config.xml";
//2.读取配置文件
InputStream in = Resources.getResourceAsStream(config);
//3.创建 SqlSessionFactory 对象,目的是获取 SqlSession
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//4.获取 SqlSession,SqlSession 能执行 sql 语句
SqlSession session = factory.openSession();
//5.执行数据库操作
int rows = session.delete("com.limi.dao.UserDao.deleteUserById", 1);
//6.输出查询结果
System.out.println("删除记录行数: "+ rows);
//7.提交事务
session.commit();
//8.关闭 SqlSession,释放资源
session.close();
}
3.完整代码
项目结构
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/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.limi</groupId>
<artifactId>mybatisTest</artifactId>
<packaging>war</packaging>
<version>1.0-SNAPSHOT</version>
<name>quickstart</name>
<!--导入相关依赖-->
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.9</version>
</dependency>
</dependencies>
<!--加入 maven 插件-->
<build>
<resources>
<resource>
<directory>src/main/java</directory><!--所在的目录-->
<includes><!--包括目录下的.properties,.xml 文件都会扫描到-->
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
mybatis-config.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>
<!-- 配置日志功能, 一定要放在这, 放在下面会报错-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!--配置 mybatis 环境-->
<environments default="mysql">
<!--id:数据源的名称-->
<environment id="mysql">
<!--配置事务类型:使用 JDBC 事务(使用 Connection 的提交和回滚)-->
<transactionManager type="JDBC"/>
<!--数据源 dataSource:创建数据库 Connection 对象
type: POOLED 使用数据库的连接池
-->
<dataSource type="POOLED">
<!--连接数据库的四个要素-->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/db_springtest?useUnicode=true&characterEncoding=utf-8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--告诉 mybatis 要执行的 sql 语句的位置-->
<mapper resource="com/limi/dao/xml/UserDao.xml"/>
</mappers>
</configuration>
提示:以下是 < mappers >标签内容几种写法
User
package com.limi.entity;
public class User {
private Integer id;
private String userName;
private String passWord;
public User(Integer id, String userName, String passWord) {
this.id = id;
this.userName = userName;
this.passWord = passWord;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", passWord='" + passWord + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassWord() {
return passWord;
}
public void setPassWord(String passWord) {
this.passWord = passWord;
}
}
UserDao
package com.limi.dao;
import com.limi.entity.User;
import java.util.List;
public interface UserDao {
//根据id获取一条学生记录
User getUserById(Integer id);
//获取所有学生记录
List<User> getAllUser();
//插入一条学生记录
int insertUser(User user);
//根据id更新一条学生记录
int updateUserById(User user);
//根据id删除一条学生记录
int deleteUserById(Integer id);
}
UserDao.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">
<!--
namespace:必须有值,自定义的唯一字符串
推荐使用:dao 接口的全限定名称 -->
<mapper namespace="com.limi.dao.UserDao">
<select id="getUserById" resultType="com.limi.entity.User">
select * from t_user where id = #{id}
</select>
<select id="getAllUser" resultType="com.limi.entity.User">
<!--要执行的 sql 语句-->
select * from t_user
</select>
<!--如果你的数据库支持自动生成主键的字段(比如 MySQL 和 SQL Server),
那么你可以设置 useGeneratedKeys=”true”,然后再把 keyProperty 设置为目标属性就 OK 了。-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into t_user(user_name, pass_word)
values(#{userName},#{passWord})
</insert>
<update id="updateUserById">
update t_user
set user_name = #{userName}, pass_word = #{passWord}
where id = #{id}
</update>
<delete id="deleteUserById">
delete from t_user
where id = #{id}
</delete>
</mapper>
MyTest
package com.limi;
import com.limi.entity.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MyTest {
@Test
public void test1() throws IOException {
//1.mybatis 主配置文件
String config = "mybatis-config.xml";
//2.读取配置文件
InputStream in = Resources.getResourceAsStream(config);
//3.创建 SqlSessionFactory 对象,目的是获取 SqlSession
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//4.获取 SqlSession,SqlSession 能执行 sql 语句
SqlSession session = factory.openSession();
//5.执行数据库操作
int rows = session.delete("com.limi.dao.UserDao.deleteUserById", 1);
//6.输出查询结果
System.out.println("删除记录行数: "+ rows);
//7.提交事务
session.commit();
//8.关闭 SqlSession,释放资源
session.close();
}
}