Solved

Getting this out of my database

Posted on 2011-09-28
10
217 Views
Last Modified: 2012-05-12
public String[] getPrices(String flightNo) {
    String[] temp = {"Svart 1000", "Röd 800"};


How do I write a correct sql statment for this?

My database:
varchar, varchar, int

so flightno is the first one
color is the second
and price the last
0
Comment
Question by:Mickeys
  • 6
  • 3
10 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 36719513
I don't understand the begiining of your questuion - with java code


for your table this is the normal isnert statenmt would be:

insert into table_name (flight_no, color, price) values ('AA-263','blue',213.23)

so if your values are

String flight_no = "AA-263";
String color = "blue";
double price = 213.23

then your ssql staetmet in Java program wiill be:
stmt.executeUpdate("insert into table_name (flight_no, color, price) values ('" + flight_no+ "','" + color+ "'," + price + ")"); 

Open in new window


0
 
LVL 86

Expert Comment

by:CEHJ
ID: 36720361
Possibly the following (use PreparedStatement)
"SELECT color, price WHERE flightno = ?"

Open in new window

0
 

Author Comment

by:Mickeys
ID: 36812919
for_yan: it is a GET from database. Not insert so that wont work

CEHJ. I have no Idea of how that will work.



public String[] getPrices(String flightNo) {
    //String[] temp = {"Svart 1000", "Röd 800"};

A select method that goes into the a table called myflights and you have the flightnumber and want to get the color and price.

When this color and price is out I want to concanate them so it gets Black 1000 and Red 800 for example
}


0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 47

Expert Comment

by:for_yan
ID: 36812986

This how you do it:

I assume you know how to connect to databse - you need either
to connect inside the method of connect before and
pass sql.Statement object as one of ther parameters:

public String[] getPrices(String flightNo) {

//connection to database

ResultSet rs = statement.executeQuery("select color, price for table_name where flight_no = '" + flight_no + "'");

ArrayList<String> ar = new ArrayList<String>();
while(rs.next()){

String color = rs.getString(1);
float price = rs.getFloat(2);

ar.add(color + " " + price);


}

String [] temp = new Strring[ar.size()];
for(int j=0; j<ar.size(); j++)temp[j] = ar.get(j);


return temp;

}

Open in new window

0
 

Author Comment

by:Mickeys
ID: 36813065
Table price
===========
prFlightNo, prColor, prPrice
-----------

prFlightNo       varchar(10) PK
prColor          varchar(10) PK
prPrice          int(11)

Code:
return getInfo("SELECT prColor, prPrice for price where prFlightNo = '" + flightNo + "'", 2);

Output systm.out:
SELECT prColor, prPrice for price where prFlightNo = 'SK405'


Error:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'price where prFlightNo = 'SK405'' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813073

SELECT prColor, prPrice for price where prFlightNo = 'SK405'

why do you have "for price"

it sghould be "from price"

if "price" is the name of the table
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813074
sorry it was my misprint of course
0
 
LVL 47

Accepted Solution

by:
for_yan earned 500 total points
ID: 36813076

this is correct:

ResultSet rs = statement.executeQuery("select color, price from table_name where flight_no = '" + flight_no + "'");
0
 

Author Closing Comment

by:Mickeys
ID: 36813087
Ah stupied. I should have spotted that myself. Sometimes you just get so blind stearing at the code.

I guess I have found my self a guru in database. You are answering all my questions. :-) Very helpful
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813160
You are always welcome.

You are right sometimes it is difficult to spot the problem.
I usually try to minimize staring at the code - if often does not help. Just trying to do something - add a printout, or something,
and eventually it crops up.  Then sometimes become ashamed of myself for doing so much work for  some misprint.
But still it is faster than just staring.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Java exception bubble up 2 18
by zero exception 10 41
string fuctions 4 26
How to use three values with DATEDIFF 3 26
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will learn how to implement Singleton Design Pattern in Java.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question