乱写的一个SQL框架
闲来没事，看了mybatis的实现形式，就心血来潮的自己弄了一个仿照mybatis的框架，性能肯定不好，而且有很多问题，但是是一次有益的尝试
1.基本配置文件
<!--加载数据源-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName"> <value>org.logicalcobwebs.proxool.ProxoolDriver</value> </property> <property name="url"> <value>proxool.inventory</value> </property> </bean> <bean id="InventoryTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource"> <ref local='dataSource' /> </property> </bean>
<!--定义数据库类型--> <bean id="multiDBSQLSupport" class="com.****.linkalarm.dao.db.MultiDBSQLSupport"> <property name="type" value="mysql"></property> </bean> <!--加载数据库操作--> <bean id="basicDBOper" class="com.****.linkalarm.dao.db.BasicDBOper"> <property name="multiDBSQLSupport" ref="multiDBSQLSupport"></property> <property name="jdbcTemplate" ref="InventoryTemplate"></property> </bean>
2.主要实现类
1).SQL语句支持类
/** * 多数据库支持类型*/ public class MultiDBSQLSupport { //数据库类型 private String type = "mysql"; private Properties localProperties; public String getBasicSQL(String key){ return getProperties(key,""); } /** * 获得插入SQL*/ public String getInsertSQL(String key,Map<String,String> valueMap){ if(valueMap==null){ return null; } String sql = getBasicSQL(key).toLowerCase(); if(!sql.equals("")){ String v = sql.trim().split("values")[1].replace("(", "").replace(")", "").replace(";", ""); String[] vs = v.split(","); for(String s:vs){ s = s.trim(); if(s.contains("#")){ String s1 = s.replaceAll("#", "").trim(); String valueString; if(valueMap.containsKey(s1)){ valueString = valueMap.get(s1); }else if(valueMap.containsKey(s1.toUpperCase())){ valueString = valueMap.get(s1.toUpperCase()); }else{ valueString = ""; } sql = sql.replace(s, valueString); } } } return sql; } /** * 获得查询SQL*/ public String getQuerySQL(String key,Map<String,String> valueMap){ String sql = getBasicSQL(key).toLowerCase().trim(); List<String> paramsList = new ArrayList<String>(); for(int i=0;i<sql.length();i++){ char ch = sql.charAt(i); if(ch=='#'){ String tempStr = ""; char c; while((c = sql.charAt(++i))!='#'){ tempStr = tempStr + sql.charAt(i); } paramsList.add("#"+tempStr+"#"); } } for(String parms:paramsList){ sql = sql .replace(parms, valueMap.get(parms.replaceAll("#", "").trim())); } return sql; } /** * 获得插入SQL*/ public String getQuerySQL(String key,String...values){ String sql = getBasicSQL(key).toLowerCase().trim(); List<String> paramsList = new ArrayList<String>(); for(int i=0;i<sql.length();i++){ char ch = sql.charAt(i); if(ch=='#'){ String tempStr = ""; char c; while((c = sql.charAt(++i))!='#'){ tempStr = tempStr + sql.charAt(i); } paramsList.add("#"+tempStr+"#"); } } for(int i=0;i<values.length;i++){ sql = sql.replace(paramsList.get(i), values[i]+""); } return sql; } /** * 获得批量插入SQL*/ public String[] getBatchInsertSQL(String key,List<Map<String,String>> valueMaps){ if(valueMaps==null){ return null; } int length = valueMaps.size(); String[] results = new String[length]; String sql = getBasicSQL(key).toLowerCase(); if(!sql.equals("")){ String v = sql.trim().split("values")[1].replace("(", "").replace(")", "").replace(";", ""); String[] vs = v.split(","); for(int i=0;i<length;i++){ String newsql = sql; Map<String,String> valueMap = valueMaps.get(i); for(String s:vs){ s = s.trim(); if(s.contains("#")){ String s1 = s.replaceAll("#", "").trim(); String valueString; if(valueMap.containsKey(s1)){ valueString = valueMap.get(s1); }else if(valueMap.containsKey(s1.toUpperCase())){ valueString = valueMap.get(s1.toUpperCase()); }else{ valueString = ""; } newsql = newsql.replace(s, valueString); } } results[i] = newsql; } } return results; } /** * 读取配置文件 * */ private String getProperties(String key,String defaultValue){ if(localProperties==null){ try{ String path = ProgramPathHelper.getProgramPath() +System.getProperty("file.separator")+"sql_"+type+".properties"; URL url = null; File file = new File(path); try { if (file.exists()) { url = file.toURI().toURL(); } else { path="sql_"+type+".properties"; url = new URL(path); } } catch (MalformedURLException e) { url = ConfigHelp.findAsResource(path); } if (null == url) { throw new FileNotFoundException(path); } InputStream in = new BufferedInputStream(new FileInputStream(new File(url.toURI()))); localProperties = new Properties(); localProperties.load(in); }catch(Exception ex){ Logger logger = Logger.getLogger(ConfigHelp.class); logger.warn("read configuration.properties while error", ex); } } try{ String result = localProperties.getProperty(key); if(result!=null && !"".equals(result)){ return result; } }catch(Exception ex){ Logger logger = Logger.getLogger(ConfigHelp.class); logger.warn("return configuration.properties value while error", ex); } return defaultValue; } public String getType() { return type; } public void setType(String type) { this.type = type; } }
2).数据操作接口
public interface IDataBaseOper<T> { /** * 查询*/ public List<Map<String,Object>> getQueryList(String sql); /** * 查询*/ public List<T> getQueryList(String sql,Class classs); /** * 插入*/ public int insertSQL(String sql); /** * 更新*/ public int updateSQL(String sql); /** * 获取sql语句*/ public String getSQL(String key,String...values); }
3).抽象数据操作
public abstract class ADataBaseOper<T> implements IDataBaseOper<T>{ protected JdbcTemplate jdbcTemplate; protected MultiDBSQLSupport multiDBSQLSupport; public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } public MultiDBSQLSupport getMultiDBSQLSupport() { return multiDBSQLSupport; } public void setMultiDBSQLSupport(MultiDBSQLSupport multiDBSQLSupport) { this.multiDBSQLSupport = multiDBSQLSupport; } }
4).基本数据操作类
/** * 基础数据库操作类*/ public class BasicDBOper<T> extends ADataBaseOper<T>{ private static final Logger logger = Logger.getLogger(BasicDBOper.class); public List<Map<String,Object>> getQueryList(String sql){ try { return jdbcTemplate.queryForList(sql); } catch (DataAccessException e) { try { System.out.println(jdbcTemplate.getDataSource().getConnection().getMetaData().getURL()); } catch (SQLException e1) { e1.printStackTrace(); } logger.info(e); } return null; } public int insertSQL(String sql){ try { jdbcTemplate.execute(sql); return 1; } catch (Exception e) { try { System.out.println(jdbcTemplate.getDataSource().getConnection().getMetaData().getURL()); } catch (SQLException e1) { e1.printStackTrace(); } logger.info(e); } return 0; } public int updateSQL(String sql){ return jdbcTemplate.update(sql); }
@Override public List<T> getQueryList(String sql,Class classs) { try { List<T> objects = null; List<Map<String,Object>> results = this.getQueryList(sql); if(results!=null&&results.size()>0){ objects = new ArrayList<T>(); for(int i=0;i<results.size();i++){ Map<String,Object> map = results.get(i); try { Object obj = classs.newInstance(); Field[] fs = classs.getDeclaredFields(); for(Field f:fs){ String name = f.getName(); if(map.containsKey(name)){ f.setAccessible(true); f.set(obj, map.get(name)+""); f.setAccessible(false); } } objects.add((T)obj); } catch (Exception e) { logger.info(e); continue; } } } return objects; } catch (Exception e) { try { System.out.println(jdbcTemplate.getDataSource().getConnection().getMetaData().getURL()); } catch (SQLException e1) { e1.printStackTrace(); } logger.info(e); } return null; } @Override public String getSQL(String key, String... values) { String sql = this.getMultiDBSQLSupport().getQuerySQL(key, values); //logger.info("GET SQL result is "+sql); return sql; } }
3.使用这个框架
1).定义SQL语句
sql_mysql.properties
jdbc.getLinkVPN=select v.name from link l,re_link_vpn r,vpn v where l.UUID ='#linkid#' and r.vpn_id = v.UUID and l.UUID = r.link_id
jdbc.analyse.getRule=select uuid,name,raction as action from patrolrule where atype = '2' and uuid = #ruleid#
2).使用类
public static List<Map<String,Object>> getLinkVPN(String linkid){ return dataBaseOper.getQueryList(dataBaseOper.getSQL("jdbc.getLinkVPN",linkid)); }
public static List<PatrolRule> getTaskRule(String ruleId){ return dataBaseOper.getQueryList(dataBaseOper.getSQL("jdbc.analyse.getRule", ruleId), PatrolRule.class); }
