关注不迷路,各种自创工具源码全部开放,和我一起学写工具类的思路及方法,做个工具小达人
最近开发一个新的系统,需要把一个库中几十张mysql的表转换为java实体类,作为一个开发,肯定不可能用最笨的方法去一个个手动添加,我之前用mybatis的一个工具实现过,但是感觉配置较为麻烦,就自己写了个工具类来实现该功能,执行完后的效果截图如下所示:
1、首先是查某个数据库中的所有表,把所有表名放到一个集合里面;
2、遍历该集合,根据表名去查询数据,把每张表的所有字段名查出来;
3、然后进行字符串拼接,中间加了个package包名的截取,整合数据;
4、最后用输出流,把整合的数据写如到指定文件中。
该方法只需要把mysql的ip地址及端口号、mysql用户名及密码、数据库名字传进去,直接就能生成所有表的实体类,该代码中包含main方法,修改main方法中的参数即可直接使用,但是需要引入一个mysql-connect的jar包,具体代码直接附上:
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
public class TableToDto {
//mysql的ip地址:端口号
private String url;
//mysql的用户名
private String userName;
//mysql的密码
private String passWord;
//连接的数据库
private String dataBase;
//连接的表集合
private ArrayList<String> tableNameList;
//实体类放置的文件夹
private String filePath;
public String getFilePath() {
return filePath;
}
public void setFilePath(String filePath) {
this.filePath = filePath;
}
public TableToDto() {
}
//有参构造方法
public TableToDto(String url, String userName, String passWord, String dataBase,
ArrayList<String> tableNameList) {
this.url = url;
this.userName = userName;
this.passWord = passWord;
this.dataBase = dataBase;
this.tableNameList = tableNameList;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassWord() {
return passWord;
}
public void setPassWord(String passWord) {
this.passWord = passWord;
}
public String getDataBase() {
return dataBase;
}
public void setDataBase(String dataBase) {
this.dataBase = dataBase;
}
public ArrayList<String> gettableNameList() {
return tableNameList;
}
public void settableNameList(ArrayList<String> tableNameList) {
this.tableNameList = tableNameList;
}
public void tableToExcel(){
String demo01=
"import lombok.AllArgsConstructor;\n" +
"import lombok.Data;\n" +
"import lombok.NoArgsConstructor;\n\n" +
"@Data\n" +
"@AllArgsConstructor\n" +
"@NoArgsConstructor\n" +
"public class ";
String demo02="{\n";
String demo03="}";
File file=new File(filePath);
if (!file.exists()){
file.mkdirs();
}
//数据库连接路径
url = "jdbc:mysql://"+url+"/"+dataBase+"?characterEncoding=utf-8";
//数据库连接
Connection connection = null;
//预编译处理
PreparedStatement ps = null;
//结果集
ResultSet rs = null;
try {
//通过类加载机制,加载MySQL数据库连接驱动
Class.forName("com.mysql.jdbc.Driver");
//连接数据库
connection = DriverManager.getConnection(url, userName, passWord);
//遍历表名的集合
ArrayList<String> tableNameListFinal=new ArrayList<>();
if (tableNameList.size()==1 && tableNameList.get(0).equals("AllTable")){
String sqlFindAllTableName="show tables;";
//对SQL语句进行预编译
ps = connection.prepareStatement(sqlFindAllTableName);
//执行SQL,获取结果集
rs = ps.executeQuery();
//遍历结果集
while (rs.next()) {
tableNameListFinal.add(rs.getString(1));
}
}else {
tableNameListFinal=tableNameList;
}
for (String tableName : tableNameListFinal) {
//用分割src把package拿到,如果是springboot项目,则换为java
String splitToPackAge="src";
String packAge=filePath.substring(filePath.lastIndexOf(splitToPackAge)+splitToPackAge.length()+1);
while (packAge.contains("\\") || packAge.contains("/")){
packAge=packAge.replace("\\",".").replace("/",".");
}
StringBuilder AllString= new StringBuilder("package "+packAge+";\n\n"+demo01 + tableName+"DTO" + demo02);
//查询该表的所有字段信息
String sqlFindTableName="select COLUMN_NAME,DATA_TYPE from information_schema.COLUMNS" +
" where table_name = ? and table_schema = ? ";
//对SQL语句进行预编译
ps = connection.prepareStatement(sqlFindTableName);
//对SQL语句中的占位符进行设置
ps.setString(1, tableName);
ps.setString(2, dataBase);
//执行SQL,获取结果集
rs = ps.executeQuery();
//遍历结果集
while (rs.next()) {
//该表的所有字段信息,用','分开
String COLUMN_NAME=rs.getString(1);
String DATA_TYPE=rs.getString(2).toLowerCase();
String replace="";
switch (DATA_TYPE){
case "char":
case "varchar":
case "text":
case "longtext":
replace="String" ;
break;
case "int": replace="Long" ;
break;
case "tinyint":
case "samllint": replace="Integer" ;
break;
case "float": replace="Float" ;
break;
case "double": replace="Double" ;
break;
case "date": replace="Date" ;
break;
case "time": replace="Time" ;
break;
}
String line="\tprivate "+replace+" "+COLUMN_NAME+";\n";
AllString.append(line);
}
AllString.append(demo03);
//获得读写流
FileWriter fw= new FileWriter(filePath+"/"+tableName+"DTO.java");
BufferedWriter bw=new BufferedWriter(fw);
bw.write(AllString.toString());
bw.flush();
bw.close();
fw.close();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//释放资源
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void main(String[] args) {
ArrayList<String> list=new ArrayList<String>();
Collections.addAll(list,"AllTable");
TableToDto tableToExcel0421=new TableToDto("localhost:3306",
"root","suixin1022","yh00",list);
tableToExcel0421.setFilePath("D:\\Users\\untitled\\src\\com\\dto");
tableToExcel0421.tableToExcel();
}
}
为了更加方便,我还打了个jar包,可以直接运行jar包,其中指定了jar包执行的main方法,具体代码如下:
import java.util.ArrayList;
public class Main {
public static void main(String[] args) {
if (args.length>=6){
ArrayList<String> list=new ArrayList<String>();
String url=args[0];String dataBase=args[1];
String userName=args[2];String passWord=args[3];
String filePath=args[4];
for (int i = 5; i < args.length; i++) {
list.add(args[i]);
}
TableToDto tableToDto=new TableToDto(url,
userName,passWord,dataBase,list);
tableToDto.setFilePath(filePath);
tableToDto.tableToExcel();
System.out.println("TableToDto success");
}else {
System.out.println("Not have table name!");
}
}
}
cmd执行jar包的方法以及执行结果如下所示:
原创文章,转载请备注出处