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

akash-coded · 2022-03-21T05:32:57Z

akash-coded
Mar 21, 2022
Maintainer

JDBC Task - 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:
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 · 2022-03-21T06:27:18Z

Sarfinaa
Mar 21, 2022

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

0 replies

shreiyarandive · 2022-03-21T06:31:36Z

shreiyarandive
Mar 21, 2022

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

0 replies

lalit-ky · 2022-03-21T06:37:12Z

lalit-ky
Mar 21, 2022

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

0 replies

RohitNaikade1 · 2022-03-21T06:37:35Z

RohitNaikade1
Mar 21, 2022

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

0 replies

SrideviYamala · 2022-03-21T06:41:33Z

SrideviYamala
Mar 21, 2022

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

0 replies

Gaurav-Joshi-31 · 2022-03-21T06:41:35Z

Gaurav-Joshi-31
Mar 21, 2022

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.%[email protected]' 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);
        }
    }
}

0 replies

Khyati-tripathi · 2022-03-21T06:41:46Z

Khyati-tripathi
Mar 21, 2022

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

    }
}

0 replies

RohitN09 · 2022-03-21T06:42:16Z

RohitN09
Mar 21, 2022

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

            }
        }
    }
}

0 replies

VigneshVishwa · 2022-03-21T06:43:04Z

VigneshVishwa
Mar 21, 2022

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

0 replies

ratnadeepikavuddagiri · 2022-03-21T06:44:21Z

ratnadeepikavuddagiri
Mar 21, 2022

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

0 replies

abhiraj2k · 2022-03-21T06:44:28Z

abhiraj2k
Mar 21, 2022

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

0 replies

Shubhi27 · 2022-03-21T06:45:09Z

Shubhi27
Mar 21, 2022

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

0 replies

rakeshGit419 · 2022-03-21T06:46:07Z

rakeshGit419
Mar 21, 2022

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

0 replies

preeti-max · 2022-03-21T06:47:44Z

preeti-max
Mar 21, 2022

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

}

}

0 replies

vysh675 · 2022-03-21T06:49:26Z

vysh675
Mar 21, 2022

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

}

0 replies

Prem-3 · 2022-03-21T06:58:01Z

Prem-3
Mar 21, 2022

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

0 replies

charani712 · 2022-03-21T07:01:06Z

charani712
Mar 21, 2022

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

}

0 replies

SabaMhabar · 2022-03-21T07:02:45Z

SabaMhabar
Mar 21, 2022

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

}

0 replies

DivikaAgrawal · 2022-03-21T07:03:19Z

DivikaAgrawal
Mar 21, 2022

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

}

0 replies

Amrit-Raj22 · 2022-03-21T07:04:04Z

Amrit-Raj22
Mar 21, 2022

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

1 reply

Amrit-Raj22 Mar 31, 2022

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

hemanth409 · 2022-03-21T07:53:05Z

hemanth409
Mar 21, 2022

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

}

0 replies

bhanu-04 · 2022-03-21T08:52:02Z

bhanu-04
Mar 21, 2022

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

0 replies

ShraddhaPenshanwar · 2022-03-22T03:24:01Z

ShraddhaPenshanwar
Mar 22, 2022


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

0 replies

Ashwesh09 · 2022-03-22T03:35:16Z

Ashwesh09
Mar 22, 2022

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

0 replies

Krishreddy460 · 2022-04-04T17:12:18Z

Krishreddy460
Apr 4, 2022

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

0 replies

ujjwal18bit1167 · 2022-04-17T06:38:42Z

ujjwal18bit1167
Apr 17, 2022

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

0 replies

ghost · 2022-07-19T12:30:35Z

ghost
Jul 19, 2022

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

    }

}

0 replies

ApoorvaSinha · 2022-07-20T05:08:30Z

ApoorvaSinha
Jul 20, 2022

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

    }
}

0 replies

vshvandana · 2022-07-20T05:16:27Z

vshvandana
Jul 20, 2022

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

    }
}

0 replies

meghvi-raj · 2022-07-20T05:53:58Z

meghvi-raj
Jul 20, 2022

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

0 replies

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

Uh oh!

Uh oh!

akash-coded Mar 21, 2022 Maintainer

JDBC Task - 1

Part-2 in Discussion #59

Replies: 40 comments · 1 reply

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

akash-coded
Mar 21, 2022
Maintainer

Replies: 40 comments 1 reply