创建数据库（MySQL）
/*
Navicat MySQL Data Transfer
Source Server : local_mysql
Source Server Version : 50562
Source Host : localhost:3306
Source Database : crm_manager
Target Server Type : MYSQL
Target Server Version : 50562
File Encoding : 65001
Date: 2020-07-28 11:57:36
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`user_name` varchar(15) NOT NULL COMMENT '用户名',
`password` varchar(15) NOT NULL COMMENT '用户密码',
`real_name` varchar(30) NOT NULL COMMENT '真实名称',
`img` varchar(50) DEFAULT NULL COMMENT '用户图像',
`type` int(1) NOT NULL COMMENT '用户类型 1 管理员 2 业务员',
`is_del` int(1) NOT NULL COMMENT '是否有效 1 有效 2 无效',
`create_time` varchar(19) NOT NULL COMMENT '创建时间',
`modify_time` varchar(19) NOT NULL COMMENT '修改时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `user_name_uq` (`user_name`) USING BTREE COMMENT '用户名唯一'
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES ('7', 'abc', 'abc', 'ABC', null, '1', '1', '2020-06-08 11:32:06', '2020-06-08 11:32:06');
INSERT INTO `t_user` VALUES ('8', 'test', '123456', 'TT', null, '2', '2', '2020-06-08 11:32:43', '2020-06-08 11:32:43');
INSERT INTO `t_user` VALUES ('9', 'abc1', '123456', 'aa', null, '2', '2', '2020-06-08 11:32:56', '2020-06-08 11:32:56');
INSERT INTO `t_user` VALUES ('10', 'bbb', '123456', 'b', null, '2', '2', '2020-06-08 11:33:02', '2020-06-08 11:33:02');
INSERT INTO `t_user` VALUES ('11', 'ccc', '123456', 'c', null, '1', '2', '2020-06-08 11:33:08', '2020-06-08 11:33:08');
INSERT INTO `t_user` VALUES ('12', 'hhh', '123456', 'h', null, '2', '2', '2020-06-08 11:33:12', '2020-06-08 11:33:12');
INSERT INTO `t_user` VALUES ('13', 'ggg', '123456', 'g', null, '2', '1', '2020-06-08 11:33:18', '2020-06-08 11:33:18');
INSERT INTO `t_user` VALUES ('14', 'dafs', '123456', 'dd', null, '2', '2', '2020-06-08 11:33:23', '2020-06-08 11:33:23');
INSERT INTO `t_user` VALUES ('15', 'fdsaf', '123456', 'fds', null, '2', '2', '2020-06-08 11:33:27', '2020-06-08 11:33:27');
INSERT INTO `t_user` VALUES ('16', 'kk', '123456', 'k', null, '2', '1', '2020-06-08 11:33:37', '2020-06-08 11:33:37');
INSERT INTO `t_user` VALUES ('17', 'ooo', '123456', 'o', null, '2', '1', '2020-06-08 11:33:48', '2020-06-08 11:33:48');
INSERT INTO `t_user` VALUES ('18', 'ttt', '123456', 't', null, '2', '1', '2020-06-08 11:33:59', '2020-06-08 11:33:59');
INSERT INTO `t_user` VALUES ('19', 'uuu', '123456', 'u', null, '2', '1', '2020-06-08 11:34:04', '2020-06-08 11:34:04');
INSERT INTO `t_user` VALUES ('20', 'nn', '123456', 'n', null, '2', '1', '2020-06-08 11:34:08', '2020-06-08 11:34:08');
INSERT INTO `t_user` VALUES ('21', 'f', '123456', 'f', null, '1', '1', '2020-06-08 11:34:13', '2020-06-08 11:34:13');
为了方便操作数据，用户名使用了唯一索引, 日期时间字段都是字符串类型
1.工程搭建（maven）
1.1pom文件
<dependencies>
<!--文件上传-->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.1.3</version>
</dependency>
<!--阿里数据库连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.11</version>
</dependency><!--阿里fastjson-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.36</version>
</dependency><!--jsp和servlet相关jar-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.0.1</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.1</version>
<scope>provided</scope>
</dependency>
<!--jstl表达式-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- 小而全的Java工具类库 -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.3.9</version>
</dependency>
<!--日志 start-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.25</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.25</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-simple</artifactId>
<version>1.7.25</version>
<scope>test</scope>
</dependency>
<!--日志end-->
<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.37</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.tomcat.maven</groupId>
<artifactId>tomcat7-maven-plugin</artifactId>
<version>2.2</version>
<configuration>
<path>/oa</path>
<port>8888</port>
<uriEncoding>UTF-8</uriEncoding>
</configuration>
</plugin>
</plugins>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<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>
</build>
1.2 官网下载layui
拷贝到工程 resources下面 , 接着在layui官网打开: “示例”–“布局”–“后台布局” – 使用这个后台主界
面–修改页面
2.后台搭建
2.1 编写DBHelper连接数据库
public class DBHelper {
static DruidDataSource dataSource;
static{
Properties properties = new Properties();
try {
properties.load(DBHelper.class.getClassLoader().getResourceAsStream("jdbc.properties"));
dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(Connection conn, PreparedStatement ps){
try {
if (conn!=null){
conn.close();
}
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(Connection conn, PreparedStatement ps, ResultSet rs){
try {
if (rs!=null){
rs.close();
}
close(conn,ps);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
System.out.println(DBHelper.getConnection());//测试连接数据库是否成功
}
}
2.2 编码过滤器(注意拦截路径)
@WebFilter(urlPatterns = {"*.do"})
public class CharacterEncoding implements Filter {
public void init(FilterConfig filterConfig) throws ServletException {}
public void doFilter(ServletRequest servletRequest, ServletResponse servletRespon
servletRequest.setCharacterEncoding("UTF-8");
servletResponse.setCharacterEncoding("UTF-8");
filterChain.doFilter(servletRequest,servletResponse);
}
public void destroy() {}
}
2.3 创建用户实体类
public class User {
private Integer id;
private String userName;
private String password;
private String realName; //真实姓名
private String img;//头像
private Integer type; //用户类型 1 管理员 2 业务员
private Integer isDel; // 是否有效 1 有效 2 无效
private String createTime;//创建时间
private String modifyTime;//修改时间
2.4 公共类
分页工具类
public class PageBean<T> {
private Integer page; //当前页
private Integer limit; //每页显示条数
private Integer total;//总条目
private Integer totalPage;//总页数
private List<T> data;//当前页显示的 分页数据
Json工具类
public class JsonData {
private Integer code; //状态码
private String msg; //返回的消息
private Object data; //返回的数据
public JsonData(Integer code, String msg, Object data) {
this.code = code;
this.msg = msg;
this.data = data;
}
public JsonData() { }
//操作成功,不需要返回数据
public static JsonData buildSuc(String msg){
return new JsonData(200,msg,null);
}
//操作成功,返回数据
public static JsonData buildSuc(String msg,Object data){
return new JsonData(200,msg,data);
}
//操作失败
public static JsonData buildError(String msg){
return new JsonData(-1,null,msg);
}
对外输出Json数据的工具(比如:需要响应json到前台)
public class PrintJsonData {
public static void printJson(HttpServletResponse response,JsonData jsonData){
response.setCharacterEncoding("UTF-8");
PrintWriter pw =null;
try {
pw = response.getWriter();
pw.print(JSON.toJSONString(jsonData));
} catch (IOException e) {
e.printStackTrace();
}finally {
pw.close();
}
}
}
2.5 编写BaseDao
public class BaseDao {
/**
* 查询集合的通用方法
* @param sql
* @param clz
* @param param 可变参数 name, age, sex
* @param
* @return
*/
public <T> List<T> findList(String sql, Class<T> clz, Object ...param){
Connection conn = DBHelper.getConnection();
PreparedStatement ps =null;
ResultSet rs=null;
List<T> list = new ArrayList<T>();
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < param.length; i++) {
ps.setObject(i+1,param[i]);
}
rs = ps.executeQuery();
while(rs.next()){
T bean = rsToBean(rs,clz);
list.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBHelper.close(conn,ps,rs);
}
return list;
}
//根据条件查询单个对象
public <T> T findOne(String sql,Class<T> clz,Object...param){
List<T> list = findList(sql, clz, param);
if(list!=null&&list.size()==1){
return list.get(0);
}
return null;
}
/**
* 删除 伪删除,实际上最终调用了update
*/
public boolean del(String sql,Object ... param){
Connection conn = DBHelper.getConnection();
try {
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < param.length; i++) {
ps.setObject(i+1,param[i]);
}
int i = ps.executeUpdate();
return i>0;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public boolean update(String sql,Object...param){
Connection conn = DBHelper.getConnection();
try {
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < param.length; i++) {
ps.setObject(i+1,param[i]);
}
int i = ps.executeUpdate();
return i>0?true:false;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public int insert(String sql,Object...param){
Connection conn = DBHelper.getConnection();
try {
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < param.length; i++) {
ps.setObject(i+1,param[i]);
}
return ps.executeUpdate();
//生成主键
/* ResultSet rs = ps.getGeneratedKeys();
if(rs!=null){
rs.next();
return rs.getInt(1);
}*/
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
private <T> T rsToBean(ResultSet rs, Class<T> clz) {
try {
T t = clz.newInstance();
ResultSetMetaData metaData = rs.getMetaData();//获取表结构信息
int columnCount = metaData.getColumnCount();//获取列长度
for (int i = 0; i < columnCount; i++) {
String columnLabel = metaData.getColumnLabel(i + 1);//列从1开始 ,i+1 ,列名
Object value = rs.getObject(columnLabel);//拿到列对应的数据
//反射获取类的属性
Field field = clz.getDeclaredField(columnLabel);
field.setAccessible(true);//提速反射
field.set(t,value);
}
return t;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 分页查询
* @param sql
* @param page 当前页 , layui需要这个page名称,不能乱写
* @param limit 每页显示几条数据? layui需要这个limit名称,不能乱写
* @param clz
* @param <T>
* @return
*
* 分页查询 ,需要 计算 每页显示的数据 ,需要查询总数据量
*/
public <T> PageBean<T> findPage(String sql, Class<T> clz, Integer page, Integer limit){
PageBean<T> pageBean = null;
Connection conn = DBHelper.getConnection();
try {
int count = getCount(sql);
int startIndex = (page-1)*limit;
int totalPages = count%limit==0?count/limit:count/limit+1;
sql = sql+" limit "+startIndex+","+limit;
List<T> list = findList(sql, clz);
pageBean = new PageBean<T>(page,limit,count,totalPages,list);
return pageBean;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
private int getCount(String sql) throws SQLException {
Connection conn = DBHelper.getConnection();
sql = "select count(1) from ("+sql+") as p";
try {
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
if(rs.next()){
return rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
}
** 2.6 编写监听器**
一般在项目中,使用${pageContext.request.contextPath}获取部署服务器的路径,这样无论服务器怎
么更换,都可以得到正确路径. 编写监听器,在程序启动时,读取服务器地址.
* ServletContextListener 可以监听 tomcat的启动和关闭
*/
@WebListener
public class MyListener implements ServletContextListener {
//初始化
public void contextInitialized(ServletContextEvent servletContextEvent) {
ServletContext context = servletContextEvent.getServletContext(); //拿到ser
context.setAttribute("ctx", PropertiesUtils.readFile("web.properties","webPat
}
//销毁
public void contextDestroyed(ServletContextEvent servletContextEvent) { }
}
工具类
// 读取配置文件的工具
public class PropertiesUtils {
/**
* 读取配置文件
*/
public static String readFile(String fileName,String key){
Properties properties = new Properties();
InputStream is = PropertiesUtils.class.getClassLoader().getResourceAsStream(f
if(is==null){
throw new RuntimeException(fileName+ "文件不存在");
}
try {
properties.load(is);
return (String) properties.get(key);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
说明:
工程中使用了2个 Json工具类, JsonData只是将后台处理的数据存入到该类. PrintJsonData作用
是,将数据转换为json写出去.
版权声明：本文为wzw_jiexika原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。