Java/Database SQL JDBC/Column

Материал из Java эксперт
Перейти к: навигация, поиск

Adding a New Column Name in 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 {
   Class.forName("com.mysql.jdbc.Driver");
   Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
       "root", "root");
   Statement st = con.createStatement();
   int n = st.executeUpdate("ALTER TABLE mytable ADD col int");
   System.out.println("Query OK, " + n + " rows affected");
 }

}


 </source>
   
  
 
  



Arrange a Column of 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 {
   Class.forName("com.mysql.jdbc.Driver");
   Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
       "root", "root");
   Statement st = con.createStatement();
   ResultSet res = st.executeQuery("SELECT col_name  FROM mytable  ORDER BY col_name ASC");
   while (res.next()) {
     int col = res.getInt(1);
     System.out.println(col);
   }
 }

}


 </source>
   
  
 
  



Change Column Name of a 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 {
   Class.forName("com.mysql.jdbc.Driver");
   Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
       "root", "root");
   Statement st = con.createStatement();
   int n = st.executeUpdate("ALTER TABLE myTable CHANGE old_col  new_col int");
   System.out.println("Query OK, " + n + " rows affected");
 }

}


 </source>
   
  
 
  



Delete a Column 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 {
   Class.forName("com.mysql.jdbc.Driver");
   Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
       "root", "root");
   Statement st = con.createStatement();
   st.executeUpdate("ALTER TABLE mytable DROP col");
   System.out.println("Column is deleted successfully!");
 }

}


 </source>
   
  
 
  



Designated column"s table name

   <source lang="java">
 

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

 private static final String DRIVER = "com.mysql.jdbc.Driver";
 private static final String URL = "jdbc:mysql://localhost/testdb";
 private static final String USERNAME = "root";
 private static final String PASSWORD = "";
 public static void main(String[] args) throws Exception {
   Class.forName(DRIVER);
   Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
   Statement statement = connection.createStatement();
   String query = "SELECT a.id, a.username, a.country_id, b.country_name " + "FROM users a "
       + "LEFT JOIN countries b ON a.country_id = b.id";
   ResultSet resultSet = statement.executeQuery(query);
   ResultSetMetaData metadata = resultSet.getMetaData();
   String tableName = metadata.getTableName(1);
   System.out.println("Table name of column "id" = " + tableName);
   tableName = metadata.getTableName(4);
   System.out.println("Table name of column "country name" = " + tableName);
 }

}


 </source>
   
  
 
  



Get Column Corresponding Class Name

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // get the designated column"s class name.
     System.out.println(rsMetaData.getColumnClassName(i));
   }
   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>
   
  
 
  



Get Column Detail Information

   <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.");
   ResultSet rsColumns = null;
   DatabaseMetaData meta = conn.getMetaData();
   rsColumns = meta.getColumns(null, "%", "code", "%");
   
   while (rsColumns.next()) {
     String columnType = rsColumns.getString("TYPE_NAME");
     String columnName = rsColumns.getString("COLUMN_NAME");
     int size = rsColumns.getInt("COLUMN_SIZE");
     int nullable = rsColumns.getInt("NULLABLE");
     int position = rsColumns.getInt("ORDINAL_POSITION");
     System.out.println("column name=" + columnName);
     System.out.println("type=" + columnType);
     System.out.println("size=" + size);
     if (nullable == DatabaseMetaData.columnNullable) {
       System.out.println("nullable is true");
     } else {
       System.out.println("nullable is false");
     }
     System.out.println("position" + position);
   }
   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>
   
  
 
  



Get Column Display Size.zip

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // indicates the designated column"s normal maximum width in
     // characters
     System.out.println(rsMetaData.getColumnDisplaySize(i));
     // gets the designated column"s suggested title
     // for use in printouts and displays.
   }
   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>
   
  
 
  



Get Column Label

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // get the designated column"s name.
     System.out.println(rsMetaData.getColumnLabel(i));
     
   }
   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>
   
  
 
  



Get Column Name

   <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 rsColumns = null;
   DatabaseMetaData meta = conn.getMetaData();
   rsColumns = meta.getColumns(null, null, "survey", null);
   while (rsColumns.next()) {
     String columnName = rsColumns.getString("COLUMN_NAME");
     System.out.println("column name=" + columnName);
     String columnType = rsColumns.getString("TYPE_NAME");
     System.out.println("type:" + columnType);
     int size = rsColumns.getInt("COLUMN_SIZE");
     System.out.println("size:" + size);
     int nullable = rsColumns.getInt("NULLABLE");
     if (nullable == DatabaseMetaData.columnNullable) {
       System.out.println("nullable true");
     } else {
       System.out.println("nullable false");
     }
     int position = rsColumns.getInt("ORDINAL_POSITION");
     System.out.println("position:" + position);
     
   }
   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>
   
  
 
  



Get Column Name And Type For A Table

   <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 rsColumns = null;
   DatabaseMetaData meta = conn.getMetaData();
   rsColumns = meta.getColumns(null, null, "survey", null);
   while (rsColumns.next()) {
     System.out.println(rsColumns.getString("TYPE_NAME"));
     System.out.println(rsColumns.getString("COLUMN_NAME"));
   }
   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>
   
  
 
  



Get Column Name From ResultSet Metadata

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // get the column"s name.
     System.out.println(rsMetaData.getColumnName(i));


   }
   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>
   
  
 
  



Get Column Names From ResultSet for MySQL

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; public class GetColumnNamesFromResultSet_MySQL {

 public static Connection getConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/databaseName";
   String username = "root";
   String password = "root";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static void getColumnNames(ResultSet rs) throws SQLException {
   if (rs == null) {
     return;
   }
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   // get the column names; column indexes start from 1
   for (int i = 1; i < numberOfColumns + 1; i++) {
     String columnName = rsMetaData.getColumnName(i);
     // Get the name of the column"s table name
     String tableName = rsMetaData.getTableName(i);
     System.out.println("column name=" + columnName + " table=" + tableName + "");
   }
 }
 public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   ResultSet rs = null;
   try {
     conn = getConnection();
     // prepare query
     String query = "select id, name, age from employees";
     // create a statement
     stmt = conn.createStatement();
     // execute query and return result as a ResultSet
     rs = stmt.executeQuery(query);
     // get the column names from the ResultSet
     getColumnNames(rs);
   } catch (Exception e) {
     e.printStackTrace();
     System.exit(1);
   } finally {
     // release database resources
     try {
       rs.close();
       stmt.close();
       conn.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
 }

}


 </source>
   
  
 
  



Get Column Number Of Digits To Right Of The Decimal Point

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // gets the designated column"s number of
     // digits to right of the decimal point.
     System.out.println(rsMetaData.getScale(i));
   }
   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>
   
  
 
  



Get Column Number Of Presions Number Of Decimal Digits

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // get the designated column"s number of decimal digits.
     System.out.println(rsMetaData.getPrecision(i));
   }
   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>
   
  
 
  



Get Column ORDINAL POSITION

   <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 rsColumns = null;
   DatabaseMetaData meta = conn.getMetaData();
   rsColumns = meta.getColumns(null, null, "survey", null);
   while (rsColumns.next()) {
     String columnName = rsColumns.getString("COLUMN_NAME");
     System.out.println("column name=" + columnName);
     String columnType = rsColumns.getString("TYPE_NAME");
     System.out.println("type:" + columnType);
     int size = rsColumns.getInt("COLUMN_SIZE");
     System.out.println("size:" + size);
     int nullable = rsColumns.getInt("NULLABLE");
     if (nullable == DatabaseMetaData.columnNullable) {
       System.out.println("nullable true");
     } else {
       System.out.println("nullable false");
     }
     int position = rsColumns.getInt("ORDINAL_POSITION");
     System.out.println("position:" + position);
     
   }
   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>
   
  
 
  



Get Column Privileges

   <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 privileges = null;
   DatabaseMetaData meta = conn.getMetaData();
   // The "_" character represents any single character.
   // The "%" character represents any sequence of zero
   // or more characters.
   privileges = meta.getColumnPrivileges(conn.getCatalog(),null,"survey","%");
   while (privileges.next()) {
     String catalog = privileges.getString("TABLE_CAT");
     String schema = privileges.getString("TABLE_SCHEM");
     String tableName = privileges.getString("TABLE_NAME");
     String dbColumn = privileges.getString("COLUMN_NAME");
     String privilege = privileges.getString("PRIVILEGE");
     String grantor = privileges.getString("GRANTOR");
     String grantee = privileges.getString("GRANTEE");
     String isGrantable = privileges.getString("IS_GRANTABLE");
     System.out.println("table name:" + tableName);
     System.out.println("catalog:"+catalog);
     System.out.println("column:"+dbColumn);
     System.out.println("schema:"+ schema);
     System.out.println("privilege:"+privilege);
     System.out.println("grantor:"+grantor);
     System.out.println("isGrantable:"+isGrantable);
     System.out.println("grantee:"+grantee);
   }
   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>
   
  
 
  



Get Column Size

   <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 rsColumns = null;
   DatabaseMetaData meta = conn.getMetaData();
   rsColumns = meta.getColumns(null, null, "survey", null);
   while (rsColumns.next()) {
     String columnName = rsColumns.getString("COLUMN_NAME");
     System.out.println("column name=" + columnName);
     String columnType = rsColumns.getString("TYPE_NAME");
     System.out.println("type:" + columnType);
     int size = rsColumns.getInt("COLUMN_SIZE");
     System.out.println("size:" + size);
     int nullable = rsColumns.getInt("NULLABLE");
     if (nullable == DatabaseMetaData.columnNullable) {
       System.out.println("nullable true");
     } else {
       System.out.println("nullable false");
     }
     int position = rsColumns.getInt("ORDINAL_POSITION");
     System.out.println("position:" + position);
     
   }
   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>
   
  
 
  



Get Column Sql Data Type

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // get the designated column"s SQL type.
     System.out.println(rsMetaData.getColumnType(i));
   }
   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>
   
  
 
  



Get Column SQL Type Name

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // get the designated column"s SQL type name.
     System.out.println(rsMetaData.getColumnTypeName(i));
   }
   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>
   
  
 
  



Get Column Type

   <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 rsColumns = null;
   DatabaseMetaData meta = conn.getMetaData();
   rsColumns = meta.getColumns(null, null, "survey", null);
   while (rsColumns.next()) {
     String columnName = rsColumns.getString("COLUMN_NAME");
     System.out.println("column name=" + columnName);
     String columnType = rsColumns.getString("TYPE_NAME");
     System.out.println("type:" + columnType);
     int size = rsColumns.getInt("COLUMN_SIZE");
     System.out.println("size:" + size);
     int nullable = rsColumns.getInt("NULLABLE");
     if (nullable == DatabaseMetaData.columnNullable) {
       System.out.println("nullable true");
     } else {
       System.out.println("nullable false");
     }
     int position = rsColumns.getInt("ORDINAL_POSITION");
     System.out.println("position:" + position);
     
   }
   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>
   
  
 
  



Get Table Optimal Set Of Columns

   <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")");
   DatabaseMetaData meta = conn.getMetaData();
   // The "_" character represents any single character.
   // The "%" character represents any sequence of zero
   // or more characters.
   ResultSet rs = meta.getBestRowIdentifier(conn.getCatalog(), null, "survey",
       DatabaseMetaData.bestRowTemporary, false);
   while (rs.next()) {
     short actualScope = rs.getShort("SCOPE");
     String columnName = rs.getString("COLUMN_NAME");
     int dataType = rs.getInt("DATA_TYPE");
     String typeName = rs.getString("TYPE_NAME");
     int columnSize = rs.getInt("COLUMN_SIZE");
     short decimalDigits = rs.getShort("DECIMAL_DIGITS");
     short pseudoColumn = rs.getShort("PSEUDO_COLUMN");
     System.out.println("tableName=survey");
     System.out.println("scope=" + actualScope);
     System.out.println("columnName=" + columnName);
     System.out.println("dataType=" + dataType);
     System.out.println("typeName" + typeName);
     System.out.println("columnSize" + columnSize);
     System.out.println("decimalDigits" + decimalDigits);
     System.out.println("pseudoColumn" + pseudoColumn);
   }
   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>
   
  
 
  



If a table column can have a null value or not?

   <source lang="java">
 

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

 private static final String DRIVER = "com.mysql.jdbc.Driver";
 private static final String URL = "jdbc:mysql://localhost/testdb";
 private static final String USERNAME = "root";
 private static final String PASSWORD = "";
 public static void main(String[] args) throws Exception {
   Class.forName(DRIVER);
   Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
   Statement statement = connection.createStatement();
   ResultSet resultSet = statement.executeQuery("SELECT id, username FROM users");
   ResultSetMetaData metadata = resultSet.getMetaData();
   int nullability = metadata.isNullable(1);
   if (nullability == ResultSetMetaData.columnNullable) {
     System.out.println("Columns ID can have a null value");
   } else if (nullability == ResultSetMetaData.columnNoNulls) {
     System.out.println("Columns ID does not allowed to have a null value");
   } else if (nullability == ResultSetMetaData.columnNullableUnknown) {
     System.out.println("Nullability unknown");
   }
 }

}


 </source>
   
  
 
  



If a table column value is auto-increment?

   <source lang="java">
 

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

 private static final String DRIVER = "com.mysql.jdbc.Driver";
 private static final String URL = "jdbc:mysql://localhost/testdb";
 private static final String USERNAME = "root";
 private static final String PASSWORD = "";
 public static void main(String[] args) throws Exception {
   Class.forName(DRIVER);
   Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
   Statement statement = connection.createStatement();
   ResultSet resultSet = statement.executeQuery("SELECT id, username FROM users");
   ResultSetMetaData metadata = resultSet.getMetaData();
   if (metadata.isAutoIncrement(1)) {
     System.out.println("Column ID is an auto-increment column");
   }
 }

}


 </source>
   
  
 
  



Is Column A Cash Value

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // indicates whether the designated column is a cash value.
     System.out.println(rsMetaData.isCurrency(i));
   }
   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>
   
  
 
  



Is Column Auto Increase

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // indicates whether the designated column is
     // automatically numbered, thus read-only.
     System.out.println(rsMetaData.isAutoIncrement(i));
   }
   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>
   
  
 
  



Is Column Case Sensitive

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // Indicates whether a column"s case matters
     // in the designated column.
     System.out.println(rsMetaData.isCaseSensitive(i));
   }
   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>
   
  
 
  



Is Column Definitely Writable

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // indicates whether a write on the designated
     // column will definitely succeed.
     System.out.println(rsMetaData.isDefinitelyWritable(i));
   }
   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>
   
  
 
  



Is Column Nullable

   <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 rsColumns = null;
   DatabaseMetaData meta = conn.getMetaData();
   rsColumns = meta.getColumns(null, null, "survey", null);
   while (rsColumns.next()) {
     String columnName = rsColumns.getString("COLUMN_NAME");
     System.out.println("column name=" + columnName);
     String columnType = rsColumns.getString("TYPE_NAME");
     System.out.println("type:" + columnType);
     int size = rsColumns.getInt("COLUMN_SIZE");
     System.out.println("size:" + size);
     int nullable = rsColumns.getInt("NULLABLE");
     if (nullable == DatabaseMetaData.columnNullable) {
       System.out.println("nullable true");
     } else {
       System.out.println("nullable false");
     }
     int position = rsColumns.getInt("ORDINAL_POSITION");
     System.out.println("position:" + position);
     
   }
   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>
   
  
 
  



Is Column Nullable From ResultSet Metadata

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // indicates the nullability of values
     // in the designated column.
     System.out.println(rsMetaData.isNullable(i));
   }
   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>
   
  
 
  



Is Column Readonly

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // Indicates whether the designated column
     // is definitely not writable.
     System.out.println(rsMetaData.isReadOnly(i));
   }
   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>
   
  
 
  



Is Column Searchable

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // Indicates whether a column"s case matters
     // in the designated column.
     System.out.println(rsMetaData.isSearchable(i));
   }
   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>
   
  
 
  



Is Column Signed Number

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // indicates whether values in the designated
     // column are signed numbers.
     System.out.println(rsMetaData.isSigned(i));
   }
   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>
   
  
 
  



Is Column Writable

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column MetaData ");
     System.out.println("column number " + i);
     // indicates whether a write on the designated
     // column will succeed.
     System.out.println(rsMetaData.isWritable(i));
   }
   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>
   
  
 
  



Make Unique Column in 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 {
   Class.forName("com.mysql.jdbc.Driver");
   Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
       "root", "root");
   Statement st = con.createStatement();
   int n = st.executeUpdate("ALTER TABLE mytable ADD UNIQUE( colName)");
   System.out.println("Query OK, " + n + " rows affected.");
 }

}


 </source>
   
  
 
  



Remove Unique Column in 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 {
   Class.forName("com.mysql.jdbc.Driver");
   Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
       "root", "root");
   Statement st = con.createStatement();
   int n = st.executeUpdate("ALTER TABLE mytable DROP INDEX col");
   System.out.println("Query OK, " + n + " rows affected.");
 }

}


 </source>
   
  
 
  



Sum of Column 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 {
   int sum = 0;
   Class.forName("com.mysql.jdbc.Driver");
   Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
       "root", "root");
   Statement st = con.createStatement();
   ResultSet res = st.executeQuery("SELECT SUM(col) FROM mytable");
   while (res.next()) {
     int c = res.getInt(1);
     sum = sum + c;
   }
   System.out.println("Sum of column = " + sum);
 }

}


 </source>
   
  
 
  



Use DatabaseMetaData to get table column names

   <source lang="java">
 

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

 private static final String DRIVER = "com.mysql.jdbc.Driver";
 private static final String URL = "jdbc:mysql://localhost/testdb";
 private static final String USERNAME = "root";
 private static final String PASSWORD = "";
 public static void main(String[] args) throws Exception {
   Class.forName(DRIVER);
   Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
   DatabaseMetaData metadata = connection.getMetaData();
   ResultSet resultSet = metadata.getColumns(null, null, "users", null);
   while (resultSet.next()) {
     String name = resultSet.getString("COLUMN_NAME");
     String type = resultSet.getString("TYPE_NAME");
     int size = resultSet.getInt("COLUMN_SIZE");
     System.out.println("Column name: [" + name + "]; type: [" + type + "]; size: [" + size + "]");
   }
   connection.close();
 }

}


 </source>