Clojurians Log v2
sql 2024-01-03
Hi folks, I am new to next jdbc 1. I am trying to use join using next.jdbc ! Currently, since i am doing the left join on the same table, the output has the keys with the table name associated with it for eg, :emptable/id, now is there a way where i can just get id for it ? 2. I am currently using plain query with execute! using preparedStatement of next.jdbc is there any better way we can do it ? is the execute! along with preparedStatement safe from sql injection ?
1. in your query, specify the aliases explicitly, for example:
select
t1.id as id,
t2.id as parent_id
etcfor example:
(let [stmt (jdbc/prepare ....)
(doseq ...
(jdbc/execute stmt params)))To prevent sql injections, just pass all the parameters through params, not as a part of a query. Long story short, never use format or str for building a query, and you're good
@igrishaev I am currently passing the alias explicitly
SELECT parent.employee_id AS id,
parent.name AS name,Left JOIN on same table it is working little weird and I am also getting the table name as
:employee/idTo omit the table names, pass the :as option with a value from the result-set module. Smth like this {:as rs/as-unqualified-maps} Writing by memory
It's :builder-fn but I'd recommend just using unique aliases and keeping the qualified keys.
Otherwise use execute! You don't need to work with prepared statements directly in general. You do want to use connection pooling in production code. That's all explained in the docs.
@igrishaev @seancorfield thanks for the inputs
I had already implemented :builder-fn in custom config for all the conn during its creation but it was overriding because instead of passing the ds i was passing a new connection !
Eg: instead of
(execute! ds [params])
I was doing
(execute! (:connectable ds) [params])
This did create a new conn
Silly !! 😅