com.mycomm.dao.dao4comm.annotation.dialect.mysql.MysqlSqlBuilder Maven / Gradle / Ivy
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package com.mycomm.dao.dao4comm.annotation.dialect.mysql;
import com.mycomm.IProtocol.beans.JDataTypes;
import com.mycomm.IProtocol.beans.MySqlCharSet;
import com.mycomm.IProtocol.sql.annotation.MyColumn;
import com.mycomm.IProtocol.sql.annotation.MyId;
import com.mycomm.IProtocol.sql.annotation.MyTable;
import com.mycomm.IProtocol.sql.annotation.UniversalDBColumType;
import com.mycomm.dao.dao4comm.annotation.dialect.ColumTypeDetector;
import com.mycomm.dao.dao4comm.annotation.dialect.DataBaseType;
import com.mycomm.dao.dao4comm.annotation.dialect.DialectConfiguration;
import com.mycomm.dao.dao4comm.annotation.dialect.DialectHandlerSelector;
import com.mycomm.dao.dao4comm.annotation.dialect.FieldTypeDetector;
import com.mycomm.dao.dao4comm.annotation.dialect.SqlBuilder;
import com.mycomm.dao.dao4comm.util.ConstantsKeeper;
import java.lang.reflect.Field;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
*
* @author jw362j
*/
public class MysqlSqlBuilder implements SqlBuilder {
private static final Logger log = LoggerFactory.getLogger(MysqlSqlBuilder.class);
/**
*
*
*
* CREATE TABLE `showable` ( `id` bigint(20) NOT NULL AUTO_INCREMENT,
* `author` varchar(255) DEFAULT NULL, `catagory` varchar(255) NOT NULL,
* `catagory_order` int(11) NOT NULL DEFAULT '0', `cnt` int(11) DEFAULT
* NULL, `content` longtext, `createTime` datetime DEFAULT NULL,
* `description` varchar(255) DEFAULT NULL, `imgname` varchar(255) DEFAULT
* NULL, `title` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY
* `id` (`id`) )ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8
*
* @param tableName
* @param myTable
* @param fields
* @return
*/
public String doCreateTable(String tableName, MyTable myTable, Field[] fields) {
if ("".equals(tableName) || tableName == null || myTable == null || fields == null || fields.length <= 0) {
return null;
}
String mytableName = tableName;
log.info("theTableName is:" + mytableName);
String tableIdColumName = "";
for (Field f : fields) {
MyId ff = f.getAnnotation(MyId.class);
if (ff == null) {
continue;
}
tableIdColumName = ff.IdColumName();
if ("".equals(tableIdColumName) || tableIdColumName == null) {
tableIdColumName = f.getName();
}
break;
}
String sqlCreateTable = "CREATE TABLE IF NOT EXISTS " + ConstantsKeeper.quotation_mark + mytableName + ConstantsKeeper.quotation_mark + " ( " + ConstantsKeeper.quotation_mark + tableIdColumName + ConstantsKeeper.quotation_mark + " bigint NOT NULL AUTO_INCREMENT,";
String colName;
String colType;
MySqlCharSet universalCharSet = myTable.tableCharset();
String tableCharset = DialectHandlerSelector.selectTableCharSetSelector(DialectConfiguration.dbType).selectCharSet(universalCharSet);
String tableEngine = myTable.tableEngine().toString();
String uniqueColum = null;
for (Field field : fields) {
if (field.isAnnotationPresent(MyId.class)) {
continue;
}
field.setAccessible(true);
if (field.isAnnotationPresent(MyColumn.class)) {
MyColumn myColumn = (MyColumn) field.getAnnotation(MyColumn.class);
//initial for columName
colName = myColumn.ColumnName();
if ("".equals(colName) || colName == null) {
colName = field.getName();
}
colName = ConstantsKeeper.quotation_mark + colName + ConstantsKeeper.quotation_mark + " ";
sqlCreateTable += colName;
//initial for columType
colType = DialectHandlerSelector.selectColumDialectHandler(DataBaseType.MYSQL).getColumType(myColumn.ColumnType());
if (UniversalDBColumType.DBColumNull.equals(UniversalDBColumType.fromValue(colType)) || "".equals(colType) || colType == null) {
//use the fiels`s data type
JDataTypes jDataType = FieldTypeDetector.getDataType(field);
ColumTypeDetector hand = DialectHandlerSelector.selectColumDialectHandler(DialectConfiguration.dbType);
colType = hand.getColumType(jDataType);
}
sqlCreateTable = sqlCreateTable + " " + colType + " ";
if (myColumn.isColumnNullable()) {
sqlCreateTable += ",";
}
//initial for colNullable
if (!myColumn.isColumnNullable()) {
if (field.getType().toString().contains("String") || field.getType().toString().contains("char")) {
sqlCreateTable = sqlCreateTable + " NOT NULL DEFAULT " + ConstantsKeeper.quotation_mark + myColumn.ColumnDefaultValue() + ConstantsKeeper.quotation_mark + ",";
} else {
sqlCreateTable = sqlCreateTable + " NOT NULL DEFAULT " + myColumn.ColumnDefaultValue() + ",";
}
}
if(myColumn.isColumnUnique()){
uniqueColum = colName;
}
}
}
// UNIQUE KEY `id` (`id`)
sqlCreateTable = sqlCreateTable + " PRIMARY KEY ( " + ConstantsKeeper.quotation_mark + tableIdColumName + ConstantsKeeper.quotation_mark + ") "+( uniqueColum == null ? "": " , UNIQUE KEY "+uniqueColum +" ("+uniqueColum+")" )+")ENGINE=" + tableEngine + " DEFAULT CHARSET=" + tableCharset;
if (DialectConfiguration.printSQL) {
log.info(sqlCreateTable);
}
return sqlCreateTable;
}
public String doInsertInToTable(String tableName, Field[] fields) {
if ("".equals(tableName) || tableName == null || fields == null || fields.length <= 0) {
return null;
}
String mytableName = tableName;
log.info("theTableName is:" + mytableName);
final StringBuffer sql_saveList = new StringBuffer("INSERT INTO " + mytableName + "(");
int fieldCounter = 0;
for (Field field : fields) {
field.setAccessible(true);
if (field.isAnnotationPresent(MyId.class)) {
continue;
}
if (field.isAnnotationPresent(MyColumn.class)) {
MyColumn myColumn = (MyColumn) field.getAnnotation(MyColumn.class);
String columName = myColumn.ColumnName();
if ("".equals(columName) || columName == null) {
columName = field.getName();
}
sql_saveList.append(ConstantsKeeper.quotation_mark).append(columName).append(ConstantsKeeper.quotation_mark).append(",");
fieldCounter++;
}
}
sql_saveList.deleteCharAt(sql_saveList.length() - 1);
sql_saveList.append(") VALUES(");
for (int i = 0; i < fieldCounter; i++) {
if (i != fieldCounter - 1) {
sql_saveList.append(" ? ,");
} else {
sql_saveList.append(" ? )");
}
}
log.info("the doInsertInToTable sql===:" + sql_saveList);
if (DialectConfiguration.printSQL) {
log.info(sql_saveList.toString());
}
if (sql_saveList == null) {
throw new RuntimeException("sql_save insert is null");
}
return sql_saveList.toString();
}
public String doUpdateTable(String tableName, Field[] fields, String IdFieldName) {
if ("".equals(tableName) || tableName == null || fields == null || fields.length <= 0 || IdFieldName == null || IdFieldName.length() <= 0) {
return null;
}
StringBuilder sql_update = new StringBuilder("UPDATE " + tableName + " SET ");
int firtIndex = 1;
for (Field field : fields) {
field.setAccessible(true);
if (field.isAnnotationPresent(MyColumn.class)) {
MyColumn myColumn = (MyColumn) field.getAnnotation(MyColumn.class);
String columName = myColumn.ColumnName();
Object columValue = null;
if ("".equals(columName) || columName == null) {
columName = field.getName();
}
sql_update.append(ConstantsKeeper.quotation_mark).append(columName).append(ConstantsKeeper.quotation_mark + " = ? ,");
}
}
sql_update.deleteCharAt(sql_update.length() - 1);
sql_update.append(" WHERE ").append(IdFieldName).append("=?");
if (DialectConfiguration.printSQL) {
log.info(sql_update.toString());
}
return sql_update.toString();
}
}