数据库表生成MyBatis的Mapping文件

zhouying23

　　当使用MyBatis的时候，需要每个表建立一个Entity类，还要创建一个Mapping文件，如果表的数量少还好说，当表的数量多的时候，而且以后还会增加的时候，手动创建这些文件就成了一个繁琐而又重复的工作，根据“能用工具不手写”的原则，可以根据Mapping xml的格式，用程序来生成这些对应的文件。
　　这里是一个生成Mapping XML文件的demo code:

import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 数据表生成mybatis的对应mapping xml
* Created by usr1999 on 2015/1/30.
*/
public class TableToXml {
private static final String LINE = "\r\n";
private static final String TAB = "\t";
private static Map<String, String> jdbcTypeMap;
String rootPath = "/Users/jiangzhiqiang/dev";
static {
jdbcTypeMap = new HashMap<String, String>();
jdbcTypeMap.put("DATETIME", "TIMESTAMP");
jdbcTypeMap.put("TINYINT", "INTEGER");
jdbcTypeMap.put("SMALLINT", "INTEGER");
jdbcTypeMap.put("INT", "INTEGER");
}
public static void main(String[] args) throws SQLException, ClassNotFoundException {
String jdbcString = "jdbc:mysql://m3307i.test.db.dmall.com:3307/dmall_ledger";
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(jdbcString, "devtest", "WDevTest@Dmall");
DatabaseMetaData metadata = con.getMetaData();
ResultSet rs = metadata.getTables("dmall_ledger", null, null, null);
TableToXml d = new TableToXml();
while (rs.next()) {
String tableName = rs.getString(3);
System.out.println("正在生成XML Mapping的表名： ================ "+tableName);
d.tableToXml(con, tableName);
}
}
private void tableToXml(Connection conn, String tableName) {
String entityName = convertTableToEntity(tableName);
String sql = "select * from " + tableName + " limit 1"; //MySql DB
StringBuilder xml = new StringBuilder();
xml.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
xml.append(LINE);
xml.append("<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\" >");
xml.append(LINE);
// mapping.xml中要引用的Mapper接口,com.zainagou.supplier.mapper为xml映射的entity类的包名
String mapperName = "com.dmall.settle.mapper." +entityName+"Mapper";
xml.append("<mapper namespace=\""+mapperName+"\">");
xml.append(LINE);
xml.append(TAB);
String fullEntityName = "com.dmall.settle.domain."+entityName;
xml.append("<resultMap id=\"baseResultMap\" type=\""+fullEntityName+"\" >");
List<String> columeList = new ArrayList<String>();
try {
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
for(int i=1;i<=columnCount;i++){
xml.append(LINE);
xml.append(TAB);
xml.append(TAB);
String columnName = metaData.getColumnName(i);
columeList.add(columnName);
String columnTypeName = metaData.getColumnTypeName(i);
if(columnTypeName.contains(" ")){
columnTypeName = columnTypeName.split(" ")[0];
}
//                System.out.println("columnName:"+columnName+", columnType:"+columnTypeName);
if(jdbcTypeMap.containsKey(columnTypeName)){
columnTypeName = jdbcTypeMap.get(columnTypeName);
}
if(columnName.equals("id")){
xml.append("<id column=\"id\" property=\"id\" jdbcType=\"");
xml.append(columnTypeName);
xml.append("\" />");
}else{
xml.append("<result column=\"");
xml.append(columnName);
xml.append("\" property=\"");
String propertyName = dealName(columnName);
xml.append(propertyName);
xml.append("\" jdbcType=\"");
xml.append(columnTypeName);
xml.append("\" />");
}
}
}catch (Exception e){
e.printStackTrace();
}
xml.append(LINE);
xml.append(TAB);
xml.append("</resultMap>");
xml.append(LINE);
xml.append(TAB);
xml.append("<sql id=\"base_column_list\" >");
xml.append(LINE);
xml.append(TAB);
xml.append(TAB);
for(int i=0,size=columeList.size();i<size;i++){
xml.append(columeList.get(i));
if(i!=(size-1)){
xml.append(",");
}
}
xml.append(LINE);
xml.append(TAB);
xml.append("</sql>");
xml.append(LINE);
xml.append("</mapper>");
//        String rootPath = this.getClass().getClassLoader().getResource("").getPath();
//        String endPath = rootPath + "\\" + (packages.replace("/", "\\")).replace(".", "\\");
buildXmlFile(rootPath + "/" + entityName + ".xml", xml.toString());
}
//生成xml文件
public void buildXmlFile(String filePath, String fileContent) {
try {
File file = new File(filePath);
FileOutputStream osw = new FileOutputStream(file);
PrintWriter pw = new PrintWriter(osw);
pw.println(fileContent);
pw.close();
} catch (Exception e) {
System.out.println("生成xml文件出错：" + e.getMessage());
}
}
//    转换表名为实体名
public String convertTableToEntity(String tableName){
tableName = tableName.substring(0, 1).toUpperCase() + tableName.subSequence(1, tableName.length());
String entityName = dealLine(tableName);
return entityName;
}
private String dealLine(String tableName) {
// 处理下划线情况，把下划线后一位的字母变大写；
tableName = dealName(tableName);
return tableName;
}
//下划线后一位字母大写
private String dealName(String columnName) {
if (columnName.contains("_")) {
StringBuffer names = new StringBuffer();
String arrayName[] = columnName.split("_");
names.append(arrayName[0]);
for (int i = 1; i < arrayName.length; i++) {
String arri = arrayName;
String tmp = arri.substring(0, 1).toUpperCase() + arri.substring(1, arri.length());
names.append(tmp);
}
columnName = names.toString();
}
return columnName;
}
}
　　 
　　题外话：写这个程序耗费了2个左右小时时间，表面上看上去不如手动创建文件的速度快，但长远看来，节省了未来做这些工作的时间，也是提高了生产力，就像节能灯一次投入较大，从长远来看价值却大一样。