Is it possible to connect mysql and sql server to get the tables present in it?

Question

The user should not specify the type whether it is ms sql or my sql? I want to do it in netbeans? If not is there any way?

Thank you

Answer 1

EDIT

I somehow, thought that you wanted to know how to develop an application that can handle multiple databases. So this is a pattern that can help you with that. But to actually query your database and get all the data you want from it, you will need to use the according APIs. (JDBC for your Java application for instance). Read until the end of this answer, I just added an example of how to use JDBC with an actual Sql Server database.

(First part: how to manage your persistence layer)

You should have a multi-layer architecture for your program. One of these layers should be the persistence layer that is going to query/retrieve data from your databases. From now on, I am only going to discuss this persistence layer.

One way to do this is to use Java Interfaces.

public interface DataService
{
   connect();
   close();
   ArrayList<IUser> retrieveUserDatas(..);
   // other expected operations
   .... 
}

And then, have one implementation class of this interface for each database type. So you will have:

class SqlServer implements DataService
{
   // Implementation of your operations using Sql Server drivers
}

class Mysql implements DataService
{ 
   // Implementation of your operations using Mysql drivers
}

And then you could have a manager that will decide which one of the databases you want to use:

class Manager
{
   DataService dataService;
   public Manager(string databaseType)
   {
       if(databaseType.equals("sqlserver"))
       {
          dataService = new SqlServer();
       }

       else if(databaseType.equals("mysql"))
       {
          dataService = new Mysql();
       }
   }
}

So in your upper layers you will be able to create a manager for your persistence layer with the suitable database system:

Manager manager = new Manager("mysql"); // I want to manage my database layer with mysql

Now the attribut dataService from your manager will be an instance of the Mysql class, and you will be able to use the operations of this class. That's just one line of code that you need to change every time you add a new database to your system (+ the class that implements the interface and that uses the specific drivers of your database).

FYI, that's a brige design-pattern!

(Second part: working with JDBC)

For instance here is what the implement class for a Sql Server database can actually look like:

public class SqlServerAccess implements IDataServerAccess {

    static final String url= "jdbc:sqlserver://localhost:1433;databaseName="NAME OF YOUR DATABASE";integratedSecurity=true;";
    Connection connector;


    public void connection() {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
            connector = DriverManager.getConnection(url, "root", "");
        } catch (Exception ex) {
            Logger.getLogger(SqlServerAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
    }


    @Override
    public void close() {
        try {
            if(connector != null){
                connector.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(SqlServerAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

In the connection() method, you can see that I referenced the Sql Server driver: com.microsoft.sqlserver.jdbc.SQLServerDrive

So for each database, you will have a specific driver to download (.jar) and to specify in your implementation class.

Finally, here is what an insert looks like:

public void insertData(Data data) {
        try {
            Statement st = dataServerAccess.connect().createStatement();
            st.execute("INSERT INTO DATA(bla, bla, bla, bla, created_at)"
                                + "VALUES('" + data.bla()
                                + "', '" + ..
                                + "', '" + ..
                                + "', '" + ..
                                + "','"  + getTodayTimestamp() +"')");
        } catch (SQLException ex) {

        } finally {
            dataServerAccess.close();
        }
    }

Hope this explanation will help you building what you want. You may need to google all those keywords (like JDBC, database layer, and you can go further with JPA later, wich is an Object-Relational-Mapper. Anyway, Keywords to check on Google!). For instance here is some documentation to help you use JDBC with mysql.