Java jdbc数据库连接 [英] Java jdbc database connection

问题描述

jdbc的代码是，在运行时错误是



java.sql.SQLSyntaxErrorException：列'ID'要么不在FROM列表中的任何表中，要么出现在连接规范中，并且不在连接规范的范围内，或者出现在HAVING子句中，并且不在GROUP BY列表中。如果这是CREATE或ALTER TABLE语句，那么'ID'不是目标表中的列。

at org.apache.derby.client.am.SQLExceptionFactory.getSQLException（Unknown Source）

at org.apache.derby.client.am.SqlException.getSQLException（未知来源）

at org.apache.derby.client.am.ClientStatement.execute（Unknown Source）

在Rdbms.javadbms.insertGood（javadbms.java:44）

在Rdbms.javadbms.main（javadbms.java:22）

引发者：错误42X04：列'ID'要么不在FROM列表中的任何表中，要么出现在连接规范中，并且不在连接规范的范围内，或者出现在HAVING子句中，并且不在GROUP BY列表中。如果这是CREATE或ALTER TABLE语句，那么'ID'不是目标表中的列。

at org.apache.derby.client.am.ClientStatement.completeSqlca（Unknown Source）

at org.apache.derby.client.am.ClientStatement.completeExecuteImmediate（未知来源）

at org.apache.derby.client.net.NetStatementReply.parseEXCSQLIMMreply（Unknown Source）

org.apache.derby.client.net.NetStatementReply.readExecuteImmediate（未知来源）

org.apache.derby.client.net.StatementReply.readExecuteImmediate（未知）来源）

org.apache.derby.client.net.NetStatement.readExecuteImmediate_（未知来源）

at org.apache.derby.client.am.ClientStatement.readExecuteImmediate （未知来源）

at org.apache.derby.client.am.ClientStatement.flowExecute（未知来源）

at org.apache.derby.client.am.ClientStatement .executeX（未知来源）

... 3更多

BUILD SUCCESSFUL（总时间：2 se conds）



表名很好，列号和名称

code for jdbc is , on run the error is

java.sql.SQLSyntaxErrorException: Column 'ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'ID' is not a column in the target table.
at org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.ClientStatement.execute(Unknown Source)
at Rdbms.javadbms.insertGood(javadbms.java:44)
at Rdbms.javadbms.main(javadbms.java:22)
Caused by: ERROR 42X04: Column 'ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'ID' is not a column in the target table.
at org.apache.derby.client.am.ClientStatement.completeSqlca(Unknown Source)
at org.apache.derby.client.am.ClientStatement.completeExecuteImmediate(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parseEXCSQLIMMreply(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.readExecuteImmediate(Unknown Source)
at org.apache.derby.client.net.StatementReply.readExecuteImmediate(Unknown Source)
at org.apache.derby.client.net.NetStatement.readExecuteImmediate_(Unknown Source)
at org.apache.derby.client.am.ClientStatement.readExecuteImmediate(Unknown Source)
at org.apache.derby.client.am.ClientStatement.flowExecute(Unknown Source)
at org.apache.derby.client.am.ClientStatement.executeX(Unknown Source)
... 3 more
BUILD SUCCESSFUL (total time: 2 seconds)

the table name is good and has the columns id and Name

package Rdbms;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSetMetaData;


public class javadbms
{
    private static String dbURL = "jdbc:derby://localhost:1527/Oracle;create=true;user=Android;password=java";
    private static String tableName = "GOOD";
    // jdbc Connection
    private static Connection conn = null;
    private static Statement stmt = null;

    public static void main(String[] args)
    {
        createConnection();
        insertGood(4, "id");              
    }
    
    private static void createConnection()
    {
        try
        {
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            //Get a connection
            conn = DriverManager.getConnection(dbURL); 
        }
        catch (Exception except)
        {
            except.printStackTrace();
        }
    }
    
    private static void insertGood(int id, String Name)
    {
        try
        {
            stmt = conn.createStatement();
            stmt.execute("insert into " + tableName + " values ("+id +", "+ Name +")");
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
    }        
}

我尝试了什么：



更改了代码，表名和检查列

What I have tried:

changed the code , table name and checked columns

推荐答案

java.sql.SQLSyntaxErrorException: Column 'ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'ID' is not a column in the target table.

您需要找出收到此消息的原因。您还需要学习如何使用参数化查询正确创建SQL语句，而不是使用字符串连接。

You need to find out why you are receiving this message. You also need to learn how to create SQL statements properly using parameterised queries, and not using string concatenation.

我不知道这是否是错误的原因但是字符串参数应该是引号括起来：

I don't know if this is the reason of the error but string parameters should be enclosed by quotes:

stmt.execute("INSERT INTO " + tableName + " VALUES (" + id + ", '" + Name + "')");

使用已建议的参数化查询时可以避免此类问题。



另一个错误的可能原因可能是当您的 id 列是自动增量列时。



无论如何您可以尝试使用完整的 INSERT 命令来检查是否会导致相同的错误：

Such problems can be avoided when using parameterised queries as already suggested.

Another possible reason for the error might be when your id column is an auto-increment column.

In any case you can try to use a full INSERT command to check if that results in the same the error:

stmt.execute("INSERT INTO " + tableName + " (id, Name) VALUES (" + id + ", '" + Name + "')");

For connecting java application with the mysql database, you need to follow 5 steps to perform database connectivity.

In this example we are using MySql as the database. So we need to know following informations for the mysql database:

Driver class: The driver class for the mysql database is com.mysql.jdbc.Driver.
Connection URL: The connection URL for the mysql database is jdbc:mysql://localhost:3306/sonoo where jdbc is the API, mysql is the database, localhost is the server name on which mysql is running, we may also use IP address, 3306 is the port number and sonoo is the database name. We may use any database, in such case, you need to replace the sonoo with your database name.
Username: The default username for the mysql database is root.
Password: Password is given by the user at the time of installing the mysql database. In this example, we are going to use root as the password.

这篇关于Java jdbc数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！