让JAVA的JDBC支持命名参数的SQL语句
让JAVA的JDBC支持命名参数的SQL语句JAVA 的JDBC连接数据库时,传递参数的方式是通过索引位置实现(根据SQL中?号出现的顺序,例如SELECT * FROM table WHERE name =? OR title=?);这让使用SQL语句变得比较麻烦也不符合使用习惯。为此专门创建了一个类NSQL用于支持命名方式给SQL语句传递参数(例如SELECT * FROM table WHERE name =?name OR title=?title)。其基本原理是,以?号为参数标识,后跟参数名称,在编写SQL语句时候采用命名参数方式,然后由NSQL类将其分析后生成JDBC可用的基于?号顺序的SQL语句,同时记录参数顺序。这样既可实现命名参数的SQL语句。乐淘棋牌http:/www.letaoqpyx.com代码如下所示:?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132/* * 2017年2月16日 */package com.kiy.service.data; import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Map;import java.util.concurrent.ConcurrentHashMap; /* * SQL语句抽象,提供基于命名参数的SQL语句功能 * * author Simon(ZhangXi) * */public final class NSQL / 静态集合缓存使用过的NSQL private static Map<string, nsql=""> caches = new ConcurrentHashMap<string, nsql="">(); private String sql_naming; private String sql_execute; private String names; private NSQL() / 用户不能实例化对象 / 通过get方法获取可用实例 public boolean hasName() return names.length > 0; public void setParameter(PreparedStatement ps, String name, Object value) throws SQLException for (int a = 0, b = names.length - 1; a <= b; a+, b-) if (namesa.equals(name) ps.setObject(a + 1, value); if (a != b && namesb.equals(name) ps.setObject(b + 1, value); public void setParameters(PreparedStatement ps, Map<string, object=""> values) throws SQLException for (int index = 0; index < names.length; index+) ps.setObject(index + 1, values.get(namesindex); /* * 获取用于数据库执行的SQL语句 * * return */ public String getSql() return sql_execute; /* * 获取用户定义的命名SQL语句 * * return */ public String getNamingSql() return sql_naming; /* * 获取对象实例,此方法将缓存分析过的SQL语句以提高性能 * * param sql * return */ public static NSQL get(String sql) NSQL nsql = caches.get(sql); if (nsql = null) nsql = NSQL.parse(sql); caches.put(sql, nsql); return nsql; /* * 分析命名SQL语句获取抽象NSQl实例;java(JDBC)提供SQL语句命名参数而是通过?标识参数位置,638棋牌游戏http:/www.rodlg.com * 通过此对象可以命名参数方式使用SQL语句,命名参数以?开始后跟名称?name。 * 例如:SELECT * FROM table WHERE name = ?key AND email = ?key; * * param sql * return */ public static NSQL parse(String sql) / SELECT * FROM table WHERE name = ?key AND email = ?key; / AZ az 019 _ if (sql = null) throw new NullPointerException("SQL String is null"); char c; List<string> names = new ArrayList<string>(); StringBuilder sql_builder = new StringBuilder(); StringBuilder name_builder = new StringBuilder(); for (int index = 0; index < sql.length(); index+) c = sql.charAt(index); sql_builder.append(c); if ('?' = c) while (+index < sql.length() c = sql.charAt(index); if (c >= 'a' && c <= 'z') | (c >= 'A' && c <= 'Z') | c = '_' | (c >= '0' && c <= '9') name_builder.append(c); else sql_builder.append(c); break; names.add(name_builder.toString(); name_builder.setLength(0); NSQL dbsql = new NSQL(); dbsql.sql_naming = sql; dbsql.sql_execute = sql_builder.toString(); dbsql.names = names.toArray(dbsql.names = new Stringnames.size(); return dbsql; public String toString() return "NAMING: " + sql_naming + "nEXECUTE: " + sql_execute; </string></string></string,></string,></string,>NSQL类的使用如下所示: ?12345678910111213141516171819202122public boolean CreateUser(User u) NSQL sql1 = NSQL.get("INSERT INTO users (id,name,password,enable,realname,mobile,phone,email,remark,created,u