Last week, we had the author of TDD for a Shopping Website LiveProject. Friday at 11am Ranch time, Steven Solomon will be hosting a live TDD session just for us. See for the agenda and registration link
Last week, we had the author of TDD for a Shopping Website LiveProject. Friday at 11am Ranch time, Steven Solomon will be hosting a live TDD session just for us. See for the agenda and registration link
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

Java MySQL PreparedStatement does not work as intended

 
Ranch Foreman
Posts: 264
6
Eclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello guys,

I was using PreparedStatement with a for loop to create a method that will make a table for dynamically without me having to type in the whole thing but the result as good not the intended, apparently the setString method places the string within apostrophes ruining the statement, is there any other way?



Result:



Intended result:



thank you in advance
 
Saloon Keeper
Posts: 25470
180
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hmmm. Yes, I can see why it would do that.

You actually can (and sometimes must) quote column names in MySQL, but not with ordinary single quotes. You'd use "back-tick" quotes, instead.

I actually don't think that you can do this in a PreparedStatement. You'd need a standard Statement, instead and a LOT of caution to ensure that nobody can stuff SQL injection into your column name/type values.

Better yet, if you can, define a prototype table in your database and clone it as needed.

If you have an indeterminate set of columns, then perhaps what you really need is a NoSQL DBMS such as MongoDB or Node4J.
 
Yosuf Ibrahim
Ranch Foreman
Posts: 264
6
Eclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Fixed it this way



i could not find a better way, if there is please let me know
 
Marshal
Posts: 27211
87
Eclipse IDE Firefox Browser MySQL Database
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Usually people don't create enough SQL tables to make it worthwhile writing code to generate the tables. For example in the time it has taken you to work on this code, you could have manually typed in the commands to create a hundred or so tables, or maybe more. That's why it isn't a priority for the writers of JDBC drivers to make them work for CREATE TABLE commands, I suppose.

So I agree with Tim that there may be better ways to achieve what you want to do. Where does the need to create a lot of tables quickly come from?
 
Saloon Keeper
Posts: 13880
314
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There really is no point to using PreparedStatement for executing static commands.

Just put the entire table creation command in a text file, and add the text file as an embedded resource to your application:
 
Tim Holloway
Saloon Keeper
Posts: 25470
180
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Stephan van Hulst wrote:There really is no point to using PreparedStatement for executing static commands.



Quite so. Although I did wonder if some JDBC drivers might be capable of detecting injected SQL on static text when preparing statements. I don't think there's a formal rule on that one way or the other.

Yusuf's solution is fairly tidy. But unless the Prepare processor can detect such malice, it wouldn't be better than a plain statement.
 
Yosuf Ibrahim
Ranch Foreman
Posts: 264
6
Eclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hey guys,

Really thank you for all the support I am still learning and this is a personal project I am doing and hopefully would be a great learning experience. The idea is a I am making an Store Inventory management system for some of my family members, after I am done and I send them the application I will walk them through downloading MySQL and the java application I made and asking them to create the tables would be a hassle, that is why I have been trying to have the application create the Tables itself.

The reason I would rather not use the text file is because I am learning and I keep on editing the tables and its columns, so far I have deleted and remade the tables like 50 times in the past 48 hours alone, that's why I came up with having Enums that contain the table names and their columns as shown below and this allows me to automatically update the tables incase I made a spontaneous change as I usually do.





I am probably wrong in what I am doing, but it is what I do know how to do, so if there is a better method please let me know

Thank you again guys for your advice and help that I have received so far and that I am still going to receive, you guys have no idea how much your help means to me and others who are lost and guided to the light by you
 
Tim Holloway
Saloon Keeper
Posts: 25470
180
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Actually, this is a common issue and Spring Boot, for example, has a convention where you can create and pre-load database tables from resource file definitions that are made part of the deployable module.

And Spring JPA has the ability to create/mutate tables at application startup.

If you're not using either of those, you could simply create a resource file(s) that would go into the execution classpath and have a format something like this:

And so forth. I hope I remember how MySQL CREATE TABLE works!

Now you can load the resource and iterate through its keys, taking each value in turn and creating and executing the value as a SQL Statement (doesn't need to be PreparedStatement since there's no risk of inserting Bad Stuff).
 
It will give me the powers of the gods. Not bad for a tiny ad:
free, earth-friendly heat - a kickstarter for putting coin in your pocket while saving the earth
https://coderanch.com/t/751654/free-earth-friendly-heat-kickstarter
reply
    Bookmark Topic Watch Topic
  • New Topic