struggling to fix sql code in java Statement object

[brisk slate]

trying to use jdbc statement but gettin syntax exception everytime
whats wrong with this part of code?
maybe another solution where i dont have to use java.sql package exists?

[tidal bridge]

⌛ This post has been reserved for your question.

Hey @brisk slate! Please use /close or the Close Post button above when you're finished. Please remember to follow the help guidelines. This post will be automatically closed after 300 minutes of inactivity.

TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here.

[young imp]

Can you please translate that and show the whole exception as text?

[brisk slate]

syntax error in the end(it is actually translated like this)

wait a sec

i need some time to restart it bcs my port is somehow used now

thats it

[young imp]

what's the code responsible for it?

and please share it in a codeblock, not an image

[tidal bridge]

Please format your code & make it more readable.
For the java programming language, it should look like this:

```java
public class Main{
public static void main(String[] args){
System.out.println("Hello World!");
}
}```
• These are backticks, not quotes.

[brisk slate]

sry if it is not correct

ill try to fix now

@KafkaListener(topics = "mytopic")
    public void addRank(ConsumerRecord<Long, String> record) {

        String url = "jdbc:postgresql://localhost:5432/valorant_recruiting_db";
        Connection con = null;
        Statement stmt = null;
        try {
            Class.forName("org.postgresql.Driver");
        }catch (ClassNotFoundException e){
                e.printStackTrace();
            }
        try{
            con = DriverManager.getConnection(url, "postgres", "vsevjopu12");
            stmt = con.createStatement();
            String userId = record.key().toString();
            System.out.println(userId.getClass());
            String userRank = record.value();
            String sql = "INSERT INTO recruit_schema.applicant (id) VALUES (" + userId + ") ON CONFLICT (id)";
            stmt.execute(sql);
            System.out.println("экзекутед");
            sql = "UPDATE recruit_schema.applicant SET rank = '" + userRank + "' WHERE id =" + userId;
            System.out.println("коннект есть");
        }catch (SQLException e){
            e.printStackTrace();
        }



    }```

here it is

[young imp]

What's the ON CONFLICT (id)?

Also, never use string concatenation for SQL statements - this can lead to SQL injection vulnerabilities

and ig you forgot quoting the user id

[brisk slate]

i took it from here(used screenshot to show the site)

[brisk slate]

wdym quotin?

[young imp]

then you probably forgot the DO UPDATE

[young imp]

VALUES ('"+userId+"') vs. VALUES ("+userId+")

[brisk slate]

i guess no, update worked in my pgadmin query tool with same db

[brisk slate]

i tried first variant

it didnt work

[young imp]

What did you run in pgadmin?

[brisk slate]

INSERT INTO recruit_schema.applicant (id) VALUES (1) ON CONFLICT (id)

UPDATE recruit_schema.applicant SET rank = 'BRONZE' WHERE id =1

it worked out correctly

[young imp]

What's line 40?

What's the actual value of userId?

in your code?

Can you check that?

[brisk slate]

39 String sql = "INSERT INTO recruit_schema.applicant (id) VALUES (" + userId + ") ON CONFLICT (id)";
40 stmt.execute(sql);

[brisk slate]

i checked it before

it was an instance of string

[young imp]

What is the value?

[brisk slate]

with value of 18 digits id

[young imp]

not the type

18 digits?

[brisk slate]

yes

[young imp]

that might be too long

[brisk slate]

i tried

1

[young imp]

Does it work with shorter values?

[brisk slate]

instead

[young imp]

so?

[brisk slate]

no

bigint supports 19 digits

[young imp]

What if you use String sql = "INSERT INTO recruit_schema.applicant (id) VALUES (1) ON CONFLICT (id)";?

for testing

[brisk slate]

thats actually what i did

i wll try one more time

maybe i did small syntax mistake

cant send it with codeblock

too long

should i try to use injecting sql lang here?

[young imp]

What exactly does that mean?

[young imp]

?

[brisk slate]

position 70

like this

i have variants like postgres

sql

and generic sql

i mentioned all that suits my code somehow

[young imp]

maybe try removing the ON CONFLICT (id)

[brisk slate]

it says that key id = 1 already exists and i cant add one more bcs of unique constraint on my id

if i put 2 instead of 1

my code eventually works

it actually interacts with my db

maybe i should try this with postgres specification?

[brisk slate]

mean this

[brisk slate]

ty btw

[young imp]

I think the variants don't make a difference

[brisk slate]

i thought the same way, but it actually does

only psql variant supports on conflict

my problem now is that i cant pass any variables here

cant even google it bcs everything i get is sql injections preventing instructions

[young imp]

idk why you need on conflict

but you should use Prepared Statements instead of string concatenation

[brisk slate]

bcs here i need something like INSERT IF NOT EXISTS

my id column values have to be unique for each raw

[young imp]

what about autoincrement?

[brisk slate]

doesnt work for me

i take this id

from jda

[young imp]

why not?

oh those are JDA IDs

[brisk slate]

yes

[young imp]

just use multiple SQL statements

first check whether it exists

then insert

[brisk slate]

i am actually pretty new to programming, can u pls describe it more detailed?

[brisk slate]

how am i gettin whether it is true or not return value from sql

[young imp]

//initialize
PreparedStatement checkStmt=con.prepareStatement("SELECT id FROM ... WHERE id = ?");
PreparedStatement insertStmt=con.prepareStatement(INSERT INTO ... (id) VALUES (?)");
//do it
checkStmt.setLong(1, id);
try(ResultSet res=checkStmt.executeQuery()){
    if(res.next()){
        //id exists
    }else{
        //id doesn't exist
        insertStmt.setLong(1, id);
        insertStmt.execute();
    }
}

obviously you need to replace the ...

but don't use + on Strings for SQL/database stuff

[brisk slate]

ty a lot for this

[brisk slate]

use append or what?

[young imp]

except you want unauthorised people to delete your database

[young imp]

Don't create SQL strings with user inout

input*

instead, use prepared statements

like in my example

[brisk slate]

so i can pass my java variables when using prepared statement?

i will better just goggle it nvm

i took lotta ur time already

[young imp]

yes

using methods like setString, setLong etc

you use ? instead of user input

and then substitute it with the setXYZ methods

[brisk slate]

ok ty alot