Java - Getting Data from MySQL database

Question

I've connected to a MySQL database, which contains four fields (the first of which being an ID, the latter ones each containing varchar strings).

I am trying to get the last row of the database and retrieve the contents of the fields so that I can set them to variables (an int and three strings) and use them later.

So far, I have the bare minimum to make the connection, where do I go from here? As you can see I have tried to write a SQL statement to get the last row but it's all gone wrong from there and I don't know how to split it into the separate fields.

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/t", "", "");

Statement st = con.createStatement();
String sql = ("SELECT * FROM posts ORDER BY id DESC LIMIT 1;");
st.getResultSet().getRow();
con.close();

Answer 1

Here you go :

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/t", "", "");

Statement st = con.createStatement();
String sql = ("SELECT * FROM posts ORDER BY id DESC LIMIT 1;");
ResultSet rs = st.executeQuery(sql);
if(rs.next()) { 
 int id = rs.getInt("first_column_name"); 
 String str1 = rs.getString("second_column_name");
}

con.close();

In rs.getInt or rs.getString you can pass column_id starting from 1, but i prefer to pass column_name as its more informative as you don't have to look at database table for which index is what column.

UPDATE : rs.next

boolean next() throws SQLException

Moves the cursor froward one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

When a call to the next method returns false, the cursor is positioned after the last row. Any invocation of a ResultSet method which requires a current row will result in a SQLException being thrown. If the result set type is TYPE_FORWARD_ONLY, it is vendor specified whether their JDBC driver implementation will return false or throw an SQLException on a subsequent call to next.

If an input stream is open for the current row, a call to the method next will implicitly close it. A ResultSet object's warning chain is cleared when a new row is read.

Returns: true if the new current row is valid; false if there are no more rows Throws: SQLException - if a database access error occurs or this method is called on a closed result set

reference

Answer 2

Something like this would do:

public static void main(String[] args) {

    Connection con = null;
    Statement st = null;
    ResultSet rs = null;

    String url = "jdbc:mysql://localhost/t";
    String user = "";
    String password = "";

    try {
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection(url, user, password);
        st = con.createStatement();
        rs = st.executeQuery("SELECT * FROM posts ORDER BY id DESC LIMIT 1;");

        if (rs.next()) {//get first result
            System.out.println(rs.getString(1));//coloumn 1
        }

    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(Version.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage(), ex);

    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (con != null) {
                con.close();
            }

        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Version.class.getName());
            lgr.log(Level.WARNING, ex.getMessage(), ex);
        }
    }
}

you can iterate over the results with a while like this:

while(rs.next())
{
System.out.println(rs.getString("Colomn_Name"));//or getString(1) for coloumn 1 etc
}

There are many other great tutorial out there like these to list a few:

• http://www.vogella.com/articles/MySQLJava/article.html
• http://www.java-samples.com/showtutorial.php?tutorialid=9

As for your use of Class.forName("com.mysql.jdbc.Driver").newInstance(); see JDBC connection- Class.forName vs Class.forName().newInstance? which shows how you can just use Class.forName("com.mysql.jdbc.Driver") as its not necessary to initiate it yourself

References:

• http://zetcode.com/databases/mysqljavatutorial/