SQL queries

[Fred Victa]

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]

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]

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]

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]

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]

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]

I got it to work. I created a list of VehicleInfo objects.

[Junilu Lacar]

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]

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.