Hello I just started with SQL in Java ( actually also started not long ago with Java also.. )and I created a class to get connected with a MySQL database and it all worked well.
Now I have a question for the 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 im going the right way or that it should be much different or whatever.. here is what i've made ( 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;}}Best Answer
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 how to use JDBC is the JDBC tutorial from Oracle website.
For understanding how to retrieve values from a resultset look at this part of the tutorial.
A ResultSet object is a table of data representing a database result set, which is usually generated by executing a statement that queries the database.
You access the data in a ResultSet object through a cursor. Note thatthis cursor is not a database cursor. This cursor is a pointer thatpoints to one row of data in the ResultSet. Initially, the cursor ispositioned before the first row. The method ResultSet.next moves thecursor to the next row. This method returns false if the cursor ispositioned after the last row. This method repeatedly calls theResultSet.next method with a while loop to iterate through all thedata in the ResultSet.
The ResultSet interface declares getter methods (for example,getBoolean and getLong) for retrieving column values from the currentrow. You can retrieve values using either the index number of thecolumn or the alias or name of the column. The column index is usuallymore efficient. Columns are numbered from 1. For maximum portability,result set columns within each row should be read in left-to-rightorder, and each column should be read only once.
Try below code. Change query, url, user, password with desired query 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 please visit below oracle site.
Documentation and api of ResultSetMetaData
ResultSetMetaData is an interface which provides all the information about the ResultSet like all the Column names, types, length etc of generated ResultSet.
Just use that interface. For more information and documentation about API just visit Oracle website.
You have a lot of methods in the ResultSet for dealing with it.
If you need to to loop 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 indexrs.getString(3);You also have an utility class for extracting information about the result set like the number of columns and their names:
rs.getMetaData();Make sure you close your connection after you're done with it.
Just past your result set to below method and it will return 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;}in your while loop you have used assignment operator. rs.next() returns true or false
while(row=rs.next()) {}you can simply say,
while(rs.next()) {}to deal with Database Java provides java.sql.* package. You need to read the java docs for this API and and learn their usages. You can apply best logic and trick based on your business requirement only when you know the usages of java.sql.* package.
I suggest you to go through the oracle docs first then come to community if you stuck somewhere.
Go through the ResultSetMetaData Interface. I guess this interface is answer to your question.
http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/ResultSetMetaData.html