在pom.xml中添加依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.2</version>
</dependency>
配置数据源 在application.yml添加如下配置 并修改url连接的数据库 用户名 和 密码
spring:
# 数据源配置
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/test?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
username: root
password: 1234
# Druid配置
druid:
initial-size: 10 #初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
max-active: 100 #最大连接池数量
min-idle: 10 #最小连接池数量
max-wait: 60000 #获取连接时最大等待时间，单位毫秒。
pool-prepared-statements: true #是否缓存preparedStatement，也就是PSCache
max-open-prepared-statements: 100 #要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1 FROM DUAL #验证连接有效性
test-while-idle: true #建议配置为true，不影响性能，并且保证安全性。
test-on-borrow: false #申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
test-on-return: false #归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
stat-view-servlet: #内置监控
enabled: true
url-pattern: /druid/*
#login-username: admin
#login-password: admin
filter:
stat:
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true #是否允许一次执行多条语句，缺省关闭
在pom.xml中添加依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
在application.yml添加如下配置
# Mybatis配置
mybatis:
mapperLocations: classpath:mapper/**/*.xml
typeAliasesPackage: com.xiaohan.bootdemo.entity
#config-location: classpath:mybatis.xml
mapperLocations 扫描mapper.xml文件
typeAliasesPackage 为实体类型起别名
接下来别名包下新建一个Entity类
package com.xiaohan.bootdemo.entity;
import java.util.Date;
public class UserEntity {
private Integer id;
private String name;
private Date createTime;
//省略get set
}
Entity类与数据库中的t_user表对应
image.png
接下来新建一个接口 在该接口中编写 对表进行增删查改的方法
要注意接口上的@Mapper注解
package com.xiaohan.bootdemo.dao;
import com.xiaohan.bootdemo.entity.UserEntity;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
@Mapper
public interface UserDao {
@Insert({"insert into t_user (name,create_time) values (#{name},#{createTime})"})
int insert(UserEntity userEntity);
}
在测试之前先配置好日志 查看sql语句
在resources文件夹下新建 logback-spring.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<include resource="org/springframework/boot/logging/logback/base.xml" />
<logger name="org.springframework.web" level="DEBUG"/>
<logger name="org.springboot.sample" level="TRACE" />
<!-- 配置要打印日志的包 -->
<logger name="com.xiaohan.bootdemo" level="DEBUG" />
</configuration>
编写测试类进行测试
package com.xiaohan.bootdemo;
import com.xiaohan.bootdemo.dao.UserDao;
import com.xiaohan.bootdemo.entity.UserEntity;
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.context.junit4.SpringRunner;
@RunWith(SpringRunner.class)
@SpringBootTest
public class BootdemoApplicationTests {
@Autowired
private UserDao userDao;
@Test
public void insertTest() {
UserEntity userEntity = new UserEntity();
userEntity.setName("张三");
int insert = userDao.insert(userEntity);
Integer id = userEntity.getId();
System.err.println("影响行数==>" + insert);
System.err.println("id==>" + id);
}
}
输出如下
影响行数==>1
id==>null
可以看到id的值为null 要得到id的值还需要以下两步
- 数据库的id设为自增长
- 在方法上添加注解 @Options
@Insert({"insert into t_user (name,create_time) values (#{name},#{createTime})"})
@Options(useGeneratedKeys = true, keyProperty = "id")
int insert(UserEntity userEntity);
重新运行测试类
影响行数==>1
id==>2
后面我就不一一写了 直接把UserDao跟测试类贴出来了
UserDao 在update那里 使用了jdk1.8的新特性 可以在接口里面写被default修饰的方法
package com.xiaohan.bootdemo.dao;
import com.xiaohan.bootdemo.entity.UserEntity;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
@Mapper
public interface UserDao {
@Insert({"insert into t_user (name,create_time) values (#{name},#{createTime})"})
@Options(useGeneratedKeys = true, keyProperty = "id")
int insert(UserEntity userEntity);
@Select({"select * from t_user"})
List<UserEntity> selectAll();
@Select({"select * from t_user where id=#{id}"})
UserEntity selectById(Integer id);
@Delete({"delete from t_user where id=#{id}"})
int deleteById(Integer id);
@Update({"${value}"})
int update(String sql);
default int updateById(UserEntity userEntity) {
return update(new SQL() {{
UPDATE("t_user");
if (userEntity.getName() != null) {
SET("name=" + StringUtils.wrap(userEntity.getName(), "\'"));
}
if (userEntity.getCreateTime() != null) {
String format = DateFormatUtils.format(userEntity.getCreateTime(), "yyyy-MM-dd HH:mm:ss");
SET("create_time=" + StringUtils.wrap(format, "\'"));
}
WHERE("id=" + userEntity.getId());
}}.toString());
}
}
测试类
package com.xiaohan.bootdemo;
import com.xiaohan.bootdemo.dao.UserDao;
import com.xiaohan.bootdemo.entity.UserEntity;
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.context.junit4.SpringRunner;
import java.util.Date;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
public class BootdemoApplicationTests {
@Autowired
private UserDao userDao;
@Test
public void insertTest() {
UserEntity userEntity = new UserEntity();
userEntity.setName("李四");
int i = userDao.insert(userEntity);
Integer id = userEntity.getId();
System.err.println("影响行数==>" + i);
System.err.println("id==>" + id);
}
@Test
public void selectAllTest() {
List<UserEntity> list = userDao.selectAll();
for (UserEntity userEntity:
list) {
System.err.println(userEntity);
}
}
@Test
public void selectByIdTest() {
UserEntity userEntity = userDao.selectById(1);
System.err.println(userEntity);
}
@Test
public void deleteTest() {
int i = userDao.deleteById(1);
System.err.println("影响行数==>" + i);
selectAllTest();
}
@Test
public void updateByIdTest() {
UserEntity userEntity = new UserEntity();
userEntity.setId(2);
userEntity.setName("王五");
userEntity.setCreateTime(new Date());
int i = userDao.updateById(userEntity);
System.err.println("影响行数==>" + i);
selectAllTest();
}
}
影响行数==>1
2017-08-09 20:33:45.569 DEBUG 11588 --- [ main] c.x.bootdemo.dao.UserDao.selectAll : ==> Parameters:
UserEntity{id=2, name='王五', createTime=null}
UserEntity{id=3, name='李四', createTime=null}
当直接updateByIdTest方法后可以看到 怎么王五的createTime是null呢
是因为数据库中是create_time跟createTime不匹配造成的
需要增加 map-underscore-to-camel-case: true
# Mybatis配置
mybatis:
mapperLocations: classpath:mapper/**/*.xml
typeAliasesPackage: com.xiaohan.bootdemo.entity
#config-location: classpath:mybatis.xml
configuration:
map-underscore-to-camel-case: true #使用驼峰法映射属性
之后就能看到时间了
UserEntity{id=2, name='王五', createTime=Wed Aug 09 20:42:44 CST 2017}
UserEntity{id=3, name='李四', createTime=null}
