乱写的一个SQL框架
2024-08-25 05:37:15
闲来没事，看了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 = '' 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);
}
最新文章
- <转>iOS性能优化：Instruments使用实战
- IOS第二天多线程-04简化单例模式
- new和delete malloc和free
- 【BZOJ】【4002】【JLOI2015】有意义的字符串
- 高薪诚聘.NET MVC开发工程师
- BZOJ 无数据题集合
- jQuery 遍历函数（八）
- hackerrank [Week of Code 33] Bonnie and Clyde
- 1.9 list 列表
- CSS3_过渡_2D 变换_瓶体旋转_动态时钟
- java-消息中间件-基于内存的mq
- Java的常用命令javac与java
- luogu2149 Elaxia的路线 (dijkstra+拓扑dp)
- JAVA代码中获取JVM信息
- 八数码问题 Eight Digital Problem
- linux令普通用户拥有root权限
- 临时表 on commit delete rows 与 on commit preserve rows 的区别
- 设计模式之单例模式（Singleton）（1）
- cmdb安装脚本
- Linux下编译安装MySQL5.6