数据库表到Java类转换工具的实现

数据库表到Java类转换工具的实现

 

没有工具，动手自造，想尽办法提高生产力！

目前做工作中常常用到表数据到xml的转换，常常需要将数据库表字段一一转换为Java Bean的成员，一般做法都是看着数据字段去写代码，这样效率低下还容易出错，本人深有体会，于是乎写出一个小工具自动将从数据库表到Java Bean的转换，先将核心的源码拿出来，欢迎各位博友能在更多数据库平台上实现，并分享源码。

 

我目前用的数据是DB2  V9。

 

实现的代码如下：

 

数据库配置文件

zfzvf.properties

host=127.0.0.1
port=50000
dbname=zfzvf
driverClassName=com.ibm.db2.jcc.DB2Driver
username=root
password=lavasoft

 

配置文件读取类

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.util.Properties;
import java.io.InputStream;
import java.io.IOException;

/**
* Created by IntelliJ IDEA.
* File: PropertyUtil.java
* User: leizhimin
* Date: 2008-3-5 15:13:30
*/
public class PropertyUtils {
    private static final Log log = LogFactory.getLog(PropertyUtils.class);
    private static String host;
    private static String port;
    private static String dbname;
    private static String driverClassName;
    private static String username;
    private static String password;

    private static String url;

    static {
        reload();
    }

    /**
     * 私有构造方法，不需要创建对象
     */
    private PropertyUtils() {
    }

    /**
     * 重载配置文件
     *
     * @return 重载成功返回true，否则为false
     */
    public static boolean reload() {
        boolean flag = false;
        Properties prop = new Properties();
        InputStream in = PropertyUtils.class.getResourceAsStream("/zfzvf.properties");
        try {
            prop.load(in);
            host = prop.getProperty("host").trim();
            port = prop.getProperty("port").trim();
            dbname = prop.getProperty("dbname").trim();
            driverClassName = prop.getProperty("driverClassName").trim();
            username = prop.getProperty("username").trim();
            password = prop.getProperty("password").trim();
            url = "jdbc:db2://" + host + ":" + port + "/" + dbname;
            flag = true;
        } catch (IOException e) {
            log.error("找不系统配置文件zfzvf.properties，请检查！");
            e.printStackTrace();
        }
        return flag;
    }

    public static String getDbname() {
        return dbname;
    }

    public static void setDbname(String dbname) {
        PropertyUtils.dbname = dbname;
    }

    public static String getDriverClassName() {
        return driverClassName;
    }

    public static void setDriverClassName(String driverClassName) {
        PropertyUtils.driverClassName = driverClassName;
    }

    public static String getHost() {
        return host;
    }

    public static void setHost(String host) {
        PropertyUtils.host = host;
    }

    public static String getPassword() {
        return password;
    }

    public static void setPassword(String password) {
        PropertyUtils.password = password;
    }

    public static String getPort() {
        return port;
    }

    public static void setPort(String port) {
        PropertyUtils.port = port;
    }

    public static String getUrl() {
        return url;
    }

    public static void setUrl(String url) {
        PropertyUtils.url = url;
    }

    public static String getUsername() {
        return username;
    }

    public static void setUsername(String username) {
        PropertyUtils.username = username;
    }
}

 

核心实现类

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.*;

/**
* Created by IntelliJ IDEA.
* File: DBUtils.java
* User: leizhimin
* Date: 2008-3-5 15:05:00
*/
public class DBUtils {
    private static Log log = LogFactory.getLog(DBUtils.class);
    private static final String tb_sql = "\n" +
            "SELECT C.COLTYPE, C.NAME, C.REMARKS\n" +
            "  FROM SYSIBM.SYSCOLUMNS C, SYSIBM.SYSTABLES T\n" +
            " WHERE C.TBNAME = T.NAME\n" +
            "   AND C.TBCREATOR = T.CREATOR\n" +
            "   AND T.CREATOR = ?\n" +
            "   AND T.NAME = ?";

    /**
     * 获取数据库连接
     *
     * @return 数据连接
     */
    public static Connection makeConnection() {
        Connection conn = null;
        String url = PropertyUtils.getUrl();
        String username = PropertyUtils.getUsername();
        String password = PropertyUtils.getPassword();
        String diverClassName = PropertyUtils.getDriverClassName();
        try {
            Class.forName(diverClassName);
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void main(String args[]) {
//        testDBConnection();
        getTableProperty("ZFZVF", "JG_NJQK");
    }

    /**
     * 数据连接测试类，并将测试结果打印到控制台。
     *
     * @return 返回查询结果字符串
     */
    public static String testDBConnection() {
        Connection conn = makeConnection();
        StringBuffer sb = new StringBuffer();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT NAME,CTIME FROM SYSIBM.SYSTABLES WHERE NAME NOT LIKE 'DM_%' AND NAME NOT LIKE 'SYS%'");
            while (rs.next()) {
                sb.append(rs.getString(1) + "\t\t\t\t" + rs.getString(2) + "\n");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(sb.toString());
        return sb.toString();
    }

    /**
     * 获取表到Java Bean的成员列表的Java代码
     * @param schema 模式
     * @param tbName 表名
     * @return Java Bean的成员列表的Java代码
     */
    public static String getTableProperty(String schema, String tbName) {
        Connection conn = makeConnection();
        StringBuffer sb = new StringBuffer();
        sb.append(schema).append(".").append(tbName).append("\n");
        try {
            PreparedStatement pstmt = conn.prepareStatement(tb_sql);
            pstmt.setString(1, schema);
            pstmt.setString(2, tbName);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                String type = typeProcessor(rs.getString(1).trim());
                String name = rs.getString(2).toLowerCase();
                String remark;
                if (rs.getString(3) == null) {
                    remark = "";
                } else {
                    remark = rs.getString(3);
                }
                sb.append("\tprivate\t" + type + "\t" + name + ";\t\t\t//" + remark + "\n");
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        System.out.println(sb.toString());
        return sb.toString();
    }

    /**
     * SQL数据类型到Java数据类型的转换
     * @param sqlType SQL数据类型
     * @return Java数据类型
     */
    public static String typeProcessor(String sqlType) {
        String newType;
        if (sqlType.equalsIgnoreCase("varchar")) newType = "String";
        else if (sqlType.equalsIgnoreCase("char")) newType = "String";
        else if (sqlType.equalsIgnoreCase("bigint")) newType = "Long";
        else if (sqlType.equalsIgnoreCase("smallint")) newType = "int";
        else if (sqlType.equalsIgnoreCase("integer")) newType = "int";
        else if (sqlType.equalsIgnoreCase("decimal")) newType = "double";
        else if (sqlType.equalsIgnoreCase("timestmp")) newType = "Date";
        else {
            newType = sqlType;
        }
        return newType;
    }
}

 

运行结果：

ZFZVF.JG_NJQK
    private    String    gxr;      //管辖人
    private    Long    bs;      //标识
    private    Long    qybs;      //企业标识
    private    String    qymc;      //（企业）名称
    private    String    zh;      //字号
    private    String    xzqh_dm;      //行政区划代码
    private    String    hylb_dm;      //行业类别
    private    String    zzxs_dm;      //组织形式
    private    String    fddbr;      //法定代表人（负责人、投资人、执行合伙企业事务的合伙人）
    private    double    zczb;      //注册资本（金）
    private    String    jydz;      //经营地址
    private    String    lxdh;      //联系电话
    private    String    jyfw;      //经营范围
    private    String    qydl_dm;      //企业类型
    private    String    qyxxlx_dm;      //企业详细类型
    private    String    yyzzhm;      //营业执照号码
    private    String    yyzxm;      //经营者姓名
    private    Long    gxdw;      //管辖单位
    private    String    zt;      //状态
    private    Long    jgbs;      //机构标识
    private    Long    zwbs;      //
    private    Long    nd;      //年度
    private    int    mjbz;      //免检标志
    private    String    njms;      //年检描述
    private    String    njwt_dm;      //年检问题代码
    private    Long    njr;      //年检人
    private    Date    njsj;      //年检时间
    private    Date    slrq;      //受理日期
    private    Long    slr;      //受理人
    private    Date    bsrq;      //报送日期
    private    String    bsr;      //报送人
    private    int    sfggdwbz;      //是否广告单位标志
    private    int    ggsfnjbz;      //广告经营许可证是否年检标志
    private    String    slyj;      //受理意见
    private    String    qylxsx_dm;      //
    private    String    njjg_dm;      //
    private    Long    shr;      //
    private    Date    shrq;      //


Process finished with exit code 0

 

呵呵，实现很粗糙，加工一下，可以直接输出java Bean的源码。