超市订单管理系统

超市订单管理系统

原文地址: https://www.ncucoder.com/2019/07/04/supermarket-orders-management-system/
GitHub Repo: https://github.com/hsowan/soms
SOMS
Supermarket Orders M…

原文地址: https://www.ncucoder.com/2019/07/04/supermarket-orders-management-system/

GitHub Repo: https://github.com/hsowan/soms

SOMS

Supermarket Orders Management System 超市订单管理系统

Day 01

使用MyBatis

MyBatis 官方文档

Refer: http://www.mybatis.org/mybatis-3/zh/index.html

类型别名

类型别名是你的好帮手。使用它们,你就可以不用输入类的完全限定名称了。比如:

<!-- 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: https://logging.apache.org/log4j/2.x/manual/configuration.html#Properties

Eclipse for Mac 快捷键

删除一行

Command + D

Refer: https://www.cnblogs.com/TankXiao/p/4018219.html

格式化代码

Shift + Command + F

Day 02

MyBatis 核心配置

mybatis-config.xml 配置

  • configuration(配置)
    • properties(属性)
    • settings(设置)
    • typeAliases(类型别名)
    • typeHandlers(类型处理器)
    • objectFactory(对象工厂)
    • plugins(插件)
    • environments(环境配置)
      • environment(环境变量)
        • transactionManager(事务管理器)
        • dataSource(数据源)
    • databaseIdProvider(数据库厂商标识)
    • mappers(映射器)

Refer: http://www.mybatis.org/mybatis-3/zh/configuration.html

mapper.xml 配置

  • insert – 映射插入语句
  • update – 映射更新语句
  • delete – 映射删除语句
  • select – 映射查询语句
  • resultMap – 是最复杂也是最强大的元素,用来描述如何从数据库结果集中来加载对象。
  • sql – 可被其他语句引用的可重用语句块。
  • cache – 对给定命名空间的缓存配置。
  • cache-ref – 对其他命名空间缓存配置的引用。

Refer: http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html

使用单例模式创建SqlSessionFactory


private static String config = "mybatis-config.xml";
private static SqlSessionFactory sqlSessionFactory;


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: http://maven.apache.org/surefire/maven-surefire-plugin/examples/junit.html

Skipping Tests

mvn install -DskipTests

Refer: http://maven.apache.org/surefire/maven-surefire-plugin/examples/skipping-tests.html

JSP 九大内置对象+四大域对象

Refer: https://my.oschina.net/u/3805464/blog/1813805

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

http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html

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 params = new HashMap();
params.put("username", "%user%");

List users = session.selectList("com.ncucoder.mapper.UserMapper.listByUsername", params);

执行结果:

==>  Preparing: select id, username, password from user where username like ? 
==> Parameters: %user%(String)

添加单元测试 testListByUsernameUsingLimit:

Map params = new HashMap();
params.put("username", "%user%");
params.put("start", 0);
params.put("count", 2);

List 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=""/>
select id, username, password
from user
where username like #{username}
<if test="start != null and count != null">
limit #{start}, #{count}
</if>
</select>

修改单元测试 testListByUsername():

Map params = new HashMap();
// params.put("username", "%user%");
params.put("username", "user");

List 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 Optimizerpossible_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

官方文档

http://www.mybatis.org/generator/configreference/xmlconfig.html

问题:

  • 改用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>

修改配置文件

<!--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
# SSM

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×