jdbcsystemdatabase.java

这是linux下ssl vpn的实现程序

    public List getReplacementsForContent(String username, int replaceType, String mimeType, String site) throws Exception {
        String cacheKey = (username == null ? "" : username) + "_" + replaceType + "_" + (mimeType == null ? "" : mimeType) + "_"
                        + (site == null ? "" : site);
        synchronized (replacementsCache) {
            if (replacementsCache.contains(cacheKey)) {
                return (List) replacementsCache.retrieve(cacheKey);
            }
        }
        JDBCPreparedStatement ps = null;

        // global=SELECT * FROM replacements WHERE username=? AND mime_type=?
        // AND replace_type=? AND MATCHES(?,site_pattern=?) = ?
        // ORDER BY username,replace_type,mime_type,sequence ASC
        // user=SELECT * FROM replacements WHERE ( username=? OR username=? )
        // AND mime_type=? AND replace_type=? AND MATCHES(?,site_pattern=?) = ?
        // ORDER BY username,replace_type,mime_type,sequence ASC

        String sitePattern = site == null || site.equals("") ? ".*" : site;
        if (site == null || site.equals("")) {
            ps = db.getStatement("getReplacementsForContent.select.allSites");
            ps.setString(3, "");

        } else {
            ps = db.getStatement("getReplacementsForContent.select");
            ps.setString(3, sitePattern);
            ps.setString(4, sitePattern);
        }
        ps.setString(1, mimeType == null ? "" : mimeType);
        ps.setInt(2, replaceType);

        try {
            ResultSet rs = ps.executeQuery();
            CacheList v = new CacheList();
            try {
                while (rs.next()) {
                    v.add(new DefaultReplacement(rs.getString("mime_type"), replaceType, rs.getInt("sequence"), rs
                                    .getString("site_pattern"), rs.getString("match_pattern"), rs.getString("replace_pattern")));
                }
                replacementsCache.store(cacheKey, v, new Long(Long.MAX_VALUE), null);
                return v;
            } finally {
                rs.close();
            }
        } finally {
            ps.releasePreparedStatement();
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#deleteReplacement(int)
     */
    public void deleteReplacement(int sequence) throws Exception {
        JDBCPreparedStatement ps = db.getStatement("deleteReplacements.delete");
        try {
            ps.setInt(1, sequence);
            ps.execute();
        } finally {
            ps.releasePreparedStatement();
        }
        replacementsCache.clear();
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#updateReplacement(com.sslexplorer.services.Replacement)
     */
    public void updateReplacement(Replacement replacement) throws Exception {
        JDBCPreparedStatement ps = db.getStatement("updateReplacements.update");
        try {
            ps.setString(1, replacement.getMimeType());
            ps.setString(2, replacement.getSitePattern());
            ps.setString(3, replacement.getMatchPattern());
            ps.setString(4, replacement.getReplacePattern());
            ps.setInt(5, replacement.getReplaceType());
            ps.setInt(6, replacement.getSequence());
            ps.execute();
        } finally {
            ps.releasePreparedStatement();
        }
        replacementsCache.clear();
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#createReplacement(com.sslexplorer.services.Replacement)
     */
    public Replacement createReplacement(Replacement replacement) throws Exception {
        JDBCPreparedStatement ps = db.getStatement("createReplacement.insert");
        try {
            ps.setInt(1, replacement.getReplaceType());
            ps.setString(2, replacement.getMimeType());
            ps.setString(3, replacement.getSitePattern());
            ps.setString(4, replacement.getMatchPattern());
            ps.setString(5, replacement.getReplacePattern());
            ps.execute();
            return new DefaultReplacement(replacement.getMimeType(), replacement.getReplaceType(), db.getLastInsertId(ps,
                "createReplacement.lastInsertId"), replacement.getSitePattern(), replacement.getMatchPattern(), replacement
                            .getReplacePattern());
        } finally {
            replacementsCache.clear();
            ps.releasePreparedStatement();
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#getReplacements()
     */
    public List getReplacements() throws Exception {
        JDBCPreparedStatement ps = db.getStatement("getReplacements.select");
        try {
            ResultSet rs = ps.executeQuery();
            try {
                List v = new ArrayList();
                while (rs.next()) {
                    v.add(new DefaultReplacement(rs.getString("mime_type"), rs.getInt("replace_type"), rs.getInt("sequence"), rs
                                    .getString("site_pattern"), rs.getString("match_pattern"), rs.getString("replace_pattern")));
                }
                return v;
            } finally {
                rs.close();
            }
        } finally {
            ps.releasePreparedStatement();
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#getReplacement(int)
     */
    public Replacement getReplacement(int sequence) throws Exception {
        JDBCPreparedStatement ps = db.getStatement("getReplacement.select");
        ps.setInt(1, sequence);
        try {
            ResultSet rs = ps.executeQuery();
            try {
                if (rs.next()) {
                    return new DefaultReplacement(rs.getString("mime_type"), rs.getInt("replace_type"), rs.getInt("sequence"), rs
                                    .getString("site_pattern"), rs.getString("match_pattern"), rs.getString("replace_pattern"));
                }
            } finally {
                rs.close();
            }
        } finally {
            ps.releasePreparedStatement();
        }
        return null;
    }

    //
    // Authentication Schemes
    //

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#getAuthenticationSchemeSequences()
     */
    public List getAuthenticationSchemeSequences() throws Exception {
        JDBCPreparedStatement ps = db.getStatement("getAuthenticationSchemeSequences.select");
        try {
            ResultSet rs = ps.executeQuery();
            try {
                List sequences = new ArrayList();
                while (rs.next()) {
                    AuthenticationSchemeSequence sequence = buildAuthenticationSchemeSequence(rs);
                    if (sequence != null) {
                        sequences.add(sequence);
                    }
                }
                return sequences;
            } finally {
                rs.close();
            }
        } finally {
            ps.releasePreparedStatement();
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#getAuthenticationSchemeSequence(int)
     */
    public AuthenticationSchemeSequence getAuthenticationSchemeSequence(int id) throws Exception {
        JDBCPreparedStatement ps = db.getStatement("getAuthenticationSchemeSequence.select");
        try {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            try {
                if (rs.next()) {
                    return buildAuthenticationSchemeSequence(rs);
                }
            } finally {
                rs.close();
            }
        } finally {
            ps.releasePreparedStatement();
        }
        return null;
    }

    public AuthenticationSchemeSequence createAuthenticationSchemeSequence(String name, String description, String[] modules,
                                                                           Calendar dateCreated, Calendar dateAmended,
                                                                           boolean enabled) throws Exception {
        JDBCPreparedStatement ps = db.getStatement("createAuthenticationSchemeSequence.insert");
        try {
            ps.setString(1, name);
            ps.setString(2, description);
            Calendar now = Calendar.getInstance();
            ps.setString(3, db.formatTimestamp(now));
            ps.setString(4, db.formatTimestamp(now));
            ps.setInt(4, enabled ? 1 : 0);
            ps.execute();
            int id = db.getLastInsertId(ps, "createAuthenticationSchemeSequence.lastInsertId");
            AuthenticationSchemeSequence seq = getAuthenticationSchemeSequence(id);
            for (int i = 0; i < modules.length; i++) {
                seq.addModule(modules[i]);
            }
            updateSequence(seq);
            return seq;
        } finally {
            ps.releasePreparedStatement();
        }

    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#updateAuthenticationSchemeSequence(com.sslexplorer.security.AuthenticationSchemeSequence)
     */
    public void updateAuthenticationSchemeSequence(AuthenticationSchemeSequence sequence) throws Exception {
        JDBCPreparedStatement ps = db.getStatement("updateAuthenticationSchemeSequence.update");
        try {
            ps.setString(1, sequence.getResourceName());
            ps.setString(2, sequence.getResourceDescription());
            Calendar now = Calendar.getInstance();
            ps.setString(3, db.formatTimestamp(now));
            ps.setInt(4, sequence.getEnabled() ? 1 : 0);
            ps.setInt(5, sequence.getResourceId());
            ps.execute();
            updateSequence(sequence);
        } finally {
            ps.releasePreparedStatement();
        }
    }

    void updateSequence(AuthenticationSchemeSequence sequence) throws Exception {
        JDBCPreparedStatement ps = db.getStatement("updateSequence.delete");
        try {
            ps.setInt(1, sequence.getResourceId());
            ps.execute();
        } finally {
            ps.releasePreparedStatement();
        }
        int seq = 0;
        ps = db.getStatement("updateSequence.insert");
        try {
            for (Iterator i = sequence.modules(); i.hasNext();) {
                String module = (String) i.next();
                ps.setInt(1, sequence.getResourceId());
                ps.setString(2, module);
                ps.setInt(3, seq);
                ps.execute();
                seq += 10;
                ps.reset();
            }
        } finally {
            ps.releasePreparedStatement();
        }

    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#deleteAuthenticationSchemeSequence(java.lang.String)
     */
    public void deleteAuthenticationSchemeSequence(int id) throws Exception {
        JDBCPreparedStatement ps = db.getStatement("deleteAuthenticationSchemeSequence.delete.authSchemes");
        try {
            ps.setInt(1, id);
            ps.execute();
        } finally {
            ps.releasePreparedStatement();
        }
        ps = db.getStatement("deleteAuthenticationSchemeSequence.delete.authSequence");
        try {
            ps.setInt(1, id);
            ps.execute();
        } finally {
            ps.releasePreparedStatement();
        }
    }

    // 
    // Supporting methods
    //

    Tunnel buildTunnel(ResultSet rs) throws Exception {
        Timestamp cd = rs.getTimestamp("date_created");
        Calendar c = Calendar.getInstance();
        c.setTimeInMillis(cd == null ? System.currentTimeMillis() : cd.getTime());
        Timestamp ad = rs.getTimestamp("date_amended");
        Calendar a = Calendar.getInstance();
        a.setTimeInMillis(ad == null ? System.currentTimeMillis() : ad.getTime());
        return new DefaultTunnel(rs.getString("name"), rs.getString("description"), rs.getInt("tunnel_id"), rs.getInt("type"), rs
                        .getBoolean("auto_start"), rs.getString("transport"), rs.getString("username"), rs.getInt("source_port"),
                        new HostService(rs.getString("destination_host"), rs.getInt("destination_port")), rs
                                        .getBoolean("allow_external_hosts"), rs.getInt("parent_resource_permission"), c, a);
    }

    WebForward buildWebForward(ResultSet rs) throws Exception {

        int type = rs.getInt("type");
        int id = rs.getInt("id");
        Timestamp cd = rs.getTimestamp("date_created");
        Calendar created = Calendar.getInstance();
        created.setTimeInMillis(cd == null ? System.currentTimeMillis() : cd.getTime());
        Timestamp ad = rs.getTimestamp("date_amended");
        Calendar amended = Calendar.getInstance();
        amended.setTimeInMillis(ad == null ? System.currentTimeMillis() : ad.getTime());
        int parentResourcePermission = rs.getInt("parent_resource_permission");
        String category = rs.getString("category");
        String name = rs.getString("short_name");
        String description = rs.getString("description");
        String url = rs.getString("destination_url");
        if (type == WebForward.TYPE_REVERSE_PROXY) {
            JDBCPreparedStatement ps2 = db.getStatement("getWebForward.reverseProxy.selectById");
            try {
                ps2.setInt(1, id);
                ResultSet rs2 = ps2.executeQuery();
                try {
                    StringBuffer paths = new StringBuffer();
                    String hostHeader = "";
                    while (rs2.next()) {
                        if (paths.length() > 0) {
                            paths.append('\n');
                        }
                        paths.append(rs2.getString("path"));
                    }
                    JDBCPreparedStatement ps3 = db.getStatement("getWebForward.reverseProxyOptions.selectById");
                    try {
                        ps3.setInt(1, id);