java.sql.SQLException : no such column with the value I'm trying to insert

Question

项目描述

我正在尝试构建一个 Java 应用程序，用于使用 sqlite 训练外语词汇，目前没有任何 GUI 或太多功能。 sqlite 数据库非常小:它只有一张表(名为 vocabulary)和两列(名为 english 和 german)。项目上传至Github .

exec:java 目标在我的项目中起什么作用？

我的词汇列表以 csv 文件形式存储在外部存储库中。文件 LoadVocabularyListIntoDB.java 的唯一目的是解析该文件并将每一行插入 sqlite 数据库。

确切的错误是什么？

我认为，maven编译日志中的这些行很重要:

java.lang.reflect.InvocationTargetException
[...]
Caused by: java.lang.RuntimeException: Cannot insert word pair into SQL Database.
    at voc.Database.insertWordPairIntoTable(Database.java:79)
    at voc.LoadVocabularyListIntoDB.insertVocabularyListIntoDB(LoadVocabularyListIntoDB.java:52)
[...]
Caused by: java.sql.SQLException: no such column: accountant
[...]
    at voc.Database.insertWordPairIntoTable(Database.java:76)

最后两行，特别是错误消息no such column: account证明我的解析有效，因为这实际上是第一行的第一列。

但我不明白，为什么 accountant 被解释为一列，因为我对该方法的测试返回 true，这意味着方法 insertWordPairIntoTable() 应该可以工作.

    @Test
    public void TestDataInsertion() {
        db = null;
        db = new Database();

        String f = "/tmp/test.db";
        db.setDBfilename(f);
        db.createNewEmptyDbFile();
        db.establishConnection();

        db.createBasicSqlTable();

        db.insertWordPairIntoTable("'time'", "'zeit'");
        assertEquals(db.getGermanTranslation("'time'"), "zeit");
    }

<小时/>

其次，我不明白在我的例子中，InitationTargetException 是什么意思。我读过这个 stackOverflow 线程:Java: InvocationTargetException ，但是用 try/catch block 包围 theDB.insertWordPairIntoTable(englishWord, germanWord); 行并没有帮助——这是我根据这行 Maven 日志的调用方法:

at voc.LoadVocabularyListIntoDB.insertVocabularyListIntoDB(LoadVocabularyListIntoDB.java:52)

从来没有在那里抛出过InitationTargetException。

请注意:由于文件 LoadVocabularyListIntoDB.java 是在构建项目时执行的，因此我无法调试该文件，例如带有断点之类的(至少，我还没有找到解决方案)

完整日志:

<小时/>

目录列表

.
├── package-list
├── pom.xml
├── src
│   ├── main
│   │   └── java
│   │       └── voc
│   │           ├── Control.java
│   │           ├── Database.java
│   │           └── LoadVocabularyListIntoDB.java
│   └── test
│       └── java
│           └── voc
│               └── DatabaseTest.java
└── voc.iml

<小时/>

ma​​ven编译日志

$ mvn compile
[INFO] Scanning for projects...
[WARNING] 
[WARNING] Some problems were encountered while building the effective model for voc:voc:jar:1.0-SNAPSHOT
[WARNING] 'build.plugins.plugin.version' for org.apache.maven.plugins:maven-jar-plugin is missing. @ line 76, column 21
[WARNING] 
[WARNING] It is highly recommended to fix these problems because they threaten the stability of your build.
[WARNING] 
[WARNING] For this reason, future Maven versions might no longer support building such malformed projects.
[WARNING] 
[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building voc 1.0-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- maven-resources-plugin:2.6:resources (default-resources) @ voc ---
[WARNING] Using platform encoding (UTF-8 actually) to copy filtered resources, i.e. build is platform dependent!
[INFO] skip non existing resourceDirectory /home/toogley/src/voc/src/main/resources
[INFO] 
[INFO] --- maven-compiler-plugin:3.1:compile (default-compile) @ voc ---
[INFO] Changes detected - recompiling the module!
[WARNING] File encoding has not been set, using platform encoding UTF-8, i.e. build is platform dependent!
[INFO] Compiling 3 source files to /home/toogley/src/voc/target/classes
[INFO] 
[INFO] --- exec-maven-plugin:1.5.0:java (compilation preparations) @ voc ---
[WARNING] 
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.codehaus.mojo.exec.ExecJavaMojo$1.run(ExecJavaMojo.java:294)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.RuntimeException: Cannot insert word pair into SQL Database.
        at voc.Database.insertWordPairIntoTable(Database.java:79)
        at voc.LoadVocabularyListIntoDB.insertVocabularyListIntoDB(LoadVocabularyListIntoDB.java:52)
        at voc.LoadVocabularyListIntoDB.<init>(LoadVocabularyListIntoDB.java:25)
        at voc.LoadVocabularyListIntoDB.main(LoadVocabularyListIntoDB.java:57)
        ... 6 more
Caused by: java.sql.SQLException: no such column: accountant
        at org.sqlite.core.NativeDB.throwex(NativeDB.java:397)
        at org.sqlite.core.NativeDB._exec(Native Method)
        at org.sqlite.jdbc3.JDBC3Statement.executeUpdate(JDBC3Statement.java:116)
        at voc.Database.insertWordPairIntoTable(Database.java:76)
        ... 9 more
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 2.962 s
[INFO] Finished at: 2016-07-22T22:40:31+02:00
[INFO] Final Memory: 16M/159M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.codehaus.mojo:exec-maven-plugin:1.5.0:java (compilation preparations) on project voc: An exception occured while executing the Java class. null: InvocationTargetException: Cannot insert word pair into SQL Database. no such column: accountant -> [Help 1]
[ERROR] 
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR] 
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

<小时/>

数据库.java

package voc;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;

public class Database {
    private Connection conn = null;
    private Statement st = null;
    private String DBfilename  = null;

    public void setDBfilename(String pFilename)
    {
        this.DBfilename = pFilename;
    }

    public void createNewEmptyDbFile() {
        try {
            File file = new File(this.DBfilename);
            file.delete();
        } catch (Exception e) {
            throw new RuntimeException("Cannot create a new database file.",e);
        }
    }

    public void establishConnection() {
        try {
            // create database connection
            conn = DriverManager.getConnection("jdbc:sqlite:" + this.DBfilename);
            st = conn.createStatement();
            st.setQueryTimeout(30);
        } catch (SQLException e) {
            throw new RuntimeException("Cannont create a connection to the SQL database.",e);
        }
    }

    public void createBasicSqlTable() {
        try {
            String s = "create table vocabulary (english string, german string);";
            st.executeUpdate(s);
        } catch (SQLException e) {
            throw new RuntimeException("Cannot create a basic SQL Table.",e);

        }
    }

    /**
     * @returns the names of the SQL tables.
     */
    public ArrayList<String> showTables() {
        try {
            String s = "SELECT name FROM sqlite_master WHERE type='table';";
            ResultSet rs = st.executeQuery(s);

            ArrayList<String> SQLTableNames = new ArrayList<String>();
            while (rs.next())
            {
                 SQLTableNames.add(rs.getString(1));
            }

            return SQLTableNames;

        } catch (SQLException e) {
            throw new RuntimeException("SQL command to select all tables failed", e);
        }
    }

    /**
     * @param englishVoc: given word we want to learn
     * @param germanTranslation: translation of the given word
     */
    public void insertWordPairIntoTable(String englishVoc, String germanTranslation) {
        try {
            String s = "insert into vocabulary values("
                    + englishVoc + "," + germanTranslation + ");";
            st.executeUpdate(s);

        } catch (SQLException e) {
            throw new RuntimeException("Cannot insert word pair into SQL Database.", e);
        }
    }

    /**
     * @param pEnglishVoc: english word we want to learn (1st column of db)
     * @return the german translation of the parameter (2nd column of db)
     */
    public String getGermanTranslation(String pEnglishVoc) {
        ResultSet rs;
        try {
            String s = "select german from vocabulary where english="
                    + pEnglishVoc + ";";
            rs = st.executeQuery(s);
            return rs.getString(1);

        } catch (SQLException e) {
            throw new RuntimeException("Cannot get german translation for given english word.", e);
        }
    }
}

<小时/>

LoadVocabularyListIntoDB.java

package voc;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;

import java.io.IOException;
import java.nio.charset.Charset;

import java.net.MalformedURLException;
import java.net.URL;

import static java.nio.charset.Charset.forName;

public class LoadVocabularyListIntoDB {
    private Database theDB;

    public LoadVocabularyListIntoDB() {
        theDB = new Database();
        theDB.setDBfilename("database.db");
        theDB.createNewEmptyDbFile();
        theDB.establishConnection();
        theDB.createBasicSqlTable();

        this.insertVocabularyListIntoDB();
    }


    public void insertVocabularyListIntoDB() {
        URL url = null;

        try {
            url = new URL("https://raw.githubusercontent.com/toogley/voc-data/master/technology_and_society");
        } catch (MalformedURLException e) {
            throw new RuntimeException("URL of VocabularyList(" + url +") is malformed", e);
        }

        // CSVParser.parse(url,...) needs a Charset object
        Charset charset = forName("UTF-8");

        CSVParser parser = null;
        try {
            parser = CSVParser.parse(url, charset, CSVFormat.RFC4180);
        } catch (IOException e) {
            throw new RuntimeException("Failed to read from" + url, e);
        }

        for (CSVRecord csvRecord : parser) {
            String englishWord = csvRecord.get(0);
            String germanWord = csvRecord.get(1);

            theDB.insertWordPairIntoTable(englishWord, germanWord);
        }
    }

    public static void main(String[] args) {
        new LoadVocabularyListIntoDB();
    }
}

<小时/>

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>voc</groupId>
    <artifactId>voc</artifactId>
    <packaging>jar</packaging>
    <version>1.0-SNAPSHOT</version>
    <name>voc</name>
    <url>http://maven.apache.org</url>
    <repositories>
        <repository>
            <id>tmate</id>
            <url>http://maven.tmatesoft.com/content/repositories/releases</url>
        </repository>
    </repositories>
    <dependencies>
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.8.11.2</version>
        </dependency>
        <!-- http://mvnrepository.com/artifact/juni1t/junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>org.dbunit</groupId>
            <artifactId>dbunit</artifactId>
            <version>2.5.2</version>
            <scope>test</scope>
        </dependency>


        <!-- The dependencies below are needed for inserting the vocabularyList into DB. -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-csv</artifactId>
            <version>1.3</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.5</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.5.0</version>
                <executions>
                    <execution>
                        <id>compilation preparations</id>
                        <phase>compile</phase>
                        <goals>
                            <goal>java</goal>
                        </goals>
                    </execution>
                    <execution>
                        <id>test preparations</id>
                        <phase>test</phase>
                        <goals>
                            <goal>java</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <!-- adds every word pair of vocabulary csv list (https://github.com/toogley/voc-data)
                         to the SQL Database (while building) -->
                    <mainClass>voc.LoadVocabularyListIntoDB</mainClass>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <configuration>
                    <archive>
                        <manifest>
                            <mainClass>voc.Control</mainClass>
                        </manifest>
                    </archive>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

<小时/>

Answer 1

这就是为什么您应该使用带有参数占位符的 PreparedStatement 而不是将值连接到查询字符串中。问题是在您的测试中您在值周围添加了引号。这避免了语句中最基本的问题，但是您使用的数据源没有这些引号，因此输入:

accountant,Buchhalter

执行的查询是:

insert into vocabulary values(accountant,Buchalter)

而不是你的意图:

insert into vocabulary values('accountant','Buchalter')

解决方案是不添加引号，而是重写您的方法以使用准备好的语句:

public void insertWordPairIntoTable(String englishVoc, String germanTranslation) {
    try (PreparedStatement insert = connection.prepareStatement(
            "insert into vocabulary values(?,?)")) {
        insert.setString(1, englishVoc);
        insert.setString(2, germanTranslation);

        insert.executeUpdate();
    } catch (SQLException e) {
        throw new RuntimeException("Cannot insert word pair into SQL Database.", e);
    }
}

您可能需要考虑准备一次语句，以便可以重复使用它。

下面是您其他问题的附录。如果您需要更多信息，您确实需要针对每个问题提出一个单独的问题来仅解决该单个问题。

java.lang.reflect.InitationTargetException 是通过 Maven 执行此操作引起的。 Maven 通过反射执行您的代码，代码中引发的任何异常都会在进入 Maven 之前被包装在 java.lang.reflect.InvocatTargetException 中。

关于您的调试:我不确定您的意思。你应该能够在任何像样的 IDE 中调试它；在构建期间执行的代码(我认为不是:maven 在构建阶段之后执行它)始终可以通过单独执行来进行调试。