首先MyBatis是一个ORM框架，那么理所当然，我们现在需要创建几张表：
-
Users表(用户信息表)
create table users( user_id int primary key auto_increment, user_name varchar(20) not null, user_pwd varchar(20) not null, user_realname varchar(50) not null, user_img varchar(50) )charset=utf8;
-
Details表（用户详情信息表，其中user_id是外键，用于多表查询）
-
create table details( detail_id int primary key auto_increment, user_addr varchar(32) not null, user_tel varchar(11) not null, user_desc varchar(50) , user_id int not null, foreign key (user_id) references Users(user_id) )charset=utf8;
-
Clazz表（由于java不能使用class命名，所以使用clazz）
-
create table clazz( cId int primary key auto_increment, cName varchar(20) not null, cDesc varchar(20) not null )charset=utf8;
-
Student表(学生表)
-
create table students( sid varchar(5) primary key , sName varchar(20) not null, sage int not null, scid int not null, foreign key (scid) references Clazz(cid) )charset=utf8;
-
数据库创建完成
添加本次所需依赖坐标
org.mybatis mybatis 3.4.6 mysql mysql-connector-java 5.1.47 log4j log4j 1.2.17 junit junit 4.13.2 test org.projectlombok lombok 1.18.20 com.alibaba druid 1.1.10 org.apache.maven.plugins maven-surefire-plugin 2.22.2 true
添加完成之后记得刷新一下
由于本次项目整合Druid连接池，所以先写一个DruidDataSourcesFactory工具类用于主配置文件的编写
package com.ccl.Utils;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.datasource.pooled.PooledDataSourceFactory;
public class DruidDataSourcesFactory extends PooledDataSourceFactory {
public DruidDataSourcesFactory(){
this.dataSource = new DruidDataSource();
}
}
然后编写数据库连接所需要的jdbc.properties文件
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/db4?characterEncoding=utf8&useSSL=true username=root password=admin
同时编写一下log4j.properties配置文件
# 声明日志的声明级别及输出方式 log4j.rootLogger=DEBUG,stdout # MyBatis Logging configuration... log4j.logger.org.mybatis.example.BlogMapper=TRACE # Console output... log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout # 定义日志的打印格式 %t表示线程名称 %5p 输出日志级别 %msg表示日志信息 log4j.appender.stdout.layout.ConversionPattern=[%t] %5p -%msg :%m%n
接下来我们现在开始配置MyBatis-config.xml主配置文件
3、编写数据库对应的domain对象
**注意：**在编写domain对象时尽可能保证对象属性与数据库字段名字的统一
-
User类
-
package com.ccl.domain; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; @Data @NoArgsConstructor @AllArgsConstructor @ToString public class User { private Integer user_id; private String user_name; private String user_pwd; private String user_realName; private String user_img; } -
Detail类
-
package com.ccl.domain; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; @Data @NoArgsConstructor @AllArgsConstructor @ToString public class Detail { private Integer detail_id; private String user_addr; private String user_tel; private String user_desc; private Integer user_id; } -
Clazz类
-
package com.ccl.domain; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; @Data @NoArgsConstructor @AllArgsConstructor @ToString public class Clazz { private Integer cId; private String cName; private String cDesc; } -
Student类
package com.ccl.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Student {
private String sid;
private String sName;
private String sage;
private String sCid;
}
4、接下来为我们注解开发的CURD做准备
编写MyBatisUtil工具类
package com.ccl.Utils;
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 java.io.IOException;
import java.io.InputStream;
public class MyBatisUtil {
private static SqlSessionFactory factory;
//线程锁
private static ThreadLocal local = new ThreadLocal();
//static 代码块随着类的加载而加载
static{
try {
//加载MyBatis-config.xml配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("MyBatis-config.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
factory = builder.build(resourceAsStream);
} catch (IOException e) {
e.printStackTrace();
}
}
//对外获取SqlSessionFactory对象
public static SqlSessionFactory getSqlSessionFactory() {
return factory;
}
//对内方法
private static SqlSession getSqlSession(boolean isAutoCommit){
//sqlSession对象从local中获取
SqlSession sqlSession =local.get();
//如果第一次SqlSession为空
if(sqlSession == null ){
sqlSession = factory.openSession(isAutoCommit);
local.set(sqlSession);
}
return sqlSession;
}
//对外方法
public static SqlSession getSqlSession(){
return getSqlSession(false);
}
//对外方法直接获取Mapper一般查询会用到此方法
public static T getMapper(Class c){
SqlSession sqlSession = getSqlSession(true);
return sqlSession.getMapper(c);
}
//用于增删改操作完成之后控制台输出
public static void SystemOut(int i){
System.out.println(i > 0 ? "操作成功" : "未对数据库造成影响");
}
}
5、Dao层
Dao层用于注解开发，首先
单表的CRUDUserDao举例，insert操作
public interface UserDao {
@Insert("insert into users (user_name,user_pwd,user_realName,user_img) values (#{user_name} ,#{user_pwd} , #{user_realName} , #{user_img})")
public int insertUser(User user);
}
添加测试类
public class UserDaoTest {
@Test
public void testInsertUser(){
//创建User对象
User user = new User(0, "梨花", "12345", "李华", "01.jpg");
//只有insert一个对象，所以直接调用getMapper()获取Mapper对象
int i = MyBatisUtil.getMapper(UserDao.class).insertUser(user);
MyBatisUtil.SystemOut(i);
}
}
现在项目还不能够运行，因为我们的MyBatis-config.xml配置文件还没完全配置完成，需要在标签上方加入标签，在其下方加入标签
现在可以完美运行insert操作
为了用于测试开发，我们重复以上步骤，多添加几条数据
UserDao delete操作
UserDao:
@Delete("delete from users where user_id = #{user_id}")
public int deleteUser(Integer user_id);
UserDaoTest:
@Test
public void testDeleteUser(){
UserDao userDao = MyBatisUtil.getMapper(UserDao.class);
int i = userDao.deleteUser(6);
MyBatisUtil.SystemOut(i);
}
学会了增加和删除，update操作也是同理
UserDao
@Update("update users set " +
"user_name = #{user_name}," +
"user_pwd = #{user_pwd}," +
"user_realName = #{user_realName}" +
"where user_id = #{user_id}")
public int updateUser(User user);
UserDaoTest
@Test
public void testUpdate(){
UserDao userDao = MyBatisUtil.getMapper(UserDao.class);
User user = new User(2,"爱在心头","188888","张三","02.jpg");
int i = userDao.updateUser(user);
MyBatisUtil.SystemOut(i);
}
接下来是查询操作
UserDao：
@Select("select * from users")
public List listUser();
@Select("select * from users " +
"where user_id = #{user_id}")
public User queryUser(Integer user_id);
@Select("select count(*) from users")
public int countUsers();
UserDaoTest:
@Test
public void testSelectListUser(){
UserDao userDao = MyBatisUtil.getMapper(UserDao.class);
List users = userDao.listUser();
for (User user : users) {
System.out.println(user);
}
}
@Test
public void testQueryUser(){
UserDao userDao = MyBatisUtil.getMapper(UserDao.class);
User user = userDao.queryUser(5);
System.out.println(user);
}
@Test
public void testCountUsers(){
UserDao userDao = MyBatisUtil.getMapper(UserDao.class);
int i = userDao.countUsers();
System.out.println("数据库存在用户总数：" + i);
}
多表查询
1、一对一
分析：用户对详情信息是一对一
User–Details
有用户才有用户详情信息，一条用户对应一个用户详情信息，首先在details表中插入相应数据
编写一个DetailsDao
public interface DetailsDao {
@Select("select * from details where user_id = #{user_id}")
public Detail queryDetail(int user_id);
}
public class DetailsTest {
@Test
public void testQueryDetail(){
DetailsDao detailsDao = MyBatisUtil.getMapper(DetailsDao.class);
Detail detail = detailsDao.queryDetail(1);
System.out.println(detail);
}
}
下面编写UserDao下面的对应方法
@Select("select * from users")
@Results(id = "userMap" , value = {
@Result(id = true , column = "user_id" , property = "user_id"),
@Result(column = "user_name" , property = "user_name"),
@Result(column = "user_pwd" ,property = "user_pwd"),
@Result(column = "user_realName" , property = "user_realName"),
@Result(column = "user_img" , property = "user_img"),
@Result(column = "user_id" , property = "detail" ,
one= @One(select = "com.ccl.Dao.DetailsDao.queryDetail" , fetchType = FetchType.EAGER))
})
public List listUser();
UserDaoTest
@Test
public void testSelectListUser(){
UserDao userDao = MyBatisUtil.getMapper(UserDao.class);
List users = userDao.listUser();
for (User user : users) {
System.out.println(user);
}
}
2、一对多
班级对学生为一堆多关系
所以CLassDao：
public interface ClassDao {
@Select("select * from clazz")
@Results(id = "classMap" ,value = {
@Result(id = true , column = "cId", property = "cId"),
@Result(column = "cName" , property = "cName"),
@Result(column = "cDesc" , property = "cDesc")
})
public List listCLass();
}
StudentDao
public interface StudentDao {
@Select("select * from students ")
@Results(id = "resultMap" , value = {
@Result(id = true , column = "sid" ,property = "sid"),
@Result(column = "sName" , property = "sName"),
@Result(column = "sage" , property = "sage"),
@Result(column = "sCid" , property = "sCid"),
@Result(column = "sCid" , property = "clazzes",
many = @Many(select = "com.ccl.Dao.ClassDao.listCLass" ,fetchType = FetchType.LAZY))
})
public List selectStudent();
}
StudentDaoTest
public class StudentDaoTest {
@Test
public void testLisStudent() {
StudentDao studentDao = MyBatisUtil.getMapper(StudentDao.class);
List list = studentDao.selectStudent();
for (Student student : list) {
System.out.println(student);
}
}
}
