spring jdbc分離資料庫代碼和java代碼

標籤：

讀取設定檔類

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代碼

@Overridepublic 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 );}@Overridepublic 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 );}

　　

　

　

spring jdbc分離資料庫代碼和java代碼