Java Tutorial/Database/Table

Adding a New Column Name in Database Table

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");
  }
}

Arrange a Column of Database Table

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

Change Column Name of a Table

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");
  }
}

Copy data from one table to another in a database

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

Copy One Database Table to Another

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();
    }
  }
}

Create a table in database

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    String url = "jdbc:oracle:thin:@localhost:1521:javaDemo";
    String username = "username";
    String password = "welcome";
    String sql = "CREATE TABLE books (id NUMBER(11), title VARCHAR2(64))";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement statement = connection.createStatement();
    statement.execute(sql);
    connection.close();
  }
}

Delete a Column from a Database Table

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!");
  }
}

Delete record from table

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] args) throws Exception {
    String url = "jdbc:mysql://localhost/sampledb";
    String username = "root";
    String password = "";
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection(url, username, password);
    String sql = "DELETE FROM users WHERE user_id = ?";
    int userId = 2;
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setInt(1, userId);
    int rows = statement.executeUpdate();
    System.out.println(rows + " record(s) deleted.");
    connection.close();
  }
}

Description of Database Table

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
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 rs = st.executeQuery("DESCRIBE myTable");
    ResultSetMetaData md = rs.getMetaData();
    int col = md.getColumnCount();
    for (int i = 1; i <= col; i++) {
      String col_name = md.getColumnName(i);
      System.out.println(col_name);
    }
    DatabaseMetaData dbm = con.getMetaData();
    ResultSet rs1 = dbm.getColumns(null, "%", "myTable", "%");
    while (rs1.next()) {
      String col_name = rs1.getString("COLUMN_NAME");
      String data_type = rs1.getString("TYPE_NAME");
      int data_size = rs1.getInt("COLUMN_SIZE");
      int nullable = rs1.getInt("NULLABLE");
      System.out.println(col_name + " " + data_type + "(" + data_size + ")");
      if (nullable == 1) {
        System.out.println("YES");
      } else {
        System.out.println("NO");
      }
    }
  }
}

Drop table from database

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    String url = "jdbc:oracle:thin:@localhost:1521:demo";
    String username = "java";
    String password = "";
    String sql = "DROP TABLE books";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement statement = connection.createStatement();
    statement.execute(sql);
    connection.close();
  }
}

Make Unique Column in Database Table

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.");
  }
}

Remove Unique Column in Database Table

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.");
  }
}

Sum of Column in a Database Table

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