Java jdbc database connection

Question

1.00/5 (1 vote)

See more:

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

Posted 29-Jan-18 1:24am

four systems

Updated 29-Jan-18 3:14am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2018-01-29T01:32:00

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.

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.

Jochen Arndt · Answer 2 · 2018-01-29T03:14:00

Solution 3

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

Java

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

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:

Java

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

Posted 29-Jan-18 3:14am

Jochen Arndt

Comments

four systems 29-Jan-18 10:02am

thanks

four systems 29-Jan-18 10:03am

is there a way where records could read from csv file and insert in db

Jochen Arndt 29-Jan-18 10:41am

You need a CSV reader class (which might be implemented as CSV database driver). But you have to observe the CSV format (separation character, with/without header line), the encoding, and if the CSV fields matches the columns of the DB table.

four systems 30-Jan-18 3:26am

thanks

shwetakakran · Answer 3 · 2018-01-29T02:32:00

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 database connection

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0