jdbc如何优雅的解决字典表数据转化
我们在做数据库设计的时候肯定会用字典表或者说枚举表等固化数据,那么当查询数据的时候用到了这些字典值的时候我们会怎么做呢。以下举个栗子吧,不对应该是好几个栗子
创新互联建站-专业网站定制、快速模板网站建设、高性价比鸡泽网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式鸡泽网站制作公司更省心,省钱,快速模板网站建设找我们,业务覆盖鸡泽地区。费用合理售后完善,10多年实体公司更值得信赖。
字典表(PUB_RESTRICTION)
SERIAL_NO | DESC_ID | DESC_CHINA | KEYWORD |
---|---|---|---|
67550001 | 1 | 城区 | AREA_TYPE |
67550002 | 2 | 郊区 | AREA_TYPE |
67550003 | 3 | 县城 | AREA_TYPE |
67550004 | 4 | 乡镇 | AREA_TYPE |
用户表(MANA_USER)
USER_ID | USER_NAME | USER_SEX | AREA_TYPE | USER_STATE |
---|---|---|---|---|
1 | 张三 | 男 | 67550001 | 正常 |
2 | 李四 | 女 | 67550002 | 正常 |
3 | 王五 | 未知 | 67550001 | 冻结 |
4 | 黄六 | 男 | 67550004 | 未审核 |
刚毕业的Egan接到老大的需求,把用户数据展示在页面上,‘AREA_TYPE’字段从字典表里面取出. Egan说到:这个简单,两张表关联查询一下。
然后Egan就吧啦吧啦的写起了代码….
SELECT
mu.USER_ID,
mu.USER_NAME,
mu.USER_SEX,
pr.DESC_CHINA AREA_TYPE,
mu.USER_STATE
FROM MANA_USER mu LEFT JOIN PUB_RESTRICTION pr ON mu.AREA_TYPE = pr.SERIAL_NO
WHERE pr.KEYWORD='AREA_TYPE'
就这样超快速的写完了,真的好简单啊,有什么难的,去老大那边炫耀一下我的效率:老大写完了,这样是不是你要的效果
USER_ID | USER_NAME | USER_SEX | AREA_TYPE | USER_STATE |
---|---|---|---|---|
1 | 张三 | 男 | 城区 | 正常 |
2 | 李四 | 女 | 郊区 | 正常 |
3 | 王五 | 未知 | 城区 | 冻结 |
4 | 黄六 | 男 | 乡镇 | 未审核 |
“嗯嗯,小伙子不错不错”。老大回答到。“等等,你看性别还有状态直接放字符串不好吧,也改成字典数据吧”。
Egan答到:“这样也不难,我在关联下就好了”,然后Egan又开始嗒嗒嗒的开始了。
字典表增加性别还有状态的字典数据
SERIAL_NO | DESC_ID | DESC_CHINA | KEYWORD |
---|---|---|---|
67550001 | 1 | 城区 | AREA_TYPE |
67550002 | 2 | 郊区 | AREA_TYPE |
67550003 | 3 | 县城 | AREA_TYPE |
67550004 | 4 | 乡镇 | AREA_TYPE |
67510001 | 1 | 男 | USER_SEX |
67510002 | 2 | 女 | USER_SEX |
67510003 | 3 | 未知 | USER_SEX |
67520001 | 1 | 正常 | USER_STATE |
67520002 | 2 | 冻结 | USER_STATE |
67520003 | 3 | 未审核 | USER_STATE |
用户表修改数据
USER_ID | USER_NAME | USER_AGE | AREA_TYPE | USER_STATE |
---|---|---|---|---|
1 | 张三 | 67510001 | 67550001 | 67520001 |
2 | 李四 | 67510002 | 67550002 | 67520001 |
3 | 王五 | 67510003 | 67550001 | 67520002 |
4 | 黄六 | 67510001 | 67550004 | 67520003 |
修改sql
SELECT
mu.USER_ID,
mu.USER_NAME,
pr2.DESC_CHINA USER_SEX,
pr.DESC_CHINA AREA_TYPE,
pr2.DESC_CHINA USER_STATE
FROM MANA_USER mu LEFT JOIN PUB_RESTRICTION pr ON mu.AREA_TYPE = pr.SERIAL_NO
LEFT JOIN PUB_RESTRICTION pr1 ON mu.USER_SEX = pr1.SERIAL_NO
LEFT JOIN PUB_RESTRICTION pr2 ON mu.USER_STATE = pr2.SERIAL_NO
WHERE pr.KEYWORD = 'AREA_TYPE' AND pr1.KEYWORD = 'USER_SEX' AND pr2.KEYWORD = 'USER_STATE'
就这样写好了,Egan看着自己写的代码有点烦的说到,这样子有点难看。咚咚咚的跑去问老大:“老大,这样写表关联太多了,重复的关联那张表,好臃肿的样子,有没有什么比较好的方式” “是有点,有点臃肿,让我想想”,一会儿老大答到:“你用函数吧,写一个函数通过字典值去取”.
然后Egan就回去写写起了函数
CREATE FUNCTION "GET_DESC_CHINA"(IN_SERIAL_NO NUMBER, IN_KEYWORD VARCHAR(32))
RETURN VARCHAR
IS
OUT_DESC_CHINA VARCHAR(40);
BEGIN
IF IN_SERIAL_NO IS NOT NULL
THEN
SELECT DESC_CHINA
INTO OUT_DESC_CHINA
FROM PUB_RESTRICTION
WHERE SERIAL_NO = IN_SERIAL_NO AND KEYWORD = IN_KEYWORD;
RETURN OUT_DESC_CHINA;
ELSE
RETURN '';
END IF;
END;
修改sql
SELECT
mu.USER_ID,
mu.USER_NAME,
GET_DESC_CHINA(mu.USER_SEX,'USER_SEX') USER_SEX,
GET_DESC_CHINA(mu.AREA_TYPE,'AREA_TYPE') AREA_TYPE,
GET_DESC_CHINA(mu.USER_STATE,'USER_STATE') USER_STATE
FROM MANA_USER mu
噎,看起来舒服多了。
日积月累之后,数据量增长上去,某一天Egan接到一个奇葩的需求:需要根据查询条件导出对应的用户数据。由于导出的数据量有点大效率有点慢下来了.
Egan有点摸不着头脑了,找到老大说:“这样用函数的形式数据量一大起来发现比我不转化直接导出id来说慢了好多好多。函数这样写,每一行都会去字典表查询一遍,好慢好慢,慢到要哭了(┬_┬)”
老大说到字典的数据量不大也比较固化,要不你把字典数据放到内存里面吧。然后在程序里面匹配吧。
Egan咚咚咚的回去,优雅的定义了一个字典对象
/**
* 公共资源处理
* Created by egan on 2018/11/19.
*/
@Component("pr")
public class PubRestrictUtils {
public static final String USER_SEX = "USER_SEX";
public static final String AREA_TYPE = "AREA_TYPE";
public static final String USER_STATE = "USER_STATE";
/**
* 字典表: Map<关键词, Map>
*/
private Map> propertys = new HashMap>();
@Autowired
private PubRestrictDao pubRestrictDao;
@PostConstruct
public void init(){
loadRestrict(USER_SEX);
loadRestrict(AREA_TYPE);
loadRestrict(USER_STATE);
}
public void loadRestrict(String keyWord){
List
修改sql
SELECT
mu.USER_ID,
mu.USER_NAME,
mu.USER_SEX,
mu.AREA_TYPE,
mu.USER_STATE
FROM MANA_USER mu
写完字典工具类之后,把数据库查询出来的结果集遍历一遍,把对应的字段取出来匹配。大概是以下这样的
@Autowired
PubRestrictUtils pubRestrict
... 此处省略好多饭
List
刚把用户功能改完,Egan发现还有订单,账户…………,好多好多,忍不住流下酸痛的泪(┬_┬)。
Egan又只好咚咚咚跑去老大那里诉苦了:“老大,这样我要改哭啊,而且感觉都是重复的代码(┬_┬)”
老大连忙安慰到:“不哭不哭,你看,数据库操作使用jdbc是吧,那在查询的时候返回来结果是ResultSet,遍历ResultSet之后才是你的dataList数据是吧,那你直接在统一处理吧,去判断哪些是需要转换的,这样子业务层你就不用任何改动了。”
“噎,好像很有道理哦,那我怎么知道哪些是需要转换的呢?” Egan傻傻的回到。
老大: “你通过ResultSetMetaData.getColumnName去跟字典表的KeyWord进行匹配就好了,匹配上去了就转换”
Egan:“不是所有ColumnName的名字都跟字典表KeyWord一样的呢”
老大:“你试试别名上面下手脚,比如:吧啦吧啦……”
讲完了,Egan好像听懂了,就哒啦嗒啦的开始写了
/**
*
* 通过别名表达式处理处理结果集方案
*
* 注意 因别名不能超过30个字符,所以在处理的时候尽量是简短
*
* 使用的方式 主要分为四种
* 方式一: bean名称#方法名$数据库字段名别名,这里数据库字段名别名用于最后展示使用
* 这里通过bean名称去匹配对应的处理对象
*
* select mu.AREA_TYPE \"pr#getAreaType$AREA_TYPE\" FROM MANA_USER mu
*
* 方式二: 方法名$数据库字段名别名,这里数据库字段名别名用于最后展示使用
* PubRestrict pubRestrict
* 这里通过 new HeartAliasColumnMapRowMapper(pubRestrict) 的形式进行 pubRestrict是对象实例,然后规则内的方法名就是属于本实例的方法名
*
* select mu.AREA_TYPE \"getAreaType$AREA_TYPE\" FROM MANA_USER mu
*
* 方式三:集合key名称$数据库字段名别名,这里数据库字段名别名用于最后展示使用
* 这里的集合属于字典表: Map<集合key名称, Map<记录数据, 需要转换的值>> 这样类型的一个集合,主要用于对结果整体的处理
* Map> propertys = new HashMap>();
* 这里通过 new HeartAliasColumnMapRowMapper(propertys) 的形式进行, propertys是所有结果列里面存储的信息
*
* select mu.AREA_TYPE AREA_TYPE$AREA_TYPE FROM MANA_USER mu
*
* 方式四:k$数据库字段名别名,这里数据库字段名别名用于最后展示使用
* 这里的集合属于字典表: Map<记录数据, 需要转换的值> 这样类型的一个集合,主要用于对结果整体的处理
* Map propertys = new HashMap();
* 这里通过 new HeartAliasColumnMapRowMapper(propertys) 的形式进行, propertys是所有结果列里面存储的信息
*
* select mu.AREA_TYPE k$AREA_TYPE FROM MANA_USER mu
*
* 方式五:(数据库字段名别名=集合key名称)$,这里数据库字段名别名用于最后展示使用
* 这里的集合属于字典表: Map<集合key名称, Map<记录数据, 需要转换的值>> 这样类型的一个集合,主要用于对结果整体的处理
* Map> propertys = new HashMap>();
* 这里通过 new HeartAliasColumnMapRowMapper(propertys) 的形式进行, propertys是所有结果列里面存储的信息
*
* select mu.AREA_TYPE AREA_TYPE$ FROM MANA_USER mu
*
*
* 使用方式
* JdbcTemplate.query(sql, new AliasElColumnMapRowMapper(propertys)
*
* 注意: 方案五是方案三的简化版;方案二,方案三(方案五),方案四是互斥的,在场景中只能使用一种,方案一的话是通用型的
*
*
* Created by egan on 2018/11/19.
*
* email: egzosn@gmail.com
*/
public class AliasElColumnMapRowMapper extends ColumnMapRowMapper {
private static final Logger LOGGER = LoggerFactory.getLogger(AliasElColumnMapRowMapper.class);
private static final Map METHOD_MAP = new HashMap();
WebApplicationContext wac = ContextLoader.getCurrentWebApplicationContext();
/**
* 转换bean实例,用于方案一,二的处理
*/
private Object bean;
/**
* 方案四:k$数据库字段名别名
*/
private Map
修改sql
SELECT
mu.USER_ID,
mu.USER_NAME,
mu.USER_SEX$,
mu.AREA_TYPE$,
mu.USER_STATE$
FROM MANA_USER mu
Dao部分调用代码, JdbcTemplate 调用的时候加入第二个参数;
@Autowired
PubRestrictUtils pubRestrict
@Autowired
JdbcTemplate jdbcTemplate;
...此处省略好多好多
String sql = "SELECT mu.USER_ID, mu.USER_NAME, mu.USER_SEX USER_SEX$, mu.AREA_TYPE AREA_TYPE$, mu.USER_STATE USER_STATE$ FROM MANA_USER mu";
List
就这样写好了。老大还夸奖了下Egan,Egan都乐了好几天。
好了,进入正题,讲解下AliasElColumnMapRowMapper的使用吧,虽然在这个类的说明里面也讲了很细了。
使用的时候再重申一遍
注意:
- 方案五是方案三的简化版;方案二,方案三(方案五),方案四是互斥的,在场景中只能使用一种,方案一的话是通用型的
- 因别名不能超过30个字符,所以在处理的时候尽量是简短
以下还是根据以上Egan所遇的问题去举例每种方案吧。
- 方案一 这里别名你不一定要跟字段表的关键词KeyWord一样,只需要你调用的方法正确即可,比如 mu.USER_SEX pr#getUserSex$SEX,别名部分pr#getUserSex$SEX的"pr"部分是bean在spring容器中的名字这里可以倒回去看字典对象类上面的标识@Component("pr"),然后"getUserSex" 是对应pr对象的对应方法
@Autowired
PubRestrictUtils pubRestrict
@Autowired
JdbcTemplate jdbcTemplate;
String sql = "SELECT mu.USER_ID, mu.USER_NAME, mu.USER_SEX \"pr#getUserSex$SEX\", mu.AREA_TYPE \"pr#getAreaType$AREA_TYPE\", mu.USER_STATE \"pr#getUserState$USER_STATE\" FROM MANA_USER mu";
List
查询结果List<Map<String, Object>>
对应的值:[{USER_ID=1,USER_NAME=张三,SEX=男,AREA_TYPE=城区,USER_STATE=正常},...]
- 方案二 这里别名你不一定要跟字段表的关键词KeyWord一样,只需要你调用的方法正确即可,比如 mu.USER_SEX getUserSex$SEX,别名部分getUserSex$SEX的"getUserSex"是对应传进去pubRestrict的方法
@Autowired
PubRestrictUtils pubRestrict
@Autowired
JdbcTemplate jdbcTemplate;
String sql = "SELECT mu.USER_ID, mu.USER_NAME, mu.USER_SEX \"getUserSex$SEX\", mu.AREA_TYPE \"getAreaType$AREA_TYPE\", mu.USER_STATE \"getUserState$USER_STATE\" FROM MANA_USER mu";
List
查询结果Lis<Map<String, Object>>
对应的值:[{USER_ID=1,USER_NAME=张三,SEX=男,AREA_TYPE=城区,USER_STATE=正常},...]
- 方案三 这里别名你不一定要跟字段表的关键词KeyWord一样,只需要你调用的方法正确即可,比如 mu.USER_SEX USER_SEX$SEX,别名部分USER_SEX$SEX的"USER_SEX"是外层MAP的key然后SEX是前端展示的名字
@Autowired
PubRestrictUtils pubRestrict
@Autowired
JdbcTemplate jdbcTemplate;
String sql = "SELECT mu.USER_ID, mu.USER_NAME, mu.USER_SEX USER_SEX$SEX, mu.AREA_TYPE AREA_TYPE$AREA_TYPE, mu.USER_STATE USER_STATE$USER_STATE FROM MANA_USER mu";
List
查询结果Lis<Map<String, Object>>
对应的值:[{USER_ID=1,USER_NAME=张三,SEX=男,AREA_TYPE=城区,USER_STATE=正常},...]
方案五,这里就不讲方案五了,Egan同学的例子就是方案五了。
- 方案四。这里需要对字典对象做一些改动
字典对象,这里把属性对象做成了单层的MAP
/**
* 公共资源处理
* Created by egan on 2018/11/19.
*/
@Component("pr")
public class PubRestrictUtils {
/**
* 字典表: Map
*/
private Map propertys = null;
@Autowired
private PubRestrictDao pubRestrictDao;
@PostConstruct
public void loadRestrict(){
List
对象使用, 别名展示同理 mu.USER_SEX k$SEX ,别名“k$SEX”中的“k”是固定值,“SEX”是需要展示的值
@Autowired
PubRestrictUtils pubRestrict
@Autowired
JdbcTemplate jdbcTemplate;
String sql = "SELECT mu.USER_ID, mu.USER_NAME, mu.USER_SEX k$SEX, mu.AREA_TYPE k$AREA_TYPE, mu.USER_STATE k$USER_STATE FROM MANA_USER mu";
List
查询结果List<Map<String, Object>>
对应的值:[{USER_ID=1,USER_NAME=张三,SEX=男,AREA_TYPE=城区,USER_STATE=正常},...]
.
最后,安利一个全能支付Java开发工具包.优雅的轻量级支付模块集成支付对接支付整合(微信支付,支付宝,银联,友店,富友,跨境支付paypal,payoneer(P卡派安盈)易极付)app,扫码,即时到帐刷卡付条码付刷脸付转账服务商模式、支持多种支付类型多支付账户,支付与业务完全剥离,简单几行代码即可实现支付,简单快速完成支付模块的开发,可轻松嵌入到任何系统里 目前仅是一个开发工具包(即SDK),只提供简单Web实现,建议使用maven或gradle引用本项目即可使用本SDK提供的各种支付相关的功能
https://www.oschina.net/p/pay-java-parent
文章题目:jdbc如何优雅的解决字典表数据转化
转载来于:http://pwwzsj.com/article/jhpgci.html