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

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

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

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);


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

return items;

Thanks, any advice is appreciated