Spring JdbcTemplate

抽象类

public abstract class JdbcTemplate {

    //template method
    public final Object execute(String sql) throws SQLException {
        Connection con = HsqldbUtil.getConnection();
        Statement stmt = null;
        try{
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            Object result = doInStatement(rs);//abstract method
            return result;
        } catch (SQLException ex){
            ex.printStackTrace();
            throw ex;
        } finally {
            try {
                stmt.close();
            } catch (SQLException  e) {
                e.printStackTrace();
            }
            try {
                if(!con.isClosed()){
                    try {
                        con.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //implements in subclass
    protected abstract Object doInStatement(ResultSet rs);
}

子类

public class JdbcTemplateUserImpl extends JdbcTemplate {

    @Override
    protected Object doInStatement(ResultSet rs) {
        List<User> userList = new ArrayList<User>();
        try {
            User user = null;
            while (rs.next()){
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUserName(rs.getString("user_name"));
                user.setBirth(rs.getDate("birth"));
                user.setCreateDate(rs.getDate("create_date"));
                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();return null;

        }
    }

    public static void main(String[] args) {
        String sql = "select * from User";
        JdbcTemplate jt = new JdbcTemplateUserImpl();
        List<User> userList = (List<User>) jt.execute(sql);
    }
}

 

改进之后：

回调接口

public interface StatementCallback {
    Object doInStatement(Statement stmt) throws SQLException;
}

模板类

public class JdbcTemplate {

    //template method
    public final Object execute(StatementCallback action) throws SQLException {
        Connection con = HsqldbUtil.getConnection();
        Statement stmt = null;
        try{
            stmt = con.createStatement();
            Object result = action.doInStatement(stmt);//abstract method
            return result;
        } catch (SQLException ex){
            ex.printStackTrace();
            throw ex;
        } finally {
            try {
                stmt.close();
            } catch (SQLException  e) {
                e.printStackTrace();
            }
            try {
                if(!con.isClosed()){
                    try {
                        con.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public Object query(StatementCallback stmt) throws SQLException{
        return execute(stmt);
    }
    //匿名类方式，这里还可以传入一个接口类型的参数ResultSetExtractor用来处理结果集
    public Object query2(final String sql) throws Exception{
        JdbcTemplate jt = new JdbcTemplate();
        return jt.query(new StatementCallback() {
            @Override
            public Object doInStatement(Statement stmt) throws SQLException {
                ResultSet rs = stmt.executeQuery(sql);
                List<User> userList = new ArrayList<User>();
                User user = null;
                while (rs.next()){
                    user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUserName(rs.getString("user_name"));
                    user.setBirth(rs.getDate("birth"));
                    user.setCreateDate(rs.getDate("create_date"));
                    userList.add(user);
                }
                return userList;
            }
        });
    }

    public static void main(String[] args) throws Exception {
        String sql = "select * from User";
        JdbcTemplate jt = new JdbcTemplate();
        List<User> userList = (List<User>) jt.query2(sql);
    }
}

结束。