综合:pom文件中加入
jpa,druid,jdbc,mybatis,mysql地址坐标
congfig中配置druid与filter,同时换药配置dataresource(数据源不能少,版本不同配置的方法不同)
开发工具:
MySQL,Tomcat3.3.9,springboot2.0.1
pom
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>cn.vp</groupId> <artifactId>ssb08</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>ssb08</name> <description>Demo project for Spring Boot</description> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.1.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <!-- https://mvnrepository.com/artifact/com.alibaba/druid --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.9</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project> |
1.实体类:
1.1Department.java
package cn.vp.entity; public class Department { private Integer id; private String departmentName; public Department() { } public Department(int i, String string) { this.id = i; this.departmentName = string; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getDepartmentName() { return departmentName; } public void setDepartmentName(String departmentName) { this.departmentName = departmentName; } @Override public String toString() { return "Department [id=" + id + ", departmentName=" + departmentName + "]"; } } |
1.2Employee.java
package cn.vp.entity; import java.util.Date; public class Employee { private Integer id; private String lastName; private String email; //1 male, 0 female private Integer gender; private Department department; private Date birth; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Integer getGender() { return gender; } public void setGender(Integer gender) { this.gender = gender; } public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } public Date getBirth() { return birth; } public void setBirth(Date birth) { this.birth = birth; } public Employee(Integer id, String lastName, String email, Integer gender, Department department) { super(); this.id = id; this.lastName = lastName; this.email = email; this.gender = gender; this.department = department; this.birth = new Date(); } public Employee() { } @Override public String toString() { return "Employee{" + "id=" + id + ", lastName='" + lastName + '\'' + ", email='" + email + '\'' + ", gender=" + gender + ", department=" + department + ", birth=" + birth + '}'; } } |
2.dao
DepartmentDao.java
package cn.vp.dao; import cn.vp.entity.Department; import org.apache.ibatis.annotations.*; @Mapper public interface DepartmentDao { @Insert("INSERT INTO `Department` (`departmentName`) VALUES (#{departmentName})") public int addDepartment(Department department); @Delete("DELETE FROM department where id=#{id}") public int deleteById(Integer id); @Update("UPDATE `Department` SET `departmentName`=#{departmentName} WHERE (`id`=#{id})") public int updateDepartment(Department department); @Select("SELECT * from department where id=#{id}") public Department queryById(@Param("id") Integer id); } |
3.config
DataSourcesConfig.java
package cn.vp.config; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.support.http.StatViewServlet; import com.alibaba.druid.support.http.WebStatFilter; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.web.servlet.FilterRegistrationBean; import org.springframework.boot.web.servlet.ServletRegistrationBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import javax.sql.DataSource; import java.sql.SQLException; import java.util.Arrays; import java.util.HashMap; import java.util.Map; @Configuration public class DataSourcesConfig { 以下是Springboot2.0.1版本的配置 private static final String DB_PREFIX = "spring.datasource"; /** * 配置druid后台管理的servlet * * @return */ @Bean public ServletRegistrationBean druidServlet() { ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); Map<String, String> initParams = new HashMap<>(); initParams.put("loginUsername", "admin"); initParams.put("loginPassword", "admin"); initParams.put("allow", "");//默认就是允许所有访问 initParams.put("deny", "192.168.15.21");//禁止访问的ip bean.setInitParameters(initParams); return bean; } /** * 配置druid后台管理的filter * * @return */ @Bean public FilterRegistrationBean druidFilter() { FilterRegistrationBean bean = new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); Map<String, String> initParams = new HashMap<>(); //设置不拦截的路径 *.cs *.js /druid/* initParams.put("exclusions","*.js,*.css,/druid/*"); bean.setInitParameters(initParams); //设置filter拦截 那些请求 bean.setUrlPatterns(Arrays.asList("/*")); return bean; } //解决 spring.datasource.filters=stat,wall,log4j 无法正常注册进去 @ConfigurationProperties(prefix = DB_PREFIX) class IDataSourceProperties { private String url; private String username; private String password; private String driverClassName; private int initialSize; private int minIdle; private int maxActive; private int maxWait; private int timeBetweenEvictionRunsMillis; private int minEvictableIdleTimeMillis; private String validationQuery; private boolean testWhileIdle; private boolean testOnBorrow; private boolean testOnReturn; private boolean poolPreparedStatements; private int maxPoolPreparedStatementPerConnectionSize; private boolean useGlobalDataSourceStat; private String filters; private String connectionProperties; @Bean //声明其为Bean实例 @Primary //在同样的DataSource中,首先使用被标注的DataSource public DataSource dataSource() { DruidDataSource datasource = new DruidDataSource(); datasource.setUrl(url); datasource.setUsername(username); datasource.setPassword(password); datasource.setDriverClassName(driverClassName); //configuration datasource.setInitialSize(initialSize); datasource.setMinIdle(minIdle); datasource.setMaxActive(maxActive); datasource.setMaxWait(maxWait); datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); datasource.setValidationQuery(validationQuery); datasource.setTestWhileIdle(testWhileIdle); datasource.setTestOnBorrow(testOnBorrow); datasource.setTestOnReturn(testOnReturn); datasource.setPoolPreparedStatements(poolPreparedStatements); datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat); datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize); try { datasource.setFilters(filters); } catch (SQLException e) { System.err.println("druid configuration initialization filter: " + e); } datasource.setConnectionProperties(connectionProperties); return datasource; } public boolean isUseGlobalDataSourceStat() { return useGlobalDataSourceStat; } public void setUseGlobalDataSourceStat(boolean useGlobalDataSourceStat) { this.useGlobalDataSourceStat = useGlobalDataSourceStat; } public String getUrl() { return url; } public void setUrl(String url) { this.url = url; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getDriverClassName() { return driverClassName; } public void setDriverClassName(String driverClassName) { this.driverClassName = driverClassName; } public int getInitialSize() { return initialSize; } public void setInitialSize(int initialSize) { this.initialSize = initialSize; } public int getMinIdle() { return minIdle; } public void setMinIdle(int minIdle) { this.minIdle = minIdle; } public int getMaxActive() { return maxActive; } public void setMaxActive(int maxActive) { this.maxActive = maxActive; } public int getMaxWait() { return maxWait; } public void setMaxWait(int maxWait) { this.maxWait = maxWait; } public int getTimeBetweenEvictionRunsMillis() { return timeBetweenEvictionRunsMillis; } public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) { this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis; } public int getMinEvictableIdleTimeMillis() { return minEvictableIdleTimeMillis; } public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) { this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis; } public String getValidationQuery() { return validationQuery; } public void setValidationQuery(String validationQuery) { this.validationQuery = validationQuery; } public boolean isTestWhileIdle() { return testWhileIdle; } public void setTestWhileIdle(boolean testWhileIdle) { this.testWhileIdle = testWhileIdle; } public boolean isTestOnBorrow() { return testOnBorrow; } public void setTestOnBorrow(boolean testOnBorrow) { this.testOnBorrow = testOnBorrow; } public boolean isTestOnReturn() { return testOnReturn; } public void setTestOnReturn(boolean testOnReturn) { this.testOnReturn = testOnReturn; } public boolean isPoolPreparedStatements() { return poolPreparedStatements; } public void setPoolPreparedStatements(boolean poolPreparedStatements) { this.poolPreparedStatements = poolPreparedStatements; } public int getMaxPoolPreparedStatementPerConnectionSize() { return maxPoolPreparedStatementPerConnectionSize; } public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) { this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize; } public String getFilters() { return filters; } public void setFilters(String filters) { this.filters = filters; } public String getConnectionProperties() { return connectionProperties; } public void setConnectionProperties(String connectionProperties) { this.connectionProperties = connectionProperties; } } -------------------------------------------以下是springboot1.5.-版本的 @Bean @ConfigurationProperties(prefix = "spring.datasource") public DataSource getDataSources() { return new DruidDataSource(); } /** * 配置druid后台管理的servlet * * @return */ @Bean public ServletRegistrationBean druidServlet() { ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); Map<String, String> initParams = new HashMap<>(); initParams.put("loginUsername", "admin"); initParams.put("loginPassword", "admin"); initParams.put("allow", "");//默认就是允许所有访问 initParams.put("deny", "192.168.15.21");//禁止访问的ip bean.setInitParameters(initParams); return bean; } /** * 配置druid后台管理的filter * * @return */ @Bean public FilterRegistrationBean druidFilter() { FilterRegistrationBean bean = new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); Map<String, String> initParams = new HashMap<>(); //设置不拦截的路径 *.cs *.js /druid/* initParams.put("exclusions","*.js,*.css,/druid/*"); bean.setInitParameters(initParams); //设置filter拦截 那些请求 bean.setUrlPatterns(Arrays.asList("/*")); return bean; } } |
MyBatisConfig.java
package cn.vp.config; import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; @Configuration public class MyBatisConfig { @Bean public ConfigurationCustomizer configurationCustomizer() { return new ConfigurationCustomizer() { @Override public void customize(org.apache.ibatis.session.Configuration configuration) { //-自动使用驼峰命名属性映射字段 userId user_id configuration.setMapUnderscoreToCamelCase(true); //使用列别名替换列名 select user as User configuration.setUseColumnLabel(true); } }; } } |
Ssb08Application.java
package cn.vp; import cn.vp.dao.DepartmentDao; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.CommandLineRunner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @MapperScan(value = "cn.vp.dao") @SpringBootApplication public class Ssb08Application implements CommandLineRunner { private DepartmentDao departmentDao; public Ssb08Application (DepartmentDao departmentDao) { this.departmentDao = departmentDao; } public static void main(String[] args) { SpringApplication.run(Ssb08Application.class, args); } @Override public void run(String... args) throws Exception { System.out.println(departmentDao.queryById(1)); } } |
resources
application.yml
spring: datasource: username: root password: root url: jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull driver-class-name: com.mysql.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource # 数据源其他配置 initialSize: 5 minIdle: 5 maxActive: 20 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 FROM DUAL testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙 filters: stat,wall,slf4j maxPoolPreparedStatementPerConnectionSize: 20 useGlobalDataSourceStat: true #配置 connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500 |
test
Ssb08ApplicationTests。java
package cn.vp; 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 javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; @RunWith(SpringRunner.class) @SpringBootTest public class Ssb08ApplicationTests { @Autowired private DataSource dataSource; @Test public void contextLoads() throws SQLException { System.out.println(dataSource.getClass()); Connection connection = null; try { connection = dataSource.getConnection(); System.err.println("---获取一个连接--"); } catch (SQLException e) { e.printStackTrace(); } finally { if (connection != null) connection.close(); } } } |
dao
DepartmentDaoTest.java
package cn.vp.dao; import cn.vp.entity.Department; 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 static org.junit.Assert.*; @RunWith(SpringRunner.class) @SpringBootTest public class DepartmentDaoTest { @Autowired private DepartmentDao departmentDao; @Test public void queryById() { Department department = departmentDao.queryById(1); System.out.println(department); } } |