• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Liutauras Vilda
  • Knute Snortum
  • Bear Bibeault
Sheriffs:
  • Devaka Cooray
  • Jeanne Boyarsky
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • salvin francis
Bartenders:
  • Tim Holloway
  • Piet Souris
  • Frits Walraven

Using a variable in a database query

 
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Experts;

Good day. I am stuck and it is very confusing for me that when i passed the name of the user in the select statement the data id feteched from Mysql database. When i passed the variable i can'nt get any record.
The code is below.



Early reply will be appreciated.

Thanks
 
Marshal
Posts: 25194
64
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


You didn't "pass the variable". Your SQL asks for users whose name is "namecombo". And you don't have any users with that name (naturally).

What you should be doing is to use a PreparedStatement and inserting the variable into it. Like this:



Unfortunately it looks like somebody gave you a tool to execute a query and return a ResultSet. That's a bad idea because it gets in the way of using a PreparedStatement, which has several important advantages. You could try to build the SQL using string concatenation but that is subject to security problems, and it will break as soon as one of your users has an apostrophe in their name. (That's the O'Brien problem.)

So if you can I would recommend getting rid of that query-executing tool.
 
Hamad Sultan
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello;
Thanks for good suggestion. Can you guide me what is the best approach to insert, select, update and delete data from the database.

1. Create connection and result sets in each function separately?

2. Make a Generic class and create its object and then pass the parameters and get result?

Please guide me what is best approach for database application? I am beginner and required experts opinion.

Regards
 
Sheriff
Posts: 15038
251
Mac Android IntelliJ IDE Eclipse IDE Spring Debian Java Ubuntu Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If your application is a real-world app, you should not be writing your own DB connection / query management code. There are many frameworks out there that already provide the plumbing to do that for you. I recommend the Spring framework.

I agree with Paul's advice to get away from classes like DBCON. Homegrown solutions like this are fraught with security vulnerabilities. Code that uses this DBCON class is very likely to be vulnerable to SQL injection attacks, which are probably the easiest vulnerabilities to discover and exploit.
 
Paul Clapham
Marshal
Posts: 25194
64
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Hamad Sultan wrote:Can you guide me what is the best approach to insert, select, update and delete data from the database.



Clearly the best approach for an application you're writing for you and your friends is not the best approach for an application which will handle thousands of web requests every minute.

I would request starting with the basics. Create a connection at the start of your program and store it somewhere where you'll have access to it. Then when you want to access the data, write the code which creates and configures a PreparedStatement (or Statement) and then executes it. Don't forget to close those things and the ResultSets that they can produce. The try-with-resources statement is the best way to do that.

Later when you are working with real applications you may well find that it's easier to delegate all of that repetitive coding to frameworks like Spring, at the cost of working with complex frameworks.
 
Bartender
Posts: 21731
148
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1. Using class.forName() is a process that is very long obsolete. You can delete that statement entirely.

2. Regardless of whether you use a framework like Spring or choose to do it all by brute force, you should not be using DriverManager to get a database connection in a web application. Web applications should use database connection pools, which are created and maintained by the web application server itself, and accessed by using JNDI to locate their service interface.

3. The technical term for user-designed login processes is "hacked" or "pwned". It's much better to use the JEE-standard Container-Managed security system provided by the web application server.

4. If you absolutely must check a username/password yourself, this is a better SQL:
If the returned value is 0, the password is invalid for that user. If it's one, the password is valid. If it's more than 1, the database has duplicated user_ids and needs to be cleaned up (and should be configured to reject adding duplicate user_id's).

Doing it this way reduces exposure since the actual password is not brought into the appserver's memory where it might be scooped up by an attacker.
 
Hamad Sultan
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"
I would request starting with the basics. Create a connection at the start of your program and store it somewhere where you'll have access to it. Then when you want to access the data, write the code which creates and configures a PreparedStatement (or Statement) and then executes it. Don't forget to close those things and the ResultSets that they can produce. The try-with-resources statement is the best way to do that.
"
Respected Forum Members;

As i mention i am beginner. Can any one guide me the Generic class as  Paul Clapham suggested. My application have 3 tables in the database and only one URL is used for the Web Server. It is not a enterprise application.

Regards
 
Hamad Sultan
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please give me a code which i follow to do the job efficiently.

Thanks
 
Saloon Keeper
Posts: 6200
157
Android Mac OS X Firefox Browser VI Editor Tomcat Server Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's not how this site works. You got a lot of suggestions on how to improve the code. Did any of those make sense to you? If not, tell us where you got confused, and we'll try to clear things up.
 
Paul Clapham
Marshal
Posts: 25194
64
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Or if you are looking for a place which has example code with explanations, you could try this place: Trail: JDBC(TM) Database Access.

(In general if you want to find a tutorial which explains topic X in Java, then your web search keywords would be "java X tutorial".)
 
Gravity is a harsh mistress. But this tiny ad is pretty easy to deal with:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!