4Mybaits入门
4.1需求列表
根据用户ID查询用户信息
根据用户名查找用户列表
添加用户
修改用户
删除用户
4.2工程搭建
1.导入依赖jar包,在课前资源中有
2.配置SqlMapConfig.xml,可参考课前资料
3.配置log4j.properties,可参考课前资料
4.在课前资料复制pojo到工程目录下
5.配置sql查询的映射文件,可参考课前资料
6.加载映射文件
4.3完成需求
4.3.1需求完成步骤
1.编写sql语句
2.配置user映射文件
3.编写测试程序
4.3.2根据用户ID查询用户信息
4.3.2.1 映射文件与sql
<select id="getUserById" parameterType="int" resultType="com.itheima.mybatis.pojo.User" >
SELECT * FROM USER WHERE id = #{id1}
</select>
4.3.2.2 MyBatis访问数据库代码
@Test
public void testGetUserByid() throws IOException {
// 创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sfb = new SqlSessionFactoryBuilder();
// 查找配置文件创建输入流
InputStream inputStream = Resources.getResourceAsStream(“SqlMapConfig.xml”);
// 加载配置文件,创建SqlSessionFactory对象
SqlSessionFactory sqlSessionFactory = sfb.build(inputStream);
// 创建SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 执行查询,参数一:要查询的statementId ,参数二:sql语句入参
User user = sqlSession.selectOne(“user.getUserById”, 1);
// 输出查询结果
System.out.println(user);
// 释放资源
sqlSession.close();
}
4.3.3抽取SqlSessionFactoryUtils工具类,共享SqlSessionFactory创建过程
/**
- SqlSessionFactory工具类
- @author Steven
*/
public class SqlSessionFactoryUtils {
/**
* 单例SqlSessionFactory
*/
private static SqlSessionFactory sqlSessionFactory;
static {
// 创建SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sfb = new SqlSessionFactoryBuilder();
try {
// 查找配置文件创建输入流
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
// 加载配置文件,创建SqlSessionFactory对象
sqlSessionFactory = sfb.build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取单例SqlSessionFactory
* @return
*/
public static SqlSessionFactory getSqlSessionFactory() {
return sqlSessionFactory;
}
}
4.3.4根据用户名查找用户列表
4.3.4.1映射文件与sql
<select id="getUserByName" parameterType="string" resultType="com.itheima.mybatis.pojo.User">
<!-- SELECT * FROM USER WHERE username LIKE #{name} -->
SELECT * FROM USER WHERE username LIKE '%${value}%'
</select>
4.3.4.2MyBatis访问数据库代码
@Test
public void getUserByName() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
//List users = sqlSession.selectList(“user.getUserByName”, “%张%”);
List users = sqlSession.selectList(“user.getUserByName”, “张”);
for (User user : users) {
System.out.println(user);
}
// 释放资源
sqlSession.close();
}
4.3.5添加用户
4.3.5.1映射文件与sql
INSERT INTO USER
(username
,
birthday
,
sex
,
address
)
VALUES (#{username},
#{birthday},
#{sex},
#{address});
4.3.5.2MyBatis访问数据库代码
@Test
public void testInsertUser() throws IOException {
// 创建SqlSession对象
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
User user = new User();
user.setUsername("张飞");
user.setAddress("深圳市黑马");
user.setBirthday(new Date());
user.setSex("1");
// 执行插入
sqlSession.insert("user.insertUser", user);
// 提交事务
sqlSession.commit();
// 释放资源
sqlSession.close();
}
4.3.5.3Mysql自增返回
<insert id="insertUserKey" parameterType="com.itheima.mybatis.pojo.User" useGeneratedKeys="true" keyProperty="id">
<!-- selectKey:用于配置主键返回
keyProperty:要绑定的pojo属性
resultType:属性数据类型
order:指定什么时候执行,AFTER之后
-->
<!-- <selectKey keyProperty="id" resultType="int" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey> -->
INSERT INTO USER
(username
,
birthday
,
sex
,
address
,
uuid2
)
VALUES (#{username},
#{birthday},
#{sex},
#{address},
#{uuid2});
4.3.5.4Mysql的uuid返回主键
注:在使用uuid之前数据库user表要先加上uuid2字段、user的pojo也要加上相应属性
<insert id="insertUserUUID" parameterType="com.itheima.mybatis.pojo.User">
<!-- selectKey:用于配置主键返回
keyProperty:要绑定的pojo属性
resultType:属性数据类型
order:指定什么时候执行,BEFORE之前
-->
<selectKey keyProperty="uuid2" resultType="string" order="BEFORE">
SELECT UUID()
</selectKey>
INSERT INTO USER
(`username`,
`birthday`,
`sex`,
`address`,
`uuid2`)
VALUES (#{username},
#{birthday},
#{sex},
#{address},
#{uuid2});
</insert>
4.3.6修改用户
UPDATE USER SET username = #{username} WHERE id = #{id}
4.3.7删除用户
DELETE FROM user
WHERE id
= #{id1}
4.4Mybatis入门小结与Mybatis架构图