读取配置文件类
package com.eshore.ismp.contract.sql;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class SQLPropertyConfigurer {
private Logger logger = LoggerFactory.getLogger(SQLPropertyConfigurer.class);
private static final Properties properties =new Properties();
private String path;
private SQLPropertyConfigurer(String path) {
this.path = path;
load();
}
/**
*
* //DESC 获取sql语句
* @time: 2016年6月16日 下午12:12:48
* @throws
*/
private void load() {
if (null != properties) {
InputStream in = null;
try {
/* 检测是否需要从classpath下进行sql配置文件的读取 */
if (path.indexOf("classpath:") != -1) {
/* 从classpath下获取sql配置文件 */
in = this.getClass().getResourceAsStream("/" + path.split("classpath:")[1]);
}
if (null == in) {
/* 从文件路径获取sql配置文件 */
in = new FileInputStream(path);
properties.load(in);
} else {
properties.load(in);
}
logger.info("load sql file success");
} catch (FileNotFoundException e) {
logger.error("sqlfile is not found:",e);
} catch (IOException e) {
logger.error("read sqlfile error:",e);
} finally {
if (null != in) {
try {
in.close();
} catch (IOException e) {
logger.error("read sqlfile error:",e);
}
}
}
}
}
/**
*
* //DESC (这里用一句话描述这个方法的作用)
* @time: 2016年6月6日 上午10:25:55
* @param key
* @param routeKey
* @return
* @throws
*/
public static String getSql(String key) {
String sql = null;
if (null != properties) {
sql = properties.getProperty(key);
}
return sql;
}
}
spring配置文件
<bean id="SQLPropertyConfigurer" class="com.eshore.ismp.contract.sql.SQLPropertyConfigurer">
<constructor-arg name="path" value="classpath:sql.properties" />
</bean>
数据库代码配置文件
getId=CALL getId(?,?,?)
insertContract=insert into T_PRODUCT_CONTRACT (id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
insertContractHisByServNbr=insert into T_PRODUCT_CONTRACT_HIS (id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
queryContractByServNbr=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where serv_nbr=? and city_id=?
queryNonormalByServNbr=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where serv_nbr=? and city_id=? and state_id IN (0,1,2,11,12,14)
queryContractByServNbrAndServNbrParent=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where serv_nbr=? and serv_nbr_parent=? and city_id=?
queryContractByServNbrAndProductSpecId=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where serv_nbr\=? and product_spec_id\=? and city_id\=?
queryContractByBnetId=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where bnet_id\=? and city_id\=?
queryContractByBnetIdAndProductSpecId=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where bnet_id\=? and product_spec_id\=? and city_id\=?
queryContractByServNbrParent=select id, bnet_id,product_spec_id,state_id,offering_id,accept_number,offering_spec_id,serv_nbr_parent,serv_nbr,acc_nbr,node_id,sys_id,city_id,create_time,modify_time from T_PRODUCT_CONTRACT where serv_nbr_parent\=? and city_id\=?
updateContractStatusByServNbr=update T_PRODUCT_CONTRACT set state_id=? where serv_nbr=? and city_id=?
updateContractStatusByServNbrAndProductSpecId=update T_PRODUCT_CONTRACT set state_id=? where serv_nbr=? and product_spec_id=? and city_id=?
updateAccNbr=update T_PRODUCT_CONTRACT set acc_nbr=? where serv_nbr=? and city_id=?
updateContractByServNbr=update T_PRODUCT_CONTRACT set
java代码
@Override
public List<Contract> queryByServNbrAndServNbrParent(String servNbr,
String servNbrParent, int cityId) {
RowMapper<Contract> rowMapper = new ContractRowMapper();
return jdbcTemplate.query(SQLPropertyConfigurer.getSql("queryContractByServNbrAndServNbrParent"), new Object[]{servNbr,servNbrParent,cityId}, new int[]{java.sql.Types.VARCHAR,java.sql.Types.VARCHAR,java.sql.Types.INTEGER}, rowMapper );
}
@Override
public List<Contract> queryUserOrderData(String bnetId, int cityId) {
RowMapper<Contract> rowMapper = new ContractRowMapper();
return jdbcTemplate.query(SQLPropertyConfigurer.getSql("queryContractInfoBybnetIdAndCityId"), new Object[]{bnetId,cityId}, new int[]{java.sql.Types.VARCHAR,java.sql.Types.INTEGER}, rowMapper );
}