一、使用 PowerDesigner 工具
-
1. 新建模型 PDM（物理数据模型）
- 将该模型的数据库设置为 MySQL
- 新增三张表，Student（学生）， Subject（课程）， Score（成绩）
- 其中 Student 和 Subject 是主表
- Score 分别是 Student 和 Subject 的从表，某个成绩一定是某个学生某门课程的成绩
- 设置外键时，直接使用 referecnes 拉线就可以完成，从从表拉向主表
- 设置自增时，将 identity 勾选
- 选择生成数据库脚本 DDL 以及测试数据脚本 DML ，在对应的 MySQL 数据库中执行，生成数据库以及测试数据
- 这样就生成带有各种外键约束的，非空的，唯一的，自增的，主键的表的结构
- PowerDesigner.png
- DDL.png
- DML.png
- PDM.png
-
2. 将生成好的 PDM 转换为 CDM（概念数据模型）
- 之前的三张表 table 变成三个 Entity 实体
- 而且实体之间也有正确的对应关系，一对一，一对多，多对一，多对多
-
3. 还可以将 PDM 生成 OOM（面向对象模型）
- 之前的三张表变成了三个 class 文件
- 而且类与类之间的关系也自动生成好了
- 对一用对象，对多用数组
- 该类要进行使用时，可能要根据业务做具体的调整
-
4. 还可以将 PDM 转换为 xml 的约束
- 之前的三个表变成了三个 xsd 文件
- 在各自的 xsd 文件中指定了 xml 文件的约束
二、操作mysql
- 将第一步生成好的 DDL 在 MySQL 中执行生成对应的数据库
- 将测试数据的 DML 脚本在数据库中执行，生成对应的测试数据
- 注意一些运维的细节
- 注意 MySQL 数据库以及每张数据表的编码格式
- sql 文件
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `scid` int(11) NOT NULL AUTO_INCREMENT, `sid` int(11) NULL DEFAULT NULL, `suid` int(11) NULL DEFAULT NULL, `score` decimal(10, 2) NOT NULL, `sdesc` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`scid`) USING BTREE, INDEX `FK_sc_c`(`sid`) USING BTREE, INDEX `FK_sc_su`(`suid`) USING BTREE, CONSTRAINT `FK_sc_c` FOREIGN KEY (`sid`) REFERENCES `student` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `FK_sc_su` FOREIGN KEY (`suid`) REFERENCES `subject` (`suid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES (1, 1, 1, 100.00, 'AA'); INSERT INTO `score` VALUES (2, 2, 2, 90.00, 'BB'); INSERT INTO `score` VALUES (3, 3, 3, 100.00, 'CC'); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `pass` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `tel` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `addr` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`sid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES (1, 'AAA', '111111', '11111111111', 'AA', 17); INSERT INTO `student` VALUES (2, 'BBB', '111111', '11111111111', 'BB', 13); INSERT INTO `student` VALUES (3, 'CCC', '222222', '11111111111', 'CC', 11); -- ---------------------------- -- Table structure for subject -- ---------------------------- DROP TABLE IF EXISTS `subject`; CREATE TABLE `subject` ( `suid` int(11) NOT NULL AUTO_INCREMENT, `suname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `degree` int(11) NOT NULL, PRIMARY KEY (`suid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of subject -- ---------------------------- INSERT INTO `subject` VALUES (1, 'AAA', 90); INSERT INTO `subject` VALUES (2, 'BBB', 90); INSERT INTO `subject` VALUES (3, 'CCC', 80); SET FOREIGN_KEY_CHECKS = 1;
三、使用 EasyCode 插件将 MySQL 中的数据表使用逆向工程来生成对应 pojo，dao，xml 方式，甚至是 service 以及 controller
- 首先在 IDEA 中打开 database 界面
- 分别设置数据库、用户名和密码新增一个 MySQL 的链接
- 打开数据库中的schemaS，选择你要逆向生成的表（可以多选）
- 右键 EasyCode --> 点击 Generate Code
- 选择自己的 package（可以提前建好），选择所有 ' all '，可以去掉 debug.json ，选择禁止提示来防止多次提示是否生成子目录
- 正常情况会在刚刚选择的包下分别生成 controller，entity，service 以及 dao 子包以及接口、实现类、xml
- 正常情况，EasyCode会在 resources 下生成 mapper 文件夹以及 dao 的 .xml 文件，注意手动将其配置在与 dao 接口平级的 resources 目录下
- 基本骨架生成成功
- EasyCode.png
- EasyCode1.png
四、搭建 SSM
- 添加依赖
<?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> <groupId>com</groupId> <artifactId>SSM-EasyCode</artifactId> <version>1.0-SNAPSHOT</version> <!-- web项目 --> <packaging>war</packaging> <properties> <!-- 统一管理spring相关产品的版本 --> <spring-version>4.3.9.RELEASE</spring-version> </properties> <dependencies> <!-- spring mvc的依赖 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>${spring-version}</version> </dependency> <!-- rest 风格 --> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> <version>2.8.10</version> </dependency> <!-- mysql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.19</version> </dependency> <!-- mybatis --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.6</version> </dependency> <!-- mybatis spring的插件，将mybatis交给spring来管理 --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version>1.3.1</version> </dependency> <!-- spring的单元测试 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>${spring-version}</version> </dependency> <!-- spring jdbc,包含事务 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring-version}</version> </dependency> <!-- spring aop的面向切面的配置 --> <dependency> <groupId>org.aspectj</groupId> <artifactId>com.springsource.org.aspectj.weaver</artifactId> <version>1.6.8.RELEASE</version> </dependency> <!-- druid 数据源 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.28</version> </dependency> <!-- 日志信息 --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> <!-- junit单元测试 --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> <!-- lombok，特别注意，与maven的tomcat插件冲突时，将scope设置为provided --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.8</version> <scope>provided</scope> </dependency> <!-- jsp --> <dependency> <groupId>javax.servlet.jsp</groupId> <artifactId>jsp-api</artifactId> <version>2.2</version> <scope>provided</scope> </dependency> <!-- servlet --> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <!-- jstl --> <dependency> <groupId>jstl</groupId> <artifactId>jstl</artifactId> <version>1.2</version> </dependency> </dependencies> <build> <!-- 不过滤java下的.xml文件和.properties配置文件 --> <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> <filtering>false</filtering> </resource> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>false</filtering> </resource> </resources> <plugins> <!-- define the project compile level --> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>3.6.1</version> <configuration> <source>1.8</source> <target>1.8</target> </configuration> </plugin> <!-- 添加tomcat插件 --> <plugin> <groupId>org.apache.tomcat.maven</groupId> <artifactId>tomcat7-maven-plugin</artifactId> <version>2.2</version> <configuration> <path>/</path> <port>8081</port> </configuration> </plugin> </plugins> </build> </project> - 修改 web.xml 内容，新增 Spring MVC 和字符编码过滤器的配置
<?xml version="1.0" encoding="UTF-8"?> <web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd" version="4.0"> <!--配置Spring mvc--> <servlet> <servlet-name>dispatcherServlet</servlet-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <init-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:spring-mvc.xml</param-value> </init-param> </servlet> <servlet-mapping> <servlet-name>dispatcherServlet</servlet-name> <url-pattern>/</url-pattern> </servlet-mapping> <!--spring写好的中文过滤器--> <filter> <filter-name>encode</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> </filter> <filter-mapping> <filter-name>encode</filter-name> <url-pattern>/*</url-pattern> </filter-mapping> </web-app> - 在 resources 下新增 SSM 所需的所有的配置文件
- spring-mybatis.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd"> <!-- 引入数据库的配置文件信息 --> <context:property-placeholder location="classpath:db.properties" /> <context:component-scan base-package="com.service" /> <!-- druid数据源 --> <bean id="ds" class="com.alibaba.druid.pool.DruidDataSource"> <property name="url" value="${url}" /> <property name="driverClassName" value="${driver}" /> <property name="username" value="${user}" /> <property name="password" value="${pass}" /> </bean> <!-- 配置SqlSessionFactoryBean对象，将mybatis交给spring来统一管理，获取SqlSession --> <bean id="sf" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="typeAliasesPackage" value="com.pojo" /> <property name="mapperLocations" value="classpath:com/mapper/*Dao.xml" /> <property name="dataSource" ref="ds" /> <property name="configLocation" value="classpath:mybatis-config.xml" /> </bean> <!-- 配置映射扫描配置，分别设置dao包扫描和SqlSessionFactory的指定 --> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer" > <property name="basePackage" value="com.dao" /> <property name="sqlSessionFactoryBeanName" value="sf" /> </bean> <!-- 配置事务管理器 --> <bean id="dtx" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="ds" /> </bean> <!-- 声明事务的实现方式 以这些关键字开头的方法分别设置事务的隔离级别以及出错后的操作--> <tx:advice transaction-manager="dtx" id="tx"> <!-- 声明属性，声明事务规则 --> <tx:attributes> <tx:method name="save*" propagation="REQUIRED" isolation="DEFAULT" rollback-for="Exception"/> <tx:method name="insert*" propagation="REQUIRED" isolation="DEFAULT" rollback-for="Exception"/> <tx:method name="update*" propagation="REQUIRED" isolation="DEFAULT" rollback-for="Exception"/> <tx:method name="delete*" propagation="REQUIRED" isolation="DEFAULT" rollback-for="Exception"/> </tx:attributes> </tx:advice> <!-- 定义切面 --> <aop:config> <aop:pointcut id="mpt" expression="execution(* com.service.*.*(..))" /> <aop:advisor advice-ref="tx" pointcut-ref="mpt" /> </aop:config> </beans> - spring-mvc.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:mvc="http://www.springframework.org/schema/mvc" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd"> <!-- 设置ssm整合项目的注解配置 --> <context:annotation-config /> <!-- 设置包扫描 扫描controller和service包 --> <context:component-scan base-package="com.controller" /> <context:component-scan base-package="com.service" /> <!-- 注解驱动 --> <mvc:annotation-driven /> <!-- 配置默认资源可以访问 --> <mvc:default-servlet-handler /> <!-- 引入spring和mybatis的整合文件 --> <import resource="classpath:spring-mybatis.xml" /> <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <property name="prefix" value="/" /> <property name="suffix" value=".jsp" /> </bean> </beans> - mybatis-config.xml
<?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> <!-- 给当前mybatis项目添加日志功能，该STDOUT_LOGGING值的好处是不用添加第三方jar包就可以有日志的输出 --> <settings> <setting name="logImpl" value="LOG4J"/> </settings> </configuration> - db.properties
driver = com.mysql.cj.jdbc.Driver url = jdbc:mysql://localhost:3307/school?serverTimezone=UTC user = root pass = 123456 maxActive = 50 minIdle = 1 - log4j.properties
# Global logging configuration log4j.rootLogger = ERROR, stdout, F # MyBatis 日志配置 log4j.logger.com = TRACE # 控制台输出 log4j.appender.stdout = org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout = org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern = %6p [%t] - %m%n log4j.appender.F = org.apache.log4j.DailyRollingFileAppender log4j.appender.F.File = myproj.log log4j.appender.F.Append = true log4j.appender.F.Threshold = DEBUG log4j.appender.F.layout = org.apache.log4j.PatternLayout log4j.appender.F.layout.ConversionPattern = %-d{yyyy-MM-dd HH\:mm\:ss}-[%p %F\:%L] %m%n
- spring-mybatis.xml
- 分别搭建 Spring的单元测试
@RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration("classpath:spring-mybatis.xml") public class TestStudentService { @Autowired private StudentService studentService; @Test public void testService(){ Student s = studentService.queryById(1); System.out.println(s); } } - 测试 SSM
INFO [main] - {dataSource-1} inited DEBUG [main] - ==> Preparing: select sid, name, pass, tel, addr, age from school.student where sid = ? DEBUG [main] - ==> Parameters: 1(Integer) TRACE [main] - <== Columns: sid, name, pass, tel, addr, age TRACE [main] - <== Row: 1, AAA, 111111, 11111111111, AA, 17 DEBUG [main] - <== Total: 1 com.entity.Student@34f22f9d INFO [Thread-1] - {dataSource-1} closed - 完成 SSM 的搭建
- SSM 的简单分页测试：打开浏览器输入--》http://localhost:8081/student/selectPage
在selectPage后可以加 ?cp=1&ps=3 （数字可更改）来访问不同页数据@RestController @RequestMapping("student") public class StudentController { @Resource private StudentService studentService; @GetMapping("selectOne/{id}") public Student selectOne(@PathVariable Integer id) { return this.studentService.queryById(id); } @GetMapping("/selectPage") public List<Student> getStudentsByPage(@RequestParam(value = "cp",defaultValue = "1") int currentPage , @RequestParam(value = "ps",defaultValue = "5") int pageSize){ currentPage = currentPage <= 1 ? 1 : currentPage; pageSize = pageSize <= 0 ? 10 : pageSize; return studentService.queryAllByLimit((currentPage - 1) * pageSize,pageSize); } }
