These are some interview question and answer asked during my recent interview. Oracle interview questions are very important during any programming job interview. Interviewer always want to check how comfortable we are with any database either we go for Java developer position or C, C++ programmer position .So here I have discussed some basic question related with oracle database. Apart from these questions which is very specific to Oracle database you may find some general questions related to database fundamentals and SQL e.g. Difference between correlated and noncorrelated subquery in database or truncate vs delete in SQL etc. Some of the most important topics in Oracle Interview questions are SQL, date, inbuilt function, stored procedure and less used features like cursor, trigger and views. These questions also gives an idea about formats of questions asked during Oracle Interview.
Showing posts with label database. Show all posts
Showing posts with label database. Show all posts
Spring Hibernate Database interview questions and answers
Q. What do you understand by the terms clustered index and non-clustered index?
A. When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage space separate from the table data storage. Clustered and non-clustered indexes are stored as binary search tree (i.e. keep data sorted and has the average performance of O(log n) for delete, inserts, and search) structures with the leaf level nodes having the index key and it's row locator for a faster retrieval.
Q. What is the difference between primary key and unique key?
A. Both primary key and unique key enforce uniqueness of the column on which they are defined. But by default, a primary key creates a clustered index on the column, whereas a unique key creates a non clustered index by default. Another major difference is that, a primary key doesn't allow NULL values, but unique key allows a single NULL.
Q. What are the pros and cons of an index?
A.
PROS
- If an index does not exist on a table, a table scan must be performed for each table referenced in a database query. The larger the table, the longer a table scan takes because a table scan requires each table row to be accessed sequentially. So, indexes can improve search performance, especially for the reporting requirements.
CONS
- Excessive non-clustered indexes can consume additional storage space.
- Excessive non-clustered indexes can adversely impact performance of the INSERT, UPDATE, and DELETE statements as the indexes need to recreated after each of the above operation.
Q. What are the pros and cons of stored procedures?
A.
PROS
- pre-compiled and less network trips for faster performance
- less susceptible to SQL injection attacks
- more precise control over transactions and locking
- can abstract complex data processing from application by acting as a facade layer.
CONS
- There are chances of larger chunks of business logic and duplications creeping into stored procedures and causing maintenance issues. Writing and maintaining stored procedures is most often a specialized skill set that not all developers possess. This situation may introduce bottlenecks in the project development schedule.
- Less portable.The stored procedures are specific to a particular database.
- Scaling a database is much harder than scaling an application.
- The application performance can be improved by caching the relevant data to reduce the network trips.
So, when should stored procedures be used ?
Stored procedures are ideal when there is a complex piece of business logic that needs complex data logic to be performed involving a lot of database operations. If this logic is required in many different places, then store procedure makes even more sense. For example, batch jobs and complex report generation that performs lots of database operations.
So, when shouldn't stored procedures be used ?
When you are performing basic CRUD (Create, Read, Update, and Delete) operations. For example, in a Web application a user creates some data, read the created data, and then updates or deletes some of the created data.
Q. How would you go about writing a stored procedure that needs to loop through a number of selected rows?
A. You need to use a cursor. A cursor is basically a pointer to row by operation. For example, you can create a cursor by selecting a number of records into it. Then, you can fetch each row at a time and perform some operations like invoking another stored proc by passing the selected row value as an argument, etc. Once uou have looped through all the records, you need to close and deallocate the cursor. For example, the stored procedure below written in Sybase demonstrates the use of a cursor.
Apply to the database "mydatabase"
use mydatabase
go
Drop the stored procedure if it already exists
IF OBJECT_ID('dbo.temp_sp') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.temp_sp
IF OBJECT_ID('dbo.temp_sp') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.temp_sp >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.temp_sp >>>'
END
go
Create the stored procedure that uses cursor
create proc temp_sp
as
DECLARE @ADVISERID char(10)
DECLARE advisers_cur cursor
for select adviser_id FROM tbl_advisers where adviser_id like 'Z%' -- select adviser_ids starting with 'Z'
for read only
open advisers_cur -- open the cursor
FETCH advisers_cur INTO @ADVISERID -- store value(s) from the cursor into declared variables
--@@sqlstatus is a sybase implcit variable that returns success/failure status of previous statement execution
WHILE (@@sqlstatus = 0)
BEGIN
SELECT @ADVISERID -- select the adviser_id stored into @ADVISERID
FETCH advisers_cur INTO @ADVISERID --store value(s) from the cursor into declared variables
END
close advisers_cur
deallocate cursor advisers_cur
go
Execute the stored procedure that uses a cursor
exec mydatabase..temp_sp
Q. Why should you deallocate the cursors?
A. You need deallocate the cursor to clear the memory space occupied by the cursor. This will enable the cleared space to be availble for other use.
Q. How would you go about copying bulk data in and out of a database?
A. The process is known as bulk copy, and the tools used for this are database specific. For example, in Sybase and SQLServer use a utility called "bcp", which allows you to export bulk data into comma delimited files, and then import the data in csv or any other delimited formats back into different database or table. In Oracle database, you achieve this via the SQLLoader. The DB2 database has IMPORT and LOAD command to achieve the same.
Q. What are triggers? what are the different types of triggers?
A. Triggers are stored procedures that are stored in the database and implicitly run, or fired, when something like INSERT, UPDATE , or DELETE happens to that table. There are 3 types of DML triggers that happens before or after events like INSERT, UPDATE, or DELETE. There could be other database specific triggers.
Q. When to not use a trigger, and when is it appropriate to use a trigger?
A.
When to not use a trigger?
The database triggers need to be used very judiciously as they are executed every time an event like insert, update or delete occur. Don't use a trigger where
- database constraints like unique constraint, not null, primary key, check constraints, etc can be used to check for data validity.
- triggers are recursive.
Where to use a trigger?
- Maintaining complex integrity constraints (referential integrity) or business rules where other types of constraints cannot be used. Because triggers are executed as part of the SQL statement (and its containing transaction) causing the row change event, and because the trigger code has direct access to the changed row, you could in theory use them to correct or reject invalid data.
- Auditing information in a table by recording the changes. Some tables are required to be audited as part of the non-functional requirement for changes.
- Automatically signaling other programs that action needs to take place when changes are made to a table.
- Collecting and maintaining aggregate or statistical data.
Q. If one of your goals is to reduce network loads, how will you about achieving it?
A.
- you can use materialized views to distribute your load from a master site to other regional sites. Instead of the entire company accessing a single database server, user load is distributed across multiple database servers with the help of multi-tier materialized views. This enables you to distribute the load to materialized view sites instead of master sites. To decrease the amount of data that is replicated, a materialized view can be a subset of a master table or master materialized view.
- Write stored procedures to minimize network round trips.
- Carefully crafting your SQL to return only required data. For example Don't do select * from tbl_mytable. Instead, specify the columns you are interested in. For example, select firstname, surname from tbl_mytable.
- You can set the fetch size to an appropriate value to get the right balance between data size and number of network trips made.
A.
- Materialized view is one of the key SQL tuning approaches to improve performance by allowing you to pre-join complex views and pre-compute summaries for super-fast response time.
- Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse, reporting, etc. A materialized view can be either read-only, updatable, or writable. Users cannot perform data manipulation language (DML) statements on read-only materialized views, but they can perform DML on updatable and writable materialized views.
- A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data. You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.
Q. If you are working with a legacy application, and some of the database tables are not properly designed with the appropriate constraints, how will you go about rectifying the situation?
A. One possible solution is to write triggers to perform the appropriate validation. Here is an example of an insert trigger.
CREATE TRIGGER TableA_itrig
ON TableA FOR INSERT
AS
BEGIN
IF @@rowcount = 0
RETURN
IF NOT EXISTS
(
SELECT *
FROM inserted ins, TableB ol
WHERE ins.code = ol.code
)
BEGIN
RAISERROR 20001, "The associated object is not found"
ROLLBACK TRAN
RETURN
END
END
Q. If you are working on a new application that requires stringent auditing requirements, how would you go about achieving it?
A. Since it is a new application, there are a number of options as listed below.
- The application is designed from the beginning so that all changes are logged either synchronously or asynchronously. Asynchronously means publishing the auditing messages to a queue or topic, and a separate process will receive these messages and write a database or flat file. All data changes go through a data access layer of the application which logs all changes
- The database is constructed in such a way that logging information is included in each table, perhaps set via a trigger. This approach may adversely impact performance when inserts and updates are very frequent.
Q. What if you have to work with an existing legacy application?
A. Use triggers.
SQL Interview Questions and Answers
Spring Framework Difference between Primary key vs Foreign key in table – SQL database tutorial
Main difference between Primary key and Foreign key in a table is that, it’s the same column which behaves as primary key in parent table and as foreign key in child table. For example in Customer and Orderrelationship, customer_id is primary key in Customer table but foreign key in Order table. By the way what is foreign key in a table and difference between Primary and Foreign key are some of the popular SQL interview questions, much like truncate vs delete in SQL or difference between correlated and noncorrelated subquery. We have been learning key SQL concepts along with these frequently asked SQL questions and in this SQL tutorial we will discuss about what is foreign key in SQL and purpose of foreign key in any table. By the way this is the third article related to primary key in SQL, other being difference between primary and unique key and How to find second highest salary in SQL. If you are preparing for any technical job interview where you expect some SQL questions, check out these questions, they are worth preparing.
Spring Hibernate SQL Query to find all table names on database in MySQL and SQL Server Examples
How do you find names of all tables in a database is a recent SQL interview questions asked to one of my friend. There are many ways to find all table names form any database like MySQL and SQL Server. You can get table names either from INFORMATION_SCHEMA or sys.tables based upon whether you are using MySQL or Sql Server database. This is not a popular question like when to use truncate and delete or correlated vs noncorrelated subquery which you can expect almost all candidate prepare well but this is quite common if you are working on any database e.g. MySQL. In this SQL tutorial we will see examples of getting names of all tables from MySQL and SQL Server database. In MySQL there are two ways to find names of all tables, either by using "show" keyword or by query INFORMATION_SCHEMA. In case of SQL Server or MSSQL, You can either use sys.tables or INFORMATION_SCHEMA to get all table names for a database. By the way if you are new in MySQL server and exploring it , you may find this list of frequently used MySQL server commands handy.
Spring Framework What is Referential Integrity in Database or SQL - MySQL Example Tutorial
Referential Integrity is set of constraints applied to foreign key which prevents entering a row in child table (where you have foreign key) for which you don't have any corresponding row in parent table i.e. entering NULL or invalid foreign keys. Referential Integrity prevents your table from having incorrect or incomplete relationship e.g. If you have two tables Order and Customer where Customer is parent table with primary key customer_id and Order is child table with foreign key customer_id. Since as per business rules you can not have an Order without a Customer and this business rule can be implemented using referential integrity in SQL on relational database. Referential Integrity will cause failure on any INSERT or UPDATE SQL statement changing value of customer_id in child table, If value of customer_id is not present in Customer table. By the way What is Referential Integrity in SQL is also an important SQL question similar to finding second highest salary in SQL or difference between truncate and delete and should be prepared well before going for any job interview, where knowledge of SQL is one of the requirement.
Spring Framework Oracle Pagination SQL query example for Java programmer
Many time we need SQL query which return data page by page i.e. 30 or 40 records at a time, which can be specified as page size. In fact Database pagination is common requirement of Java web developers, especially dealing with largest data sets. In this article we will see how to query Oracle 10g database for pagination or how to retrieve data using paging from Oracle. Many Java programmer also use display tag for paging in JSP which supports both internal and external paging. In case of internal paging all data is loaded in to memory in one shot and display tag handles pagination based upon page size but it only suitable for small data where you can afford those many objects in memory. If you have hundreds of row to display than its best to use external pagination by asking databaseto do pagination. In pagination, ordering is another important aspect which can not be missed. It’s virtually impossible to sort large collection in Java using Comparator or Comparablebecause of limited memory available to Java program, sorting data in database using ORDER BY clause itself is good solution while doing paging in web application.
Spring Framework SQL query to add, modify and drop column in table with default value NOT NULL constraint – MySQL database
How to add column in existing table with default value is another popular SQL interview question asked for Junior level programming job interviews. Though syntax of SQL query to add column with default value varies little bit from database to database, it always been performed using ALTER keyword of ANSI SQL. Adding column in existing table in MySQL database is rather easy and straight forward and we will see example of SQL query for MySQL database which adds a column with default value. You can also provide constraints like NULL or NOT NULL while adding new column in table. In this SQL tutorial we are adding third column in a table called Contacts which contains name and phone of contacts. Now we want to add another column email with default value "abc@yahoo.com". We will use ALTER command in SQL to do that. By the way this is next in our SQL tutorials e.g. How to join three tables in SQL and SQL query to find duplicate records in table. If you haven’t read them yet, then you may find them useful.
Spring Framework How to create auto incremented identity column in SQL Server, MySQL, Sybase and Oracle ?
Automatic incremented ID, Sequence or Identity columns are those columns in any table whose value is automatically incremented by database based upon predefined rule. Almost all databases e.g. Microsoft SQL Server, MySQL, Oracleor Sybase supports auto incremented identity columns but in different ways like Oracle provides SEQUENCE object which can be used to generate automatic numbers, Microsoft SQL Server upto 2008 version provides IDENTITY() functions for similar purpose. Sybase also has IDENTITY function but little different than SQL Server and MySQL uses auto_incremented keyword to make any numeric column auto incremented. As first normal form advised about primary keys which is used to uniquely identity row and if there is no natural column or combination of column exists to act as primary key, mostly database developer use auto incremented surrogate keys which is used to uniquely identify each row. In this SQL tutorial we will see how to generate auto incremented ID column in Microsoft SQL Server, Oracle 11g, MySQL and Sybase ASE Server. By the way this SQL article is continuation of my earlier post on SQL and database like difference between truncate and delete in SQL and Finding second highest salary in MySQL and SQL Server. If you haven't got chance to read them than I suggest they are worth looking.
Java J2EE Spring Database Transaction Tutorial in SQL with Example for Beginners
Database transaction is an important concept to understand while working in database and SQL. Transaction in database is required to protect data and keep it consistent when multiple users access the database at same time. In this database transaction tutorial we will learn what is transaction in database, why do you need transaction in database, ACID properties of database transaction and an example of database transaction along with commit and rollback. Almost all vendors like Oracle, MySQL, SQL Server or Sybase provide transaction facility though MySQL only provide it for certain storage engine like InnoDB and BDB and not for MyISAM.
Spring Framework Sybase and SQL Server PATINDEX CHARINDEX Example to split String in Stored procedure
Some time we need to split a long comma separated String in Stored procedure e.g. Sybase or SQL Server stored procedures. Its quite common to pass comma delimited or delimiter separated String as input parameter to Stored procedure and than later split comma separated String into multiple values inside stored proc. This is not just case of input parameter but you can also have comma separated string in any table data. Unfortunately there is no split() function in Sybase or SQL Server 2005 or 2008 which can directly split string based on delimiter just like in Java string split method. Fortunately Sybase Adaptive Server and Microsoft SQL server has functions like CHARINDEX and PATINDEX which can be used to split comma separated String. This is next on our SQL tutorials after seeing SQL query to find duplicate records in table and How to find 2nd and Nth maximum salary in SQL.
Spring Hibernate Different types of JDBC drivers in Java - Quick overview
How many types of JDBC drivers in Java is a classical JDBC interview question , though I have not see this question recently but it was very popular during 2006 - 2008 period and still asked mostly on Junior programmer level interviews. There are mainly 4 types of JDBC drivers in Java, those are referred as type 1 to type 4 jdbc drivers. I agree its easy to remember them by type rather than with there actual name, Which I have yet to get in memory except plain old JDBC-ODBC bridge driver. By the way here are there full names :
Type 1 JDBC Driver is called JDBC-ODBC Bridge driver (bridge driver)
Type 2 JDBC Driver is referred as Native-API/partly Java driver (native driver)
Type 3 JDBC Driver is called AllJava/Net-protocol driver (middleware driver)
Type 4 JDBC Driver is called All Java/Native-protocol driver (Pure java driver)
This JDBC tutorial is in continuation of my earlier tutorials in JDBC like How to connect to Oracle database using JDBC
and 4 tips to improve performance of JDBC applications. If you are new here and haven't read them already, Its worth looking. Anyway out of all those 4 types, JDBC-ODBC Bridge driver is most common for connecting SQL Server, MS Access and mostly on training and development. here are quick review of all these four types of JDBC drivers. Also there has been some speculation of type 5 JDBC driver, I have to yet to see it.
and 4 tips to improve performance of JDBC applications. If you are new here and haven't read them already, Its worth looking. Anyway out of all those 4 types, JDBC-ODBC Bridge driver is most common for connecting SQL Server, MS Access and mostly on training and development. here are quick review of all these four types of JDBC drivers. Also there has been some speculation of type 5 JDBC driver, I have to yet to see it.
Spring Framework How to find second highest or maximum salary of Employee in SQL - Interview question
How to find second highest or second maximum salary of an Employee is one of the most frequently asked SQL interview question similar to finding duplicate records in tableand when to use truncate vs delete. There are many ways to find second highest salary based upon which database you are using as different database provides different feature which can be used to find second maximum or Nth maximum salary of employee. Well this question can also be generalized with other scenario like finding second maximum age etc. In this SQL tutorial we will see different example of SELECT SQL query to find second highest salary independent of databases or you may call in ANSI SQL and other SQL queries which uses database specific feature to find second maximum salary.
Spring Framework How to join three tables in SQL query – MySQL Example
Three table JOIN Example SQL
Read more »
Joining three tables in single SQL query can be very tricky if you are not good with concept of SQL Join. SQL Joins have always been tricky not only for new programmers but for many others, who are in programmingand SQL for more than 2 to 3 years. There are enough to confuse someone on SQL JOIN ranging from various types of SQL JOIN like INNER and OUTER join, LEFT and RIGHT outer join, CROSS join etc. Between all of these fundamentals, What is most important about Join is, combining multiple tables. If you need data from multiple tables in one SELECT query you need to use either subqueryor JOIN . Most of times we only join two tables like Employee and Department but some time you may require to join more than two tables and a popular case is joining three tables in SQL. In case of joining three tables table 1 relates to table 2 and than table 2 relates to table 3. If you look at closely you find that table 2 is a joining table which contains primary key from both table 1 and table 2. As I said it can be extremely confusing to understand join of three or more tables. I have found that understanding table relationship as primary key and foreign key helps to alleviate confusion than the classical matching row paradigm. SQL Join is also a very popular topic in SQL interviews and there is always been some questions from Join like Difference between INNER and OUTER JOIN, SQL query with JOIN e.g. Employee Departmentrelationship and Difference between LEFT and RIGHT OUTER JOIN etc. In short this is one of the most important topic in SQL both from experience and interview point of view.
Spring Framework SQL query to copy, duplicate or backup table in MySQL, Oracle and PostgreSQL database
Many times we need to create backup or copy of tables in database like MySQL, Oracle or PostgreSQL while modifying table schema like adding new columns, modifying column or dropping columns. Since its always best to have a backup of table which can be used in any event. I was looking for an easy way to create exact copy or duplicate tables which must be same in schema as well as in data, similar to creating copy of folder. Luckily there is an easy SQL query "CREATE table table_name AS" which allows you to create exact copy of table by executing just one SQL query. Yes, you read it correctly, no tool is required to create backup of table you just need to execute an SQL query. This is simply awesome given its importance and best part of this SQL query is that it works in almost all the database. I have tested it in MySQL and Oracle but t it should work perfectly find in other databases like PostgreSQL, SQL Server and DB2 as well. This SQL query tip is in continuation of my earlier SQL query examples like SQL query to find duplicate rows in a table and SQL query to join three tables in MySQL.
Java J2EE Spring How to use truncate and delete command in SQL >> Tutorial and Example
Delete and truncate command in SQL
Truncate and delete in SQL are two commands which is used to remove or delete data from table. Though quite basic in nature both sql commands can create lot of trouble until you are familiar with details before using it. Truncate and delete are not just important to understand perspective but also a very popular interview topic which in my opinion a definite worthy topic. What makes them tricky is amount of data. Since most of Electronic trading system stores large amount of transactional data and some even maintain historical data, good understanding of delete and truncate command is required to effectively work on those environment.I have still seen people firing delete command just to empty a table with millions of records which eventually lock the whole table for doing anything and take ages to complete or Simply blew log segment or hang the machine.
Java J2EE Spring 10 Example Queries of SQL Select Command
Select command in SQL is one of the most powerful and heavily used commands. This is I guess the first command any one learn in SQL even before CREATE which is used to create table in SQL. SELECT is used in SQL to fetch records from database tables and you can do a lot many things using Select. For example you can select all records, you can select few records based on condition specified in WHERE clause, select all columns using wild card (*) or only selecting few columns by explicitly declaring them in query.
Spring Framework How to find duplicate records in a table on database - SQL tips
How to find duplicate records in table is a popular SQL interview question which has been asked as many times as difference between truncate and delete in SQL or finding second highest salary of employee. Both of these SQL queries are must know for any one who is appearing on any programming interview where some questions on database and SQL are expected. In order to find duplicate records in database table you need to confirm definition of duplicates, for example in below contact table which is suppose to store name and phone number of contact, a record is considered to be duplicate if both name and phone number is same but unique if either of them varies. Problem of duplicates in database arise when you don't have a primary key or unique key on database and that's why its recommended to have a key column in table. Anyway its easy to find duplicate records in table by using group by clause of ANSI SQL. Group by clause is used to group data based upon any column or a number of columns. Here in order to locate duplicate records we need to use group by clause on both name and phone as shown in second SQL SELECT query example. You can see in first query that it listed Ruby as duplicate record even though both Ruby have different phone number because we only performed group by on name. Once you have grouped data you can filter out duplicates by using having clause. Having clause is counter part of where clause for aggregation queries. Just remember to provide temporary name to count() data in order to use them in having clause.
Java J2EE Spring Storing username and password using Struts and Jdbc
This is the application to persist the data into database from jsp pages using jdbc and struts.
The required components to develop Struts with jdbc:
Required elements to our project:
From the beginning the end user submits the request from JSP form(Login.jsp) to struts based web application.Using struts-html.tld file we generate the html tags in jsp page to read username and password and for submit button.
Before that we should create a table 'login ' in mysql database as:
create table login (username varchar(10),password varchar(10));
In the Login page the logical path is login to send the action path to Controller in web.xml.
#1). Login.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib uri="/WEB-INF/struts-html.tld" prefix="html" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<html:form action="/login" >
User Name : <html:text name="LoginForm" property="uName" /> <br>
Password : <html:password name="LoginForm" property="pWd" /> <br>
<html:submit value="Login" />
</html:form>
</body>
</html>
In web.xml the The struts-config.xml will read by the ActionServlet which is configured in init-params.
#2). web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<servlet>
<servlet-name>action</servlet-name>
<servlet-class>org.apache.struts.action.ActionServlet</servlet-class>
<init-param>
<param-name>config</param-name>
<param-value>/WEB-INF/struts-config.xml</param-value>
</init-param>
<init-param>
<param-name>debug</param-name>
<param-value>2</param-value>
</init-param>
<init-param>
<param-name>detail</param-name>
<param-value>2</param-value>
</init-param>
<load-on-startup>2</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>action</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>
<session-config>
<session-timeout>30</session-timeout>
</session-config>
<welcome-file-list>
<welcome-file>Login.jsp</welcome-file>
</welcome-file-list>
<jsp-config>
<taglib>
<taglib-uri>/WEB-INF/struts-html.tld</taglib-uri>
<taglib-location>/WEB-INF/struts-html.tld</taglib-location>
</taglib>
</jsp-config>
</web-app>
Based on configurations Action Servlet forwards to struts-config.xml to check the LoginForm name and ActionMappings name to create the LoginForm instance.And the LoginForm object will be created by using form object.Then read the form values keep it in string variables.
#3). struts-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts-config PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 1.2//EN"
"http://jakarta.apache.org/struts/dtds/struts-config_1_2.dtd">
<struts-config>
<!-- ========== Form Bean Definitions ================= -->
<form-beans>
<form-bean name="LoginForm" type="com.javabynataraj.login.LoginForm" />
</form-beans>
<!-- ========== Action Mapping Definitions ============ -->
<action-mappings>
<action name="LoginForm" path="/login" scope="session"
type="com.javabynataraj.login.LoginAction">
<forward name="success" path="/success.jsp" />
<forward name="failure" path="/failure.jsp" />
</action>
</action-mappings>
</struts-config>
#4). LoginForm.java
package com.javabynataraj.login;
import org.apache.struts.action.ActionForm;
public class LoginForm extends ActionForm{
private String uName;
private String pWd;
public String getuName() {
return uName;
}
public void setuName(String uName) {
this.uName = uName;
}
public String getpWd() {
return pWd;
}
public void setpWd(String pWd) {
this.pWd = pWd;
}
}
#5). LoginAction.java
package com.javabynataraj.login;
import java.sql.Connection;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import com.javabynataraj.dao.LoginDao;
public class LoginAction extends Action {
public ActionForward execute(ActionMapping mapping,ActionForm form,HttpServletRequest request,HttpServletResponse response){
Connection con = null;
String key = null;
try{
//get the values from LoginForm by creating an object
LoginForm lf = (LoginForm)form;
String un = lf.getuName();
String pw = lf.getpWd();
LoginDao dao=new LoginDao();
//pass the un,pw values to the LoginDao class of insert method
int i=dao.insert(un,pw);
if(i>0){
key="success";
}
else
{
key="failure";
}
}
catch(Exception e){
e.printStackTrace();
}
return mapping.findForward(key);
}
}
By creating the Connection object we can connect with database (MySql).
#6). LoginDao.java
package com.javabynataraj.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.javabynataraj.dbcon.DBConnection;
public class LoginDao {
public int insert(String un,String pw){
int r=0;
PreparedStatement ps = null;
//get connection object from DBConnection class
Connection con = DBConnection.dbConn();
System.out.println("Connection"+con);
try{
//use PreparedStatement to insert the values of username and password
ps=con.prepareStatement("insert into login(username,password) values(?,?)");
ps.setString(1, un);
ps.setString(2, pw);
r = ps.executeUpdate();
}
catch(SQLException ex) {
try {
ps.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.err.println("SQLException: " + ex.getMessage());
}
return r;
}
}
#7). DBConnection.java
package com.javabynataraj.dbcon;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection {
public static Connection dbConn(){
Connection conn = null;
String url = "jdbc:mysql://192.168.1.101:3306/";
String dbName = "TEST";
String driver = "com.mysql.jdbc.Driver";
String userName = "test";
String password = "test";
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+dbName,userName,password);
System.out.println("Connected to the database");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
#8). success.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<h2><font color="green">Successfully done..!</font></h2>
</body>
</html>
#9). failure.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<h2><font color="red">Please try again..!</font></h2>
</body>
</html>
By running this code in Tomcat5.5 we can see the page Login.jsp as configured in web.xml welcome-file-list.
If any connection problems and some error will occurred in processing then the failure.jsp will displayed.
Download SOURCE
Spring Framework Difference between LEFT and RIGHT OUTER Joins in SQL - MySQL Join example
There are two kinds of OUTER joins in SQL, LEFT OUTER join and RIGHT OUTER join. Main difference between RIGHT OUTER join and LEFT OUTER join, as there name suggest, is inclusion of non matched rows. Sine INNER join only include matching rows, where value of joining column is same, in final result set, but OUTER join extends that functionality and also include unmatched rows in final result. LEFT outer join includes unmatched rows from table written on left of join predicate. On the other hand RIGHT OUTER join, along with all matching rows, includes unmatched rows from right side of table. In short result of LEFT outer join is INNER JOIN + unmatched rows from LEFT table and RIGHT OUTER join is INNER JOIN + unmatched rows from right hand side table. Similar to difference between INNER join and OUTER join, difference between LEFT and RIGHT OUTER JOIN can be better understand by a simple example, which we will see in next section. By the way joins are very popular in SQL interviews, and along with classic questions like finding second highest salary of employee,Inner join vs outer join or left outer join vs right outer join is commonly asked.
Spring Framework Migrating SQL Query from Oracle to SQL Server 2008 or Sybase
Oracle and Microsoft SQL Server are very different than each other and if you are migrating SQL queries or database, tables from Oracle 11g database to Microsoft 2008 SQL server than you are bound to face some issues. Main reason of these porting issues are features, which are supported and exists in Oracle database, but not available in Microsoft SQL Server 2008 like SEQUENCE, Order by clause in sub queries and derived tables, derived table without name etc. I am sure there are few more and it will surface based upon different database objects you are using in your tables and queries. On other hand SQL Engine for SQL Server and Sybase are very much similar, at least syntactically, and if you are migrating queries from SQL Server to Sybase you can do that without much hassle, of course there will be slight changes but not as much like Oracle. So if you are migrating from Oracle to Sybase or SQL Server its most likely same job and you should first start with either SQL Server or Sybase ASE and than later migrate them from each other. In this Oracle and SQL Server tutorial we will see couple of examples, where Oracle and SQL Server are different and how to change those SQL queries so that it can run on Microsoft SQL Server. By the way I have also written couple of post on queries like 10 ways to use SQL SELECT queriesand Don’t delete, truncate it. If you like reading more on SQL queries than those are for you.
Subscribe to:
Posts (Atom)