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

java技术 江风成 2021-01-28 07:01 评论 3 阅读 3,495 　

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

 

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());
}

}

}

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

*文章为作者独立观点，不代表上流阁立场
 本文由 江风成 授权 上流阁 发表，并经上流阁编辑。转载此文章须经作者同意，并请附上出处(上流阁)及本页链接。原文链接https://www.o6c.com/java/2021/01/28/1461.html
发表评论

3 条评论
  • 板凳 雷达探币　 2年前 (2021-11-16)

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

    回复
  • 椅子 iMJMJ　 3年前 (2021-09-25)

    顶顶顶！！！学习了~

    回复
  • 沙发 今日头条新闻　 3年前 (2021-02-22)

    文章不错支持一下吧

    回复
相关文章
jar包解压后，修改完配置文件，再还原成jar包
jar包解压后，修改完配置文件，再还原成…
JAVA DES 十六进制加密
JAVA DES 十六进制加密
HTML中Data URI scheme BASE64 文件的拼接头
HTML中Data URI scheme BASE64 文件的拼…
mysql 优化方案整理
mysql 优化方案整理
windows BAT 文件 多个命令同步执行
windows BAT 文件 多个命令同步执行
java 中 封装BigDecimal常用计算类MathUtils
java 中 封装BigDecimal常用计算类MathU…
江风成 站长
javaweb开发程序员php开发，微信开发。接受定制开发

随机文章

最新评论

热门标签