spring jdbc分离数据库代码和java代码
读取配置文件类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | 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配置文件
1 2 3 | <bean id= "SQLPropertyConfigurer" class = "com.eshore.ismp.contract.sql.SQLPropertyConfigurer" > <constructor-arg name= "path" value= "classpath:sql.properties" /> </bean> |
数据库代码配置文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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代码
1 2 3 4 5 6 7 8 9 10 11 12 13 | @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 ); } |
作者: lost blog
出处: http://www.cnblogs.com/JAYIT/
关于作者:专注服务器端开发
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接 如有问题, 可邮件(sawyershaw@qq.com)咨询.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步