jdbc

How to connect various databases?

MSSQL:

File: TestMSSQLJDBC.java

import java.sql.*;

public class TestMSSQLJDBC{

        public static void main(String[] args) throws Exception{

                System.out.println("Hello world!");

                String url = "jdbc:sqlserver://<IPADDRESS>\\SQLEXPRESS;databaseName=<DATABASE_NAME>;integratedSecurity=true";

                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

                Connection conn = DriverManager.getConnection(url);

                System.out.println("-----------" + conn);

        }

}

Compile : javac TestMSSQLJDBC.java

Run : java -cp .:mssql-jdbc-6.2.2.jre7.jar TestMSSQLJDBC

MYSQL:

ORACLE:

How to execute DML queries?

How to execute stored procedure?

How to execute DDL and DCL queries?

Error#1:Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The server sqlexpress is not configured to listen with TCP/IP.

Simple java app to test connectivity to mysql server. 

vi JDBCTest.java

import java.sql.*;

public class JDBCTest {

        public static void main(String[] args) {

                Connection con = null;

                try{

                        String URL = "jdbc:mysql://hive-server:3306";

                        Class.forName("com.mysql.cj.jdbc.Driver");

                        con = DriverManager.getConnection(URL, "rohit", "********");

                        Statement stmt = con.createStatement();

                        stmt.execute("SELECT 1;");

                        System.out.println("Hi");

                } catch(Exception ex){

                        ex.printStackTrace();

                } finally {

                        try {

                                if (con != null) {

                                        con.close();

                                }

                        } catch(Exception ex){}

                }

        }
}

javac JDBCTest.java  #Compile

java JDBCTest #Run, may need to set class path pointing to connector jar

Errors--- 

[rohit@hive-server ~]$ java JDBCTest
java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver
        at java.net.URLClassLoader.findClass(URLClassLoader.java:387)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:352)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:264)
        at JDBCTest.main(JDBCTest.java:7)
[rohit@hive-server ~]$ export CLASSPATH=$CLASSPATH:/usr/share/java/mysql-connector-java.jar
[rohit@hive-server ~]$ java JDBCTest
java.sql.SQLException: null,  message from server: "Host 'hive-server' is not allowed to connect to this MySQL server"
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:819)
        at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:440)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:239)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:188)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at JDBCTest.main(JDBCTest.java:8)
[rohit@hive-server ~]$

For above error, need to check user in mysql. should be '<NAME>'@'%'; also grant all permission to this user ..  then try rerun.