目录
四、Mybatis的CRUD操作
4.1 依赖管理
<?xml version="1.0" encoding="UTF-8"?>
<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/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.cpz</groupId>
<artifactId>mybatis_day01_crud</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.18</version>
</dependency>
<!-- 日志坐标 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
<!--测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
</dependency>
</dependencies>
</project>
4.2 User 实体类
package com.cpz.domain;
import java.io.Serializable;
import java.util.Date;
public class User implements Serializable {
private int id;// 主键ID
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", sex='" + sex + '\'' +
", birthday=" + birthday +
", address='" + address + '\'' +
'}';
}
}
4.3 UserDao接口(持久化层)
package com.cpz.dao;
import com.cpz.domain.User;
import java.util.List;
public interface UserDao {
// 查询所有
public List<User> findAll();
// 保存
public void saveUser(User user);
// 修改
public void updateUser(User user);
// 删除
public void deleteUser(Integer id);
// 主键查询
public User findUserById(Integer id);
// 模糊查询(方案一)
public List<User> findUserByUserNameLike1(String username);
// 模糊查询(方案二)
public List<User> findUserByUserNameLike2(String username);
// 查询数量
public int findCount();
}
4.4 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">
<!--mybatis的核心配置文件-->
<configuration>
<!--配置连接数据库的环境-->
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql:///test"></property>
<property name="username" value="root"></property>
<property name="password" value="1234"></property>
</dataSource>
</environment>
</environments>
<!--加载映射文件-->
<mappers>
<mapper resource="com/cpz/dao/UserDao.xml"></mapper>
</mappers>
</configuration>
4.5 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">
<!--建立映射:建立Dao中的方法(namespace+id)和数据库sql执行的映射(查询的结果封装到实体中)-->
<mapper namespace="com.cpz.dao.UserDao">
<!--查询所有
namespace="com.itheima.dao.UserDao":表示类名的路径
id="findAll":表示方法名
resultType="com.itheima.domain.User":结果集的类型封装,封装到User实体中(实体中的属性和数据库表的字段一致)
-->
<select id="findAll" resultType="com.cpz.domain.User">
select * from user
</select>
<!-- 保存
parameterType:参数类型映射
#{username},#{birthday},#{sex},#{address}:表示OGNL表达式,用对象图导航语言,使用#{}读取User中的属性值
保存之后,将id查询的值,封装到实体中的id属性中
selectKey:
keyProperty="id":封装到id的属性中
keyColumn="id":查询的列的名称
resultType="":结果类型,完整写法java.lang.Integer == 使用别名 int(Int)
order="AFTER":表示在执行insert语句之后执行
-->
<insert id="saveUser" parameterType="com.cpz.domain.User">
<selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER">
select last_insert_id();
</selectKey>
insert into user (username,birthday,sex,address) values (#{username},#{birthday},#{sex},#{address});
</insert>
<!--修改-->
<update id="updateUser" parameterType="com.cpz.domain.User">
update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
</update>
<!--删除
如果传递的是1个参数,通过类型的方式写入值,所以名称随意命名
-->
<delete id="deleteUser" parameterType="int">
delete from user where id = #{id}
</delete>
<!--主键查询-->
<select id="findUserById" parameterType="int" resultType="com.cpz.domain.User">
select * from user where id = #{id}
</select>
<!--模糊查询(方案一)-->
<select id="findUserByUserNameLike1" resultType="com.cpz.domain.User" parameterType="java.lang.String">
select * from user where username like #{name}
</select>
<!--模糊查询(方案二)-->
<select id="findUserByUserNameLike2" resultType="com.cpz.domain.User" parameterType="java.lang.String">
select * from user where username like '%${value}%'
</select>
<!--查询数量-->
<select id="findCount" resultType="int">
select count(*) from user
</select>
</mapper>
4.6 log4j.properties 日志配置文件
# Set root category priority to INFO and its only appender to CONSOLE.
#log4j.rootCategory=INFO, CONSOLE debug info warn error fatal
log4j.rootCategory=debug, CONSOLE
# Set the enterprise logger category to FATAL and its only appender to CONSOLE.
log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE
# CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n
# LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=d://axis.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n
4.7 TestUserDao 测试类
package com.cpz.test;
import com.cpz.dao.UserDao;
import com.cpz.domain.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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
public class TestUserDao {
InputStream is;
SqlSession sqlSession;
UserDao userDao;
@Before
public void init() throws IOException {
// 1:加载sqlMapConfig.xml(配置文件),同时加载UserDao.xml(映射文件)
is = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2:创建SqlSessionFactory(1个),相当于DataSource
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
// 3:创建SqlSession(多个,每个线程都会使用独立的SqlSession),相当于Connection
sqlSession = sqlSessionFactory.openSession();
// 4:使用SqlSession获取Dao(底层反射和代理技术完成)
userDao = sqlSession.getMapper(UserDao.class);
}
@After
public void destroy() throws IOException {
// 手动提交
sqlSession.commit();
// 6:关闭资源
sqlSession.close();
is.close();
}
// 查询所有
@Test
public void findAll() throws IOException {
List<User> list = userDao.findAll();
for (User user : list) {
System.out.println(user);
}
}
// 保存
@Test
public void saveUser(){
User user = new User();
user.setUsername("哪吒");
user.setSex("男");
user.setAddress("钱塘关");
user.setBirthday(new Date());
System.out.println(user);
userDao.saveUser(user);
System.out.println(user);
}
// 修改
@Test
public void updateUser(){
User user = new User();
user.setId(56);
user.setUsername("浩克");
user.setSex("男");
user.setAddress("美国");
user.setBirthday(new Date());
userDao.updateUser(user);
}
// 删除
@Test
public void deleteUser(){
userDao.deleteUser(56);
}
// 主键查询
@Test
public void findUserById(){
User user = userDao.findUserById(52);
System.out.println(user);
}
// 模糊查询(方案一)
// 如果: select * from user where username like #{abc},此时%%在条件中完成
// 此时执行的sql:select * from user where username like ?
@Test
public void findUserByUserNameLike1() {
List<User> list = userDao.findUserByUserNameLike1("%王%");
for (User user : list) {
System.out.println(user);
}
}
// 模糊查询(方案二)
// 如果: select * from user where username like '%${value}%',此时%%在sql中完成
// 此时执行的sql:select * from user where username like '%王%'
// ${value}只能写成value,底层源代码固定value
@Test
public void findUserByUserNameLike2(){
List<User> list = userDao.findUserByUserNameLike2("王");
for (User user : list) {
System.out.println(user);
}
}
// 查询数量
@Test
public void findCount(){
int count = userDao.findCount();
System.out.println(count);
}
}
4.8 项目结构