CODEWITHSUNDEEP
javamysqldatabasejdbc
nikhil
nikhil

Reputation: 895

Executing a Mysql query without selecting database in java

I want to execute a MySQL query without selecting a database in Java. I am able to do so directly in the MySQL console, like:

CREATE TEMPORARY TABLE  temptable
As(select REPLACE(a.keywordSupplied,"www.","") as keywordSupplied
from db1.table1 
;

I am able to execute the above query directly after logging into the MySQL console.I don't make use of the use statement here

use databasename;

I need to do this as I will be taking data from two different databases in a single query. According to http://www.tutorialspoint.com/jdbc/jdbc-create-database.htm I gave DB URL as jdbc:mysql://localhost/

but it throws an Exception

java.sql.SQLException: No database selected.

Screenshot for the same in mysql console enter image description here

Any solutions to make it work?

Upvotes: 4

Views: 3274

Answers (4)

Niclet
Niclet

Reputation: 41

It seems that when you do "select * from test.demo;", it actually selects 'test' database and does the query in the 'demo' table.

Upvotes: 0

Manoj Sharma
Manoj Sharma

Reputation: 616

Try this code:

static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
static final String DB_URL = "jdbc:mysql://localhost:3306/";
static final String USER = "root";
static final String PASS = "";
Class.forName(JDBC_DRIVER);
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println(conn);
conn.setCatalog("mysql");
PreparedStatement stmt1 = conn.prepareStatement("CREATE TEMPORARY TABLE  temptable (select test.a.id as id, students.a.id as sid from test.a join students.a on test.a.id = students.a.id)");
stmt1.execute();
stmt1 = conn.prepareStatement("select * from temptable");
ResultSet set1 = stmt1.executeQuery();
while(set1.next()){
    System.out.println(set1.getString(1));  
}

Now you have the connection with your database server execute your query. I hope your query will be execute without any problem.

Upvotes: 0

RandomSeed
RandomSeed

Reputation: 29809

In the tutorial you are following, they issue a CREATE DATABASE statement. This statement is one of the very few statements that you can issue outside of the context of a database (the only other one I can think about is CREATE USER).

Even a TEMPORARY table must be attached to a database.

You must choose an arbitrary database for this operation. You probably do not care which one in particular (just make sure you have write permissions on the one you select :)

If you really do not want to provide a database at connection time, you can specify the target database in the SQL statement: CREATE TEMPORARY TABLE [database].temptable.

Upvotes: 1

SMA
SMA

Reputation: 37103

Instead of

DriverManager.getConnection("jdbc:mysql://localhost/", USER, PASS);

Use:

DriverManager.getConnection("jdbc:mysql://localhost/db1", USER, PASS);//specify the db within URL itself.btw dont you have port in url?

Upvotes: 1

Related Questions