Java Tutorial/Database/Table

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>

Copy data from one table to another in a database

   <source lang="java">

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

}</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 a table in database

   <source lang="java">

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

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

Delete record from table

   <source lang="java">

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

}</source>

Description of Database Table

   <source lang="java">

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

}</source>

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

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