使用idea做开发工具
1、新建项目:
2、pom.xml
<?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>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.ly</groupId>
<artifactId>wxbook</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>wxbook</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
<scope>true</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- servlet依赖. -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
</dependency>
<!-- tomcat的支持.-->
<dependency>
<groupId>org.apache.tomcat.embed</groupId>
<artifactId>tomcat-embed-jasper</artifactId>
<scope>provided</scope>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.21</version>
</dependency>
<!--druid 连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.28</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<fork>true</fork>
</configuration>
</plugin>
</plugins>
</build>
</project>
3、目录结构
4、application.yml
server:
port: 8087
spring:
mvc:
view:
prefix: /WEB-INF/jsp/
suffix: .jsp
datasource:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3306/nearsvr?characterEncoding=UTF-8&allowMultiQueries=true
username: root
password: sa123456
driverClassName: com.mysql.jdbc.Driver
initialSize: 1
minIdle: 3
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 30000
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
#useGlobalDataSourceStat: true
mybatis:
type-aliases-package: com.ly.wxbook.entity
logging:
level:
com.ly.wxbook.mapper: debug
5、连接池配置:
package com.ly.wxbook.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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 org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.sql.SQLException;
@SuppressWarnings("AlibabaRemoveCommentedCode")
@Configuration
public class DruidDBConfig {
private Logger logger = LoggerFactory.getLogger(DruidDBConfig.class);
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driverClassName}")
private String driverClassName;
@Value("${spring.datasource.initialSize}")
private int initialSize;
@Value("${spring.datasource.minIdle}")
private int minIdle;
@Value("${spring.datasource.maxActive}")
private int maxActive;
@Value("${spring.datasource.maxWait}")
private int maxWait;
@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
// @Value("${spring.datasource.validationQuery}")
// private String validationQuery;
@Value("${spring.datasource.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.testOnReturn}")
private boolean testOnReturn;
@Value("${spring.datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.filters}")
private String filters;
@Value("{spring.datasource.connectionProperties}")
private String connectionProperties;
@Bean(initMethod = "init", destroyMethod = "close") //声明其为Bean实例
@Primary //在同样的DataSource中,首先使用被标注的DataSource
public DataSource dataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(this.dbUrl);
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.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
logger.error("druid configuration initialization filter", e);
}
datasource.setConnectionProperties(connectionProperties);
return datasource;
}
@Bean(name = "transactionManager")
@Primary
public DataSourceTransactionManager transactionManager() {
return new DataSourceTransactionManager(dataSource());
}
@Bean(name = "sqlSessionFactory")
@Primary
public SqlSessionFactory ds1SqlSessionFactory(@Qualifier("dataSource") DataSource dataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setTypeAliasesPackage("com.ly.wxbook.entity");
// sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
// .getResources(DatasourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean reg = new ServletRegistrationBean();
reg.setServlet(new StatViewServlet());
reg.addUrlMappings("/druid/*");
reg.addInitParameter("allow", ""); //白名单
return reg;
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
filterRegistrationBean.addInitParameter("profileEnable", "true");
filterRegistrationBean.addInitParameter("principalCookieName","USER_COOKIE");
filterRegistrationBean.addInitParameter("principalSessionName","USER_SESSION");
filterRegistrationBean.addInitParameter("DruidWebStatFilter","/*");
return filterRegistrationBean;
}
}
6、实体类
package com.ly.wxbook.entity;
import java.io.Serializable;
public class ModuleEntity implements Serializable {
private String id;
private String name;
private String iconCls;
private int orderNumber;
private String parentId;
private String urlPath;
public ModuleEntity(){super();}
public ModuleEntity(String id, String name, String iconCls, int orderNumber, String parentId, String urlPath) {
this.id = id;
this.name = name;
this.iconCls = iconCls;
this.orderNumber = orderNumber;
this.parentId = parentId;
this.urlPath = urlPath;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getIconCls() {
return iconCls;
}
public void setIconCls(String iconCls) {
this.iconCls = iconCls;
}
public int getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(int orderNumber) {
this.orderNumber = orderNumber;
}
public String getParentId() {
return parentId;
}
public void setParentId(String parentId) {
this.parentId = parentId;
}
public String getUrlPath() {
return urlPath;
}
public void setUrlPath(String urlPath) {
this.urlPath = urlPath;
}
}
7、mapper类
package com.ly.wxbook.mapper;
import com.ly.wxbook.entity.ModuleEntity;
import com.ly.wxbook.sqlprovider.ModuleProvider;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;
public interface ModuleMapper {
/**
* 返回实体类列表
* @return
*/
@Select("select * from t_modules order by orderNumber")
@Results({
@Result(column = "id",property = "id"),
@Result(column="name",property = "name")
})
List<ModuleEntity> getAll();
/**
* 返回单一实体
* @param id
* @return
*/
@Select("select * from t_modules where id=#{id}")
ModuleEntity getById(String id);
/**
* 使用自定义sql语句生成类进行条件判断的查询
* @param parentId
* @return
*/
@SelectProvider(type =ModuleProvider.class,method = "getLastId")
String getLastId(String parentId);
/**
* 用传参的方式进行插入操作
* @param entity
*/
@Insert("insert into t_modules(id,name,iconCls,orderNumber,parentId,urlPath) values(#{id},#{name},#{iconCls}," +
"#{orderNumber},#{parentId},#{urlPath})")
void insert(ModuleEntity entity);
/**
* 关联查询,返回动态数据集
* @return
*/
@Select("select u.*,r.name rolename,ur.roleid,m.name modulename,rm.moduleId,m.iconCls,m.urlPath from t_sysuser u\n" +
"left join t_sysuser_role ur\n" +
"on u.id=ur.userid\n" +
"left join t_roles r\n" +
"on ur.roleid=r.id\n" +
"left join t_roles_module rm\n" +
"on rm.roleid=r.id\n" +
"left join t_modules m\n" +
"on rm.moduleid=m.id")
List<Map<String,Object>> selectListMap();
/**
* 自定义模糊查询,带分页
* @param title
* @return
*/
@SelectProvider(type= ModuleProvider.class,method = "getTitleSql")
List<Map<String,Object>> listByLike(@Param("title") String title,@Param("rows") int rows,@Param("page") int page);
/**
* 自定义模糊查询统计数量
* @param title
* @return
*/
@SelectProvider(type = ModuleProvider.class,method = "getTitleSqlCount")
int countByLike(String title);
}
8、sqlprovider
package com.ly.wxbook.sqlprovider;
import com.ly.wxbook.entity.ModuleEntity;
import com.ly.wxbook.utils.Func;
import org.apache.ibatis.jdbc.SQL;
public class ModuleProvider {
public String getLastId(String parentId) {
return new SQL() {{
SELECT("max(id)");
FROM("t_modules");
if (parentId != null) {
WHERE("parentId=#{parentId}");
} else {
WHERE("parentId is null");
}
}}.toString();
}
private StringBuilder likeSql(StringBuilder sb, String title) {
if (!(title == null || title.equals(""))) {
sb.append(" where i.title like concat('%',#{title},'%')\n");
}
return sb;
}
public String getTitleSql(String title, int rows, int page) {
StringBuilder sb = new StringBuilder();
sb.append("select a.title atitle,i.* from t_novel a\n" +
"left join t_novel_item i\n" +
"on a.id=i.nid\n");
sb = likeSql(sb, title);
sb.append("order by orderNums\n");
if (rows > 0) {
int beginRow = Func.compuPageBegin(rows, page);
sb.append(String.format(" limit %d,%d", beginRow, rows));
}
return sb.toString();
}
public String getTitleSqlCount(String title) {
StringBuilder sb = new StringBuilder();
sb.append("select count(0) from t_novel_item i\n");
sb = likeSql(sb, title);
return sb.toString();
}
}
9、service
package com.ly.wxbook.service;
import com.ly.wxbook.entity.ModuleEntity;
public interface ModuleService {
String add(ModuleEntity entity);
}
10、impl
package com.ly.wxbook.impl;
import com.ly.wxbook.service.ModuleService;
import com.ly.wxbook.entity.ModuleEntity;
import com.ly.wxbook.mapper.ModuleMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class ModuleImpl implements ModuleService {
@Autowired
ModuleMapper moduleMapper;
@Override
public String add(ModuleEntity entity) {
int initId = 100;
String lastId = moduleMapper.getLastId(entity.getParentId());
if (entity.getParentId() == null) {
if (lastId == null) {
lastId = String.valueOf(initId);
}else{
lastId = String.valueOf(Integer.valueOf(lastId)+1);
}
}else{
if(lastId==null){
lastId = entity.getParentId()+String.valueOf(initId);
}else{
lastId = String.valueOf(Integer.valueOf(lastId)+1);
}
}
entity.setId(lastId);
moduleMapper.insert(entity);
return lastId;
}
}
11、控制器
页面控制器:
package com.ly.wxbook.controller;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
@Controller
public class PageController {
@RequestMapping("/page")
public String index(){
return "index";
}
@RequestMapping("/about")
public String about(){
return "about";
}
}
json控制器:
package com.ly.wxbook.controller;
import com.ly.wxbook.entity.ModuleEntity;
import com.ly.wxbook.mapper.ModuleMapper;
import com.ly.wxbook.service.ModuleService;
import com.ly.wxbook.utils.DataGridJson;
import com.ly.wxbook.utils.EnumState;
import com.ly.wxbook.utils.JsonResultObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
public class JsonController {
@Autowired
private ModuleMapper moduleMapper;
@Autowired
private ModuleService moduleService;
@RequestMapping("/listAllModule")
public List<ModuleEntity> getAllModule() {
return moduleMapper.getAll();
}
@PostMapping("/addmodule")
public JsonResultObject addModule(ModuleEntity entity) {
String id = moduleService.add(entity);
return new JsonResultObject(EnumState.Ok, entity);
}
@PostMapping("/listmap")
public JsonResultObject listMap() {
List<Map<String, Object>> list = moduleMapper.selectListMap();
return new JsonResultObject(EnumState.Ok, list);
}
@PostMapping("/listlike")
public DataGridJson listLike(String title, int rows, int page) {
List<Map<String, Object>> list = moduleMapper.listByLike(title,rows,page);
int total = moduleMapper.countByLike(title);
return new DataGridJson(total,list);
}
}
12、启动类
package com.ly.wxbook;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@EnableTransactionManagement //事务支持
@SpringBootApplication
@MapperScan("com.ly.wxbook.mapper")
public class WxbookApplication {
public static void main(String[] args) {
SpringApplication.run(WxbookApplication.class, args);
}
}
13、运行
第一次时main方法点右键:
运行一次后idea中会生成一个相关的配置,以后再运行时在右上角找到下图所示,点击运行