本文详细介绍了如何在项目中配置Druid数据源，包括依赖导入、自定义数据源参数及监控功能的设置。同时，探讨了解决MyBatis多条件模糊查询时遇到的参数错误问题，并提供了正确的SQL语句写法。
1 导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>
2 配置自定义数据源
#自定义数据源
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
spring.datasource.maxWait=60000
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.filters=stat,wall,log4j
#spring.datasource.filters.commons-log.connection-logger-name=stat,wall,log4j
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.useGlobalDataSourceStat=true
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
3 配置druid监控功能
-
配置监控
@Configuration public class DruidConfig { @ConfigurationProperties(prefix = "spring.datasource") @Bean public DataSource druidDataSource() { return new DruidDataSource(); } //配置 Druid 监控管理后台的Servlet； //内置 Servlet 容器时没有web.xml文件，所以使用 Spring Boot 的注册 Servlet 方式 @Bean public ServletRegistrationBean statViewServlet() { ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); // 这些参数可以在 com.alibaba.druid.support.http.StatViewServlet // 的父类 com.alibaba.druid.support.http.ResourceServlet 中找到 Map<String, String> initParams = new HashMap<>(); initParams.put("loginUsername", "admin"); //后台管理界面的登录账号 initParams.put("loginPassword", "root"); //后台管理界面的登录密码 //后台允许谁可以访问 //initParams.put("allow", "localhost")：表示只有本机可以访问 //initParams.put("allow", "")：为空或者为null时，表示允许所有访问 initParams.put("allow", ""); //deny：Druid 后台拒绝谁访问 //initParams.put("aa", "192.168.1.20");表示禁止此ip访问 //设置初始化参数 bean.setInitParameters(initParams); return bean; } } -
项目启动后访问路劲加’druid’，如
localhost:8080/druid/输入上面设置的用户名和密码，可以进入监控管理页面
4 MyBatis多条件模糊查询报错
- 模糊查询前端界面
-
mapper中sql语句(理论上是这么写的没错)
<select id="queryLikeBook" resultType="Books"> select bookID, bookName,bookCategory, bookCounts, detail from ssm.books <where> <if test="bookName != null and bookName != ''"> bookName like "%#{bookName}%" </if> <if test="bookCategory != null and bookCategory != ''"> and bookCategory like "%#{bookCategory}%" </if> </where> </select>报错
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0). at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1372) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.mysql.cj.jdbc.ClientPreparedStatement.getCoreParameterIndex(ClientPreparedStatement.java:1385) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.mysql.cj.jdbc.ClientPreparedStatement.setString(ClientPreparedStatement.java:1752) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setString(FilterChainImpl.java:3353) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.filter.FilterAdapter.preparedStatement_setString(FilterAdapter.java:1363) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setString(FilterChainImpl.java:3350) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.filter.FilterAdapter.preparedStatement_setString(FilterAdapter.java:1363) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setString(FilterChainImpl.java:3350) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.setString(PreparedStatementProxyImpl.java:611) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.pool.DruidPooledPreparedStatement.setString(DruidPooledPreparedStatement.java:369) ~[druid-1.1.21.jar:1.1.21] at org.apache.ibatis.type.StringTypeHandler.setNonNullParameter(StringTypeHandler.java:31) ~[mybatis-3.5.0.jar:3.5.0] at org.apache.ibatis.type.StringTypeHandler.setNonNullParameter(StringTypeHandler.java:26) ~[mybatis-3.5.0.jar:3.5.0] at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:69) ~[mybatis-3.5.0.jar:3.5.0] at org.apache.ibatis.type.UnknownTypeHandler.setNonNullParameter(UnknownTypeHandler.java:45) ~[mybatis-3.5.0.jar:3.5.0] at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:69) ~[mybatis-3.5.0.jar:3.5.0] at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:87) ~[mybatis-3.5.0.jar:3.5.0] at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:94) ~[mybatis-3.5.0.jar:3.5.0] at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:64) ~[mybatis-3.5.0.jar:3.5.0] at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87) ~[mybatis-3.5.0.jar:3.5.0] at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62) ~[mybatis-3.5.0.jar:3.5.0] ......报错信息貌似跟SQL注入方面的问题有关，如果在关闭sql监控功能的话是不会报错能正常查询的，即配置文件中的这句，但是这样就监控不了sql了
最后将mapper的sql改成这样解决了问题，但具体还是不知道为什么，多条件模糊查询的
#改成$就不会报错了<select id="queryLikeBook" resultType="Books"> select bookID, bookName,bookCategory, bookCounts, detail from ssm.books <where> <if test="bookName != null and bookName != ''"> bookName like "%${bookName}%" </if> <if test="bookCategory != null and bookCategory != ''"> and bookCategory like "%${bookCategory}%" </if> </where> </select>
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
