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

  

 

 

posted @   Lost blog  阅读(761)  评论(0编辑  收藏  举报




点击右上角即可分享
微信分享提示