小工具(一)——读取PowerDesigner生成EXCEL文件,生成POJO字段和ResultMap中的结果集映射的配置标签
一、前言
介绍该工具的主要作用:
1. 读取PowerDesigner生成EXCEL文件中的内容,生成ResultMap形式;
2. 生成POJO内容及map的相关字段;
注:pdm生成的配置文件如下图:
二、步骤
1.读取PowerDesigner生成EXCEL文件中的内容
注:采用jxl.jar是个很好读取excel工具,以后有时间再学习APACHE POI;
1 public static List<Map<String, Object>> getFileContent(final File file) 2 { 3 List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); 4 5 FileInputStream fis = null; 6 Workbook wb = null; 7 Map<String, Object> map = null; 8 try 9 { 10 fis = new FileInputStream(file); 11 //提供jxl包读取excel文件 12 wb = Workbook.getWorkbook(fis); 13 Sheet sheet = wb.getSheet(0); 14 15 for (int i = 0; i < sheet.getRows(); i++) 16 { 17 map = new HashMap<String, Object>(); 18 for (int j = 0; j < sheet.getColumns(); j++) 19 { 20 21 map.put(sheet.getCell(j, 0).getContents(), sheet.getCell(j, i).getContents()); 22 23 } 24 list.add(map); 25 } 26 27 } 28 catch (Exception e) 29 { 30 e.printStackTrace(); 31 } 32 finally 33 { 34 if (fis != null) 35 { 36 try 37 { 38 fis.close(); 39 } 40 catch (IOException e) 41 { 42 e.printStackTrace(); 43 } 44 } 45 } 46 47 return list; 48 }
生成结果:
1. 获取resultMap中的字段列值:
<result column="ROLEID" jdbcType="VARCHAR" property="roleid"/>
<result column="ROLENAME" jdbcType="VARCHAR" property="rolename"/>
<result column="DESCRIPTION" jdbcType="VARCHAR" property="description"/>
<result column="CREATDATE" jdbcType="VARCHAR" property="creatdate"/>
2. 生成POJO内容及map的相关字段:
既然读取内容已经搞定,那么生成POJO的内容和MAP形式的相关字段也可以很容易生成
1)、 主要代码:
1 private static String transferJAVAType(String str) 2 { 3 String result = null; 4 5 //如果字符串含有括号,截取字符串 6 if (str.indexOf("(") != -1) 7 { 8 str = str.substring(0, str.indexOf("(")); 9 } 10 11 //转换成大写 12 switch (str.toUpperCase()) 13 { 14 case "CHAR": 15 result = "String"; 16 break; 17 case "VARCHAR": 18 result = "String"; 19 break; 20 case "BLOB": 21 result = "byte[]"; 22 break; 23 case "DATE": 24 result = "date"; 25 break; 26 case "NUMERIC": 27 result = "double"; 28 break; 29 case "INTEGER": 30 result = "long"; 31 break; 32 default: 33 result = "String"; 34 break; 35 } 36 37 return result; 38 39 } 40 41 private static String transferUpToLow(final String str) 42 { 43 44 return str.toLowerCase(); 45 } 46 47 private static String transferJDBCType(String str) 48 { 49 String result = null; 50 51 //如果字符串含有括号,截取字符串 52 if (str.indexOf("(") != -1) 53 { 54 str = str.substring(0, str.indexOf("(")); 55 } 56 57 //转换成大写 58 switch (str.toUpperCase()) 59 { 60 case "CHAR": 61 result = "CHAR"; 62 break; 63 case "VARCHAR": 64 result = "VARCHAR"; 65 break; 66 case "BLOB": 67 result = "BLOB"; 68 break; 69 case "DATE": 70 result = "DATE"; 71 break; 72 case "NUMERIC": 73 result = "DOUBLE"; 74 break; 75 case "TIMESTAMP": 76 result = "TIMESTAMP"; 77 break; 78 case "INTEGER": 79 result = "INTEGER"; 80 break; 81 default: 82 result = "VARCHAR"; 83 break; 84 } 85 86 return result; 87 88 }
2) 验证代码:
//3. 获取excel列表中“Name”POJO System.out.println("2. 获取POJO的值:"); int count = 0; for (Map<String, Object> map : result) { count++; System.out.println("//" + count + ". " + (String) map.get("Comment")); System.out.println("private " + transferJAVAType((String) map.get("Data Type")) + " " + transferUpToLow((String) map.get("Name"))); } System.out.println("**************************************"); //4. 输出map.put("","");形式 System.out.println("3. 获取map形式的值: "); for (Map<String, Object> map : result) { System.out.println("map.put(\"" + transferUpToLow((String) map.get("Name")) + "\", " + transferUpToLow((String) map.get("Name")) + ");"); }
生成结果:
1 2. 获取POJO的值: 2 //1. 角色ID 3 private String roleid 4 //2. 角色名 5 private String rolename 6 //3. 描述信息 7 private String description 8 //4. 创建日期 9 private String creatdate 10 ************************************** 11 3. 获取map形式的值: 12 map.put("roleid", roleid); 13 map.put("rolename", rolename); 14 map.put("description", description); 15 map.put("creatdate", creatdate);
三、完整代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package utils; 2 3 import java.io.File; 4 import java.io.FileInputStream; 5 import java.io.FileNotFoundException; 6 import java.io.IOException; 7 import java.util.ArrayList; 8 import java.util.HashMap; 9 import java.util.List; 10 import java.util.Map; 11 12 import jxl.Sheet; 13 import jxl.Workbook; 14 15 16 public class HandleExcelFile 17 { 18 public static void main(final String[] args) 19 { 20 21 // String pathname = "C:\\Users\\linsiwen\\Desktop\\cnop迭代四20180620\\表\\SYS_ROLE.xls"; 22 String pathname = "D:\\SYS_ROLE.xls"; 23 24 //1.获取Csv文件路径 25 File csvFile = new File(pathname); 26 if (!csvFile.exists()) 27 { 28 try 29 { 30 throw new FileNotFoundException("该文件不存在!"); 31 } 32 catch (FileNotFoundException e) 33 { 34 e.printStackTrace(); 35 } 36 } 37 38 List<Map<String, Object>> result = getFileContent(csvFile); 39 result.remove(0); 40 //System.out.println(JSONObject.toJSONString(result)); 41 42 //2.生成相应的“<result column="REGION" jdbcType="VARCHAR" property="region"/>”形式 43 System.out.println("1. 获取resultMap中的字段列值:"); 44 for (Map<String, Object> map : result) 45 { 46 System.out.println("<result column=\"" + (String) map.get("Name") + "\" jdbcType=\"" 47 + transferJDBCType((String) map.get("Data Type")) + "\" property=\"" 48 + transferUpToLow((String) map.get("Name")) + "\"/>"); 49 50 } 51 52 System.out.println("*************************************"); 53 54 //3. 获取excel列表中“Name”POJO 55 System.out.println("2. 获取POJO的值:"); 56 int count = 0; 57 for (Map<String, Object> map : result) 58 { 59 count++; 60 System.out.println("//" + count + ". " + (String) map.get("Comment")); 61 System.out.println("private " + transferJAVAType((String) map.get("Data Type")) + " " 62 + transferUpToLow((String) map.get("Name"))); 63 64 } 65 66 System.out.println("**************************************"); 67 68 //4. 输出map.put("","");形式 69 System.out.println("3. 获取map形式的值: "); 70 for (Map<String, Object> map : result) 71 { 72 System.out.println("map.put(\"" + transferUpToLow((String) map.get("Name")) + "\", " 73 + transferUpToLow((String) map.get("Name")) + ");"); 74 } 75 76 } 77 78 private static String transferJAVAType(String str) 79 { 80 String result = null; 81 82 //如果字符串含有括号,截取字符串 q 83 if (str.indexOf("(") != -1) 84 { 85 str = str.substring(0, str.indexOf("(")); 86 } 87 88 //转换成大写 89 switch (str.toUpperCase()) 90 { 91 case "CHAR": 92 result = "String"; 93 break; 94 case "VARCHAR": 95 result = "String"; 96 break; 97 case "BLOB": 98 result = "byte[]"; 99 break; 100 case "DATE": 101 result = "date"; 102 break; 103 case "NUMERIC": 104 result = "double"; 105 break; 106 case "INTEGER": 107 result = "long"; 108 break; 109 default: 110 result = "String"; 111 break; 112 } 113 114 return result; 115 116 } 117 118 private static String transferUpToLow(final String str) 119 { 120 121 return str.toLowerCase(); 122 } 123 124 private static String transferJDBCType(String str) 125 { 126 String result = null; 127 128 //如果字符串含有括号,截取字符串 129 if (str.indexOf("(") != -1) 130 { 131 str = str.substring(0, str.indexOf("(")); 132 } 133 134 //转换成大写 135 switch (str.toUpperCase()) 136 { 137 case "CHAR": 138 result = "CHAR"; 139 break; 140 case "VARCHAR": 141 result = "VARCHAR"; 142 break; 143 case "BLOB": 144 result = "BLOB"; 145 break; 146 case "DATE": 147 result = "DATE"; 148 break; 149 case "NUMERIC": 150 result = "DOUBLE"; 151 break; 152 case "TIMESTAMP": 153 result = "TIMESTAMP"; 154 break; 155 case "INTEGER": 156 result = "INTEGER"; 157 break; 158 default: 159 result = "VARCHAR"; 160 break; 161 } 162 163 return result; 164 165 } 166 167 public static List<Map<String, Object>> getFileContent(final File file) 168 { 169 List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); 170 171 FileInputStream fis = null; 172 Workbook wb = null; 173 Map<String, Object> map = null; 174 try 175 { 176 fis = new FileInputStream(file); 177 //提供jxl包读取excel文件 178 wb = Workbook.getWorkbook(fis); 179 Sheet sheet = wb.getSheet(0); 180 181 for (int i = 0; i < sheet.getRows(); i++) 182 { 183 map = new HashMap<String, Object>(); 184 for (int j = 0; j < sheet.getColumns(); j++) 185 { 186 187 map.put(sheet.getCell(j, 0).getContents(), sheet.getCell(j, i).getContents()); 188 189 } 190 list.add(map); 191 } 192 193 } 194 catch (Exception e) 195 { 196 e.printStackTrace(); 197 } 198 finally 199 { 200 if (fis != null) 201 { 202 try 203 { 204 fis.close(); 205 } 206 catch (IOException e) 207 { 208 e.printStackTrace(); 209 } 210 } 211 } 212 213 return list; 214 } 215 }
四、完整结果
1 1. 获取resultMap中的字段列值: 2 <result column="ROLEID" jdbcType="VARCHAR" property="roleid"/> 3 <result column="ROLENAME" jdbcType="VARCHAR" property="rolename"/> 4 <result column="DESCRIPTION" jdbcType="VARCHAR" property="description"/> 5 <result column="CREATDATE" jdbcType="VARCHAR" property="creatdate"/> 6 ************************************* 7 2. 获取POJO的值: 8 //1. 角色ID 9 private String roleid 10 //2. 角色名 11 private String rolename 12 //3. 描述信息 13 private String description 14 //4. 创建日期 15 private String creatdate 16 ************************************** 17 3. 获取map形式的值: 18 map.put("roleid", roleid); 19 map.put("rolename", rolename); 20 map.put("description", description); 21 map.put("creatdate", creatdate);
五、总结
有时候数据表设计字段有40~50个,一个个手写比较麻烦,如果用mybaits逆向工程,全程无脑操作,久而久之就会使自己对设计的表字段完全不熟悉;所以本人还是写了个小工具,目的是学习新的知识,例如如何读取excel表格的内容?如何使用switch知识点?等等;促进自己的java进步。学习语言最短捷径就是多写,多用自己没有用过的知识点,只有用了,你才能慢慢的积累经验,不断的成长!