Tuesday, November 28, 2017

Servlet + JSP + JDBC + Hibernate

SERVLET

A servlet life cycle can be defined as the entire process from its creation till the destruction. The following are the paths followed by a servlet
·         The servlet is initialized by calling the init() method.
·         The servlet calls service() method to process a client's request.
·         The servlet is terminated by calling the destroy() method.
·         Finally, servlet is garbage collected by the garbage collector of the JVM.

 

When init() method of servlet gets called?
The init method is designed to be called only once. It is called when the servlet is first created, and not called again for each user request. So, it is used for one-time initializations, just as with the init method of applets.
When service() method of servlet gets called?
Each time the server receives a request for a servlet, the server spawns a new thread and calls service. The service() method checks the HTTP request type (GET, POST, PUT, DELETE, etc.) and calls doGet, doPost, doPut, doDelete, etc. methods as appropriate.

 




When doGet() method of servlet to be called?
A GET request results from a normal request for a URL or from an HTML form that has no METHOD specified and it should be handled by doGet() method.
When doPost() method of servlet to be called?
A POST request results from an HTML form that specifically lists POST as the METHOD and it should be handled by doPost() method.
When destroy() method of servlet gets called?
The destroy() method is called only once at the end of the life cycle of a servlet.
For what purpose init() method of a servlet is used?
The init() method simply creates or loads some data that will be used throughout the life of the servlet.
For what purpose destroy() method of a servlet is used?
This method gives your servlet a chance to close database connections, halt background threads, write cookie lists or hit counts to disk, and perform other such cleanup activities.
Explain working of service() method of a servlet.
The service() method is the main method to perform the actual task. The servlet container (i.e. web server) calls the service() method to handle requests coming from the client( browsers) and to write the formatted response back to the client.
Each time the server receives a request for a servlet, the server spawns a new thread and calls service. The service() method checks the HTTP request type (GET, POST, PUT, DELETE, etc.) and calls doGet, doPost, doPut, doDelete, etc. methods as appropriate.
Here is the signature of this method:
public void service(ServletRequest request,
                    ServletResponse response)
   throws ServletException, IOException{
}
The service () method is called by the container and service method invokes doGe, doPost, doPut, doDelete, etc. methods as appropriate. So you have nothing to do with service() method but you override either doGet() or doPost() depending on what type of request you receive from the client.



Methods of HttpServlet class

  1. protected void doGet(HttpServletRequestreq, HttpServletResponse res) handles the GET request. It is invoked by the web container.
  2. protected void doPost(HttpServletRequestreq, HttpServletResponse res) handles the POST request. It is invoked by the web container.
  3. protected void doHead(HttpServletRequestreq, HttpServletResponse res) handles the HEAD request. It is invoked by the web container.
  4. protected void doOptions(HttpServletRequestreq, HttpServletResponse res) handles the OPTIONS request. It is invoked by the web container.
  5. protected void doPut(HttpServletRequestreq, HttpServletResponse res) handles the PUT request. It is invoked by the web container.
  6. protected void doTrace(HttpServletRequestreq, HttpServletResponse res) handles the TRACE request. It is invoked by the web container.
  7. protected void doDelete(HttpServletRequestreq, HttpServletResponse res) handles the DELETE request. It is invoked by the web container.

Servlet :-
Q 1 - When service() method of servlet gets called?
B - The service() method is called whenever the servlet is invoked.
Answer : B
Explaination
Each time the server receives a request for a servlet, the server spawns a new thread and calls service() method.

Q 2 - Which of the following is true about HTTP Post method?
C - Both of the above.
Answer : C
Explaination
The POST method sends the encoded user information as a seperate message to page request. It is used to submit form data normally.


Q 3 - Which of the following code retrieves the name of the authentication scheme?
C - request.getAuthType()
Answer : C
Explaination
request.getAuthType() returns the name of the authentication scheme used to protect the servlet, for example, BASIC or SSL or null if the JSP was not protected.

Q 4 - Which of the following code retrieves the Internet Protocol (IP) address of the client that sent the request?
A - request.getRemoteAddr()
C - Header.getRemoteAddr()
Answer : A
Explaination
request.getRemoteAddr() returns the Internet Protocol (IP) address of the client that sent the request.

Q 5 - Which of the following code retrieves the login of the user making this request?
A - request.getRemoteUser()
Answer : A
Explaination
request.getRemoteUser() returns the login of the user making this request, if the user has been authenticated, or null if the user has not been authenticated.


Q 6 - Which of the following code can be used to add a date header?
A - response.addDateHeader(name,date)
Answer : A
Explaination
response.addDateHeader(name,date) adds a response header with the given name and date-value.

Q 7 - When init() method of filter gets called?
A - The init() method is called by the web container to indicate to a filter that it is being placed into service.
Answer : A
Explaination
The init method is designed to be called only once. It is called by the web container to indicate to a filter that it is being placed into service.
 Hide Answer
Q 8 - Which of the following is true about init() method of filter?
C - Both of the above.
Answer : C
Explaination
The init method is designed to be called only once. It is called when the filter is first created, and not called again for each user request. It simply creates or loads some data that will be used throughout the life of the filter.
 Hide Answer
Q 9 - Which of the following code is used to update an attribute in a HTTP Session object in servlets?
A - session.setAttribute(name,value)
Answer : A
Explaination
session.setAttribute() binds an object to this session, using the name specified.
 Hide Answer
Q 10 - Which of the following code is used to get language code in servlets?
C - request.getLanguage()
Answer : C
Explaination
request.getLanguage() returns the language code in lower case for this locale in ISO 639 format.



---------------------------------------------------------------------------------------------------------------------------
JSPs

Object
Out
Request
Response
Config
Application
Session
PageContext
Page
Exception

Difference between forward and sendRedirect

Forward

Control can be forward to resources available within the server from where the call is made. This transfer of control is done by the container internally and browser / client are not involved. This is the major difference between forward and sendRedirect. When the forward is done, the original request and response objects are transferred along with additional parameters if needed.

 

Redirect

Control can be redirect to resources to different servers or domains. This transfer of control task is delegated to the browser by the container. That is, the redirect sends a header back to the browser / client. This header contains the resource URL to be redirected by the browser. Then the browser initiates a new request to the given URL. Since it is a new request, the old request and response object is lost.
For example, sendRedirect can transfer control from http://javapapers.com to http://anydomain.com but forward cannot do this.
‘Session’ is not lost in both forward and redirect.
To feel the difference between forward and sendRedirect visually see the address bar of your browser,
in forward, you will not see the forwarded address (since the browser is not involved)
in redirect, you can see the redirected address.

When can we use forward and when can we use sendRedirect?

Technical scenario: redirect should be used
  1. If you need to transfer control to different domain
  2. To achieve separation of task.
For example, database update and data display can be separated by redirect. Do the PaymentProcess and then redirect to displayPaymentInfo. If the client refreshes the browser only the displayPaymentInfo will be done again and PyamenProcess will not be repeated. But if you use forward in this scenario, both PaymentProcess and displayPaymentInfo will be re-executed sequentially, which may result in incosistent data.
For other than the above two scenarios, forward is efficient to use since it is faster than sendRedirect.

 

Example for forward and sendRedirect based on real world


Consider the real world scenario, the milk man comes and asks for monthly payment to you in your house. Here house is the container and you are a resource existing in the container. Milk man is the client or browser.
He asks for the monthly payment to you, this is the request made by the browser to resource A. If you go inside your house and ask your mother (another resource B inside the same container) for the cash and come back and deliver to milkman this is called forward.
If you ask the milkman to speak himself to your mother inside your house or you ask the milkman to speak to your father who is in his office (different domain) then this is called redirect.

·         redirect sets the response status to 302, and the new url in a Location header, and sends the response to the browser. Then the browser, according to the http specification, makes another request to the new url
·         forward happens entirely on the server. The servlet container just forwards the same request to the target url, without the browser knowing about that. Hence you can use the same request attributes and the same request parameters when handling the new url. And the browser won't know the url has changed (because it has happened entirely on the server)

A list of the 9 implicit objects is given below:
Object
Out
Request
Response
Config
Application
Session
PageContext
Page
Exception

Exception Handling

Exception Handling is a process of handling exceptional condition that might occur in your application. Exception Handling in JSP is much easier than Java Technology exception handling. Although JSP Technology also uses the same exception class object.
It is quite obvious that you dont want to show error stack trace to the guy surfing your website. You can't prevent all errors in your application but you can atleast give an user friendlier error response page.
Session

Ways to perform exception handling in JSP

JSP provide two different way to perform exception handling.
  1. Using isErrorPage and errorPage attribute of page directive.
  2. Using <error-page> tag in Deployment Descriptor.

Example of isErrorPage and errorPage attribute

isErrorPage attribute in page directive officially appoint a JSP page as an error page.
error.jsp

errorPage attribute in page directive tells the Web Container that if an exception occur in this page, forward the request to an error page.
sum.jsp


Declaring error page in Deployment Descriptor

You can also declare error pages in the DD for the entire Web Apllication.Using<error-page> tag in Deployment Descriptor you can even configure different error pages for different exception types, or HTTP error code type(400,500 etc.).
Declaring an error page for all type of exception
<error-page>
<exception-type>java.lang.Throwable</exception-type>
<location>/error.jsp</location>
</error-page>
Declaring an error page for more detailed exception
<error-page>
<exception-type>java.lang.ArithmeticException</exception-type>
<location>/error.jsp</location>
</error-page>
Declaring an error page based on HTTP Status code
<error-page>
<error-code>404</error-code>
<location>/error.jsp</location>
</error-page>

Exception Handling in JSP

The exception is normally an object that is thrown at runtime. Exception Handling is the process to handle the runtime errors. There may occur exception any time in your web application. So handling exceptions is a safer side for the web developer. In JSP, there are two ways to perform exception handling:
  1. By errorPage and isErrorPage attributes of page directive
  2. By <error-page> element in web.xml file

Example of exception handling in jsp by the elements of page directive

In this case, you must define and create a page to handle the exceptions, as in the error.jsp page. The pages where may occur exception, define the errorPage attribute of page directive, as in the process.jsp page.
There are 3 files:
  • index.jsp for input values
  • process.jsp for dividing the two numbers and displaying the result
  • error.jsp for handling the exception
  •  
index.jsp
  1. <form action="process.jsp">  
  2. No1:<input type="text" name="n1" /><br/><br/>  
  3. No1:<input type="text" name="n2" /><br/><br/>  
  4. <input type="submit" value="divide"/>  
  5. </form>  

process.jsp

  1. <%@ page errorPage="error.jsp" %>  
  2. <%  
  3.   
  4. String num1=request.getParameter("n1");  
  5. String num2=request.getParameter("n2");  
  6.   
  7. int a=Integer.parseInt(num1);  
  8. int b=Integer.parseInt(num2);  
  9. int c=a/b;  
  10. out.print("division of numbers is: "+c);  
  11.   
  12. %>  

error.jsp

  1. <%@ page isErrorPage="true" %>  
  2.   
  3. <h3>Sorry an exception occured!</h3>  
  4.   
  5. Exception is: <%= exception %>  

Example of exception handling in jsp by specifying the error-page element in web.xml file

This approach is better because you don't need to specify the errorPage attribute in each jsp page. Specifying the single entry in the web.xml file will handle the exception. In this case, either specify exception-type or error-code with the location element. If you want to handle all the exception, you will have to specify the java.lang.Exception in the exception-type element. Let's see the simple example:
There are 4 files:
  • web.xml file for specifying the error-page element
  • index.jsp for input values
  • process.jsp for dividing the two numbers and displaying the result
  • error.jsp for displaying the exception

1) web.xml file if you want to handle any exception

  1. <web-app>  
  2.   
  3.  <error-page>  
  4.   <exception-type>java.lang.Exception</exception-type>  
  5.   <location>/error.jsp</location>  
  6.   </error-page>  
  7.    
  8. </web-app>  
This approach is better if you want to handle any exception. If you know any specific error code and you want to handle that exception, specify the error-code element instead of exception-type as given below:

1.1.1.1       1) web.xml file if you want to handle the exception for a specific error code

  1. <web-app>  
  2.   
  3.  <error-page>  
  4.   <error-code>500</error-code>  
  5.   <location>/error.jsp</location>  
  6.   </error-page>  
  7.    
  8. </web-app>  

2) index.jsp file is same as in the above example

3) process.jsp

Now, you don't need to specify the errorPage attribute of page directive in the jsp page.
  1. <%@ page errorPage="error.jsp" %>  
  2. <%  
  3.   
  4. String num1=request.getParameter("n1");  
  5. String num2=request.getParameter("n2");  
  6.   
  7. int a=Integer.parseInt(num1);  
  8. int b=Integer.parseInt(num2);  
  9. int c=a/b;  
  10. out.print("division of numbers is: "+c);  
  11.   
  12. %>  

Session on JSP










1)      DataBase
2)      JDBC
3)      Hibernate

DATABASE


Self_Join

select e.name, m.name from self_join e , self_join m where e.mid=m.empid;
+------------+---------+
| name       | name    |
+------------+---------+
| Ali        | Krishan |
| neeraj     | sanjeev |
| Krishan    | sanjeev |
| Neha Singh | sanjeev |
| Neha Saini | sanjeev |
+------------+---------+


+-------+------------+------+
| empid | name       | mid  |
+-------+------------+------+
|     1 | Ali        |    3 |
|     2 | neeraj     |    4 |
|     3 | Krishan    |    4 |
|     4 | sanjeev    | NULL |
|     5 | Neha Singh |    4 |
|     6 | Neha Saini |    4 |
+-------+------------+------+

MariaDB [abc]> select m.name, e.name from SJ e , SJ m where m.mid=e.empid;
+---------+---------+
| name    | name    |
+---------+---------+
| Ali     | Krishan |
| Krishan | Sanjeev |
| Sanjeev | DevRaj  |
| DevRaj  | DevRaj  |
+---------+---------+
MariaDB [abc]> select * from SJ;
+-------+---------+------+
| empid | name    | mid  |
+-------+---------+------+
|     1 | Ali     |    4 |
|     4 | Krishan |    3 |
|     3 | Sanjeev |    2 |
|     2 | DevRaj  |    2 |

+-------+---------+------+
1. What is a stored procedure?
    A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server.

What            is         a          Stored Procedure:

They are one or more SQL programs stored in a database as an executable object. They can be called interactively, from within a client application or from another stored procedure and from within triggers. We can pass parameters to and return from stored procedures to increase their usefulness and flexibility. A stored procedure can return a number or result set and a status code.

MySQL stored procedures advantages

·         Typically stored procedures help increase the performance of the applications. Once created, stored procedures are compiled and stored in the database. However, MySQL implements the stored procedures slightly different. MySQL stored procedures are compiled on demand. After compiling a stored procedure, MySQL puts it into a cache. And MySQL maintains its own stored procedure cache for every single connection. If an application uses a stored procedure multiple times in a single connection, the compiled version is used, otherwise, the stored procedure works like a query.
·         Stored procedures help reduce the traffic between application and database server because instead of sending multiple lengthy SQL statements, the application has to send only name and parameters of the stored procedure.
·         Stored procedures are reusable and transparent to any applications. Stored procedures expose the database interface to all applications so that developers don’t have to develop functions that are already supported in stored procedures.
·         Stored procedures are secure. The database administrator can grant appropriate permissions to applications that access stored procedures in the database without giving any permissions on the underlying database tables.
Besides those advantages, stored procedures have their own disadvantages, which you should be aware of before using them in your databases.

MySQL stored procedures disadvantages

·         If you use a lot of stored procedures, the memory usage of every connection that is using those stored procedures will increase substantially. In addition, if you overuse a large number of logical operations inside store procedures, the CPU usage will also increase because the database server is not well-designed for logical operations.
·         Constructs of stored procedures make it more difficult to develop stored procedures that have complicated business logic.
·         It is difficult to debug stored procedures. Only a few database management systems allow you to debug stored procedures. Unfortunately, MySQL does not provide facilities for debugging stored procedures.
·         It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures are often required a specialized skill set that not all application developers possess. This may lead to problems in both application development and maintenance phases.




JOIN OPERATOR

The JOIN operator specifies how to relate tables in the query.  The JOIN operator is one of the set operations available in relational databases.

The following join types of join are available in most relational databases:

            INNER
            OUTER (LEFT. RIGHT, FULL)
            CROSS
Joins may be represented as Venn diagrams, as shown below along with other common set operations:





Result of applying these joins in a query:

INNER JOIN:  Select only those rows that have values in common in the columns specified in the ON clause.

LEFT, RIGHT, or FULL OUTER JOIN:  Select all rows from the table on the left (or right, or both) regardless of whether the other table has values in common and (usually) enter NULL where data is missing.  (Note:  FULL OUTER JOIN not implemented in Access.)

CROSS JOIN (not illustrated - not exactly a set operation):  Select all possible combinations of  rows and columns from both tables (Cartesian product). Not available in Access but can "happen" by not specifying relationships between tables or not setting up the appropriate joins in a query.  (Not A Good Thing - the query may run for a very long time  and produce a huge, not very useful result set.)     
Access uses the ANSI  (American National Standards Institute) style, with the JOIN and ON keywords.  Access, MySQL, and Oracle all use similar syntax, with more join types and options and other set operations in MySQL and Oracle (CROSS JOIN, FULL OUTER JOIN, INTERSECT, MINUS).
        


Select a.username ,a.addressb.firstname, b.lastname, b.city  from USER a LEFT JOIN adminuser b on a.id = b.id;




Select a.username ,a.addressb.firstname, b.lastname, b.city  from USER a RIGHT  JOIN adminuser b on a.id = b.id;

create table emp (id int , name varchar(20) , salary varchar(20));

Alter table emp add primary key(id);

desc   company;

Describe   company;
`
alter table company modify column Name varchar(20);

update company set name='Apple' where id='4';

select company.name, emp.name,emp.salary from company right join emp on company.id=emp.id;


àselect company.name, emp.name,emp.salary from company left join emp on  company.id = emp.id;

Adding new column :alter table sales name varchar(20);

Dropping a column "alter table company drop column name;"

 With Foreign key

DELETE customers
FROM customers
LEFT JOIN orders ON customers.customerNumber = orders.customerNumber
WHERE orderNumber IS NULL;

DELETE FROM table_name [WHERE Clause]

 Add Date column in a Table

 alter table register add column Date date default null;

 insert date in table

 update register set date=curdate() where id='1';

  update register set date='2018-09-01' where id='3';
+---------------+----------------+----------------+----------+----------------+----+------+
| name          | password       | email          | sex      | country        | id | Date |
+---------------+----------------+----------------+----------+----------------+----+------+
| ali           | pass           | a@a.com        | male     | India          |  1 | NULL |
| Gaurav Mourya | password       | G@gmail.com    | male     | India          |  2 | NULL |
| Perwaiz Ali   | ghghjg         | a@ag.cokm      | male     | India          |  5 | NULL |
| Choudhary     | ada            | a@a.com        | male     | Berma          |  6 | NULL |
| Choudhary     | aaa            | a@a.com        | male     | India          |  7 | NULL |
| Kunal         | kkkk           | kka@g.com      | male     | India          |  8 | NULL |
| NULL          | NULL           | NULL           | NULL     | Not Done       |  9 | NULL |
| NULL          | NULL           | NULL           | NULL     | Not Done       | 10 | NULL |
| NULL          | NULL           | NULL           | NULL     | NULL           | 11 | NULL |
| Kunal         | aaaa           | a@a.com        | male     | India          | 12 | NULL |
| aaa           | aaa            | aa@g.com       | Done     | Not Done       | 14 | NULL |
| NULL          | NULL           | NULL           | Done     | Done in Mosque | 15 | NULL |
| Not Done      | Not Done       | Not Done       | Not Done | Not Done       | 16 | NULL |
| Not Done      | Not Done       | Not Done       | Not Done | NULL           | 17 | NULL |
| Not Done      | Not Done       | Not Done       | Not Done | Not Done       | 18 | NULL |
| Not Done      | Not Done       | Not Done       | Not Done | Not Done       | 19 | NULL |
| Not Done      | Done in Mosque | Not Done       | Not Done | Not Done       | 20 | NULL |
| Not Done      | Done in Mosque | Not Done       | Not Done | Not Done       | 21 | NULL |
| Not Done      | Not Done       | Done in Mosque | Not Done | Not Done       | 22 | NULL |

+---------------+----------------+----------------+----------+----------------+----+------+

We have two tables: customers and orders. Each customer has zero or more orders and each order belongs to only one customer. The relationship between customers table and orders table is one-to-many, and it is established by a foreign key in the orders table specified by the customerNumberfield. The customerNumberfield in the orders table relates to the customerNumberprimary key field in customers table.
The customers table is called parent table or referenced table, and the orders table is known as child table or referencing table.
A row in the child table must contain values that exist in the parent table e.g., each order record in the orders table must have a customerNumberthat exists in the customers table. Multiple orders can refer to the same customer therefore this relationship is called one (customer) to many (orders), or one-to-many.

In addition, you can set up a cascade on delete action for the customerNumberforeign key so that when you delete a customer in the customers table, all the orders associated with the customer are also deleted.


MariaDB [test]> select MAX(salary) from perwaiz;
+-------------+
| MAX(salary) |
+-------------+
|      175000 |

MariaDB [test]> select MAX(salary),id, name,city from perwaiz;
+-------------+------+------+-------+
| MAX(salary) | id   | name | city  |
+-------------+------+------+-------+
|      175000 |    1 | Ali  | Delhi |
+-------------+------+------+-------+

MariaDB [test]> select MIN(Salary) from perwaiz;
+-------------+
| MIN(Salary) |
+-------------+
|       60000 |
+-------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT MAX(Salary) From Perwaiz WHERE Salary < ( SELECT Max(Salary) FROM Perwaiz);
+-------------+
| MAX(Salary) |
+-------------+
|       95000 |
+-------------+

1 row in set (0.07 sec)

MariaDB [test]> SELECT Min(Salary) From Perwaiz WHERE Salary > ( SELECT min(Salary) FROM Perwaiz);
+-------------+
| Min(Salary) |
+-------------+
|       75000 |
+-------------+
MariaDB [test]> select name, salary from perwaiz where salary = (select MAX(salary) from perwaiz);
+--------+--------+
| name   | salary |
+--------+--------+
| shakti | 175000 |

MariaDB [test]> select name from perwaiz where salary =(select  MAX(salary) from perwaiz where salary<(select Max(salary) from Perwaiz));
+----------+
| name     |
+----------+
| Paritosh |
+----------+

MariaDB [test]> select * from perwaiz;
+------+----------------+-----------+--------+
| id   | name           | city      | salary |
+------+----------------+-----------+--------+
|    1 | Ali            | Delhi     |  60000 |
|    2 | Mako           | Mumbai    |  90000 |
|    2 | Paritosh       | Bangalore |  95000 |
|    4 | shakti         | gr. Noida | 175000 |
|    5 | Gaurav Shooter | bglr      |  75000 |
+------+----------------+-----------+--------+
5 rows in set (0.03 sec)

MariaDB [test]> select count(*) from perwaiz;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
Different way
Max salary from employee table 

 select * from employee emp1 where 0=(select count (distinct salary)  from employee emp2 where emp2.salary>emp1.salary);

 3rd highest and many more 

 select  * from employee emp1 where 2=(select count (distinct salary)  from employee emp2 where emp2.salary>emp1.salary);

         ID NAME                     SALARY
---------- -------------------- ----------
         2 sumaira_2                  1300

         3 sumaira_2                  1300


select salary from
(select salary from employee order by salary desc limit 5)
As emp
order by salary limit 1;

MySql Table

SELF_JOIN








Primary Key:
·         There can only be one primary key in a table
·         In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL
·         Primary Key is a unique key identifier of the record
Unique Key:
·         Can be more than one unique key in one table
·         Unique key can have NULL values
·         It can be a candidate key
·         Unique key can be NULL and may not be unique


Unique Key (UK): It's a column or a group of columns that can identify a uniqueness in a row.
Primary Key (PK): It's also a column or group of columns that can identify a uniqueness in a row.
So the Primary key is just another name for unique key, but the default implementation in SQL Server is different for Primary and Unique Key.
By Default:
1.      PK creates a Clustered index and UK creates a Non Clustered Index.
2.      PK is not null, but UK allows nulls (Note: By Default)
3.      There can only be one and only one PK on a table, but there can be multiple UK's
4.      You can override the default implementation depending upon your need.
It really depends what is your aim when deciding whether to create a UK or PK. It follows an analogy like "If there is a team of three people, so all of them are peers, but there will be one of them who will be a pair of peers: PK and UK has similar relation.". I would suggest reading this article: The example given by the author may not seem suitable, but try to get an overall idea.



Remove Duplicate Records in Orcale





5 ways to delete duplicate records Oracle

sqlandplsql In Oracle there are many ways to delete duplicate records. Note that below example are described to just explain the different possibilities.

Consider the EMP table with below rows
create table emp(
EMPNNO  integer,
EMPNAME varchar2(20),
SALARY  number);
10    Bill    2000
11    Bill    2000
12    Mark    3000
12    Mark    3000
12    Mark    3000
13    Tom    4000
14    Tom    5000
15    Susan    5000
1. Using rowid
SQL > delete from emp
where rowid not in
(select max(rowid) from emp group by empno);
This technique can be applied to almost scenarios. Group by operation should be on the columns which identify the duplicates.
2. Using self-join
SQL > delete from emp e1
where rowid not in
(select max(rowid) from emp e2
where e1.empno = e2.empno );
3. Using row_number()
SQL > delete from emp where rowid in
(
select rid from
(
select rowid rid,
row_number() over(partition by empno order by empno) rn
from emp
)
where rn > 1
);
This is another efficient way to delete duplicates
4. Using dense_rank()
SQL > delete from emp where rowid in
(
select rid from
(
select rowid rid,
dense_rank() over(partition by empno order by rowid) rn
from emp
)
where rn > 1
);
Here you can use both rank() and dens_rank() since both will give unique records when order by rowid.
5. Using group by
Consider the EMP table with below rows
10    Bill    2000
11    Bill    2000
12    Mark    3000
13    Mark    3000
SQL > delete from emp where
(empno,empname,salary) in
(
select max(empno),empname,salary from emp
group by empname,salary
);
This technique is only applicable in few scenarios.
Always take extra caution while deleting records. 
1. First identify the duplicates using select.
2. Double verify those are actual  ‘duplicates’ or not
3. Take backup if necessary
4. Apply commit only if you are sure.
Did you find above post useful ? Your comments are highly valuable.
Advertisements

Delete duplicate row from a table





Removing duplicate rows from table






truncate 

https://youtu.be/JeN6ijUdvsA





JDBC

3) What are the steps to connect to the database in java?

  • Registering the driver class
  • Creating connection
  • Creating statement
  • Executing queries
  • Closing connection

1.1.2        2) What is JDBC Driver?

JDBC Driver is a software component that enables java application to interact with the database.There are 4 types of JDBC drivers:
  1. JDBC-ODBC bridge driver
  2. Native-API driver (partially java driver)
  3. Network Protocol driver (fully java driver)
  4. Thin driver (fully java driver)

1.1.3        4) What are the JDBC API components?

The java.sql package contains interfaces and classes for JDBC API.
Interfaces:
  • Connection
  • Statement
  • PreparedStatement
  • ResultSet
  • ResultSetMetaData
  • DatabaseMetaData
  • CallableStatement etc.
Classes:
  • DriverManager
  • Blob
  • Clob
  • Types
  • SQLException etc.

1.1.4        5) What are the JDBC statements?

There are 3 JDBC statements.
  1. Statement
  2. PreparedStatement
  3. CallableStatement

1.1.5        6) What is the difference between Statement and PreparedStatement interface?

In case of Statement, query is complied each time whereas in case of PreparedStatement, query is complied only once. So performance of PreparedStatement is better than Statement.

1.1.6        7) How can we execute stored procedures and functions?

By using Callable statement interface, we can execute procedures and functions.

1.1.7        8) What is the role of JDBC DriverManager class?

The DriverManager class manages the registered drivers. It can be used to register and unregister drivers. It provides factory method that returns the instance of Connection.

1.1.8        9) What does the JDBC Connection interface?

The Connection interface maintains a session with the database. It can be used for transaction management. It provides factory methods that returns the instance of Statement, PreparedStatement, CallableStatement and DatabaseMetaData.

1.1.9        10) What does the JDBC ResultSet interface?

The ResultSet object represents a row of a table. It can be used to change the cursor pointer and get the information from the database.

1.1.10    13) Which interface is responsible for transaction management in JDBC?

The Connection interface provides methods for transaction management such as commit(), rollback() etc
//STEP 1. Import required packages
import java.sql.*;
 
publicclassFirstExample{
// JDBC driver name and database URL
staticfinalString JDBC_DRIVER ="com.mysql.jdbc.Driver";
staticfinalString DB_URL ="jdbc:mysql://localhost/EMP";
 
//  Database credentials
staticfinalString USER ="username";
staticfinalString PASS ="password";
 
publicstaticvoid main(String[]args){
Connection conn =null;
Statement stmt =null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
 
//STEP 3: Open a connection
System.out.println("Connecting to database...");
      conn =DriverManager.getConnection(DB_URL,USER,PASS);
 
//STEP 4: Execute a query
System.out.println("Creating statement...");
stmt=conn.createStatement();
Stringsql;
sql="SELECT id, first, last, age FROM Employees";
ResultSetrs=stmt.executeQuery(sql);
 
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id  =rs.getInt("id");
int age =rs.getInt("age");
String first =rs.getString("first");
Stringlast=rs.getString("last");
 
//Display values
System.out.print("ID: "+ id);
System.out.print(", Age: "+ age);
System.out.print(", First: "+ first);
System.out.println(", Last: "+last);
}
//STEP 6: Clean-up environment
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
}//end FirstExample
Now let us compile the above example as follows −
C:\>javac FirstExample.java
C:\>
When you run FirstExample, it produces the following result −
C:\>java FirstExample
Connecting to database...
Creating statement...
ID:100,Age:18,First:Zara,Last:Ali
ID:101,Age:25,First:Mahnaz,Last:Fatma
ID:102,Age:30,First:Zaid,Last:Khan
ID:103,Age:28,First:Sumit,Last:Mittal
C:\>

HIBERNATE


1.      What is serialization for save() and persistence() in Hibernate ?
2.      How to lazy initialization in Load() of Hibernate ?
3.      How to use of scopes of session , global session in spring?
4.      Example of "Composition and Aggregation " in inheritance ?
5.      coding example for foreign key in DB ?
6.      Can  we create an instance for Interface ?
7.      How many Interfaces can extends in a class ?
8.      what problem occurs at multiple Inheritance ?
9.      Synchronized ?
10.  Comparable and comparator interface practical implementation ?
11.  ConcurentHashMap , what is bucket lock ?
12.  when using stringbuffer and stringbuilder ?
13.  Difference between Iterator and Enumeration ?
14.  Behavioral Design pattern in java ?
15.  @Configuration, @      all annotation details in SPRING framework ?
16.  How to use exception in JSP ?
--------------------------------------------------------

1.      Employee  property  for  equality ? Comparator and comparable ?
2.      HashMap  key  how to recognize ?
3.      Synchronized  key  explanation ?
4.      Inheritance concept ?
5.      Lazy loading in hibernate ?
6.      procedures and function in mysql and hibernate ?

How second level cache works

1.      Whenever hibernate session try to load an entity, the very first place it look for cached copy of entity in first level cache (associated with particular hibernate session).
2.      If cached copy of entity is present in first level cache, it is returned as result of load method.
3.      If there is no cached entity in first level cache, then second level cache is looked up for cached entity.
4.      If second level cache has cached entity, it is returned as result of load method. But, before returning the entity, it is stored in first level cache also so that next invocation to load method for entity will return the entity from first level cache itself, and there will not be need to go to second level cache again.
5.      If entity is not found in first level cache and second level cache also, then database query is executed and entity is stored in both cache levels, before returning as response of load() method.
6.      Second level cache validate itself for modified entities, if modification has been done through hibernate session APIs.
7.      If some user or process make changes directly in database, the there is no way that second level cache update itself until “timeToLiveSeconds” duration has passed for that cache region. In this case, it is good idea to invalidate whole cache and let hibernate build its cache once again. You can use below code snippet to invalidate whole hibernate second level cache.

 Behaviour when not closing hibernate session


When you don't close your Hibernate sessions and therefore do not release JDBC connections, you have what is typically called Connection leak. So, after a number of requests (depending on the size of your connection pool) the server will not be able to acquire a connection to respond your request. Actually, server will be waiting for connections to be released and be available on pool again and it will seem to be hanging.
Tutorial Point
Caching is all about application performance optimization and it sits between your application and the database to avoid the number of database hits as many as possible to give a better performance for performance critical applications.
Caching is important to Hibernate as well which utilizes a multilevel caching schemes as explained below:


First-level cache:
The first-level cache is the Session cache and is a mandatory cache through which all requests must pass. The Session object keeps an object under its own power before committing it to the database.
If you issue multiple updates to an object, Hibernate tries to delay doing the update as long as possible to reduce the number of update SQL statements issued. If you close the session, all the objects being cached are lost and either persisted or updated in the database.
Second-level cache:
Second level cache is an optional cache and first-level cache will always be consulted before any attempt is made to locate an object in the second-level cache. The second-level cache can be configured on a per-class and per-collection basis and mainly responsible for caching objects across sessions.
Any third-party cache can be used with Hibernate. An org.hibernate.cache.CacheProvider interface is provided, which must be implemented to provide Hibernate with a handle to the cache implementation.
Query-level cache:
Hibernate also implements a cache for query resultsets that integrates closely with the second-level cache.
This is an optional feature and requires two additional physical cache regions that hold the cached query results and the timestamps when a table was last updated. This is only useful for queries that are run frequently with the same parameters.
The Second Level Cache:
Hibernate uses first-level cache by default and you have nothing to do to use first-level cache. Let's go straight to the optional second-level cache. Not all classes benefit from caching, so it's important to be able to disable the second-level cache
The Hibernate second-level cache is set up in two steps. First, you have to decide which concurrency strategy to use. After that, you configure cache expiration and physical cache attributes using the cache provider.
Concurrency strategies:
A concurrency strategy is a mediator which responsible for storing items of data in the cache and retrieving them from the cache. If you are going to enable a second-level cache, you will have to decide, for each persistent class and collection, which cache concurrency strategy to use.
·         Transactional: Use this strategy for read-mostly data where it is critical to prevent stale data in concurrent transactions, in the rare case of an update.
·         Read-write: Again use this strategy for read-mostly data where it is critical to prevent stale data in concurrent transactions, in the rare case of an update.
·         Nonstrict-read-write: This strategy makes no guarantee of consistency between the cache and the database. Use this strategy if data hardly ever changes and a small likelihood of stale data is not of critical concern.
·         Read-only: A concurrency strategy suitable for data which never changes. Use it for reference data only.
If we are going to use second-level caching for our Employee class, let us add the mapping element required to tell Hibernate to cache Employee instances using read-write strategy.



1.      Read Only: This caching strategy should be used for persistent objects that will always read but never updated. It’s good for reading and caching application configuration and other static data that are never updated. This is the simplest strategy with best performance because there is no overload to check if the object is updated in database or not.
2.      Read Write: It’s good for persistent objects that can be updated by the hibernate application. However if the data is updated either through backend or other applications, then there is no way hibernate will know about it and data might be stale. So while using this strategy, make sure you are using Hibernate API for updating the data.
3.      Nonrestricted Read Write: If the application only occasionally needs to update data and strict transaction isolation is not required, a nonstrict-read-write cache might be appropriate.
4.      Transactional: The transactional cache strategy provides support for fully transactional cache providers such as JBoss TreeCache. Such a cache can only be used in a JTA environment and you must specify hibernate.transaction.manager_lookup_class.




<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
 "-//Hibernate/Hibernate Mapping DTD//EN"
 "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<classname="Employee"table="EMPLOYEE">
<metaattribute="class-description">
         This class contains the employee detail.
</meta>
<cacheusage="read-write"/>
<idname="id"type="int"column="id">
<generatorclass="native"/>
</id>
<propertyname="firstName"column="first_name"type="string"/>
<propertyname="lastName"column="last_name"type="string"/>
<propertyname="salary"column="salary"type="int"/>
</class>
</hibernate-mapping>
The usage="read-write" attribute tells Hibernate to use a read-write concurrency strategy for the defined cache.
Cache provider:
Your next step after considering the concurrency strategies you will use for your cache candidate classes is to pick a cache provider. Hibernate forces you to choose a single cache provider for the whole application.
S.N.
Cache Name
Description
1
EHCache
It can cache in memory or on disk and clustered caching and it supports the optional Hibernate query result cache.
2
OSCache
Supports caching to memory and disk in a single JVM, with a rich set of expiration policies and query cache support.
3
warmCache
A cluster cache based on JGroups. It uses clustered invalidation but doesn't support the Hibernate query cache
4
JBoss Cache
A fully transactional replicated clustered cache also based on the JGroups multicast library. It supports replication or invalidation, synchronous or asynchronous communication, and optimistic and pessimistic locking. The Hibernate query cache is supported
Every cache provider is not compatible with every concurrency strategy. The following compatibility matrix will help you choose an appropriate combination.

Strategy/Provider
Read-only
Nonstrictread-write
Read-write
Transactional
EHCache
X
X
X

OSCache
X
X
X

SwarmCache
X
X


JBoss Cache
X


X
You will specify a cache provider in hibernate.cfg.xml configuration file. We choose EHCache as our second-level cache provider:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration SYSTEM
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
<propertyname="hibernate.dialect">
org.hibernate.dialect.MySQLDialect
</property>
<propertyname="hibernate.connection.driver_class">
com.mysql.jdbc.Driver
</property>

<!-- Assume students is the database name -->
<propertyname="hibernate.connection.url">
jdbc:mysql://localhost/test
</property>
<propertyname="hibernate.connection.username">
root
</property>
<propertyname="hibernate.connection.password">
      root123
</property>
<propertyname="hibernate.cache.provider_class">
org.hibernate.cache.EhCacheProvider
</property>

<!-- List of XML mapping files -->
<mappingresource="Employee.hbm.xml"/>

</session-factory>
</hibernate-configuration>
Now, you need to specify the properties of the cache regions. EHCache has its own configuration file,ehcache.xml, which should be in the CLASSPATH of the application. A cache configuration in ehcache.xml for the Employee class may look like this:
<diskStorepath="java.io.tmpdir"/>
<defaultCache
maxElementsInMemory="1000"
eternal="false"
timeToIdleSeconds="120"
timeToLiveSeconds="120"
overflowToDisk="true"
/>

<cachename="Employee"
maxElementsInMemory="500"
eternal="true"
timeToIdleSeconds="0"
timeToLiveSeconds="0"
overflowToDisk="false"
/>
That's it, now we have second-level caching enabled for the Employee class and Hibernate now hits the second-level cache whenever you navigate to a Employee or when you load a Employee by identifier.
You should analyze your all the classes and choose appropriate caching strategy for each of the classes. Sometime, second-level caching may downgrade the performance of the application. So it is recommended to benchmark your application first without enabling caching and later on enable your well suited caching and check the performance. If caching is not improving system performance then there is no point in enabling any type of caching.
The Query-level Cache:
To use the query cache, you must first activate it using the hibernate.cache.use_query_cache="true"property in the configuration file. By setting this property to true, you make Hibernate create the necessary caches in memory to hold the query and identifier sets.
Next, to use the query cache, you use the setCacheable(Boolean) method of the Query class. For example:
Sessionsession=SessionFactory.openSession();
Queryquery=session.createQuery("FROM EMPLOYEE");
query.setCacheable(true);
List users =query.list();
SessionFactory.closeSession();
Hibernate also supports very fine-grained cache support through the concept of a cache region. A cache region is part of the cache that's given a name.
Sessionsession=SessionFactory.openSession();
Queryquery=session.createQuery("FROM EMPLOYEE");
query.setCacheable(true);
query.setCacheRegion("employee");
List users =query.list();
SessionFactory.closeSession();
This code uses the method to tell Hibernate to store and look for the query in the employee area of the cache.

20) What is the difference between first level cache and second level cache?

No.
First Level Cache
Second Level Cache
1)
First Level Cache is associated with Session.
Second Level Cache is associated
with SessionFactory.
2)
It is enabled by default.
It is not enabled by default.
1.1) First-level cache
First-level cache always Associates with the Session object. Hibernate uses this cache by default. Here, it processes one transaction after another one, means wont process one transaction many times. Mainly it reduces the number of SQL queries it needs to generate within a given transaction. That is instead of updating after every modification done in the transaction, it updates the transaction only at the end of the transaction.
1.2) Second-level cache
Second-level cache always associates with the Session Factory object. While running the transactions, in between it loads the objects at the Session Factory level, so that those objects will be available to the entire application, not bound to single user. Since the objects are already loaded in the cache, whenever an object is returned by the query, at that time no need to go for a database transaction. In this way the second level cache works. Here we can use query level cache also. Later we will discuss about it.
-------------------------------------------------------------------------------------------------------------------------------
First level cache is associated with “session” object. The scope of cache objects is of session. Once session is closed, cached objects are gone forever. First level cache is enabled by default and you can not disable it. When we query an entity first time, it is retrieved from database and stored in first level cache associated with hibernate session. If we query same object again with same session object, it will be loaded from cache and no sql query will be executed. The loaded entity can be removed from session using evict() method. The next loading of this entity will again make a database call if it has been removed using evict() method. The whole session cache can be removed using clear() method. It will remove all the entities stored in cache.
Second level cache is apart from first level cache which is available to be used globally in session factory scope. second level cache is created in session factory scope and is available to be used in all sessions which are created using that particular session factory. It also means that once session factory is closed, all cache associated with it die and cache manager also closed down. Whenever hibernate session try to load an entity, the very first place it look for cached copy of entity in first level cache (associated with particular hibernate session). If cached copy of entity is present in first level cache, it is returned as result of load method. If there is no cached entity in first level cache, then second level cache is looked up for cached entity. If second level cache has cached entity, it is returned as result of load method. But, before returning the entity, it is stored in first level cache also so that next invocation to load method for entity will return the entity from first level cache itself, and there will not be need to go to second level cache again. If entity is not found in first level cache and second level cache also, then database query is executed and entity is stored in both cache levels, before returning as response of load() method.


Second level of Query Cache with springboot



 Java Brains





Locking Mechanism in Hibernate

















Difference between get and load in Hibernate
get vs load in Hibernate
Difference between get and load method in Hibernate is a one of the most popular question asked in Hibernate and spring interviews. Hibernate Session class provides two method to access object e.g. session.get() and session.load() both looked quite similar to each other but there are subtle difference between load and get method which can affect performance of application. Main difference between get() vs load method is that get() involves database hit if object doesn't exists in Session Cache and returns a fully initialized object which may involve several database call while load method can return proxy in place and only initialize the object or hit the database if any method other than getId() is called on persistent or entity object. This lazy initialization can save couple of database round-trip which result in better performance. By the way there are many articles on interview questions in Java, you can use search button on top left to find them. Some of them like 20 design pattern interview questions and 10 Singleton pattern questions are my favorites, you may also like. Coming back to article, you can find more difference between load and get in rest of this article in point format but this is the one which really makes difference while comparing both of them. If you look at how get and load gets called its pretty identical.

Difference between get and load method
Here are few differences between get and load method in Hibernate.

1. Behavior when Object is not found in Session Cache
Apart from performance this is another difference between get and load which is worth remembering. get method of Hibernate Session class returns null if object is not found in cache as well as on database while load() method throwsObjectNotFoundException if object is not found on cache as well as on database but never return null.

2. Database hit
Get method always hit database while load() method may not always hit the database, depending upon which method is called.

3. Proxy
Get method never returns a proxy, it either returns null or fully initialized Object, while load() method may return proxy, which is the object with ID but without initializing other properties, which is lazily initialized. If you are just using returned object for creating relationship and only need Id then load() is the way to go.

4. Performance
By far most important difference between get and load in my opinion. get method will return a completely initialized object if  Object is not on the cache but exists on Database, which may involve multiple round-trips to database based upon object relational mappings while load() method of Hibernate can return a proxy which can be initialized on demand (lazy initialization) when a non identifier method is accessed. Due to above reason use of load method will result in slightly better performance, but there is a caveat that proxy object will throw ObjectNotFoundException later if corresponding row doesn’t exists in database, instead of failing immediately so not a fail fast behavior.

5. load method exists prior to get method which is added on user request.

When to use Session get() and load() in Hibernate
So far we have discussed how get and load are different to each other and how they can affect performance of your web application, after having this information in our kitty we can see some best practices to get most of load and get together. This section suggest some scenario which help you when to use get and load in Hibernate.

1. Use get method to determine if an instance exists or not because it can return null if instance doesn’t exists in cache and database and use load method to retrieve instance only if you think that instance should exists and non availability is an error condition.

2.  As stated in difference number 2 between get and load in Hibernate. get() method could suffer performance penalty if only identifier method like getId()  is accessed. So consider using load method  if  your code doesn't access any method other than identifier or you are OK with lazy initialization of object, if persistent object is not in Session Cache because load() can return proxy.

How to call get records in Hibernate using get and load method
If you look at below code , there is not much difference on calling get() and load() method, though both are overloaded now and can accept few more parameters but the primary methods looks exactly identical. It’s there behavior which makes them different.

//Example of calling get method of Hiberante Session class
Sessionsession = SessionFactory.getCurrentSession();
EmployeeEmployee = (Employee)session.get(Employee.class, EmployeeID);

//Example of calling load method of Hiberante Session
Sessionsession = SessionFactory.getCurrentSession();
EmployeeEmployee = (Employee)session.load(Employee.class, EmployeeID);




10) What is the difference between get and load method?
The differences between get() and load() methods are given below.
No.
get()
load()
1)
Returns null if object is not found.
Throws ObjectNotFoundException if
object is not found.
2)
get() method always hit the database.
load() method doesn't hit the
database.
3)
It returns real object not proxy.
It returns proxy object.
4)
It should be used if you are not sure about the existence of instance.
It should be used if you are sure 
that instance exists.

9) What is the difference between session.save() and session.persist() method?


No.
save()
persist()
1)
returns the identifier (Serializable) of the instance.
return nothing because its return
type is void.
2)
Syn: public Serializable save(Object o)
Syn: public void persist(Object o)


Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)