...

View Full Version : Best Method for Accessing mySQL DB through a Java/jersey REST Resource



flexillu
03-22-2012, 05:22 PM
I've managed to achieve this currently in one method of a class by hacking away. I'd like to seperate the DB connection and other DB work to other classes. I'm new to connection pooling; does the connection need to be in the constructor of the resoruce? Or would that mean a connection to the DB is still being created every time?

So basically how should I change my code to sperate the DB work from the GET method? :D



@GET
@Produces(MediaType.TEXT_XML)
public List<Item> getItemsBrowser(){

DataSource dataSource = null;
java.sql.Connection connection;
java.sql.Statement statement;

List<Item> items = new ArrayList<Item>();

javax.naming.Context initContext;
try {

initContext = new InitialContext();
dataSource = (DataSource)initContext.lookup("java:comp/env/jdbc/fyproj");
} catch (NamingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

ResultSet resultSet = null;

try {
connection = dataSource.getConnection();
statement = connection.createStatement();

String query = "SELECT * FROM ITEMS";

resultSet = statement.executeQuery(query);

// Fetch each row from the result set
while (resultSet.next()) {
String a = resultSet.getString("itemID");

String b = resultSet.getString("itemDescription");

//Assuming you have a user object
Item item = new Item(a, b);

items.add(item);
}


} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return items;
}

Thanks, any advice is appreciated



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum