002-基本业务搭建【日志,工具类dbutils,dbcp等使用】
一、需求分析
1.1、概述
1、用户进入“客户管理”,通过列表方式查看用户;
2、客户名称,模糊查询用户列表
3、客户名称,可查看客户详细信息
4、新增、编辑、删除功能等
二、系统设计
需要对原始需求进行分析,找出Use Case(用例),然后设计表结构,画原型图,定义URL规范。
设计过程是由粗到细,由表及里。注意:设计阶段不涉及具体技术实现。
2.1、用例设计
查询用户、显示客户列表、显示客户基本信息、创建客户、编辑客户、删除客户
建议使用UML用例图展示,更加直观
2.2、数据库表设计
2.3、设计界面原型
可以使用Mockplus、axure 等工具设计界面原型。
2.4、设计URL
注:还有其他设计过程,如数据模型,业务流程等。略。最终会形成一份设计文档。
三、代码开发
https://github.com/bjlhx15/smart-framework.git
知识点1 单元测试
引用包
<dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency>
在需要添加测试的类中使用快捷键:ctrl+alt+T即可
单元测试需要使用的配置,可在test下增加resources,并且右键→Mark Directory as/Resources Root。
需要注意main/java,main/resources,test/java,test/resources都是classpath的根目录,当运行单元测试的时候,遵循“就近原则”,即先从test/java,test/resources加载类或配置文件。
知识点2 日志使用
添加slf4j依赖,用于提供日志API,使用Log4j作为实现,POM配置
<!--slf4j 日志 默认使用log4j--> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.7.7</version> </dependency>
在resource下增加log4j.properties文件
log4j.rootLogger=ERROR,console,file log4j.appender.console=org.apache.log4j.ConsoleAppender log4j.appender.console.layout=org.apache.log4j.PatternLayout log4j.appender.console.layout.ConversionPattern=%m%n log4j.appender.file=org.apache.log4j.DailyRollingFileAppender log4j.appender.file.File=${user.home}/logs/book.log log4j.appender.file.DatePattern='_'yyyyMMdd log4j.appender.file.layout=org.apache.log4j.PatternLayout log4j.appender.file.layout.ConversionPattern=%d{HH:mm:ss SSS} %p %c (%L) %m%n log4j.logger.com.lhx=DEBUG
提供了两种方式的日志,包com.lhx下的日志级别是debug
知识点3 commons工具类的使用
<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.4</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-collections4</artifactId> <version>4.1</version> </dependency>
知识点4 Dbutil应用以及threadlocal应用
<!--mysql--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> <!--commons-dbutils --> <dependency> <groupId>commons-dbutils</groupId> <artifactId>commons-dbutils</artifactId> <version>1.6</version> </dependency>
DatabaseHelper代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.lhx.chapter2.helper; import com.lhx.chapter2.util.PropsUtil; import com.sun.org.apache.regexp.internal.REUtil; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.collections4.MapUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.Properties; /** * db链接工具 */ public class DatabaseHelper { private static final Logger log = LoggerFactory.getLogger(DatabaseHelper.class); private static final String DRIVER; private static final String URL; private static final String USERNAME; private static final String PASSWORD; private static final QueryRunner QUERY_RUNNER ; private static final ThreadLocal<Connection> CONNECTION_HOLDER ; static { QUERY_RUNNER = new QueryRunner(); CONNECTION_HOLDER = new ThreadLocal<>(); Properties conf = PropsUtil.loadProps("config.properties"); DRIVER = conf.getProperty("jdbc.driver"); URL = conf.getProperty("jdbc.url"); USERNAME = conf.getProperty("jdbc.username"); PASSWORD = conf.getProperty("jdbc.password"); try { Class.forName(DRIVER); } catch (ClassNotFoundException e) { log.error("类加载失败", e); } } public static Connection getConnection() { Connection conn = CONNECTION_HOLDER.get(); if (conn == null) { try { conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); } catch (SQLException e) { log.error("获取链接失败", e); } finally { CONNECTION_HOLDER.set(conn); } } return conn; } public static void closeConnection() { Connection conn = CONNECTION_HOLDER.get(); if (conn != null) { try { conn.close(); } catch (SQLException e) { log.error("关闭链接失败", e); } finally { CONNECTION_HOLDER.remove(); } } } public static <T> List<T> queryEntityList(Class<T> entityClass, String sql, Object... parmas) { List<T> entityList; try { entityList = QUERY_RUNNER.query(getConnection(), sql, new BeanListHandler<T>(entityClass), parmas); } catch (SQLException e) { log.error("查询实体失败", e); throw new RuntimeException(e); } finally { closeConnection(); } return entityList; } public static <T> T queryEntity(Class<T> entityClass, String sql, Object... parmas) { T entity; try { entity = QUERY_RUNNER.query(getConnection(), sql, new BeanHandler<T>(entityClass), parmas); } catch (SQLException e) { log.error("查询实体失败", e); throw new RuntimeException(e); } finally { closeConnection(); } return entity; } public static List<Map<String, Object>> excuteQuery(String sql, Object... parmas) { List<Map<String, Object>> result; try { result = QUERY_RUNNER.query(getConnection(), sql, new MapListHandler(), parmas); } catch (SQLException e) { log.error("查询失败", e); throw new RuntimeException(e); } finally { closeConnection(); } return result; } public static int excuteUpdate(String sql, Object... parmas) { int rows = 0; try { rows = QUERY_RUNNER.update(getConnection(), sql, parmas); } catch (SQLException e) { log.error("更新失败", e); throw new RuntimeException(e); } finally { closeConnection(); } return rows; } private static String getTableName(Class<?> entityClass) { return entityClass.getSimpleName(); } public static <T> boolean insertEntity(Class<T> entityClass, Map<String, Object> filedMap) { if (MapUtils.isEmpty(filedMap)) { log.error("请求插入的map不能为空"); return false; } String sql = "insert into " + getTableName(entityClass); StringBuilder columns = new StringBuilder("("); StringBuilder values = new StringBuilder("("); for (String filedName : filedMap.keySet()) { columns.append(filedName).append(", "); values.append("?, "); } columns.replace(columns.lastIndexOf(", "), columns.length(), ")"); values.replace(values.lastIndexOf(", "), values.length(), ")"); sql += columns + " values " + values; Object[] parmas = filedMap.values().toArray(); return excuteUpdate(sql, parmas) == 1; } public static <T> boolean updateEntity(Class<T> entityClass, long id, Map<String, Object> filedMap) { if (MapUtils.isEmpty(filedMap)) { log.error("请求更新的map不能为空"); return false; } String sql = "update " + getTableName(entityClass) + " set "; StringBuilder columns = new StringBuilder(); for (String filedName : filedMap.keySet()) { columns.append(filedName).append("=?, "); } sql += columns.substring(0, columns.lastIndexOf(", ")) + " where id=?"; List<Object> paramsList = new ArrayList<>(); paramsList.addAll(filedMap.values()); paramsList.add(id); Object[] parmas = paramsList.toArray(); return excuteUpdate(sql, parmas) == 1; } public static <T> boolean deleteEntity(Class<T> entityClass, long id) { String sql = "delete from " + getTableName(entityClass) + " where id=? "; return excuteUpdate(sql, id) == 1; } public static void excuteSqlFile(String filePath) { String file = "sql/customer_init.sql"; InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(file); BufferedReader reader = new BufferedReader(new InputStreamReader(is)); String sql; try { while ((sql = reader.readLine()) != null) { excuteUpdate(sql); } } catch (IOException e) { log.error("文件读取异常", e); throw new RuntimeException(e); } } }
知识点5 Dbutil、threadlocal、dbcp连接池应用
<!--mysql--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> <!--commons-dbutils --> <dependency> <groupId>commons-dbutils</groupId> <artifactId>commons-dbutils</artifactId> <version>1.6</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.1.1</version> </dependency>
DatabaseDbcpHelper代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.lhx.chapter2.helper; import com.lhx.chapter2.util.PropsUtil; import org.apache.commons.collections4.MapUtils; import org.apache.commons.dbcp2.BasicDataSource; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.apache.commons.lang3.StringUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.sql.Connection; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.Properties; /** *db链接工具 */ public class DatabaseDbcpHelper { private static final Logger log = LoggerFactory.getLogger(DatabaseHelper.class); private static final QueryRunner QUERY_RUNNER; private static final ThreadLocal<Connection> CONNECTION_HOLDER; private static final BasicDataSource DATA_SOURCE; static { QUERY_RUNNER = new QueryRunner(); CONNECTION_HOLDER = new ThreadLocal<>(); DATA_SOURCE = new BasicDataSource(); Properties conf = PropsUtil.loadProps("config.properties"); String driver = conf.getProperty("jdbc.driver"); String url = conf.getProperty("jdbc.url"); String username = conf.getProperty("jdbc.username"); String password = conf.getProperty("jdbc.password"); DATA_SOURCE.setDriverClassName(driver); DATA_SOURCE.setUrl(url); DATA_SOURCE.setUsername(username); DATA_SOURCE.setPassword(password); } public static Connection getConnection() { Connection conn = CONNECTION_HOLDER.get(); if (conn == null) { try { conn = DATA_SOURCE.getConnection(); } catch (SQLException e) { log.error("获取链接失败", e); } finally { CONNECTION_HOLDER.set(conn); } } return conn; } public static <T> List<T> queryEntityList(Class<T> entityClass, String sql, Object... parmas) { List<T> entityList; try { entityList = QUERY_RUNNER.query(getConnection(), sql, new BeanListHandler<T>(entityClass), parmas); } catch (SQLException e) { log.error("查询实体失败", e); throw new RuntimeException(e); } return entityList; } public static <T> T queryEntity(Class<T> entityClass, String sql, Object... parmas) { T entity; try { entity = QUERY_RUNNER.query(getConnection(), sql, new BeanHandler<T>(entityClass), parmas); } catch (SQLException e) { log.error("查询实体失败", e); throw new RuntimeException(e); } return entity; } public static List<Map<String, Object>> excuteQuery(String sql, Object... parmas) { List<Map<String, Object>> result; try { result = QUERY_RUNNER.query(getConnection(), sql, new MapListHandler(), parmas); } catch (SQLException e) { log.error("查询失败", e); throw new RuntimeException(e); } return result; } public static int excuteUpdate(String sql, Object... parmas) { int rows = 0; try { rows = QUERY_RUNNER.update(getConnection(), sql, parmas); } catch (SQLException e) { log.error("更新失败", e); throw new RuntimeException(e); } return rows; } private static String getTableName(Class<?> entityClass) { return entityClass.getSimpleName(); } public static <T> boolean insertEntity(Class<T> entityClass, Map<String, Object> filedMap) { if (MapUtils.isEmpty(filedMap)) { log.error("请求插入的map不能为空"); return false; } String sql = "insert into " + getTableName(entityClass); StringBuilder columns = new StringBuilder("("); StringBuilder values = new StringBuilder("("); for (String filedName : filedMap.keySet()) { columns.append(filedName).append(", "); values.append("?, "); } columns.replace(columns.lastIndexOf(", "), columns.length(), ")"); values.replace(values.lastIndexOf(", "), values.length(), ")"); sql += columns + " values " + values; Object[] parmas = filedMap.values().toArray(); return excuteUpdate(sql, parmas) == 1; } public static <T> boolean updateEntity(Class<T> entityClass, long id, Map<String, Object> filedMap) { if (MapUtils.isEmpty(filedMap)) { log.error("请求更新的map不能为空"); return false; } String sql = "update " + getTableName(entityClass) + " set "; StringBuilder columns = new StringBuilder(); for (String filedName : filedMap.keySet()) { columns.append(filedName).append("=?, "); } sql += columns.substring(0, columns.lastIndexOf(", ")) + " where id=?"; List<Object> paramsList = new ArrayList<>(); paramsList.addAll(filedMap.values()); paramsList.add(id); Object[] parmas = paramsList.toArray(); return excuteUpdate(sql, parmas) == 1; } public static <T> boolean deleteEntity(Class<T> entityClass, long id) { String sql = "delete from " + getTableName(entityClass) + " where id=? "; return excuteUpdate(sql, id) == 1; } public static void excuteSqlFile(String filePath) { String file = "sql/customer_init.sql"; InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(file); BufferedReader reader = new BufferedReader(new InputStreamReader(is)); String sql; try { while ((sql = reader.readLine()) != null) { if (StringUtils.isNotEmpty(sql)) { excuteUpdate(sql); } } } catch (IOException e) { log.error("文件读取异常", e); throw new RuntimeException(e); } } }
知识点6 Dbutils简述
DbUtils给我们提供了10个ResultSetHandler实现类,如下:
①ArrayHandler: 将查询结果的第一行数据,保存到Object数组中
②ArrayListHandler 将查询的结果,每一行先封装到Object数组中,然后将数据存入List集合
③BeanHandler 将查询结果的第一行数据,封装到user对象
④BeanListHandler 将查询结果的每一行封装到user对象,然后再存入List集合
⑤ColumnListHandler 将查询结果的指定列的数据封装到List集合中
⑥MapHandler 将查询结果的第一行数据封装到map结合(key==列名,value==列值)
⑦MapListHandler 将查询结果的每一行封装到map集合(key==列名,value==列值),再将map集合存入List集合
⑧BeanMapHandler 将查询结果的每一行数据,封装到User对象,再存入mao集合中(key==列名,value==列值)
⑨KeyedHandler 将查询的结果的每一行数据,封装到map1(key==列名,value==列值 ),然后将map1集合(有多个)存入map2集合(只有一个)
⑩ScalarHandler 封装类似count、avg、max、min、sum......函数的执行结果