Blog

Fetch Data From Google Sheet

let offset = 'A2';
let limit = 'Z999';
const sheetid = "<sheetid>"; 

const apikey = "<api key>"; 

const sheetname = "Sheet1";
const url = `https://sheets.googleapis.com/v4/spreadsheets/${sheetid}/values/${sheetname}!${offset}:${limit}?key=${apikey}`;

var sheet_data = [];

fetch(url).then(data => data.json()).then(data => { 
	sheet_data = data.values;
	console.table(sheet_data);
});
  • https://docs.google.com/spreadsheets/d/sheetid/edit?usp=sharing
  • create/get apikey from https://console.developers.google.com/apis/credentials

Assignment

===========================================================================
Week 1 Assignment
===========================================================================

  1. WAP to read radius of circle and calculate Area and Circumference
  2. WAP to read 3 numbers and find their mean
  3. WAP to read 2 numbers and find Sum of their last digit
  4. WAP to read 4 digit number and sum of its digits
  5. WAP to read radius of Sphere and find its Volume
  6. WAP to read 3 digit number and sum of its digit
  7. WAP to read 4 digit number and find reverse of that number
  8. WAP to read temperature in degree Celsius and convert it into Fahrenheit
  9. WAP to read value in inches and print it in feet and inches
  10. WAP to read marks of 5 subjects and print total and percentage
  11. Create radio button
    red blue yellow green
    on change of radio button change background color of body
  12. Create 2 select box and move items from left to right and vice versa
  13. Create 3 select box country state city
  • on change of country load states
  • on change of state load cities

Programs on If Statement

SWITCH Statement

===========================================================================
Week 2 Assignment
===========================================================================

Loops Programs

Arrays Programs (One Dimensional)

Arrays Programs (Multi Dimensional)

JSON

  1. Create JSON Object with following information
    Roll Number, Name, Age, Sex
    EmpNo, Name, Job, Sal
    Name, Wages, WorkingDays
  2. WAP to create array of 5 json objects worker and display average payment
    object parameter: name,wages,working_days
  3. WAP to create array of 5 json objects student and print all student records who has greater than average marks
    roll_no, name, marks

AJAX (fetch API)

  1. WAS to make AJAX call for following operations
    1. Input a number in text box and get square of that number from php script
    2. Input 2 numbers in text box and get addition of two numbers from php script
    3. Input radius in text box and find area and circumference of circle from php script
    4. Input length and breadth in text box and find area of rectangle from php script
    5. Input a number in text box and find factorial of number from php script
  2. WAP to print users data in html table by making ajax call to
    • https://jsonplaceholder.typicode.com/users
    • https://reqres.in/api/users (should have column to display thumbnail of user avatar)

===========================================================================
Week 3 Assignment
===========================================================================

  1. Design a class Rectangle
    • data members:
      1.  length
      2. breadth
    • member function / method: 
      1. setDimension()
      2. area()
      3. perimeter()
  2. Design a class Worker
    • data members:
      1. wages
      2. wdays
    • member function / method:
      1. setData()
      2. payment()
  3. Design a class Box
    • data members:
      1. length
      2. breadth
      3. height
    • member functions / methods:
      1. setDimension()
      2. volume()
  4. Design a class Rectangle
    • data members:
      1. length
      2. breadth
    • member functions / methods:
      1. setDimension()
      2. area()
      3. perimeter()
  5. Design a class Box
    • data members:
      1. length
      2. breadth
      3. height
    • member functions / methods:
      1. volume()
  6. Design a class Account
    • data members:
      1. account_number
      2. balance
    • member functions / methods:
      1. deposit()
      2. withdraw()
      3. showBalance()
  7. Design a class Set
    • data members:
      1. 3 numbers
    • member functions / methods:
      1. SUM()
      2. MEAN()
      3. MAX()
      4. MIN()
  8. Design a class Student
    • data members:
      1. roll_number
      2. name
    • member functions / methods:
      1. setData()
      2. getData()
  9. Design a class Account
    • data members:
      1. account_number
      2. balance
      3. interest_rate
        NOTE: interest_rate must be shared by all objects and its default value 10.25
    • member function:
      1. interest(no_of_years)
  10. Design a class Student
    • data members:
      1. roll_number
      2. name
    • member functions / methods:
      1. setData()
      2. getData()
        NOTE: roll_number must be automatically generated.It also keep track of total number of Students.
  11. Design a class Student
    • data members:
      1. roll_number
      2. name
    • member functions / methods:
      1. getData()
      2. showData()
        Create Array of 5 Student objects
  12. Create array of Student Objects
    • data members:
      1. roll_no
      2. name
      3. college
    • member functions / methods:
      1. readData()
      2. showData()
  13. Create a class RBI
    • data members:
      1. account_number
      2. balance
    • member functions / methods:
      1. deposit(int amt)
      2. withdraw(int amt)
      3. showBalance()

Fun with JS CSS and HTML

  1. Create radio button red blue yellow green on change of radio button change background color of body
  2. Create 2 select box and move items from left to right and vice versa
  3. Create 3 select box country state city a) on change of country load states b) on change of state load cities
    NOTE: Use AJAX call and fetch country states and cities from mysql database
  4. Create tic-tac-toe game.
    1. single player with computer
    2. multiplayer with manual event

Hibernate 5.4 Configuration

 

Core Hibernate 5.4 Configuration

<project xmlns="http://maven.apache.org/POM/4.0.0"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>HibernateProj</groupId>
  <artifactId>HibernateProj</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <build>
    <sourceDirectory>src</sourceDirectory>
    <plugins>
      <plugin>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.0</version>
        <configuration>
          <source>1.8</source>
          <target>1.8</target>
        </configuration>
      </plugin>
    </plugins>
  </build>
  <dependencies>
    <!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-core</artifactId>
      <version>5.4.10.Final</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.15</version>
    </dependency>
  </dependencies>
</project>
<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
    	<!-- <property name="hibernate.current_session_context_class">thread</property> -->
    	<property name="hibernate.current_session_context_class">org.hibernate.context.internal.ThreadLocalSessionContext</property>
        <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.password"></property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="show_sql">true</property>
    </session-factory>
</hibernate-configuration>
package com;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;

public class MainClass {
  @SuppressWarnings("deprecation")
  public static void main(String[] args) {

    try{  
      Class.forName("com.mysql.cj.jdbc.Driver");  
      Connection con=DriverManager.getConnection(  
          "jdbc:mysql://localhost:3306/test","root","");  
      //here sonoo is database name, root is username and password  
      Statement stmt=con.createStatement();
//			stmt.executeUpdate("insert into emp VALUES (null, 'shailesh', 31, 'Nagpur')");
      ResultSet rs=stmt.executeQuery("select * from emp");  
      while(rs.next())
        System.out.println(rs.getString(1)+"  "+rs.getString(2)+"  "+rs.getString(3)+"  "+rs.getString(4));  
      con.close();  
    } catch(Exception e){ System.out.println(e);}  

    System.out.println("Hibernate connection established successfully...");

    Session session = new Configuration().configure("hibernate.cfg.xml").buildSessionFactory().openSession();
    Query query = session.createNativeQuery("select * from emp");
    List<Student[]> list = query.list();
    System.out.println(list);

    for(Object[] obj : list) {
      Student std = new Student((Integer)obj[0], (String)obj[1], (Integer)obj[2], (String)obj[3]);
      System.out.println(std);
    }		
    ////////////////////////////////////////////////
    Query query2 = session.createNativeQuery("Select id, name, age, city from emp");
    query2.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
    List<Student> students = query2.list();
    System.out.println(students);
    session.close();
    //==============================//
    Session session2 = new Configuration().configure("database.cfg.xml").buildSessionFactory().openSession();
    List list2 = session2.createNativeQuery("select * from testdmarc").list();
    System.out.println(list2);
  }
}

 

Reference:

https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#sql

https://tools.jboss.org/downloads/jbosstools/2019-09/4.13.0.Final.html#update_site

https://www.tutorialspoint.com/hibernate/hibernate_examples.htm

Hibernate Native SQL Query Example

Servlet File Upload

Ref:
https://www.codejava.net/java-ee/servlet/java-file-upload-example-with-servlet-30-api

https://www.baeldung.com/upload-file-servlet

Servlet 3 File Upload – @MultipartConfig, Part

import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.MultipartConfig;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.Part;

/**
 * Servlet implementation class MultiPartServlet
 */
@WebServlet("/multiPartServlet")
@MultipartConfig(fileSizeThreshold=1024*1024*10, 	// 10 MB 
maxFileSize=1024*1024*50,      	// 50 MB
maxRequestSize=1024*1024*100)   	// 100 MB
public class MultiPartServlet extends HttpServlet {
  private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public MultiPartServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

  /**
   * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
   */
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    response.getWriter().append("Served at: ").append(request.getContextPath());
    
    PrintWriter out = response.getWriter();
  }

  private static final String SAVE_DIR = "uploadFiles";
  
  /**
   * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
   */
  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    
    // gets absolute path of the web application
        String appPath = request.getServletContext().getRealPath("");
        // constructs path of the directory to save uploaded file
        String savePath = appPath + File.separator + SAVE_DIR;
         
        // creates the save directory if it does not exists
        File fileSaveDir = new File(savePath);
        if (!fileSaveDir.exists()) {
            fileSaveDir.mkdir();
        }
    
    
    for (Part part : request.getParts()) {
        String fileName = extractFileName(part);
//		    part.write(fileName);
        response.getWriter().println("/home/shailesh/eclipse-workspace/TestProject/WebContent/images/" + fileName);
        part.write("/home/shailesh/eclipse-workspace/TestProject/WebContent/images/" + fileName);
    }
    response.getWriter().println("Uploaded...");
    doGet(request, response);
  }
  
  private String extractFileName(Part part) {
      String contentDisp = part.getHeader("content-disposition");
      String[] items = contentDisp.split(";");
      for (String s : items) {
          if (s.trim().startsWith("filename")) {
              return s.substring(s.indexOf("=") + 2, s.length()-1);
          }
      }
      return "";
  }

}

 

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>TestProject</groupId>
  <artifactId>TestProject</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>
  <build>
    <sourceDirectory>src</sourceDirectory>
    <plugins>
      <plugin>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.0</version>
        <configuration>
          <source>1.8</source>
          <target>1.8</target>
        </configuration>
      </plugin>
      <plugin>
        <artifactId>maven-war-plugin</artifactId>
        <version>3.2.3</version>
        <configuration>
          <warSourceDirectory>WebContent</warSourceDirectory>
        </configuration>
      </plugin>
    </plugins>
  </build>
  <dependencies>
  	<!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
  <dependency>
    	<groupId>org.springframework</groupId>
   	 	<artifactId>spring-webmvc</artifactId>
    	<version>5.2.1.RELEASE</version>
  </dependency>
  
  <!-- Apache Commons FileUpload -->
    <dependency>
        <groupId>commons-fileupload</groupId>
        <artifactId>commons-fileupload</artifactId>
        <version>1.3.2</version>
    </dependency>

    <!-- Apache Commons IO -->
    <dependency>
        <groupId>commons-io</groupId>
        <artifactId>commons-io</artifactId>
        <version>2.5</version>
    </dependency>
  
  </dependencies>
</project>
<form method="post" action="multiPartServlet" enctype="multipart/form-data">
  Choose a file: <input type="file" name="multiPartServlet" />
    <input type="submit" value="Upload" />
</form>

 

PHP and MySQL Interview Questions with Answers

1.Difference Between Char and Varchar data type?

  • CHAR Data Type is a Fixed Length Data Type. It can be any value from 0 to 255 bytes.Uses static memory allocation.
    For example, if you declare a variable/column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character in this variable or column. And in this example, as we have declared this variable/column as CHAR(10), so we can store max 10 characters in this column.
  • On the other hand, VARCHAR is a variable length Data Type.Maximum lengths can range from 0 to 255 bytes (before MySQL 5.0.3) or from 0 to 65,535 bytes in later versions.Uses dynamic memory allocation.
    For example, if you declare a variable/column of VARCHAR(10) data type, it will take the number of bytes equal to the number of characters stored in this column. So, in this variable/column, if you are storing only one character, then it will take only one byte and if we are storing 10 characters, then it will take 10 bytes. And in this example, as we have declared this variable/column as , VARCHAR(10) so we can store max 10 characters in this column.

2.What all are the Mysql Storage Engines?

  • InnoDB-
    InnoDB is the most widely used storage engine with transaction support. It is an ACID compliant storage engine. It supports row-level locking, crash recovery and multi-version concurrency control. It is the only engine which provides foreign key referential integrity constraint. Oracle recommends using InnoDB for tables except for specialized use cases.
  •   MyISAM-
    MyISAM is the original storage engine. It is a fast storage engine. It does not support transactions. MyISAM provides table-level locking. It is used mostly in Web and data warehousing.
  • Memory-
    Memory storage engine creates tables in memory. It is the fastest engine. It provides table-level locking. It does not support transactions. Memory storage engine is ideal for creating temporary tables or quick lookups. The data is lost when the database is restarted.
  • CSV-
    CSV stores data in CSV files. It provides great flexibility because data in this format is easily integrated into other applications.
  • Merge-
    Merge operates on underlying MyISAM tables. Merge tables help manage large volumes of data more easily. It logically groups a series of identical MyISAM tables, and references them as one object. Good for data warehousing environments.
  • Archive-
    Archive storage engine is optimised for high speed inserting. It compresses data as it is inserted. It does not support transactions. It is ideal for storing and retrieving large amounts of seldom referenced historical, archived data.
  • Blackhole-
    The Blackhole storage engine accepts but does not store data. Retrievals always return an empty set. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally. This storage engine can be used to perform performance tests or other testing.
  • Federated-
    Federated storage engine offers the ability to separate MySQL servers to create one logical database from many physical servers. Queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables. It is good for distributed environments.

3.$a=3,$b=7 swap values of these two variable without using third variable.
$a = $b – $a;
$b = $b – $a;
$a = $b + $a;
4.Difference between joins and union?
5.What all types of joins are there?
6.Difference between minified js jquery.min.js and jquery.js ?
7.Latest versions of Mysql,PHP,Codeigniter.
8.Differences between older and newer versions.
9.Difference between echo and print?
10.CURL functions?
11.How to set unlimited upload_max_filesize?
12.libarary/helper for xss_clean?
13.Difference between library and helper?
14.Get last executed query and insert id in MySQL with PHP/CodeIgniter.
$this->db->last_query();
$this->db->insert_id();

sample htaccess file

Sample htaccess file

<Limit GET POST>
order allow,deny
SetEnvIf Remote_Addr ^10\.87\. ban
#allow from 10.87.63.0/8
allow from env=allow
deny from env=ban
</Limit>
#https://www.countryipblocks.net/block-specific-ips-from-your-allow-list-using-setenvif

<IfModule mod_rewrite.c>
  RewriteEngine on
  RewriteRule ^test$ https://google.co.in
  #RewriteRule .* https://google.co.in
    #RewriteRule ^abc/([a-z]*)/([0-9]*)$ xyz.php?name=$1&id=$2 [NC,L,QSA]
    #RewriteCond %{HTTP_HOST} ^reporting.soc.saint-gobain.net [NC] 
    #RewriteCond %{SERVER_PORT} 80 RewriteRule ^(.*)$ https://domain.com/$1 [R,L]
</IfModule>

 

 

php debug performance script

Php Script to check bugs and performance:
<?php

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Asia/Kolkata');
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
date_default_timezone_set('Asia/Kolkata');

echo date('H:i:s') , " Program Started" , EOL;


// Echo memory peak usage
echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
// Echo done
echo date('H:i:s') , " Program End" , EOL;

?>

 

Core Java Assignment

Classes and Objects
1. Create class Rectangle
a) data members
– length
– breadth
b) methods
– setDimensions
– area
– perimeter

2. Create class Cube
a) data members
– length
– breadth
– height
b) methods
– setDimensions
– volume

3. Create class Worker
a) data members
– wages
– wdays
b) methods
– setData
– payment

Method Overloading

1. Create class Box
a) data members
– length
– breadth
– height
b) methods
– setDimensions(3 args)
– setDimensions(1 arg)
– volume

Method Returning Value
1. WAP to find total payment of two employeesCreate
Create class Worker
a) data members
– wages
– wdays
b) methods
– setData
– int payment

Constructors
1. Create class Circle and initialize radius using a default constructor
2. Create class Circle and initialize radius using parameterized constructor
3. Create class Circle and define two constructor default and parameterized (Constructor overloading)

Static data members and member functions
1. Create a class Circle and find its area
a) data members
– int radius
– static double pi
b) methods
– area

2. Create a class Bank Account and find interest
a) data members
– int account_number
– int balance
– static double interest_rate
b) methods
– interest (balance * interest_rate * no_of_period / 100.0)
– changeRate (change interest_rate)