Java database application

Question

Can someone help me with this: I'm making a java database application and I want to put my methods for select,insert,update and delete into separated class so they can be called from another classes and reused. Till now I managed to separate only methods for update and delete and for insert when not using prepared statement. Problem I'm encountering is how to return data's when doing select from database and put them into table.

Here are my update and delete method's in Queries class:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.Konekcija.Konekcija;

public class Queries {
Konekcija konekcija = new Konekcija();

public void updateTable(String sqlQuery){
    Connection conn = null;
    Statement st = null;
    try{
        Class.forName("com.mysql.jdbc.Driver");
        conn = konekcija.getConn();
        st = conn.createStatement();
        st.executeUpdate(sqlQuery);

    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

public void deleteFromTable(String sqlQuery){
    Connection conn = null;
    Statement st = null;
    try{
        Class.forName("com.mysql.jdbc.Driver");
        conn = konekcija.getConn();
        st = conn.createStatement();
        st.executeUpdate(sqlQuery);
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

}

P.S. Connection properties are in another class "Konekcija"

Nathan Hughes · Accepted Answer · 2012-07-23 21:31:38Z

1

You should create a collection and populate it with the results of the query, it should look something like:

List<Foo> selectFoos(Connection connection) throws SQLException {
    PreparedStatement ps = connection.prepareStatement("select * from foo");
    try {
        ResultSet resultSet = ps.executeQuery();
        try {
            List<Foo> foos = new ArrayList<Foo>();
            while (resultSet.next()) {
                Foo foo = new Foo();
                // use resultSet methods get... to retrieve data from current row of results
                // and populate foo
                foos.add(foo);
            }
        } finally {
            resultSet.close();
        }
    } finally {
        ps.close();
    }
    return foos;
}

answered Jul 23, 2012 at 21:31

Nathan Hughes

96.7k21 gold badges192 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ViROscar · Accepted Answer · 2012-07-23 21:24:02Z

0

try executeQuery method. in the java doc for "resultset" class you will find a example:

http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSet.html

answered Jul 23, 2012 at 21:24

ViROscar

1481 silver badge12 bronze badges

Comments

kosa · Accepted Answer · 2012-07-23 21:30:32Z

0

Return data for "select from table" would be ResultSet.

You may return the ResultSet to caller and get values (or)
Inside the "Select" method of Queries class retrieve the data from resultset and set it some VO object and add this VO to collection and return the collection (assuming you will get more than one row in ResultSet). For example if you are querying User table, create Java bean class "User" with get/set methods. Set retrieved values to this bean and return it. //Create User class with get/set in some package.
```
Class.forName("com.mysql.jdbc.Driver");
    conn = konekcija.getConn();
    st = conn.createStatement();
    ResultSet rs=st.execute(sqlQuery);
    //Instantiate user class
       while (rs.next())
    System.out.println("Name= " + rs.getString("moviename") + " Date= " +               String fName = rs.getString("firstName");
```
User myUser = new User(); myUser.setFirstName(fName); }

NOTE: This code is hand typed. There may be syntax errors. Please use it as starting point.

edited Jul 23, 2012 at 21:30

answered Jul 23, 2012 at 21:19

kosa

66.7k15 gold badges134 silver badges170 bronze badges

2 Comments

Branislav Lazic Over a year ago

I was thinking about that also, but I don't know to do it.

kosa Over a year ago

@brano88: Updated my answer with example.

Collectives™ on Stack Overflow

Java database application

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related