目录
1、创建数据表
1.1、创建数据库
1.2、创建数据表
这里使用执行 SQL 脚本来导入数据表,各个脚本的 sql 语句如下
tbl_user.sql
/*
Navicat MySQL Data Transfer
Source Server : 192.168.151.2
Source Server Version : 50536
Source Host : 192.168.151.2:3306
Source Database : crm
Target Server Type : MYSQL
Target Server Version : 50536
File Encoding : 65001
Date: 2018-11-27 17:02:13
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tbl_user`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_user`;
CREATE TABLE `tbl_user` (
`id` char(32) NOT NULL COMMENT 'uuid\r\n ',
`login_act` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`login_pwd` varchar(255) DEFAULT NULL COMMENT '密码不能采用明文存储,采用密文,MD5加密之后的数据',
`email` varchar(255) DEFAULT NULL,
`expire_time` char(19) DEFAULT NULL COMMENT '失效时间为空的时候表示永不失效,失效时间为2018-10-10 10:10:10,则表示在该时间之前该账户可用。',
`lock_state` char(1) DEFAULT NULL COMMENT '锁定状态为空时表示启用,为0时表示锁定,为1时表示启用。',
`deptno` char(4) DEFAULT NULL,
`allow_ips` varchar(255) DEFAULT NULL COMMENT '允许访问的IP为空时表示IP地址永不受限,允许访问的IP可以是一个,也可以是多个,当多个IP地址的时候,采用半角逗号分隔。允许IP是192.168.100.2,表示该用户只能在IP地址为192.168.100.2的机器上使用。',
`createTime` char(19) DEFAULT NULL,
`create_by` varchar(255) DEFAULT NULL,
`edit_time` char(19) DEFAULT NULL,
`edit_by` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tbl_user
-- ----------------------------
INSERT INTO `tbl_user` VALUES ('06f5fc056eac41558a964f96daa7f27c', 'ls', '李四', 'yf123', 'ls@163.com', '2018-11-27 21:50:05', '1', 'A001', '192.168.1.1,0:0:0:0:0:0:0:1', '2018-11-22 12:11:40', '李四', null, null);
INSERT INTO `tbl_user` VALUES ('40f6cdea0bd34aceb77492a1656d9fb3', 'zs', '张三', 'yf123', 'zs@qq.com', '2018-11-30 23:50:55', '1', 'A001', '192.168.1.1,192.168.1.2,127.0.0.1,0:0:0:0:0:0:0:1', '2018-11-22 11:37:34', '张三', null, null);
dictionary.sql
/*==============================================================*/
/* DBMS name: MySQL 5.0 */
/* Created on: 2020/3/5 9:12:54 */
/*==============================================================*/
drop table if exists tbl_dic_type;
drop table if exists tbl_dic_value;
/*==============================================================*/
/* Table: tbl_dic_type */
/*==============================================================*/
create table tbl_dic_type
(
code varchar(255) not null comment '编码是主键,不能为空,不能含有中文。',
name varchar(255),
description varchar(255),
primary key (code)
);
/*==============================================================*/
/* Table: tbl_dic_value */
/*==============================================================*/
create table tbl_dic_value
(
id char(32) not null comment '主键,采用UUID',
value varchar(255) comment '不能为空,并且要求同一个字典类型下字典值不能重复,具有唯一性。',
text varchar(255) comment '可以为空',
order_no varchar(255) comment '可以为空,但不为空的时候,要求必须是正整数',
type_code varchar(255) comment '外键',
primary key (id)
);
crm-3_市场活动.sql
drop table if exists tbl_activity;
drop table if exists tbl_activity_remark;
/*==============================================================*/
/* Table: tbl_activity */
/*==============================================================*/
create table tbl_activity
(
id char(32) not null,
owner char(32),
name varchar(255),
start_date char(10),
end_date char(10),
cost varchar(255),
description varchar(255),
create_time char(19),
create_by varchar(255),
edit_time char(19),
edit_by varchar(255),
primary key (id)
);
/*==============================================================*/
/* Table: tbl_activity_remark */
/*==============================================================*/
create table tbl_activity_remark
(
id char(32) not null,
note_content varchar(255),
create_time char(19),
create_by varchar(255),
edit_time char(19),
edit_by varchar(255),
edit_flag char(1) comment '0表示未修改,1表示已修改',
activity_id char(32),
primary key (id)
);
crm-4_线索_客户_联系人_交易.sql
drop table if exists tbl_clue;
drop table if exists tbl_clue_activity_relation;
drop table if exists tbl_clue_remark;
drop table if exists tbl_contacts;
drop table if exists tbl_contacts_activity_relation;
drop table if exists tbl_contacts_remark;
drop table if exists tbl_customer;
drop table if exists tbl_customer_remark;
drop table if exists tbl_tran;
drop table if exists tbl_tran_history;
drop table if exists tbl_tran_remark;
/*==============================================================*/
/* Table: tbl_clue */
/*==============================================================*/
create table tbl_clue
(
id char(32) not null,
fullname varchar(255),
appellation varchar(255),
owner char(32),
company varchar(255),
job varchar(255),
email varchar(255),
phone varchar(255),
website varchar(255),
mphone varchar(255),
state varchar(255),
source varchar(255),
create_by varchar(255),
create_time char(19),
edit_by varchar(255),
edit_time char(19),
description varchar(255),
contact_summary varchar(255),
next_contact_time char(10),
address varchar(255),
primary key (id)
);
/*==============================================================*/
/* Table: tbl_clue_activity_relation */
/*==============================================================*/
create table tbl_clue_activity_relation
(
id char(32) not null,
clue_id char(32),
activity_id char(32),
primary key (id)
);
/*==============================================================*/
/* Table: tbl_clue_remark */
/*==============================================================*/
create table tbl_clue_remark
(
id char(32) not null,
note_content varchar(255),
create_by varchar(255),
create_time char(19),
edit_by varchar(255),
edit_time char(19),
edit_flag char(1),
clue_id char(32),
primary key (id)
);
/*==============================================================*/
/* Table: tbl_contacts */
/*==============================================================*/
create table tbl_contacts
(
id char(32) not null,
owner char(32),
source varchar(255),
customer_id char(32),
fullname varchar(255),
appellation varchar(255),
email varchar(255),
mphone varchar(255),
job varchar(255),
create_by varchar(255),
create_time char(19),
edit_by varchar(255),
edit_time char(19),
description varchar(255),
contact_summary varchar(255),
next_contact_time char(10),
address varchar(255),
primary key (id)
);
/*==============================================================*/
/* Table: tbl_contacts_activity_relation */
/*==============================================================*/
create table tbl_contacts_activity_relation
(
id char(32) not null,
contacts_id char(32),
activity_id char(32),
primary key (id)
);
/*==============================================================*/
/* Table: tbl_contacts_remark */
/*==============================================================*/
create table tbl_contacts_remark
(
id char(32) not null,
note_content varchar(255),
create_by varchar(255),
create_time char(19),
edit_by varchar(255),
edit_time char(19),
edit_flag char(1),
contacts_id char(32),
primary key (id)
);
/*==============================================================*/
/* Table: tbl_customer */
/*==============================================================*/
create table tbl_customer
(
id char(32) not null,
owner char(32),
name varchar(255),
website varchar(255),
phone varchar(255),
create_by varchar(255),
create_time char(19),
edit_by varchar(255),
edit_time char(19),
contact_summary varchar(255),
next_contact_time char(10),
description varchar(255),
address varchar(255),
primary key (id)
);
/*==============================================================*/
/* Table: tbl_customer_remark */
/*==============================================================*/
create table tbl_customer_remark
(
id char(32) not null,
note_content varchar(255),
create_by varchar(255),
create_time char(19),
edit_by varchar(255),
edit_time char(19),
edit_flag char(1),
customer_id char(32),
primary key (id)
);
/*==============================================================*/
/* Table: tbl_tran */
/*==============================================================*/
create table tbl_tran
(
id char(32) not null,
owner char(32),
money varchar(255),
name varchar(255),
expected_date char(10),
customer_id char(32),
stage varchar(255),
type varchar(255),
source varchar(255),
activity_id char(32),
contacts_id char(32),
create_by varchar(255),
create_time char(19),
edit_by varchar(255),
edit_time char(19),
description varchar(255),
contact_summary varchar(255),
next_contact_time char(10),
primary key (id)
);
/*==============================================================*/
/* Table: tbl_tran_history */
/*==============================================================*/
create table tbl_tran_history
(
id char(32) not null,
stage varchar(255),
money varchar(255),
expected_date char(10),
create_time char(19),
create_by varchar(255),
tran_id char(32),
primary key (id)
);
/*==============================================================*/
/* Table: tbl_tran_remark */
/*==============================================================*/
create table tbl_tran_remark
(
id char(32) not null,
note_content varchar(255),
create_by varchar(255),
create_time char(19),
edit_by varchar(255),
edit_time char(19),
edit_flag char(1),
tran_id char(32),
primary key (id)
);
2、搭建开发环境
2.1、创建一个空项目,设置 JDK 为 1.8
创建 Empty Project,作为项目的工作空间,设置 JDK 为 1.8
2.2、创建 Maven 模块
创建 Maven 模块,从原型创建即可
创建完后补全目录结构
2.3、添加依赖
2.3.1、mysql驱动
<!-- MySQL数据库连接驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.43</version>
</dependency>
2.3.2、JDBC数据库连接池:Druid
<!-- JDBC数据源连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.1</version>
</dependency>
2.3.3、MyBatis框架依赖
<!-- MyBatis框架依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.1</version>
</dependency>
2.3.4、Spring相关依赖
<!-- Spring框架依赖的JAR配置 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-oxm</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
2.3.5、SpringAOP依赖
<!-- Spring AOP支持-->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.8.9</version>
</dependency>
2.3.6、MyBatis 与 Spring 整合依赖
<!-- MyBatis与Spring整合依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.0</version>
</dependency>
2.3.7、添加项目对 JSP 的支持
<!-- servlet及jstl标签库依赖的JAR配置 -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>javax.servlet.jsp.jstl</groupId>
<artifactId>jstl-api</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>org.apache.taglibs</groupId>
<artifactId>taglibs-standard-spec</artifactId>
<version>1.2.1</version>
</dependency>
<dependency>
<groupId>org.apache.taglibs</groupId>
<artifactId>taglibs-standard-impl</artifactId>
<version>1.2.1</version>
</dependency>
2.3.8、JackSon 插件依赖
<!-- 加载jackson插件依赖 -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>2.7.3</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.7.3</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-annotations</artifactId>
<version>2.7.3</version>
</dependency>
2.3.9、poi 依赖
<!--poi依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
2.3.10、fileupload 依赖
<!-- 文件上传 -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
2.3.11、log4j 依赖
<!-- Log4j2依赖的JAR配置 -->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>2.3</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.3</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-jcl</artifactId>
<version>2.3</version>
</dependency>
3、添加各种配置
3.1、MyBatis 配置
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>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<package name="com.bjpowernode.crm.model"/>
</typeAliases>
<mappers>
<package name="com.bjpowernode.crm.mapper"/>
</mappers>
</configuration>
3.2、配置数据连接和事务
applicationContext-datasource.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:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
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-4.3.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">
<!-- 配置数据源 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="username" value="root"/>
<property name="password" value="zyj123"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/crm2008?useSSL=false&useUnicode=true&characterEncoding=UTF-8"/>
</bean>
<!-- 配置SqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 必须注入属性dataSource -->
<property name="dataSource" ref="dataSource"/>
<!-- 如果mybatis没有特殊的配置(比如别名等),configLocation可以省去 ;否则,不能省略-->
<property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>
<!-- mapper注解扫描器配置,扫描@MapperScan注解,自动生成代码对象 -->
<bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.bjpowernode.crm.mapper"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
<!-- 配置事务管理器 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 配置事务 -->
<aop:config>
<aop:pointcut expression="execution(* com.bjpowernode.crm..service.*.*(..))" id="allMethodPointcut"/>
<aop:advisor advice-ref="txAdvice" pointcut-ref="allMethodPointcut"/>
</aop:config>
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="add*" propagation="REQUIRED" rollback-for="Exception"/>
<tx:method name="save*" propagation="REQUIRED" rollback-for="Exception"/>
<tx:method name="edit*" propagation="REQUIRED" rollback-for="Exception"/>
<tx:method name="update*" propagation="REQUIRED" rollback-for="Exception"/>
<tx:method name="delete*" propagation="REQUIRED" rollback-for="Exception"/>
<tx:method name="do*" propagation="REQUIRED" rollback-for="Exception"/>
<tx:method name="*" propagation="REQUIRED" read-only="true"/>
</tx:attributes>
</tx:advice>
</beans>
3.3、springmvc 配置
applicationContext-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:p="http://www.springframework.org/schema/p"
xmlns:util="http://www.springframework.org/schema/util"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
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/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
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util.xsd">
<!-- dispatcherServlet截获所有URL请求 -->
<mvc:default-servlet-handler />
<!-- spring mvc 扫描包下的controller -->
<context:component-scan base-package="com.bjpowernode.crm.web.controller"/>
<!-- 配置注解驱动 -->
<mvc:annotation-driven/>
<!-- 配置视图解析器 -->
<bean id="viewResolver"
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<!-- 视图前缀 -->
<property name="prefix" value="/WEB-INF/pages/"/>
<!-- 视图后缀 -->
<property name="suffix" value=".jsp"/>
</bean>
<!-- 配置文件上传解析器 id:必须是multipartResolver-->
<!--<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="maxUploadSize" value="#{1024*1024*80}"/>
<property name="defaultEncoding" value="utf-8"/>
</bean>-->
</beans>
3.4、spring 总配置文件
applicationContext.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:p="http://www.springframework.org/schema/p"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:task="http://www.springframework.org/schema/task"
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:*.properties" />-->
<!-- 扫描注解 -->
<context:component-scan base-package="com.bjpowernode.crm.service"/>
<!-- 导入数据相关配置 -->
<import resource="applicationContext-datasource.xml"/>
</beans>
3.5、web.xml配置
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
id="dataservice" version="3.0">
<display-name>dataservice application</display-name>
<!-- spring监听器加载applicationContext.xml配置文件 -->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:applicationContext.xml</param-value>
</context-param>
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<!-- spring字符过滤器 -->
<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>
</filter>
<filter-mapping>
<filter-name>encodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<!-- Spring mvc分发servlet -->
<servlet>
<servlet-name>dispatcher</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:applicationContext-mvc.xml</param-value>
</init-param>
<!-- 设置服务器一启动就加载该实例 -->
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>dispatcher</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>dispatcher</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>
<!-- 欢迎页,默认进入index controller -->
<welcome-file-list>
<welcome-file>/</welcome-file>
</welcome-file-list>
</web-app>
3.6、设置maven对配置文件的编译选项
maven 默认只对 java 目录下的文件编译,不会编译配置文件
pom.xml,放在 build 标签中
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.*</include>
</includes>
</resource>
</resources>
4、添加页面及静态资源
如下图添加页面及静态资源
5、在 IDEA 添加 运行/调试配置