Java - Getting Data from MySQL database

user_835084 0
Viewed 109970

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();
jsp mysql sql database java
6 Answers

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

  • 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);
       
    } 
}
Admin1
Related