首页 文章

在插入SQL Server之前检查列值是否存在

提问于
浏览
0

使用JSoup解析HTML,然后将行插入MS SQL Server数据库 . 为了避免重复,我需要在插入执行之前检查guid是否存在 . 该代码有效,但我无法对guid进行验证 . 请参阅插入块中的Select语句 . 返回以下错误:

线程“main”中的异常com.microsoft.sqlserver.jdbc.SQLServerException:无法绑定多部分标识符“FeedMessage.guid” . 在com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)在com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)在com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement (SQLServerPreparedStatement.java:404)在com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement $ PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)在com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)在COM . microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)在com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)在com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement . Java的:155)在com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:314)在Archiver.Database.writer(Database.java:58)在RSS.Test.main(Test.java:35)的Java Resul t:1

// Need to use this class for all of the DB-related interactions. 
    // Add methods to this class for added capabilities from within the GUI
package Archiver;

import RSS.FeedMessage;
import RSS.Test;
import Scraper.Scraping;
import java.sql.*;

/**
 *
 * @author joel.ramsey
 */
public class Database {

    Statement stmt;

    String DriverName;
    String url = "jdbc:sqlserver://sss.ss.ss.ss:1433;databaseName=TextAnalytics";
    String DBuser = "xx";
    String DBpassword = "xxxxxx";

    //Method to connect to the DB upon initialization
    public void connectionText() throws ClassNotFoundException, SQLException {

        try (Connection con = DriverManager.getConnection(this.url, this.DBuser, this.DBpassword);) {

            if (con != null) {
                DatabaseMetaData dm = (DatabaseMetaData) con.getMetaData();
                System.out.println("Driver name: " + dm.getDriverName());
                System.out.println("Driver version: " + dm.getDriverVersion());
                System.out.println("Product name: " + dm.getDatabaseProductName());
                System.out.println("Product version: " + dm.getDatabaseProductVersion());
            }

        } catch (Exception e) {
            System.err.println("Got an exception...");
            System.err.println(e.getMessage());

        }

    }

    //Method to write the parsed content of the RSS source article to the DB.
    public void writer(String Title, String Description, String Link, String Author, String guid, String contents) throws SQLException, ClassNotFoundException {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Connection con = null;
        PreparedStatement writeStmt = null;
        ResultSet rs = null;
        try {
            con = DriverManager.getConnection(this.url, this.DBuser, this.DBpassword);
            writeStmt = con.prepareStatement("IF NOT EXISTS (SELECT TextAnalytics FROM Archive_Source WHERE guid = FeedMessage.guid) INSERT INTO Archive_Source(title,description,link,author,guid,contents) VALUES (?,?,?,?,?,?)");
            writeStmt.setString(1, Title);
            writeStmt.setString(2, Description);
            writeStmt.setString(3, Link);
            writeStmt.setString(4, Author);
            writeStmt.setString(5, guid);
            writeStmt.setString(6, contents);
            writeStmt.executeUpdate();
        } finally {
            close(con, writeStmt, rs);
        }
    }

    //Method to close the connection manually. Called at the conclusion of each DB contact job.
    protected void close(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
    }

    //Method to delete all contents from the table. Should be called from the GUI
    public void delete(String Title, String Description, String Link, String Author, String guid, String contents) throws SQLException, ClassNotFoundException {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Connection con = null;
        PreparedStatement writeStmt = null;
        ResultSet rs = null;
        try {
            con = DriverManager.getConnection(this.url, this.DBuser, this.DBpassword);
            writeStmt = con.prepareStatement("DELETE * FROM Archive_Source");
            writeStmt.executeUpdate();
        } finally {
            close(con, writeStmt, rs);
        }
    }
}

1 回答

  • 0

    您的SQL语句中有几个错误 . 我把它分成了几行,所以答案更容易阅读 .

    " IF NOT EXISTS (SELECT TextAnalytics FROM Archive_Source " +
    " WHERE guid = FeedMessage.guid) INSERT INTO " +
    " Archive_Source(title,description,link,author,guid,contents) " +
    " VALUES (?,?,?,?,?,?)") "
    
    • 什么是 FeedMessage.guid ?如果 FeedMessage 是表,则需要在 FROM 语句中引用它 .

    • 您正在设置6 INSERT 语句参数的值,但不是 guid 函数参数的值 . 在您说 WHERE guid = FeedMessage.guid 的部分中,您不应该参数化 guid ,或者像这样连接值 . "WHERE '" + guid + "'= FeedMessage.guid" .

    因此,如果我对您的代码的假设是正确的,则代码应如下所示 .

    try {
            con = DriverManager.getConnection(this.url, this.DBuser, this.DBpassword);
            writeStmt = con.prepareStatement(
                             " IF NOT EXISTS (SELECT TextAnalytics " +
                             " FROM Archive_Source WHERE guid = ?) " +
                             " INSERT INTO Archive_Source " +                                                          
                             " (title,description,link,author,guid,contents) " +
                             " VALUES (?,?,?,?,?,?) )";
            writeStmt.setString(1, guid);
            writeStmt.setString(2, Title);
            writeStmt.setString(3, Description);
            writeStmt.setString(4, Link);
            writeStmt.setString(5, Author);
            writeStmt.setString(6, guid);
            writeStmt.setString(7, contents);
            writeStmt.executeUpdate();
        } finally {
            close(con, writeStmt, rs);
        }
    

    注意我删除了 FeedMessage.guid 并将其替换为 guid 参数,并假设该比较的左侧是 Archive_Source 表中名为 guid 的字段 .

相关问题