存在即合理，重复轮子orm java版本

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，代码实现

   1 package net.sz.engine.db;
   2 
   3 import java.io.ByteArrayInputStream;
   4 import java.io.ByteArrayOutputStream;
   5 import java.io.IOException;
   6 import java.io.ObjectInputStream;
   7 import java.io.ObjectOutputStream;
   8 import java.lang.reflect.Field;
   9 import java.lang.reflect.InvocationTargetException;
  10 import java.lang.reflect.Method;
  11 import java.lang.reflect.Modifier;
  12 import java.sql.Connection;
  13 import java.sql.Date;
  14 import java.sql.PreparedStatement;
  15 import java.sql.ResultSet;
  16 import java.sql.SQLException;
  17 import java.util.ArrayList;
  18 import java.util.HashMap;
  19 import java.util.List;
  20 import java.util.Map;
  21 import java.util.concurrent.ConcurrentHashMap;
  22 import javax.persistence.Column;
  23 import javax.persistence.GeneratedValue;
  24 import javax.persistence.Id;
  25 import javax.persistence.Table;
  26 import net.sz.engine.utils.StringUtil;
  27 import org.apache.log4j.Logger;
  28 
  29 /**
  30  *
  31  * <br>
  32  * author 失足程序员<br>
  33  * mail 492794628@qq.com<br>
  34  * phone 13882122019<br>
  35  */
  36 public abstract class Dao {
  37 
  38     private static final Logger log = Logger.getLogger(Dao.class);
  39     /**
  40      * 数据库连接
  41      */
  42     protected String dbUrl;
  43     /**
  44      * 数据库名字
  45      */
  46     protected String dbName;
  47     /**
  48      * 数据库用户
  49      */
  50     protected String dbUser;
  51     /**
  52      * 数据库密码
  53      */
  54     protected String dbPwd;
  55     /**
  56      * 是否显示sql语句
  57      */
  58     protected boolean showSql;
  59     /**
  60      * 存储所有类型解析
  61      */
  62     protected static final ConcurrentHashMap<String, List<SqlColumn>> sqlColumnMap = new ConcurrentHashMap<>();
  63 
  64     public Dao() {
  65     }
  66 
  67     //<editor-fold defaultstate="collapsed" desc="构造函数 public Dao(String dbUrl, String dbName, String dbUser, String dbPwd, boolean showSql)">
  68     /**
  69      * 构造函数
  70      *
  71      * @param dbUrl
  72      * @param dbName
  73      * @param dbUser
  74      * @param dbPwd
  75      * @param showSql
  76      */
  77     public Dao(String dbUrl, String dbName, String dbUser, String dbPwd, boolean showSql) {
  78         this.dbUrl = dbUrl;
  79         this.dbName = dbName;
  80         this.dbUser = dbUser;
  81         this.dbPwd = dbPwd;
  82         this.showSql = showSql;
  83     }
  84     //</editor-fold>
  85 
  86     //<editor-fold defaultstate="collapsed" desc="获取表名 protected String getTableName(Object o)">
  87     /**
  88      * 获取表名
  89      *
  90      * @param oClass
  91      * @return
  92      */
  93     protected String getTableName(Class<?> oClass) {
  94         //判断指定类型的注释是否存在于此元素上
  95         boolean isHaveTable = oClass.isAnnotationPresent(Table.class);
  96         if (!isHaveTable) {
  97             return oClass.getSimpleName();//不存在就不需要获取其表名
  98         }
  99         Table table = oClass.getAnnotation(Table.class);//拿到对应的表格注解类型
 100         return table.name();//返回注解中的值，也就是表名
 101     }
 102     //</editor-fold>
 103 
 104     //<editor-fold defaultstate="collapsed" desc="设置字段值，插入数据库，支持sql注入攻击  protected void setStmtParams(PreparedStatement stmt, SqlColumn sqlColumn, Integer nums, Object value)">
 105     /**
 106      * 设置字段值，插入数据库，支持sql注入攻击
 107      *
 108      * @param stmt
 109      * @param sqlColumn
 110      * @param nums
 111      * @param value
 112      * @throws SQLException
 113      * @throws IOException
 114      */
 115     protected void setStmtParams(PreparedStatement stmt, SqlColumn sqlColumn, Integer nums, Object value) throws SQLException, IOException {
 116         switch (sqlColumn.getClassType().getName().toLowerCase()) {
 117             case "int":
 118             case "java.lang.integer":
 119                 if (value == null) {
 120                     if (!sqlColumn.isColumnNullAble()) {
 121                         value = 0;
 122                     }
 123                 }
 124                 if (value == null) {
 125                     stmt.setObject(nums, null);
 126                 } else {
 127                     stmt.setInt(nums, (Integer) value);
 128                 }
 129 
 130                 break;
 131             case "string":
 132             case "java.lang.string":
 133                 if (value == null) {
 134                     if (!sqlColumn.isColumnNullAble()) {
 135                         value = "";
 136                     }
 137                 }
 138                 stmt.setString(nums, (String) value);
 139                 break;
 140             case "double":
 141             case "java.lang.double":
 142                 if (value == null) {
 143                     if (!sqlColumn.isColumnNullAble()) {
 144                         value = 0.0;
 145                     }
 146                 }
 147                 if (value == null) {
 148                     stmt.setObject(nums, null);
 149                 } else {
 150                     stmt.setDouble(nums, (Double) value);
 151                 }
 152                 break;
 153             case "float":
 154             case "java.lang.float":
 155                 if (value == null) {
 156                     if (!sqlColumn.isColumnNullAble()) {
 157                         value = 0.0;
 158                     }
 159                 }
 160                 if (value == null) {
 161                     stmt.setObject(nums, null);
 162                 } else {
 163                     stmt.setFloat(nums, (float) value);
 164                 }
 165                 break;
 166             case "long":
 167             case "java.lang.long":
 168                 if (value == null) {
 169                     if (!sqlColumn.isColumnNullAble()) {
 170                         value = 0.0;
 171                     }
 172                 }
 173                 if (value == null) {
 174                     stmt.setObject(nums, null);
 175                 } else {
 176                     stmt.setLong(nums, (long) value);
 177                 }
 178                 break;
 179             case "byte":
 180             case "java.lang.byte":
 181                 if (value == null) {
 182                     if (!sqlColumn.isColumnNullAble()) {
 183                         value = 0.0;
 184                     }
 185                 }
 186                 if (value == null) {
 187                     stmt.setObject(nums, null);
 188                 } else {
 189                     stmt.setByte(nums, (byte) value);
 190                 }
 191                 break;
 192             case "short":
 193             case "java.lang.short":
 194                 if (value == null) {
 195                     if (!sqlColumn.isColumnNullAble()) {
 196                         value = 0.0;
 197                     }
 198                 }
 199                 if (value == null) {
 200                     stmt.setObject(nums, null);
 201                 } else {
 202                     stmt.setShort(nums, (short) value);
 203                 }
 204                 break;
 205             case "date":
 206             case "java.lang.date":
 207                 if (value == null) {
 208                     if (!sqlColumn.isColumnNullAble()) {
 209                         value = 0.0;
 210                     }
 211                 }
 212                 stmt.setDate(nums, (Date) value);
 213                 break;
 214             default: {
 215                 if (value == null) {
 216                     stmt.setObject(nums, null);
 217                 } else {
 218                     stmt.setBytes(nums, writeObject(value));
 219                 }
 220             }
 221         }
 222     }
 223     //</editor-fold>
 224 
 225     //<editor-fold defaultstate="collapsed" desc="设置字段值，插入数据库，支持sql注入攻击  protected void setStmtParams(PreparedStatement stmt, SqlColumn sqlColumn, Integer nums, Object value)">
 226     /**
 227      * 设置字段值，插入数据库，支持sql注入攻击
 228      *
 229      * @param stmt
 230      * @param sqlColumn
 231      * @param nums
 232      * @param value
 233      * @throws SQLException
 234      * @throws IOException
 235      */
 236     protected void setStmtParams(PreparedStatement stmt, Integer nums, Object value) throws SQLException, IOException {
 237         if (value == null) {
 238             stmt.setObject(nums, null);
 239             return;
 240         }
 241         switch (value.getClass().getName().toLowerCase()) {
 242             case "int":
 243             case "java.lang.integer":
 244                 stmt.setInt(nums, (Integer) value);
 245                 break;
 246             case "string":
 247             case "java.lang.string":
 248                 stmt.setString(nums, (String) value);
 249                 break;
 250             case "double":
 251             case "java.lang.double":
 252                 stmt.setDouble(nums, (Double) value);
 253                 break;
 254             case "float":
 255             case "java.lang.float":
 256                 stmt.setFloat(nums, (float) value);
 257                 break;
 258             case "long":
 259             case "java.lang.long":
 260                 stmt.setLong(nums, (long) value);
 261                 break;
 262             case "byte":
 263             case "java.lang.byte":
 264                 stmt.setByte(nums, (byte) value);
 265                 break;
 266             case "short":
 267             case "java.lang.short":
 268                 stmt.setShort(nums, (short) value);
 269                 break;
 270             case "date":
 271             case "java.lang.date":
 272                 stmt.setDate(nums, (Date) value);
 273                 break;
 274             default: {
 275                 stmt.setBytes(nums, writeObject(value));
 276             }
 277         }
 278     }
 279     //</editor-fold>
 280 
 281     //<editor-fold defaultstate="collapsed" desc="反射获取字段信息 过滤 transient 字段 protected Map<String, SqlColumn> getColumns(Object o)">
 282     /**
 283      * 反射获取字段信息 过滤 transient 字段
 284      *
 285      * @param clazz
 286      * @return
 287      */
 288     protected List<SqlColumn> getColumns(Class<?> clazz) {
 289         List<SqlColumn> cols = sqlColumnMap.get(clazz.getName());
 290         if (cols != null) {
 291             return cols;
 292         }
 293         //获取对象中所有的属性
 294         Field[] fields = clazz.getDeclaredFields();
 295         Method[] methods = clazz.getMethods();
 296         cols = new ArrayList<>();
 297         boolean ispakey = false;
 298         //遍历所有属性
 299         for (Field field : fields) {
 300             //忽略字段，静态字段，最终字段，不会书写到数据库
 301             if (Modifier.isTransient(field.getModifiers())
 302                     || Modifier.isStatic(field.getModifiers())
 303                     || Modifier.isFinal(field.getModifiers())) {
 304                 if (showSql) {
 305                     log.error("类：" + clazz.getName() + " 字段：" + field.getName() + " is transient or static or final;");
 306                 }
 307                 continue;
 308             }
 309 
 310             //如果属性上有对应的列注解类型则获取这个注解类型
 311             Column column = field.getAnnotation(Column.class);
 312             SqlColumn sqlColumn = new SqlColumn();
 313             sqlColumn.setColumnName(field.getName());
 314             sqlColumn.setFieldName(field.getName());
 315 
 316             if (column != null) {
 317                 if (column.name() != null && !column.name().trim().isEmpty()) {
 318                     sqlColumn.setColumnName(column.name().trim());
 319                 }
 320 
 321                 if (column.length() > 0) {
 322                     sqlColumn.setColunmLength(column.length());
 323                 }
 324 
 325                 sqlColumn.setColumnNullAble(column.nullable());
 326 
 327                 if (column.columnDefinition() != null) {
 328                     sqlColumn.setColumnDefinition(column.columnDefinition());
 329                 }
 330             }
 331             //拿到对应属性的类型，然后根据对应的类型去声明字段类型
 332             Class<?> type = field.getType();
 333 
 334             sqlColumn.setClassType(type);
 335 
 336             String columnvalue = null;
 337             switch (type.getName().toLowerCase()) {
 338                 case "int":
 339                 case "java.lang.integer":
 340                     columnvalue = "int(4)";
 341                     break;
 342                 case "string":
 343                 case "java.lang.string":
 344                     if (sqlColumn.getColunmLength() < 1000) {
 345                         columnvalue = "varchar(" + sqlColumn.getColunmLength() + ")";
 346                     } else {
 347                         columnvalue = "text(" + sqlColumn.getColunmLength() + ")";
 348                     }
 349                     break;
 350                 case "double":
 351                 case "java.lang.double":
 352                     columnvalue = "double";
 353                     break;
 354                 case "float":
 355                 case "java.lang.float":
 356                     columnvalue = "float";
 357                     break;
 358                 case "byte":
 359                 case "java.lang.byte":
 360                     columnvalue = "tinyint(1)";
 361                     break;
 362                 case "long":
 363                 case "java.lang.long":
 364                     columnvalue = "bigint";
 365                     break;
 366                 case "short":
 367                 case "java.lang.short":
 368                     columnvalue = "tinyint(2)";
 369                     break;
 370                 default:
 371 
 372                     columnvalue = "blob";
 373 
 374                     break;
 375             }
 376             if (columnvalue != null) {
 377                 //如果属性上有对应的主键ID注解类型则获取这个注解类型
 378                 Id tpid = field.getAnnotation(Id.class);
 379                 if (tpid != null) {
 380                     ispakey = true;
 381                     sqlColumn.setColumnkey(true);
 382                     sqlColumn.setColumnNullAble(false);
 383                     GeneratedValue annotation = field.getAnnotation(GeneratedValue.class);
 384                     //判断主键是否为自动增长
 385                     if (annotation != null) {
 386                         sqlColumn.setColumnAuto(true);
 387                     }
 388                 }
 389 
 390                 if (sqlColumn.isColumnNullAble()) {
 391                     columnvalue += " null";
 392                 } else {
 393                     columnvalue += " not null";
 394                 }
 395 
 396                 if (sqlColumn.isColumnkey()) {
 397                     if (sqlColumn.isColumnAuto()) {
 398                         columnvalue += " auto_increment";
 399                     }
 400                     columnvalue += " primary key";
 401                 }
 402 
 403                 sqlColumn.setValue(columnvalue);
 404 
 405                 for (Method method : methods) {
 406                     String methodName = method.getName().toLowerCase();//获取每一个方法名
 407                     if (methodName.equals("get" + sqlColumn.getFieldName().toLowerCase())) {
 408                         sqlColumn.setGetMethod(method);
 409                         break;
 410                     }
 411                 }
 412 
 413                 for (Method method : methods) {
 414                     String methodName = method.getName().toLowerCase();//获取每一个方法名
 415                     if (methodName.equals("set" + sqlColumn.getFieldName().toLowerCase())) {
 416                         sqlColumn.setSetMethod(method);
 417                         break;
 418                     }
 419                 }
 420 
 421                 cols.add(sqlColumn);
 422             } else {
 423                 if (showSql) {
 424                     log.error("类：" + clazz.getName() + " 无法识别的字段：" + field.getName() + " ;");
 425                 }
 426             }
 427         }
 428         if (!ispakey) {
 429             throw new UnsupportedOperationException("实体类不允许没有组件字段：" + clazz.getName());
 430         }
 431         if (cols.isEmpty()) {
 432             throw new UnsupportedOperationException("实体模型未有任何字段：" + clazz.getName());
 433         }
 434         sqlColumnMap.put(clazz.getName(), cols);
 435         return cols;
 436     }
 437     //</editor-fold>
 438 
 439     //<editor-fold defaultstate="collapsed" desc="序列化一个对象 protected byte[] writeObject(Object obj) ">
 440     /**
 441      * 序列化一个对象
 442      *
 443      * @param obj 要序列化的对象
 444      * @return byte数组
 445      * @throws java.io.IOException
 446      */
 447     protected byte[] writeObject(Object obj) throws IOException {
 448         ByteArrayOutputStream baos = new ByteArrayOutputStream();
 449         ObjectOutputStream out = null;
 450         try {
 451             out = new ObjectOutputStream(baos);
 452             out.writeObject(obj);
 453         } finally {
 454             try {
 455                 out.close();
 456             } catch (IOException e) {
 457             }
 458         }
 459         return baos.toByteArray();
 460     }
 461     //</editor-fold>
 462 
 463     //<editor-fold defaultstate="collapsed" desc="序列化一个对象 protected byte[] writeObject(Object obj) ">
 464     /**
 465      * 序列化一个对象
 466      *
 467      * @param bytes
 468      * @return byte数组
 469      * @throws java.io.IOException
 470      * @throws java.lang.ClassNotFoundException
 471      */
 472     protected Object readObject(byte[] bytes) throws IOException, ClassNotFoundException {
 473         try (ByteArrayInputStream bis = new ByteArrayInputStream(bytes)) {
 474             try (ObjectInputStream ois = new ObjectInputStream(bis)) {
 475                 return ois.readObject();
 476             }
 477         }
 478     }
 479     //</editor-fold>
 480 
 481     //<editor-fold defaultstate="collapsed" desc="创建表 public void createTable(List<Object> objs)">
 482     /**
 483      * 创建表
 484      *
 485      * @param objs 所有需要创建表的实体对象
 486      * @throws java.sql.SQLException
 487      * @throws java.io.IOException
 488      * @throws java.lang.ClassNotFoundException
 489      */
 490     public void createTable(List<Object> objs) throws SQLException, IOException, ClassNotFoundException {
 491         //遍历所有要创建表的对象
 492         for (Object obj : objs) {
 493             createTable(obj);
 494         }
 495     }
 496     //</editor-fold>
 497 
 498     //<editor-fold defaultstate="collapsed" desc="创建表 public void createTable(Object obj)">
 499     /**
 500      * 创建表
 501      *
 502      * @param obj
 503      * @throws SQLException
 504      * @throws IOException
 505      * @throws ClassNotFoundException
 506      */
 507     public void createTable(Object obj) throws SQLException, IOException, ClassNotFoundException {
 508         Class<?> aClass = obj.getClass();
 509         String talbeName = getTableName(obj.getClass());
 510         //拿到表的所有要创建的字段名
 511         List<SqlColumn> columns = getColumns(aClass);
 512         createTable(obj, talbeName, columns);
 513     }
 514     //</editor-fold>
 515 
 516     //<editor-fold defaultstate="collapsed" desc="创建表 protected abstract void createTable(Object o, String tableName, List<SqlColumn> columns)">
 517     /**
 518      * 创建表
 519      *
 520      * @param o
 521      * @param tableName
 522      * @param columns
 523      * @throws java.sql.SQLException
 524      * @throws java.lang.ClassNotFoundException
 525      * @throws java.io.IOException
 526      */
 527     protected abstract void createTable(Object o, String tableName, List<SqlColumn> columns) throws SQLException, ClassNotFoundException, IOException;
 528     //</editor-fold>
 529 
 530     //<editor-fold defaultstate="collapsed" desc="检查表是否存在 public abstract boolean existsTable(String tableName)">
 531     /**
 532      * 检查表是否存在
 533      *
 534      * @param tableName
 535      * @return
 536      * @throws java.sql.SQLException
 537      * @throws java.lang.ClassNotFoundException
 538      * @throws java.io.IOException
 539      */
 540     public abstract boolean existsTable(String tableName) throws SQLException, ClassNotFoundException, IOException;
 541     //</editor-fold>
 542 
 543     //<editor-fold defaultstate="collapsed" desc="获取数据库的连接 protected abstract Connection getConnection()">
 544     /**
 545      * 获取数据库的连接
 546      *
 547      * @return
 548      * @throws java.sql.SQLException
 549      * @throws java.lang.ClassNotFoundException
 550      */
 551     protected abstract Connection getConnection() throws SQLException, ClassNotFoundException, ClassNotFoundException;
 552     //</editor-fold>
 553 
 554     //<editor-fold defaultstate="collapsed" desc="插入对象 public boolean addInsertSql(List<Object> os)">
 555     /**
 556      * 插入对象
 557      *
 558      * @param os
 559      * @return
 560      * @throws IOException
 561      * @throws ClassNotFoundException
 562      * @throws SQLException
 563      * @throws IllegalAccessException
 564      * @throws IllegalArgumentException
 565      * @throws InvocationTargetException
 566      */
 567     public boolean addInsertSql(List<Object> os) throws IOException, ClassNotFoundException, SQLException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
 568         if (os == null || os.isEmpty()) {
 569             return false;
 570         }
 571         Object[] toArray = os.toArray(new Object[0]);
 572         return addInsertSql(toArray);
 573     }
 574     //</editor-fold>
 575 
 576     //<editor-fold defaultstate="collapsed" desc="插入对象到数据库 public boolean addInsertSql(Object... os)">
 577     /**
 578      * 插入对象到数据库
 579      *
 580      * @param os os 必须是对同一个对象
 581      * @return
 582      * @throws IOException
 583      * @throws ClassNotFoundException
 584      * @throws SQLException
 585      * @throws IllegalAccessException
 586      * @throws IllegalArgumentException
 587      * @throws InvocationTargetException
 588      */
 589     public boolean addInsertSql(Object... os) throws IOException, ClassNotFoundException, SQLException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
 590         if (os == null || os.length == 0) {
 591             return false;
 592         }
 593 
 594         Object objfirst = os[0];
 595         //得到对象的类
 596         Class<?> clazz = objfirst.getClass();
 597         //获取表名
 598         String tableName = getTableName(clazz);
 599         //拿到表的所有要创建的字段名
 600         List<SqlColumn> columns = getColumns(clazz);
 601         //这里如果不存在字段名就不需要创建了
 602         if (columns == null || columns.isEmpty()) {
 603             throw new UnsupportedOperationException("实体类没有任何字段，");
 604         }
 605 
 606         if (!existsTable(tableName)) {
 607             createTable(objfirst, tableName, columns);
 608         }
 609 
 610         StringBuilder builder = new StringBuilder();
 611 
 612         builder.append("insert into `").append(tableName).append("` (");
 613         //将所有的字段拼接成对应的SQL语句
 614         for (SqlColumn value : columns) {
 615             builder.append("`").append(value.getColumnName()).append("`, ");
 616         }
 617         builder.delete(builder.length() - 2, builder.length());
 618         builder.append(") values ");
 619 
 620         for (int i = 0; i < os.length; i++) {
 621             builder.append("\n(");
 622             for (int j = 0; j < columns.size(); j++) {
 623                 builder.append("?");
 624                 if (j < columns.size() - 1) {
 625                     builder.append(",");
 626                 }
 627                 builder.append(" ");
 628             }
 629             builder.append(")");
 630             if (i < os.length - 1) {
 631                 builder.append(",");
 632             }
 633         }
 634         builder.append(";");
 635         String sqlString = builder.toString();
 636         Connection con = getConnection();
 637         try (PreparedStatement prepareCall = con.prepareStatement(sqlString)) {
 638             for (int i = 0; i < os.length; i++) {
 639                 int tmp = i * columns.size();
 640                 int j = 1;
 641                 Object obj = os[i];
 642                 for (SqlColumn value : columns) {
 643                     Object invoke = value.getGetMethod().invoke(obj);
 644                     setStmtParams(prepareCall, value, tmp + j, invoke);
 645                     j++;
 646                 }
 647             }
 648 
 649             int execute = prepareCall.executeUpdate();
 650 
 651             if (showSql) {
 652                 log.error("执行 " + prepareCall.toString() + " 添加数据 表：" + tableName + " 结果 影响行数：" + execute);
 653             }
 654 
 655             if (execute <= 0) {
 656                 return false;
 657             }
 658 
 659         }
 660         return true;
 661     }
 662     //</editor-fold>
 663 
 664     //<editor-fold defaultstate="collapsed" desc="返回结果集 public <T> List<T> getList(Class<T> clazz, String whereSqlString, Object... strs)">
 665     /**
 666      * 返回结果集
 667      *
 668      * @param <T>
 669      * @param clazz
 670      * @param whereSqlString 例如： a=? and b=? 或者 a=? or a=? 这样才能防止sql注入攻击
 671      * @param strs
 672      * @return
 673      * @throws SQLException
 674      * @throws ClassNotFoundException
 675      * @throws IOException
 676      * @throws InstantiationException
 677      * @throws IllegalAccessException
 678      * @throws IllegalArgumentException
 679      * @throws InvocationTargetException
 680      */
 681     public <T> List<T> getList(Class<T> clazz, String whereSqlString, Object... strs) throws SQLException, ClassNotFoundException, IOException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
 682         List<T> ts = new ArrayList<>();
 683         //获取表名
 684         String tableName = getTableName(clazz);
 685         //拿到表的所有要创建的字段名
 686         List<SqlColumn> columns = getColumns(clazz);
 687 
 688         List<Map<String, Object>> rs = getResultSet(tableName, columns, whereSqlString, strs);
 689         //得到对象的所有的方法
 690         for (Map<String, Object> r : rs) {
 691             T object = getObject(r, clazz, columns);
 692             ts.add(object);
 693         }
 694         return ts;
 695     }
 696     //</editor-fold>
 697 
 698     //<editor-fold defaultstate="collapsed" desc="返回查询结果集 public ResultSet getResultSet(Class<?> clazz, String whereSqlString, Object... strs)">
 699     /**
 700      * 返回查询结果集
 701      *
 702      * @param clazz
 703      * @param whereSqlString 例如： a=? and b=? 或者 a=? or a=? 这样才能防止sql注入攻击
 704      * @param strs
 705      * @return
 706      * @throws SQLException
 707      * @throws ClassNotFoundException
 708      * @throws IOException
 709      * @throws InstantiationException
 710      * @throws IllegalAccessException
 711      * @throws IllegalArgumentException
 712      * @throws InvocationTargetException
 713      */
 714     public List<Map<String, Object>> getResultSet(Class<?> clazz, String whereSqlString, Object... strs) throws SQLException, ClassNotFoundException, IOException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
 715 
 716         if (clazz == null) {
 717             throw new UnsupportedOperationException("obj or clzz 为 null，");
 718         }
 719 
 720         //获取表名
 721         String tableName = getTableName(clazz);
 722         //拿到表的所有要创建的字段名
 723         List<SqlColumn> columns = getColumns(clazz);
 724 
 725         List<Map<String, Object>> resultSet = getResultSet(tableName, columns, whereSqlString, strs);
 726 
 727         return resultSet;
 728     }
 729     //</editor-fold>
 730 
 731     //<editor-fold defaultstate="collapsed" desc="返回查询结果集 public abstract <T> List<T> getList(Object obj, String... strs)">
 732     /**
 733      * 返回查询结果集
 734      *
 735      * @param tableName
 736      * @param columns
 737      * @param whereSqlString 范例 a=? and b=? 或者 a=? or a=?
 738      * @param strs
 739      * @return
 740      * @throws SQLException
 741      * @throws IOException
 742      * @throws java.lang.InstantiationException
 743      * @throws java.lang.IllegalAccessException
 744      * @throws java.lang.reflect.InvocationTargetException
 745      * @throws java.lang.ClassNotFoundException
 746      */
 747     protected List<Map<String, Object>> getResultSet(String tableName, List<SqlColumn> columns, String whereSqlString, Object... strs) throws SQLException, ClassNotFoundException, IOException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, ClassNotFoundException {
 748         //这里如果不存在字段名就不需要创建了
 749         if (columns == null || columns.isEmpty()) {
 750             throw new UnsupportedOperationException("实体类没有任何字段，");
 751         }
 752         StringBuilder builder = new StringBuilder();
 753         builder.append("SELECT ");
 754         int i = 0;
 755         for (SqlColumn value : columns) {
 756             builder.append("`").append(value.getColumnName()).append("`");
 757             if (i < columns.size() - 1) {
 758                 builder.append(",");
 759             }
 760             i++;
 761         }
 762         builder.append(" FROM `").append(tableName).append("` ");
 763         if (whereSqlString != null && whereSqlString.length() > 0) {
 764             builder.append(" WHERE ").append(whereSqlString);
 765         }
 766         String sqlString = builder.toString();
 767         ResultSet rs = null;
 768         List<Map<String, Object>> res = new ArrayList<>();
 769         Connection con = getConnection();
 770         try (PreparedStatement prepareStatement = con.prepareStatement(sqlString)) {
 771             if (strs != null && strs.length > 0) {
 772                 for (int j = 0; j < strs.length; j++) {
 773                     setStmtParams(prepareStatement, j + 1, strs[j]);
 774                 }
 775             }
 776             if (showSql) {
 777                 log.error("\n" + prepareStatement.toString());
 778             }
 779             rs = prepareStatement.executeQuery();
 780 
 781             while (rs.next()) {
 782                 Map<String, Object> map = new HashMap<>();
 783                 int columnCount = rs.getMetaData().getColumnCount();
 784                 for (int j = 1; j < columnCount + 1; j++) {
 785                     Object object = rs.getObject(j);
 786                     String columnName = rs.getMetaData().getColumnName(j);
 787                     map.put(columnName, object);
 788                 }
 789                 res.add(map);
 790             }
 791         }
 792         return res;
 793     }
 794     //</editor-fold>
 795 
 796     //<editor-fold defaultstate="collapsed" desc="返回结果对象 protected <T> T getObject(ResultSet rs, Class<T> clazz, List<SqlColumn> columns)">
 797     /**
 798      * 返回结果对象
 799      *
 800      * @param <T>
 801      * @param rs
 802      * @param clazz
 803      * @param columns
 804      * @return
 805      * @throws InstantiationException
 806      * @throws IllegalAccessException
 807      * @throws SQLException
 808      * @throws IllegalArgumentException
 809      * @throws InvocationTargetException
 810      * @throws IOException
 811      * @throws ClassNotFoundException
 812      */
 813     protected <T> T getObject(Map<String, Object> rs, Class<T> clazz, List<SqlColumn> columns) throws InstantiationException, IllegalAccessException, SQLException, IllegalArgumentException, InvocationTargetException, IOException, ClassNotFoundException {
 814         /* 生成一个实例 */
 815         T obj = clazz.newInstance();
 816         for (SqlColumn column : columns) {
 817             Method m = column.getSetMethod();
 818             m.invoke(obj, getObject(rs, column.getColumnName(), column.getClassType()));
 819         }
 820         return obj;
 821     }
 822     //</editor-fold>
 823 
 824     //<editor-fold defaultstate="collapsed" desc="获取一个已经返回的结果集的值 public Object getObject(ResultSet rs, String columnName, Class<?> columnType)">
 825     /**
 826      * 获取一个已经返回的结果集的值
 827      *
 828      * @param rs
 829      * @param columnName
 830      * @param columnType
 831      * @return
 832      * @throws SQLException
 833      * @throws IOException
 834      * @throws ClassNotFoundException
 835      */
 836     public Object getObject(Map<String, Object> rs, String columnName, Class<?> columnType) throws SQLException, IOException, ClassNotFoundException {
 837         Object obj = rs.get(columnName);
 838 
 839         String toLowerCase = columnType.getName().toLowerCase();
 840         switch (toLowerCase) {
 841             case "int":
 842             case "java.lang.integer":
 843                 obj = (Integer) obj;
 844                 break;
 845             case "string":
 846             case "java.lang.string":
 847                 obj = (String) obj;
 848                 break;
 849             case "double":
 850             case "java.lang.double":
 851                 obj = (Double) obj;
 852                 break;
 853             case "float":
 854             case "java.lang.float":
 855                 obj = (Float) obj;
 856                 break;
 857             case "long":
 858             case "java.lang.long":
 859                 obj = (Long) obj;
 860                 break;
 861             case "byte":
 862             case "java.lang.byte":
 863                 obj = (Byte) obj;
 864                 break;
 865             case "short":
 866             case "java.lang.short":
 867                 obj = (Short) obj;
 868                 break;
 869             case "date":
 870             case "java.lang.date":
 871                 obj = (Date) obj;
 872                 break;
 873             default: {
 874                 byte[] bytes = (byte[]) obj;
 875                 if (bytes != null) {
 876                     obj = readObject(bytes);
 877                 } else {
 878                     obj = null;
 879                 }
 880 //                byte[] bytes = rs.getBytes(columnName);
 881 //                if (bytes != null) {
 882 //                    obj = readObject(bytes);
 883 //                }
 884             }
 885         }
 886         return obj;
 887     }
 888     //</editor-fold>
 889 
 890     //<editor-fold defaultstate="collapsed" desc="更新数据 public boolean update(Object... objs)">
 891     /**
 892      * 更新数据
 893      *
 894      * @param objs
 895      * @return
 896      * @throws SQLException
 897      * @throws ClassNotFoundException
 898      * @throws IOException
 899      * @throws IllegalAccessException
 900      * @throws IllegalArgumentException
 901      * @throws InvocationTargetException
 902      */
 903     public boolean update(Object... objs) throws SQLException, ClassNotFoundException, IOException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
 904         for (Object obj : objs) {
 905             update(obj);
 906         }
 907         return true;
 908     }
 909     //</editor-fold>
 910 
 911     //<editor-fold defaultstate="collapsed" desc="更新数据 public boolean update(Object obj)">
 912     /**
 913      * 更新数据
 914      *
 915      * @param obj
 916      * @return
 917      * @throws SQLException
 918      * @throws ClassNotFoundException
 919      * @throws IOException
 920      * @throws IllegalAccessException
 921      * @throws IllegalArgumentException
 922      * @throws InvocationTargetException
 923      */
 924     public boolean update(Object obj) throws SQLException, ClassNotFoundException, IOException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
 925         if (obj == null) {
 926             throw new UnsupportedOperationException("obj is null");
 927         }
 928 
 929         String tableName = getTableName(obj.getClass());
 930         List<SqlColumn> columns = getColumns(obj.getClass());
 931         StringBuilder builder = new StringBuilder();
 932         builder.append("update `").append(tableName).append("` set");
 933         /* 需要更新的字段 */
 934         List<SqlColumn> addValues = new ArrayList<>();
 935         /* 主键字段 */
 936         List<SqlColumn> keyValues = new ArrayList<>();
 937 
 938         for (SqlColumn column : columns) {
 939             if (!column.isColumnkey()) {
 940                 if (!addValues.isEmpty()) {
 941                     builder.append(",");
 942                 }
 943                 /* 不是主键 */
 944                 builder.append(" `").append(column.getColumnName()).append("` = ?");
 945                 addValues.add(column);
 946             }
 947         }
 948 
 949         for (SqlColumn column : columns) {
 950             if (column.isColumnkey()) {
 951 
 952                 if (keyValues.isEmpty()) {
 953                     builder.append(" where ");
 954                 } else {
 955                     builder.append(" and ");
 956                 }
 957 
 958                 keyValues.add(column);
 959 
 960                 /* 不是主键 */
 961                 builder.append(" `").append(column.getColumnName()).append("` = ? ");
 962             }
 963         }
 964 
 965         Connection con = getConnection();
 966         try (PreparedStatement prepareStatement = con.prepareStatement(builder.toString())) {
 967 
 968             for (int i = 0; i < addValues.size(); i++) {
 969                 SqlColumn sqlColumn = addValues.get(i);
 970                 Object invoke = sqlColumn.getGetMethod().invoke(obj);
 971                 setStmtParams(prepareStatement, sqlColumn, i + 1, invoke);
 972             }
 973 
 974             for (int i = 0; i < keyValues.size(); i++) {
 975                 SqlColumn sqlColumn = keyValues.get(i);
 976                 Object invoke = sqlColumn.getGetMethod().invoke(obj);
 977                 setStmtParams(prepareStatement, sqlColumn, addValues.size() + i + 1, invoke);
 978             }
 979             int executeUpdate = prepareStatement.executeUpdate();
 980             if (showSql) {
 981                 log.error("\n" + prepareStatement.toString() + " 执行结果：" + executeUpdate);
 982             }
 983             if (executeUpdate <= 0) {
 984                 return false;
 985             }
 986         }
 987         return true;
 988     }
 989     //</editor-fold>
 990 
 991     //<editor-fold defaultstate="collapsed" desc="更新数据 public int executeUpdate(String sql, Object... objs)">
 992     /**
 993      * 更新数据
 994      *
 995      * @param sql
 996      * @param objs
 997      * @return
 998      * @throws SQLException
 999      * @throws ClassNotFoundException
1000      * @throws IOException
1001      */
1002     public int executeUpdate(String sql, Object... objs) throws SQLException, ClassNotFoundException, IOException {
1003         Connection con = getConnection();
1004         try (PreparedStatement prepareStatement = con.prepareStatement(sql)) {
1005             if (objs != null && objs.length > 0) {
1006                 for (int i = 0; i < 10; i++) {
1007                     setStmtParams(prepareStatement, i + 1, objs[i]);
1008                 }
1009             }
1010             int executeUpdate = prepareStatement.executeUpdate();
1011             if (showSql) {
1012                 log.error("\n" + prepareStatement.toString() + " 执行结果：" + executeUpdate);
1013             }
1014             return executeUpdate;
1015         }
1016     }
1017     //</editor-fold>
1018 
1019     //<editor-fold defaultstate="collapsed" desc="删除数据 public int delete(Class<?> clazz)">
1020     /**
1021      * 删除数据
1022      *
1023      * @param clazz
1024      * @return
1025      * @throws SQLException
1026      * @throws ClassNotFoundException
1027      * @throws IOException
1028      */
1029     public int delete(Class<?> clazz) throws SQLException, ClassNotFoundException, IOException {
1030         return delete(clazz, null);
1031     }
1032     //</editor-fold>
1033 
1034     //<editor-fold defaultstate="collapsed" desc="删除行 public int delete(Class<?> clazz, String sqlWhere, Object... objs)">
1035     /**
1036      * 删除行
1037      *
1038      * @param clazz
1039      * @param sqlWhere
1040      * @param objs
1041      * @return
1042      * @throws SQLException
1043      * @throws ClassNotFoundException
1044      * @throws IOException
1045      */
1046     public int delete(Class<?> clazz, String sqlWhere, Object... objs) throws SQLException, ClassNotFoundException, IOException {
1047         StringBuilder builder = new StringBuilder();
1048         String tableName = getTableName(clazz);
1049         builder.append("DELETE FROM `").append(tableName).append("`");
1050         if (!StringUtil.isNullOrEmpty(sqlWhere)) {
1051             builder.append(" WHERE ").append(sqlWhere);
1052         }
1053         return executeUpdate(builder.toString(), objs);
1054     }
1055     //</editor-fold>
1056 
1057     //<editor-fold defaultstate="collapsed" desc="删除表 public int dropTable(Object obj)">
1058     /**
1059      * 删除表
1060      *
1061      * @param obj
1062      * @return
1063      * @throws SQLException
1064      * @throws ClassNotFoundException
1065      * @throws IOException
1066      */
1067     public int dropTable(Object obj) throws SQLException, ClassNotFoundException, IOException {
1068         return dropTable(obj.getClass());
1069     }
1070     //</editor-fold>
1071 
1072     //<editor-fold defaultstate="collapsed" desc="删除表 public int dropTable(Class<?> clazz)">
1073     /**
1074      * 删除表
1075      *
1076      * @param clazz
1077      * @return
1078      * @throws SQLException
1079      * @throws ClassNotFoundException
1080      * @throws IOException
1081      */
1082     public int dropTable(Class<?> clazz) throws SQLException, ClassNotFoundException, IOException {
1083         StringBuilder builder = new StringBuilder();
1084         String tableName = getTableName(clazz);
1085         builder.append("DROP TABLE IF EXISTS `").append(tableName).append("`;");
1086         return executeUpdate(builder.toString());
1087     }
1088     //</editor-fold>
1089 
1090     //<editor-fold defaultstate="collapsed" desc="删除数据库 public int dropDatabase(String database)">
1091     /**
1092      * 删除数据库
1093      *
1094      * @param database
1095      * @return
1096      * @throws SQLException
1097      * @throws ClassNotFoundException
1098      * @throws IOException
1099      */
1100     public int dropDatabase(String database) throws SQLException, ClassNotFoundException, IOException {
1101         StringBuilder builder = new StringBuilder();
1102         builder.append("DROP DATABASE IF EXISTS `").append(database).append("`;");
1103         return executeUpdate(builder.toString());
1104     }
1105     //</editor-fold>
1106 
1107     //<editor-fold defaultstate="collapsed" desc="创建数据库 public int createDatabase(String database)">
1108     /**
1109      * 创建数据库 , 吃方法创建数据库后会自动使用 use 语句
1110      *
1111      * @param database
1112      * @return
1113      * @throws SQLException
1114      * @throws ClassNotFoundException
1115      * @throws IOException
1116      */
1117     public int createDatabase(String database) throws SQLException, ClassNotFoundException, IOException {
1118         StringBuilder builder = new StringBuilder();
1119         builder.append("CREATE DATABASE IF NOT EXISTS `").append(database).append("` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;");
1120         int executeUpdate = executeUpdate(builder.toString());
1121         builder = new StringBuilder();
1122         builder.append("use `").append(database).append("`;");;
1123         executeUpdate(builder.toString());
1124         return executeUpdate;
1125     }
1126     //</editor-fold>
1127 
1128 }

由于我们存在mysql和sqlite的版本，这里抽象出来了base class

  1 package net.sz.engine.db;
  2 
  3 import java.io.IOException;
  4 import java.sql.Connection;
  5 import java.sql.DriverManager;
  6 import java.sql.PreparedStatement;
  7 import java.sql.ResultSet;
  8 import java.sql.SQLException;
  9 import java.sql.Statement;
 10 import java.util.List;
 11 import org.apache.log4j.Logger;
 12 
 13 /**
 14  *
 15  * <br>
 16  * author 失足程序员<br>
 17  * mail 492794628@qq.com<br>
 18  * phone 13882122019<br>
 19  */
 20 public class MysqlDaoImp1 extends Dao {
 21 
 22     private static final Logger log = Logger.getLogger(MysqlDaoImp1.class);
 23 
 24     public MysqlDaoImp1(String dbUrl, String dbName, String dbUser, String dbPwd, boolean showSql) {
 25         super(dbUrl, dbName, dbUser, dbPwd, showSql);
 26     }
 27     private Connection _con = null;
 28 
 29     /**
 30      * 获取数据库的连接
 31      *
 32      * @return
 33      * @throws java.sql.SQLException
 34      * @throws java.lang.ClassNotFoundException
 35      */
 36     @Override
 37     protected Connection getConnection() throws SQLException, ClassNotFoundException {
 38         if (_con != null && !_con.isClosed()) {
 39             return _con;
 40         }
 41         try {
 42             if (_con != null) {
 43                 _con.close();
 44             }
 45         } catch (Exception e) {
 46             log.error("", e);
 47         }
 48         try {
 49             Class.forName("com.mysql.jdbc.Driver");
 50         } catch (ClassNotFoundException e) {
 51         }
 52         try {
 53             Class.forName("com.mysql.cj.jdbc.Driver");
 54         } catch (ClassNotFoundException e) {
 55         }
 56         _con = DriverManager.getConnection("jdbc:mysql://" + dbUrl + "/" + dbName, dbUser, dbPwd);
 57         return _con;
 58     }
 59 
 60     @Override
 61     public boolean existsTable(String tableName) throws SQLException, IOException, ClassNotFoundException {
 62         String ifexits = "select sum(1) `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='" + dbName + "' and `TABLE_NAME`='" + tableName + "' ;";
 63         Connection con = getConnection();
 64         try (PreparedStatement createStatement = con.prepareStatement(ifexits)) {
 65             ResultSet executeQuery = createStatement.executeQuery();
 66             if (executeQuery != null && executeQuery.next()) {
 67                 int aInt = executeQuery.getInt("TABLE_NAME");
 68                 if (showSql) {
 69                     log.error("表：" + tableName + " 检查结果：" + (aInt > 0 ? " 已经存在 " : " 无此表 "));
 70                 }
 71                 if (aInt > 0) {
 72                     return true;
 73                 }
 74             }
 75         }
 76         return false;
 77     }
 78 
 79     /**
 80      *
 81      * @param o
 82      * @param tableName
 83      * @param columns
 84      * @throws SQLException
 85      * @throws IOException
 86      * @throws ClassNotFoundException
 87      */
 88     @Override
 89     protected void createTable(Object o, String tableName, List<SqlColumn> columns) throws SQLException, IOException, ClassNotFoundException {
 90         if (existsTable(tableName)) {
 91             //执行对应的创建表操作
 92             Connection con = getConnection();
 93             if (showSql) {
 94                 log.error("表" + tableName + "已经存在，检测字段变更，只会处理新增字段；");
 95             }
 96             for (SqlColumn value : columns) {
 97                 String ifexits = "SELECT sum(1) usm FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='" + dbName + "' AND table_name='" + tableName + "' AND COLUMN_NAME='" + value.getColumnName() + "'";
 98                 try (Statement createStatement = con.createStatement()) {
 99                     ResultSet executeQuery = createStatement.executeQuery(ifexits);
100                     if (executeQuery != null && executeQuery.next()) {
101                         int aInt = executeQuery.getInt("usm");
102                         if (aInt == 0) {
103                             if (showSql) {
104                                 log.error("表：" + tableName + " 字段：" + value.getFieldName() + " 映射数据库字段：" + value.getColumnName() + " 不存在，");
105                             }
106                             String sqls = "ALTER TABLE `" + tableName + "` ADD `" + value.getColumnName() + "` " + value.getValue() + ";";
107                             boolean execute1 = createStatement.execute(sqls);
108                             if (showSql) {
109                                 log.error("执行语句：" + sqls + " 执行结果：" + execute1);
110                             }
111                         } else {
112                             if (showSql) {
113                                 log.error("表：" + tableName + " 字段：" + value.getFieldName() + " 映射数据库字段：" + value.getColumnName() + " 存在，将不会修改");
114                             }
115                             /*   String sqls = "ALTER TABLE " + tableName + " CHANGE `" + key + "` " + value.getValue() + ";";
116                                     if (showSql) {
117                                         log.error("执行语句：" + sqls);
118                                     }
119                                     try (Statement cs1 = con.createStatement()) {
120                                         boolean execute1 = cs1.execute(sqls);
121                                         if (showSql) {
122                                             log.error("执行结果：" + execute1);
123                                         }
124                                     }*/
125                         }
126                     }
127                 }
128             }
129         } else {
130             StringBuilder sb = new StringBuilder();
131             sb.append("\n create table if not exists `").append(tableName).append("` (\n");
132             //将所有的字段拼接成对应的SQL语句
133             for (SqlColumn sqlColumn : columns) {
134                 sb.append("     `").append(sqlColumn.getColumnName()).append("` ").append(sqlColumn.getValue()).append(" COMMENT '").append(sqlColumn.getColumnDefinition()).append("' ").append(",\n");
135             }
136             sb.delete(sb.length() - 2, sb.length());
137             sb.append("\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
138             //执行对应的创建表操作
139             String sql = sb.toString();
140             Connection con = getConnection();
141             try (PreparedStatement p1 = con.prepareStatement(sql)) {
142                 boolean execute = p1.execute();
143                 if (showSql) {
144                     log.error("\n表：" + sql + "\n 创建完成；");
145                 }
146             }
147         }
148     }
149 }

sqlite 数据库支持

  1 package net.sz.engine.db;
  2 
  3 import java.io.IOException;
  4 import java.sql.Connection;
  5 import java.sql.DriverManager;
  6 import java.sql.PreparedStatement;
  7 import java.sql.ResultSet;
  8 import java.sql.SQLException;
  9 import java.sql.Statement;
 10 import java.util.List;
 11 import org.apache.log4j.Logger;
 12 
 13 /**
 14  * 尚未完善的数据集合
 15  * <br>
 16  * author 失足程序员<br>
 17  * mail 492794628@qq.com<br>
 18  * phone 13882122019<br>
 19  */
 20 public class SqliteDaoImp1 extends Dao {
 21 
 22     private static final Logger log = Logger.getLogger(SqliteDaoImp1.class);
 23 
 24     public SqliteDaoImp1(String dbUrl, String dbName, String dbUser, String dbPwd, boolean showSql) {
 25         super(dbUrl, dbName, dbUser, dbPwd, showSql);
 26     }
 27 
 28     private Connection _con = null;
 29 
 30     /**
 31      * 获取数据库的连接
 32      *
 33      * @return
 34      * @throws java.sql.SQLException
 35      * @throws java.lang.ClassNotFoundException
 36      */
 37     @Override
 38     protected Connection getConnection() throws SQLException, ClassNotFoundException {
 39         if (_con != null && !_con.isClosed()) {
 40             return _con;
 41         }
 42         try {
 43             if (_con != null) {
 44                 _con.close();
 45             }
 46         } catch (Exception e) {
 47             log.error("", e);
 48         }
 49         Class.forName("org.sqlite.JDBC");
 50         _con = DriverManager.getConnection("jdbc:sqlite:" + dbName, dbUser, dbPwd);
 51         return _con;
 52     }
 53 
 54     @Override
 55     public boolean existsTable(String tableName) throws SQLException, IOException, ClassNotFoundException {
 56         String ifexits = "select sum(1) `TABLE_NAME` from sqlite_master where type ='table' and `name`='" + tableName + "' ;";
 57         Connection con = getConnection();
 58         try (PreparedStatement createStatement = con.prepareStatement(ifexits)) {
 59             ResultSet executeQuery = createStatement.executeQuery();
 60             if (executeQuery != null && executeQuery.next()) {
 61                 int aInt = executeQuery.getInt("TABLE_NAME");
 62                 if (showSql) {
 63                     log.error("表：" + tableName + " 检查结果：" + (aInt > 0 ? " 已经存在 " : " 无此表 "));
 64                 }
 65                 if (aInt > 0) {
 66                     return true;
 67                 }
 68             }
 69         }
 70         return false;
 71     }
 72 
 73     /**
 74      *
 75      * @param o
 76      * @param tableName
 77      * @param columns
 78      * @throws SQLException
 79      * @throws IOException
 80      * @throws ClassNotFoundException
 81      */
 82     @Override
 83     protected void createTable(Object o, String tableName, List<SqlColumn> columns) throws SQLException, IOException, ClassNotFoundException {
 84         if (existsTable(tableName)) {
 85             //执行对应的创建表操作
 86             Connection con = getConnection();
 87             if (showSql) {
 88                 log.error("表" + tableName + "已经存在，检测字段变更，只会处理新增字段；");
 89             }
 90             for (SqlColumn value : columns) {
 91                 String ifexits = "SELECT sum(1) usm FROM sqlite_master WHERE name='" + tableName + "' AND sql like '%" + value.getColumnName() + "%'";
 92                 try (Statement createStatement = con.createStatement()) {
 93                     ResultSet executeQuery = createStatement.executeQuery(ifexits);
 94                     if (executeQuery != null && executeQuery.next()) {
 95                         int aInt = executeQuery.getInt("usm");
 96                         if (aInt == 0) {
 97                             if (showSql) {
 98                                 log.error("表：" + tableName + " 字段：" + value.getFieldName() + " 映射数据库字段：" + value.getColumnName() + " 不存在，");
 99                             }
100                             String sqls = "ALTER TABLE `" + tableName + "` ADD `" + value.getColumnName() + "` " + value.getValue() + ";";
101                             int execute1 = createStatement.executeUpdate(sqls);
102                             if (showSql) {
103                                 log.error("执行语句：" + sqls + " 执行结果：" + execute1);
104                             }
105                         } else {
106                             if (showSql) {
107                                 log.error("表：" + tableName + " 字段：" + value.getFieldName() + " 映射数据库字段：" + value.getColumnName() + " 存在，将不会修改");
108                             }
109                             /*   String sqls = "ALTER TABLE " + tableName + " CHANGE `" + key + "` " + value.getValue() + ";";
110                                     if (showSql) {
111                                         log.error("执行语句：" + sqls);
112                                     }
113                                     try (Statement cs1 = con.createStatement()) {
114                                         boolean execute1 = cs1.execute(sqls);
115                                         if (showSql) {
116                                             log.error("执行结果：" + execute1);
117                                         }
118                                     }*/
119                         }
120                     }
121                 }
122             }
123         } else {
124             StringBuilder sb = new StringBuilder();
125             sb.append("\n create table if not exists `").append(tableName).append("` (\n");
126             //将所有的字段拼接成对应的SQL语句
127             for (SqlColumn value : columns) {
128                 sb.append("     `").append(value.getColumnName()).append("` ").append(value.getValue()).append(",\n");
129             }
130             sb.delete(sb.length() - 2, sb.length());
131             sb.append("\n);");
132             //执行对应的创建表操作
133             String sql = sb.toString();
134             Connection con = getConnection();
135             try (PreparedStatement p1 = con.prepareStatement(sql)) {
136                 int execute = p1.executeUpdate();
137                 if (showSql) {
138                     log.error("\n表：" + sql + " \n创建完成；");
139                 }
140             }
141         }
142     }
143 
144     @Override
145     public int createDatabase(String database) throws SQLException, ClassNotFoundException, IOException {
146         throw new UnsupportedOperationException("Create Database do not Operation");
147     }
148 
149     @Override
150     public int dropDatabase(String database) throws SQLException, ClassNotFoundException, IOException {
151         throw new UnsupportedOperationException("Drop Database do not Operation");
152     }
153 
154 }

在实体模型解析上面需求的辅助类

  1 package net.sz.engine.db;
  2 
  3 import java.lang.reflect.Method;
  4 import org.apache.log4j.Logger;
  5 
  6 /**
  7  *
  8  * <br>
  9  * author 失足程序员<br>
 10  * mail 492794628@qq.com<br>
 11  * phone 13882122019<br>
 12  */
 13 class SqlColumn {
 14 
 15     private static final Logger log = Logger.getLogger(SqlColumn.class);
 16     //数据库映射名字
 17     private String columnName;
 18     //字段名字
 19     private String fieldName;
 20     //字段长度
 21     private int colunmLength;
 22     //是否是自增列表
 23     private boolean columnAuto;
 24     //是否是主键列
 25     private boolean columnkey;
 26     //字段是否为空
 27     private boolean columnNullAble;
 28     //字段描述
 29     private String columnDefinition;
 30     //最后拼接
 31     private String value;
 32     //
 33     private Class<?> classType;
 34 
 35     private Method setMethod;
 36 
 37     private Method getMethod;
 38 
 39     public SqlColumn() {
 40         this.columnName = "";
 41         this.fieldName = "";
 42         this.colunmLength = 255;
 43         this.columnAuto = false;
 44         this.columnkey = false;
 45         this.columnNullAble = true;
 46         this.columnDefinition = "";
 47     }
 48 
 49     public String getFieldName() {
 50         return fieldName;
 51     }
 52 
 53     public void setFieldName(String fieldName) {
 54         this.fieldName = fieldName;
 55     }
 56 
 57     public Class<?> getClassType() {
 58         return classType;
 59     }
 60 
 61     public void setClassType(Class<?> classType) {
 62         this.classType = classType;
 63     }
 64 
 65     public String getColumnName() {
 66         return columnName;
 67     }
 68 
 69     public void setColumnName(String columnName) {
 70         this.columnName = columnName;
 71     }
 72 
 73     public int getColunmLength() {
 74         return colunmLength;
 75     }
 76 
 77     public void setColunmLength(int colunmLength) {
 78         this.colunmLength = colunmLength;
 79     }
 80 
 81     public boolean isColumnAuto() {
 82         return columnAuto;
 83     }
 84 
 85     public void setColumnAuto(boolean columnAuto) {
 86         this.columnAuto = columnAuto;
 87     }
 88 
 89     public boolean isColumnkey() {
 90         return columnkey;
 91     }
 92 
 93     public void setColumnkey(boolean columnkey) {
 94         this.columnkey = columnkey;
 95     }
 96 
 97     public boolean isColumnNullAble() {
 98         return columnNullAble;
 99     }
100 
101     public void setColumnNullAble(boolean columnNullAble) {
102         this.columnNullAble = columnNullAble;
103     }
104 
105     public String getColumnDefinition() {
106         return columnDefinition;
107     }
108 
109     public void setColumnDefinition(String columnDefinition) {
110         this.columnDefinition = columnDefinition;
111     }
112 
113     public String getValue() {
114         return value;
115     }
116 
117     public void setValue(String value) {
118         this.value = value;
119     }
120 
121     public Method getSetMethod() {
122         return setMethod;
123     }
124 
125     public void setSetMethod(Method setMethod) {
126         this.setMethod = setMethod;
127     }
128 
129     public Method getGetMethod() {
130         return getMethod;
131     }
132 
133     public void setGetMethod(Method getMethod) {
134         this.getMethod = getMethod;
135     }
136 
137     @Override
138     public String toString() {
139         return "SqlColumn{" + "columnName=" + columnName + ", colunmLength=" + colunmLength + ", columnAuto=" + columnAuto + ", columnkey=" + columnkey + ", columnNullAble=" + columnNullAble + ", columnDefinition=" + columnDefinition + ", value=" + value + '}';
140     }
141 
142 }

测试情况：

  1 package net.sz.engine.db;
  2 
  3 import org.apache.log4j.Logger;
  4 import java.util.HashMap;
  5 import java.util.List;
  6 import java.util.Map;
  7 import javax.persistence.Column;
  8 import javax.persistence.Id;
  9 import javax.persistence.Table;
 10 
 11 /**
 12  * 反射自动查询和封装的类
 13  * <br>
 14  * author 失足程序员<br>
 15  * mail 492794628@qq.com<br>
 16  * phone 13882122019<br>
 17  */
 18 public class TestDao {
 19 
 20     private static final Logger log = Logger.getLogger(TestDao.class);
 21 
 22     public static void main(String[] args) throws Exception {
 23         //====================添加======================
 24 //        Dog d = new Dog(21, "小不点", "藏獒", "灰色", 25);
 25         Person p = new Person(64, "大象hadoop", 10, "家住Apache基金组织");
 26         p.getMap().put("s", "s");
 27         Person p1 = new Person(65, "xxxx", 10, "家住Apache基金组织");
 28         p1.getMap().put("s", "s");
 29         Dao mysqlps = new MysqlDaoImp1("192.168.2.220:3306", "test", "root", "1qaz2wsx", true);
 30         Dao sqliteps = new SqliteDaoImp1("", "log.db", "root", "1qaz2wsx", true);
 31 
 32         mysqlps.dropDatabase("test");
 33         mysqlps.createDatabase("test");
 34 //        mysqlps.dropTable(p);
 35         mysqlps.createTable(p);
 36         mysqlps.delete(p.getClass());
 37         mysqlps.addInsertSql(p, p1);
 38 
 39 //        sqliteps.dropDatabase("test");
 40 //        sqliteps.createDatabase("test");
 41 //        sqliteps.dropTable(p);
 42         sqliteps.createTable(p);
 43         sqliteps.delete(p.getClass());
 44         sqliteps.addInsertSql(p, p1);
 45         List<? extends Person> list = mysqlps.getList(p.getClass(), null);
 46         for (Person person : list) {
 47             log.error(person.toString());
 48         }
 49         mysqlps.update(p1);
 50         sqliteps.update(p1);
 51 //        =======================查询=======================
 52 //        强制转换为原始类
 53 //    Dog d1=(Dog)getOneObject("com.qin.model.Dog", "id", "1");
 54 //    log.debug(d1);
 55 //        Person d1 = (Person) getOneObject(Person.class.getName(), "id", "1");
 56 //        Person d1=(Person)getOneObject("com.qin.model.Person", "name", "王婷");
 57 //        log.debug(d1);
 58     }
 59 
 60 }
 61 
 62 /**
 63  *
 64  * <br>
 65  * author 失足程序员<br>
 66  * mail 492794628@qq.com<br>
 67  * phone 13882122019<br>
 68  */
 69 @Table(name = "Person")
 70 class Person {
 71 
 72     private static final Logger log = Logger.getLogger(Person.class);
 73 
 74     @Id
 75     @Column(name = "_id")
 76     private int id;
 77 
 78     @Column(name = "dName", length = 655)
 79     private String name;
 80 
 81     private int age;
 82     private Short age1;
 83     private Byte age2;
 84     private String address;
 85 
 86     @Column(nullable = false)
 87     private Map<String, String> map = new HashMap<>();
 88 
 89     public Person() {
 90         // TODO Auto-generated constructor stub
 91     }
 92 
 93     public Person(int id, String name, int age, String address) {
 94         super();
 95         this.id = id;
 96         this.name = name;
 97         this.age = age;
 98         this.address = address;
 99     }
100 
101     public int getId() {
102         return id;
103     }
104 
105     public void setId(int id) {
106         this.id = id;
107     }
108 
109     public String getName() {
110         return name;
111     }
112 
113     public void setName(String name) {
114         this.name = name;
115     }
116 
117     public int getAge() {
118         return age;
119     }
120 
121     public void setAge(int age) {
122         this.age = age;
123     }
124 
125     public String getAddress() {
126         return address;
127     }
128 
129     public void setAddress(String address) {
130         this.address = address;
131     }
132 
133     public Map<String, String> getMap() {
134         return map;
135     }
136 
137     public void setMap(Map<String, String> map) {
138         this.map = map;
139     }
140 
141     public Short getAge1() {
142         return age1;
143     }
144 
145     public void setAge1(Short age1) {
146         this.age1 = age1;
147     }
148 
149     public Byte getAge2() {
150         return age2;
151     }
152 
153     public void setAge2(Byte age2) {
154         this.age2 = age2;
155     }
156 
157     @Override
158     public String toString() {
159         return "Person{" + "id=" + id + ", name=" + name + ", age=" + age + ", age1=" + age1 + ", age2=" + age2 + ", address=" + address + ", map=" + map + '}';
160     }
161 
162 }

 1 --- exec-maven-plugin:1.2.1:exec (default-cli) @ net.sz.game.engine ---
 2 [11-04 15:27:11:0938:ERROR: db.Dao.executeUpdate():1012] -> 
 3 com.mysql.jdbc.JDBC42PreparedStatement@7c53a9eb: DROP DATABASE IF EXISTS `test`; 执行结果：1
 4 [11-04 15:27:11:0943:ERROR: db.Dao.executeUpdate():1012] -> 
 5 com.mysql.jdbc.JDBC42PreparedStatement@2f333739: CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 执行结果：1
 6 [11-04 15:27:11:0944:ERROR: db.Dao.executeUpdate():1012] -> 
 7 com.mysql.jdbc.JDBC42PreparedStatement@12bb4df8: use `test`; 执行结果：0
 8 [11-04 15:27:11:0953:ERROR: db.Dao.getColumns():305] -> 类：net.sz.engine.db.Person 字段：log is transient or static or final;
 9 [11-04 15:27:11:0962:ERROR: db.MysqlDaoImp1.existsTable():69 ] -> 表：Person 检查结果： 无此表 
10 [11-04 15:27:12:0138:ERROR: db.MysqlDaoImp1.createTable():144] -> 
11 表：
12  create table if not exists `Person` (
13      `_id` int(4) not null primary key COMMENT '' ,
14      `dName` varchar(655) null COMMENT '' ,
15      `age` int(4) null COMMENT '' ,
16      `age1` tinyint(2) null COMMENT '' ,
17      `age2` tinyint(1) null COMMENT '' ,
18      `address` varchar(255) null COMMENT '' ,
19      `map` blob not null COMMENT '' 
20 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
21  创建完成；
22 [11-04 15:27:12:0141:ERROR: db.Dao.executeUpdate():1012] -> 
23 com.mysql.jdbc.JDBC42PreparedStatement@27ddd392: DELETE FROM `Person` 执行结果：0
24 [11-04 15:27:12:0143:ERROR: db.MysqlDaoImp1.existsTable():69 ] -> 表：Person 检查结果： 已经存在 
25 [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 
26 (64, '大象hadoop', 10, null, null, '家住Apache基金组织', x'ACED0005737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F4000000000000C770800000010000000017400017371007E000278' ),
27 (65, 'xxxx', 10, null, null, '家住Apache基金组织', x'ACED0005737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F4000000000000C770800000010000000017400017371007E000278' ); 添加数据 表：Person 结果 影响行数：2
28 [11-04 15:27:12:0356:ERROR: db.SqliteDaoImp1.existsTable():63 ] -> 表：Person 检查结果： 已经存在 
29 [11-04 15:27:12:0356:ERROR: db.SqliteDaoImp1.createTable():88 ] -> 表Person已经存在，检测字段变更，只会处理新增字段；
30 [11-04 15:27:12:0356:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：id 映射数据库字段：_id 存在，将不会修改
31 [11-04 15:27:12:0357:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：name 映射数据库字段：dName 存在，将不会修改
32 [11-04 15:27:12:0357:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：age 映射数据库字段：age 存在，将不会修改
33 [11-04 15:27:12:0357:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：age1 映射数据库字段：age1 存在，将不会修改
34 [11-04 15:27:12:0358:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：age2 映射数据库字段：age2 存在，将不会修改
35 [11-04 15:27:12:0360:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：address 映射数据库字段：address 存在，将不会修改
36 [11-04 15:27:12:0361:ERROR: db.SqliteDaoImp1.createTable():107] -> 表：Person 字段：map 映射数据库字段：map 存在，将不会修改
37 [11-04 15:27:12:0386:ERROR: db.Dao.executeUpdate():1012] -> 
38 org.sqlite.jdbc4.JDBC4PreparedStatement@66d33a 执行结果：2
39 [11-04 15:27:12:0386:ERROR: db.SqliteDaoImp1.existsTable():63 ] -> 表：Person 检查结果： 已经存在 
40 [11-04 15:27:12:0401:ERROR: db.Dao.addInsertSql():652] -> 执行 org.sqlite.jdbc4.JDBC4PreparedStatement@7cf10a6f 添加数据 表：Person 结果 影响行数：2
41 [11-04 15:27:12:0401:ERROR: db.Dao.getResultSet():777] -> 
42 com.mysql.jdbc.JDBC42PreparedStatement@7e0babb1: SELECT `_id`,`dName`,`age`,`age1`,`age2`,`address`,`map` FROM `Person` 
43 [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}}
44 [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}}
45 [11-04 15:27:12:0438:ERROR: db.Dao.update():981] -> 
46 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
47 [11-04 15:27:12:0452:ERROR: db.Dao.update():981] -> 
48 org.sqlite.jdbc4.JDBC4PreparedStatement@75bd9247 执行结果：1
49 ------------------------------------------------------------------------
50 BUILD SUCCESS
51 ------------------------------------------------------------------------
52 Total time: 2.299s
53 Finished at: Fri Nov 04 15:27:12 CST 2016
54 Final Memory: 7M/238M
55 ------------------------------------------------------------------------

由于这是出版的，并没有加入连接池，批量修改，和事务的处理；

想喷的尽管喷。

有什么建议和意见的也尽管提；

跪求保留标示符
/**
 * @author: Troy.Chen(失足程序员, 15388152619)
 * @version: 2021-07-20 10:55
 **/

C#版本代码 vs2010及以上工具可以

java 开发工具是netbeans 和 idea 版本，只有项目导入如果出现异常，请根据自己的工具调整


提供免费仓储。
最新的代码地址：↓↓↓
https://gitee.com/wuxindao

觉得我还可以，打赏一下吧，你的肯定是我努力的最大动力

posted on 2016-11-04 16:08 無心道（失足程序员）阅读(1078) 评论(2) 编辑收藏举报