JDBC原生链接Mysql数据根据表映射生成bean实体类

作者： jiangfengcheng / 2021年1月28日

因为项目比较老，没用任何框架，所以用原生的写法，根据表结构生成对应的实体类

package com.jiangfengcheng.mysql;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

/**
* 类:DBToVO
* 功能:根据数据库表生成VO–Value Object
* @author jiangfengcheng
* @e-mail:admin@uesj.com
*/
public class DBToJavaVO {
private static final String TARGET_DIR=”D:/data/”; //类文件存放的路径
private static final String DIVER_NAME=”com.mysql.jdbc.Driver”;
private static final String URL=”jdbc:mysql://localhost:3306/yue_data_collect_dsj?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useAffectedRows=true”;
private static final String USERNAME=”root”;
private static final String PASSWORD=”211314″;
private static final String DATABASE_NAME=”dpsj”; //数据库名称
private static final String AUTHOR=”jiangfengcheng”; //作者
private static final String E_MAIL=”15853505525@139.com”; //作者邮箱
private static final String PACKAGE_NAME=”com.bdsoft.bigdata.modules.zbDp.entity”; //包名
private Connection conn;
private Statement stmt;
private String sql;
private ResultSet rs;
//是否需要导入包java.util.*
private boolean needUtil = true;
//是否需要导入包java.sql.*
private boolean needSql = false;
//是否需要导入包java.math.BigDecimal
private boolean needBigDecimal = false;
private String[] fields; //属性
private String[] dataTypes; //数据类型
private String[] comments; //属性的注释
/**
* 方法:根据数据库表生成VO–Value Object
*/
private void tableToVo(){

String tableName;
String className;
try {
InputStreamReader reader = new InputStreamReader(System.in);
BufferedReader bf = new BufferedReader(reader);
System.out.println(“输入要转换成VO的表名称:”);
tableName = bf.readLine().trim();
System.out.println(“输入生成的类名,不输入的时候默认跟表名相同:”);
className = bf.readLine().trim();
Class.forName(DIVER_NAME);
conn = (Connection) DriverManager.getConnection(URL,USERNAME,PASSWORD);
stmt = (Statement) conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); //创建可滚动的,只读的结果集
sql = “SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT”+
” FROM INFORMATION_SCHEMA.COLUMNS”+
” WHERE table_name = ‘”+tableName+”‘”+ //表名
” AND table_schema = ‘”+DATABASE_NAME+”‘”; //数据库名
System.out.println(sql);
rs = stmt.executeQuery(sql);
rs.last(); //把指针指向结果集的最后
int fieldNum = rs.getRow(); //取得最后一条结果的行号,作为类的属性个数
int n = fieldNum;
if(n>0){ //判断数据表中是否存在字段
fields = new String[n];
dataTypes = new String[n];
comments = new String[n];
fields[–n] = rs.getString(1);
dataTypes[n] = rs.getString(2);
comments[n] = rs.getString(3);
while(rs.previous()){
fields[–n]=rs.getString(1); //取得结果集的第一列数据,对应的列名:Field
dataTypes[n] = rs.getString(2);
comments[n] = rs.getString(3);
}
//打印相关信息
System.out.println(“你要转换的表是:”+tableName);
System.out.println(“该表中共有”+fieldNum+”个字段,信息如下:”);
for(int i=0,j=fields.length;i<j;i++){
System.out.println(“—————————————-“);
String field = fields[i];
System.out.println(“字段名称:”+field);
//把字段名称格式化成java命名规则形式
//field=formatField(field);
fields[i]=field; //把格式化后的字段放入属性数组中
System.out.println(“数据类型:”+dataTypes[i]);
//把数据库字段类型转换成java数据类型
String dataType = dataTypes[i].toLowerCase();
dataType = formatDataType(dataType);
dataTypes[i] = dataType;

System.out.println(“字段注释:”+comments[i]);
if(“”.equals(comments[i])||comments[i]==null){
comments[i]=fields[i];
}
System.out.println(“—————————————-“);
}
//生成类文件,写入到磁盘中
writeObjectToFile(foramtClassName(className,tableName));
}else{
System.out.println(“该表不存在或者表中没有字段”);
}
} catch (ClassNotFoundException e) {
System.out.println(“未找到数据库驱动”);
System.out.println(e.getMessage());
} catch (SQLException e) {
System.out.println(e.getMessage());
//e.printStackTrace();
} catch (IOException e) {
System.out.println(e.getMessage());
//e.printStackTrace();
}finally{
try {
if(stmt!=null){
if(!stmt.isClosed()){
stmt.close();
stmt=null;
System.gc();
}
}
if(conn!=null){
if(!conn.isClosed()){
conn.close();
conn=null;
System.gc();
}
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}

/**
* 方法:根据数据库表生成VO–Value Object
*/
private void tableToVo(String tableName){

String className = null;
try {

Class.forName(DIVER_NAME);
conn = (Connection) DriverManager.getConnection(URL,USERNAME,PASSWORD);
stmt = (Statement) conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); //创建可滚动的,只读的结果集
sql = “SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT”+
” FROM INFORMATION_SCHEMA.COLUMNS”+
” WHERE table_name = ‘”+tableName+”‘”+ //表名
” AND table_schema = ‘”+DATABASE_NAME+”‘”; //数据库名
System.out.println(sql);
rs = stmt.executeQuery(sql);
rs.last(); //把指针指向结果集的最后
int fieldNum = rs.getRow(); //取得最后一条结果的行号,作为类的属性个数
int n = fieldNum;
if(n>0){ //判断数据表中是否存在字段
fields = new String[n];
dataTypes = new String[n];
comments = new String[n];
fields[–n] = rs.getString(1);
dataTypes[n] = rs.getString(2);
comments[n] = rs.getString(3);
while(rs.previous()){
fields[–n]=rs.getString(1); //取得结果集的第一列数据,对应的列名:Field
dataTypes[n] = rs.getString(2);
comments[n] = rs.getString(3);
}
//打印相关信息
System.out.println(“你要转换的表是:”+tableName);
System.out.println(“该表中共有”+fieldNum+”个字段,信息如下:”);
for(int i=0,j=fields.length;i<j;i++){
System.out.println(“—————————————-“);
String field = fields[i];
System.out.println(“字段名称:”+field);
//把字段名称格式化成java命名规则形式
//field=formatField(field);
fields[i]=field; //把格式化后的字段放入属性数组中
System.out.println(“数据类型:”+dataTypes[i]);
//把数据库字段类型转换成java数据类型
String dataType = dataTypes[i].toLowerCase();
dataType = formatDataType(dataType);
dataTypes[i] = dataType;

System.out.println(“字段注释:”+comments[i]);
if(“”.equals(comments[i])||comments[i]==null){
comments[i]=fields[i];
}
System.out.println(“—————————————-“);
}
//生成类文件,写入到磁盘中
writeObjectToFile(foramtClassName(className,tableName));
}else{
System.out.println(“该表不存在或者表中没有字段”);
}
} catch (ClassNotFoundException e) {
System.out.println(“未找到数据库驱动”);
System.out.println(e.getMessage());
} catch (SQLException e) {
System.out.println(e.getMessage());
//e.printStackTrace();
} finally{
try {
if(stmt!=null){
if(!stmt.isClosed()){
stmt.close();
stmt=null;
System.gc();
}
}
if(conn!=null){
if(!conn.isClosed()){
conn.close();
conn=null;
System.gc();
}
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}

/**
* 把数据库字段格式成java变量名
* @param field
* @return
*/
private String formatField(String field){
String[] strs = field.split(“_”);
field=””;
for(int m = 0,length=strs.length;m<length;m++){
if(m>0){
String tempStr = strs[m].toLowerCase();
tempStr = tempStr.substring(0, 1).toUpperCase()+tempStr.substring(1, tempStr.length());
field += tempStr;
}else{
field += strs[m].toLowerCase();
}
}
return field;
}

private String formatDataType(String sqlType){
if (sqlType.equalsIgnoreCase(“bit”)) {
return “Boolean”;
} else if (sqlType.equalsIgnoreCase(“tinyint”)||sqlType.equalsIgnoreCase(“varbinary”)) {
return “Byte”;
} else if (sqlType.equalsIgnoreCase(“smallint”)) {
return “Short”;
} else if (sqlType.equalsIgnoreCase(“int”)) {
return “Integer”;
} else if (sqlType.equalsIgnoreCase(“bigint”)) {
return “Long”;
} else if (sqlType.equalsIgnoreCase(“float”)) {
return “Float”;
} else if (sqlType.equalsIgnoreCase(“numeric”)
|| sqlType.equalsIgnoreCase(“real”) || sqlType.equalsIgnoreCase(“money”)
|| sqlType.equalsIgnoreCase(“smallmoney”)||sqlType.equalsIgnoreCase(“double”)) {
return “Double”;
} else if (sqlType.equalsIgnoreCase(“varchar”) || sqlType.equalsIgnoreCase(“char”)
|| sqlType.equalsIgnoreCase(“nvarchar”) || sqlType.equalsIgnoreCase(“nchar”)
|| sqlType.equalsIgnoreCase(“text”)|| sqlType.equalsIgnoreCase(“longtext”)) {
return “String”;
} else if (sqlType.equalsIgnoreCase(“datetime”)||sqlType.equalsIgnoreCase(“date”)) {
return “Date”;
} else if (sqlType.equalsIgnoreCase(“image”)) {
return “Blod”;
}else if (sqlType.equalsIgnoreCase(“decimal”)) {
return “BigDecimal”;
}
return sqlType;
}
/**
* 格式化类名
* @param className
* @param tableName
* @return
*/
private String foramtClassName(String className,String tableName){
//如果类名不是自己定义的,那么根据表名格式化类名
if(“”.equals(className)||className==null){
className=””;
String[] tempArr = tableName.split(“_”);
for(int m=0,length=tempArr.length;m<length;m++){
className += tempArr[m].substring(0,1).toUpperCase()+tempArr[m].substring(1,tempArr[m].length()).toLowerCase();
}
}else{
//如果类名已经输入,那么不管三七二十一就把类名的第一个字母大写
className = className.substring(0,1).toUpperCase()+className.substring(1,className.length());
}
return className;
}

/**
* 生成类并写到文件
* @param className
*/
@SuppressWarnings(“deprecation”)
private void writeObjectToFile(String className){
PrintWriter writer = null;
try {
File dir = new File(TARGET_DIR);
if(!dir.exists()){
dir.mkdirs();
}
File file = new File(TARGET_DIR+className+”.java”);
if(!file.exists()){
file.createNewFile();
}
writer = new PrintWriter(new BufferedWriter(new FileWriter(file)));

//拼文件内容
StringBuffer content = new StringBuffer();
content.append(“package “+PACKAGE_NAME+”;\n\n”);
// 判断是否导入工具包
if (needUtil) {
content.append(“import java.util.Date;\r\n”);
}
if (needSql) {
content.append(“import java.sql.*;\r\n”);
}

for (int i = 0; i < fields.length; i++) {
String hasbd = formatDataType(dataTypes[i]);
if(hasbd ==”BigDecimal” || “BigDecimal”.equals(hasbd)) {needBigDecimal=true;}
}
if(needBigDecimal) {
content.append(“import java.math.BigDecimal;\r\n”);
}

content.append(“/**\n”);
content.append(” *@类:”+className+”\n”);
content.append(” *@作者:”+AUTHOR+”\n”);
content.append(” *@E-mail:”+E_MAIL+”\n”);
content.append(” *@日期:”+new Date().toLocaleString().substring(0,10)+”\n”);
content.append(” */\n\n”);

// content.append(“@Data\n\n”);

content.append(“public class “+className+”{\n\n”);
for(int i=0,j=fields.length;i<j;i++){
content.append(” /**”+comments[i]+”*/\n”);
content.append(” private “+dataTypes[i]+” “+fields[i]+”;\n\n”);
}
//get set 方法
for(int i=0,j=fields.length;i<j;i++){
content.append(” /**\n”);
content.append(” *方法: 取得”+fields[i]+”\n”);
content.append(” *@return: “+dataTypes[i]+” “+fields[i]+”\n”);
content.append(” */\n”);
content.append(” public “+dataTypes[i]+” get”+fields[i].substring(0,1).toUpperCase()+fields[i].substring(1,fields[i].length())+”(){\n”);
content.append(” return this.”+fields[i]+”;\n”);
content.append(” }\n\n”);
content.append(” /**\n”);
content.append(” *方法: 设置”+fields[i]+”\n”);
content.append(” *@param: “+dataTypes[i]+” “+fields[i]+”\n”);
content.append(” */\n”);
content.append(” public void set”+fields[i].substring(0,1).toUpperCase()+fields[i].substring(1,fields[i].length())+”(“+dataTypes[i]+” “+fields[i]+”){\n”);
content.append(” this.”+fields[i]+” = “+fields[i]+”;\n”);
content.append(” }\n\n”);
}
content.append(“}”);
writer.write(content.toString());
writer.flush();
System.out.println(“类生成成功，存放路径：”+TARGET_DIR);
} catch (IOException e) {
e.printStackTrace();
}finally{
if(writer!=null){
writer.close();
}
}
}

public void createList() {

try {

Class.forName(DIVER_NAME);
conn = (Connection) DriverManager.getConnection(URL,USERNAME,PASSWORD);
stmt = (Statement) conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); //创建可滚动的,只读的结果集
sql = “SELECT DISTINCT table_name”+
” FROM INFORMATION_SCHEMA.COLUMNS”+
” WHERE table_schema = ‘”+DATABASE_NAME+”‘”; //数据库名
System.out.println(sql);
rs = stmt.executeQuery(sql);
rs.last(); //把指针指向结果集的最后
int fieldNum = rs.getRow(); //取得最后一条结果的行号,作为类的属性个数
int n = fieldNum;
if(n>0){ //判断数据表中是否存在字段
fields = new String[n];
while(rs.previous()){
fields[–n]=rs.getString(1); //取得结果集的第一列数据,对应的列名:Field

}
//打印相关信息
System.out.println(“该表中共有”+fieldNum+”个字段,信息如下:”);
for(int i=0,j=fields.length;i<j;i++){
System.out.println(“—————————————-“);
String field = fields[i];
new DBToJavaVO().tableToVo(field);
System.out.println(“—————————————-“);
}
}else{
System.out.println(“该表不存在或者表中没有字段”);
}
} catch (ClassNotFoundException e) {
System.out.println(“未找到数据库驱动”);
System.out.println(e.getMessage());
} catch (SQLException e) {
System.out.println(e.getMessage());
//e.printStackTrace();
} finally{
try {
if(stmt!=null){
if(!stmt.isClosed()){
stmt.close();
stmt=null;
System.gc();
}
}
if(conn!=null){
if(!conn.isClosed()){
conn.close();
conn=null;
System.gc();
}
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}

public static void main(String[] args) {
try {
new DBToJavaVO().createList();
//new DBToJavaVO().tableToVo();
} catch (Exception e) {
System.out.println(e.getMessage());
}

}

}

3人评论了“JDBC原生链接Mysql数据根据表映射生成bean实体类”

今日头条新闻
2021年2月23日在 12:23 上午

文章不错支持一下吧

回复
iMJMJ
2021年9月25日在 12:48 下午

顶顶顶！！！学习了~

回复
雷达探币
2021年11月16日在 6:43 下午

写什么都不重要，因为写什么都有人看。贵在坚持，谢谢分享

回复

发表评论取消回复