spring jdbc分离数据库代码和java代码

深碍√TFBOYSˉ_ 2021-10-24 02:50 202阅读 0赞

读取配置文件类

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配置文件

数据库代码配置文件

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

转载于:https://www.cnblogs.com/JAYIT/p/5630928.html