JDBC Connectivity to Various Databases and SQL Query Syntax

By -
This writeup gives us an idea of the jar files and the drivers required to connect to various databases. We have chosen six databases for this purpose and they are MySQL,Oracle,MS SQL Server-2000,MS SQL Server-2005,Sybase and Apache Derby.

We need to have suitable jar files to establish connections to corresponding databases. No need to set these jar files in Environment Variables or in any classpath. We will load the jar files dynamically.Here is the java code to establish connection to various DBs.

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.lang.reflect.Method;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class JDBCConnectivityAndSQLQuerySyntax {
 private static String driver = null;
 private static String url = null;
 private static String userId = null;
 private static String pwd = null;
 String queryString = "select count(*) from ads_user;";


 public static void main(String[] args) {
  testConnectivityAndQuerySyntax();
 }

 public static Connection getConnection(String driver,String url,
   String userId,String pwd) {
  Connection conn = null;
  try {
   Class.forName(driver);
   conn = DriverManager.getConnection(url,userId,pwd);
  } catch (Exception ex) {
   ex.printStackTrace();
  }
  return conn;
 }

 void  loadJar(String jarFilePath) throws IOException {
  try {
   URLClassLoader cl = (URLClassLoader) getClass().getClassLoader();
   URL url = new URL("file", null,jarFilePath);
   Class c = URLClassLoader.class;
   Method method = c.getDeclaredMethod("addURL", URL.class);
   method.setAccessible(true);
   method.invoke(cl, url);
  } catch (Exception ex) {
   ex.printStackTrace();
  }
 }

 public static void closeResources(Statement stm, ResultSet rs, Connection conn) {
  try {
   if(rs != null){
    rs.close();
   }
   if(stm != null){
    stm.close();
   }
   if (conn != null) {
    conn.close();
   }
  } catch(Exception ex){
   rs = null;
   stm = null;
   conn = null;
  }
 }

 public void connectToMySQL() {
  Connection conn = null;
  Statement stm = null;
  ResultSet rs = null;

  try {
   driver = "com.mysql.jdbc.Driver";
   url = "jdbc:mysql://localhost:3306/mysql_db";
   userId = "mysql_db";
   pwd = "mysql_db";

   loadJar("C:/jdbc_jars/mysql/mysql-connector-java-5.0.3-bin.jar");
   conn = getConnection(driver, url, userId, pwd);
   stm = conn.createStatement(); 
   rs = stm.executeQuery(queryString);

   while(rs.next()) {
    System.out.println(getDBNameAndVersion(conn)+" ###  Total count: "+rs.getString(1));
   }
  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   closeResources(stm, rs, conn);
  }
 }

 public void connectToOracle() {
  Connection conn = null;
  Statement stm = null;
  ResultSet rs = null;

  try {
   driver = "oracle.jdbc.driver.OracleDriver";
   url = "jdbc:oracle:thin:@192.168.2.90:1521:orcldb";
   userId = "orcl_r1";
   pwd = "orcl_r1";

   loadJar("C:/jdbc_jars/oracle/ojdbc14.jar");
   conn = getConnection(driver, url, userId, pwd);
   stm = conn.createStatement(); 
   rs = stm.executeQuery(queryString);

   while(rs.next()) {
    System.out.println(getDBNameAndVersion(conn)+" ###  Total count: "+rs.getString(1));
   }
  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   closeResources(stm, rs, conn);
  }
 }

 public void connectToSybase() {
  Connection conn = null;
  Statement stm = null;
  ResultSet rs = null;

  try {
   driver = "com.sybase.jdbc2.jdbc.SybDriver";
   url = "jdbc:sybase:Tds:192.168.2.91:5000/sybase_db";
   userId = "sybaseuser";
   pwd = "sybaseuser";

   loadJar("C:/jdbc_jars/sybase/jconn2.jar");
   conn = getConnection(driver, url, userId, pwd);
   stm = conn.createStatement(); 
   rs = stm.executeQuery(queryString);

   while(rs.next()) {
    System.out.println(getDBNameAndVersion(conn)+" ###  Total count: "+rs.getString(1));
   }
  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   closeResources(stm, rs, conn);
  }
 }

 public void connectToSqlServer2000() {
  Connection conn = null;
  Statement stm = null;
  ResultSet rs = null;

  try {

   driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
   url = "jdbc:sqlserver://192.168.2.92:1433;databasename=sqlserverdb_2000;selectMethod=cursor";
   userId = "sqlserverdb";
   pwd = "sqlserverdb"; 

   loadJar("C:/jdbc_jars/mssql2000/msbase.jar");
   loadJar("C:/jdbc_jars/mssql2000/mssqlserver.jar");
   loadJar("C:/jdbc_jars/mssql2000/msutil.jar");

   conn = getConnection(driver, url, userId, pwd);
   stm = conn.createStatement(); 
   rs = stm.executeQuery(queryString);

   while(rs.next()) {
    System.out.println(getDBNameAndVersion(conn)+" ###  Total count: "+rs.getString(1));
   }
  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   closeResources(stm, rs, conn);
  }
 }

 public void connectToSqlServer2005() {
  Connection conn = null;
  Statement stm = null;
  ResultSet rs = null;

  try {

   driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
   url = "jdbc:sqlserver://192.168.2.93:1433;databasename=sqlserverdb_2005;";
   userId = "sa";
   pwd = "sa1"; 

   loadJar("C:/jdbc_jars/sqlserver2005/sqljdbc.jar");
   conn = getConnection(driver, url, userId, pwd);
   stm = conn.createStatement(); 
   rs = stm.executeQuery(queryString);

   while(rs.next()) {
    System.out.println(getDBNameAndVersion(conn)+" ###  Total count: "+rs.getString(1));
   }

  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   closeResources(stm, rs, conn);
  }
 }
 
 public void connectToDerby() {
  Connection conn = null;
  Statement stm = null;
  ResultSet rs = null;

  try {
   driver = "org.apache.derby.jdbc.EmbeddedDriver";
   url = "jdbc:derby:/DerbyDB/SampleDerbyDB";
   userId = "derbyuser";
   pwd = "derbyuser";

   loadJar("C:/jdbc_jars/derby/derby.jar");
   
   conn = getConnection(driver, url, userId, pwd);
   stm = conn.createStatement(); 
   rs = stm.executeQuery(queryString);

   while(rs.next()) {
    System.out.println(getDBNameAndVersion(conn)+" ###  Total count: "+rs.getString(1));
   }
  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   closeResources(stm, rs, conn);
  }
 }

 public String getDBNameAndVersion(Connection conn) {
  String str = null;
  try {
   DatabaseMetaData metaData = conn.getMetaData();
   str = (metaData.getDatabaseProductName()+" "+metaData.getDatabaseProductVersion());
  } catch (Exception ex) {
   ex.printStackTrace();
  }
  return str;
 }

 public static void testConnectivityAndQuerySyntax(){
  BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(System.in));

  System.out.println("\nPlease select the type of database you want to use\n");
  System.out.println(" -> 1- MySQL");
  System.out.println("    2- Oracle");
  System.out.println("    3- Sybase");
  System.out.println("    4- SQLServer-2000");
  System.out.println("    5- SQLServer-2005");
  System.out.println("    6- Apache Derby");
  System.out.println("    7- Select All");

  System.out.println("\nEnter the number of your choice or press  to accept the default:");
  try {
   String inputString = bufferedReader.readLine();
   int input = 1;
   if(inputString != null && !inputString.equals("")) {
    input = Integer.parseInt(inputString);
   }    

   JDBCConnectivityAndSQLQuerySyntax connectivityAndSyntax = new JDBCConnectivityAndSQLQuerySyntax();
   switch(input){
   case 1:
    connectivityAndSyntax.connectToMySQL();
    break;
   case 2:
    connectivityAndSyntax.connectToOracle();
    break;
   case 3:
    connectivityAndSyntax.connectToSybase();
    break;
   case 4:
    connectivityAndSyntax.connectToSqlServer2000();
    break;
   case 5:
    connectivityAndSyntax.connectToSqlServer2005();
    break;
   case 6:
    connectivityAndSyntax.connectToDerby();
    break;
   case 7:
    connectivityAndSyntax.connectToMySQL();
    connectivityAndSyntax.connectToOracle();
    connectivityAndSyntax.connectToSybase();
    connectivityAndSyntax.connectToSqlServer2000();
    connectivityAndSyntax.connectToSqlServer2005();
    connectivityAndSyntax.connectToDerby();
    break;
   default:
    System.out.println("Invalid Input");
   }

   System.out.println("\nRepeat the process again <y/n>: ");
   BufferedReader _bufferedReader = new BufferedReader(new InputStreamReader(System.in));

   String readLine = _bufferedReader.readLine();
   if("Y".equalsIgnoreCase(readLine)){
    testConnectivityAndQuerySyntax();
   } else {
    if(!readLine.equalsIgnoreCase("N")) {
     System.out.println("Invalid Input.");
    } 
   }
  } catch (Exception ex) {
   System.out.println("Invalid Input.");
  }
 }
}

The output window appears to be like this:




What happens if a semicolon is placed at end of the query?
i.e. if queryString variable is

String queryString = "select count(*) from ads_user;";

Semicolon at the end of the Query throws an error in Oracle,Sybase and Derby DBs but works fine with MySQL and SQL Server Databases.The following errors will be in thrown,


Oracle:

java.sql.SQLException: ORA-00911: invalid character
 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
 at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
 at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
 at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
 at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:850)
 at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2599)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
 at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:653)

Sybase:

com.sybase.jdbc2.jdbc.SybSQLException: Incorrect syntax near ';'.
 at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2636)
 at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1996)
 at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
 at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:204)
 at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:187)
 at com.sybase.jdbc2.jdbc.SybStatement.queryLoop(SybStatement.java:1510)
 at com.sybase.jdbc2.jdbc.SybStatement.executeQuery(SybStatement.java:1495)
 at com.sybase.jdbc2.jdbc.SybStatement.executeQuery(SybStatement.java:402)

Apache Derby:

java.sql.SQLException: Syntax error: Encountered ";" at line 1, column 30.
 at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
 at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
 at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
 at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
 at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
 at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
 at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
 at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
Base Line
While using a query in java code it is not recommended to use a semicolon at the end of the query.

Comments

Post a Comment

Popular posts from this blog

Creating Custom Code Panels using InstallAnywhere

By -
Image
In this write-up lets look at the way to create a Custom Choose File Panel using InstallAnywhere(IA) tool. IA is a multi-platform software tool for creating customized installers.There are many documents available on IA that gives us a clear picture of IA and its features. This write-up assumes that the reader of this blog is aware of the basic features available in IA and has a licensed InstallAnywhere enterprise Edition and not InstallAnywhere standard edition.In this blog I am adding a small piece of code that tells us how to develop custom panels. In the Advanced Designer we can add a file chooser by using "Panel: Choose File" action. The code below produces the same output as IA's Choose File action minus 'Restore Default File'. To write a custom code panel ,it is necessary to write a class that extends CustomCodePanel class and override a couple of methods present in it. Here is the custom code that demonstrates a custom choose file panel. packag...
Read more

Get JBoss Version using Java Code

By -
The following are the things we will be looking into now. a) How to get the JBOSS version from JBOSS zip file using java code? b) How to get the JBoss version from JBoss folder using java code? c) How to get the manifest details of a jar file that is present inside a Zip file? The actual version of JBoss can be determined by using the run.jar file present inside the bin folder of JBoss. It is not that difficult to get the manifest details of any jar file present either inside a folder or in a zip file[If you think it is difficult, don't worry,very soon we will make it easy for you] We are here trying to get the manifest details of the run.jar file, which answers all the questions we have posted above. The first method "getJbossVersion(String sourcefile)" in the code snippet below not only tells us how to get the Version of JBoss, but also tells us how to get the contents of MANIFEST.MF file that is present inside a jar file which in turn is again present insi...
Read more

Server Migration from JBoss-4.0.5 to JBoss-4.2.3.GA

By -
As the subject line says, this write-up covers a small note on JBoss Server migration. Hopefully it may not be a tough task migrating from JBoss-4.0.5 to JBoss-4.2.3 server, but I rather had a different experience while doing this. The reason behind this was the pre-compilation of the JSPs * in my project, which was performed using the jar files present in jbossweb-tomcat55.sar folder. * If I say compile/compiling in this write-up, please read it as ‘pre-compiling JSPs’ I was supposed to do the same compiling as it was done earlier when we used JBoss-4.0.5. The jar files used to compile are jasper-compiler.jar, jasper-runtime.jar, commons-el.jar and as I said earlier these files resided in the jbossweb-tomcat55.sar folder. Now where can I find jbossweb-tomcat55.sar folder in JBoss-4.2.3.? After some time, with the help of google, I was able to find out that this directory is been replaced by a new Tomcat servlet engine which is been named as jboss-web.deployer . This r...
Read more