在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>
配置數(shù)據(jù)源 在application.yml添加如下配置 并修改url連接的數(shù)據(jù)庫 用戶名 和 密碼
spring:
# 數(shù)據(jù)源配置
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 #初始化時(shí)建立物理連接的個(gè)數(shù)祭务。初始化發(fā)生在顯示調(diào)用init方法，或者第一次getConnection時(shí)
max-active: 100 #最大連接池?cái)?shù)量
min-idle: 10 #最小連接池?cái)?shù)量
max-wait: 60000 #獲取連接時(shí)最大等待時(shí)間怪嫌，單位毫秒义锥。
pool-prepared-statements: true #是否緩存preparedStatement，也就是PSCache
max-open-prepared-statements: 100 #要啟用PSCache岩灭，必須配置大于0拌倍，當(dāng)大于0時(shí)，poolPreparedStatements自動(dòng)觸發(fā)修改為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 #驗(yàn)證連接有效性
test-while-idle: true #建議配置為true柱恤，不影響性能数初，并且保證安全性。
test-on-borrow: false #申請連接時(shí)執(zhí)行validationQuery檢測連接是否有效梗顺，做了這個(gè)配置會(huì)降低性能泡孩。
test-on-return: false #歸還連接時(shí)執(zhí)行validationQuery檢測連接是否有效，做了這個(gè)配置會(huì)降低性能
stat-view-servlet: #內(nèi)置監(jiān)控
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 #是否允許一次執(zhí)行多條語句寺谤，缺省關(guān)閉
在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 為實(shí)體類型起別名
接下來別名包下新建一個(gè)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類與數(shù)據(jù)庫中的t_user表對應(yīng)
image.png
接下來新建一個(gè)接口 在該接口中編寫 對表進(jìn)行增刪查改的方法
要注意接口上的@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>
編寫測試類進(jìn)行測試
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("影響行數(shù)==>" + insert);
System.err.println("id==>" + id);
}
}
輸出如下
影響行數(shù)==>1
id==>null
可以看到id的值為null 要得到id的值還需要以下兩步
- 數(shù)據(jù)庫的id設(shè)為自增長
- 在方法上添加注解 @Options
@Insert({"insert into t_user (name,create_time) values (#{name},#{createTime})"})
@Options(useGeneratedKeys = true, keyProperty = "id")
int insert(UserEntity userEntity);
重新運(yùn)行測試類
影響行數(shù)==>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("影響行數(shù)==>" + 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("影響行數(shù)==>" + 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("影響行數(shù)==>" + i);
selectAllTest();
}
}
影響行數(shù)==>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}
當(dāng)直接updateByIdTest方法后可以看到 怎么王五的createTime是null呢
是因?yàn)閿?shù)據(jù)庫中是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 #使用駝峰法映射屬性
之后就能看到時(shí)間了
UserEntity{id=2, name='王五', createTime=Wed Aug 09 20:42:44 CST 2017}
UserEntity{id=3, name='李四', createTime=null}
