//实体类ProductCategory:
package com.imooc.o2o.entity;
import java.util.Date;
public class ProductCategory {
private Long productCategoryId;
private Long shopId;
private String productCategoryName;
private Integer priority;
private Date createTime;
public Long getProductCategoryId() {
return productCategoryId;
}
public void setProductCategoryId(Long productCategoryId) {
this.productCategoryId = productCategoryId;
}
public Long getShopId() {
return shopId;
}
public void setShopId(Long shopId) {
this.shopId = shopId;
}
public String getProductCategoryName() {
return productCategoryName;
}
public void setProductCategoryName(String productCategoryName) {
this.productCategoryName = productCategoryName;
}
public Integer getPriority() {
return priority;
}
public void setPriority(Integer priority) {
this.priority = priority;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}
//Dao层ProductCategoryDao:
package com.imooc.o2o.dao;
import java.util.List;
import com.imooc.o2o.entity.ProductCategory;
public interface ProductCategoryDao {
/**
* 通过shop id查询店铺商品类别
*/
List<ProductCategory> queryByShopId(Long shopId);
}
<!--mapper中ProductCategoryDao.xml配置-->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.imooc.o2o.dao.ProductCategoryDao">
<!-- 目的：为dao接口方法提供sql语句配置 -->
<select id="queryByShopId" resultType="com.imooc.o2o.entity.ProductCategory"
parameterType="Long">
<!-- 具体的sql -->
SELECT
product_category_id,
product_category_name,
priority,
create_time,
shop_id
FROM
tb_product_category
WHERE
shop_id = #{shopId}
order by priority desc
</select>
</mapper>
<!--mybatis中配置-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 配置全局属性 -->
<settings>
<!-- 使用jdbc的getGeneratedKeys获取数据库自增主键值 -->
<setting name="useGeneratedKeys" value="true"/>
<!-- 使用列标签(数据库中的字段名)替换列别名(查询语句查询中字段的自定义名称) 默认:true -->
<setting name="useColumnLabel" value="true"/>
<!-- 开启驼峰命名转换：Table(create_time)->Entity(createTime) -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<plugins>
<plugin interceptor="com.imooc.o2o.dao.split.DynamicDataSourceInterceptor">
</plugin>
</plugins>
</configuration>
//报错信息
警告: Bad pool size config, start 3 < min 10. Using 10 as start.
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
六月 26, 2021 9:16:33 上午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 2, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 10000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hgerr5aije2yry1iaoufj|44fa4d96, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hgerr5aije2yry1iaoufj|44fa4d96, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://192.168.1.103:3306/o2o?useUnicode=true&characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 30, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 10, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
六月 26, 2021 9:16:33 上午 com.mchange.v2.resourcepool.BasicResourcePool
警告: Bad pool size config, start 3 < min 10. Using 10 as start.
六月 26, 2021 9:16:43 上午 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet [spring-dispatcher] in context with path [/o2o] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException:
### Error querying database. Cause: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
### The error may exist in file [E:\tomcat\apache-tomcat-7.0.90\webapps\o2o\WEB-INF\classes\mapper\ProductCategoryDao.xml]
### The error may involve com.imooc.o2o.dao.ProductCategoryDao.queryByShopId
### The error occurred while executing a query
### SQL: SELECT product_category_id, product_category_name, priority, create_time, shop_id FROM tb_product_category WHERE shop_id = ? order by priority desc
### Cause: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
; uncategorized SQLException; SQL state [null]; error code [0]; An attempt by a client to checkout a Connection has timed out.; nested exception is java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.] with root cause
com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@561d68a4 -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1505)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:194)
at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.getTargetConnection(LazyConnectionDataSourceProxy.java:412)
at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.invoke(LazyConnectionDataSourceProxy.java:385)
at com.sun.proxy.$Proxy40.prepareStatement(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
at com.imooc.o2o.dao.split.DynamicDataSourceInterceptor.intercept(DynamicDataSourceInterceptor.java:55)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
at com.sun.proxy.$Proxy39.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
at com.sun.proxy.$Proxy24.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
at com.sun.proxy.$Proxy27.queryByShopId(Unknown Source)
at com.imooc.o2o.service.impl.ProductCategoryServiceImpl.getProductCategoryList(ProductCategoryServiceImpl.java:20)
at com.imooc.o2o.web.shopadmin.ProductCategoryManagementController.getProductCategoryList(ProductCategoryManagementController.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:494)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:1025)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1136)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
tb_shop表：
CREATE TABLE `tb_shop`(
`shop_id` INT(10) NOT NULL AUTO_INCREMENT,
`owner_id` INT(10) NOT NULL COMMENT '店铺创建人',
`area_id` INT(5) DEFAULT NULL,
`shop_category_id` INT(11) DEFAULT NULL,
`shop_name` VARCHAR(256) NOT NULL,
`shop_desc` VARCHAR(1024) DEFAULT NULL,
`shop_addr` VARCHAR(200) DEFAULT NULL,
`phone` VARCHAR(128) DEFAULT NULL,
`shop_img` VARCHAR(1024) DEFAULT NULL,
`priority` INT(3) DEFAULT '0',
`create_time` DATETIME DEFAULT NULL,
`last_edit_time` DATETIME DEFAULT NULL,
`enable_status` INT(2) NOT NULL DEFAULT '0',
`advice` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`shop_id`),
CONSTRAINT `fk_shop_area` FOREIGN KEY(`area_id`) REFERENCES `tb_area` (`area_id`),
CONSTRAINT `fk_shop_profile` FOREIGN KEY (`owner_id`) REFERENCES `tb_person_info` (`user_id`),
CONSTRAINT `fk_shop_shopcate` FOREIGN KEY (`shop_category_id`) REFERENCES `tb_shop_category` (`shop_category_id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
tb_product_category表：
CREATE TABLE `tb_product_category` (
`product_category_id` INT(11) NOT NULL AUTO_INCREMENT,
`product_category_name` VARCHAR(100) NOT NULL,
`priority` INT(2) DEFAULT '0',
`create_time` DATETIME DEFAULT NULL,
`shop_id` INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`product_category_id`),
KEY `fk_procate_shop` (`shop_id`),
CONSTRAINT `fk_procate_shop` FOREIGN KEY (`shop_id`) REFERENCES `tb_shop` (`shop_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
