超市订单管理系统
GitHub: github.com/hsowan/soms
SOMS
Supermarket Orders Management System 超市订单管理系统
Day 01
使用MyBatis
MyBatis 官方文档
Refer: www.mybatis.org/mybatis-3/z…
类型别名
类型别名是你的好帮手。使用它们,你就可以不用输入类的完全限定名称了。比如:
<!-- mybatis-config.xml 中 -->
<typeAlias type="com.someapp.model.User" alias="User"/>
<!-- SQL 映射 XML 中 -->
<select id="selectUsers" resultType="User">
select id, username, hashedPassword
from some_table
where id = #{id}
</select>
log4j2
Refer: logging.apache.org/log4j/2.x/m…
Eclipse for Mac 快捷键
删除一行
Command + D
Refer: www.cnblogs.com/TankXiao/p/…
格式化代码
Shift + Command + F
Day 02
MyBatis 核心配置
mybatis-config.xml 配置
- configuration(配置)
- properties(属性)
- settings(设置)
- typeAliases(类型别名)
- typeHandlers(类型处理器)
- objectFactory(对象工厂)
- plugins(插件)
- environments(环境配置)
- environment(环境变量)
- transactionManager(事务管理器)
- dataSource(数据源)
- environment(环境变量)
- databaseIdProvider(数据库厂商标识)
- mappers(映射器)
Refer: www.mybatis.org/mybatis-3/z…
mapper.xml 配置
- insert – 映射插入语句
- update – 映射更新语句
- delete – 映射删除语句
- select – 映射查询语句
- resultMap – 是最复杂也是最强大的元素,用来描述如何从数据库结果集中来加载对象。
- sql – 可被其他语句引用的可重用语句块。
- cache – 对给定命名空间的缓存配置。
- cache-ref – 对其他命名空间缓存配置的引用。
Refer: www.mybatis.org/mybatis-3/z…
使用单例模式创建SqlSessionFactory
/**
* 主配置文件路径
*/
private static String config = "mybatis-config.xml";
private static SqlSessionFactory sqlSessionFactory;
/**
* 使用单例模式获取SqlSessionFactory
* @return
*/
private static synchronized SqlSessionFactory getSqlSessionFactory() {
if (sqlSessionFactory == null) {
try {
// 创建输入流读取配置文件
InputStream is = Resources.getResourceAsStream(config);
// 创建SQLSession工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return sqlSessionFactory;
}
Maven
Using JUnit
添加下面的插件到pom.xml
中:
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>3.0.0-M3</version>
</plugin>
执行单元测试: mvn test
Refer: maven.apache.org/surefire/ma…
Skipping Tests
mvn install -DskipTests
Refer: maven.apache.org/surefire/ma…
JSP 九大内置对象+四大域对象
Refer: my.oschina.net/u/3805464/b…
Java 八大基本类型
byte/8
char/16
short/16
int/32
float/32
long/64
double/64
boolean/~
Day 03
动态SQL
官方文档
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
- bind
if
改写UserMapper.xml:
<select id="listByUsername" resultMap="userResultMap">
select id, username, password
from user
where username like #{username}
<!-- 分页 -->
<if test="start != null and count != null">
limit #{start}, #{count}
</if>
</select>
改写单元测试 testListByUsername(使用map进行传参):
Map<String, Object> params = new HashMap<String, Object>();
params.put("username", "%user%");
List<User> users = session.selectList("com.ncucoder.mapper.UserMapper.listByUsername", params);
执行结果:
==> Preparing: select id, username, password from user where username like ?
==> Parameters: %user%(String)
添加单元测试 testListByUsernameUsingLimit:
Map<String, Object> params = new HashMap<String, Object>();
params.put("username", "%user%");
params.put("start", 0);
params.put("count", 2);
List<User> users = session.selectList("com.ncucoder.mapper.UserMapper.listByUsername", params);
执行结果:
==> Preparing: select id, username, password from user where username like ? limit ?, ?
==> Parameters: %user%(String), 0(Integer), 2(Integer)
bind 模糊查询
修改UserMapper.xml
:
<!-- 根据username模糊查询 -->
<select id="listByUsername" resultMap="userResultMap">
<bind name="username" value="'%' + _parameter.get('username') + '%'"/>
select id, username, password
from user
where username like #{username}
<if test="start != null and count != null">
limit #{start}, #{count}
</if>
</select>
修改单元测试 testListByUsername():
Map<String, Object> params = new HashMap<String, Object>();
// params.put("username", "%user%");
params.put("username", "user");
List<User> users = session.selectList("com.ncucoder.mapper.UserMapper.listByUsername", params);
执行结果:
==> Preparing: select id, username, password from user where username like ?
==> Parameters: %user%(String)
SQL优化 in
查询user表中id小于5的记录
使用in
的查询执行计划(QEP):
mysql> explain select id, username, password from user where id in (1, 2, 3, 4, 5);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
使用<
的QEP:
mysql> explain select id, username, password from user where id < 6;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
使用between
的QEP:
mysql> explain select id, username, password from user where id between 1 and 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
type
表示对表所使用的访问方式(最优到最差: NULL > system > const > eq_ref > ref > range > index > All)key
表示MySQL Query Optimizer
从possible_keys
中所选择使用的索引rows
表示MySQL Query Optimizer
通过系统收集到的统计信息估算出来的结果集记录条数, 简单理解为查询需要扫描的记录数
分析: 使用in
作为查询条件时没有使用索引而进行了全表扫描(ALL),
使用<
和between
作为查询条件时使用到主键索引以及索引范围扫描, 所以三者比较in
的执行效率是最低的
MySQL 批量插入测试数据
-- 获取指定长度的随机字符串
drop function if exists `rand_string`;
create function `rand_string`(n int) returns varchar(255) charset 'utf8'
begin
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str varchar(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 62), 1));
SET i = i + 1;
END WHILE;
RETURN return_str;
end;
-- 插入指定数量的用户
drop procedure if exists `create_users`;
create procedure `create_users`(n int)
begin
declare i int default 0;
while (i < n) do
insert into user(username, password)
values (rand_string(10), rand_string(32));
set i = i + 1;
end while;
end;
-- 调用存储过程
call create_users(1000);
Day 04
MyBatis Generator
官方文档
问题:
- 改用MySQL8时, 与其他数据库(mysql库)表名相同时出现错误
添加依赖和插件
mybatis-generator-core:
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>${mybatis-generator.version}</version>
</dependency>
mybatis-generator-maven-plugin:
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>${mybatis-generator.version}</version>
<configuration>
<overwrite>true</overwrite>
<verbose>true</verbose>
</configuration>
</plugin>
添加配置文件
生成对应的映射文件、实体类和接口类
cd ${your_project}
mvn mybatis-generator:generator
Refer:
集成SpringMVC
添加依赖
<!-- Spring相关依赖 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>${mybatis-spring.version}</version>
</dependency>
<!-- Druid连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
修改web.xml
配置文件
<!--1.创建dispatcher -->
<servlet>
<servlet-name>soms-dispatcher</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:spring/spring-*.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
<!-- 支持异步 -->
<async-supported>true</async-supported>
</servlet>
<servlet-mapping>
<servlet-name>soms-dispatcher</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
<!--中文乱码 -->
<filter>
<filter-name>encodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
<init-param>
<param-name>forceEncoding</param-name>
<param-value>true</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>encodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
添加Spring的配置文件
使用MySQL8
修改依赖版本(pom.xml):
<mysql-connector-java.version>8.0.16</mysql-connector-java.version>
修改驱动路径(db.properties):
driver=com.mysql.cj.jdbc.Driver
Day 05
JWT + SpringSecurity
转载自:https://juejin.cn/post/7056040075947147295