1，业务描述前序？
需求来源于，公司的运营部门。本人所在公司（私营，游戏行业公司），从初创业，我就进入公司，一直致力于服务器核心研发。
公司成立块3年了，前后出产了4款游戏，一直在重复的制造公司游戏对应的游戏后台管理工具（web版本）；
今年年初公司成立里运营部门，不仅开始运营公司自己产品也代理了其他公司产品。越来越觉得做出统一的平台管理和游戏后台管理工具迫切需求；
既然有需求，就有市场，就需要研发；
2，重复造*
既然是统一的管理平台，问题来了，可能做过游戏或者游戏后台相关的人员都知道，管理平台，其实需求简单明了，业务简单清晰！
问题是在于数据量很大，针对一个游戏，一个玩家，一分钟就可能产出上百条日志记录（升级，获得任何奖励，消耗任何道具等等）；
然而在面对一个游戏，多个服务器，多个游戏，很多个服务器情况下，日志量很难预估；且还有一个特色，那就是一般情况下一个月以前的日志其实是完全无用，并且可以删除的；
3，DBA的重要性
我们公司到目前为止没有专业的DBA和DBC，数据库相关的设计和操作只能自己来；
（我的40米大刀呢？）
没办法只能自己上
宝宝心里苦啊
好了废话不多说，
由此上面的综合原因，我们考虑了，日志，不同的业务逻辑日志不同的实体模型（数据库表结构），
但是每一天划分开来（比如Test 表今天的Test_2016_11_04,明天：Test_2016_11_05）;
这样的业务逻辑下我没有发现比较适合自己的orm框架，或者说我英文不好，看不懂一些api吧，
于是就有了重复造*的事情；
3，我的业务量在哪里？
我的业务量主要还是在写入数据，作为日志服务器（http api 提供方式）只提供数据接收，验证，存取操作；
也就说我只需要考虑大并发情况下，批量写入问题，并且防止好sql注入式攻击就好；
4，代码实现
package net.sz.engine.db;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import net.sz.engine.utils.StringUtil;
import org.apache.log4j.Logger;
/**
*
* <br>
* author 失足程序员<br>
* mail 492794628@qq.com<br>
* phone 13882122019<br>
*/
public abstract class Dao {
private static final Logger log = Logger.getLogger(Dao.class);
/**
* 数据库连接
*/
protected String dbUrl;
/**
* 数据库名字
*/
protected String dbName;
/**
* 数据库用户
*/
protected String dbUser;
/**
* 数据库密码
*/
protected String dbPwd;
/**
* 是否显示sql语句
*/
protected boolean showSql;
/**
* 存储所有类型解析
*/
protected static final ConcurrentHashMap<String, List<SqlColumn>> sqlColumnMap = new ConcurrentHashMap<>();
public Dao() {
}
//<editor-fold defaultstate="collapsed" desc="构造函数 public Dao(String dbUrl, String dbName, String dbUser, String dbPwd, boolean showSql)">
/**
* 构造函数
*
* @param dbUrl
* @param dbName
* @param dbUser
* @param dbPwd
* @param showSql
*/
public Dao(String dbUrl, String dbName, String dbUser, String dbPwd, boolean showSql) {
this.dbUrl = dbUrl;
this.dbName = dbName;
this.dbUser = dbUser;
this.dbPwd = dbPwd;
this.showSql = showSql;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="获取表名 protected String getTableName(Object o)">
/**
* 获取表名
*
* @param oClass
* @return
*/
protected String getTableName(Class<?> oClass) {
//判断指定类型的注释是否存在于此元素上
boolean isHaveTable = oClass.isAnnotationPresent(Table.class);
if (!isHaveTable) {
return oClass.getSimpleName();//不存在就不需要获取其表名
}
Table table = oClass.getAnnotation(Table.class);//拿到对应的表格注解类型
return table.name();//返回注解中的值，也就是表名
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="设置字段值，插入数据库，支持sql注入攻击 protected void setStmtParams(PreparedStatement stmt, SqlColumn sqlColumn, Integer nums, Object value)">
/**
* 设置字段值，插入数据库，支持sql注入攻击
*
* @param stmt
* @param sqlColumn
* @param nums
* @param value
* @throws SQLException
* @throws IOException
*/
protected void setStmtParams(PreparedStatement stmt, SqlColumn sqlColumn, Integer nums, Object value) throws SQLException, IOException {
switch (sqlColumn.getClassType().getName().toLowerCase()) {
case "int":
case "java.lang.integer":
if (value == null) {
if (!sqlColumn.isColumnNullAble()) {
value = 0;
}
}
if (value == null) {
stmt.setObject(nums, null);
} else {
stmt.setInt(nums, (Integer) value);
}
break;
case "string":
case "java.lang.string":
if (value == null) {
if (!sqlColumn.isColumnNullAble()) {
value = "";
}
}
stmt.setString(nums, (String) value);
break;
case "double":
case "java.lang.double":
if (value == null) {
if (!sqlColumn.isColumnNullAble()) {
value = 0.0;
}
}
if (value == null) {
stmt.setObject(nums, null);
} else {
stmt.setDouble(nums, (Double) value);
}
break;
case "float":
case "java.lang.float":
if (value == null) {
if (!sqlColumn.isColumnNullAble()) {
value = 0.0;
}
}
if (value == null) {
stmt.setObject(nums, null);
} else {
stmt.setFloat(nums, (float) value);
}
break;
case "long":
case "java.lang.long":
if (value == null) {
if (!sqlColumn.isColumnNullAble()) {
value = 0.0;
}
}
if (value == null) {
stmt.setObject(nums, null);
} else {
stmt.setLong(nums, (long) value);
}
break;
case "byte":
case "java.lang.byte":
if (value == null) {
if (!sqlColumn.isColumnNullAble()) {
value = 0.0;
}
}
if (value == null) {
stmt.setObject(nums, null);
} else {
stmt.setByte(nums, (byte) value);
}
break;
case "short":
case "java.lang.short":
if (value == null) {
if (!sqlColumn.isColumnNullAble()) {
value = 0.0;
}
}
if (value == null) {
stmt.setObject(nums, null);
} else {
stmt.setShort(nums, (short) value);
}
break;
case "date":
case "java.lang.date":
if (value == null) {
if (!sqlColumn.isColumnNullAble()) {
value = 0.0;
}
}
stmt.setDate(nums, (Date) value);
break;
default: {
if (value == null) {
stmt.setObject(nums, null);
} else {
stmt.setBytes(nums, writeObject(value));
}
}
}
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="设置字段值，插入数据库，支持sql注入攻击 protected void setStmtParams(PreparedStatement stmt, SqlColumn sqlColumn, Integer nums, Object value)">
/**
* 设置字段值，插入数据库，支持sql注入攻击
*
* @param stmt
* @param sqlColumn
* @param nums
* @param value
* @throws SQLException
* @throws IOException
*/
protected void setStmtParams(PreparedStatement stmt, Integer nums, Object value) throws SQLException, IOException {
if (value == null) {
stmt.setObject(nums, null);
return;
}
switch (value.getClass().getName().toLowerCase()) {
case "int":
case "java.lang.integer":
stmt.setInt(nums, (Integer) value);
break;
case "string":
case "java.lang.string":
stmt.setString(nums, (String) value);
break;
case "double":
case "java.lang.double":
stmt.setDouble(nums, (Double) value);
break;
case "float":
case "java.lang.float":
stmt.setFloat(nums, (float) value);
break;
case "long":
case "java.lang.long":
stmt.setLong(nums, (long) value);
break;
case "byte":
case "java.lang.byte":
stmt.setByte(nums, (byte) value);
break;
case "short":
case "java.lang.short":
stmt.setShort(nums, (short) value);
break;
case "date":
case "java.lang.date":
stmt.setDate(nums, (Date) value);
break;
default: {
stmt.setBytes(nums, writeObject(value));
}
}
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="反射获取字段信息 过滤 transient 字段 protected Map<String, SqlColumn> getColumns(Object o)">
/**
* 反射获取字段信息 过滤 transient 字段
*
* @param clazz
* @return
*/
protected List<SqlColumn> getColumns(Class<?> clazz) {
List<SqlColumn> cols = sqlColumnMap.get(clazz.getName());
if (cols != null) {
return cols;
}
//获取对象中所有的属性
Field[] fields = clazz.getDeclaredFields();
Method[] methods = clazz.getMethods();
cols = new ArrayList<>();
boolean ispakey = false;
//遍历所有属性
for (Field field : fields) {
//忽略字段，静态字段，最终字段，不会书写到数据库
if (Modifier.isTransient(field.getModifiers())
|| Modifier.isStatic(field.getModifiers())
|| Modifier.isFinal(field.getModifiers())) {
if (showSql) {
log.error("类：" + clazz.getName() + " 字段：" + field.getName() + " is transient or static or final;");
}
continue;
}
//如果属性上有对应的列注解类型则获取这个注解类型
Column column = field.getAnnotation(Column.class);
SqlColumn sqlColumn = new SqlColumn();
sqlColumn.setColumnName(field.getName());
sqlColumn.setFieldName(field.getName());
if (column != null) {
if (column.name() != null && !column.name().trim().isEmpty()) {
sqlColumn.setColumnName(column.name().trim());
}
if (column.length() > 0) {
sqlColumn.setColunmLength(column.length());
}
sqlColumn.setColumnNullAble(column.nullable());
if (column.columnDefinition() != null) {
sqlColumn.setColumnDefinition(column.columnDefinition());
}
}
//拿到对应属性的类型，然后根据对应的类型去声明字段类型
Class<?> type = field.getType();
sqlColumn.setClassType(type);
String columnvalue = null;
switch (type.getName().toLowerCase()) {
case "int":
case "java.lang.integer":
columnvalue = "int(4)";
break;
case "string":
case "java.lang.string":
if (sqlColumn.getColunmLength() < 1000) {
columnvalue = "varchar(" + sqlColumn.getColunmLength() + ")";
} else {
columnvalue = "text(" + sqlColumn.getColunmLength() + ")";
}
break;
case "double":
case "java.lang.double":
columnvalue = "double";
break;
case "float":
case "java.lang.float":
columnvalue = "float";
break;
case "byte":
case "java.lang.byte":
columnvalue = "tinyint(1)";
break;
case "long":
case "java.lang.long":
columnvalue = "bigint";
break;
case "short":
case "java.lang.short":
columnvalue = "tinyint(2)";
break;
default:
columnvalue = "blob";
break;
}
if (columnvalue != null) {
//如果属性上有对应的主键ID注解类型则获取这个注解类型
Id tpid = field.getAnnotation(Id.class);
if (tpid != null) {
ispakey = true;
sqlColumn.setColumnkey(true);
sqlColumn.setColumnNullAble(false);
GeneratedValue annotation = field.getAnnotation(GeneratedValue.class);
//判断主键是否为自动增长
if (annotation != null) {
sqlColumn.setColumnAuto(true);
}
}
if (sqlColumn.isColumnNullAble()) {
columnvalue += " null";
} else {
columnvalue += " not null";
}
if (sqlColumn.isColumnkey()) {
if (sqlColumn.isColumnAuto()) {
columnvalue += " auto_increment";
}
columnvalue += " primary key";
}
sqlColumn.setValue(columnvalue);
for (Method method : methods) {
String methodName = method.getName().toLowerCase();//获取每一个方法名
if (methodName.equals("get" + sqlColumn.getFieldName().toLowerCase())) {
sqlColumn.setGetMethod(method);
break;
}
}
for (Method method : methods) {
String methodName = method.getName().toLowerCase();//获取每一个方法名
if (methodName.equals("set" + sqlColumn.getFieldName().toLowerCase())) {
sqlColumn.setSetMethod(method);
break;
}
}
cols.add(sqlColumn);
} else {
if (showSql) {
log.error("类：" + clazz.getName() + " 无法识别的字段：" + field.getName() + " ;");
}
}
}
if (!ispakey) {
throw new UnsupportedOperationException("实体类不允许没有组件字段：" + clazz.getName());
}
if (cols.isEmpty()) {
throw new UnsupportedOperationException("实体模型未有任何字段：" + clazz.getName());
}
sqlColumnMap.put(clazz.getName(), cols);
return cols;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="序列化一个对象 protected byte[] writeObject(Object obj) ">
/**
* 序列化一个对象
*
* @param obj 要序列化的对象
* @return byte数组
* @throws java.io.IOException
*/
protected byte[] writeObject(Object obj) throws IOException {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ObjectOutputStream out = null;
try {
out = new ObjectOutputStream(baos);
out.writeObject(obj);
} finally {
try {
out.close();
} catch (IOException e) {
}
}
return baos.toByteArray();
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="序列化一个对象 protected byte[] writeObject(Object obj) ">
/**
* 序列化一个对象
*
* @param bytes
* @return byte数组
* @throws java.io.IOException
* @throws java.lang.ClassNotFoundException
*/
protected Object readObject(byte[] bytes) throws IOException, ClassNotFoundException {
try (ByteArrayInputStream bis = new ByteArrayInputStream(bytes)) {
try (ObjectInputStream ois = new ObjectInputStream(bis)) {
return ois.readObject();
}
}
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="创建表 public void createTable(List<Object> objs)">
/**
* 创建表
*
* @param objs 所有需要创建表的实体对象
* @throws java.sql.SQLException
* @throws java.io.IOException
* @throws java.lang.ClassNotFoundException
*/
public void createTable(List<Object> objs) throws SQLException, IOException, ClassNotFoundException {
//遍历所有要创建表的对象
for (Object obj : objs) {
createTable(obj);
}
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="创建表 public void createTable(Object obj)">
/**
* 创建表
*
* @param obj
* @throws SQLException
* @throws IOException
* @throws ClassNotFoundException
*/
public void createTable(Object obj) throws SQLException, IOException, ClassNotFoundException {
Class<?> aClass = obj.getClass();
String talbeName = getTableName(obj.getClass());
//拿到表的所有要创建的字段名
List<SqlColumn> columns = getColumns(aClass);
createTable(obj, talbeName, columns);
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="创建表 protected abstract void createTable(Object o, String tableName, List<SqlColumn> columns)">
/**
* 创建表
*
* @param o
* @param tableName
* @param columns
* @throws java.sql.SQLException
* @throws java.lang.ClassNotFoundException
* @throws java.io.IOException
*/
protected abstract void createTable(Object o, String tableName, List<SqlColumn> columns) throws SQLException, ClassNotFoundException, IOException;
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="检查表是否存在 public abstract boolean existsTable(String tableName)">
/**
* 检查表是否存在
*
* @param tableName
* @return
* @throws java.sql.SQLException
* @throws java.lang.ClassNotFoundException
* @throws java.io.IOException
*/
public abstract boolean existsTable(String tableName) throws SQLException, ClassNotFoundException, IOException;
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="获取数据库的连接 protected abstract Connection getConnection()">
/**
* 获取数据库的连接
*
* @return
* @throws java.sql.SQLException
* @throws java.lang.ClassNotFoundException
*/
protected abstract Connection getConnection() throws SQLException, ClassNotFoundException, ClassNotFoundException;
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="插入对象 public boolean addInsertSql(List<Object> os)">
/**
* 插入对象
*
* @param os
* @return
* @throws IOException
* @throws ClassNotFoundException
* @throws SQLException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
*/
public boolean addInsertSql(List<Object> os) throws IOException, ClassNotFoundException, SQLException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
if (os == null || os.isEmpty()) {
return false;
}
Object[] toArray = os.toArray(new Object[0]);
return addInsertSql(toArray);
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="插入对象到数据库 public boolean addInsertSql(Object... os)">
/**
* 插入对象到数据库
*
* @param os os 必须是对同一个对象
* @return
* @throws IOException
* @throws ClassNotFoundException
* @throws SQLException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
*/
public boolean addInsertSql(Object... os) throws IOException, ClassNotFoundException, SQLException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
if (os == null || os.length == 0) {
return false;
}
Object objfirst = os[0];
//得到对象的类
Class<?> clazz = objfirst.getClass();
//获取表名
String tableName = getTableName(clazz);
//拿到表的所有要创建的字段名
List<SqlColumn> columns = getColumns(clazz);
//这里如果不存在字段名就不需要创建了
if (columns == null || columns.isEmpty()) {
throw new UnsupportedOperationException("实体类没有任何字段，");
}
if (!existsTable(tableName)) {
createTable(objfirst, tableName, columns);
}
StringBuilder builder = new StringBuilder();
builder.append("insert into `").append(tableName).append("` (");
//将所有的字段拼接成对应的SQL语句
for (SqlColumn value : columns) {
builder.append("`").append(value.getColumnName()).append("`, ");
}
builder.delete(builder.length() - 2, builder.length());
builder.append(") values ");
for (int i = 0; i < os.length; i++) {
builder.append("\n(");
for (int j = 0; j < columns.size(); j++) {
builder.append("?");
if (j < columns.size() - 1) {
builder.append(",");
}
builder.append(" ");
}
builder.append(")");
if (i < os.length - 1) {
builder.append(",");
}
}
builder.append(";");
String sqlString = builder.toString();
Connection con = getConnection();
try (PreparedStatement prepareCall = con.prepareStatement(sqlString)) {
for (int i = 0; i < os.length; i++) {
int tmp = i * columns.size();
int j = 1;
Object obj = os[i];
for (SqlColumn value : columns) {
Object invoke = value.getGetMethod().invoke(obj);
setStmtParams(prepareCall, value, tmp + j, invoke);
j++;
}
}
int execute = prepareCall.executeUpdate();
if (showSql) {
log.error("执行 " + prepareCall.toString() + " 添加数据 表：" + tableName + " 结果 影响行数：" + execute);
}
if (execute <= 0) {
return false;
}
}
return true;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="返回结果集 public <T> List<T> getList(Class<T> clazz, String whereSqlString, Object... strs)">
/**
* 返回结果集
*
* @param <T>
* @param clazz
* @param whereSqlString 例如： a=? and b=? 或者 a=? or a=? 这样才能防止sql注入攻击
* @param strs
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @throws IOException
* @throws InstantiationException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
*/
public <T> List<T> getList(Class<T> clazz, String whereSqlString, Object... strs) throws SQLException, ClassNotFoundException, IOException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
List<T> ts = new ArrayList<>();
//获取表名
String tableName = getTableName(clazz);
//拿到表的所有要创建的字段名
List<SqlColumn> columns = getColumns(clazz);
List<Map<String, Object>> rs = getResultSet(tableName, columns, whereSqlString, strs);
//得到对象的所有的方法
for (Map<String, Object> r : rs) {
T object = getObject(r, clazz, columns);
ts.add(object);
}
return ts;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="返回查询结果集 public ResultSet getResultSet(Class<?> clazz, String whereSqlString, Object... strs)">
/**
* 返回查询结果集
*
* @param clazz
* @param whereSqlString 例如： a=? and b=? 或者 a=? or a=? 这样才能防止sql注入攻击
* @param strs
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @throws IOException
* @throws InstantiationException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
*/
public List<Map<String, Object>> getResultSet(Class<?> clazz, String whereSqlString, Object... strs) throws SQLException, ClassNotFoundException, IOException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
if (clazz == null) {
throw new UnsupportedOperationException("obj or clzz 为 null，");
}
//获取表名
String tableName = getTableName(clazz);
//拿到表的所有要创建的字段名
List<SqlColumn> columns = getColumns(clazz);
List<Map<String, Object>> resultSet = getResultSet(tableName, columns, whereSqlString, strs);
return resultSet;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="返回查询结果集 public abstract <T> List<T> getList(Object obj, String... strs)">
/**
* 返回查询结果集
*
* @param tableName
* @param columns
* @param whereSqlString 范例 a=? and b=? 或者 a=? or a=?
* @param strs
* @return
* @throws SQLException
* @throws IOException
* @throws java.lang.InstantiationException
* @throws java.lang.IllegalAccessException
* @throws java.lang.reflect.InvocationTargetException
* @throws java.lang.ClassNotFoundException
*/
protected List<Map<String, Object>> getResultSet(String tableName, List<SqlColumn> columns, String whereSqlString, Object... strs) throws SQLException, ClassNotFoundException, IOException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, ClassNotFoundException {
//这里如果不存在字段名就不需要创建了
if (columns == null || columns.isEmpty()) {
throw new UnsupportedOperationException("实体类没有任何字段，");
}
StringBuilder builder = new StringBuilder();
builder.append("SELECT ");
int i = 0;
for (SqlColumn value : columns) {
builder.append("`").append(value.getColumnName()).append("`");
if (i < columns.size() - 1) {
builder.append(",");
}
i++;
}
builder.append(" FROM `").append(tableName).append("` ");
if (whereSqlString != null && whereSqlString.length() > 0) {
builder.append(" WHERE ").append(whereSqlString);
}
String sqlString = builder.toString();
ResultSet rs = null;
List<Map<String, Object>> res = new ArrayList<>();
Connection con = getConnection();
try (PreparedStatement prepareStatement = con.prepareStatement(sqlString)) {
if (strs != null && strs.length > 0) {
for (int j = 0; j < strs.length; j++) {
setStmtParams(prepareStatement, j + 1, strs[j]);
}
}
if (showSql) {
log.error("\n" + prepareStatement.toString());
}
rs = prepareStatement.executeQuery();
while (rs.next()) {
Map<String, Object> map = new HashMap<>();
int columnCount = rs.getMetaData().getColumnCount();
for (int j = 1; j < columnCount + 1; j++) {
Object object = rs.getObject(j);
String columnName = rs.getMetaData().getColumnName(j);
map.put(columnName, object);
}
res.add(map);
}
}
return res;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="返回结果对象 protected <T> T getObject(ResultSet rs, Class<T> clazz, List<SqlColumn> columns)">
/**
* 返回结果对象
*
* @param <T>
* @param rs
* @param clazz
* @param columns
* @return
* @throws InstantiationException
* @throws IllegalAccessException
* @throws SQLException
* @throws IllegalArgumentException
* @throws InvocationTargetException
* @throws IOException
* @throws ClassNotFoundException
*/
protected <T> T getObject(Map<String, Object> rs, Class<T> clazz, List<SqlColumn> columns) throws InstantiationException, IllegalAccessException, SQLException, IllegalArgumentException, InvocationTargetException, IOException, ClassNotFoundException {
/* 生成一个实例 */
T obj = clazz.newInstance();
for (SqlColumn column : columns) {
Method m = column.getSetMethod();
m.invoke(obj, getObject(rs, column.getColumnName(), column.getClassType()));
}
return obj;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="获取一个已经返回的结果集的值 public Object getObject(ResultSet rs, String columnName, Class<?> columnType)">
/**
* 获取一个已经返回的结果集的值
*
* @param rs
* @param columnName
* @param columnType
* @return
* @throws SQLException
* @throws IOException
* @throws ClassNotFoundException
*/
public Object getObject(Map<String, Object> rs, String columnName, Class<?> columnType) throws SQLException, IOException, ClassNotFoundException {
Object obj = rs.get(columnName);
String toLowerCase = columnType.getName().toLowerCase();
switch (toLowerCase) {
case "int":
case "java.lang.integer":
obj = (Integer) obj;
break;
case "string":
case "java.lang.string":
obj = (String) obj;
break;
case "double":
case "java.lang.double":
obj = (Double) obj;
break;
case "float":
case "java.lang.float":
obj = (Float) obj;
break;
case "long":
case "java.lang.long":
obj = (Long) obj;
break;
case "byte":
case "java.lang.byte":
obj = (Byte) obj;
break;
case "short":
case "java.lang.short":
obj = (Short) obj;
break;
case "date":
case "java.lang.date":
obj = (Date) obj;
break;
default: {
byte[] bytes = (byte[]) obj;
if (bytes != null) {
obj = readObject(bytes);
} else {
obj = null;
}
// byte[] bytes = rs.getBytes(columnName);
// if (bytes != null) {
// obj = readObject(bytes);
// }
}
}
return obj;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="更新数据 public boolean update(Object... objs)">
/**
* 更新数据
*
* @param objs
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @throws IOException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
*/
public boolean update(Object... objs) throws SQLException, ClassNotFoundException, IOException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
for (Object obj : objs) {
update(obj);
}
return true;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="更新数据 public boolean update(Object obj)">
/**
* 更新数据
*
* @param obj
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @throws IOException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
*/
public boolean update(Object obj) throws SQLException, ClassNotFoundException, IOException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
if (obj == null) {
throw new UnsupportedOperationException("obj is null");
}
String tableName = getTableName(obj.getClass());
List<SqlColumn> columns = getColumns(obj.getClass());
StringBuilder builder = new StringBuilder();
builder.append("update `").append(tableName).append("` set");
/* 需要更新的字段 */
List<SqlColumn> addValues = new ArrayList<>();
/* 主键字段 */
List<SqlColumn> keyValues = new ArrayList<>();
for (SqlColumn column : columns) {
if (!column.isColumnkey()) {
if (!addValues.isEmpty()) {
builder.append(",");
}
/* 不是主键 */
builder.append(" `").append(column.getColumnName()).append("` = ?");
addValues.add(column);
}
}
for (SqlColumn column : columns) {
if (column.isColumnkey()) {
if (keyValues.isEmpty()) {
builder.append(" where ");
} else {
builder.append(" and ");
}
keyValues.add(column);
/* 不是主键 */
builder.append(" `").append(column.getColumnName()).append("` = ? ");
}
}
Connection con = getConnection();
try (PreparedStatement prepareStatement = con.prepareStatement(builder.toString())) {
for (int i = 0; i < addValues.size(); i++) {
SqlColumn sqlColumn = addValues.get(i);
Object invoke = sqlColumn.getGetMethod().invoke(obj);
setStmtParams(prepareStatement, sqlColumn, i + 1, invoke);
}
for (int i = 0; i < keyValues.size(); i++) {
SqlColumn sqlColumn = keyValues.get(i);
Object invoke = sqlColumn.getGetMethod().invoke(obj);
setStmtParams(prepareStatement, sqlColumn, addValues.size() + i + 1, invoke);
}
int executeUpdate = prepareStatement.executeUpdate();
if (showSql) {
log.error("\n" + prepareStatement.toString() + " 执行结果：" + executeUpdate);
}
if (executeUpdate <= 0) {
return false;
}
}
return true;
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="更新数据 public int executeUpdate(String sql, Object... objs)">
/**
* 更新数据
*
* @param sql
* @param objs
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @throws IOException
*/
public int executeUpdate(String sql, Object... objs) throws SQLException, ClassNotFoundException, IOException {
Connection con = getConnection();
try (PreparedStatement prepareStatement = con.prepareStatement(sql)) {
if (objs != null && objs.length > 0) {
for (int i = 0; i < 10; i++) {
setStmtParams(prepareStatement, i + 1, objs[i]);
}
}
int executeUpdate = prepareStatement.executeUpdate();
if (showSql) {
log.error("\n" + prepareStatement.toString() + " 执行结果：" + executeUpdate);
}
return executeUpdate;
}
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="删除数据 public int delete(Class<?> clazz)">
/**
* 删除数据
*
* @param clazz
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @throws IOException
*/
public int delete(Class<?> clazz) throws SQLException, ClassNotFoundException, IOException {
return delete(clazz, null);
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="删除行 public int delete(Class<?> clazz, String sqlWhere, Object... objs)">
/**
* 删除行
*
* @param clazz
* @param sqlWhere
* @param objs
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @throws IOException
*/
public int delete(Class<?> clazz, String sqlWhere, Object... objs) throws SQLException, ClassNotFoundException, IOException {
StringBuilder builder = new StringBuilder();
String tableName = getTableName(clazz);
builder.append("DELETE FROM `").append(tableName).append("`");
if (!StringUtil.isNullOrEmpty(sqlWhere)) {
builder.append(" WHERE ").append(sqlWhere);
}
return executeUpdate(builder.toString(), objs);
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="删除表 public int dropTable(Object obj)">
/**
* 删除表
*
* @param obj
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @throws IOException
*/
public int dropTable(Object obj) throws SQLException, ClassNotFoundException, IOException {
return dropTable(obj.getClass());
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="删除表 public int dropTable(Class<?> clazz)">
/**
* 删除表
*
* @param clazz
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @throws IOException
*/
public int dropTable(Class<?> clazz) throws SQLException, ClassNotFoundException, IOException {
StringBuilder builder = new StringBuilder();
String tableName = getTableName(clazz);
builder.append("DROP TABLE IF EXISTS `").append(tableName).append("`;");
return executeUpdate(builder.toString());
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="删除数据库 public int dropDatabase(String database)">
/**
* 删除数据库
*
* @param database
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @throws IOException
*/
public int dropDatabase(String database) throws SQLException, ClassNotFoundException, IOException {
StringBuilder builder = new StringBuilder();
builder.append("DROP DATABASE IF EXISTS `").append(database).append("`;");
return executeUpdate(builder.toString());
}
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="创建数据库 public int createDatabase(String database)">
/**
* 创建数据库 , 吃方法创建数据库后会自动使用 use 语句
*
* @param database
* @return
* @throws SQLException
* @throws ClassNotFoundException
* @throws IOException
*/
public int createDatabase(String database) throws SQLException, ClassNotFoundException, IOException {
StringBuilder builder = new StringBuilder();
builder.append("CREATE DATABASE IF NOT EXISTS `").append(database).append("` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;");
int executeUpdate = executeUpdate(builder.toString());
builder = new StringBuilder();
builder.append("use `").append(database).append("`;");;
executeUpdate(builder.toString());
return executeUpdate;
}
//</editor-fold>
}
由于我们存在mysql和sqlite的版本，这里抽象出来了base class
package net.sz.engine.db;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import org.apache.log4j.Logger;
/**
*
* <br>
* author 失足程序员<br>
* mail 492794628@qq.com<br>
* phone 13882122019<br>
*/
public class MysqlDaoImp1 extends Dao {
private static final Logger log = Logger.getLogger(MysqlDaoImp1.class);
public MysqlDaoImp1(String dbUrl, String dbName, String dbUser, String dbPwd, boolean showSql) {
super(dbUrl, dbName, dbUser, dbPwd, showSql);
}
private Connection _con = null;
/**
* 获取数据库的连接
*
* @return
* @throws java.sql.SQLException
* @throws java.lang.ClassNotFoundException
*/
@Override
protected Connection getConnection() throws SQLException, ClassNotFoundException {
if (_con != null && !_con.isClosed()) {
return _con;
}
try {
if (_con != null) {
_con.close();
}
} catch (Exception e) {
log.error("", e);
}
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
}
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
}
_con = DriverManager.getConnection("jdbc:mysql://" + dbUrl + "/" + dbName, dbUser, dbPwd);
return _con;
}
@Override
public boolean existsTable(String tableName) throws SQLException, IOException, ClassNotFoundException {
String ifexits = "select sum(1) `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='" + dbName + "' and `TABLE_NAME`='" + tableName + "' ;";
Connection con = getConnection();
try (PreparedStatement createStatement = con.prepareStatement(ifexits)) {
ResultSet executeQuery = createStatement.executeQuery();
if (executeQuery != null && executeQuery.next()) {
int aInt = executeQuery.getInt("TABLE_NAME");
if (showSql) {
log.error("表：" + tableName + " 检查结果：" + (aInt > 0 ? " 已经存在 " : " 无此表 "));
}
if (aInt > 0) {
return true;
}
}
}
return false;
}
/**
*
* @param o
* @param tableName
* @param columns
* @throws SQLException
* @throws IOException
* @throws ClassNotFoundException
*/
@Override
protected void createTable(Object o, String tableName, List<SqlColumn> columns) throws SQLException, IOException, ClassNotFoundException {
if (existsTable(tableName)) {
//执行对应的创建表操作
Connection con = getConnection();
if (showSql) {
log.error("表" + tableName + "已经存在，检测字段变更，只会处理新增字段；");
}
for (SqlColumn value : columns) {
String ifexits = "SELECT sum(1) usm FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='" + dbName + "' AND table_name='" + tableName + "' AND COLUMN_NAME='" + value.getColumnName() + "'";
try (Statement createStatement = con.createStatement()) {
ResultSet executeQuery = createStatement.executeQuery(ifexits);
if (executeQuery != null && executeQuery.next()) {
int aInt = executeQuery.getInt("usm");
if (aInt == 0) {
if (showSql) {
log.error("表：" + tableName + " 字段：" + value.getFieldName() + " 映射数据库字段：" + value.getColumnName() + " 不存在，");
}
String sqls = "ALTER TABLE `" + tableName + "` ADD `" + value.getColumnName() + "` " + value.getValue() + ";";
boolean execute1 = createStatement.execute(sqls);
if (showSql) {
log.error("执行语句：" + sqls + " 执行结果：" + execute1);
}
} else {
if (showSql) {
log.error("表：" + tableName + " 字段：" + value.getFieldName() + " 映射数据库字段：" + value.getColumnName() + " 存在，将不会修改");
}
/* String sqls = "ALTER TABLE " + tableName + " CHANGE `" + key + "` " + value.getValue() + ";";
if (showSql) {
log.error("执行语句：" + sqls);
}
try (Statement cs1 = con.createStatement()) {
boolean execute1 = cs1.execute(sqls);
if (showSql) {
log.error("执行结果：" + execute1);
}
}*/
}
}
}
}
} else {
StringBuilder sb = new StringBuilder();
sb.append("\n create table if not exists `").append(tableName).append("` (\n");
//将所有的字段拼接成对应的SQL语句
for (SqlColumn sqlColumn : columns) {
sb.append(" `").append(sqlColumn.getColumnName()).append("` ").append(sqlColumn.getValue()).append(" COMMENT '").append(sqlColumn.getColumnDefinition()).append("' ").append(",\n");
}
sb.delete(sb.length() - 2, sb.length());
sb.append("\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
//执行对应的创建表操作
String sql = sb.toString();
Connection con = getConnection();
try (PreparedStatement p1 = con.prepareStatement(sql)) {
boolean execute = p1.execute();
if (showSql) {
log.error("\n表：" + sql + "\n 创建完成；");
}
}
}
}
}
sqlite 数据库支持
package net.sz.engine.db;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import org.apache.log4j.Logger;
/**
* 尚未完善的数据集合
* <br>
* author 失足程序员<br>
* mail 492794628@qq.com<br>
* phone 13882122019<br>
*/
public class SqliteDaoImp1 extends Dao {
private static final Logger log = Logger.getLogger(SqliteDaoImp1.class);
public SqliteDaoImp1(String dbUrl, String dbName, String dbUser, String dbPwd, boolean showSql) {
super(dbUrl, dbName, dbUser, dbPwd, showSql);
}
private Connection _con = null;
/**
* 获取数据库的连接
*
* @return
* @throws java.sql.SQLException
* @throws java.lang.ClassNotFoundException
*/
@Override
protected Connection getConnection() throws SQLException, ClassNotFoundException {
if (_con != null && !_con.isClosed()) {
return _con;
}
try {
if (_con != null) {
_con.close();
}
} catch (Exception e) {
log.error("", e);
}
Class.forName("org.sqlite.JDBC");
_con = DriverManager.getConnection("jdbc:sqlite:" + dbName, dbUser, dbPwd);
return _con;
}
@Override
public boolean existsTable(String tableName) throws SQLException, IOException, ClassNotFoundException {
String ifexits = "select sum(1) `TABLE_NAME` from sqlite_master where type ='table' and `name`='" + tableName + "' ;";
Connection con = getConnection();
try (PreparedStatement createStatement = con.prepareStatement(ifexits)) {
ResultSet executeQuery = createStatement.executeQuery();
if (executeQuery != null && executeQuery.next()) {
int aInt = executeQuery.getInt("TABLE_NAME");
if (showSql) {
log.error("表：" + tableName + " 检查结果：" + (aInt > 0 ? " 已经存在 " : " 无此表 "));
}
if (aInt > 0) {
return true;
}
}
}
return false;
}
/**
*
* @param o
* @param tableName
* @param columns
* @throws SQLException
* @throws IOException
* @throws ClassNotFoundException
*/
@Override
protected void createTable(Object o, String tableName, List<SqlColumn> columns) throws SQLException, IOException, ClassNotFoundException {
if (existsTable(tableName)) {
//执行对应的创建表操作
Connection con = getConnection();
if (showSql) {
log.error("表" + tableName + "已经存在，检测字段变更，只会处理新增字段；");
}
for (SqlColumn value : columns) {
String ifexits = "SELECT sum(1) usm FROM sqlite_master WHERE name='" + tableName + "' AND sql like '%" + value.getColumnName() + "%'";
try (Statement createStatement = con.createStatement()) {
ResultSet executeQuery = createStatement.executeQuery(ifexits);
if (executeQuery != null && executeQuery.next()) {
int aInt = executeQuery.getInt("usm");
if (aInt == 0) {
if (showSql) {
log.error("表：" + tableName + " 字段：" + value.getFieldName() + " 映射数据库字段：" + value.getColumnName() + " 不存在，");
}
String sqls = "ALTER TABLE `" + tableName + "` ADD `" + value.getColumnName() + "` " + value.getValue() + ";";
int execute1 = createStatement.executeUpdate(sqls);
if (showSql) {
log.error("执行语句：" + sqls + " 执行结果：" + execute1);
}
} else {
if (showSql) {
log.error("表：" + tableName + " 字段：" + value.getFieldName() + " 映射数据库字段：" + value.getColumnName() + " 存在，将不会修改");
}
/* String sqls = "ALTER TABLE " + tableName + " CHANGE `" + key + "` " + value.getValue() + ";";
if (showSql) {
log.error("执行语句：" + sqls);
}
try (Statement cs1 = con.createStatement()) {
boolean execute1 = cs1.execute(sqls);
if (showSql) {
log.error("执行结果：" + execute1);
}
}*/
}
}
}
}
} else {
StringBuilder sb = new StringBuilder();
sb.append("\n create table if not exists `").append(tableName).append("` (\n");
//将所有的字段拼接成对应的SQL语句
for (SqlColumn value : columns) {
sb.append(" `").append(value.getColumnName()).append("` ").append(value.getValue()).append(",\n");
}
sb.delete(sb.length() - 2, sb.length());
sb.append("\n);");
//执行对应的创建表操作
String sql = sb.toString();
Connection con = getConnection();
try (PreparedStatement p1 = con.prepareStatement(sql)) {
int execute = p1.executeUpdate();
if (showSql) {
log.error("\n表：" + sql + " \n创建完成；");
}
}
}
}
@Override
public int createDatabase(String database) throws SQLException, ClassNotFoundException, IOException {
throw new UnsupportedOperationException("Create Database do not Operation");
}
@Override
public int dropDatabase(String database) throws SQLException, ClassNotFoundException, IOException {
throw new UnsupportedOperationException("Drop Database do not Operation");
}
}
在实体模型解析上面需求的辅助类
package net.sz.engine.db;
import java.lang.reflect.Method;
import org.apache.log4j.Logger;
/**
*
* <br>
* author 失足程序员<br>
* mail 492794628@qq.com<br>
* phone 13882122019<br>
*/
class SqlColumn {
private static final Logger log = Logger.getLogger(SqlColumn.class);
//数据库映射名字
private String columnName;
//字段名字
private String fieldName;
//字段长度
private int colunmLength;
//是否是自增列表
private boolean columnAuto;
//是否是主键列
private boolean columnkey;
//字段是否为空
private boolean columnNullAble;
//字段描述
private String columnDefinition;
//最后拼接
private String value;
//
private Class<?> classType;
private Method setMethod;
private Method getMethod;
public SqlColumn() {
this.columnName = "";
this.fieldName = "";
this.colunmLength = 255;
this.columnAuto = false;
this.columnkey = false;
this.columnNullAble = true;
this.columnDefinition = "";
}
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public Class<?> getClassType() {
return classType;
}
public void setClassType(Class<?> classType) {
this.classType = classType;
}
public String getColumnName() {
return columnName;
}
public void setColumnName(String columnName) {
this.columnName = columnName;
}
public int getColunmLength() {
return colunmLength;
}
public void setColunmLength(int colunmLength) {
this.colunmLength = colunmLength;
}
public boolean isColumnAuto() {
return columnAuto;
}
public void setColumnAuto(boolean columnAuto) {
this.columnAuto = columnAuto;
}
public boolean isColumnkey() {
return columnkey;
}
public void setColumnkey(boolean columnkey) {
this.columnkey = columnkey;
}
public boolean isColumnNullAble() {
return columnNullAble;
}
public void setColumnNullAble(boolean columnNullAble) {
this.columnNullAble = columnNullAble;
}
public String getColumnDefinition() {
return columnDefinition;
}
public void setColumnDefinition(String columnDefinition) {
this.columnDefinition = columnDefinition;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
public Method getSetMethod() {
return setMethod;
}
public void setSetMethod(Method setMethod) {
this.setMethod = setMethod;
}
public Method getGetMethod() {
return getMethod;
}
public void setGetMethod(Method getMethod) {
this.getMethod = getMethod;
}
@Override
public String toString() {
return "SqlColumn{" + "columnName=" + columnName + ", colunmLength=" + colunmLength + ", columnAuto=" + columnAuto + ", columnkey=" + columnkey + ", columnNullAble=" + columnNullAble + ", columnDefinition=" + columnDefinition + ", value=" + value + '}';
}
}
测试情况：
package net.sz.engine.db;
import org.apache.log4j.Logger;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
/**
* 反射自动查询和封装的类
* <br>
* author 失足程序员<br>
* mail 492794628@qq.com<br>
* phone 13882122019<br>
*/
public class TestDao {
private static final Logger log = Logger.getLogger(TestDao.class);
public static void main(String[] args) throws Exception {
//====================添加======================
// Dog d = new Dog(21, "小不点", "藏獒", "灰色", 25);
Person p = new Person(64, "大象hadoop", 10, "家住Apache基金组织");
p.getMap().put("s", "s");
Person p1 = new Person(65, "xxxx", 10, "家住Apache基金组织");
p1.getMap().put("s", "s");
Dao mysqlps = new MysqlDaoImp1("192.168.2.220:3306", "test", "root", "1qaz2wsx", true);
Dao sqliteps = new SqliteDaoImp1("", "log.db", "root", "1qaz2wsx", true);
mysqlps.dropDatabase("test");
mysqlps.createDatabase("test");
// mysqlps.dropTable(p);
mysqlps.createTable(p);
mysqlps.delete(p.getClass());
mysqlps.addInsertSql(p, p1);
// sqliteps.dropDatabase("test");
// sqliteps.createDatabase("test");
// sqliteps.dropTable(p);
sqliteps.createTable(p);
sqliteps.delete(p.getClass());
sqliteps.addInsertSql(p, p1);
List<? extends Person> list = mysqlps.getList(p.getClass(), null);
for (Person person : list) {
log.error(person.toString());
}
mysqlps.update(p1);
sqliteps.update(p1);
// =======================查询=======================
// 强制转换为原始类
// Dog d1=(Dog)getOneObject("com.qin.model.Dog", "id", "1");
// log.debug(d1);
// Person d1 = (Person) getOneObject(Person.class.getName(), "id", "1");
// Person d1=(Person)getOneObject("com.qin.model.Person", "name", "王婷");
// log.debug(d1);
}
}
/**
*
* <br>
* author 失足程序员<br>
* mail 492794628@qq.com<br>
* phone 13882122019<br>
*/
@Table(name = "Person")
class Person {
private static final Logger log = Logger.getLogger(Person.class);
@Id
@Column(name = "_id")
private int id;
@Column(name = "dName", length = 655)
private String name;
private int age;
private Short age1;
private Byte age2;
private String address;
@Column(nullable = false)
private Map<String, String> map = new HashMap<>();
public Person() {
// TODO Auto-generated constructor stub
}
public Person(int id, String name, int age, String address) {
super();
this.id = id;
this.name = name;
this.age = age;
this.address = address;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Map<String, String> getMap() {
return map;
}
public void setMap(Map<String, String> map) {
this.map = map;
}
public Short getAge1() {
return age1;
}
public void setAge1(Short age1) {
this.age1 = age1;
}
public Byte getAge2() {
return age2;
}
public void setAge2(Byte age2) {
this.age2 = age2;
}
@Override
public String toString() {
return "Person{" + "id=" + id + ", name=" + name + ", age=" + age + ", age1=" + age1 + ", age2=" + age2 + ", address=" + address + ", map=" + map + '}';
}
}
--- exec-maven-plugin:1.2.1:exec (default-cli) @ net.sz.game.engine ---
[11-04 15:27:11:0938:ERROR: db.Dao.executeUpdate():1012] ->
com.mysql.jdbc.JDBC42PreparedStatement@7c53a9eb: DROP DATABASE IF EXISTS `test`; 执行结果：1
[11-04 15:27:11:0943:ERROR: db.Dao.executeUpdate():1012] ->
com.mysql.jdbc.JDBC42PreparedStatement@2f333739: CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 执行结果：1
[11-04 15:27:11:0944:ERROR: db.Dao.executeUpdate():1012] ->
com.mysql.jdbc.JDBC42PreparedStatement@12bb4df8: use `test`; 执行结果：0
[11-04 15:27:11:0953:ERROR: db.Dao.getColumns():305] -> 类：net.sz.engine.db.Person 字段：log is transient or static or final;
[11-04 15:27:11:0962:ERROR: db.MysqlDaoImp1.existsTable():69 ] -> 表：Person 检查结果： 无此表
[11-04 15:27:12:0138:ERROR: db.MysqlDaoImp1.createTable():144] ->
表：
create table if not exists `Person` (
`_id` int(4) not null primary key COMMENT '' ,
`dName` varchar(655) null COMMENT '' ,
`age` int(4) null COMMENT '' ,
`age1` tinyint(2) null COMMENT '' ,
`age2` tinyint(1) null COMMENT '' ,
`address` varchar(255) null COMMENT '' ,
`map` blob not null COMMENT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
创建完成；
[11-04 15:27:12:0141:ERROR: db.Dao.executeUpdate():1012] ->
com.mysql.jdbc.JDBC42PreparedStatement@27ddd392: DELETE FROM `Person` 执行结果：0
[11-04 15:27:12:0143:ERROR: db.MysqlDaoImp1.existsTable():69 ] -> 表：Person 检查结果： 已经存在
[11-04 15:27:12:0188:ERROR: db.Dao.addInsertSql():652] -> 执行 com.mysql.jdbc.JDBC42PreparedStatement@2db0f6b2: insert into `Person` (`_id`, `dName`, `age`, `age1`, `age2`, `address`, `map`) values
(64, '大象hadoop', 10, null, null, '家住Apache基金组织', x'ACED0005737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F4000000000000C770800000010000000017400017371007E000278' ),
(65, 'xxxx', 10, null, null, '家住Apache基金组织', x'ACED0005737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F4000000000000C770800000010000000017400017371007E000278' ); 添加数据 表：Person 结果 影响行数：2
[11-04 15:27:12:0356:ERROR: db.SqliteDaoImp1.existsTable():63 ] -> 表：Person 检查结果： 已经存在
[11-04 15:27:12:0356:ERROR: db.SqliteDaoImp1.createTable():88 ] -> 表Person已经存在，检测字段变更，只会处理新增字段；
[11-04 15:27:12:0356:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：id 映射数据库字段：_id 存在，将不会修改
[11-04 15:27:12:0357:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：name 映射数据库字段：dName 存在，将不会修改
[11-04 15:27:12:0357:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：age 映射数据库字段：age 存在，将不会修改
[11-04 15:27:12:0357:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：age1 映射数据库字段：age1 存在，将不会修改
[11-04 15:27:12:0358:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：age2 映射数据库字段：age2 存在，将不会修改
[11-04 15:27:12:0360:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：address 映射数据库字段：address 存在，将不会修改
[11-04 15:27:12:0361:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：map 映射数据库字段：map 存在，将不会修改
[11-04 15:27:12:0386:ERROR: db.Dao.executeUpdate():1012] ->
org.sqlite.jdbc4.JDBC4PreparedStatement@66d33a 执行结果：2
[11-04 15:27:12:0386:ERROR: db.SqliteDaoImp1.existsTable():63 ] -> 表：Person 检查结果： 已经存在
[11-04 15:27:12:0401:ERROR: db.Dao.addInsertSql():652] -> 执行 org.sqlite.jdbc4.JDBC4PreparedStatement@7cf10a6f 添加数据 表：Person 结果 影响行数：2
[11-04 15:27:12:0401:ERROR: db.Dao.getResultSet():777] ->
com.mysql.jdbc.JDBC42PreparedStatement@7e0babb1: SELECT `_id`,`dName`,`age`,`age1`,`age2`,`address`,`map` FROM `Person`
[11-04 15:27:12:0406:ERROR: db.TestDao.main():47 ] -> Person{id=64, name=大象hadoop, age=10, age1=null, age2=null, address=家住Apache基金组织, map={s=s}}
[11-04 15:27:12:0406:ERROR: db.TestDao.main():47 ] -> Person{id=65, name=xxxx, age=10, age1=null, age2=null, address=家住Apache基金组织, map={s=s}}
[11-04 15:27:12:0438:ERROR: db.Dao.update():981] ->
com.mysql.jdbc.JDBC42PreparedStatement@c818063: update `Person` set `dName` = 'xxxx', `age` = 10, `age1` = null, `age2` = null, `address` = '家住Apache基金组织', `map` = x'ACED0005737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F4000000000000C770800000010000000017400017371007E000278' where `_id` = 65 执行结果：1
[11-04 15:27:12:0452:ERROR: db.Dao.update():981] ->
org.sqlite.jdbc4.JDBC4PreparedStatement@75bd9247 执行结果：1
------------------------------------------------------------------------
BUILD SUCCESS
------------------------------------------------------------------------
Total time: 2.299s
Finished at: Fri Nov 04 15:27:12 CST 2016
Final Memory: 7M/238M
------------------------------------------------------------------------
由于这是出版的，并没有加入连接池，批量修改，和事务的处理；
想喷的尽管喷。
有什么建议和意见的也尽管提；