• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL queries

 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm creating a RESTFul service in Java and I have a method that gets some vehicle information from a MySQL database. The parameter is a license plate. I want my method to make an SQL query and return a result set that contains the vehicle year, vehicle make, vehicle model, vehicle mileage and so on. How would I get the contents of the result set of my query? How would I get my RESTFul service to return a response in JSON that would contain the contents from my result set? Here is a snippet from my code:

@GET
@Path("/vehicle/{licensePlate}/")
public VehicleInfo getVehicleInfo(@PathParam("licensePlate") String licensePlate) {


try
{

// This will load the MySQL driver, each DB has its own driver
Class.forName("com.mysql.jdbc.Driver");
// Setup the connection with the DB
connect = DriverManager.getConnection (url, "root", "password11");
// Statements allow to issue SQL queries to the database
statement = connect.createStatement();
// Result set get the result of the SQL query
resultSet = statement.executeQuery("select * from VehicleInformation where LicensePlate = " + licensePlate);




}
catch(Exception e)
{
e.printStackTrace();
}


return VehicleInfo;
}
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Presumably the VehicleInfo class has fields that map to columns in the VehicleInformation table.
So create a VehicleInfo object with those values and return that.
Your REST framework should do the rest for you.

By the way, you should be using a PreparedStatement there and binding (setString()) for the licensePlate variable.
 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:Presumably the VehicleInfo class has fields that map to columns in the VehicleInformation table.
So create a VehicleInfo object with those values and return that.
Your REST framework should do the rest for you.

By the way, you should be using a PreparedStatement there and binding (setString()) for the licensePlate variable.


Suppose my VehicleInfo class has the fields for all of the items I mentioned in my previous post. Suppose there are multiple rows in the database table. How would I get the VehicleInfo object to store the values from multiple rows of the database table? If my VehicleInfo class has a field for the year, how would I get the VehicleInfo object to store multiple years?
 
Paul Clapham
Sheriff
Posts: 21551
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You would start by defining a VehicleInfo class which models the data that you want to collect from the database. It's possible you may need other supporting classes as well -- your throwaway statement about "years" suggests that might be the case. Then when you have that done, you would write code which selects the relevant data and writes it to a VehicleInfo object -- or more than one VehicleInfo object, or one of them plus several other objects, or whatever your design turned out to be.

That's all that can be said so far; your initial question sounded reasonable enough but then you threw in some things which might imply that it's actually more complicated. However since we don't know much about your database tables or what you're expected to return, we can't really comment on those things.
 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:You would start by defining a VehicleInfo class which models the data that you want to collect from the database. It's possible you may need other supporting classes as well -- your throwaway statement about "years" suggests that might be the case. Then when you have that done, you would write code which selects the relevant data and writes it to a VehicleInfo object -- or more than one VehicleInfo object, or one of them plus several other objects, or whatever your design turned out to be.

That's all that can be said so far; your initial question sounded reasonable enough but then you threw in some things which might imply that it's actually more complicated. However since we don't know much about your database tables or what you're expected to return, we can't really comment on those things.


Suppose I retrieve multiple rows of data from my database table:
2000, ford, mustang, 3000
2003, honda, civic, 5900
1990, toyota, corolla, 90333

Should I put the different years in a list, the different makes in another list, the different models in another list, and so on? Should my object contain multiple lists?
 
Paul Clapham
Sheriff
Posts: 21551
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fred Victa wrote:Should I put the different years in a list, the different makes in another list, the different models in another list, and so on? Should my object contain multiple lists?


Good heavens, no! Like Dave said, you should produce a List of VehicleInfo objects. One object per database row.
 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I got it to work. I created a list of VehicleInfo objects.
 
Junilu Lacar
Bartender
Pie
Posts: 8754
78
Android Eclipse IDE IntelliJ IDE Java Linux Mac Scala Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One little note: "Info" is one of those name suffixes that are kind of redundant and they lead your mind away from Object-Oriented thinking.

Consider the name VehicleInfo and compare it to just Vehicle

The former leads you to the kind of thinking where this class is all about information pertaining to a vehicle. That takes away from the biggest benefit of Object-Orientation, which is behavior. The fact that there is information about a vehicle embedded or encapsulated in this class should be secondary to the behavior that instances of this class can exhibit.

Other similar suffixes are "Data" as in "VehicleData" and "Details" as in "VehicleDetails".

If you go with just Vehicle for the name, it makes you focus less on the encapsulated information. That fact that there is information encapsulated in this class should be apparent when you look at the list of getters/setters available but your focus really should be on the object behavior.
 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Junilu Lacar wrote:One little note: "Info" is one of those name suffixes that are kind of redundant and they lead your mind away from Object-Oriented thinking.

Consider the name VehicleInfo and compare it to just Vehicle

The former leads you to the kind of thinking where this class is all about information pertaining to a vehicle. That takes away from the biggest benefit of Object-Orientation, which is behavior. The fact that there is information about a vehicle embedded or encapsulated in this class should be secondary to the behavior that instances of this class can exhibit.

Other similar suffixes are "Data" as in "VehicleData" and "Details" as in "VehicleDetails".

If you go with just Vehicle for the name, it makes you focus less on the encapsulated information. That fact that there is information encapsulated in this class should be apparent when you look at the list of getters/setters available but your focus really should be on the object behavior.


I'll change the name to Vehicle.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic