记一次增加SqlServer数据源使用JDBCTemplate操作

背景

已有项目SSM+MySQL常见的web应用，中途需要有一个需求，查询客户的数据库（sqlserver），只是一个简单查询操作，并不重度依赖该数据源，没必要再在Mybatis配置多个数据源操作，增加数据源路由选择的复杂度，在不增加其他三方库的情况下，只能选择jdbc实现，try catch finally 三连？打扰了。。用了JDBCTemplate，但是因为很少用，记一下

进入主题

1. 驱动包： sqljdbc4.jar 微软sqlserver官方下载的
2. SQLServerBaseDao

public class SqlServerBaseDao {

    protected static JdbcTemplate jdbcTemplate;
    protected static String dbName = "";

    static {
        String addrAndPort = Global.getConfig("dashi.DBAddrAndPort");
        String userAndPass = Global.getConfig("dashi.DBUserAndPass");
        dbName = Global.getConfig("dashi.DBName");
        
        String[] addr = addrAndPort.split(":");
        String[] user = userAndPass.split(":");

        SQLServerDataSource dataSource = new SQLServerDataSource();
        dataSource.setDatabaseName(dbName);
        dataSource.setIntegratedSecurity(false);
        dataSource.setServerName(addr[0]);
        dataSource.setPortNumber(Integer.parseInt(addr[1]));
        dataSource.setUser(user[0]);
        dataSource.setPassword(user[1]);
        jdbcTemplate = new JdbcTemplate(dataSource);
    }


    protected static final String countSql = "select count(1) from ( %s ) tmp;";

    protected static final String pageSql = "select * from (" +
            "select ROW_NUMBER() OVER (ORDER BY AutoID ASC) AS 'RowNumber', * from ( %s ) a" +
            ") AS b " +
            "where RowNumber BETWEEN  %d and %d";

    protected String buildSelectCountSql(String sql) {
        if (countSql != null && countSql.length() > 0) {
            return String.format(countSql, sql);
        }
        return sql;
    }

    protected String buildPageSql(String sql, Page page) {
        int pageSize = page.getPageSize();
        int pageIndex = page.getPageNo();

        int fIndex = ((pageIndex - 1) * pageSize) + 1;
        int lIndex = (pageIndex * pageSize);
        return String.format(pageSql, sql, fIndex, lIndex);
    }
}

说明：

1. jdbcTemplate通过spring配置也可以，这里是因为客户有可能改动账号密码，不必去修改配置文件。系统有系统配置管理。
2. Page对象是现有系统的Page对象，与现有界面的表格分页控件相耦合，此处作为一个转换。

---

3. AccessRecordDSDao 业务Dao

public class AccessRecordDSDao extends SqlServerBaseDao {

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }


    private RowMapper<AccessRecordDS> rowMapper = new RowMapper<AccessRecordDS>() {
        @Override
        public AccessRecordDS mapRow(ResultSet resultSet, int i) throws SQLException {
            AccessRecordDS accessRecordDS = new AccessRecordDS();
            accessRecordDS.setAutoID(resultSet.getString("AutoID"));
            accessRecordDS.setEmpCardID(resultSet.getString("EmpCardID"));
            accessRecordDS.setCardDay(resultSet.getString("CardDay"));
            accessRecordDS.setEmpno(resultSet.getString("Empno"));
            accessRecordDS.setEmpName(resultSet.getString("EmpName"));
            accessRecordDS.setDptName(resultSet.getString("DptName"));
            accessRecordDS.setDoorName(resultSet.getString("DoorName"));
            accessRecordDS.setEventDemo(resultSet.getString("EventDemo"));
            return accessRecordDS;
        }
    };

    //----------------Dao方法
    public List<AccessRecordDS> getNormalskEventList(Page page) {
        String countSql = buildSelectCountSql(selectSomethSql);
        String pageSql = buildPageSql(selectSomethSql, page);
        logger.debug(String.format("countSql=[%s],\n pageSql=[%s]", countSql, pageSql));
        Integer count = jdbcTemplate.queryForObject(countSql, Integer.class);
        page.setCount(count);
        // **注意**， 一开始的写法：jdbcTemplate.queryForList(pageSql, AccessRecordDS.class, rowMapper);
        List<AccessRecordDS> accessRecordDS = jdbcTemplate.query(pageSql, new Object[]{}, new BeanPropertyRowMapper<AccessRecordDS>(AccessRecordDS.class));
        logger.debug(String.format("count=[%d], accessRecordDS.size=[%d]", count, accessRecordDS == null ? 0 : accessRecordDS.size()));
        page.setList(accessRecordDS);
        return accessRecordDS;
    }
    /**
     * 随便搞点啥的sql
     */
    private static final String selectSomethSql = "SELECT * from xxxx where 1=1";
           
}

说明：具体的业务Dao类，只要继承SqlServerBaseDao即可

1. 其中使用jdbcTemplate.queryForList(pageSql, AccessRecordDS.class, rowMapper);用法有误。。。。。

---

问题分析

一. 为什么jdbcTemplate.queryForList(pageSql, AccessRecordDS.class, rowMapper);总报异常？

Integer count = jdbcTemplate.queryForObject(countSql, Integer.class);
一开始先入为主，类似上面操作，实际上有queryForInt操作的，后来被deprecated，实际就是因为开放出queryForObject，所以显得多余，然后queryForList方法，类比使用，所以报了第一个异常，Incorrect column count : expeccted 1, actual 8，期望是1，实际上有8列，就是8个字段，后来上网查了才知道queryForList只能返回像Integer、String类的单列数据集合List，真正用法应该像我上面写法。。。

1. Incorrect column count : expeccted 1, actual 8
2. com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.

实际在有几个异常的，由于在客户现场开发（内网），没时间总结，大概都是因为SqlServer不熟悉并且和MySQL有差异的原因导致，比如说分页之类的。。

总结

实际上也没有也没有什么有内涵的东西，几分钟解决的问题，写这篇文章就是想记录下，这次一个人出差在现场遇到杂七杂八的问题，明白有些东西要善于归纳总结，才能成为自己的经验，能回顾自己处理问题所缺少的东西，下次才能更高效、心态更稳的、眼光更准确地有目的解决问题。同时遇到大量问题的情况下，循环渐进有条不紊地按照自己节奏走。从现在起，打算多写写文章，多总结总结问题。