How to read data from a mysql database in Java?

Hi,
I use the following code to read data from a database:

    public List<HashMap<Integer, String>> getFullCheckList() throws ClassNotFoundException, SQLException{
        Class.forName("com.mysql.jdbc.Driver") ;
        Connection conn = DriverManager.getConnection("jdbc:mysql://servername/database", "database", "pwd") ;
        Statement stmt = conn.createStatement() ;
        String querycheckName = "SELECT `name` and `id` FROM `check`;" ;
        ResultSet rs = stmt.executeQuery(querycheckName) ;
        ArrayList<HashMap<Integer, String>> checkList = new ArrayList<HashMap<Integer, String>>();
        while(rs.next()) {
            System.out.println("rs:" + rs);
            HashMap<Integer, String> check = new HashMap<Integer, String>();
            System.out.println("rs.getInt(1): " + rs.getInt(1));
            System.out.println("rs.getString(1): " + rs.getString(1));
            check.put(rs.getInt(1), rs.getString(1));
            System.out.println("check:" + rs);
            checkList.add(check);
        }
        rs.close();
        stmt.close();
        conn.close();
        System.out.println("checkList:" + checkList);
        return checkList;
    }  

Open in new window


However, with this code when I print checkList in line 17, I only see the following:
checkList:[{0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}]
[{0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}, {0=0}]

Open in new window


When I print rs, check, rs.getInt(1) and  rs.getString(1) in lines 9, 11, 12 and 14, I get this:

rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78
rs.getInt(1): 0
rs.getString(1): 0
check: com.mysql.jdbc.JDBC4ResultSet@7cf1bb78

Open in new window


Why can't I read the "name" column from the "check" table in this database? Any ideas?

Thanks,
TolgarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
String querycheckName = "SELECT `name` and `id` FROM `check`;" ;

Open in new window

makes no sense. Should probably be

String querycheckName = "SELECT `name`, `id` FROM `check`" 

Open in new window


Also you access both columns with the same index (1). Should be 1,2
0
mrcoffee365Commented:
Hi again.

Among other things, you cannot retrieve the same value from the resultset twice.  So getting it to print, then getting it again for your variable won't work.

You need to change the code to look something like this:

        String querycheckName = "SELECT `name`, `id` FROM  check " ;
        ResultSet  rs = stmt.executeQuery(querycheckName) ;
        ArrayList<HashMap<Integer, String>> checkList = new ArrayList<HashMap<Integer, String>>();
        while(rs.next()) {
            HashMap<Integer, String> check = new HashMap<Integer, String>();
            Integer anId = rs.getInt("Id");
            String aName = rs.getString("Name");
            System.out.println("anId : " + anId );
            System.out.println("aName : " + aName );
            check.put(anId , aName );
            checkList.add(check);
        }
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CEHJCommented:
Among other things, you cannot retrieve the same value from the resultset twice.
What makes you say that?
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

CEHJCommented:
??
0
mrcoffee365Commented:
CEHJ -- feel free to try it.  Once a column in a row in the resultset is retrieved with getString (or whatever) then you can't get it again with getString for that row.
0
TolgarAuthor Commented:
@CEHJ: So how should it be then?
0
mrcoffee365Commented:
Tolgar:  if you're interested in fixing your code, try the sample I posted.
0
TolgarAuthor Commented:
It is all set. Thanks!

And I  would appreciate if you can take a look at this post:

http://www.experts-exchange.com/Programming/Languages/Java/Q_28230910.html

Thanks,
0
CEHJCommented:
CEHJ -- feel free to try it.
I just did, with the following minor alteration to some existing code, with no difficulty whatever:

	    while(rs.next()) {
		//System.out.println(rs.getString(1));
		System.out.printf("%s\t%s%n", rs.getString(1), rs.getString(1));
	    }

Open in new window


@CEHJ: So how should it be then?
Well, my answer was quite specific about the (only) problems with your code
0
TolgarAuthor Commented:
ok. I am ending this post. Thanks a lot.

Can you please take a look at this question? I am struggling with it.

http://www.experts-exchange.com/Programming/Languages/Java/Q_28230910.html#a39465367
0
CEHJCommented:
Can you please take a look at this question?
Sorry - i don't do SWT
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.