JDBC MySQl don't show result sets

Question

In my code, My finally output, is not like in MySQl console output:

public class d3 {

Connection con;
String dbName = "mydb";
String dbUsername = "root";
String dbPassword = "2323";
String dbUrl = "jdbc:mysql://localhost/";

public d3() {
    try {
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Find database successfuly");
    } catch (Exception e) {
        System.err.println("Unable to find and load driver");
        System.exit(1);
    }
}

public void connectToDB() {
    try {
        con = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
        System.out.println("Connect to database succesfully");
    } catch (SQLException e) {
        System.out.println("Can not connect to database");
        System.exit(1);
    }
}

public void excuteSQL() {
    String sqlString1 = "use mydb";
    String sqlString2 = "show tables";
    String result;
    try {
        Statement st1 = con.createStatement();

        ResultSet result1 = st1.executeQuery(sqlString1);
         while(result1.next()){
         result = result1.getString(dbName);
         System.out.println(result1);
        }
    } catch (SQLException sqle) {
        System.out.println("Can not excute sql statement");
    }
}

public static void main(String[] args) {
    d3 ddd = new d3();
    ddd.connectToDB();
    ddd.excuteSQL();
}
}

My output:

Find database successfuly
Connect to database succesfully
Result set representing update count of 0

But, I want to be like this:

Find database successfuly
Connect to database succesfully
Database changed      // this is shown in console

I am going step by step and comparing outputs from mysql console and my IDE console, For example, I want to see "Database changed" message after execute "use mydb" statement.

Are you sure "show tables" is a statement that your JDBC driver understands and returns a resultset for? — DaveH, Jun 21 '13 at 16:33
Try using [`execute()`](http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String)). It returns `boolean true` if the querie get executed. I think `"use mydb"` never returns `ResultSet`. — Smit, Jun 21 '13 at 16:35
possible duplicate of [how to get list of Databases "Schema" names of MySql using java JDBC](http://stackoverflow.com/questions/5679259/how-to-get-list-of-databases-schema-names-of-mysql-using-java-jdbc) — Luiggi Mendoza, Jun 21 '13 at 16:40

Karki64 · Answer 1 · 2013-06-21T16:47:00.400

1

you should do:

while (resultset.next()) {

System.out.println(resultset.getString("somevariable")
}

Some variable just represents what you are looking for in your table. Also, I'm not sure that "use mydb" works with JDBC. If you use a command string like

("SELECT NAME FROM TABLE")

then you would have to use something like

System.out.println(resultset.getString("NAME")

to look for stuff in the table column named "NAME".

edited Jun 21 '13 at 16:47

answered Jun 21 '13 at 16:41

Karki64

23
1
6

Can you explain about somevariable – Sajad Jun 21 '13 at 16:42
Refer to the possible duplicate link. This won't do it. – Luiggi Mendoza Jun 21 '13 at 16:43
@LuiggiMendoza are you sure that that is what he means? – Karki64 Jun 21 '13 at 16:48
@karki64 yes, OP wants/needs the tables in a database (schema). That link provide that info based on the metadata of the connection. – Luiggi Mendoza Jun 21 '13 at 16:49

score 1 · Accepted Answer · answered Jun 22 '13 at 07:17

You are executing use mydb, which doesn't produce a result set (it isn't a query). Also, you should not use this command to set your current database, JDBC requires you to use the JDBC methods. See the javadoc of java.sql.Connection:

Note: When configuring a Connection, JDBC applications should use the appropriate Connection method such as setAutoCommit or setTransactionIsolation. Applications should not invoke SQL commands directly to change the connection's configuration when there is a JDBC method available.

The equivalent of USE mydb is Connection.setCatalog("mydb"), calling this method makes sure the driver is in a consistent state. If you look at the actual implementation in MySQL Connector/J this method eventually calls USE mydb, but it also does additional checks and changes to the state of the Connection object.

When you already know which database you want to use, just include it directly into the connection URL:

String dbUrl = "jdbc:mysql://localhost/mydb";

Similarly, you shouldn't use SHOW TABLES to get information on tables, use DatabaseMetaData.getTables() instead. Using JDBC methods over MySQL specific commands makes your program more portable.

Thanks so much, Is there a place that i see all of this equivalent statements between mysql console and JDBC methods? — Sajad, Jun 22 '13 at 08:55
Is the Connection.setCatalog("mydb") method return any thing? — Sajad, Jun 22 '13 at 10:51

JDBC MySQl don't show result sets

2 Answers2