|
如果在项目中使用到了mybatis,那么就需要编写与数据库表对应的javabean对象,还有数据库表的数据列和javabean对象中字段的映射配置,以及一些通用的插入更新数据的配置,这些都可以通过工具自动生成。
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import com.cogcn.framework.util.CommonUtil;
/**
* 把数据库中的表转化为java对象
*
* @author tuozixuan
*
*/
public class TableToJavaTool
{
private static final String DRIVER_NAME_ORACLE = "oracle.jdbc.driver.OracleDriver";
// 数据库连接-用户名
private String user;
// 数据库连接-密码
private String password;
// 数据库连接-URL
private String dbUrl;
// 表名
private String tableName;
// 表类别名称
private String catalog;
private final List<String> primaryKeyList = new ArrayList<String>();
private List<Map<String, Object>> dataList = null;
public TableToJavaTool()
{
}
public TableToJavaTool(String user, String password, String dbUrl, String tableName)
{
this.user = user;
this.password = password;
this.dbUrl = dbUrl;
this.tableName = tableName;
}
public void process()
{
dataList = readData(getTableName());
createJavaBeanFile(getTableName());
createMybatisColumnConfig(getTableName());
}
/**
* 获取数据库连接
*
* @return Connection 数据库连接对象
*/
private Connection getConnection()
{
Connection conn = null;
try
{
Properties props = new Properties();
props.put("remarksReporting", "true");
props.put("user", getUser());
props.put("password", getPassword());
Class.forName(DRIVER_NAME_ORACLE);
conn = DriverManager.getConnection(getDbUrl(), props);
}
catch (Exception e)
{
e.printStackTrace();
}
return conn;
}
/**
* 获取数据库指定表的列信息
*
* @param tableName 表名
* @return List<Map<String, Object>> 列信息列表
*/
private List<Map<String, Object>> readData(String tableName)
{
Connection conn = getConnection();
List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
try
{
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getColumns(getCatalog(), null, tableName, null);
Map<String, Object> map = null;
while (rs.next())
{
map = new HashMap<String, Object>();
map.put("columnName", rs.getString("COLUMN_NAME"));
map.put("dataType", rs.getInt("DATA_TYPE"));
map.put("remarks", rs.getString("REMARKS"));
dataList.add(map);
}
ResultSet rs1 = dbmd.getPrimaryKeys(getCatalog(), null, tableName);
while (rs1.next())
{
primaryKeyList.add(rs1.getString("COLUMN_NAME"));
}
}
catch (SQLException e)
{
e.printStackTrace();
} finally {
CommonUtil.closeConnection(conn);
}
return dataList;
}
public void createJavaBeanFile(String tableName)
{
StringBuffer jbString = new StringBuffer();
jbString.append("public class ").append(tableName).append("\r\n");
jbString.append("{").append("\r\n");
if (dataList != null)
{
for (Map<String, Object> map : dataList)
{
String fieldName = getFieldName((String) map.get("columnName"));
String javaType = getJavaType((Integer) map.get("dataType"));
jbString.append(" // ").append(map.get("remarks")).append("\r\n");
jbString.append(" private ").append(javaType).append(" ").append(fieldName).append(";").append("\r\n");
jbString.append("\r\n");
}
for (Map<String, Object> map : dataList)
{
String fieldName = getFieldName((String) map.get("columnName"));
String javaType = getJavaType((Integer) map.get("dataType"));
jbString.append(" public ").append(javaType).append(" get").append(firstUpperCase(fieldName)).append("()").append("\r\n");
jbString.append(" {").append("\r\n");
jbString.append(" return ").append(fieldName).append(";").append("\r\n");
jbString.append(" }").append("\r\n");
jbString.append("\r\n");
jbString.append(" public void set").append(firstUpperCase(fieldName)).append("(").append(javaType).append(" ").append(fieldName)
.append(")").append("\r\n");
jbString.append(" {").append("\r\n");
jbString.append(" this.").append(fieldName).append(" = ").append(fieldName).append(";").append("\r\n");
jbString.append(" }").append("\r\n");
jbString.append("\r\n");
}
}
jbString.append("}");
System.out.println(jbString.toString());
}
/**
* 根据表名获取对应的JavaBean的名称<br/>
*
*
* @param tableName 表名
* @return String JavaBean
*/
public static String getJavaBeanName(String tableName)
{
return tableName;
}
/**
* 把以_分隔的列明转化为字段名
*
* @param columnName 列名
* @return String 字段名
*/
private static String getFieldName(String columnName)
{
if (columnName == null)
{
return "";
}
StringBuffer fieldNameBuffer = new StringBuffer();
boolean nextUpperCase = false;
columnName = columnName.toLowerCase();
for (int i = 0; i < columnName.length(); i++)
{
char c = columnName.charAt(i);
if (nextUpperCase)
{
fieldNameBuffer.append(columnName.substring(i, i + 1).toUpperCase());
}
else
{
fieldNameBuffer.append(c);
}
if (c == '_')
{
nextUpperCase = true;
}
else
{
nextUpperCase = false;
}
}
String fieldName = fieldNameBuffer.toString();
fieldName = fieldName.replaceAll("_", "");
return fieldName;
}
/**
* 字符串的第一个字母大写
*
* @param str 字符串
* @return String 处理后的字符串
*/
private static String firstUpperCase(String str)
{
if (str == null)
{
return "";
}
if (str.length() == 1)
{
str = str.toUpperCase();
}
else
{
str = str.substring(0, 1).toUpperCase() + str.substring(1);
}
return str;
}
/**
* 将数据库列类型转换为java数据类型
*
* @param dataType 列类型
* @return String java数据类型
*/
private static String getJavaType(int dataType)
{
String javaType = "";
if (dataType == Types.INTEGER)
{
javaType = "int";
}
else if (dataType == Types.BIGINT)
{
javaType = "long";
}
else if (dataType == Types.CHAR || dataType == Types.VARCHAR || dataType == Types.NVARCHAR || dataType == Types.CLOB)
{
javaType = "String";
}
else if (dataType == Types.TINYINT)
{
javaType = "short";
}
else if (dataType == Types.FLOAT)
{
javaType = "float";
}
else if (dataType == Types.NUMERIC || dataType == Types.DECIMAL || dataType == Types.DOUBLE)
{
javaType = "double";
}
else if (dataType == Types.DATE || dataType == Types.TIMESTAMP)
{
javaType = "Date";
}
return javaType;
}
public void createMybatisColumnConfig(String tableName)
{
StringBuffer buffer = new StringBuffer();
if (dataList != null)
{
buffer.append("<resultMap id=\"BaseResultMap\" type=\"").append(tableName).append("\"> ").append("\r\n");
for (Map<String, Object> map : dataList)
{
// <result column="CI_TYP" jdbcType="CHAR" property="ciTyp" />
String columnName = (String) map.get("columnName");
String fieldName = getFieldName(columnName);
String jdbcType = getMybatisJdbcType((Integer) map.get("dataType"));
if (primaryKeyList.contains(columnName))
{
buffer.append(" <id column=\"").append(columnName).append("\" ").append("jdbcType=\"").append(jdbcType)
.append("\" property=\"").append(fieldName).append("\" />").append("\r\n");
} else {
buffer.append(" <result column=\"").append(columnName).append("\" ").append("jdbcType=\"").append(jdbcType)
.append("\" property=\"").append(fieldName).append("\" />").append("\r\n");
}
}
buffer.append("</resultMap>").append("\r\n");
}
buffer.append("<sql id=\"BaseColumnList\">").append("\r\n");
int length = dataList.size();
int count = 0;
buffer.append(" ");
for (Map<String, Object> map : dataList)
{
count++;
buffer.append(map.get("columnName"));
if (count != length)
{
buffer.append(", ");
}
}
buffer.append("\r\n");
buffer.append("</sql>").append("\r\n");
// insert配置
buffer.append("<insert id=\"insert\" parameterType=\"\">").append("\r\n");
buffer.append(" insert into ").append(getTableName()).append("\r\n");
buffer.append(" <trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\">").append("\r\n");
for (Map<String, Object> map : dataList)
{
String columnName = (String) map.get("columnName");
String fieldName = getFieldName(columnName);
buffer.append(" <if test=\"").append(fieldName).append(" != null\"> \r\n");
buffer.append(" ").append(columnName).append(",").append("\r\n");
buffer.append(" </if> \r\n");
}
buffer.append(" </trim>").append("\r\n");
buffer.append(" <trim prefix=\"values (\" suffix=\")\" suffixOverrides=\",\"> \r\n");
for (Map<String, Object> map : dataList)
{
String columnName = (String) map.get("columnName");
String fieldName = getFieldName(columnName);
String jdbcType = getMybatisJdbcType((Integer) map.get("dataType"));
buffer.append(" <if test=\"").append(fieldName).append(" != null\"> \r\n");
buffer.append(" #{").append(fieldName).append(",jdbcType=").append(jdbcType).append("}, \r\n");
buffer.append(" </if> \r\n");
}
buffer.append(" </trim>").append("\r\n");
// update配置
buffer.append("<update id=\"update\" parameterType=\"java.util.Map\"> \r\n");
buffer.append(" update ").append(getTableName()).append("\r\n");
buffer.append(" <set>").append("\r\n");
for (Map<String, Object> map : dataList)
{
String columnName = (String) map.get("columnName");
String fieldName = getFieldName(columnName);
String jdbcType = getMybatisJdbcType((Integer) map.get("dataType"));
buffer.append(" <if test=\"").append(fieldName).append(" != null\"> \r\n");
buffer.append(" ").append(columnName).append(" = ").append("#{").append(fieldName).append(",jdbcType=").append(jdbcType).append("}, \r\n");
buffer.append(" </if> \r\n");
}
buffer.append(" </set> \r\n");
buffer.append(" where ").append("\r\n");
// for (String primaryKey : primaryKeyList)
// {
// buffer.append(" ").append(primaryKey).append(" = #{lnNo,jdbcType=CHAR}");
// }
buffer.append("</update>");
System.out.println(buffer.toString());
}
/**
* 根据列的类型,获取mybatis配置中的jdbcType
*
* @param dataType 列的类型
* @return String jdbcType
*/
private static String getMybatisJdbcType(int dataType)
{
String jdbcType = "";
if (dataType == Types.TINYINT)
{
jdbcType = "TINYINT";
}
else if (dataType == Types.SMALLINT)
{
jdbcType = "SMALLINT";
}
else if (dataType == Types.INTEGER)
{
jdbcType = "INTEGER";
}
else if (dataType == Types.BIGINT)
{
jdbcType = "BIGINT";
}
else if (dataType == Types.FLOAT)
{
jdbcType = "FLOAT";
}
else if (dataType == Types.DOUBLE)
{
jdbcType = "DOUBLE";
}
else if (dataType == Types.DECIMAL)
{
jdbcType = "DECIMAL";
}
else if (dataType == Types.NUMERIC)
{
jdbcType = "NUMERIC";
}
else if (dataType == Types.VARCHAR)
{
jdbcType = "VARCHAR";
}
else if (dataType == Types.NVARCHAR)
{
jdbcType = "NVARCHAR";
}
else if (dataType == Types.CHAR)
{
jdbcType = "CHAR";
}
else if (dataType == Types.NCHAR)
{
jdbcType = "NCHAR";
}
else if (dataType == Types.CLOB)
{
jdbcType = "CLOB";
}
else if (dataType == Types.BLOB)
{
jdbcType = "BLOB";
}
else if (dataType == Types.NCLOB)
{
jdbcType = "NCLOB";
}
else if (dataType == Types.DATE)
{
jdbcType = "DATE";
}
else if (dataType == Types.TIMESTAMP)
{
jdbcType = "TIMESTAMP";
}
else if (dataType == Types.ARRAY)
{
jdbcType = "ARRAY";
}
else if (dataType == Types.TIME)
{
jdbcType = "TIME";
}
else if (dataType == Types.BOOLEAN)
{
jdbcType = "BOOLEAN";
}
else if (dataType == Types.BIT)
{
jdbcType = "BIT";
}
else if (dataType == Types.BINARY)
{
jdbcType = "BINARY";
}
else if (dataType == Types.OTHER)
{
jdbcType = "OTHER";
}
else if (dataType == Types.REAL)
{
jdbcType = "REAL";
}
else if (dataType == Types.LONGVARCHAR)
{
jdbcType = "LONGVARCHAR";
}
else if (dataType == Types.VARBINARY)
{
jdbcType = "VARBINARY";
}
else if (dataType == Types.LONGVARBINARY)
{
jdbcType = "LONGVARBINARY";
}
return jdbcType;
}
public String getUser()
{
return user;
}
public void setUser(String user)
{
this.user = user;
}
public String getPassword()
{
return password;
}
public void setPassword(String password)
{
this.password = password;
}
public String getDbUrl()
{
return dbUrl;
}
public void setDbUrl(String dbUrl)
{
this.dbUrl = dbUrl;
}
public String getTableName()
{
return tableName;
}
public void setTableName(String tableName)
{
this.tableName = tableName;
}
public String getCatalog()
{
return catalog;
}
public void setCatalog(String catalog)
{
this.catalog = catalog;
}
}
使用示例:
public static void main(String[] args)
{
String dbUrl = "jdbc:oracle:thin:@10.10.16.80:1521/orcl";
String userName = "user";
String password = "password";
String tableName = "MEMBER";
TableToJavaTool tool = new TableToJavaTool(userName, password, dbUrl, tableName);
// tool.setCatalog("me");
tool.process();
} |
|
|