Java/Database SQL JDBC/Table

Another Method To Check Table Existance

   <source lang="java">
 

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getMySqlConnection();
   System.out.println("Got Connection.");
   Statement st = conn.createStatement();
   st.executeUpdate("drop table survey;");
   st.executeUpdate("create table survey (id int,name varchar(30));");
   st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
   String tableName = "survey";
   String query = "select count(*) from  " + tableName;
   Statement stmt = null;
   ResultSet rs = null;
   try {
     stmt = conn.createStatement();
     rs = stmt.executeQuery(query);
     System.out.println("Exist");;
   }
   catch (Exception e ) {
     // table does not exist or some other problem
     //e.printStackTrace();
     System.out.println("Not Exist");
   }
   st.close();
   conn.close();
 }
 private static Connection getHSQLConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   System.out.println("Driver Loaded.");
   String url = "jdbc:hsqldb:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }
 public static Connection getMySqlConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/demo2s";
   String username = "oost";
   String password = "oost";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static Connection getOracleConnection() throws Exception {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@localhost:1521:caspian";
   String username = "mp";
   String password = "mp2";
   Class.forName(driver); // load Oracle driver
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }

}

 </source>
   
  
 
  

Copy One Database Table to Another

   <source lang="java">
  

import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {

 public static void main(String[] argv) throws Exception {
   String url = "jdbc:mysql://localhost:3306/";
   String dbName = "jdbcMysql";
   String userName = "root";
   String password = "root";
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   Connection conn = DriverManager.getConnection(url + dbName, userName, password);
   Statement st = conn.createStatement();
   int rows = st.executeUpdate("INSERT INTO myTable SELECT * FROM jdbc4.Copyemployee");
   if (rows == 0) {
     System.out.println("Don"t add any row!");
   } else {
     System.out.println(rows + " row(s)affected.");
     conn.close();
   }
 }

}

 </source>
   
  
 
  

Create data table 2

   <source lang="java">
 

/* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED. Use of this software is authorized pursuant to the terms of the license found at http://developer.java.sun.ru/berkeley_license.html. Copyright 2003 Sun Microsystems, Inc. All Rights Reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: - Redistribution of source code must retain the above copyright notice, this list of conditions and the following disclaimer. - Redistribution in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of Sun Microsystems, Inc. or the names of contributors may be used to endorse or promote products derived from this software without specific prior written permission. This software is provided "AS IS," without a warranty of any kind. ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN") AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. You acknowledge that this software is not designed, licensed or intended for use in the design, construction, operation or maintenance of any nuclear facility.

• /

/*

* Copyright 2003 Sun Microsystems, Inc.  ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/ 

import java.sql.*;

public class CreateCoffees {

 public static void main(String args[]) {
     
   String url = "jdbc:mySubprotocol:myDataSource";
   Connection con;
   String createString;
   createString = "create table COFFEES " +
             "(COF_NAME varchar(32), " +
             "SUP_ID int, " +
             "PRICE float, " +
             "SALES int, " +
             "TOTAL int)";
   Statement stmt;
 
   try {
     Class.forName("myDriver.ClassName");
   } catch(java.lang.ClassNotFoundException e) {
     System.err.print("ClassNotFoundException: "); 
     System.err.println(e.getMessage());
   }
   try {
     con = DriverManager.getConnection(url, 
                  "myLogin", "myPassword");
 
     stmt = con.createStatement();              
            stmt.executeUpdate(createString);
 
     stmt.close();
     con.close();
 
   } catch(SQLException ex) {
     System.err.println("SQLException: " + ex.getMessage());
   }
 }

}

 </source>
   
  
 
  

Create data table 3

   <source lang="java">
 

/* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED. Use of this software is authorized pursuant to the terms of the license found at http://developer.java.sun.ru/berkeley_license.html. Copyright 2003 Sun Microsystems, Inc. All Rights Reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: - Redistribution of source code must retain the above copyright notice, this list of conditions and the following disclaimer. - Redistribution in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of Sun Microsystems, Inc. or the names of contributors may be used to endorse or promote products derived from this software without specific prior written permission. This software is provided "AS IS," without a warranty of any kind. ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN") AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. You acknowledge that this software is not designed, licensed or intended for use in the design, construction, operation or maintenance of any nuclear facility.

• /

/*

* Copyright 2003 Sun Microsystems, Inc.  ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/ 

import java.sql.*; public class CreateUDTs {

 public static void main(String args[]) {
   String url = "jdbc:mySubprotocol:myDataSource";
   Connection con;
   Statement stmt;
   String createAddress = "CREATE TYPE ADDRESS (NUM INTEGER, " +
                       "STREET VARCHAR(40), CITY VARCHAR(40), " +
                       "STATE CHAR(2), ZIP CHAR(5))";
   String createManager = "CREATE TYPE MANAGER (MGR_ID INTEGER, " +
     "LAST_NAME VARCHAR(40), FIRST_NAME VARCHAR(40), " +
     "PHONE char(10))";
   try {
     Class.forName("myDriver.ClassName");
   } catch(java.lang.ClassNotFoundException e) {
     System.err.print("ClassNotFoundException: ");
     System.err.println(e.getMessage());
   }
   try {
     con = DriverManager.getConnection(url,
               "myLogin", "myPassword");
     stmt = con.createStatement();
     stmt.executeUpdate(createAddress);
     stmt.executeUpdate("CREATE TYPE PHONE_NO AS CHAR(10)");
     stmt.executeUpdate(createManager);
     stmt.close();
     con.close();
       } catch(SQLException ex) {
           System.err.println("-----SQLException-----");
           System.err.println("SQLState:  " + ex.getSQLState());
           System.err.println("Message:  " + ex.getMessage());
           System.err.println("Vendor:  " + ex.getErrorCode());
       }
 }

}

 </source>
   
  
 
  

Create data table: reference

   <source lang="java">
 

/* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED. Use of this software is authorized pursuant to the terms of the license found at http://developer.java.sun.ru/berkeley_license.html. Copyright 2003 Sun Microsystems, Inc. All Rights Reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: - Redistribution of source code must retain the above copyright notice, this list of conditions and the following disclaimer. - Redistribution in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of Sun Microsystems, Inc. or the names of contributors may be used to endorse or promote products derived from this software without specific prior written permission. This software is provided "AS IS," without a warranty of any kind. ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN") AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. You acknowledge that this software is not designed, licensed or intended for use in the design, construction, operation or maintenance of any nuclear facility.

• /

/*

* Copyright 2003 Sun Microsystems, Inc.  ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/ 

import java.sql.*; public class CreateRef {

 public static void main(String args[]) {
   String url = "jdbc:mySubprotocol:myDataSource";
   Connection con;
   Statement stmt;
   try {
     Class.forName("myDriver.ClassName");
   } catch(java.lang.ClassNotFoundException e) {
     System.err.print("ClassNotFoundException: ");
     System.err.println(e.getMessage());
   }
   try {
     String createManagers = "CREATE TABLE MANAGERS OF MANAGER " +
           "(OID REF(MANAGER) VALUES ARE SYSTEM GENERATED)";
     String insertManager1 = "INSERT INTO MANAGERS " +
         "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES " +
         "(000001, "MONTOYA", "ALFREDO", "8317225600")";
     String insertManager2 = "INSERT INTO MANAGERS " +
         "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES " +
         "(000002, "HASKINS", "MARGARET", "4084355600")";
     String insertManager3 = "INSERT INTO MANAGERS " +
         "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES " +
         "(000003, "CHEN", "HELEN", "4153785600")";
     con = DriverManager.getConnection(url,
                 "myLogin", "myPassword");
     stmt = con.createStatement();
     stmt.executeUpdate(createManagers);
     con.setAutoCommit(false);
     stmt.addBatch(insertManager1);
     stmt.addBatch(insertManager2);
     stmt.addBatch(insertManager3);
     int [] updateCounts = stmt.executeBatch();
     
     con.rumit();
     System.out.println("Update count for:  ");
     for(int i = 0; i < updateCounts.length; i++) {
       System.out.print("    command " + (i + 1) + " = ");
       System.out.println(updateCounts[i]);
     }
     stmt.close();
     con.close();
   } catch(BatchUpdateException b) {
           System.err.println("-----BatchUpdateException-----");
           System.err.println("Message:  " + b.getMessage());
           System.err.println("SQLState:  " + b.getSQLState());
           System.err.println("Vendor:  " + b.getErrorCode());
           System.err.print("Update counts for successful commands:  ");
           int [] rowsUpdated = b.getUpdateCounts();
           for (int i = 0; i < rowsUpdated.length; i++) {
               System.err.print(rowsUpdated[i] + "   ");
           }
           System.err.println("");
   } catch(SQLException ex) {
     System.err.println("------SQLException------");
     System.err.println("Error message:  " + ex.getMessage());
     System.err.println("SQLState:  " + ex.getSQLState());
     System.err.println("Vendor:  " + ex.getErrorCode());
   }
 }

}

 </source>
   
  
 
  

Create data type and data table

   <source lang="java">
 

/* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED. Use of this software is authorized pursuant to the terms of the license found at http://developer.java.sun.ru/berkeley_license.html. Copyright 2003 Sun Microsystems, Inc. All Rights Reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: - Redistribution of source code must retain the above copyright notice, this list of conditions and the following disclaimer. - Redistribution in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of Sun Microsystems, Inc. or the names of contributors may be used to endorse or promote products derived from this software without specific prior written permission. This software is provided "AS IS," without a warranty of any kind. ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN") AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. You acknowledge that this software is not designed, licensed or intended for use in the design, construction, operation or maintenance of any nuclear facility.

• /

/*

* Copyright 2003 Sun Microsystems, Inc.  ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/ 

import java.sql.*;

public class CreateStores {

 public static void main(String args[]) {
     
   String url = "jdbc:mySubprotocol:myDataSource";
   Connection con;
   String createTable;
   String createArray;
   createArray = "CREATE TYPE COF_ARRAY AS ARRAY(10) OF VARCHAR(40)";
   createTable = "CREATE TABLE STORES ( " +
           "STORE_NO INTEGER, LOCATION ADDRESS, " +
           "COF_TYPES COF_ARRAY, MGR REF MANAGER )";
   Statement stmt;
 
   try {
     Class.forName("myDriver.ClassName");
   } catch(java.lang.ClassNotFoundException e) {
     System.err.print("ClassNotFoundException: "); 
     System.err.println(e.getMessage());
   }
   try {
     con = DriverManager.getConnection(url, 
                 "myLogin", "myPassword");
 
     stmt = con.createStatement();              
          stmt.executeUpdate(createArray);
          stmt.executeUpdate(createTable);
 
     stmt.close();
     con.close();
 
   } catch(SQLException ex) {
     System.err.println("SQLException: " + ex.getMessage());
   }
 }

}

 </source>
   
  
 
  

Create supplier table

   <source lang="java">
 

/* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED. Use of this software is authorized pursuant to the terms of the license found at http://developer.java.sun.ru/berkeley_license.html. Copyright 2003 Sun Microsystems, Inc. All Rights Reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: - Redistribution of source code must retain the above copyright notice, this list of conditions and the following disclaimer. - Redistribution in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of Sun Microsystems, Inc. or the names of contributors may be used to endorse or promote products derived from this software without specific prior written permission. This software is provided "AS IS," without a warranty of any kind. ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN") AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. You acknowledge that this software is not designed, licensed or intended for use in the design, construction, operation or maintenance of any nuclear facility.

• /

/*

* Copyright 2003 Sun Microsystems, Inc.  ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/ 

import java.net.URL; import java.sql.*;

public class CreateSuppliers {

 public static void main(String args[]) {
   String url = "jdbc:mySubprotocol:myDataSource";
   Connection con;
   String createString;
   createString = "create table SUPPLIERS " + 
           "(SUP_ID int, " +
           "SUP_NAME varchar(40), " +
           "STREET varchar(40), " +
           "CITY varchar(20), " +
           "STATE char(2), ZIP char(5))";
 
   Statement stmt;
 
   try {
     Class.forName("myDriver.ClassName");
   } catch(java.lang.ClassNotFoundException e) {
     System.err.print("ClassNotFoundException: "); 
     System.err.println(e.getMessage());
   }
   try {
     con = DriverManager.getConnection(url, 
                  "myLogin", "myPassword");
 
     stmt = con.createStatement();              
        stmt.executeUpdate(createString);
 
     stmt.close();
     con.close();
 
   } catch(SQLException ex) {
     System.err.println("SQLException: " + ex.getMessage());
   }
 }

}

 </source>
   
  
 
  

Create table with foreign key

   <source lang="java">
 

/* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED. Use of this software is authorized pursuant to the terms of the license found at http://developer.java.sun.ru/berkeley_license.html. Copyright 2003 Sun Microsystems, Inc. All Rights Reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: - Redistribution of source code must retain the above copyright notice, this list of conditions and the following disclaimer. - Redistribution in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of Sun Microsystems, Inc. or the names of contributors may be used to endorse or promote products derived from this software without specific prior written permission. This software is provided "AS IS," without a warranty of any kind. ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN") AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. You acknowledge that this software is not designed, licensed or intended for use in the design, construction, operation or maintenance of any nuclear facility.

• /

/*

* Copyright 2003 Sun Microsystems, Inc.  ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/ 

import java.sql.*;

public class ForeignKeysCoffees {

 public static void main(String args[]) {
     
   String url = "jdbc:mySubprotocol:myDataSource";
   Connection con;
   String createString = "create table COFFEESFK " +
             "(COF_NAME varchar(32) NOT NULL, " +
             "SUP_ID int, " +
             "PRICE float, " +
             "SALES int, " +
             "TOTAL int, " +
             "primary key(COF_NAME), " +
             "foreign key(SUP_ID) references SUPPLIERSPK)";
   Statement stmt;
 
   try {
     Class.forName("myDriver.ClassName");
 
   } catch(java.lang.ClassNotFoundException e) {
     System.err.print("ClassNotFoundException: "); 
     System.err.println(e.getMessage());
   }
   try {
     con = DriverManager.getConnection(url, 
                  "myLogin", "myPassword");
     
     stmt  = con.createStatement();
     stmt.executeUpdate(createString);
 
     DatabaseMetaData dbmd = con.getMetaData();
 
     ResultSet rs = dbmd.getImportedKeys(null, null, "COFFEESFK");
     while (rs.next()) {
       String pkTable = rs.getString("PKTABLE_NAME");
       String pkColName = rs.getString("PKCOLUMN_NAME");
       String fkTable = rs.getString("FKTABLE_NAME");
       String fkColName = rs.getString("FKCOLUMN_NAME");
       short updateRule = rs.getShort("UPDATE_RULE");
       short deleteRule = rs.getShort("DELETE_RULE");
       System.out.println("primary key table name :  " + pkTable);
       System.out.print("primary key column name :  ");
       System.out.println(pkColName);
       System.out.println("foreign key table name :  " + fkTable);
       System.out.print("foreign key column name :  ");
       System.out.println(fkColName);
       System.out.println("update rule:  " + updateRule);
       System.out.println("delete rule:  " + deleteRule);
       System.out.println("");
     }
   
     rs.close();
     stmt.close();
     con.close();
 
   } catch(SQLException ex) {
     System.err.print("SQLException: ");
     System.err.println(ex.getMessage());
   }
 }

}

 </source>
   
  
 
  

Create table with primary key

   <source lang="java">
 

/* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED. Use of this software is authorized pursuant to the terms of the license found at http://developer.java.sun.ru/berkeley_license.html. Copyright 2003 Sun Microsystems, Inc. All Rights Reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: - Redistribution of source code must retain the above copyright notice, this list of conditions and the following disclaimer. - Redistribution in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of Sun Microsystems, Inc. or the names of contributors may be used to endorse or promote products derived from this software without specific prior written permission. This software is provided "AS IS," without a warranty of any kind. ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN") AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. You acknowledge that this software is not designed, licensed or intended for use in the design, construction, operation or maintenance of any nuclear facility.

• /

/*

* Copyright 2003 Sun Microsystems, Inc.  ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/ 

import java.sql.*;

public class PrimaryKeysSuppliers {

 public static void main(String args[]) {
     
   String url = "jdbc:mySubprotocol:myDataSource";
   Connection con;
   String createString = "create table SUPPLIERSPK " +
               "(SUP_ID INTEGER NOT NULL, " +
               "SUP_NAME VARCHAR(40), " +
               "STREET VARCHAR(40), " +
               "CITY VARCHAR(20), " +
               "STATE CHAR(2), " +
               "ZIP CHAR(5), " +
               "primary key(SUP_ID))";
   Statement stmt;
 
   try {
     Class.forName("myDriver.ClassName");
 
   } catch(java.lang.ClassNotFoundException e) {
     System.err.print("ClassNotFoundException: "); 
     System.err.println(e.getMessage());
   }
   try {
     con = DriverManager.getConnection(url, 
                  "myLogin", "myPassword");
     
     stmt  = con.createStatement();
     stmt.executeUpdate(createString);
 
     DatabaseMetaData dbmd = con.getMetaData();
 
     ResultSet rs = dbmd.getPrimaryKeys(null, null, "SUPPLIERSPK");
     while (rs.next()) {
       String name = rs.getString("TABLE_NAME");
       String columnName = rs.getString("COLUMN_NAME");
       String keySeq = rs.getString("KEY_SEQ");
       String pkName = rs.getString("PK_NAME");
       System.out.println("table name :  " + name);
       System.out.println("column name:  " + columnName);
       System.out.println("sequence in key:  " + keySeq);
       System.out.println("primary key name:  " + pkName);
       System.out.println("");
     }
   
     rs.close();
     con.close();
 
   } catch(SQLException ex) {
     System.err.print("SQLException: ");
     System.err.println(ex.getMessage());
   }
 }

}

 </source>
   
  
 
  

Creating a Database Table called my_table with one column, col_string, which holds strings.

   <source lang="java">
  

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   Statement stmt = connection.createStatement();
   
   // Create table called my_table
   String sql = "CREATE TABLE my_table(col_string VARCHAR(254))";
   stmt.executeUpdate(sql);
 }

}

 </source>
   
  
 
  

Database metadata: data type

   <source lang="java">
 

/* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED. Use of this software is authorized pursuant to the terms of the license found at http://developer.java.sun.ru/berkeley_license.html. Copyright 2003 Sun Microsystems, Inc. All Rights Reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: - Redistribution of source code must retain the above copyright notice, this list of conditions and the following disclaimer. - Redistribution in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of Sun Microsystems, Inc. or the names of contributors may be used to endorse or promote products derived from this software without specific prior written permission. This software is provided "AS IS," without a warranty of any kind. ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN") AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. You acknowledge that this software is not designed, licensed or intended for use in the design, construction, operation or maintenance of any nuclear facility.

• /

/*

* Copyright 2003 Sun Microsystems, Inc.  ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/ 

import java.sql.*;

public class TableTypes {

 public static void main(String args[]) {
     
   String url = "jdbc:mySubprotocol:myDataSource";
   Connection con;
 
   try {
     Class.forName("myDriver.ClassName");
 
   } catch(java.lang.ClassNotFoundException e) {
     System.err.print("ClassNotFoundException: "); 
     System.err.println(e.getMessage());
   }
   try {
     con = DriverManager.getConnection(url, 
                  "myLogin", "myPassword");
 
     DatabaseMetaData dbmd = con.getMetaData();
     String dbmsName = dbmd.getDatabaseProductName();
     ResultSet rs = dbmd.getTableTypes();
     System.out.print("The following types of tables are ");
     System.out.println("available in " + dbmsName + ":  ");
     
     while (rs.next()) {
       String tableType = rs.getString("TABLE_TYPE");
       System.out.println("    " + tableType);
     }
   
     rs.close();
     con.close();
 
   } catch(SQLException ex) {
     System.err.print("SQLException: ");
     System.err.println(ex.getMessage());
   }
 }

}

 </source>
   
  
 
  

Deleting a Database Table called my_table from a database.

   <source lang="java">
  

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   Statement stmt = connection.createStatement();
   
   stmt.executeUpdate("DROP TABLE my_table");
 }

}

 </source>
   
  
 
  

Deleting All Rows from a Database Table

   <source lang="java">
  

import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   Statement stmt = connection.createStatement();
   // Use TRUNCATE
   String sql = "TRUNCATE my_table";
   // Execute deletion
   stmt.executeUpdate(sql);
   // Use DELETE
   sql = "DELETE FROM my_table";
   // Execute deletion
   stmt.executeUpdate(sql);
 }

}

 </source>
   
  
 
  

Deleting a Row from a Database Table

   <source lang="java">
  

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.Statement; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   Statement stmt = connection.createStatement();
   String sql = "DELETE FROM my_table WHERE col_string="a string"";
   int deleteCount = stmt.executeUpdate(sql);
   sql = "DELETE FROM my_table WHERE col_string=?";
   PreparedStatement pstmt = connection.prepareStatement(sql);
   pstmt.setString(1, "a string");
   deleteCount = pstmt.executeUpdate();
 }

}

 </source>
   
  
 
  

Deleting a Table from Database

   <source lang="java">
  

import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.mysql.jdbc.Driver";
   String userName = "root";
   String password = "root";
   Class.forName(driverName).newInstance();
   Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
       userName, password);
   Statement st = con.createStatement();
   st.executeUpdate("DROP TABLE Employee1");
   con.close();
 }

}

 </source>
   
  
 
  

Get all table catalogs

   <source lang="java">
  

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   DatabaseMetaData dbmd = connection.getMetaData();
   String[] types = { "TABLE" };
   ResultSet resultSet = dbmd.getTables(null, null, "%", types);
   while (resultSet.next()) {
     String tableName = resultSet.getString(3);
     String tableCatalog = resultSet.getString(1);
     String tableSchema = resultSet.getString(2);
   }
 }

}

 </source>
   
  
 
  

Get all table schemas

   <source lang="java">
  

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   DatabaseMetaData dbmd = connection.getMetaData();
   String[] types = { "TABLE" };
   ResultSet resultSet = dbmd.getTables(null, null, "%", types);
   while (resultSet.next()) {
     String tableName = resultSet.getString(3);
     String tableCatalog = resultSet.getString(1);
     String tableSchema = resultSet.getString(2);
   }
 }

}

 </source>
   
  
 
  

Get Available Table Name In A Database

   <source lang="java">
 

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getHSQLConnection();
   System.out.println("Got Connection.");
   Statement st = conn.createStatement();
   st.executeUpdate("create table survey (id int,name varchar);");
   st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
   ResultSet rs = null;
   DatabaseMetaData meta = conn.getMetaData();
   rs = meta.getTables(null, null, null, new String[]{"TABLE"});
   while (rs.next()) {
     String tableName = rs.getString("TABLE_NAME");
     System.out.println("tableName=" + tableName);
   }
   st.close();
   conn.close();
 }
 private static Connection getHSQLConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   System.out.println("Driver Loaded.");
   String url = "jdbc:hsqldb:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }
 public static Connection getMySqlConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/octopus";
   String username = "root";
   String password = "root";
   Class.forName(driver); // load MySQL driver
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static Connection getOracleConnection() throws Exception {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@localhost:1521:caspian";
   String username = "mp";
   String password = "mp2";
   Class.forName(driver); // load Oracle driver
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }

}

 </source>
   
  
 
  

Get Table Or View Name From A Database

   <source lang="java">
 

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getMySqlConnection();
   System.out.println("Got Connection.");
   Statement st = conn.createStatement();
   st.executeUpdate("drop table survey;");
   st.executeUpdate("create table survey (id int,name varchar(30));");
   st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
   ResultSet rs = null;
    DatabaseMetaData meta = conn.getMetaData();
    rs = meta.getTables(null, null, null, new String[]{"TABLE", "VIEW" });
    while (rs.next()) {
      String tableOrViewName = rs.getString("TABLE_NAME");
      System.out.println("getTableNames(): tableOrViewName="+tableOrViewName);
    }
   st.close();
   conn.close();
 }
 private static Connection getHSQLConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   System.out.println("Driver Loaded.");
   String url = "jdbc:hsqldb:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }
 public static Connection getMySqlConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/demo2s";
   String username = "oost";
   String password = "oost";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static Connection getOracleConnection() throws Exception {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@localhost:1521:caspian";
   String username = "mp";
   String password = "mp2";
   Class.forName(driver); // load Oracle driver
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }

}

 </source>
   
  
 
  

Getting Rows from a Database Table

   <source lang="java">
  

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   // Create a result set containing all data from my_table
   Statement stmt = connection.createStatement();
   ResultSet rs = stmt.executeQuery("SELECT * FROM my_table");
 }

}

 </source>
   
  
 
  

Getting the Number of Rows in a Database Table

   <source lang="java">
  

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   Statement stmt = connection.createStatement();
   ResultSet resultSet = stmt.executeQuery("SELECT COUNT(*) FROM my_table");
   // Get the number of rows from the result set
   resultSet.next();
   int rowcount = resultSet.getInt(1);
 }

}

 </source>
   
  
 
  

Inserting a Row into a Database Table

   <source lang="java">
  

import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   Statement stmt = connection.createStatement();
   String sql = "INSERT INTO my_table (col_string) VALUES("a string")";
   stmt.executeUpdate(sql);
 }

}

 </source>
   
  
 
  

Inserting a Row into a Database Table Using a Prepared Statement

   <source lang="java">
  

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   String sql = "INSERT INTO my_table (col_string) VALUES(?)";
   PreparedStatement pstmt = connection.prepareStatement(sql);
   // Insert 10 rows
   for (int i = 0; i < 10; i++) {
     // Set the value
     pstmt.setString(1, "row " + i);
     // Insert the row
     pstmt.executeUpdate();
   }
 }

}

 </source>
   
  
 
  

Listing All Table Names in a Database

   <source lang="java">
  

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   DatabaseMetaData dbmd = connection.getMetaData();
   String[] types = { "TABLE" };
   ResultSet resultSet = dbmd.getTables(null, null, "%", types);
   while (resultSet.next()) {
     String tableName = resultSet.getString(3);
     String tableCatalog = resultSet.getString(1);
     String tableSchema = resultSet.getString(2);
   }
 }

}

 </source>
   
  
 
  

List tables in a database

   <source lang="java">
  

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; public class Main {

 public static void main(String args[]) throws Exception {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   String URL = "jdbc:odbc:dbName";
   Connection conn = DriverManager.getConnection(URL, "user", "passw");
   DatabaseMetaData dmd = conn.getMetaData();
   ResultSet rs1 = dmd.getSchemas();
   while (rs1.next()) {
     String ss = rs1.getString(1);
     ResultSet rs2 = dmd.getTables(null, ss, "%", null);
     while (rs2.next())
       System.out.println(rs2.getString(3) + " " + rs2.getString(4));
   }
   conn.close();
 }

}

 </source>
   
  
 
  

List the Tables in an SQL database

   <source lang="java">
 

/*

* Copyright (c) Ian F. Darwin, http://www.darwinsys.ru/, 1996-2002.
* All rights reserved. Software written by Ian F. Darwin and others.
* $Id: LICENSE,v 1.8 2004/02/09 03:33:38 ian Exp $
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
* 1. Redistributions of source code must retain the above copyright
*    notice, this list of conditions and the following disclaimer.
* 2. Redistributions in binary form must reproduce the above copyright
*    notice, this list of conditions and the following disclaimer in the
*    documentation and/or other materials provided with the distribution.
*
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS""
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
* TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
* 
* Java, the Duke mascot, and all variants of Sun"s Java "steaming coffee
* cup" logo are trademarks of Sun Microsystems. Sun"s, and James Gosling"s,
* pioneering role in inventing and promulgating (and standardizing) the Java 
* language and environment is gratefully acknowledged.
* 
* The pioneering role of Dennis Ritchie and Bjarne Stroustrup, of AT&T, for
* inventing predecessor languages C and C++ is also gratefully acknowledged.
*/

/**

* List the Tables in an SQL database
*/

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; public class ListTables {

 public static void main(String[] args) throws Exception {
   Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
   // Enable logging
   // DriverManager.setLogStream(System.err);
   System.out.println("Getting Connection");
   Connection c = DriverManager.getConnection (
     "jdbc:odbc:Companies",
     "ian", "");  // user, passwd
   DatabaseMetaData md = c.getMetaData();
   ResultSet rs = md.getTables(null, null, "%", null);
   while (rs.next()) {
     System.out.println(rs.getString(3));
   }
 }

}

 </source>
   
  
 
  

Retrieving Tables from a Database

   <source lang="java">
  

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; public class Main {

 public static void main(String[] argv) throws Exception {
   String driver = "com.mysql.jdbc.Driver";
   String user = "root";
   String pass = "root";
   Class.forName(driver);
   Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", user,
       pass);
   DatabaseMetaData dbm = con.getMetaData();
   String[] types = { "TABLE" };
   ResultSet rs = dbm.getTables(null, null, "%", types);
   System.out.println("Table name:");
   while (rs.next()) {
     String table = rs.getString("TABLE_NAME");
     System.out.println(table);
     con.close();
   }
 }

}

 </source>
   
  
 
  

Table and column existance

   <source lang="java">

/****************************************************************

* Licensed to the Apache Software Foundation (ASF) under one   *
* or more contributor license agreements.  See the NOTICE file *
* distributed with this work for additional information        *
* regarding copyright ownership.  The ASF licenses this file   *
* to you under the Apache License, Version 2.0 (the            *
* "License"); you may not use this file except in compliance   *
* with the License.  You may obtain a copy of the License at   *
*                                                              *
*   http://www.apache.org/licenses/LICENSE-2.0                 *
*                                                              *
* Unless required by applicable law or agreed to in writing,   *
* software distributed under the License is distributed on an  *
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY       *
* KIND, either express or implied.  See the License for the    *
* specific language governing permissions and limitations      *
* under the License.                                           *
****************************************************************/

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Locale; /**

*

Helper class for managing common JDBC tasks.

*

*

This class is abstract to allow implementations to * take advantage of different logging capabilities/interfaces in * different parts of the code.

*
* @version CVS $Revision: 494012 $ $Date: 2007-01-08 11:23:58 +0100 (Mo, 08 Jan 2007) $
*/

abstract public class JDBCUtil {

   /**
    * An abstract method which child classes override to handle logging of
    * errors in their particular environments.
    *
    * @param errorString the error message generated
    */
   abstract protected void delegatedLog(String errorString);
   /**
    * Checks database metadata to see if a table exists.
    * Try UPPER, lower, and MixedCase, to see if the table is there.
    *
    * @param dbMetaData the database metadata to be used to look up this table
    * @param tableName the table name
    *
    * @throws SQLException if an exception is encountered while accessing the database
    */
   public boolean tableExists(DatabaseMetaData dbMetaData, String tableName)
       throws SQLException {
       return ( tableExistsCaseSensitive(dbMetaData, tableName) ||
                tableExistsCaseSensitive(dbMetaData, tableName.toUpperCase(Locale.US)) ||
                tableExistsCaseSensitive(dbMetaData, tableName.toLowerCase(Locale.US)) );
   }
   /**
    * Checks database metadata to see if a table exists.  This method
    * is sensitive to the case of the provided table name.
    *
    * @param dbMetaData the database metadata to be used to look up this table
    * @param tableName the case sensitive table name
    *
    * @throws SQLException if an exception is encountered while accessing the database
    */
   public boolean tableExistsCaseSensitive(DatabaseMetaData dbMetaData, String tableName)
       throws SQLException {
       ResultSet rsTables = dbMetaData.getTables(null, null, tableName, null);
       try {
           boolean found = rsTables.next();
           return found;
       } finally {
           closeJDBCResultSet(rsTables);
       }
   }
   /**
    * Checks database metadata to see if a column exists in a table
    * Try UPPER, lower, and MixedCase, both on the table name and the column name, to see if the column is there.
    *
    * @param dbMetaData the database metadata to be used to look up this column
    * @param tableName the table name
    * @param columnName the column name
    *
    * @throws SQLException if an exception is encountered while accessing the database
    */
   public boolean columnExists(DatabaseMetaData dbMetaData, String tableName, String columnName)
       throws SQLException {
       return ( columnExistsCaseSensitive(dbMetaData, tableName, columnName) ||
                columnExistsCaseSensitive(dbMetaData, tableName, columnName.toUpperCase(Locale.US)) ||
                columnExistsCaseSensitive(dbMetaData, tableName, columnName.toLowerCase(Locale.US)) ||
                columnExistsCaseSensitive(dbMetaData, tableName.toUpperCase(Locale.US), columnName) ||
                columnExistsCaseSensitive(dbMetaData, tableName.toUpperCase(Locale.US), columnName.toUpperCase(Locale.US)) ||
                columnExistsCaseSensitive(dbMetaData, tableName.toUpperCase(Locale.US), columnName.toLowerCase(Locale.US)) ||
                columnExistsCaseSensitive(dbMetaData, tableName.toLowerCase(Locale.US), columnName) ||
                columnExistsCaseSensitive(dbMetaData, tableName.toLowerCase(Locale.US), columnName.toUpperCase(Locale.US)) ||
                columnExistsCaseSensitive(dbMetaData, tableName.toLowerCase(Locale.US), columnName.toLowerCase(Locale.US)) );
   }
   /**
    * Checks database metadata to see if a column exists in a table.  This method
    * is sensitive to the case of both the provided table name and column name.
    *
    * @param dbMetaData the database metadata to be used to look up this column
    * @param tableName the case sensitive table name
    * @param columnName the case sensitive column name
    *
    * @throws SQLException if an exception is encountered while accessing the database
    */
   public boolean columnExistsCaseSensitive(DatabaseMetaData dbMetaData, String tableName, String columnName)
       throws SQLException {
       ResultSet rsTables = dbMetaData.getColumns(null, null, tableName, columnName);
       try {
           boolean found = rsTables.next();
           return found;
       } finally {
           closeJDBCResultSet(rsTables);
       }
   }
   /**
    * Closes database connection and logs if an error
    * is encountered
    *
    * @param conn the connection to be closed
    */
   public void closeJDBCConnection(Connection conn) {
       try {
           if (conn != null) {
               conn.close();
           }
       } catch (SQLException sqle) {
           // Log exception and continue
           subclassLogWrapper("Unexpected exception while closing database connection.");
       }
   }
   /**
    * Closes database statement and logs if an error
    * is encountered
    *
    * @param stmt the statement to be closed
    */
   public void closeJDBCStatement(Statement stmt) {
       try {
           if (stmt != null) {
               stmt.close();
           }
       } catch (SQLException sqle) {
           // Log exception and continue
           subclassLogWrapper("Unexpected exception while closing database statement.");
       }
   }
   /**
    * Closes database result set and logs if an error
    * is encountered
    *
    * @param aResultSet the result set to be closed
    */
   public void closeJDBCResultSet(ResultSet aResultSet ) {
       try {
           if (aResultSet != null) {
               aResultSet.close();
           }
       } catch (SQLException sqle) {
           // Log exception and continue
           subclassLogWrapper("Unexpected exception while closing database result set.");
       }
   }
   /**
    * Wraps the delegated call to the subclass logging method with a Throwable
    * wrapper.  All throwables generated by the subclass logging method are
    * caught and ignored.
    *
    * @param logString the raw string to be passed to the logging method implemented
    *                  by the subclass
    */
   private void subclassLogWrapper(String logString)
   {
       try {
           delegatedLog(logString);
       }
       catch(Throwable t) {
           // Throwables generated by the logging system are ignored
       }
   }

}

 </source>
   
  
 
  

Table exist?

   <source lang="java">
 

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getMySqlConnection();
   System.out.println("Got Connection.");
   Statement st = conn.createStatement();
   st.executeUpdate("drop table survey;");
   st.executeUpdate("create table survey (id int,name varchar(30));");
   st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
   String tableName = "survey";
   String query = "select * from  " + tableName + " where 1=0";
   Statement stmt = null;
   ResultSet rs = null;
   try {
     stmt = conn.createStatement();
     rs = stmt.executeQuery(query);
     System.out.println("Exist");;
   }
   catch (Exception e ) {
     // table does not exist or some other problem
     //e.printStackTrace();
     System.out.println("Not Exist");
   }
   st.close();
   conn.close();
 }
 private static Connection getHSQLConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   System.out.println("Driver Loaded.");
   String url = "jdbc:hsqldb:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }
 public static Connection getMySqlConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/demo2s";
   String username = "oost";
   String password = "oost";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static Connection getOracleConnection() throws Exception {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@localhost:1521:caspian";
   String username = "mp";
   String password = "mp2";
   Class.forName(driver); // load Oracle driver
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }

}

 </source>
   
  
 
  

Updating a Row in a Database Table

   <source lang="java">
  

import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   Statement stmt = connection.createStatement();
   String sql = "UPDATE my_table SET col_string="a new string" WHERE col_string = "a string"";
   int updateCount = stmt.executeUpdate(sql);
 }

}

 </source>