Geek Answers Handbook

SQL result set in Java

Hello, I just started with SQL in Java (in fact, I also recently started working with Java). and I created a class to connect to the MySQL database, and it all worked well.

Now I have a question for getting the result.

in PHP I would do something like

While($row = mysql_fetch_assoc()) { echo $row['rowname']; }

In Java, I tried to create something similar to this, but I don’t know if it will go the right way or what it should be completely different or something else .. this is what I did (see getResultList method)

 public class MysqlConnect{ private String query; private ResultSet rs; public void connectToAndQueryDatabase(String database, String username, String password) throws SQLException { Connection con = null; try { con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/" + database, username, password); } catch (SQLException e) { e.printStackTrace(); } Statement stmt = con.createStatement(); rs = stmt.executeQuery(query); } public void setQuery(String query) { this.query = query; } public List getResultList() { ArrayList<HashMap> row = new ArrayList<HashMap>(); while(row = rs.next()) { } return rs; } }
+4
source share
8 answers

ResultSetMetaData is an interface that provides all the information about the ResultSet, like all names, types, length, etc. generated by ResultSet.

Just use this interface. For more information and API documentation, simply visit the Oracle website.

+3
source

Try entering the code below. Change the request, url, user, password with the desired request and credentials.

 public static void main(String args[]) { String url = "database url"; Connection con; String query = "Your select query here"; Statement stmt; try { Class.forName("Full driver class name"); } catch(java.lang.ClassNotFoundException e) { System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage()); } try { con = DriverManager.getConnection(url, "user", "password"); stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); ResultSetMetaData rsmd = rs.getMetaData(); int numberOfColumns = rsmd.getColumnCount(); int rowCount = 1; while (rs.next()) { System.out.println("Row " + rowCount + ": "); for (int i = 1; i <= numberOfColumns; i++) { System.out.print(" Column " + i + ": "); System.out.println(rs.getString(i)); } System.out.println(""); rowCount++; } stmt.close(); con.close(); } catch(SQLException ex) { System.err.print("SQLException: "); System.err.println(ex.getMessage()); } }

For documentation and api visit the oracle site. A.

Documentation and api from ResultSetMetaData

+3
source

in your while loop you used the assignment operator. rs.next () returns true or false

  while(row=rs.next()) {}

you can just say:

 while(rs.next()) {}
+2
source

it is very easy to do in java, you are nice to this code, which you can find, and see an example in this link

http://www.roseindia.net/answers/viewqa/Java-Beginners/3488-mysql-with-jsp.html

+2
source 
 public void connectToAndQueryDatabase(String username, String password) { Connection con = DriverManager.getConnection( "jdbc:myDriver:myDatabase", username, password); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1"); while (rs.next()) { int x = rs.getInt("a"); String s = rs.getString("b"); float f = rs.getFloat("c"); } }

The best place to understand the use of JDBC is through the JDBC tutorial on the Oracle website.

To understand how to extract values ​​from a result set, review this part of the tutorial.

A ResultSet object is a data table representing a set of database results, which is usually generated by executing an instruction that queries the database.

You access the data in the ResultSet with the cursor. Please note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in a ResultSet. Initially, the cursor is positioned before the first line. The ResultSet.next method moves the cursor to the next line. This method returns false if the cursor is after the last line. This method repeatedly calls the ResultSet.next Method with a while loop to iterate over all the data in the ResultSet.

The ResultSet interface declares getter methods (for example, getBoolean and getLong) to retrieve column values ​​from the current row. You can get values ​​using either the index number of the column or the alias or column name. Usually a column index is more efficient. The columns are numbered from 1. For maximum portability, the columns of the result set in each row should be read from left to right order, and each column should be read only once.

+2
source

You have many methods in ResultSet for working with it.

If you need to go through it, just use

 rs.next();

If you need to get a column by its alias:

 rs.getString("alias"); rs.getFloat("other_alias"); // or by column index rs.getString(3);

You also have a utility class for retrieving information about a result set, such as the number of columns and their names:

 rs.getMetaData();

Make sure you close the connection after you are done with it.

+2
source

Passed by the result method below and it will return the desired result you are looking for.

 public List<Map<String, Object>> getResultsList(ResultSet rs) throws SQLException { ResultSetMetaData metadata = rs.getMetaData(); int columns = metadata.getColumnCount(); List<Map<String, Object>> results = new ArrayList<Map<String, Object>>(); while (rs.next()) { Map<String, Object> row = new HashMap<String, Object>(columns); for (int i = 1; i <= columns; ++i) { row.put(metadata.getColumnName(i), rs.getObject(i)); } results.add(row); } return results; }
+2
source

To work with the Java database, the java.sql package is provided. *. You need to read the java documents for this API and study their use. You can apply the best logic and trick based on your business requirements only when you know about using the java.sql package. *.

I suggest that you first go through the oracle documents and then come into the community if you are stuck somewhere.

Go to the ResultSetMetaData interface. I assume this interface answers your question.

http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/ResultSetMetaData.html

+2
source

More articles:


All Articles