JDBC Indexing
-
@dkf said in Duplicate code detected:
It's more important to get away from doing things by index, as that is actively fragile; binding by name is significantly more robust, in that it either works or fails properly. (JDBC is pretty bad that way; it has no way to bind parameters by name that I can see. My error rate decreased when I found a way to bind positionally without explixit numbering.) And make sure to test very thoroughly.
Yes, I really hate that (both in JDBC and OCCI). What do you do as an alternative? I've occasionally used an
intand incremented as I go.
-
@boomzilla Literally any chosen or invented wrapper that generates the code to map properties to indexes.
You're talking about Java here, so the 800 pound gorilla would be Hibernate. A quick Google gives me JOOQ, OrmLite, JMiniORM, Dapper, IBatis...
If none of those suit you, it's not all that much work to look at your standard pattern of accessing databases, and generating code starting from a schema configuration. In the Microsoft SQL Server world, it's pretty trivial to submit a query to the database and ask "What's the schema of the result set for this?", and it gives you the exact schema. Run that, and apply your template to the resulting schema - giving you a class that will deserialize a row.
For CRUD, it's pretty easy to generated all four operations for a table. I know you use Oracle - DESCRIBE gives you everything you need to automate this.
-
@Jaime DESCRIBE is a sqlplus command, not SQL. But OCI and OCCI have their own functions for describe, which work on query result sets.
We used that stuff plenty in the old project which was in C. Not for code generation, that was all softcoded data table layouts and runtime dynamic queries.
-
@Jaime said in JDBC Indexing:
@boomzilla Literally any chosen or invented wrapper that generates the code to map properties to indexes.
You're talking about Java here, so the 800 pound gorilla would be Hibernate. A quick Google gives me JOOQ, OrmLite, JMiniORM, Dapper, IBatis...
We use Hibernate. What drives me particularly nuts in Hibernate is that if you happen to not have unique column names it will throw an error, but doesn't give you any way to pull results by those same names.
I looked at the first two (JOOQ, OrmLite) and at first glance neither one seems to be using SQL. It's all their custom ORM objects.
If none of those suit you, it's not all that much work to look at your standard pattern of accessing databases, and generating code starting from a schema configuration. In the Microsoft SQL Server world, it's pretty trivial to submit a query to the database and ask "What's the schema of the result set for this?", and it gives you the exact schema. Run that, and apply your template to the resulting schema - giving you a class that will deserialize a row.
Yeah, I sometimes do this sort of thing. I guess I've never gotten to the level of making a code generator for it, and it feels like making classes for all this sort of thing is often overkill. And then you're still relying on indexed columns anyways.
For CRUD, it's pretty easy to generated all four operations for a table. I know you use Oracle - DESCRIBE gives you everything you need to automate this.
Eh, yeah, none of this is an issue.
-
@boomzilla said in JDBC Indexing:
And then you're still relying on indexed columns anyways.
It's not the existence of the indexed columns that is the problem, it's the fact that every line of code that a human writes with an indexed column is a potential bug. The compiler will never catch it, so you need either really great test coverage, or really thorough code review.
Generated code will never contain a bug outside of the generator itself. Bugs in generated code tend to be catastrophic and easy to find, as long as you don't get too fancy and end up with a lot of edge cases. That's why some micro ORMs tout features like "less than 500 lines of code", implying that if it works for one thing, it will work reliably for everything.
If all of your indexes are in generated code, then you can trust that the generator didn't put a "2" where a "3" should be. It also gets the indexes out of the user code, which makes it much easier to read and to confirm correctness.
foo.Price = 5.99;is much easier to validate than
foo.setDouble(7, 5.99);
-
@Jaime sure, but, for instance,
describeonly works on a schema object, not on a query. And yes, I realize this is just
complaining. I'm much more likely to have bugs in the SQL than in reading the results, based on experience.
-
@boomzilla If you define a view, you can describe it and get at the column information that way. Up to you whether or not you drop the view again afterwards.
-
@PleegWat yeah, that's what I was getting at with
. Also, would have to build the code generator.
-
@boomzilla
indeed. Also, generating code from live DB queries requires DB access. Which you may not have on the build server.
-
@PleegWat I have a development instance, so db access is no problem. Of course, another pain point is that you can't create a view with common table expressions (i.e., use
with).
-
@boomzilla So do I, but that implies checking in generated code rather than generating it as part of the build only. Which means I have to trust you that 16000 lines GeneratedDBAccessor.java is exactly what the version of the generator in source control would generate.
-
@PleegWat yeah...not going to do all this on build. I have no problem checking in generated code.
-
@PleegWat said in JDBC Indexing:
Which you may not have on the build server.
Build server? If you are generating Data Access Layer code on the build server, then how did you write the application in the first place? The code generation is typically done as part of the development process. You might be referring to re-generating the code as part of the build so as to guarantee correctness, but for audiences like @boomzilla, skipping this wouldn't typically be a problem.
Sophisticated ORMs can generate DALs from just a database schema, however those aren't for people like @boomzilla, as he already mentioned. The really simple ones require a database engine to bounce the query off of. This gives several benefits:
- The process simply won't complete if the query is malformed, so you know it's valid.
- The complicated work of mapping your query to the underlying objects is done by the actual database engine, making it both more accurate and more feature-rich.
At the same time, it does require that you have access to a database with the same schema as the one in production. Of course it's best if this is a test database, but these tools won't hold testing-in-production against you.
-
@Jaime Yes, regenerate on build. To be fair the only code generation I've dealt with has been configuration-based. As mentioned above, my database reflection stuff was for dynamic queries.
My experience in mid-size teams says that, unfortunately, you simply cannot assume GeneratedDBAccessor.java is exactly what the associated version of DBAccessorGenerator.java would spit out, if GeneratedDBAccessor.java lives in source control.
-
@PleegWat said in JDBC Indexing:
My experience in mid-size teams says that
Yes, not the situation here, so the comment is a distraction.
-
@boomzilla said in JDBC Indexing:
@dkf said in Duplicate code detected:
It's more important to get away from doing things by index, as that is actively fragile; binding by name is significantly more robust, in that it either works or fails properly. (JDBC is pretty bad that way; it has no way to bind parameters by name that I can see. My error rate decreased when I found a way to bind positionally without explixit numbering.) And make sure to test very thoroughly.
Yes, I really hate that (both in JDBC and OCCI). What do you do as an alternative? I've occasionally used an
intand incremented as I go.I use a variadic method to get an array of arguments, and I write non-trivial queries with a simple CTE up front to bind the arguments to a virtual per-statement
argstable (with one row). That lets me reuse them by name, and checking the first couple offers lines for correctness is much easier than scanning a whole complex query. I also annotate the constant containing the query with a description of what I expect the arguments and results to be. And I also have unit tests that check that (including schema compatibility; been bitten there too). And I validate that all queries come from constants.Real binding by name would be better, but I was using a database without stored procedures and the JDBC spec is retarded in that case.
