Mysql
驱动Mybatis
启动器<!--Mybatis配置-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
Mysql
连接信息spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/friday?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
username: root
password: 123123
platform: mysql
java bean
映射数据表Mapper
定义 sql
行为# Mybatis配置
mybatis:
type-aliases-package: com.springbootstudy.first_springboot.model # 实体pojo的储存路径,项目包名.model
mapper-locations: classpath:/mybatis-mappers/* # mapper 配置文件路径
configuration:
mapUnderscoreToCamelCase: true # 驼峰转换:将名字从abc_edf 转换微abcEdf
在 model
包中建立BaseEntity
@Data
public abstract class BaseEntity<ID extends Serializable> implements Serializable {
private static final long serialVersionUID = 8925514045582235838L;
private ID id;
private Date createTime = new Date();
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date updateTime = new Date();
}
建立SysUser
@Data
public class SysUser extends BaseEntity<Long> {
private static final long serialVersionUID = -6525908145032868837L;
private String username;
private String password;
private String nickname;
private String headImgUrl;
private String phone;
private String telephone;
private String email;
@JsonFormat(pattern = "yyyy-MM-dd")
private Date birthday;
private Integer sex;
private Integer status;
private String intro;
}
mapper
行为信息在 model
下创建 BaseEntity
@Data
public abstract class BaseEntity<ID extends Serializable> implements Serializable {
private static final long serialVersionUID = 8925514045582235838L;
private ID id;
private Date createTime = new Date();
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date updateTime = new Date();
}
在 model
下创建 SysUser
@Data
public class SysUser extends BaseEntity<Long> {
private static final long serialVersionUID = -6525908145032868837L;
private String username;
private String password;
private String nickname;
private String headImgUrl;
private String phone;
private String telephone;
private String email;
@JsonFormat(pattern = "yyyy-MM-dd")
private Date birthday;
private Integer sex;
private Integer status;
private String intro;
}
在dao下创建UserDao
@Mapper
public interface UserDao {
@Select("select * from sys_user t where t.username = #{username}")
SysUser getUser(String username);
}
mapper
这个DAO交给 Spring IOC
容器管理mapper
映射文件@Mapper
注解的接口生成一个实现类。可以直接在Springboot的主入口处添加 @MapperScan
扫描指定路径的mapper,就不用在每个dao上都加一个@mapper
注解了。
@MapperScan(basePackages = "com.springboot.study.mapper")
@SpringBootTest
class FirstSpringbootApplicationTests {
//@Autowired 在测试单元中使用Autowried会报错,所以这里使用resource代替
@Resource
UserDao userDao;
@Test
void contextLoads() {
SysUser info = userDao.getUser("admin");
System.out.println(info);
}
}
测试结果
-- ----------------------------
-- Table structure for `sys_user`
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` varchar(60) NOT NULL,
`nickname` varchar(255) DEFAULT NULL,
`headImgUrl` varchar(255) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL,
`telephone` varchar(30) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`sex` tinyint(1) DEFAULT NULL,
`status` tinyint(1) NOT NULL DEFAULT '1',
`createTime` datetime NOT NULL,
`updateTime` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES ('1', 'admin', '$2a$10$DFIwAy//Ol3X6Q1e5CEue.FfUnJ5Fj709z9oY1pwCWzpca.SpYs72', '管理员', null, null, '158784879852', '12@qq.com', null, null, '1', '2019-04-08 00:20:51', '2019-04-08 00:20:51');
INSERT INTO `sys_user` VALUES ('2', 'user', '$2a$10$ILWAB4ZOoRr2pXqarliI6uAuL7Q/7dAMTpWO9p7dyVSHHO7zQMTeW', '用户', null, null, '1111111111', '11@qq.com', '2019-03-31', null, '1', '2019-04-09 06:44:50', '2019-04-09 06:44:50');
INSERT INTO `sys_user` VALUES ('3', 'alex', '534b44a19bf18d20b71ecc4eb77c572f', '讲师', null, '', '13245698712', 'alex@qq.com', '2019-03-31', '1', '1', '2019-03-27 02:27:35', '2019-04-09 07:57:17');
INSERT INTO `sys_user` VALUES ('18', 'user1', '96e79218965eb72c92a549dd5a330112', '111', null, null, '123455432123', '134@qq.com', '2019-05-12', null, '1', '2019-05-14 04:44:22', '2019-05-14 04:44:22');
连接池技术技术会预先建立多个数据库连接对象,然后将连接对象保存到连接池中,当客户请求到来时,从池中取出一个连接对象为客户服务,当请求完成后,客户程序调用close()
方法,将连接对象放回池中。
连接池比较
配置依赖
<!--Druid连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
在spring.datasource下配置以下信息
# 初始化大小,最小,最大
initialSize: 1
minIdle: 3
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 30000
validationQuery: select 'x'
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 是否缓存preparedStatement,打开PSCache,并且指定每个连接上PSCache的大小。官方建议MySQL下建议关闭,如果用SQL防火墙 建议打开
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
创建config
包,新建DruidConfig.java
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druid(){
return new DruidDataSource();
}
}
运行测试单元是否正常输出。
使用@Result
注解定义字段别名
@Results(
@Result(property = "userName", column = "username"),
@Result(property = "passWord", column = "password")
)
property
与 column
分别表示本地pojo和数据库表中字段的对应。
返回自增主键,使用 @Option
@Options(useGeneratedKeys = true, keyProperty = "id")
返回非自增主键,使用 @SelectKey
@SelectKey(statement = "SELECT LASST_INSERT_ID()", keyProperty = "主键名", resultType = Long.class, before = false)
@Mapper
public interface UserDao {
// 根据用户名获取用户信息
@Results(
@Result(property = "username", column = "username")
)
@Select("select * from sys_user t where t.username = #{username}")
SysUser getUser(String username);
//删除指定id的用户信息
@Delete("delete from sys_user where id = #{id}")
int deleteUser(Long id);
//插入用户信息
@Options(useGeneratedKeys = true, keyProperty = "id")
@Insert("insert into sys_user(username, password, nickname, headImgUrl, phone, telephone, email, birthday, sex, status, createTime, updateTime) values(#{username}, #{password}, #{nickname}, #{headImgUrl}, #{phone}, #{telephone}, #{email}, #{birthday}, #{sex}, #{status}, now(), now())")
int save(SysUser sysUser);
//根据用户id获取信息
@Select("select * from sys_user where id = #{id}")
SysUser getById(Long id);
}
官方文档: http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html
注解+xml的编码步骤
在通常的情况下,一些简单的sql操作我们都使用注解方式进行,较为复杂的一些操作我们使用xml配置进行,因为xml配置更便于后期的维护。
编写抽象接口
//更新用户信息
int updateUser(SysUser user);
配置xml扫描路径
# Mybatis配置
mybatis:
type-aliases-package: com.springbootstudy.first_springboot.model # 实体pojo的储存路径,项目包名.model
mapper-locations: classpath:/mybatis-mappers/* # mapper 配置文件路径
configuration:
mapUnderscoreToCamelCase: true # 驼峰转换;将名字从abc_edf 转换微AbcEdf
在项目resources
目录下新建mybatis-mappers
目录,编写mapper.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">
<mapper namespace="com.sxbang.friday.dao.UserDao">
<update id="updateUser">
update sys_user t
<set>
<if test="username != null">
username = #{username},
</if>
<if test="nickname != null">
nickname = #{nickname},
</if>
<if test="headImgUrl != null">
headImgUrl = #{headImgUrl},
</if>
<if test="phone != null">
phone = #{phone},
</if>
<if test="telephone != null">
telephone = #{telephone},
</if>
<if test="email != null">
email = #{email},
</if>
<if test="birthday != null">
birthday = #{birthday},
</if>
<if test="sex != null">
sex = #{sex},
</if>
<if test="status != null">
status = #{status},
</if>
updateTime = #{updateTime}
</set>
where t.id = #{id}
</update>
</mapper>
在IDEA中安装 GenerateAllSetter
插件,在创建实体类时可以自动为我们生成对象的 setter
方法,例如
TbUser tbUser = new TbUser();
tbUser.setUsername("Lusifer");
tbUser.setPassword("123456");
tbUser.setPhone("15888888888");
tbUser.setEmail("topsale@vip.qq.com");
tbUser.setCreated(new Date());
tbUser.setUpdated(new Date());
tbUserMapper.insert(tbUser);
import java.util.Date;
import com.funtl.hello.spring.boot.domain.TbUser;
import com.funtl.hello.spring.boot.mapper.TbUserMapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
@Transactional
@Rollback
public class HelloSpringBootApplicationTests {
/**
* 注入 DAO
*/
@Autowired
private TbUserMapper tbUserMapper;
/**
* 查询
*/
@Test
public void testSelectAll() {
List<TbUser> tbUsers = tbUserMapper.selectAll();
tbUsers.forEach(tbUser -> {
System.out.println(tbUser);
});
}
/**
* 条件查询
*/
@Test
public void testSelectCondition() {
Example example = new Example(TbUser.class);
example.createCriteria().andEqualTo("username", "zhangsan");
List<TbUser> tbUsers = tbUserMapper.selectByExample(example);
tbUsers.forEach(tbUser -> {
System.out.println(tbUser);
});
}
/**
* 新增
*/
@Test
public void testInsert() {
TbUser tbUser = new TbUser();
tbUser.setUsername("Lusifer");
tbUser.setPassword("123456");
tbUser.setPhone("15888888888");
tbUser.setEmail("topsale@vip.qq.com");
tbUser.setCreated(new Date());
tbUser.setUpdated(new Date());
tbUserMapper.insert(tbUser);
}
/**
* 更新
*/
@Test
public void testUpdate() {
TbUser tbUser = tbUserMapper.selectByPrimaryKey(37L);
tbUser.setUsername("Happy");
tbUserMapper.updateByPrimaryKey(tbUser);
}
/**
* 修改
*/
@Test
public void testDelete() {
tbUserMapper.deleteByPrimaryKey(37L);
}
/**
* 分页
*/
@Test
public void testPage() {
Example example = new Example(TbUser.class);
example.createCriteria().andLike("username", "z%");
PageHelper.startPage(1, 5);
PageInfo<TbUser> pageInfo = new PageInfo<>(tbUserMapper.selectByExample(example));
System.out.println(pageInfo.getTotal());
System.out.println(pageInfo.getPages());
pageInfo.getList().forEach(tbUser -> {
System.out.println(tbUser);
});
}
}