目录
Mybatis3注解开发分步详解
1、数据库表sql
2、User实体类
3、db.properties
4、SqlMapConfig
5、IUserDao接口
6、测试
Mybatis3注解开发分步详解
1、数据库表sql
/*
Navicat MySQL Data Transfer
Source Server : zhulang
Source Server Version : 50727
Source Host : localhost:3306
Source Database : mybatis
Target Server Type : MYSQL
Target Server Version : 50727
File Encoding : 65001
Date: 2019-10-21 17:38:41
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`ID` int(11) NOT NULL COMMENT '编号',
`UID` int(11) DEFAULT NULL COMMENT '用户编号',
`MONEY` double DEFAULT NULL COMMENT '金额',
PRIMARY KEY (`ID`),
KEY `FK_Reference_8` (`UID`),
CONSTRAINT `FK_Reference_8` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES ('1', '41', '10000');
INSERT INTO `account` VALUES ('2', '45', '15000');
INSERT INTO `account` VALUES ('3', '41', '20000');
-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`ID` int(11) NOT NULL COMMENT '编号',
`ROLE_NAME` varchar(30) DEFAULT NULL COMMENT '角色名称',
`ROLE_DESC` varchar(60) DEFAULT NULL COMMENT '角色描述',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES ('1', '院长', '管理整个学院');
INSERT INTO `role` VALUES ('2', '总裁', '管理整个公司');
INSERT INTO `role` VALUES ('3', '校长', '管理整个学校');
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`address` varchar(256) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('41', '老王', '2019-10-25 17:47:08', '男', '北京朝阳');
INSERT INTO `user` VALUES ('42', '小二王', '2019-10-25 17:47:08', '女', '湖北武汉');
INSERT INTO `user` VALUES ('45', '百里半', '2019-10-25 17:47:08', '男', '湖北宜昌');
INSERT INTO `user` VALUES ('48', '小马', '2019-10-25 17:47:08', '女', '湖北黄石');
-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
`UID` int(11) NOT NULL COMMENT '用户编号',
`RID` int(11) NOT NULL COMMENT '角色编号',
PRIMARY KEY (`UID`,`RID`),
KEY `FK_Reference_10` (`RID`),
CONSTRAINT `FK_Reference_10` FOREIGN KEY (`RID`) REFERENCES `role` (`ID`),
CONSTRAINT `FK_Reference_9` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES ('41', '1');
INSERT INTO `user_role` VALUES ('45', '1');
INSERT INTO `user_role` VALUES ('41', '2');
2、User实体类
package com.bailiban.model;
import java.util.Date;
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username == null ? null : username.trim();
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
/*
* (non-Javadoc)
*
* @see java.lang.Object#toString()
*/
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", birthday=" + birthday + ", sex=" + sex + ", address="
+ address + "]";
}
public void setSex(String sex) {
this.sex = sex == null ? null : sex.trim();
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address == null ? null : address.trim();
}
}
3、db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8&useUnicode=true
jdbc.username=root
jdbc.password=123456
4、SqlMapConfig
<?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>
<!-- 注意：mybatis的配置文件是有顺序的 -->
<!-- 读取数据源配置文件db.properties 两种方式任选一，推荐方式二-->
<!--方式一：绝对路径使用url -->
<!-- <properties url="file:\D:\mycode\mybatis02\src\main\resources\db.properties"/> -->
<!--方式二：相对路径是用resource -->
<properties resource="db.properties"/>
<!--可以配置多个environment，例如开发环境、测试环境......default通过id指定默认数据库环境 -->
<typeAliases>
<!-- 避免返回值类型写全限定名，可以采取取别名方式，推荐以下扫描包方式，别名为类名小写 -->
<package name="com.bailiban.model"/>
</typeAliases>
<environments default="mysql">
<environment id="mysql">
<!-- 开启事务 -->
<transactionManager type="JDBC"></transactionManager>
<!--数据源类型常用pooled，带有连接池功能 -->
<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>
<mappers>
<!-- 映射器，推荐下面这种扫描包方式，xml和注解开发均实用 -->
<!--注意：此处若已配置映射器，在代码中就无须再绑定接口configuration.addMapper(UserDao.class), 只能选其一，推荐此方式写在配置文件 -->
<package name="com.bailiban.dao"/>
</mappers>
</configuration>
5、IUserDao接口
/**
* Copyright (C) 2019 Baidu, Inc. All Rights Reserved.
*/
package com.bailiban.dao;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.bailiban.model.User;
/**
* @author zhu
* @version 创建时间：2019年10月31日 下午5:56:01
*/
public interface IUserDao {
// 增，新增数据后，返回主键id，使用 @SelectKey注解，mysql是插入后更新id，因此before=false，
@Insert("insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address})")
/*
* @SelectKey(statement = { "select last_insert_id()" }, before = false, keyProperty = "id", keyColumn = "id",
* resultType = Integer.class)
*/
// 返回主键id值也可以使用以下注解，设置useGeneratedKeys = true，两种方式均需要数据库设置主键为自动增长
@Options(keyProperty = "id", keyColumn = "id", useGeneratedKeys = true)
public int insert(User user);
// 删
@Delete("delete from user where id=#{id} ")
public int deleteByPrimaryKey(int id);
// 改,注意，若新set的值为空，则会覆盖原值为null，若原值有非空约束，则会报错，可以使用@UpdateProvider注解，但不推荐，推荐使用xml方式
@Update("update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}")
public int update(User user);
// 查，如果数据库表对应的列名和实体类属性名一致，可以不写Results，否则返回结果服务映射到属性
@Select("select * from user where id=#{id} ")
@Results(id = "resultMap", value = { @Result(column = "username", property = "username") })
public User selectByPrimaryKey(int id);
// 模糊查询，注意不是like #{name}
@Select("select * from user where username like concat ('%',#{name},'%')")
public List<User> selectLikeName(String name);
// 查询所有
@Select("select * from user")
public List<User> selectAll();
}
6、测试
/**
* Copyright (C) 2019 Baidu, Inc. All Rights Reserved.
*/
package com.bailiban.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
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.After;
import org.junit.Before;
import org.junit.Test;
import com.bailiban.dao.IUserDao;
import com.bailiban.model.User;
/**
* @author zhu
* @version 创建时间：2019年11月1日 上午10:02:33
*/
public class MybatisAnnotation {
SqlSessionFactoryBuilder builder;
InputStream inputStream;
SqlSessionFactory factory;
SqlSession session;
IUserDao iUserDao;
@Before
public void init() throws IOException {
// 生成builder
builder = new SqlSessionFactoryBuilder();
// 生成工厂,需要读取配置文件
inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
factory = builder.build(inputStream);
/* 若SqlMapConfig中没有配置映射器，则需要使用以下代码注册绑定接口，二者只能选其一 */
// Configuration configuration = factory.getConfiguration();
// configuration.addMapper(IUserDao.class);
session = factory.openSession();
iUserDao = session.getMapper(IUserDao.class);
}
@After
public void destory() {
try {
if (inputStream != null) {
inputStream.close();
}
if (session != null) {
session.close();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Test
public void insert() {
User user = new User();
user.setAddress("江西南昌");
user.setBirthday(new Date(2019, 12, 23));
user.setSex("女");
user.setUsername("安安");
int insert = iUserDao.insert(user);
/* 需要手动提交事务 */
session.commit();
System.out.println(user.toString());
}
@Test
public void deleteByPrimary() {
int id = 56;
int i = iUserDao.deleteByPrimaryKey(id);
session.commit();
System.out.println(i);
}
@Test
public void update() {
User user = new User();
user.setAddress("湖北武汉洪山区");
user.setBirthday(new Date(2019, 11, 01, 10, 10));
user.setSex("男");
user.setUsername("大大");
user.setId(42);
int update = iUserDao.update(user);
session.commit();
System.out.println(update);
}
@Test
public void selectByPrimaryKey() {
int id = 41;
User user = iUserDao.selectByPrimaryKey(id);
System.out.println(user);
}
@Test
public void selectLikeByName() {
List<User> list = iUserDao.selectLikeName("大");
System.out.println(list.toString());
}
@Test
public void selectAll() {
List<User> list = iUserDao.selectAll();
for (User user : list) {
System.out.println(user);
}
}
}
