Examples with PreparedStatement java.sql.PreparedStatement used on opensource projects

Example 81 with PreparedStatement

use of java.sql.PreparedStatement in project translationstudio8 by heartsome.

the class TMDatabaseImpl method findAllTermsByText.

@Override
public Vector<Hashtable<String, String>> findAllTermsByText(String srcPureText, String srcLang, String tarLang) throws SQLException {
    commit();
    Vector<Hashtable<String, String>> terms = new Vector<Hashtable<String, String>>();
    // 构建SQL
    String getTermSql = dbConfig.getOperateDbSQL("getTerm");
    PreparedStatement stmt = conn.prepareStatement(getTermSql);
    stmt.setString(1, tarLang);
    stmt.setString(2, srcLang + "," + tarLang);
    stmt.setString(3, tarLang + "," + srcLang);
    stmt.setString(4, srcLang);
    stmt.setString(5, srcPureText);
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
        String tuid = rs.getString(1);
        String srcWord = rs.getString(2);
        String tgtWord = rs.getString(3);
        String property = rs.getString(4);
        Hashtable<String, String> tu = new Hashtable<String, String>();
        tu.put("tuid", tuid);
        tu.put("srcLang", srcLang);
        tu.put("srcWord", srcWord);
        tu.put("tgtLang", tarLang);
        tu.put("tgtWord", tgtWord);
        tu.put("property", property == null ? "" : property);
        terms.add(tu);
    }
    rs.close();
    stmt.close();
    return terms;
}

Example 82 with PreparedStatement

use of java.sql.PreparedStatement in project translationstudio8 by heartsome.

the class Test method main.

public static void main(String[] arg) {
    try {
        Connection conn2 = getConnection();
        System.out.println(conn2);
        String selectSql = "select * from test";
        PreparedStatement pstm2 = conn2.prepareStatement(selectSql);
        ResultSet rs = pstm2.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
        pstm2.close();
        rs.close();
        Connection conn1 = getConnection();
        System.out.println(conn1);
        String insertSql = "insert into test(test) values(?)";
        PreparedStatement pstm = conn1.prepareStatement(insertSql);
        pstm.setString(1, "test12313");
        pstm.executeUpdate();
        conn1.commit();
        pstm.close();
        conn1.close();
        String sql2 = "select * from test where test=?";
        PreparedStatement pstm3 = conn2.prepareStatement(sql2);
        pstm3.setString(1, "test12313");
        ResultSet rs1 = pstm3.executeQuery();
        while (rs1.next()) {
            System.out.println("\n\n  ==" + rs1.getString(1));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Example 83 with PreparedStatement

use of java.sql.PreparedStatement in project translationstudio8 by heartsome.

the class OperateDBImpl method checkDbExistOnServer.

@Override
public boolean checkDbExistOnServer() {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        MetaData data = (MetaData) metaData.clone();
        data.setDatabaseName(Constants.HSSYSDB);
        String url = Utils.replaceParams(dbConfig.getDbURL(), data);
        String driver = dbConfig.getDriver();
        Properties prop = Utils.replaceParams(dbConfig.getConfigProperty(), data);
        conn = getConnection(driver, url, prop);
        String getDbList = "SELECT DBNAME, QUALITY, USERS FROM HSCATSYSDB WHERE DBNAME=?";
        stmt = conn.prepareStatement(getDbList);
        stmt.setString(1, metaData.getDatabaseName());
        rs = stmt.executeQuery();
        if (rs.next()) {
            return true;
        } else {
            return false;
        }
    } catch (CloneNotSupportedException e) {
        logger.error("", e);
    } catch (ClassNotFoundException e) {
        logger.error("", e);
    } catch (SQLException e) {
        logger.error("", e);
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                logger.warn("", e);
            }
        }
        freeConnection(stmt, conn);
    }
    return true;
}

Example 84 with PreparedStatement

use of java.sql.PreparedStatement in project translationstudio8 by heartsome.

the class TMDatabaseImpl method findMatch_1.

@Override
public Vector<Hashtable<String, String>> findMatch_1(String puretext, String fullText, String srcLang, String tgtLang, int minSimilarity, boolean caseSensitive, int matchUpperLimit, int contextSize, String preHash, String nextHash, boolean isIngoreTarget) throws SQLException {
    Vector<Hashtable<String, String>> result = new Vector<Hashtable<String, String>>();
    int[] ngrams = generateNgrams(srcLang, puretext);
    int size = ngrams.length;
    if (size == 0) {
        return result;
    }
    // long l1 = System.currentTimeMillis();
    List<String> tpkids = getCandidatesTextDataPks4Oracle(srcLang, minSimilarity, ngrams);
    // System.out.println("查MATEX_LANG表:" + (System.currentTimeMillis() - l1));
    // long l = System.currentTimeMillis();
    // 过虑拆分条件,解决Oracle中where xx in (参数) 参数不越过1000个
    StringBuffer bf = new StringBuffer();
    List<String> tmpTpkids = new ArrayList<String>();
    for (int i = 0; i < tpkids.size(); i++) {
        String tpkid = tpkids.get(i);
        bf.append(",");
        bf.append(tpkid);
        if ((i + 1) % 100 == 0) {
            tmpTpkids.add(bf.toString().substring(1));
            bf = new StringBuffer();
        }
    }
    if (bf.toString().equals("")) {
        return result;
    }
    tmpTpkids.add(bf.toString().substring(1));
    bf = new StringBuffer();
    bf.append("TPKID IN (" + tmpTpkids.get(0) + ")");
    for (int i = 1; i < tmpTpkids.size(); i++) {
        bf.append(" OR TPKID IN (" + tmpTpkids.get(i) + ")");
    }
    String textDataSql = dbConfig.getOperateDbSQL("getTMMatch1");
    textDataSql = textDataSql.replace("__WHERE__", bf.toString());
    PreparedStatement stmt = conn.prepareStatement(textDataSql);
    stmt.setString(1, srcLang);
    stmt.setString(2, tgtLang);
    ResultSet rs = stmt.executeQuery();
    Map<Integer, Map<String, String>> tuSrc = new HashMap<Integer, Map<String, String>>();
    Map<Integer, Map<String, String>> tuTgt = new HashMap<Integer, Map<String, String>>();
    while (rs.next()) {
        Integer groupId = rs.getInt("GROUPID");
        String lang = rs.getString("LANG");
        String pureText = rs.getString("PURE");
        String content = rs.getString("CONTENT");
        String creationId = rs.getString("CREATIONID");
        creationId = creationId == null ? "" : creationId;
        String creationDate = "";
        Timestamp tempCdate = rs.getTimestamp("CREATIONDATE");
        if (tempCdate != null) {
            creationDate = DateUtils.formatToUTC(tempCdate.getTime());
        }
        String changeDate = "";
        Timestamp tempChangeDate = rs.getTimestamp("CHANGEDATE");
        if (tempChangeDate != null) {
            changeDate = DateUtils.formatToUTC(tempChangeDate.getTime());
        }
        String changeid = rs.getString("CHANGEID");
        changeid = changeid == null ? "" : changeid;
        String projectRef = rs.getString("PROJECTREF");
        projectRef = projectRef == null ? "" : projectRef;
        String jobRef = rs.getString("JOBREF");
        jobRef = jobRef == null ? "" : jobRef;
        String client = rs.getString("CLIENT");
        client = client == null ? "" : client;
        if (lang.equalsIgnoreCase(srcLang)) {
            int distance;
            if (caseSensitive) {
                if (isIngoreTarget) {
                    distance = similarity(puretext, pureText);
                } else {
                    distance = similarity(fullText, content);
                }
            } else {
                if (isIngoreTarget) {
                    distance = similarity(puretext.toLowerCase(), pureText.toLowerCase());
                } else {
                    distance = similarity(fullText.toLowerCase(), content.toLowerCase());
                }
            }
            if (distance == 100 && CommonFunction.checkEdition("U")) {
                String preContext = rs.getString("PRECONTEXT");
                preContext = preContext == null ? "" : preContext;
                String nextContext = rs.getString("NEXTCONTEXT");
                nextContext = nextContext == null ? "" : nextContext;
                if (preContext != null && nextContext != null) {
                    String[] preContexts = preContext.split(",");
                    String[] nextContexts = nextContext.split(",");
                    if (preContexts.length > contextSize) {
                        //$NON-NLS-1$
                        preContext = "";
                        for (int i = 0; i < contextSize; i++) {
                            //$NON-NLS-1$
                            preContext += "," + preContexts[i];
                        }
                        if (!"".equals(preContext)) {
                            //$NON-NLS-1$
                            preContext = preContext.substring(1);
                        }
                    }
                    if (nextContexts.length > contextSize) {
                        //$NON-NLS-1$
                        nextContext = "";
                        for (int i = 0; i < contextSize; i++) {
                            //$NON-NLS-1$
                            nextContext += "," + nextContexts[i];
                        }
                        if (!"".equals(nextContext)) {
                            //$NON-NLS-1$
                            nextContext = nextContext.substring(1);
                        }
                    }
                    if (preHash.equals(preContext) && nextHash.equals(nextContext)) {
                        distance = 101;
                    }
                }
            }
            if (distance >= minSimilarity) {
                Map<String, String> srcMap = new HashMap<String, String>();
                srcMap.put("srcLang", lang);
                srcMap.put("srcText", pureText);
                srcMap.put("srcContent", content);
                srcMap.put("srcCreationId", creationId);
                srcMap.put("srcCreationDate", creationDate);
                srcMap.put("srcChangeId", changeid);
                srcMap.put("srcChangeDate", changeDate);
                srcMap.put("srcProjectRef", projectRef);
                srcMap.put("srcJobRef", jobRef);
                srcMap.put("srcClient", client);
                srcMap.put("similarity", distance + "");
                tuSrc.put(groupId, srcMap);
            }
        }
        if (lang.equalsIgnoreCase(tgtLang)) {
            Map<String, String> tgtMap = new HashMap<String, String>();
            tgtMap.put("tgtLang", lang);
            tgtMap.put("tgtText", pureText);
            tgtMap.put("tgtContent", content);
            tgtMap.put("tgtCreationId", creationId);
            tgtMap.put("tgtCreationDate", creationDate);
            tgtMap.put("tgtChangeId", changeid);
            tgtMap.put("tgtChangeDate", changeDate);
            tgtMap.put("tgtProjectRef", projectRef);
            tgtMap.put("tgtJobRef", jobRef);
            tgtMap.put("tgtClient", client);
            tuTgt.put(groupId, tgtMap);
        }
    }
    stmt.close();
    String dbName = getMetaData().getDatabaseName();
    if (tuSrc.size() > 0) {
        Iterator<Entry<Integer, Map<String, String>>> itr = tuSrc.entrySet().iterator();
        while (itr.hasNext()) {
            Entry<Integer, Map<String, String>> entry = itr.next();
            Integer key = entry.getKey();
            Map<String, String> srcMap = entry.getValue();
            Map<String, String> tgtMap = tuTgt.get(key);
            if (tgtMap == null) {
                continue;
            }
            Hashtable<String, String> tu = new Hashtable<String, String>();
            tu.putAll(srcMap);
            tu.putAll(tgtMap);
            // 应用于origin属性
            tu.put("dbName", dbName);
            result.add(tu);
        }
    }
    int resultSize = result.size();
    if (resultSize > 1) {
        Collections.sort(result, new FindMatchComparator());
    }
    while (resultSize > matchUpperLimit) {
        resultSize--;
        result.remove(resultSize);
    }
    // System.out.println(bf.toString());
    return result;
}

Example 85 with PreparedStatement

use of java.sql.PreparedStatement in project translationstudio8 by heartsome.

the class OperateSystemDBImpl method getSysDbNames.

@Override
public List<String> getSysDbNames(int type) {
    MetaData data = null;
    try {
        data = (MetaData) metaData.clone();
        data.setDatabaseName(Constants.HSSYSDB);
    } catch (CloneNotSupportedException e) {
        logger.warn("", e);
        return null;
    }
    String driver = dbConfig.getDriver();
    String url = Utils.replaceParams(dbConfig.getDbURL(), data);
    Properties prop = Utils.replaceParams(dbConfig.getConfigProperty(), data);
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    List<String> result = new ArrayList<String>();
    try {
        conn = getConnection(driver, url, prop);
        String sql = dbConfig.getSysDbList();
        stmt = conn.prepareStatement(sql);
        stmt.setInt(1, type);
        rs = stmt.executeQuery();
        while (rs.next()) {
            result.add(rs.getString("DBNAME"));
        }
    } catch (ClassNotFoundException e) {
        logger.warn("", e);
    } catch (SQLException e) {
        logger.warn("", e);
    } finally {
        Statement _stmt = null;
        try {
            _stmt = conn.createStatement();
            _stmt.executeUpdate("SHUTDOWN;");
        } catch (SQLException e) {
            logger.error("", e);
        } finally {
            if (stmt != null) {
                try {
                    _stmt.close();
                } catch (SQLException e) {
                    logger.warn("", e);
                }
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                logger.warn("", e);
            }
        }
        freeConnection(stmt, conn);
    }
    return result;
}