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

score 16 · Accepted Answer · edited Jul 18 '13 at 07:50

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

score 3 · Answer 2 · edited May 23 '17 at 12:06

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:

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/

score 1 · Answer 3 · answered Aug 20 '12 at 16:26

1

This should work, I think...

ResultSet results = st.executeQuery(sql);

if(results.next()) { //there is a row
 int id = results.getInt(1); //ID if its 1st column
 String str1 = results.getString(2);
 ...
}

answered Aug 20 '12 at 16:26

nullpotent

9,162
1
31
42

Why does results.next() let us kknow that there is a row? – mino Aug 20 '12 at 16:56
1

It moves to the next row available (initially it's before the first row), if there's none it returns false, so it fails; otherwise it returns true and it continues. – nullpotent Aug 20 '12 at 17:01
1

also have a [look](http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#next%28%29) – Harmeet Singh Aug 20 '12 at 17:03

score 0 · Answer 4 · answered Oct 18 '13 at 16:53

Easy Java method to get data from MySQL table:

/*
 * CREDIT : WWW.CODENIRVANA.IN
*/

String Data(String query){
    String get=null;
    try{

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = (Connection)DriverManager.getConnection
("jdbc:mysql://localhost:3306/mysql","root","password");
Statement stmt = (Statement) con.createStatement();
ResultSet rs=stmt.executeQuery(query);
if (rs.next())
       {
get = rs.getString("");
        }

}
catch(Exception e){
JOptionPane.showMessageDialog (this, e.getMessage());
}
    return get;
}

score 0 · Answer 5 · answered Nov 25 '20 at 06:54

Here is what I just did right now:

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.sun.javafx.runtime.VersionInfo;  

public class ConnectToMySql {
public static ConnectBean dataBean = new ConnectBean();

public static void main(String args[]) {
    getData();
    }



public static void getData () {

    try {
        Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mynewpage", 
 "root", "root");
        
 // here mynewpage is database name, root is username and password
    
Statement stmt = con.createStatement();
        System.out.println("stmt  " + stmt);
        ResultSet rs = stmt.executeQuery("select * from carsData");
        System.out.println("rs  " + rs);
        int count = 1;
        while (rs.next()) {
            String vehicleType = rs.getString("VHCL_TYPE");
            System.out.println(count  +": " + vehicleType);
            count++;

        }

        con.close();
    } catch (Exception e) {
        Logger lgr = Logger.getLogger(VersionInfo.class.getName());
        lgr.log(Level.SEVERE, e.getMessage(), e);

        System.out.println(e.getMessage());
    }
    
    
}

}

The Above code will get you the first column of the table you have.

This is the table which you might need to create in your MySQL database

CREATE TABLE
carsData
(
    VHCL_TYPE CHARACTER(10) NOT NULL,
);

score 0 · Answer 6 · answered Nov 25 '20 at 07:10

First, Download MySQL connector jar file, This is the latest jar file as of today [mysql-connector-java-8.0.21].
Add the Jar file to your workspace [build path].
Then Create a new Connection object from the DriverManager class, so you could use this Connection object to execute queries.
Define the database name, userName, and Password for your connection.
Use the resultSet to get the data based one the column name from your database table.

Sample code is here:

public class JdbcMySQLExample{

public static void main(String[] args) {

    String url = "jdbc:mysql://localhost:3306/YOUR_DB_NAME?useSSL=false";
    String user = "root";
    String password = "root";
    
    String query = "SELECT * from YOUR_TABLE_NAME";

    try (Connection con = DriverManager.getConnection(url, user, password);
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(query)) {

        if (rs.next()) {
            
            System.out.println(rs.getString(1));
        }

    } catch (SQLException ex) {
          System.out.println(ex);
       
    } 
}

Java - Getting Data from MySQL database

6 Answers6

Linked