TableUtil
package com.example.hutool;
import org.hamcrest.beans.PropertyUtil;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class TableUtil {
private static String url = null;
private static String user = null;
private static String passwd = null;
private static String driver = null;
static {
Properties properties = null;
try {
properties = new Properties();
InputStream in = PropertyUtil.class.getClassLoader().getResourceAsStream("procedure.properties");
assert in != null;
BufferedReader bf = new BufferedReader(new InputStreamReader(in));
properties.load(bf);
in.close();
} catch (Exception ex) {
ex.printStackTrace();
}
assert properties != null;
url = properties.getProperty("url");
user = properties.getProperty("user");
passwd = properties.getProperty("passwd");
driver = properties.getProperty("driver");
}
public static TableExecuteResult createTable(Table table){
StringBuilder createTableSentence = new StringBuilder("CREATE TABLE ").append(table.getTableName()).append(" (").append("\n");
List<Field> fields = table.getFields();
List<Field> primaryKeys = new ArrayList<>();
for (Field field : fields) {
if(field.getPrimaryKey()){
primaryKeys.add(field);
}
createTableSentence.append("`")
.append(field.getFieldName())
.append("`")
.append(" ")
.append(field.getType())
.append("(")
.append(field.getLength());
if(null == field.getDecimals()){
createTableSentence.append(")");
}else{
createTableSentence.append(",").append(field.getDecimals()).append(")");
}
if(field.getNotNull()){
createTableSentence.append(" ")
.append("NOT NULL");
}
if(field.getAutoIncrement()){
createTableSentence.append(" ")
.append("AUTO_INCREMENT");
}
if(field.getUnique()){
createTableSentence.append(" ")
.append("UNIQUE");
}
if(null != field.getDescribe() && !"".equals(field.getDescribe())){
createTableSentence.append(" ").append("COMMENT '").append(field.getDescribe()).append("'");
}
createTableSentence.append(",").append("\n");
}
for (Field primaryKey : primaryKeys) {
createTableSentence.append("PRIMARY KEY (`").append(primaryKey.getFieldName()).append("`)");
createTableSentence.append(",").append("\n");
}
createTableSentence.deleteCharAt(createTableSentence.length() - 2);
createTableSentence.append(") ");
createTableSentence.append("ENGINE=").append(table.getEngine()).append(" ");
createTableSentence.append("CHARSET=").append(table.getCharset()).append(" ");
createTableSentence.append("COLLATE=").append(table.getCollate());
return executeSQL(createTableSentence.toString());
}
public static TableExecuteResult dropTable(Table table){
return executeSQL("DROP TABLE IF EXISTS `"+table.getTableName()+"`;");
}
public static TableExecuteResult addField(Table table){
StringBuilder addFieldSentences = new StringBuilder();
List<String> primaryKeyName = new ArrayList<>();
for (Field field : table.getFields()) {
StringBuilder addFieldSentence = new StringBuilder("ALTER TABLE `"+table.getTableName()+"` ADD ");
if(field.getPrimaryKey()){
primaryKeyName.add(field.getFieldName());
}
addFieldSentence.append("`")
.append(field.getFieldName())
.append("`")
.append(" ")
.append(field.getType())
.append("(")
.append(field.getLength());
if(null == field.getDecimals()){
addFieldSentence.append(")");
}else{
addFieldSentence.append(",").append(field.getDecimals()).append(")");
}
if(field.getNotNull()){
addFieldSentence.append(" ")
.append("NOT NULL");
}
if(field.getAutoIncrement()){
addFieldSentence.append(" ")
.append("AUTO_INCREMENT");
}
if(field.getUnique()){
addFieldSentence.append(" ")
.append("UNIQUE");
}
if(null != field.getDescribe() && !"".equals(field.getDescribe())){
addFieldSentence.append(" ").append("COMMENT '").append(field.getDescribe()).append("'");
}
addFieldSentence.append(";\n");
addFieldSentences.append(addFieldSentence);
}
for (String primaryKey : primaryKeyName) {
addFieldSentences.append("ALTER TABLE `")
.append(table.getTableName())
.append("` ADD PRIMARY KEY(`")
.append(primaryKey).append("`)");
}
return executeSQL(addFieldSentences.toString());
}
public static TableExecuteResult dropField(Table table){
StringBuilder addFieldSentences = new StringBuilder();
for (Field field : table.getFields()) {
String addFieldSentence = "ALTER TABLE `" + table.getTableName() + "` DROP " + "`" + field.getFieldName() + "`" +
";\n";
addFieldSentences.append(addFieldSentence);
}
return executeSQL(addFieldSentences.toString());
}
public static TableExecuteResult updateField(Table table){
StringBuilder addFieldSentences = new StringBuilder();
for (Field field : table.getFields()) {
StringBuilder addFieldSentence = new StringBuilder("ALTER TABLE `"+table.getTableName()+"` MODIFY COLUMN ");
addFieldSentence.append("`")
.append(field.getFieldName())
.append("`")
.append(" ")
.append(field.getType())
.append("(")
.append(field.getLength());
if(null == field.getDecimals()){
addFieldSentence.append(")");
}else{
addFieldSentence.append(",").append(field.getDecimals()).append(")");
}
if(field.getNotNull()){
addFieldSentence.append(" ")
.append("NOT NULL");
}
if(field.getAutoIncrement()){
addFieldSentence.append(" ")
.append("AUTO_INCREMENT");
}
if(field.getUnique()){
addFieldSentence.append(" ")
.append("UNIQUE");
}
if(null != field.getDescribe() && !"".equals(field.getDescribe())){
addFieldSentence.append(" ").append("COMMENT '").append(field.getDescribe()).append("'");
}
addFieldSentence.append(";");
addFieldSentences.append(addFieldSentence);
if(null != field.getOriginalFieldName() && !"".equals(field.getOriginalFieldName())){
addFieldSentences.append("ALTER TABLE `")
.append(table.getTableName())
.append("` CHANGE `")
.append(field.getOriginalFieldName())
.append("` `")
.append(field.getFieldName())
.append("` ");
addFieldSentences.append(field.getType())
.append("(")
.append(field.getLength());
if(null == field.getDecimals()){
addFieldSentence.append(")");
}else{
addFieldSentence.append(",").append(field.getDecimals()).append(");");
}
}
}
return executeSQL(addFieldSentences.toString());
}
private static TableExecuteResult executeSQL(String sql){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, user, passwd);
preparedStatement = connection.prepareStatement(sql);
preparedStatement.execute();
} catch (SQLException se) {
return new TableExecuteResult(false, se.getErrorCode(), se.getMessage());
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (null != connection) {
connection.close();
}
if (null != preparedStatement) {
preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return new TableExecuteResult(true, 0, null);
}
}
Table
package com.example.hutool;
import java.util.List;
public class Table {
private String tableName;
private String engine = "InnoDB";
private String charset = "utf8";
private String collate = "utf8_unicode_ci";
private List<Field> fields;
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getEngine() {
return engine;
}
public void setEngine(String engine) {
this.engine = engine;
}
public String getCharset() {
return charset;
}
public void setCharset(String charset) {
this.charset = charset;
}
public String getCollate() {
return collate;
}
public void setCollate(String collate) {
this.collate = collate;
}
public List<Field> getFields() {
return fields;
}
public void setFields(List<Field> fields) {
this.fields = fields;
}
}
Field
package com.example.hutool;
import java.util.HashSet;
import java.util.Set;
public class Field {
private static final Set<String> floatNumType = new HashSet<>();
private String fieldName;
private String originalFieldName;
private Boolean primaryKey = false;
private Boolean autoIncrement = false;
private Boolean notNull = false;
private Boolean unique = false;
private String type;
private Integer length = 0;
private Integer decimals;
private String describe;
static {
floatNumType.add("float");
floatNumType.add("double");
floatNumType.add("decimal");
}
public Field() {
correctParameters();
}
public Field(String fieldName, String originalFieldName, Boolean primaryKey, Boolean autoIncrement, Boolean notNull, Boolean unique, String type, Integer length, Integer decimals, String describe) {
this.fieldName = fieldName;
this.originalFieldName = originalFieldName;
this.primaryKey = primaryKey;
this.autoIncrement = autoIncrement;
this.notNull = notNull;
this.unique = unique;
this.type = type;
this.length = length;
this.decimals = decimals;
this.describe = describe;
correctParameters();
}
private void correctParameters(){
String type = getType();
if(floatNumType.contains(type)){
if(null == getDecimals()){
setDecimals(0);
}
}else{
if(null != getDecimals()){
setDecimals(null);
}
}
}
public Boolean getPrimaryKey() {
return primaryKey;
}
public void setPrimaryKey(Boolean primaryKey) {
this.primaryKey = primaryKey;
}
public Boolean getAutoIncrement() {
return autoIncrement;
}
public void setAutoIncrement(Boolean autoIncrement) {
this.autoIncrement = autoIncrement;
}
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public Boolean getNotNull() {
return notNull;
}
public void setNotNull(Boolean notNull) {
this.notNull = notNull;
}
public Boolean getUnique() {
return unique;
}
public void setUnique(Boolean unique) {
this.unique = unique;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public Integer getLength() {
return length;
}
public void setLength(Integer length) {
this.length = length;
}
public Integer getDecimals() {
return decimals;
}
public void setDecimals(Integer decimals) {
this.decimals = decimals;
}
public String getDescribe() {
return describe;
}
public void setDescribe(String describe) {
this.describe = describe;
}
public String getOriginalFieldName() {
return originalFieldName;
}
public void setOriginalFieldName(String originalFieldName) {
this.originalFieldName = originalFieldName;
}
}
TableExecuteResult
package com.example.hutool;
import java.util.List;
import java.util.Map;
class TableExecuteResult {
private Boolean success;
private int errCode;
private String reason;
public TableExecuteResult() {
}
public TableExecuteResult(Boolean success, int errCode, String reason) {
this.success = success;
this.errCode = errCode;
this.reason = reason;
}
public Boolean getSuccess() {
return success;
}
public void setSuccess(Boolean success) {
this.success = success;
}
public int getErrCode() {
return errCode;
}
public void setErrCode(int errCode) {
this.errCode = errCode;
}
public String getReason() {
return reason;
}
public void setReason(String reason) {
this.reason = reason;
}
}
procedure.properties
url=jdbc:mysql://192.168.0.106:3306/ccgc?characterEncoding=utf8&useOldAliasMetadataBehavior=true&allowMultiQueries=true
user=root
passwd=root
driver=com.mysql.jdbc.Driver