本文是对mybatis3.5.2基本练习的记录,没有使用任何的第三方框架
目录
一、包目录结构
二、两张表结构
用户表(user)
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`hobby` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
文章表(news)
DROP TABLE IF EXISTS `news`;
CREATE TABLE `news` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`content` varchar(255) DEFAULT NULL,
`author` varchar(255) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
三、DAO
package com.bjx.dao;
import java.util.List;
import com.bjx.domain.User;
public interface UserDao {
//获取一条数据
public User getRow(int id);
//获取多条数据
public List<User> getAll();
//使用动态接口mapper获取一条数据
public User getRowMapper(int id);
//使用动态mapper获取全部数据
public List<User> getAllMapper();
//使用动态mapper插入一条数据,返回主键的id
public int insertUserMapper(User user);
//使用动态mapper删除一条数据,返回受影响的行数
public int deleteUserMapper(int id);
//删除一条数据
public int deleteUser(int id);
//使用mapper更新单条数据
public int updateUserMapper(User user);
//update更新单条数据
public int updateUser(User user);
//获取指定用户信息,和用户的所有文章 user表和news表关联
public User getUserAndNews(int id);
}
四、domain(User)
package com.bjx.domain;
import java.util.List;
public class User{
private int id;
private String name;
private String phone;
private String address;
private int age;
private String hobby;
private List<News> news;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getHobby() {
return hobby;
}
public void setHobby(String hobby) {
this.hobby = hobby;
}
public List<News> getNews() {
return news;
}
public void setNews(List<News> news) {
this.news = news;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", phone=" + phone + ", address=" + address + ", age=" + age
+ ", hobby=" + hobby + ", news=" + news + "]";
}
}
五、domain(News)
package com.bjx.domain;
public class News {
private int id;
private String title;
private String content;
private String author;
private int uid;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
@Override
public String toString() {
return "News [id=" + id + ", title=" + title + ", content=" + content + ", author=" + author + ", uid=" + uid
+ "]";
}
}
六、UserMapper.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">
<!--
命名空间,名字可以随意起,只要不冲突即可
但是如果是通过动态代理的接口sqlSession.getMapper(UserDao.class)这样使用的话,namespace就一定要是Dao接口的全路径
比如:com.bjx.dao.UserDao
-->
<!--
动态代理Mapper接口的方式需要注意一下几点
1、这个id一定要和Dao接口中的方法名一致
2、实体类配置文件中Mapper的namespace必须和mapper接口也就是dao接口的全路径一致
3、mapper接口中方法的返回值类型一定要和resultType一致
-->
<mapper namespace="com.bjx.dao.UserDao">
<!-- 对象映射,可以不写 -->
<!-- 查询功能,resultType 设置返回值类型 -->
<select id="getRow" parameterType="int"
resultType="com.bjx.domain.User">
SELECT * FROM user WHERE id = #{id}
</select>
<select id="getAll" resultType="com.bjx.domain.User"> <!-- 书写 SQL 语句 -->
SELECT * FROM user
</select>
<select id="getRowMapper" resultType="com.bjx.domain.User">
SELECT * FROM user WHERE id = #{id}
</select>
<select id="getAllMapper" resultType="com.bjx.domain.User">
SELECT * FROM user
</select>
<!-- 新增的Statement
id:唯一标识,随便写,在同一个命名空间下保持唯一,使用动态代理之后要求和方法名必须保持一致
非动态mapper会在执行语句时指定方法名,所以不必保持一致
parameterType:参数的类型,使用动态代理之后和方法的参数类型一致
useGeneratedKeys:开启主键回写,启用后插入数据成功后就可以获取主键
keyColumn:指定数据库的主键
keyProperty:主键对应的pojo属性名
-->
<insert id="insertUserMapper" useGeneratedKeys="true" keyColumn="id" keyProperty="id"
parameterType="com.bjx.domain.User">
INSERT INTO user (name,phone,address,age,hobby)
VALUES
(#{name},#{phone},#{address},#{age},#{hobby});
</insert>
<delete id="deleteUserMapper">
delete from user where id=#{id}
</delete>
<delete id="deleteUser">
delete from user where id=#{id}
</delete>
<!--
参数类型和上面的返回值类型都一定要写全路径
字段名和参数名一定要一致
-->
<update id="updateUserMapper" parameterType="com.bjx.domain.User">
UPDATE user SET name = #{name} WHERE id = #{id}
</update>
<update id="updateUser" parameterType="com.bjx.domain.User">
UPDATE user SET name= #{name} WHERE id = #{id}
</update>
<!--
一对多关联 start
1、<resultMap> 的id属性是和<select>的resultMap值一致的
2、<resultMap>的autoMapping为true则表示把主表(user)的信息也查出来,默认是只查多表
3、collection中是子对象的映射
4、resultMap和collection中的<id>分别设置的是各自的主键
5、对于主表(user)可以不设置<result />,但是对于多表一定要设置,否则查询为null,其中column代表要查询的字段名,property代表实体类中的属性名
6、<result />中的column值得是查询出来的字段,不一定是数据表中的字段,比如如果起别名的话两则就是不相同的
-->
<resultMap type="com.bjx.domain.User" id="resultUserAndNews" autoMapping="true">
<id column="userid" property="id"></id>
<collection property="news" ofType="com.bjx.domain.News" column="uid">
<id property="id" column="nid" javaType="int" jdbcType="INTEGER"/>
<result column="title" property="title"/>
<result column="content" property="content"/>
<result column="author" property="author"/>
<result column="uid" property="uid"/>
</collection>
</resultMap>
<!-- select的第一种写法,这里如果两张表的主键名字相同一定要起别名,否则可能只能查询一条-->
<select id="getUserAndNews" resultMap="resultUserAndNews" parameterType="int">
SELECT u.*,n.title,n.uid,n.content,n.author
FROM user u, news n
WHERE u.id=n.uid AND u.id=#{id}
</select>
<!-- select的第二种写法 ,貌似没必要,但这样写也行的-->
<!-- <select id="getUserAndNews" resultMap="resultUserAndNews" parameterType="int">
SELECT u.id as userid,u.name,n.id as nid,n.title,n.uid,n.content,n.author
FROM user u left join news n On u.id=n.uid and u.id=#{id}
</select> -->
<!-- 以上两种方法在<collection>中是无需使用select的,但是还有第三中方式就是使用select属性,这种方法会产出N+1的问题 -->
<!-- 一对多关联 end -->
</mapper>
七、Service中直接调用、测试
package com.bjx.service;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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.bjx.dao.UserDao;
import com.bjx.domain.News;
import com.bjx.domain.User;
public class UserService {
private static SqlSession sqlSession=null;
static {
/**
* 1、获得 SqlSessionFactory
* 2、获得 SqlSession
* 3、调用在 mapper 文件中配置的 SQL 语句
*/
String resource = "mybatis.xml"; // 定位核心配置文件
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream(resource);
} catch (IOException e) {
e.printStackTrace();
}
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);// 创建 SqlSessionFactory
sqlSession = sqlSessionFactory.openSession(); // 获取到 SqlSession
}
/**
* 获取一条用户数据
* 一般获取一个值或对象使用selectOne方法
*/
@Test
public void getRow() {
User user = sqlSession.selectOne("com.bjx.dao.UserDao.getRow",1);
System.out.println(user.getAddress());
}
/**
* 获取全部用户数据
* 获取的结果集是一个集合则使用selectList方法
*/
@Test
public void getAll() {
List<User> personList = sqlSession.selectList("com.bjx.dao.UserDao.getAll");
for (User u : personList){
System.out.println(u);
}
}
/**
* 使用动态mapper方式获取一条数据
* 动态代理Mapper实现类,可以只写接口,不写实现类,这种方式比较主流
*/
@Test
public void getRowMapper() {
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = userDao.getRowMapper(1);
System.out.println(user);
}
/**
* 使用动态mapper方式获取全部数据
* 动态代理Mapper实现类,可以只写接口,不写实现类,这种方式比较主流
*/
@Test
public void getAllMapper() {
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> allUser = userDao.getAllMapper();
for (User user : allUser) {
System.out.println(user.getAddress());
}
}
/**
* 插入数据
* 通过Mapper添加一条数据,使用的是最新的3.5版本,默认sql是不提交,一定要手动提交
*/
@Test
public void insertUserMapper() {
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = new User();
user.setAddress("mapper添加");
user.setName("nz");
user.setPhone("18326552894");
userDao.insertUserMapper(user);
System.out.println(user.getId());
sqlSession.commit();
}
/**
* 通过mapper删除数据
*/
@Test
public void deleteUserMapper() {
UserDao userDao = sqlSession.getMapper(UserDao.class);
int i = userDao.deleteUserMapper(4);
System.out.println(i);
sqlSession.commit();
}
/**
* 删除数据
* 删除,注意如果有外键关联是不能删除的,这是mysql的机制
*/
@Test
public void deleteUser() {
int count = sqlSession.delete("com.bjx.dao.UserDao.deleteUser", 2);
sqlSession.commit();
System.out.println(count);
}
/**
* 更新数据
* mapper方式
*/
@Test
public void updateUserMapper() {
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = new User();
user.setName("update123");
user.setId(6);
userDao.updateUserMapper(user);
sqlSession.commit();
}
/**
* 更新数据
* update 方式
*/
@Test
public void updateUser() {
User user = new User();
user.setName("update2");
user.setId(7);
sqlSession.update("com.bjx.dao.UserDao.updateUser",user);
sqlSession.commit();
}
/**
* 一对多关联查询
* mapper、selectOne两种方式
*/
@Test
public void getUserAndNews() {
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = userDao.getUserAndNews(1);
// User user = sqlSession.selectOne("com.bjx.dao.UserDao.getUserAndNews",1);
System.out.println(user);
}
}
八、配置文件(mybatis.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>
<!-- 引入外部配置资源 -->
<properties resource="jdbc.properties"></properties>
<!-- 类型别别名 -->
<!-- <typeAliases>
<typeAlias type="com.bjx.mybatis.domain.User" alias="User" />
</typeAliases> -->
<!-- mybatis数据库连接和应用环境 -->
<environments default="dev">
<environment id="dev">
<!-- JDBC事务 -->
<transactionManager type="JDBC" />
<!-- 配置数据库的属性) -->
<dataSource type="POOLED">
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
</environments>
<!-- 加载mybatis的映射文件 -->
<mappers>
<mapper resource="com/bjx/mapping/UserMapper.xml" />
</mappers>
</configuration>
九、properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.10.113:3306/ask?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
username=root
password=mservdb@admin123