SQL创建拼接工具代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SQL创建拼接工具
作者:金马超 / 发布于2015/8/3/ 933
如果是使用JDBC进行开发，难免需要组装SQL，各种判断十分丑陋，insert、update和select三种已经完成，delete未编写

	package com.github.sqlcteator;
	
	import static com.github.sqlcteator.util.StrUtil.isEmpty;
	import static com.github.sqlcteator.util.StrUtil.isNotEmpty;
	
	import java.sql.SQLException;
	import java.util.ArrayList;
	import java.util.Collection;
	import java.util.List;
	import java.util.Map;
	
	import org.apache.commons.dbutils.BasicRowProcessor;
	import org.apache.commons.dbutils.GenerousBeanProcessor;
	import org.apache.commons.dbutils.RowProcessor;
	import org.apache.commons.dbutils.handlers.BeanHandler;
	import org.apache.commons.dbutils.handlers.BeanListHandler;
	import org.apache.commons.dbutils.handlers.ColumnListHandler;
	import org.apache.commons.dbutils.handlers.MapHandler;
	import org.apache.commons.dbutils.handlers.MapListHandler;
	import org.apache.commons.dbutils.handlers.ScalarHandler;
	import org.apache.commons.lang3.StringUtils;
	
	import com.github.sqlcteator.condition.OrderBy;
	import com.github.sqlcteator.mapping.MappingDb;
	import com.github.sqlcteator.util.StrUtil;
	import com.google.common.base.Joiner;
	
	/**
	* SQL SELECT查询器
	*
	* @Author 杨健/YangJian
	* @Date 2015年7月16日 下午3:21:32
	*/
	public class Query {
	
	 private final StringBuilder sql;
	 private final List<Object> parameters;
	 private final List<OrderBy> orders;
	 private boolean isDubeg = true;
	 private Class<?> clazz;
	
	 public void setDubeg(boolean isDubeg) {
	 this.isDubeg = isDubeg;
	 }
	
	 public Query() {
	 this.sql = new StringBuilder();
	 this.orders = new ArrayList<OrderBy>();
	 this.parameters = new ArrayList<Object>();
	 }
	
	 public Query(Class<?> clazz) {
	 this.clazz = clazz;
	 String table = MappingDb.camelToUnderscore(clazz.getSimpleName());
	 this.sql = new StringBuilder(" select * from ").append(table).append(" where 1=1 ");
	 this.orders = new ArrayList<OrderBy>();
	 this.parameters = new ArrayList<Object>();
	 }
	
	 public Query(String sql) {
	 this.sql = new StringBuilder(sql);
	 this.orders = new ArrayList<OrderBy>();
	 this.parameters = new ArrayList<Object>();
	 }
	
	 public static Query selectAll(String table) {
	 return new Query(" select * from ").append(table).append(" where 1=1 ");
	 }
	
	 public static Query select(String table, String... columnNames) {
	 Query sql = new Query(" select ");
	 sql.append(StringUtils.join(columnNames, ","));
	 sql.append(" from ");
	 sql.append(table);
	 sql.append(" where 1=1 ");
	 return sql;
	 }
	
	 /**
	 * 直接拼接sql片段
	 *
	 * @param segment
	 * sql片段
	 * @return
	 * @return Query
	 * @Author 杨健/YangJian
	 * @Date 2015年7月16日 上午10:52:11
	 * @Version 1.0.0
	 */
	 public Query append(String segment) {
	 if (isEmpty(segment))
	 return this;
	
	 sql.append(segment);
	 return this;
	 }
	
	 @Override
	 public String toString() {
	 return sql.toString();
	 }
	
	 public List<Object> getParameters() {
	 return parameters;
	 }
	
	 public Query setParameters(Object parameter) {
	 if (isEmpty(parameter))
	 return this;
	 parameters.add(parameter);
	 return this;
	 }
	
	 public Query setParameters(List<Object> parameter) {
	 if (isEmpty(parameter))
	 return this;
	 parameters.addAll(parameter);
	 return this;
	 }
	
	 /**
	 * 拼接SQL查询条件（and）
	 *
	 * @param sql
	 * @param params
	 * 保存参数值
	 * @param columnName
	 * 字段
	 * @param value
	 * 参数值
	 * @return Query
	 * @Author 杨健/YangJian
	 * @Date 2015年6月30日 上午11:46:48
	 * @Version 1.0.0
	 */
	 public Query eq(String columnName, Object value) {
	
	 if (isEmpty(value))
	 return this;
	
	 sql.append(" and ").append(columnName).append(" = ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /** 不相等 */
	 public Query notEq(String columnName, Object value) {
	 if (isEmpty(value))
	 return this;
	
	 sql.append(" and ").append(columnName).append(" <> ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 不加单引号
	 *
	 * @param columnName
	 * @param value
	 * @return
	 * @return Query
	 * @Author 杨健/YangJian
	 * @Date 2015年7月19日 下午2:52:52
	 * @Version 1.0.0
	 */
	 public Query in(String columnName, Object value) {
	 return in(columnName, value, false);
	 }
	
	 /**
	 * in
	 *
	 * @param sql
	 * @param columnName
	 * @param value
	 * "WallLatticeStatus_2", "WallLatticeStatus_1" 或 Lists.newArrayList("WallLatticeStatus_2",
	 * "WallLatticeStatus_1");
	 * @param isQuotationMark
	 * 是否需要引号
	 * @return Query
	 * @Author 杨健/YangJian
	 * @Date 2015年7月15日 下午5:22:49
	 * @Version 1.0.0
	 */
	 public Query in(String columnName, Object value, boolean isQuotationMark) {
	 if (isEmpty(value))
	 return this;
	
	 String quotationMark = isQuotationMark == true ? "'" : "";
	 if (value instanceof Collection<?>) {
	 value = StrUtil.strAppend(Joiner.on(",").skipNulls().join((Collection<?>) value), ",", quotationMark);
	 } else {
	 value = StringUtils.join(quotationMark, value, quotationMark);
	 }
	
	 if (isEmpty(value) || "null".equals(value))
	 return this;
	
	 sql.append(" and ").append(columnName).append(" in ( ").append(value).append(" )");
	 return this;
	 }
	
	 /**
	 * not in
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 值集合
	 * @param isQuotationMark
	 * 是否需要引号
	 * @return
	 */
	 public Query notIn(String columnName, Object value, boolean isQuotationMark) {
	 if (isEmpty(value))
	 return this;
	
	 String quotationMark = isQuotationMark == true ? "'" : "";
	 if (value instanceof Collection<?>) {
	 value = StrUtil.strAppend(Joiner.on(",").skipNulls().join((Collection<?>) value), ",", quotationMark);
	 } else {
	 value = StringUtils.join(quotationMark, value, quotationMark);
	 }
	
	 if (isEmpty(value) || "null".equals(value))
	 return this;
	
	 sql.append(" and ").append(columnName).append(" not in ( ").append(value).append(" )");
	 return this;
	 }
	
	 /** 空 */
	 public Query isNull(String columnName) {
	 if (isEmpty(columnName))
	 return this;
	
	 sql.append(" and ").append(columnName).append(" is null ");
	 return this;
	 }
	
	 /** 非空 */
	 public Query isNotNull(String columnName) {
	 if (isEmpty(columnName))
	 return this;
	
	 sql.append(" and ").append(columnName).append(" is not null ");
	 return this;
	 }
	
	 public Query or(String columnName, Object value) {
	 if (isEmpty(columnName))
	 return this;
	 if (isEmpty(value))
	 return this;
	 sql.append(" or ").append(columnName).append(" = ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 模糊匹配
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Query like(String columnName, String value) {
	 if (isEmpty(value))
	 return this;
	 if (value.indexOf("%") < 0)
	 value = StringUtils.join("'%", value, "%'");
	
	 sql.append(" and ").append(columnName).append(" like ").append(value);
	 return this;
	 }
	
	 /**
	 * 时间区间查询
	 *
	 * @param columnName
	 * 属性名称
	 * @param lo
	 * 日期属性起始值
	 * @param go
	 * 日期属性结束值
	 * @return
	 */
	 public Query between(String columnName, String lo, String go) {
	 if (isNotEmpty(lo) && isNotEmpty(go)) {
	 return this;
	 }
	
	 if (isNotEmpty(lo) && isEmpty(go)) {
	 sql.append(" and ").append(columnName).append(" >= ? ");
	 setParameters(lo);
	 return this;
	 }
	
	 if (isEmpty(lo) && isNotEmpty(go)) {
	 sql.append(" and ").append(columnName).append(" <= ? ");
	 setParameters(go);
	 return this;
	 }
	
	 sql.append(" between ? and ? ");
	 setParameters(lo);
	 setParameters(go);
	 return this;
	 }
	
	 public Query between(String columnName, Number lo, Number go) {
	 if (isNotEmpty(lo))
	 ge(columnName, lo);
	
	 if (isNotEmpty(go))
	 le(columnName, go);
	
	 return this;
	 }
	
	 /**
	 * 小于等于
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Query le(String columnName, Number value) {
	 if (isEmpty(value)) {
	 return this;
	 }
	 sql.append(" and ").append(columnName).append(" <= ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 小于
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Query lt(String columnName, Number value) {
	 if (isEmpty(value)) {
	 return this;
	 }
	 sql.append(" and ").append(columnName).append(" < ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 大于等于
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Query ge(String columnName, Number value) {
	 if (isEmpty(value)) {
	 return this;
	 }
	 sql.append(" and ").append(columnName).append(" >= ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 大于
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Query gt(String columnName, Number value) {
	 if (isEmpty(value)) {
	 return this;
	 }
	 sql.append(" and ").append(columnName).append(" > ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 排序
	 *
	 * @param order
	 * @return Query
	 * @Author 杨健/YangJian
	 * @Date 2015年7月28日 下午3:41:43
	 * @Version 1.0.0
	 */
	 public Query orderBy(OrderBy order) {
	 if (isEmpty(order)) {
	 return this;
	 }
	 this.orders.add(order);
	 return this;
	 }
	
	 public Query limit(int start, int limit) {
	 sql.append(" limit ? offset ? ");
	 parameters.add(limit);
	 parameters.add(start);
	 return this;
	 }
	
	 /** 排序 */
	 private void appendOrderBy() {
	 if (isEmpty(orders)) {
	 return;
	 }
	 sql.append(" order by ");
	
	 int size = orders.size();
	
	 for (int i = 0; i < size; i++) {
	 sql.append(orders.get(i).toString());
	 if (i < size - 1) {
	 sql.append(",");
	 }
	 }
	 }
	
	 public static <T> BeanHandler<T> getBeanHandler(Class<T> clazz) {
	 RowProcessor rowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());
	 BeanHandler<T> bh = new BeanHandler<T>(clazz, rowProcessor);
	 return bh;
	 }
	
	 public static <T> BeanListHandler<T> getBeanListHandler(Class<T> clazz) {
	 RowProcessor rowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());
	 BeanListHandler<T> bh = new BeanListHandler<T>(clazz, rowProcessor);
	 return bh;
	 }
	
	 public Long getCount() throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	 ScalarHandler<Long> handler = new ScalarHandler<Long>(1);
	 Long count = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), handler, parameters.toArray());
	 debug(count);
	 return count;
	 }
	
	 public <T> T getCount(ScalarHandler<T> sh) throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	 T count = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), sh, parameters.toArray());
	 debug(count);
	 return count;
	 }
	
	 public Map<String, Object> map() throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	 appendOrderBy();
	 Map<String, Object> map = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), new MapHandler(),
	 parameters.toArray());
	 debug(map);
	 return map;
	 }
	
	 public List<Map<String, Object>> listmap() throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	 appendOrderBy();
	 List<Map<String, Object>> map = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(),
	 new MapListHandler(), parameters.toArray());
	 debug(map);
	 return map;
	 }
	
	 @SuppressWarnings("unchecked")
	 public <T> T singleResult() throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	
	 if (isEmpty(clazz)) {
	 throw new IllegalArgumentException("Not set clazz!");
	 }
	
	 BeanHandler<T> beanHandler = (BeanHandler<T>) getBeanHandler(clazz);
	 T list = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), beanHandler, parameters.toArray());
	 debug(list);
	 return list;
	 }
	
	 @SuppressWarnings("unchecked")
	 public <T> List<T> list() throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	
	 if (isEmpty(clazz)) {
	 throw new IllegalArgumentException("Not set clazz!");
	 }
	
	 appendOrderBy();
	
	 BeanListHandler<T> beanListHandler = (BeanListHandler<T>) getBeanListHandler(clazz);
	 List<T> list = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), beanListHandler,
	 parameters.toArray());
	
	 debug(list);
	 return list;
	 }
	
	 public <T> T singleResult(Class<T> clazz) throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	 BeanHandler<T> beanHandler = getBeanHandler(clazz);
	 T list = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), beanHandler, parameters.toArray());
	 debug(list);
	 return list;
	 }
	
	 public <T> List<T> list(Class<T> clazz) throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	
	 appendOrderBy();
	
	 BeanListHandler<T> beanListHandler = getBeanListHandler(clazz);
	 List<T> list = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), beanListHandler,
	 parameters.toArray());
	
	 debug(list);
	 return list;
	 }
	
	 public <T> List<T> list(ColumnListHandler<T> columnListHandler) throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	
	 appendOrderBy();
	
	 List<T> list = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), columnListHandler,
	 parameters.toArray());
	 debug(list);
	 return list;
	 }
	
	 private void debug(Object result) {
	
	 if (!isDubeg) {
	 return;
	 }
	
	 System.out.println("SQL=" + sql.toString());
	 System.out.println("SQL Parameters=" + parameters.toArray());
	 System.out.println("SQL Value=" + result);
	 }
	
	}
	2. [文件] Query.java ~ 14KB 下载(2)
	?
	1
	2
	3
	4
	5
	6
	7
	8
	9
	10
	11
	12
	13
	14
	15
	16
	17
	18
	19
	20
	21
	22
	23
	24
	25
	26
	27
	28
	29
	30
	31
	32
	33
	34
	35
	36
	37
	38
	39
	40
	41
	42
	43
	44
	45
	46
	47
	48
	49
	50
	51
	52
	53
	54
	55
	56
	57
	58
	59
	60
	61
	62
	63
	64
	65
	66
	67
	68
	69
	70
	71
	72
	73
	74
	75
	76
	77
	78
	79
	80
	81
	82
	83
	84
	85
	86
	87
	88
	89
	90
	91
	92
	93
	94
	95
	96
	97
	98
	99
	100
	101
	102
	103
	104
	105
	106
	107
	108
	109
	110
	111
	112
	113
	114
	115
	116
	117
	118
	119
	120
	121
	122
	123
	124
	125
	126
	127
	128
	129
	130
	131
	132
	133
	134
	135
	136
	137
	138
	139
	140
	141
	142
	143
	144
	145
	146
	147
	148
	149
	150
	151
	152
	153
	154
	155
	156
	157
	158
	159
	160
	161
	162
	163
	164
	165
	166
	167
	168
	169
	170
	171
	172
	173
	174
	175
	176
	177
	178
	179
	180
	181
	182
	183
	184
	185
	186
	187
	188
	189
	190
	191
	192
	193
	194
	195
	196
	197
	198
	199
	200
	201
	202
	203
	204
	205
	206
	207
	208
	209
	210
	211
	212
	213
	214
	215
	216
	217
	218
	219
	220
	221
	222
	223
	224
	225
	226
	227
	228
	229
	230
	231
	232
	233
	234
	235
	236
	237
	238
	239
	240
	241
	242
	243
	244
	245
	246
	247
	248
	249
	250
	251
	252
	253
	254
	255
	256
	257
	258
	259
	260
	261
	262
	263
	264
	265
	266
	267
	268
	269
	270
	271
	272
	273
	274
	275
	276
	277
	278
	279
	280
	281
	282
	283
	284
	285
	286
	287
	288
	289
	290
	291
	292
	293
	294
	295
	296
	297
	298
	299
	300
	301
	302
	303
	304
	305
	306
	307
	308
	309
	310
	311
	312
	313
	314
	315
	316
	317
	318
	319
	320
	321
	322
	323
	324
	325
	326
	327
	328
	329
	330
	331
	332
	333
	334
	335
	336
	337
	338
	339
	340
	341
	342
	343
	344
	345
	346
	347
	348
	349
	350
	351
	352
	353
	354
	355
	356
	357
	358
	359
	360
	361
	362
	363
	364
	365
	366
	367
	368
	369
	370
	371
	372
	373
	374
	375
	376
	377
	378
	379
	380
	381
	382
	383
	384
	385
	386
	387
	388
	389
	390
	391
	392
	393
	394
	395
	396
	397
	398
	399
	400
	401
	402
	403
	404
	405
	406
	407
	408
	409
	410
	411
	412
	413
	414
	415
	416
	417
	418
	419
	420
	421
	422
	423
	424
	425
	426
	427
	428
	429
	430
	431
	432
	433
	434
	435
	436
	437
	438
	439
	440
	441
	442
	443
	444
	445
	446
	447
	448
	449
	450
	451
	452
	453
	454
	455
	456
	457
	458
	459
	460
	461
	462
	463
	464
	465
	466
	467
	468
	469
	470
	471
	472
	473
	474
	475
	476
	477
	478
	479
	480
	481
	482
	483
	484
	485
	486
	487
	488
	489
	490
	491
	492
	493
	494
	495
	496
	497
	498
	499
	500
	501
	502
	503
	504
	505
	506
	507
	508
	509
	510
	511
	512
	513
	514
	515
	516
	517
	518
	519
	520
	521
	522
	523
	524
	525
	526
	527
	528
	529
	530
	531
	532
	533
	534
	535
	536
	537
	538
	539
	540
	541
	542
	543
	544
	545
	546
	547
	548
	549
	550
	551
	552
	553
	554
	555
	556
	557
	558
	559
	560
	561
	562
	563
	564
	565
	566
	567
	568
	569
	570
	package com.github.sqlcteator;
	
	import static com.github.sqlcteator.util.StrUtil.isEmpty;
	import static com.github.sqlcteator.util.StrUtil.isNotEmpty;
	
	import java.sql.SQLException;
	import java.util.ArrayList;
	import java.util.Collection;
	import java.util.List;
	import java.util.Map;
	
	import org.apache.commons.dbutils.BasicRowProcessor;
	import org.apache.commons.dbutils.GenerousBeanProcessor;
	import org.apache.commons.dbutils.RowProcessor;
	import org.apache.commons.dbutils.handlers.BeanHandler;
	import org.apache.commons.dbutils.handlers.BeanListHandler;
	import org.apache.commons.dbutils.handlers.ColumnListHandler;
	import org.apache.commons.dbutils.handlers.MapHandler;
	import org.apache.commons.dbutils.handlers.MapListHandler;
	import org.apache.commons.dbutils.handlers.ScalarHandler;
	import org.apache.commons.lang3.StringUtils;
	
	import com.github.sqlcteator.condition.OrderBy;
	import com.github.sqlcteator.mapping.MappingDb;
	import com.github.sqlcteator.util.StrUtil;
	import com.google.common.base.Joiner;
	
	/**
	* SQL SELECT查询器
	*
	* @Author 杨健/YangJian
	* @Date 2015年7月16日 下午3:21:32
	*/
	public class Query {
	
	 private final StringBuilder sql;
	 private final List<Object> parameters;
	 private final List<OrderBy> orders;
	 private boolean isDubeg = true;
	 private Class<?> clazz;
	
	 public void setDubeg(boolean isDubeg) {
	 this.isDubeg = isDubeg;
	 }
	
	 public Query() {
	 this.sql = new StringBuilder();
	 this.orders = new ArrayList<OrderBy>();
	 this.parameters = new ArrayList<Object>();
	 }
	
	 public Query(Class<?> clazz) {
	 this.clazz = clazz;
	 String table = MappingDb.camelToUnderscore(clazz.getSimpleName());
	 this.sql = new StringBuilder(" select * from ").append(table).append(" where 1=1 ");
	 this.orders = new ArrayList<OrderBy>();
	 this.parameters = new ArrayList<Object>();
	 }
	
	 public Query(String sql) {
	 this.sql = new StringBuilder(sql);
	 this.orders = new ArrayList<OrderBy>();
	 this.parameters = new ArrayList<Object>();
	 }
	
	 public static Query selectAll(String table) {
	 return new Query(" select * from ").append(table).append(" where 1=1 ");
	 }
	
	 public static Query select(String table, String... columnNames) {
	 Query sql = new Query(" select ");
	 sql.append(StringUtils.join(columnNames, ","));
	 sql.append(" from ");
	 sql.append(table);
	 sql.append(" where 1=1 ");
	 return sql;
	 }
	
	 /**
	 * 直接拼接sql片段
	 *
	 * @param segment
	 * sql片段
	 * @return
	 * @return Query
	 * @Author 杨健/YangJian
	 * @Date 2015年7月16日 上午10:52:11
	 * @Version 1.0.0
	 */
	 public Query append(String segment) {
	 if (isEmpty(segment))
	 return this;
	
	 sql.append(segment);
	 return this;
	 }
	
	 @Override
	 public String toString() {
	 return sql.toString();
	 }
	
	 public List<Object> getParameters() {
	 return parameters;
	 }
	
	 public Query setParameters(Object parameter) {
	 if (isEmpty(parameter))
	 return this;
	 parameters.add(parameter);
	 return this;
	 }
	
	 public Query setParameters(List<Object> parameter) {
	 if (isEmpty(parameter))
	 return this;
	 parameters.addAll(parameter);
	 return this;
	 }
	
	 /**
	 * 拼接SQL查询条件（and）
	 *
	 * @param sql
	 * @param params
	 * 保存参数值
	 * @param columnName
	 * 字段
	 * @param value
	 * 参数值
	 * @return Query
	 * @Author 杨健/YangJian
	 * @Date 2015年6月30日 上午11:46:48
	 * @Version 1.0.0
	 */
	 public Query eq(String columnName, Object value) {
	
	 if (isEmpty(value))
	 return this;
	
	 sql.append(" and ").append(columnName).append(" = ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /** 不相等 */
	 public Query notEq(String columnName, Object value) {
	 if (isEmpty(value))
	 return this;
	
	 sql.append(" and ").append(columnName).append(" <> ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 不加单引号
	 *
	 * @param columnName
	 * @param value
	 * @return
	 * @return Query
	 * @Author 杨健/YangJian
	 * @Date 2015年7月19日 下午2:52:52
	 * @Version 1.0.0
	 */
	 public Query in(String columnName, Object value) {
	 return in(columnName, value, false);
	 }
	
	 /**
	 * in
	 *
	 * @param sql
	 * @param columnName
	 * @param value
	 * "WallLatticeStatus_2", "WallLatticeStatus_1" 或 Lists.newArrayList("WallLatticeStatus_2",
	 * "WallLatticeStatus_1");
	 * @param isQuotationMark
	 * 是否需要引号
	 * @return Query
	 * @Author 杨健/YangJian
	 * @Date 2015年7月15日 下午5:22:49
	 * @Version 1.0.0
	 */
	 public Query in(String columnName, Object value, boolean isQuotationMark) {
	 if (isEmpty(value))
	 return this;
	
	 String quotationMark = isQuotationMark == true ? "'" : "";
	 if (value instanceof Collection<?>) {
	 value = StrUtil.strAppend(Joiner.on(",").skipNulls().join((Collection<?>) value), ",", quotationMark);
	 } else {
	 value = StringUtils.join(quotationMark, value, quotationMark);
	 }
	
	 if (isEmpty(value) || "null".equals(value))
	 return this;
	
	 sql.append(" and ").append(columnName).append(" in ( ").append(value).append(" )");
	 return this;
	 }
	
	 /**
	 * not in
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 值集合
	 * @param isQuotationMark
	 * 是否需要引号
	 * @return
	 */
	 public Query notIn(String columnName, Object value, boolean isQuotationMark) {
	 if (isEmpty(value))
	 return this;
	
	 String quotationMark = isQuotationMark == true ? "'" : "";
	 if (value instanceof Collection<?>) {
	 value = StrUtil.strAppend(Joiner.on(",").skipNulls().join((Collection<?>) value), ",", quotationMark);
	 } else {
	 value = StringUtils.join(quotationMark, value, quotationMark);
	 }
	
	 if (isEmpty(value) || "null".equals(value))
	 return this;
	
	 sql.append(" and ").append(columnName).append(" not in ( ").append(value).append(" )");
	 return this;
	 }
	
	 /** 空 */
	 public Query isNull(String columnName) {
	 if (isEmpty(columnName))
	 return this;
	
	 sql.append(" and ").append(columnName).append(" is null ");
	 return this;
	 }
	
	 /** 非空 */
	 public Query isNotNull(String columnName) {
	 if (isEmpty(columnName))
	 return this;
	
	 sql.append(" and ").append(columnName).append(" is not null ");
	 return this;
	 }
	
	 public Query or(String columnName, Object value) {
	 if (isEmpty(columnName))
	 return this;
	 if (isEmpty(value))
	 return this;
	 sql.append(" or ").append(columnName).append(" = ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 模糊匹配
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Query like(String columnName, String value) {
	 if (isEmpty(value))
	 return this;
	 if (value.indexOf("%") < 0)
	 value = StringUtils.join("'%", value, "%'");
	
	 sql.append(" and ").append(columnName).append(" like ").append(value);
	 return this;
	 }
	
	 /**
	 * 时间区间查询
	 *
	 * @param columnName
	 * 属性名称
	 * @param lo
	 * 日期属性起始值
	 * @param go
	 * 日期属性结束值
	 * @return
	 */
	 public Query between(String columnName, String lo, String go) {
	 if (isNotEmpty(lo) && isNotEmpty(go)) {
	 return this;
	 }
	
	 if (isNotEmpty(lo) && isEmpty(go)) {
	 sql.append(" and ").append(columnName).append(" >= ? ");
	 setParameters(lo);
	 return this;
	 }
	
	 if (isEmpty(lo) && isNotEmpty(go)) {
	 sql.append(" and ").append(columnName).append(" <= ? ");
	 setParameters(go);
	 return this;
	 }
	
	 sql.append(" between ? and ? ");
	 setParameters(lo);
	 setParameters(go);
	 return this;
	 }
	
	 public Query between(String columnName, Number lo, Number go) {
	 if (isNotEmpty(lo))
	 ge(columnName, lo);
	
	 if (isNotEmpty(go))
	 le(columnName, go);
	
	 return this;
	 }
	
	 /**
	 * 小于等于
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Query le(String columnName, Number value) {
	 if (isEmpty(value)) {
	 return this;
	 }
	 sql.append(" and ").append(columnName).append(" <= ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 小于
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Query lt(String columnName, Number value) {
	 if (isEmpty(value)) {
	 return this;
	 }
	 sql.append(" and ").append(columnName).append(" < ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 大于等于
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Query ge(String columnName, Number value) {
	 if (isEmpty(value)) {
	 return this;
	 }
	 sql.append(" and ").append(columnName).append(" >= ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 大于
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Query gt(String columnName, Number value) {
	 if (isEmpty(value)) {
	 return this;
	 }
	 sql.append(" and ").append(columnName).append(" > ? ");
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 排序
	 *
	 * @param order
	 * @return Query
	 * @Author 杨健/YangJian
	 * @Date 2015年7月28日 下午3:41:43
	 * @Version 1.0.0
	 */
	 public Query orderBy(OrderBy order) {
	 if (isEmpty(order)) {
	 return this;
	 }
	 this.orders.add(order);
	 return this;
	 }
	
	 public Query limit(int start, int limit) {
	 sql.append(" limit ? offset ? ");
	 parameters.add(limit);
	 parameters.add(start);
	 return this;
	 }
	
	 /** 排序 */
	 private void appendOrderBy() {
	 if (isEmpty(orders)) {
	 return;
	 }
	 sql.append(" order by ");
	
	 int size = orders.size();
	
	 for (int i = 0; i < size; i++) {
	 sql.append(orders.get(i).toString());
	 if (i < size - 1) {
	 sql.append(",");
	 }
	 }
	 }
	
	 public static <T> BeanHandler<T> getBeanHandler(Class<T> clazz) {
	 RowProcessor rowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());
	 BeanHandler<T> bh = new BeanHandler<T>(clazz, rowProcessor);
	 return bh;
	 }
	
	 public static <T> BeanListHandler<T> getBeanListHandler(Class<T> clazz) {
	 RowProcessor rowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());
	 BeanListHandler<T> bh = new BeanListHandler<T>(clazz, rowProcessor);
	 return bh;
	 }
	
	 public Long getCount() throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	 ScalarHandler<Long> handler = new ScalarHandler<Long>(1);
	 Long count = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), handler, parameters.toArray());
	 debug(count);
	 return count;
	 }
	
	 public <T> T getCount(ScalarHandler<T> sh) throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	 T count = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), sh, parameters.toArray());
	 debug(count);
	 return count;
	 }
	
	 public Map<String, Object> map() throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	 appendOrderBy();
	 Map<String, Object> map = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), new MapHandler(),
	 parameters.toArray());
	 debug(map);
	 return map;
	 }
	
	 public List<Map<String, Object>> listmap() throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	 appendOrderBy();
	 List<Map<String, Object>> map = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(),
	 new MapListHandler(), parameters.toArray());
	 debug(map);
	 return map;
	 }
	
	 @SuppressWarnings("unchecked")
	 public <T> T singleResult() throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	
	 if (isEmpty(clazz)) {
	 throw new IllegalArgumentException("Not set clazz!");
	 }
	
	 BeanHandler<T> beanHandler = (BeanHandler<T>) getBeanHandler(clazz);
	 T list = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), beanHandler, parameters.toArray());
	 debug(list);
	 return list;
	 }
	
	 @SuppressWarnings("unchecked")
	 public <T> List<T> list() throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	
	 if (isEmpty(clazz)) {
	 throw new IllegalArgumentException("Not set clazz!");
	 }
	
	 appendOrderBy();
	
	 BeanListHandler<T> beanListHandler = (BeanListHandler<T>) getBeanListHandler(clazz);
	 List<T> list = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), beanListHandler,
	 parameters.toArray());
	
	 debug(list);
	 return list;
	 }
	
	 public <T> T singleResult(Class<T> clazz) throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	 BeanHandler<T> beanHandler = getBeanHandler(clazz);
	 T list = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), beanHandler, parameters.toArray());
	 debug(list);
	 return list;
	 }
	
	 public <T> List<T> list(Class<T> clazz) throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	
	 appendOrderBy();
	
	 BeanListHandler<T> beanListHandler = getBeanListHandler(clazz);
	 List<T> list = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), beanListHandler,
	 parameters.toArray());
	
	 debug(list);
	 return list;
	 }
	
	 public <T> List<T> list(ColumnListHandler<T> columnListHandler) throws SQLException {
	 if (isEmpty(sql)) {
	 return null;
	 }
	
	 appendOrderBy();
	
	 List<T> list = ConnectionUtils.getRunnerWithDataSource().query(sql.toString(), columnListHandler,
	 parameters.toArray());
	 debug(list);
	 return list;
	 }
	
	 private void debug(Object result) {
	
	 if (!isDubeg) {
	 return;
	 }
	
	 System.out.println("SQL=" + sql.toString());
	 System.out.println("SQL Parameters=" + parameters.toArray());
	 System.out.println("SQL Value=" + result);
	 }
	
	}
	3. [文件] Insert.java ~ 9KB 下载(2)
	?
	1
	2
	3
	4
	5
	6
	7
	8
	9
	10
	11
	12
	13
	14
	15
	16
	17
	18
	19
	20
	21
	22
	23
	24
	25
	26
	27
	28
	29
	30
	31
	32
	33
	34
	35
	36
	37
	38
	39
	40
	41
	42
	43
	44
	45
	46
	47
	48
	49
	50
	51
	52
	53
	54
	55
	56
	57
	58
	59
	60
	61
	62
	63
	64
	65
	66
	67
	68
	69
	70
	71
	72
	73
	74
	75
	76
	77
	78
	79
	80
	81
	82
	83
	84
	85
	86
	87
	88
	89
	90
	91
	92
	93
	94
	95
	96
	97
	98
	99
	100
	101
	102
	103
	104
	105
	106
	107
	108
	109
	110
	111
	112
	113
	114
	115
	116
	117
	118
	119
	120
	121
	122
	123
	124
	125
	126
	127
	128
	129
	130
	131
	132
	133
	134
	135
	136
	137
	138
	139
	140
	141
	142
	143
	144
	145
	146
	147
	148
	149
	150
	151
	152
	153
	154
	155
	156
	157
	158
	159
	160
	161
	162
	163
	164
	165
	166
	167
	168
	169
	170
	171
	172
	173
	174
	175
	176
	177
	178
	179
	180
	181
	182
	183
	184
	185
	186
	187
	188
	189
	190
	191
	192
	193
	194
	195
	196
	197
	198
	199
	200
	201
	202
	203
	204
	205
	206
	207
	208
	209
	210
	211
	212
	213
	214
	215
	216
	217
	218
	219
	220
	221
	222
	223
	224
	225
	226
	227
	228
	229
	230
	231
	232
	233
	234
	235
	236
	237
	238
	239
	240
	241
	242
	243
	244
	245
	246
	247
	248
	249
	250
	251
	252
	253
	254
	255
	256
	257
	258
	259
	260
	261
	262
	263
	264
	265
	266
	267
	268
	269
	270
	271
	272
	273
	274
	275
	276
	277
	278
	279
	280
	281
	282
	283
	284
	285
	286
	287
	288
	289
	290
	291
	292
	293
	294
	295
	296
	297
	298
	299
	300
	301
	302
	303
	304
	305
	306
	307
	308
	309
	310
	311
	312
	313
	314
	315
	316
	317
	318
	319
	320
	321
	322
	323
	324
	325
	326
	package com.github.sqlcteator;
	
	import static com.github.sqlcteator.util.StrUtil.isEmpty;
	
	import java.sql.Connection;
	import java.sql.SQLException;
	import java.sql.Timestamp;
	import java.util.Collections;
	import java.util.Date;
	import java.util.LinkedList;
	import java.util.List;
	
	import org.apache.commons.dbutils.handlers.ColumnListHandler;
	import org.apache.commons.lang3.StringUtils;
	
	import com.github.sqlcteator.mapping.MappingDb;
	import com.github.sqlcteator.mapping.MappingField;
	import com.google.common.collect.Lists;
	
	/**
	*
	* @Comment SQL INSERT
	* @Author 杨健/YangJian
	* @Date 2015年7月2日 下午2:32:08
	* @Version 1.0.0
	*/
	public class Insert {
	
	 private String table;
	 private Class<?> clazz;
	 private MappingDb mappingDb;
	 private final List<MappingField> fields;
	 private final List<String> columns;
	 private final List<Object[]> values;
	 private boolean seqBefore = false;
	 private boolean terminated = false;
	 private boolean isPrepareStatement = true;
	 private final StringBuilder sql;
	 private final static ColumnListHandler<Long> rsh = new ColumnListHandler<Long>("id");
	 private boolean isDubeg = false;
	
	 public Insert() {
	 this.sql = new StringBuilder(" INSERT INTO ");
	 this.fields = new LinkedList<>();
	 this.columns = new LinkedList<>();
	 this.values = new LinkedList<>();
	 }
	
	 public Insert(String table) {
	 this();
	 this.table = table;
	 }
	
	 public Insert(Class<?> clazz) {
	 this();
	 this.clazz = clazz;
	 this.mappingDb = new MappingDb(this.clazz);
	 this.table = MappingDb.camelToUnderscore(this.clazz.getSimpleName());
	 if (this.mappingDb.isMapUnderscoreToCamelCase()) {
	 this.table = MappingDb.camelToUnderscore(this.clazz.getSimpleName());
	 } else {
	 this.table = this.clazz.getSimpleName();
	 }
	 this.fields.addAll(this.mappingDb.getFields());
	 this.columns.addAll(this.mappingDb.getColumns());
	 }
	
	 public Insert(Class<?> clazz, boolean mapUnderscoreToCamelCase) {
	 this();
	 this.clazz = clazz;
	 this.mappingDb = new MappingDb(this.clazz);
	 this.mappingDb.setMapUnderscoreToCamelCase(mapUnderscoreToCamelCase);
	 if (mapUnderscoreToCamelCase) {
	 this.table = MappingDb.camelToUnderscore(this.clazz.getSimpleName());
	 } else {
	 this.table = this.clazz.getSimpleName();
	 }
	 this.fields.addAll(this.mappingDb.getFields());
	 this.columns.addAll(this.mappingDb.getColumns());
	 }
	
	 public Insert(Object obj) {
	 this();
	 this.clazz = obj.getClass();
	 this.mappingDb = new MappingDb(obj);
	 if (this.mappingDb.isMapUnderscoreToCamelCase()) {
	 this.table = MappingDb.camelToUnderscore(this.clazz.getSimpleName());
	 } else {
	 this.table = this.clazz.getSimpleName();
	 }
	 this.fields.addAll(this.mappingDb.getFields());
	 this.columns.addAll(this.mappingDb.getColumns());
	 this.values.add(this.mappingDb.getValues());
	 }
	
	 public Insert(Object obj, boolean mapUnderscoreToCamelCase) {
	 this();
	 this.clazz = obj.getClass();
	 this.mappingDb = new MappingDb(obj);
	 this.mappingDb.setMapUnderscoreToCamelCase(mapUnderscoreToCamelCase);
	 if (mapUnderscoreToCamelCase) {
	 this.table = MappingDb.camelToUnderscore(this.clazz.getSimpleName());
	 } else {
	 this.table = this.clazz.getSimpleName();
	 }
	 this.fields.addAll(this.mappingDb.getFields());
	 this.columns.addAll(this.mappingDb.getColumns());
	 this.values.add(this.mappingDb.getValues());
	 }
	
	 public Insert insert(String table) {
	 this.table = table;
	 return this;
	 }
	
	 /**
	 * 设置序列seq前置还是后置，默认后置
	 *
	 * @param seqBefore
	 * @return void
	 * @Author 杨健/YangJian
	 * @Date 2015年7月21日 上午11:38:17
	 * @Version 1.0.0
	 */
	 public void setSeqBefore(boolean seqBefore) {
	 this.seqBefore = seqBefore;
	 }
	
	 public String getSequenceName() {
	 String sequenceName = StringUtils.join(this.table, "_id_seq");
	 if (seqBefore) {
	 sequenceName = StringUtils.join("seq_", this.table, "_id");
	 }
	 return sequenceName.toUpperCase();
	 }
	
	 public Insert append(String expression) {
	 sql.append(expression);
	 return this;
	 }
	
	 public Insert appendLine(String expression) {
	 sql.append(expression);
	 return this;
	 }
	
	 public Insert table(String table) {
	 this.table = table;
	 return this;
	 }
	
	 public Insert columns(String... columns) {
	 Collections.addAll(this.columns, columns);
	 return this;
	 }
	
	 public Insert values(Object... values) {
	 this.values.add(values);
	 return this;
	 }
	
	 public String toString() {
	 toString(true);
	 return sql.toString();
	 }
	
	 public String toString(boolean isPrepareStatement) {
	 this.isPrepareStatement = isPrepareStatement;
	 if (isPrepareStatement) {
	 terminatePrepareStatement();
	 } else {
	 terminate();
	 }
	 return sql.toString();
	 }
	
	 private void terminatePrepareStatement() {
	 if (columns.isEmpty())
	 throw new RuntimeException("No columns informed!");
	 if (values.isEmpty())
	 throw new RuntimeException("No values informed!");
	
	 for (Object[] valueSet : values) {
	 if (valueSet.length != columns.size()) {
	 throw new RuntimeException("Value size different from column size!");
	 }
	 }
	
	 if (!terminated) {
	 this.appendLine(table).append(" ( ").append(StringUtils.join(columns, ", ")).appendLine(" )")
	 .append(" VALUES (");
	
	 for (MappingField field : fields) {
	 if (field.isPrimaryKey() || "id".equalsIgnoreCase(field.getKeyName())) {
	 this.append(StringUtils.join(" nextval ('",
	 field.getSequenceName() == null ? this.getSequenceName() : field.getSequenceName(),
	 "'), "));
	 } else {
	 this.append(StringUtils.join("?", ", "));
	 }
	 }
	 sql.deleteCharAt(sql.length() - 2);
	 sql.append(")");
	 }
	 }
	
	 private void terminate() {
	 if (columns.isEmpty())
	 throw new RuntimeException("No columns informed!");
	 if (values.isEmpty())
	 throw new RuntimeException("No values informed!");
	
	 for (Object[] valueSet : values) {
	 if (valueSet.length != columns.size()) {
	 throw new RuntimeException("Value size different from column size!");
	 }
	 }
	
	 if (!terminated) {
	 this.appendLine(table).append(" ( ").append(StringUtils.join(columns, ", ")).appendLine(" )")
	 .append("VALUES ").append(StringUtils.join(getSqlValues(), ", "));
	 }
	 }
	
	 public Object[] getColumns() {
	 Object[] result = new Object[values.size()];
	 for (int i = 0; i < result.length; i++) {
	 result[i] = values.get(i);
	 }
	 return columns.toArray();
	 }
	
	 public Object[] getValues() {
	 List<Object> v = Lists.newArrayList();
	 for (Object[] v2 : values) {
	 for (Object value : v2) {
	 if (isPrepareStatement && value != null && value.toString().startsWith("nextval")) {
	 continue;
	 } else if (value != null && value instanceof Date) {
	 Date date = (Date) value;
	 v.add(new Timestamp(date.getTime()));
	 } else {
	 v.add(value);
	 }
	 }
	 }
	 return v.toArray();
	 }
	
	 private String[] getSqlValues() {
	 String[] result = new String[values.size()];
	 for (int i = 0; i < result.length; i++) {
	 Object[] objs = values.get(i);
	 result[i] = toValue(objs);
	 }
	 return result;
	 }
	
	 private String toValue(Object[] objs) {
	 String[] result = new String[objs.length];
	
	 for (int i = 0; i < result.length; i++) {
	 if (objs[i] instanceof String) {
	 if (objs[i].toString().startsWith("nextval")) {
	 result[i] = objs[i].toString();
	 } else {
	 result[i] = StringUtils.join("'", objs[i].toString(), "'");
	 }
	 } else if (objs[i] instanceof Date) {
	 Date date = (Date) objs[i];
	 result[i] = "'" + new Timestamp(date.getTime()) + "'";
	 } else {
	 result[i] = objs[i] == null ? "null" : objs[i].toString();
	 }
	 }
	 return "(" + StringUtils.join(result, ", ") + ")";
	 }
	
	 public List<Long> save(Connection connection) throws SQLException {
	 List<Long> result = ConnectionUtils.getRunner().insert(connection, this.toString(), rsh, this.getValues());
	 debug(result, null);
	 return result;
	 }
	
	 public <T> List<T> save(Connection connection, ColumnListHandler<T> columnlisthandler) throws SQLException {
	 List<T> result = ConnectionUtils.getRunner().insert(connection, this.toString(), columnlisthandler,
	 this.getValues());
	 debug(result, null);
	 return result;
	 }
	
	 public List<Long> insertBatch(Connection connection, String sql, Object[][] batchParams) throws SQLException {
	 List<Long> result = ConnectionUtils.getRunner().insertBatch(connection, sql, rsh, batchParams);
	 debug(result, sql);
	 return result;
	 }
	
	 public List<Long> save(Connection connection, String sql) throws SQLException {
	 List<Long> result = ConnectionUtils.getRunner().insert(connection, sql, rsh, this.getValues());
	 debug(result, sql);
	 return result;
	 }
	
	 public List<Long> save(Connection connection, String sql, Object[] values) throws SQLException {
	 List<Long> result = ConnectionUtils.getRunner().insert(connection, sql, rsh, values);
	 debug(result, sql);
	 return result;
	 }
	
	 public void setDubeg(boolean isDubeg) {
	 this.isDubeg = isDubeg;
	 }
	
	 private void debug(Object result, String sql) {
	 if (!isDubeg) {
	 return;
	 }
	 if (isEmpty(sql)) {
	 System.out.println("SQL=" + this.toString());
	 } else {
	 System.out.println("SQL=" + sql);
	 }
	 System.out.println("SQL Parameters=" + this.getValues());
	 System.out.println("SQL Value=" + result);
	 }
	}
	4. [文件] Update.java ~ 9KB 下载(2)
	?
	1
	2
	3
	4
	5
	6
	7
	8
	9
	10
	11
	12
	13
	14
	15
	16
	17
	18
	19
	20
	21
	22
	23
	24
	25
	26
	27
	28
	29
	30
	31
	32
	33
	34
	35
	36
	37
	38
	39
	40
	41
	42
	43
	44
	45
	46
	47
	48
	49
	50
	51
	52
	53
	54
	55
	56
	57
	58
	59
	60
	61
	62
	63
	64
	65
	66
	67
	68
	69
	70
	71
	72
	73
	74
	75
	76
	77
	78
	79
	80
	81
	82
	83
	84
	85
	86
	87
	88
	89
	90
	91
	92
	93
	94
	95
	96
	97
	98
	99
	100
	101
	102
	103
	104
	105
	106
	107
	108
	109
	110
	111
	112
	113
	114
	115
	116
	117
	118
	119
	120
	121
	122
	123
	124
	125
	126
	127
	128
	129
	130
	131
	132
	133
	134
	135
	136
	137
	138
	139
	140
	141
	142
	143
	144
	145
	146
	147
	148
	149
	150
	151
	152
	153
	154
	155
	156
	157
	158
	159
	160
	161
	162
	163
	164
	165
	166
	167
	168
	169
	170
	171
	172
	173
	174
	175
	176
	177
	178
	179
	180
	181
	182
	183
	184
	185
	186
	187
	188
	189
	190
	191
	192
	193
	194
	195
	196
	197
	198
	199
	200
	201
	202
	203
	204
	205
	206
	207
	208
	209
	210
	211
	212
	213
	214
	215
	216
	217
	218
	219
	220
	221
	222
	223
	224
	225
	226
	227
	228
	229
	230
	231
	232
	233
	234
	235
	236
	237
	238
	239
	240
	241
	242
	243
	244
	245
	246
	247
	248
	249
	250
	251
	252
	253
	254
	255
	256
	257
	258
	259
	260
	261
	262
	263
	264
	265
	266
	267
	268
	269
	270
	271
	272
	273
	274
	275
	276
	277
	278
	279
	280
	281
	282
	283
	284
	285
	286
	287
	288
	289
	290
	291
	292
	293
	294
	295
	296
	297
	298
	299
	300
	301
	302
	303
	304
	305
	306
	307
	308
	309
	310
	311
	312
	313
	314
	315
	316
	317
	318
	319
	320
	321
	322
	323
	324
	325
	326
	327
	328
	329
	330
	331
	332
	333
	334
	335
	336
	337
	338
	339
	340
	341
	342
	343
	344
	345
	346
	347
	348
	349
	350
	351
	352
	353
	354
	355
	356
	357
	358
	359
	360
	361
	362
	363
	364
	365
	366
	367
	368
	369
	370
	371
	372
	373
	374
	375
	376
	package com.github.sqlcteator;
	
	import static com.github.sqlcteator.util.StrUtil.isEmpty;
	import static com.github.sqlcteator.util.StrUtil.isNotEmpty;
	
	import java.sql.Connection;
	import java.sql.SQLException;
	import java.sql.Timestamp;
	import java.util.Collection;
	import java.util.Date;
	import java.util.Iterator;
	import java.util.LinkedList;
	import java.util.List;
	
	import org.apache.commons.lang3.StringUtils;
	
	import com.github.sqlcteator.util.StrUtil;
	import com.google.common.base.Joiner;
	
	/**
	* SQL UPDATE 更新
	*
	* @Author 杨健/YangJian
	* @Date 2015年7月21日 上午10:15:32
	*/
	public class Update {
	
	 private boolean terminated = false;
	
	 private String table;
	
	 private Collection<String> conditions;
	
	 private final List<String> columns;
	
	 private final List<Object> parameters;
	
	 private final StringBuilder sql;
	
	 public Update() {
	 this.sql = new StringBuilder(" update ");
	 this.columns = new LinkedList<>();
	 this.parameters = new LinkedList<>();
	 this.conditions = new LinkedList<>();
	 }
	
	 public Update(String table) {
	 this();
	 this.table = table;
	 }
	
	 public Update table(String table) {
	 this.table = table;
	 return this;
	 }
	
	 public Update set(String column, Object value) {
	 columns.add(column);
	 if (value != null && value instanceof Date) {
	 Date date = (Date) value;
	 value = new Timestamp(date.getTime());
	 }
	 parameters.add(value);
	 return this;
	 }
	
	 /**
	 * 拼接SQL查询条件（and）
	 *
	 * @param sql
	 * @param params
	 * 保存参数值
	 * @param columnName
	 * 字段
	 * @param value
	 * 参数值
	 * @return Update
	 * @Author 杨健/YangJian
	 * @Date 2015年6月30日 上午11:46:48
	 * @Version 1.0.0
	 */
	 public Update eq(String columnName, Object value) {
	
	 if (isEmpty(value))
	 return this;
	
	 conditions.add(new StringBuilder().append(" and ").append(columnName).append(" = ? ").toString());
	 setParameters(value);
	 return this;
	 }
	
	 private Update setParameters(Object value) {
	 parameters.add(value);
	 return this;
	 }
	
	 /** 不相等 */
	 public Update notEq(String columnName, Object value) {
	 if (isEmpty(value))
	 return this;
	
	 conditions.add(new StringBuilder().append(" and ").append(columnName).append(" <> ? ").toString());
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * in
	 *
	 * @param sql
	 * @param columnName
	 * @param value
	 * "WallLatticeStatus_2", "WallLatticeStatus_1" 或 Lists.newArrayList("WallLatticeStatus_2",
	 * "WallLatticeStatus_1");
	 * @param isQuotationMark
	 * 是否需要引号
	 * @return Update
	 * @Author 杨健/YangJian
	 * @Date 2015年7月15日 下午5:22:49
	 * @Version 1.0.0
	 */
	 public Update in(String columnName, Object value, boolean isQuotationMark) {
	 if (isEmpty(value))
	 return this;
	
	 String quotationMark = isQuotationMark == true ? "'" : "";
	 if (value instanceof Collection<?>) {
	 value = StrUtil.strAppend(Joiner.on(",").skipNulls().join((Collection<?>) value), ",", quotationMark);
	 } else {
	 value = StringUtils.join(quotationMark, value, quotationMark);
	 }
	 conditions.add(new StringBuilder().append(" and ").append(columnName).append(" in ( ").append(value)
	 .append(" )").toString());
	 return this;
	 }
	
	 /**
	 * 不加单引号
	 *
	 * @param columnName
	 * @param value
	 * @return
	 * @return Update
	 * @Author 杨健/YangJian
	 * @Date 2015年7月19日 下午2:52:52
	 * @Version 1.0.0
	 */
	 public Update in(String columnName, Object value) {
	 return in(columnName, value, false);
	 }
	
	 /**
	 * not in
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 值集合
	 * @param isQuotationMark
	 * 是否需要引号
	 * @return
	 */
	 public Update notIn(String columnName, Object value, boolean isQuotationMark) {
	 if (isEmpty(value))
	 return this;
	
	 String quotationMark = isQuotationMark == true ? "'" : "";
	 if (value instanceof Collection<?>) {
	 value = StrUtil.strAppend(Joiner.on(",").skipNulls().join((Collection<?>) value), ",", quotationMark);
	 } else {
	 value = StringUtils.join(quotationMark, value, quotationMark);
	 }
	 conditions.add(new StringBuilder().append(" and ").append(columnName).append(" not in ( ").append(value)
	 .append(" )").toString());
	 return this;
	 }
	
	 /** 空 */
	 public Update isNull(String columnName) {
	 if (isEmpty(columnName))
	 return this;
	
	 conditions.add(new StringBuilder().append(" and ").append(columnName).append(" is null ").toString());
	 return this;
	 }
	
	 /** 非空 */
	 public Update isNotNull(String columnName) {
	 if (isEmpty(columnName))
	 return this;
	
	 conditions.add(new StringBuilder().append(" and ").append(columnName).append(" is not null ").toString());
	 return this;
	 }
	
	 public Update or(String columnName, Object value) {
	 if (isEmpty(columnName))
	 return this;
	 if (isEmpty(value))
	 return this;
	 conditions.add(new StringBuilder().append(" or ").append(columnName).append(" = ? ").toString());
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 模糊匹配
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Update like(String columnName, String value) {
	 if (isEmpty(value))
	 return this;
	 if (value.indexOf("%") < 0)
	 value = StringUtils.join("'%", value, "%'");
	
	 conditions
	 .add(new StringBuilder().append(" and ").append(columnName).append(" like ").append(value).toString());
	 return this;
	 }
	
	 /**
	 * 时间区间查询
	 *
	 * @param columnName
	 * 属性名称
	 * @param lo
	 * 日期属性起始值
	 * @param go
	 * 日期属性结束值
	 * @return
	 */
	 public Update between(String columnName, String lo, String go) {
	 if (isNotEmpty(lo) && isNotEmpty(go)) {
	 return this;
	 }
	
	 if (isNotEmpty(lo) && isEmpty(go)) {
	 conditions.add(new StringBuilder().append(" and ").append(columnName).append(" >= ? ").toString());
	 setParameters(lo);
	 return this;
	 }
	
	 if (isEmpty(lo) && isNotEmpty(go)) {
	 conditions.add(new StringBuilder().append(" and ").append(columnName).append(" <= ? ").toString());
	 setParameters(go);
	 return this;
	 }
	
	 conditions.add(new StringBuilder().append(" between ? and ? ").toString());
	 setParameters(lo);
	 setParameters(go);
	 return this;
	 }
	
	 public Update between(String columnName, Number lo, Number go) {
	 if (isNotEmpty(lo))
	 ge(columnName, lo);
	
	 if (isNotEmpty(go))
	 le(columnName, go);
	
	 return this;
	 }
	
	 /**
	 * 小于等于
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Update le(String columnName, Number value) {
	 if (isEmpty(value)) {
	 return this;
	 }
	 conditions.add(new StringBuilder().append(" and ").append(columnName).append(" <= ? ").toString());
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 小于
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Update lt(String columnName, Number value) {
	 if (isEmpty(value)) {
	 return this;
	 }
	 conditions.add(new StringBuilder().append(" and ").append(columnName).append(" < ? ").toString());
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 大于等于
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Update ge(String columnName, Number value) {
	 if (isEmpty(value)) {
	 return this;
	 }
	 conditions.add(new StringBuilder().append(" and ").append(columnName).append(" >= ? ").toString());
	 setParameters(value);
	 return this;
	 }
	
	 /**
	 * 大于
	 *
	 * @param columnName
	 * 属性名称
	 * @param value
	 * 属性值
	 */
	 public Update gt(String columnName, Number value) {
	 if (isEmpty(value)) {
	 return this;
	 }
	 conditions.add(new StringBuilder().append(" and ").append(columnName).append(" > ? ").toString());
	 setParameters(value);
	 return this;
	 }
	
	 private void terminate() {
	 if (columns.isEmpty() || parameters.isEmpty())
	 throw new IllegalArgumentException("Not contains SET statements!");
	
	 if (!terminated) {
	 sql.append(table).append(" set ");
	 int size = columns.size();
	 for (int i = 0; i < size; i++) {
	 sql.append(columns.get(i)).append(" = ? ");
	 if (i < size - 1) {
	 sql.append(",");
	 }
	 }
	 if (!conditions.isEmpty()) {
	 sql.append(" where 1=1 ");
	 Iterator<String> conditionIter = conditions.iterator();
	 while (conditionIter.hasNext()) {
	 String condition = conditionIter.next();
	 sql.append(condition);
	 }
	 }
	
	 terminated = true;
	 }
	 }
	
	 @Override
	 public String toString() {
	 terminate();
	 return sql.toString();
	 }
	
	 public int doUpdate(Connection connection) throws SQLException {
	 System.out.println("SQL=" + this.toString());
	 int update = ConnectionUtils.getRunner().update(connection, this.toString(), parameters.toArray());
	 System.out.println("SQL Value=" + update);
	 return update;
	 }
	
	}
试试其它关键字
　拼接工具　
同语言下
可能有用的
金马超贡献的其它代码(3)