由于系统当中使用jdbc方式访问数据库,代码不易维护。所以写一个通用的sql资源文件,
便于日后维护。有不足之处,希望大家继续完善。
1 配置sqlresource.xml 文件,文件路径shane/WebRoot/WEB-INF/classes下
1.1 增加sql语句
<sqlresource>
<!-- 此段为注释,company是自定义sql的名称,在下面的类中调用时会使用-->
<company>
select * from c_his_family_member t
</company>
<!-- 此段为注释,companyperson是自定义sql的名称,在下面的类中调用时会使用,{1}为要传入参数的位置,
会与类中调用方法对应-->
<companyperson>
select * from c_his_family_member t where t.name like '%{1}%'
</companyperson>
</sqlresource>
2 使用已经定义完成的SQLService类
2.1 引入类
import com.eranet.service.SQLService;
2.2 使用已经定义的方法
// 使用SQLService无参数的构造方法,就默认的sql语句资源文件sqlresource.xml
// 如果使用SQLService(paramter)的带有一个参数的构造方法,就可以自定义sql资源文件,
// 并在shane/WebRoot/WEB-INF/classes,自行建立自定义名的xml文件。
SQLService returnsqls = new SQLService();
log.debug(returnsqls.get("company")); // company 对应xml文件的名称
log.debug(returnsqls.get("companyperson","李")); // companyperson 对应xml文件的名称
2.3 上段程序得到的结果就是:
select * from c_his_family_member t
select * from c_his_family_member t where t.name like '%李%'
类文件:SQLService.java
import java.net.URL;
import java.io.IOException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.sql.SQLException;
import javax.servlet.ServletException;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.input.SAXBuilder;
public class SQLService
{
private static Log log = LogFactory.getLog( SQLService.class );
// sql资源文件
private String fileName = null;
/**
* 默认的sql语句资源文件
*
*/
public SQLService() {
fileName = "sqlresource.xml";
}
/**
* 设置自定义的sql资源文件
* @param fileNameValue
*/
public SQLService(String fileNameValue)
{
fileName = fileNameValue.trim()+".xml";
}
/**
* 资源文件初始化
* @throws IOException
* @throws ServletException
* @throws SQLException
*/
public String sqlInit(String sqlname) throws IOException, ServletException,SQLException
{
SAXBuilder sb=new SAXBuilder();
Document doc=new Document();
try{
// 读取sql资源文件
URL url = getClass().getClassLoader().getResource( fileName );
doc = sb.build( url );
}catch(Exception e){
e.printStackTrace();
}
Element root=doc.getRootElement(); //获取根元素
String sqlvalue = root.getChild(sqlname).getText();
return sqlvalue;
}
/**
* 自动根据参数配置出相对应的SQL语句
* @param key
* @param args
* @return
* @throws IOException
* @throws ServletException
* @throws SQLException
*/
public String getAllSQL(String key, Object[] args) throws IOException, ServletException, SQLException
{
String value = sqlInit( key );
if( null==value )
{
log.error( "-ERR: invalid SQL key: " + key );
return null;
}
for (int i=1; i<=args.length; i++)
{
value = value.replaceAll( "//{"+i+"//}", String.valueOf(args[i-1]) );
}
return value;
}
/**
* 通过名字查询sql语句
*/
public String get(String key) throws IOException, ServletException, SQLException
{
String sqls = sqlInit(key);
return sqls;
}
/**
* 传入一个参数,得到sql
* @param key
* @param arg
* @return
* @throws IOException
* @throws ServletException
* @throws SQLException
*/
public String get(String key, Object arg) throws IOException, ServletException, SQLException
{
return getAllSQL(key, new Object[] {arg} );
}
/**
* 传入2个参数,得到sql
* @param key
* @param arg1
* @param arg2
* @return
* @throws IOException
* @throws ServletException
* @throws SQLException
*/
public String get(String key, Object arg1, Object arg2) throws IOException, ServletException, SQLException
{
return getAllSQL(key, new Object[] { arg1, arg2 } );
}
/**
* 传入3个参数,得到sql
* @param key
* @param arg1
* @param arg2
* @param arg3
* @return
* @throws IOException
* @throws ServletException
* @throws SQLException
*/
public String get(String key, Object arg1, Object arg2,
Object arg3) throws IOException, ServletException, SQLException
{
return getAllSQL(key, new Object[] { arg1, arg2, arg3 } );
}
/**
* 传入4个参数,得到sql
* @param key
* @param arg1
* @param arg2
* @param arg3
* @param arg4
* @return
* @throws IOException
* @throws ServletException
* @throws SQLException
*/
public String get(String key, Object arg1, Object arg2,
Object arg3, Object arg4) throws IOException, ServletException, SQLException
{
return getAllSQL(key, new Object[] { arg1, arg2, arg3, arg4 } );
}
/**
* 传入5个参数,得到sql
* @param key
* @param arg1
* @param arg2
* @param arg3
* @param arg4
* @param arg5
* @return
* @throws IOException
* @throws ServletException
* @throws SQLException
*/
public String get(String key, Object arg1, Object arg2,
Object arg3, Object arg4, Object arg5) throws IOException, ServletException, SQLException
{
return getAllSQL(key, new Object[] { arg1, arg2, arg3, arg4, arg5 } );
}
/**
* 传入6个参数,得到sql
* @param key
* @param arg1
* @param arg2
* @param arg3
* @param arg4
* @param arg5
* @param arg6
* @return
* @throws IOException
* @throws ServletException
* @throws SQLException
*/
public String get(String key, Object arg1, Object arg2,
Object arg3, Object arg4, Object arg5, Object arg6) throws IOException, ServletException, SQLException
{
return getAllSQL(key, new Object[] { arg1, arg2, arg3, arg4, arg5, arg6 } );
}
}
xml文件sqlresource.xml
<?xml version="1.0" encoding="gb2312" ?>
<sqlresource>
<company>
select * from c_his_family_member t
</company>
<companyperson>
select * from c_his_family_member t where t.name like '%{1}%'
</companyperson>
</sqlresource>
/*******************************************************利用jdk1.5新特性改写*******************/
package com.shane.components.xmlservice;
import java.net.URL;
import java.io.IOException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.sql.SQLException;
import javax.servlet.ServletException;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.input.SAXBuilder;
/**
* 用于对指定的xml资源文件,通过指定名得到相应的文本值。
*
* @author shane
*
*/
public class XMLService {
private static Log log = LogFactory.getLog(XMLService.class);
// 资源文件名
private String fileName = null;
/**
* 默认的sql语句资源文件
*
*/
public XMLService() {
fileName = "/datasql/sqlresource.xml";
}
/**
* 设置自定义的sql资源文件
*
* @param fileNameValue
*/
public XMLService(String fileNameValue) {
fileName = "/datasql/" + fileNameValue.trim() + ".xml";
}
/**
* 资源文件初始化
*
* @throws IOException
* @throws ServletException
* @throws SQLException
*/
public String sqlInit(String sqlname) throws IOException, ServletException,
SQLException {
SAXBuilder sb = new SAXBuilder();
Document doc = new Document();
try {
// 读取资源文件
URL url = getClass().getClassLoader().getResource(fileName);
//URL url = new URL("file://D://eclipse_CH_3.2//work//wtdpf//web//WEB-INF//classes//datasql//impdate.xml");
System.out.print(url.toString());
doc = sb.build(url);
} catch (Exception e) {
e.printStackTrace();
}
Element root = doc.getRootElement(); // 获取根元素
String sqlvalue = null;
try {
sqlvalue = root.getChild(sqlname).getText();
} catch (Exception e) {
log.info("配置sql名称错误!");
e.printStackTrace();
}
return sqlvalue;
}
/**
* 自动根据参数配置出相对应的SQL语句
*
* @param key
* @param args
* @return
* @throws IOException
* @throws ServletException
* @throws SQLException
*/
public String getAllSQL(String key, Object[] args) throws IOException,
ServletException, SQLException {
String value = sqlInit(key);
if (null == value) {
log.error("-ERR: invalid SQL key: " + key);
return null;
}
for (int i = 1; i <= args.length; i++) {
value = value.replaceAll("//{" + i + "//}", String
.valueOf(args[i - 1]));
}
return value;
}
/**
* 通过名字查询返回语句
*
* @throws SQLException
* @throws ServletException
* @throws IOException
*/
public String get(String key) {
try {
String sqls = sqlInit(key);
return sqls;
} catch (IOException e) {
e.printStackTrace();
} catch (ServletException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return "";
}
/**
* 传入多个参数,得到资源文件对应的字符串
*
* @param key
* @param arg
* @return String
* @throws IOException
* @throws ServletException
* @throws SQLException
*/
public String get(String key, Object... arg) {
try {
int i = 0;
for(Object o1 : arg){
i++;
}
Object[] object = new Object[i];
int j = 0;
for(Object o : arg){
object[j++] = o;
}
return getAllSQL(key, object);
} catch (IOException e) {
e.printStackTrace();
} catch (ServletException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return "";
}
}