Java - Retrieving Data from a MySQL Database

Question

I connected to a MySQL database that contains four fields (the first of which is an identifier, the last of which contains varchar strings).

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

I still have a minimum minimum to establish a connection, where do I go from here? As you can see, I tried to write an SQL statement to get the last row, but that wasn’t the case, and I don’t know how to split it into 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 at 1 , but I prefer to pass column_name as more informative, since you don't have to look for the table database for which index is what column .

UPDATE: rs.next

boolean next () throws a SQLException

Moves the froward cursor one line from the current position. The ResultSet cursor is initially positioned before the first line; the first call to next makes the first line the current line; the second call makes the second line the current line, etc.

When the next method call returns false, the cursor is located after the last line. Any call to the ResultSet method that requires the current line to throw a SQLException. If the type of the result set is TYPE_FORWARD_ONLY, it is indicated by the provider whether their implementation of the JDBC driver will return false or throw an SQLException on a subsequent call to the next one.

If the input stream is open for the current line, calling the next method will implicitly close it. The ResultSet object's warning chain is cleared when reading a new line.

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

link

Answer 2

Something like that:

 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 while as follows:

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

There are many other great lessons that are listed below:

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

How to use Class.forName("com.mysql.jdbc.Driver").newInstance(); see JDBC connection- Class.forName vs Class.forName (). newInstance? , which shows how you can simply use Class.forName("com.mysql.jdbc.Driver") since it does not need to be initiated on its own

Literature:

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

Answer 3

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

Answer 4

A simple Java method for retrieving data from a 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; }