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 ------------------------------------------------------------------------
由于这是出版的，并没有加入连接池，批量修改，和事务的处理；
想喷的尽管喷。
有什么建议和意见的也尽管提；
1486
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
