框架 ---Mybatis 入门增删该查 别名 配置文件标签说明等no 02.
搭建环境
SqlMapConfig.xml解释
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
DTD格式 标签出现顺序必须按照前后一定顺序编写否则报错
<environments default="development">
development是开发环境.如果是测试环境直接给id就可以到不同的数据库里面.
<mappers>
<mapper resource="mybatis/user.xml" />
</mappers>
用于加载映射文件
xxx.xml映射文件说明
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"D://mybatis-dtd/dtd/mybatis-3-mapper.dtd">
<mapper namespace="test">
</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="test">
</mapper>
因为dtd要指向外网 写成指向外网的dtd的话 会报类似:文档类型声明包含或指向的标记声明必须格式正确。的错误!所以下载dtd把路径写成绝对路径。
<mapper namespace="user">
</mapper>
namespace解释:命名空间,隔离sql
编写sql语句:
<mapper namespace="user">
<select id="getUserById" parameterType="int" resultType="com.st.mybatis.pojo.User">
SELECT
`id`,
`username`,
`birthday`,
`sex`,
`address`
FROM `user`
WHERE id=#{id1}
</select>
id: sql语句执行的唯一标识
parameterType: 入参的类型
resultType: 返回结果的数据类型
#{}:是一个占位符 相当于jdbc的?
mybatis第一个入门程序:
TestDemo.java
package com.st.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
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 com.st.mybatis.pojo.User;
public class TestDemo {
@Test
public void getUserById() throws IOException {
//创建一个sqlsessionfactorybyulder对象
SqlSessionFactoryBuilder ssfb=new SqlSessionFactoryBuilder();
//通过输入流去读取SqlMapConfig.xml的映射文件
InputStream inputStream=Resources.getResourceAsStream("SqlMapConfig.xml");
//创建sqlsessionfactory对象
SqlSessionFactory sqlsessionfactory=ssfb.build(inputStream);
//创建sqlsession对象
SqlSession sqlSession=sqlsessionfactory.openSession();
//执行查询sql语句
User user=sqlSession.selectOne("user.getUserById", 1);
//输出结果
System.out.println(user);
//关闭资源
sqlSession.close();
}
}
user.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"D://mybatis-dtd/dtd/mybatis-3-mapper.dtd">
<!--命名空间,用于隔离sql-->
<mapper namespace="user">
<!--
id: sql语句执行的唯一标识
parameterType: 入参的类型
resultType: 返回结果的数据类型
#{}:是一个占位符 相当于jdbc的?
-->
<select id="getUserById" parameterType="int" resultType="com.st.mybatis.pojo.User">
SELECT
`id`,
`username`,
`birthday`,
`sex`,
`address`
FROM `user`
WHERE id=#{id1}
</select>
</mapper>
SqlMapConfig.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>
<!-- 和spring整合后 environments配置将废除 -->
<environments default="development">
<environment id="development">
<!-- 使用jdbc事务管理 -->
<transactionManager type="JDBC" />
<!-- 数据库连接池 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useSSL=false&suseSSL=true" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<!--用于加载映射文件-->
<mappers>
<mapper resource="mybatis/user.xml" />
</mappers>
</configuration>
运行结果:
抽取公共类:
package com.st.mybatis.utils;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
/**
* SqlSessionFactory工具类
* @author
*
*/
public class SqlSessionFactoryUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
//创建一个sqlsessionfactorybyulder对象
SqlSessionFactoryBuilder ssfb=new SqlSessionFactoryBuilder();
//通过输入流去读取SqlMapConfig.xml的映射文件
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
//创建sqlsessionfactory对象
sqlSessionFactory=ssfb.build(inputStream);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static SqlSessionFactory getSqlSessionFactory() {
return sqlSessionFactory;
}
}
通用编写步骤:
- 编写sql语句
- 配置user映射文件
- 编写测试程序
几个测试案例:
- 根据id查询到某个人(已经完成)
- 根据用户名查找用户列表(模糊查询like)
- 添加用户
- 修改用户
- 删除用户
根据用户名查找用户列表:
配置映射:
<select id="getUserByUsername" parameterType="String" resultType="com.st.mybatis.pojo.User">
SELECT
`id`,
`username`,
`birthday`,
`sex`,
`address`
FROM `user`
WHERE username LIKE #{name}
</select>
编写测试类:
@Test
public void getUserByUsername() {
//获取SqlSession
SqlSessionFactory sqlsessionfactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlsession=sqlsessionfactory.openSession();
List<User> list=sqlsession.selectList("user.getUserByUsername", "%张%");
for (User user : list) {
System.out.println(user);
}
sqlsession.close();
}
结果:
这里可以改造一下用字符串拼接指令:’%${value}%’
where username like '%${value}%'
List<User> list=sqlsession.selectList("user.getUserByUsername", "张");
其结果一样.
添加用户:
映射sql语句:
<insert id="inserUser" parameterType="com.st.mybatis.pojo.User">
INSERT INTO `user` (
`username`,
`birthday`,
`sex`,
`address`
)
VALUES
(
#{username},
#{birthday},
#{sex},
#{address}
);
</insert>
编写测试类:
@Test
public void insertUser() {
//获取SqlSession
SqlSessionFactory sqlsessionfactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlsession=sqlsessionfactory.openSession();
User user = new User();
user.setUsername("张四");
user.setSex("1");
user.setBirthday(new Date());
user.setAddress("成都");
sqlsession.insert("user.inserUser", user);
//提交事务必须要写 默认不提交
sqlsession.commit();
sqlsession.close();
}
运行结果:
查找用户可以调用MySQL的函数: SELECT LAST_INSERT_ID();
<selectKey keyProperty="id" resultType="int" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
selectKey:主键返回
keyProperty:user的主键
result:主键的数据类型
order:指定selectKey在什么时候执行/afer之后。
测试类中插入张四2 并打印输出user.
这里还有简写:
<insert id="inserUser" parameterType="com.st.mybatis.pojo.User" useGeneratedKeys="true" keyProperty="id">
useGeneratedKeys:使用自增 与keyProperty配套使用.
keyProperty:user主键
其结果一样!
插入用户-uuid的使用:
改变表结构
pojo添加uuid1属性.
<insert id="inserUserUUID" parameterType="com.st.mybatis.pojo.User" useGeneratedKeys="true" keyProperty="id">
<selectKey keyProperty="uuid1" resultType="String" order="BEFORE">
SELECT UUID()
</selectKey>
INSERT INTO `user` (
`username`,
`birthday`,
`sex`,
`address`,
`uuid1`
)
VALUES
(
#{username},
#{birthday},
#{sex},
#{address},
#{uuid1}
);
</insert>
@Test
public void insertUserUuid() {
//获取SqlSession
SqlSessionFactory sqlsessionfactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlsession=sqlsessionfactory.openSession();
User user = new User();
user.setUsername("张四4");
user.setSex("1");
user.setBirthday(new Date());
user.setAddress("成都");
sqlsession.insert("user.inserUserUUID", user);
System.out.println(user);
//提交事务必须要写 默认不提交
sqlsession.commit();
sqlsession.close();
}
结果:
这里有几点说明:在配置了<selectKey keyProperty="uuid1" resultType="String" order="BEFORE">
默认useGeneratedKeys="true" keyProperty="id
不开启.这个就是有主键的情况下用userGeneratedKey 没有主键的情况下给uuid.
修改用户:
配置映射:
<update id="updateUserById" parameterType="com.st.mybatis.pojo.User">
UPDATE
`user`
SET
`username` = #{username},
`address` = #{address}
WHERE `id` = #{id};
</update>
测试类:
@Test
public void updateUserById() {
//获取SqlSession
SqlSessionFactory sqlsessionfactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlsession=sqlsessionfactory.openSession();
User user=new User();
user.setId(1);
user.setUsername("王六");
user.setAddress("成都");
sqlsession.update("user.updateUserById",user );
//提交事务必须要写 默认不提交
sqlsession.commit();
sqlsession.close();
}
删除用户:
配置映射:
<delete id="deleteUserById" parameterType="int" >
DELETE
FROM
`user`
WHERE `id` = #{id};
</delete>
编写测试类:
@Test
public void deleteUserById() {
//获取SqlSession
SqlSessionFactory sqlsessionfactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlsession=sqlsessionfactory.openSession();
sqlsession.delete("user.deleteUserById",1);
//提交事务必须要写 默认不提交
sqlsession.commit();
sqlsession.close();
}
结果:
Mybatis的Dao包装方式
原始DAO开发 方法:
编写Dao层:
package com.st.mybatis.dao;
import java.util.List;
import com.st.mybatis.pojo.User;
public interface UserDao {
//根据用户ID查询用户信息
User getUserById(Integer id);
//根据用户姓名查找用户列表
List<User> getUserByUsername(String userName);
//添加用户
void insertUser(User user);
}
编写DaoImpl:
package com.st.mybatis.dao.impl;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import com.st.mybatis.dao.UserDao;
import com.st.mybatis.pojo.User;
import com.st.mybatis.utils.SqlSessionFactoryUtils;
public class UserDaoImpl implements UserDao {
@Override
public User getUserById(Integer id) {
// TODO Auto-generated method stub
SqlSessionFactory sqlsessionfactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlsession=sqlsessionfactory.openSession();
User user=sqlsession.selectOne("user.getUserById", 10);
sqlsession.close();
return user;
}
@Override
public List<User> getUserByUsername(String userName) {
// TODO Auto-generated method stub
SqlSessionFactory sqlsessionfactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlsession=sqlsessionfactory.openSession();
List<User> list=sqlsession.selectList("user.getUserByUsername", userName);
return list;
}
@Override
public void insertUser(User user) {
// TODO Auto-generated method stub
SqlSessionFactory sqlsessionfactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlsession=sqlsessionfactory.openSession(true);
sqlsession.insert("user.inserUser", user);
sqlsession.close();
}
}
测试:
package com.st.mybatis.dao.test;
import static org.junit.Assert.*;
import java.util.Date;
import java.util.List;
import org.junit.Test;
import com.st.mybatis.dao.UserDao;
import com.st.mybatis.dao.impl.UserDaoImpl;
import com.st.mybatis.pojo.User;
public class UserDaoTest {
@Test
public void testGetUserById() {
UserDao dao=new UserDaoImpl();
User user=dao.getUserById(10);
System.out.println(user);
}
@Test
public void testGetUserByUsername() {
UserDao dao=new UserDaoImpl();
List<User> list=dao.getUserByUsername("张");
for (User user : list) {
System.out.println(user);
}
}
@Test
public void testInsertUser() {
UserDao dao=new UserDaoImpl();
User user=new User();
user.setAddress("成都");
user.setBirthday(new Date());
user.setSex("1");
user.setUsername("王二");
dao.insertUser(user);
System.out.println(user);
}
}
动态代理包装方式:(常用)
动态代理开发规则:
1.namespace必需是接口的全路径名
2.接口的方法名必须与sql id一致
3.接口的入参与parameterType类型一致
4.接口的返回值必须与resultType类型一致
动态代理包装方式 不需要接口实现不是没有 而是底层已经完成了:
建立映射:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"D://mybatis-dtd/dtd/mybatis-3-mapper.dtd">
<!--
动态代理开发规则:
1.namespace必需是接口的全路径名
2.接口的方法名必须与sql id一致
3.接口的入参与parameterType类型一致
4.接口的返回值必须与resultType类型一致
-->
<!--命名空间,用于隔离sql-->
<mapper namespace="com.st.mybatis.mapper.UserMapper">
<!--
id: sql语句执行的唯一标识
parameterType: 入参的类型
resultType: 返回结果的数据类型
#{}:是一个占位符 相当于jdbc的?
'%${value}%'字符串拼接指令
-->
<select id="getUserById" parameterType="int" resultType="com.st.mybatis.pojo.User">
SELECT
`id`,
`username`,
`birthday`,
`sex`,
`address`
FROM `user`
WHERE id=#{id}
</select>
<select id="getUserByUsername" parameterType="String" resultType="com.st.mybatis.pojo.User">
SELECT
`id`,
`username`,
`birthday`,
`sex`,
`address`
FROM `user`
<!--WHERE username LIKE #{name} -->
where username like '%${value}%'
</select>
<!--
selectKey:主键返回
keyProperty:user的主键
result:主键的数据类型
order:指定selectKey在什么时候执行/fater之后
-->
<!--
useGeneratedKeys:使用自增 与keyProperty配套使用.
keyProperty:user主键
-->
<insert id="inserUser" parameterType="com.st.mybatis.pojo.User" useGeneratedKeys="true" keyProperty="id">
<!--
<selectKey keyProperty="id" resultType="int" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
-->
INSERT INTO `user` (
`username`,
`birthday`,
`sex`,
`address`
)
VALUES
(
#{username},
#{birthday},
#{sex},
#{address}
);
</insert>
</mapper>
引入映射文件:
<mapper resource="mybatis/UserMapper.xml" />
编写UserMapper.java接口
package com.st.mybatis.mapper;
import java.util.List;
import com.st.mybatis.pojo.User;
public interface UserMapper {
//根据用户ID查询用户信息
User getUserById(Integer id);
//根据用户姓名查找用户列表
List<User> getUserByUsername(String userName);
//添加用户
void inserUser(User user);
}
测试类:
package com.st.mybatis.test;
import static org.junit.Assert.*;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.st.mybatis.mapper.UserMapper;
import com.st.mybatis.pojo.User;
import com.st.mybatis.utils.SqlSessionFactoryUtils;
public class UserMapperTest {
@Test
public void testGetUserById() {
SqlSession sqlSession=SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
User user=userMapper.getUserById(10);
System.out.println(user);
sqlSession.close();
}
@Test
public void testGetUserByUsername() {
fail("Not yet implemented");
}
@Test
public void testInsertUser() {
fail("Not yet implemented");
}
}
运行结果:
SqlMapConfig.xml配置属性:
配置jdbc.properties
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&suseSSL=true&serverTimezone=UTC
jdbc.username=root
jdbc.password=123456
<!--先加载内部标签,再加载外部文件,名称一致时,会替换内容-->
<properties resource="jdbc.properties">
<property name="jdbc.username" value="root1"></property>
<property name="jdbc.password" value="123456"></property>
</properties>
<!-- 和spring整合后 environments配置将废除 -->
<environments default="development">
<environment id="development">
<!-- 使用jdbc事务管理 -->
<transactionManager type="JDBC" />
<!-- 数据库连接池 -->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}" />
<property name="url"
value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
先加载内部标签,再加载外部文件,名称一致时,会替换内容。
typeAliases(类型别名)
mybatis支持的别名:
自定义别名:
自定义单个别名:
<typeAliases>
<!--单个别名定义-->
<typeAlias type="com.st.mybatis.pojo.User" alias="user"></typeAlias>
</typeAliases>
UserMapper.xml
推荐使用下面这种:包扫描器
<typeAliases>
<!--包扫描器 别名是类名称 不区分大小写-->
<package name="com.st.mybatis.pojo"></package>
</typeAliases>
映射文件加载:
<mapper class="com.st.mybatis.mapper.UserMapper" />
- 映射文件,class扫描器:
1.接口文件必须与映射文件同一目录下
2.接口文件和映射文件名称必须一致
<package name="com.st.mybatis.mapper"></package>
- 映射文件,包扫描器:(推荐方式)
1.接口文件必须与映射文件同一目录下
2.接口文件和映射文件名称必须一致