Task - JDBC (1/2) - my_org DB Setup and Worker Schema Updation

[akash-coded]

JDBC Task - 1

1. Clone the org DB into a new database called my_org. The resultant DB should have the schema as well as the data.

   Cloned DB Schema:

   

2. Perform the following DB operations using a JDBC program:

   • Write a JDBC program to add a new field (column) called email into the Worker table. (Hint - Use execute() with Statement/PreparedStatement)

     Modified DB Schema:
     (To be updated)

   • After that, update all the existing records to set their email field as: <first_name[0]>.<last_name>@my_org.com. Note - The email must be in all lowercase. Example - a.das@my_org.com (Hint - Use JDBC batch update)

   • Replace ".com" in all the email addresses with ".in". Example - a.das@my_org.in (Hint - You can use MySQL REPLACE command)

Part-2 in Discussion #59

[Sarfinaa]

import java.sql.*;
import java.util.Arrays;

public class App {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3306/my_org";
        final String username = "root";
        final String password = "password";
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());

            try (Statement statement = connection.createStatement()) {
                // ADDING COLUMN EMAIL INTO WORKER
                String sql1 = "ALTER TABLE worker ADD email varchar(50) NOT NULL";
                String sql2 = "SELECT * FROM worker";
                statement.execute(sql1);
                // ADDING EMAILS TO EMAIL COLUMN
                ResultSet result1 = statement.executeQuery(sql2);
                while (result1.next()) {
                    int id = result1.getInt("WORKER_ID");
                    String firstName = result1.getString("FIRST_NAME").toLowerCase().substring(0, 1);
                    String lastName = result1.getString("LAST_NAME").toLowerCase();
                    String sql3 = String.format("UPDATE worker SET email='%s.%s@my_org.com' WHERE worker_id=%d",
                            firstName, lastName, id);
                    statement.addBatch(sql3);
                }
                statement.executeBatch();
                // UPDATING EMAILS
                ResultSet result2 = statement.executeQuery(sql2);
                while (result2.next()) {
                    int id = result2.getInt("WORKER_ID");

                    String sql3 = String.format("UPDATE worker SET email=REPLACE(email,'com','in') WHERE worker_id=%d",
                            id);
                    statement.addBatch(sql3);
                }
                statement.executeBatch();

            }
        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

[shreiyarandive]

import java.sql.*;

public class App {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3306/my_org";
        final String username = "root";
        final String password = "******";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());

            try (Statement statement = connection.createStatement()) {
                String sql = "ALTER TABLE Worker ADD email varchar(255)";
                statement.execute(sql);
                System.out.println("Table altered ");

                String sql2 = """
                        UPDATE Worker
                            SET
                                email = CONCAT(LOWER(SUBSTRING(FIRST_NAME, 1, 1)), ".", LOWER(LAST_NAME), "@my_org.com")""";
                int rowsAffected = statement.executeUpdate(sql2);
                System.out.println(rowsAffected + " row(s) affected");

                String sql3 = """
                        UPDATE Worker
                            SET
                                email = REPLACE(email, ".com", ".in")
                            """;
                int rowsAffectedTwo = statement.executeUpdate(sql3);
                System.out.println(rowsAffectedTwo + " row(s) affected");
            }

        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

[lalit-ky]

import java.sql.*;

public class App {
    static final String DB_URL = "jdbc:mysql://localhost:3307/my_org";
    static final String USER = "root";
    static final String PASS = "123654";

    static void showTable(Statement st) throws Exception {
        String display = "select * from worker";
        ResultSet displayRs = st.executeQuery(display);
        int rowCount = displayRs.getMetaData().getColumnCount();
        while (displayRs.next()) {
            for (int i = 1; i <= rowCount; i++) {
                System.out.print(displayRs.getObject(i) + "\t");
            }
            System.out.println();
        }
    }

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
                Statement st = conn.createStatement();) {

            String alterWorker = " Alter Table worker ADD email varchar(255)";
            st.execute(alterWorker);

            String select = "select worker_id,first_name,last_name from worker";
            ResultSet rs = st.executeQuery(select);
            while (rs.next()) {
                int worker_id = rs.getInt(1);
                String first_name = rs.getString(2);
                String last_name = rs.getString(3);
                String email = first_name.toLowerCase().charAt(0) + "." + last_name.toLowerCase() + "@my_org.com";
                String addEmail = "UPDATE worker SET email = '" + email + "' WHERE worker_id = " + worker_id;
                st.addBatch(addEmail);
            }
            int[] a = st.executeBatch();
            showTable(st);
            System.out.println("\n\n");
            String updateEmail = "UPDATE worker SET email = REPLACE(email, '@my_org.com', '@my_org.in')";
            st.executeUpdate(updateEmail);
            showTable(st);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

[RohitNaikade1]

task 1:
import java.sql.*;

public class task1 {
    public static void main(String[] args) {

        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            final String url = "jdbc:mysql://localhost:3307/my_org";
            final String username = "root";
            final String password = "admin";

            Connection con=DriverManager.getConnection(url, username, password);

            Statement stmt=con.createStatement();
            boolean res=stmt.execute("alter table Worker add email varchar(40);");

            String query="""
                SELECT distinct COLUMN_NAME
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = 'Worker'
                ORDER BY ORDINAL_POSITION;
                    """;
            ResultSet answer=stmt.executeQuery(query);
            while(answer.next()){
                System.out.println(answer.getString(1));
            }
        }catch(Exception e){
            System.out.println(e);
        }
        
    }
}


Task 2:
import java.sql.*;

public class task2 {
    public static void main(String[] args) {

        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            final String url = "jdbc:mysql://localhost:3307/my_org";
            final String username = "root";
            final String password = "admin";

            Connection con=DriverManager.getConnection(url, username, password);

            Statement stmt=con.createStatement();
            
            ResultSet answer=stmt.executeQuery("select * from worker;");

            while(answer.next()){
                String fname=answer.getString(2).toLowerCase();
                String sname=answer.getString(3).toLowerCase();
                String email=fname+"."+sname+"@my_org.com";
                String query=String.format("UPDATE worker SET email='%s.%s@@my_org.com' WHERE worker_id=%d",fname, sname, answer.getInt(1));
                stmt.addBatch(query);
            }
            stmt.executeBatch();
        }catch(Exception e){
            System.out.println(e);
        }
        
    }
}


Task 3:
import java.sql.*;

public class task3 {
    public static void main(String[] args) {

        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            final String url = "jdbc:mysql://localhost:3307/my_org";
            final String username = "root";
            final String password = "admin";

            Connection con=DriverManager.getConnection(url, username, password);

            Statement stmt=con.createStatement();
            
            ResultSet answer=stmt.executeQuery("select * from worker;");

            String query = """
                        UPDATE Worker
                            SET
                                email = REPLACE(email, ".com", ".in")
                            """;
                int rowsAffectedTwo = stmt.executeUpdate(query);
                System.out.println(rowsAffectedTwo + " row(s) affected");
        }catch(Exception e){
            System.out.println(e);
        }
        
    }
}

[SrideviYamala]

import java.sql.*;
public class JDBC_Task1 {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3306/my_org";
        final String username = "root";
        final String password = "root";
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            try (Statement statement = connection.createStatement()) {
                statement.execute("alter table worker drop column email");
                String addColSql = "ALTER TABLE worker ADD COLUMN email varchar(20) not null";
                statement.execute(addColSql);
                String updateSql = """
                            UPDATE worker
                            SET
                                email = concat(lower(substring(first_name,1,1)),".",
                                lower(last_name),"@my_org.com")
                            """;
                statement.execute(updateSql);
                String replaceSql = "update worker set email = replace(email,'com','in')";
                statement.execute(replaceSql);
            }
        }
        catch (SQLException e)
        {
            System.out.println(e);
        }
    }
}

[Gaurav-Joshi-31]

import java.sql.*;
import java.util.Arrays;

public class App {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3307/myorg";
        final String username = "root";
        final String password = "grv123";
        String sqlAlter = "ALTER TABLE worker ADD COLUMN email VARCHAR(30)";
        String sqlSelect = "Select * from worker";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {

            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());

            try (Statement statement = connection.createStatement()) {

                // statement.execute(sqlAlter);
                // System.out.println("Column added...");

                ResultSet result1 = statement.executeQuery(sqlSelect);
                while (result1.next()) {
                    int id = result1.getInt("WORKER_ID");
                    String firstName = result1.getString("FIRST_NAME").toLowerCase().substring(0, 1);
                    String lastName = result1.getString("LAST_NAME").toLowerCase();
                    String sql = String.format("UPDATE worker SET email='%s.%s@myorg.com' WHERE worker_id=%d",
                            firstName, lastName, id);
                    statement.addBatch(sql);
                }
                statement.executeBatch();

                ResultSet result2 = statement.executeQuery(sqlSelect);
                while (result2.next()) {
                    int id = result2.getInt("WORKER_ID");

                    String sql = String.format("UPDATE worker SET email=REPLACE(email,'com','in') WHERE worker_id=%d",
                            id);
                    statement.addBatch(sql);
                }
                statement.executeBatch();

            }

        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

[Khyati-tripathi]

import java.sql.*;
import java.util.Arrays;

public class task1 {

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3307/myorg";
        final String username = "root";
        final String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password);
                Statement st = connection.createStatement();) {
            String addColQuery = "ALTER TABLE WORKER ADD COLUMN email VARCHAR(255)";
            int res = st.executeUpdate(addColQuery);
            System.out.println(res);
            String sqlUpdate = """
                    UPDATE Worker
                        SET
                            email = CONCAT(LOWER(SUBSTRING(first_name, 1, 1)), ".", LOWER(last_name), "@my_org.com")""";
            int rows1 = st.executeUpdate(sqlUpdate);
            System.out.println(rows1);

            String changeExt = "UPDATE WORKER SET email= REPLACE(email,'.com','.in')";
            int res1 = st.executeUpdate(changeExt);
            System.out.println(res1);

        } catch (SQLException e) {
            System.out.println(e);
        }

    }
}

[RohitN09]

import java.sql.*;

public class AddEmail {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3307/my_org";
        final String user = "root";
        final String pass = "root";

        try (Connection con = DriverManager.getConnection(url, user, pass)) {
            try (Statement state = con.createStatement()) {
                String alter = "alter table worker add column EMAIL varchar(30)";
                state.executeUpdate(alter);

                String names = "select * from worker";
                ResultSet result = state.executeQuery(names);
                while (result.next()) {
                    String fname = result.getString("FIRST_NAME").toLowerCase();
                    String lname = result.getString("LAST_NAME").toLowerCase();

                    String formatUpdate = """
                            update worker
                            set email = ?
                            where first_name=?
                            and
                            last_name=?""";

                    try (PreparedStatement prep = con.prepareStatement(formatUpdate)) {
                        String field = fname.charAt(0) + "." + lname + "@my_org.com";
                        prep.setString(1, field);
                        prep.setString(2, fname);
                        prep.setString(3, lname);
                        prep.executeUpdate();
                    }
                }

                String update2 = """
                        update worker
                        set EMAIL = replace(EMAIL,".com",".in")""";
                int rowsUpdated = state.executeUpdate(update2);
                System.out.println(rowsUpdated + " rows were updated");

            }
        }
    }
}

[VigneshVishwa]

import java.sql.*;
import java.util.Arrays;

public class App {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3306/my_org";
        final String username = "root";
        final String password = "password";
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());

            try (Statement statement = connection.createStatement()) {
                String s1 = "ALTER TABLE worker ADD email varchar(50) ";
                String s2 = "SELECT * FROM worker";
                ResultSet resultset = statement.executeQuery(s2);
                while (resultset.next()) {
                    String insert = "UPDATE worker SET email=concat(lower(first_name),'.',lower(last_name),'@my_org.com')";
                    statement.addBatch(insert);
                }
            }statement.executeBatch();
                while (resultset.next()) {
                    String update = "UPDATE Worker SET email = REPLACE(email, '.com', '.in')";
                    statement.addBatch(update);
                }
                statement.executeBatch();     
            }
            
        } catch (SQLException e) {
            System.out.println(e);
        }
        
    }
}

[ratnadeepikavuddagiri]

import java.sql.*;
import java.util.Arrays;

public class JdbcTask {
    public static void main(String[] args) throws ClassNotFoundException{
    Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3306/my_org";
        final String username = "root";
        final String password = "root";

        
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());

            
            try (Statement statement = connection.createStatement()) {
                String query = "alter table worker add email varchar(255)";
                boolean result = statement.execute(query);
                String sql ="select * from worker";
                ResultSet resultset = statement.executeQuery(sql);
                while (resultset.next()) {
                    String insert = "UPDATE worker SET email=concat(lower(first_name),'.',lower(last_name),'@my_org.com')";
                    statement.addBatch(insert);
                }
                statement.executeBatch();
                while (resultset.next()) {
                    String update = "UPDATE Worker SET email = REPLACE(email, '.com', '.in')";
                    statement.addBatch(update);
                }
                statement.executeBatch();     
            }
            
        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

[abhiraj2k]

import java.sql.*;

public class App {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3306/myOrg";
        final String username = "root";
        final String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected");
            try (Statement st = connection.createStatement();) {
                int modified;
                String addEmail = "ALTER TABLE worker ADD EMAIL VARCHAR(150)";
                modified = st.executeUpdate(addEmail);
                System.out.println("Modified " + modified + " row/s");

                String updateEmail = """
                        UPDATE worker
                        SET EMAIL = CONCAT(
                            LOWER(worker.FIRST_NAME),
                            LOWER(worker.LAST_NAME),
                            '@gmail.com'
                            )
                        WHERE WORKER_ID = ?""";
                ResultSet rs = st.executeQuery("SELECT WORKER_ID FROM worker");
                try (PreparedStatement ps = connection.prepareStatement(updateEmail);) {

                    while (rs.next()) {
                        ps.setInt(1, rs.getInt("WORKER_ID"));
                        modified = ps.executeUpdate();
                        System.out.println("Modified" + modified);
                    }
                }

                rs = st.executeQuery("SELECT WORKER_ID FROM worker");
                String replaceEmail = """
                        UPDATE worker
                        SET EMAIL = REPLACE(worker.EMAIL, '.com', '.in')
                        WHERE WORKER_ID = """;

                while (rs.next()) {
                    st.addBatch(replaceEmail + rs.getInt("WORKER_ID"));
                }

                int[] recordsAffected = st.executeBatch();
                System.out.println("Modified " + recordsAffected.length + " rows");

            }
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

[Shubhi27]

import java.sql.*;

public class App {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3307/my_org";
        final String username = "root";
        final String password = "********";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());

            try (Statement statement = connection.createStatement()) {
                // Adding column email
                String sql = "ALTER TABLE worker ADD COLUMN email VARCHAR(25)";
                String sql1 = "SELECT * FROM worker";
                statement.execute(sql);
                System.out.println("Column added...");

                ResultSet rs = statement.executeQuery(sql1);

                // Set email fields
                while (rs.next()) {
                    int workerId = rs.getInt(1);
                    String firstName = rs.getString(2);
                    String lastName = rs.getString(3);
                    String email = firstName.toLowerCase().charAt(0) + "." + lastName.toLowerCase() + "@my_org.com";
                    String sql2 = "UPDATE worker SET email = '" + email + "' WHERE worker_id = " + workerId;
                    statement.addBatch(sql2);
                }
                statement.executeBatch();

                // Updating values
                String sql3 = "UPDATE worker SET email = REPLACE(email, '@my_org.com', '@my_org.in')";
                statement.executeUpdate(sql3);
            }

        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

[rakeshGit419]

import java.sql.*;

public class App {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        final String url = "jdbc:mysql://localhost:3306/my_org";
        final String username = "ShowBot";
        final String password = "XXXXXXX";//private

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());

            try (Statement st= connection.createStatement()) {
                String q1= "alter table Worker add email varchar(255)";
                st.execute(q1);//2

                String q2 = "UPDATE Worker set email = concat(Lcase(left(FIRST_NAME, 1)), '.', lcase(LAST_NAME), '@my_org.com')";
                st.executeUpdate(q2);//3
                
                String q3 = "update worker set email = replace(email,'com','in')";
                st.executeUpdate(q3);//4
            }

        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

[preeti-max]

`sql import java.sql.*;

public class App {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
final String url = "jdbc:mysql://localhost:3306/my_org";
final String username = "root";
final String password = "1234";
try (Connection con = DriverManager.getConnection(url, username, password)) {
try (Statement st = con.createStatement()) {
String query = "Alter Table worker ADD COLUMN email varchar(30)";
String query2 = "UPDATE worker set email=concat(substring(LOWER(first_name),1,1),'.',LOWER(last_name),'@myorg.com')";

            st.execute(query);
            st.executeUpdate(query2);
            String query3 = """
                    UPDATE Worker
                        SET email = REPLACE(email, ".com", ".in")
                        """;
            st.executeUpdate(query3);

        }

        // Select concat(substring(LOWER(first_name),1,1),LOWER(last_name),'@myorg.com'
        // )from worker;

    } catch (SQLException e) {
        System.out.println(e);
    }

}

}

[vysh675]

import java.sql.*;

public class editemail {
public static void main(String[] args) {

    try{
        Class.forName("com.mysql.cj.jdbc.Driver");
        final String url = "jdbc:mysql://localhost:3307/my_org";
        final String username = "root";
        final String password = "admin";

        Connection con=DriverManager.getConnection(url, username, password);

        Statement stmt=con.createStatement();
        
        ResultSet ans=stmt.executeQuery("select * from worker;");

        while(ans.next()){
            String fname=ans.getString(2).toLowerCase();
            String sname=ans.getString(3).toLowerCase();
            String email=fname+"."+sname+"@my_org.com";
            String query=String.format("UPDATE worker SET email='%s.%s@@my_org.com' WHERE worker_id=%d",fname, sname, ans.getInt(1));
            stmt.addBatch(query);
        }
        stmt.executeBatch();
    }catch(Exception e){
        System.out.println(e);
    }
    
}

}

[Prem-3]

import java.sql.*;

public class App {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        final String url = "jdbc:mysql://localhost:3306/my_org";
        final String username = "root";
        final String password = "Premkumar_pk";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());

            try (Statement st= connection.createStatement()) {
                String query = "alter table Worker add email varchar(255)";
                st.execute(sql);
                String sql = "UPDATE Worker set email = concat(Lcase(FIRST_NAME, 1)), '.', Lcase(LAST_NAME), '@my_org.com')";
                st.executeUpdate(sql);               
            }
        } 
catch (SQLException e) {
        System.out.println(e);
        }
    }
}

[charani712]

import java.sql.*;

public class App {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
final String url = "jdbc:mysql://localhost:3306/my_org";
final String username = "root";
final String password = "pass";

    try (Connection connection = DriverManager.getConnection(url, username, password)) {
        System.out.println("Connected to database server "
                + connection.getMetaData().getDatabaseProductName()
                + " version: "
                + connection.getMetaData().getDatabaseProductVersion());

        try (Statement statement = connection.createStatement()) {
            String sql = "alter table Worker add email varchar(255)";
            statement.execute(sql);

            String sql1 = "UPDATE Worker set email = concat(Lcase(left(FIRST_NAME, 1)), '.', lcase(LAST_NAME), '@my_org.com')";
            statement.executeUpdate(sql1);
            

        }

    } catch (SQLException e) {
        System.out.println(e);
    }
}

}

[SabaMhabar]

import java.sql.*;

class App {
public static void main(String args[]) throws ClassNotFoundException {
Class.forName("com.mysql.cj.jdbc.Driver");

    final String url = "jdbc:mysql://localhost:3306/jdbc_demo";
    final String username = "root";
    final String password = "root123";

    try (Connection connection = DriverManager.getConnection(url, username, password)) {
        System.out.println("Connected to database server "
                + connection.getMetaData().getDatabaseProductName()
                + " version: "
                + connection.getMetaData().getDatabaseProductVersion());

        try (Statement statement = connection.createStatement()) {
            String sql = "ALTER TABLE Worker ADD email varchar(255)";
            statement.execute(sql);
            System.out.println("Table altered ");

            String sql2 = """
                    UPDATE Worker
                        SET
                            email = CONCAT(LOWER(SUBSTRING(FIRST_NAME, 1, 1)), ".", LOWER(LAST_NAME), "@my_org.com")""";
            int rowsAffected = statement.executeUpdate(sql2);
            System.out.println(rowsAffected + " row(s) affected");

            String sql3 = """
                    UPDATE Worker
                        SET
                            email = REPLACE(email, ".com", ".in")
                        """;
            int rowsAffectedTwo = statement.executeUpdate(sql3);
            System.out.println(rowsAffectedTwo + " row(s) affected");
        }

    } catch (SQLException e) {
        System.out.println(e);
    }
}

}

[DivikaAgrawal]

java.sql.*;

public class App{
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");

    final String url = "jdbc:mysql://localhost:3306/my_org";
    final String username = "root";
    final String password = "root";
    try (Connection connection = DriverManager.getConnection(url, username, password)) {
        try (Statement statement = connection.createStatement()) {
            statement.execute("alter table worker drop column email");
            String addColSql = "ALTER TABLE worker ADD COLUMN email varchar(20) not null";
            statement.execute(addColSql);
            String updateSql = """
                        UPDATE worker
                        SET
                            email = concat(lower(substring(first_name,1,1)),".",
                            lower(last_name),"@my_org.com")
                        """;
            statement.execute(updateSql);
            String replaceSql = "update worker set email = replace(email,'com','in')";
            statement.execute(replaceSql);
        }
    }
    catch (SQLException e)
    {
        System.out.println(e);
    }
}

}

[Amrit-Raj22]

import java.sql.*;

public class App {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3306/org";
        final String username = "root";
        final String password = "Amritraj@1998";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());

            try (Statement statement = connection.createStatement()) {
                String str = "ALTER TABLE Worker ADD EmailEmp varchar(255)";
                statement.execute(str);

                String sql = """
                        UPDATE Worker SET email = CONCAT(LOWER(SUBSTRING(first_name, 1, 1)), ".", LOWER(last_name), "@my_org.com"
                        )""";
                statement.executeUpdate(sql);

                String changeExt = "UPDATE WORKER SET email= REPLACE(email,'.com','.in')";
                statement.executeUpdate(changeExt);

            }
        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

[Amrit-Raj22]

@akash-coded All images in jdbc whiteboard are broke.

[hemanth409]

import java.sql.*;

public class App {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
final String url = "jdbc:mysql://localhost:3306/my_org";
final String username = "Test";
final String password = "test123";

    try (Connection connection = DriverManager.getConnection(url, username, password)) {
        System.out.println("Connected to database server "
                + connection.getMetaData().getDatabaseProductName()
                + " version: "
                + connection.getMetaData().getDatabaseProductVersion());

        try (Statement st= connection.createStatement()) {
            String q1= "alter table Worker add email varchar(255)";
            st.execute(q1);//2

            String q2 = "UPDATE Worker set email = concat(Lcase(left(FIRST_NAME, 1)), '.', lcase(LAST_NAME), '@my_org.com')";
            st.executeUpdate(q2);//3
            
            String q3 = "update worker set email = replace(email,'com','in')";
            st.executeUpdate(q3);//4
        }

    } catch (SQLException e) {
        System.out.println(e);
    }
}

}

[bhanu-04]

import java.sql.*;
import java.util.Arrays;

public class App {
    public static void main(String[] args) throws ClassNotFoundException{
    Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3306/my_org";
        final String username = "root";
        final String password = "root";

        
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());

            
            try (Statement statement = connection.createStatement()) {
                String query = "alter table worker add email varchar(255)";
                boolean result = statement.execute(query);
                String sql ="select * from worker";
                ResultSet resultset = statement.executeQuery(sql);
                while (resultset.next()) {
                    String insert = "UPDATE worker SET email=concat(lower(first_name),'.',lower(last_name),'@my_org.com')";
                    statement.addBatch(insert);
                }
                statement.executeBatch();
                while (resultset.next()) {
                    String update = "UPDATE Worker SET email = REPLACE(email, '.com', '.in')";
                    statement.addBatch(update);
                }
                statement.executeBatch();     
            }
            
        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

[ShraddhaPenshanwar]

public class App {
    public static void main(String[] args) throws Exception {

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_org", "root", "123");
            Statement stmt = con.createStatement();

             stmt.execute("ALTER TABLE worker ADD COLUMN email VARCHAR(50);");
            String sql = "SELECT * FROM worker";
             ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
            int id = rs.getInt("WORKER_ID");
            String firstName = rs.getString("FIRST_NAME").toLowerCase().substring(0, 1);
            String lastName = rs.getString("LAST_NAME").toLowerCase();
            String sql3 = String.format("UPDATE worker SET email='%s.%s@my_org.com' WHERE
            worker_id=%d",
            firstName, lastName, id);
            stmt.addBatch(sql3);
            }

            stmt.executeBatch();
            // UPDATING EMAILS
            ResultSet result2 = stmt.executeQuery(sql);
            while (result2.next()) {
            int id = result2.getInt("WORKER_ID");

            String sql3 = String.format("UPDATE worker SET
            email=REPLACE(email,'com','in') WHERE worker_id=%d",
            id);
            stmt.addBatch(sql3);
            }
            stmt.executeBatch();

            con.close();

        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

[Ashwesh09]

import java.sql.*;

public class App {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3307/my_org";
        final String username = "root";
        final String password = "*****";
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());
            try (Statement st = connection.createStatement()) {
                // ADDING COLUMN EMAIL INTO WORKER
                String alterTableQ = "ALTER TABLE worker ADD email varchar(50) NOT NULL";
                String showTableQ = "SELECT * FROM worker";
               st.execute(alterTableQ);
                // ADDING EMAILS TO EMAIL COLUMN
                ResultSet res =st.executeQuery(showTableQ);
                while (res.next()) {
                    int id = res.getInt("WORKER_ID");
                    String firstName = res.getString("FIRST_NAME").toLowerCase();
                    firstName = firstName.substring(0, 1);
                    String lastName = res.getString("LAST_NAME").toLowerCase();
                    String sql3 = "UPDATE worker SET email='" + firstName + "." + lastName + "@my_org.com' WHERE worker_id=" + id + ";";
                    st.addBatch(sql3);
                }
               st.executeBatch();
                // UPDATING EMAILS
                String changeEmail = "UPDATE WORKER SET email= REPLACE(email,'.com','.in')";
                int res1 = st.executeUpdate(changeEmail);
                System.out.println(res1);
            }
        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

[Krishreddy460]

package jdbc_tasks;

import java.sql.*;
import java.util.Arrays;

public class App {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");

    final String url = "jdbc:mysql://localhost:3306/org";
    final String username = "root";
    final String password = "Vamsireddy@460";
    Connection connection = DriverManager.getConnection(url, username, password);
        System.out.println("Connected to database server "
                + connection.getMetaData().getDatabaseProductName()
                + " version: "
                + connection.getMetaData().getDatabaseProductVersion());

        Statement statement = connection.createStatement() ;
            // ADDING COLUMN EMAIL INTO WORKER
            String sql1 = "ALTER TABLE worker ADD mmmail varchar(50) NOT NULL";
            String sql2 = "SELECT * FROM worker";
            statement.execute(sql1);
            // ADDING EMAILS TO EMAIL COLUMN
            ResultSet result1 = statement.executeQuery(sql2);
            while (result1.next()) {
                int id = result1.getInt("WORKER_ID");
                String firstName = result1.getString("FIRST_NAME").toLowerCase().substring(0, 1);
                String lastName = result1.getString("LAST_NAME").toLowerCase();
                String email=firstName+"."+lastName+"@org.com";
                String sql3 = String.format("UPDATE worker SET mmmail='%s.%s@my_org.com' WHERE worker_id=%d",
                        firstName, lastName, id);
               String s3= "UPDATE worker SET mmmail='"+email+"' WHERE worker_id="+id;
                statement.addBatch(sql3);
            }
            statement.executeBatch();

            ResultSet result = statement.executeQuery(sql2);
            while (result.next()) {
                int id = result.getInt("WORKER_ID");
                String firstName = result.getString("FIRST_NAME").toLowerCase().substring(0, 1);
                String lastName = result.getString("LAST_NAME").toLowerCase();
                String mail=result.getString("mmmail");
                System.out.println(id+" "+firstName+" "+lastName+" "+mail);
                
                String sql3 = String.format("UPDATE worker SET email=REPLACE(email,'com','in') WHERE worker_id=%d",
                        id);
                statement.addBatch(sql3);
            }
            statement.executeBatch();
            }
            
            
        
    
    }

[ujjwal18bit1167]

import java.sql.*;

public class App {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3306/org";
        final String username = "root";
        final String password = "Ujjwal@123";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());

            try (Statement statement = connection.createStatement()) {
                String str = "ALTER TABLE Worker ADD EmailEmp varchar(255)";
                statement.execute(str);

                String sql = """
                        UPDATE Worker SET email = CONCAT(LOWER(SUBSTRING(first_name, 1, 1)), ".", LOWER(last_name), "@my_org.com"
                        )""";
                statement.executeUpdate(sql);

                String changeExt = "UPDATE WORKER SET email= REPLACE(email,'.com','.in')";
                statement.executeUpdate(changeExt);

            }
        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

[ghost]

import java.util.*;

import java.sql.*;

public class task1 {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        final String url = "jdbc:mysql://localhost:3307/my_org";
        final String username = "root";
        final String password = "password";
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());

            try (Statement statement = connection.createStatement()) {
                String query1 = """
                        alter table worker add column email varchar(50);
                        """;
                 statement.execute(query1);
                String query2 = "SELECT * FROM worker";
                ResultSet result1 = statement.executeQuery(query2);
                while (result1.next()) {
                    int id = result1.getInt("WORKER_ID");
                    String firstName = result1.getString("FIRST_NAME").toLowerCase().substring(0, 1);
                    String lastName = result1.getString("LAST_NAME").toLowerCase();
                    String query3 = String.format("UPDATE worker SET email='%s.%s@my_org.com' WHERE worker_id=%d",
                            firstName, lastName, id);
                    statement.addBatch(query3);
                }
                statement.executeBatch();
                String query3 = "SELECT * FROM worker";
                ResultSet result2 = statement.executeQuery(query3);
                while (result2.next()) {

                    String query4 = String.format(
                            "UPDATE worker SET email=REPLACE(email,'com','in')");

                    statement.addBatch(query4);
                }
                statement.executeBatch();

            }
        } catch (SQLException e) {
            System.out.println(e);
        }

    }

}

[ApoorvaSinha]

package main;
import java.math.BigDecimal;
import java.sql.*;
import java.text.DecimalFormat;
import java.util.Arrays;

public class App {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver"); // optional

        final String url = "jdbc:mysql://localhost:3307/my_org";
        final String username = "root";
        final String password = "password";

        System.out.println("Establishing a connection to the database...\n");
        try (Connection connection = DriverManager.getConnection(url, username, password);) {
            /* Fetching some DB metadata */
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion()
                    + "\n");

            System.out.println("USING STATEMENT:: ");
            try (Statement statement = connection.createStatement()) {
     
               //Task 2.a) 
                System.out.println("Update Email");
                String updatecol="""
                		ALTER TABLE worker 
                		ADD email varchar(50) 
                		NOT NULL""";
                
                selectAllSql = """
                        SELECT
                            *
                        FROM
                        Worker
                        """;
             
                statement.execute(updatecol);
                ResultSet result1 = statement.executeQuery(selectAllSql);
                //task2.b)
                while (result1.next()) {
                    int id = result1.getInt("WORKER_ID");
                    String firstName = result1.getString("FIRST_NAME").toLowerCase().substring(0, 1);
                    String lastName = result1.getString("LAST_NAME").toLowerCase();
                    String sql3 = String.format("UPDATE worker SET email='%s.%s@my_org.com' WHERE worker_id=%d",firstName,lastName,id);
                    statement.addBatch(sql3);
                }
                statement.executeBatch();
                
                ResultSet result2 = statement.executeQuery(selectAllSql);
                //task 2.c)
                while (result2.next()) {
                    int id = result2.getInt("WORKER_ID");

                    String sql3 = String.format("UPDATE worker SET email=REPLACE(email,'com','in') WHERE worker_id=%d",
                            id);
                    statement.addBatch(sql3);
                }
                statement.executeBatch();
            }
            
            
            
        } catch (SQLException e) {
            System.out.println(e);
        }

    }
}

[vshvandana]

import java.sql.*;
import java.util.Arrays;

public class App {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver"); // optional

        final String url = "jdbc:mysql://localhost:3307/my_org";
        final String username = "root";
        final String password = "password";

        System.out.println("Establishing a connection to the database...\n");
        try (Connection connection = DriverManager.getConnection(url, username, password);) {
            /* Fetching some DB metadata */
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion()
                    + "\n");
            // Creating a field email

            try (Statement statement = connection.createStatement()) {
                String q1 = "ALTER TABLE worker Add email varchar(30) NOT NULL";
                statement.execute(q1);

                String q2 = "Select * from worker";
                ResultSet r = statement.executeQuery(q2);
                while (r.next()) {
                    String q3 = """
                                UPDATE worker
                            SET email =CONCAT(LOWER(Substring(first_name,1,1)),".",LOWER(LAST_NAME),"@my_org.com")""";
                    statement.addBatch(q3);
                }
                statement.executeBatch();
                ResultSet rs = statement.executeQuery(q2);
                while (rs.next()) {
                    String q4 = """
                            Update Worker Set email= REPLACE(email,".com",".in")
                            """;
                    statement.addBatch(q4);
                }
                statement.executeBatch();

            }
        } catch (SQLException e) {
            System.out.println(e);
        }

    }
}

[meghvi-raj]

import java.sql.*;
import java.util.Arrays;

public class D58

{

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        final String url = "jdbc:mysql://localhost:3307/my_org";
        final String username = "root";
        final String password = "password";
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected to database server "
                    + connection.getMetaData().getDatabaseProductName()
                    + " version: "
                    + connection.getMetaData().getDatabaseProductVersion());

            // ADDING COLUMN TO WORKER TABLE
            try (Statement statement = connection.createStatement()) {
                String sql1 = "ALTER TABLE worker ADD email varchar(50) NOT NULL";
                String sql2 = "SELECT * FROM worker";
                statement.execute(sql1);
                ResultSet result1 = statement.executeQuery(sql2);
                while (result1.next()) {
                    int id = result1.getInt("WORKER_ID");
                    String firstName = result1.getString("FIRST_NAME").toLowerCase().substring(0, 1);
                    String lastName = result1.getString("LAST_NAME").toLowerCase();
                    String sql3 = String.format("UPDATE worker SET email='%s.%s@my_org.com' WHERE worker_id=%d",
                            firstName, lastName, id);
                    statement.addBatch(sql3);

                }
                statement.executeBatch();
                ResultSet result2 = statement.executeQuery(sql2);
                while (result2.next()) {
                    int id = result2.getInt("WORKER_ID");

                    String sql3 = String.format("UPDATE worker SET email=REPLACE(email,'com','in') WHERE worker_id=%d",
                            id);
                    statement.addBatch(sql3);
                }
                statement.executeBatch();

            }
        }

        catch (SQLException e) {
            System.out.println(e);
        }
    }
}