Joins in SQL Server allows the retrieval of data records from one or more tables having some relation between them. Logical operators can also be used to drill down the number of records to get the desired output from sql join queries.
Inner Join: Inner Join is a default type join of SQL Server. It uses logical operators such as =, >,< to match the records in two tables. Inner Join includes equi join and natural joins.
SQL Inner Join Examples
SELECT C.CATEGORYID, C.CATEGORYNAME, P.PRODUCTID, P.PRODUCTNAME, P.UNITPRICE FROM CATEGORIES C INNER JOINPRODUCTS P ON P.CATEGORYID = C.CATEGORYIDWHERE P.UNITPRICE = 10ORDER BY C.CATEGORYNAME, P.PRODUCTNAME
SQL Inner Natural
This inner join query will return the categoryid, categoryname, productid, productname, unitprice where product unit price = 10
SQL Inner Natural Join Examples
SELECT C.*, P.PRODUCTID, P.PRODUCTNAME FROM CATEGORIES C INNER JOINPRODUCTS P ON P.CATEGORYID = C.CATEGORYID
This natural join query will return all the columns of categories table and prodcutId and productName from products table. You can further modify this natural inner join query as per your requirements to visualize the data by specifying the column names of categories table also.
SQL Inner Equi Join Examples
Inner join is a default type of SQL Join that return the records matching in all the tables joined in sql query satisfying the condition specified in WHERE clause.
Inner join includes 3 types of joins similar to one another.
Self Join: Self join joins a single sql database table to itself. Equi Join: Equi Join returns all the columns from both tables and filters the records satisfying the matching condition specified in Join “ON” statement of sql inner join query.
USE NORTHWIND SELECT * FROM CATEGORIES C INNER JOINPRODUCTS P ON P.CATEGORYID = C.CATEGORYID
Result will display the following columns: CategoryID, CategoryName, Description, Picture, ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued Above equi join sql query will display the categoryId two times in a row because both the tables have categoryId column. You can convert the result into natural join by elimination the identical columns and unnecessary columns.
Outer Join: Outer Join has further 3 sub categories as left, right and full. Outer Join uses these category names as keywords that can be specified in the FROM clause. Left Outer Join: Left Outer Join returns all the rows from the table specified first in the Left Outer Join Clause. If in the left table any row has no matching record in the right side table then that row returns null column values for that particular tuple. Inner joins return only those rows from both sql database tables having matching records in both the tables whereas left outer join returns all the rows from the left table and related matching records from the other one.
SQL Left Outer Join Example:
SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAMEFROM AUTHORS A LEFT OUTER JOIN PUBLISHERS PON A.CITY = P.CITYORDER BY A.AU_LNAME, A.AU_FNAME
This left outer join query retrieves the author names and publisher name having same cities. Here all rows retrieved from the left table i.e. authors and publisher name having the similar city other columns of pub_name column are null due to no match found in the right table. Right Outer Join: Right Outer Join is exactly the reverse method of Left Outer Join. It returns all the rows from right table and returns null values for the rows having no match in the left joined table. Just change the left keyword to right outer join in above example; you will get the reverse output of left outer join in the form of right outer join.
SQL Right Outer Join query Example:
SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAMEFROM AUTHORS A RIGHT OUTER JOIN PUBLISHERS PON A.CITY = P.CITYORDER BY A.AU_LNAME, A.AU_FNAME
Full Outer Join: Full outer join returns all the rows from both left and right joined tables. If there is any match missing from the left table then it returns null column values for left side table and if there is any match missing from right table then it returns null value columns for the right side table. To retrieve all the records from left as well as right table unless the records have matching relations in each row you can use SQL FULL OUTER JOIN. You can consider the examples of last two articles about left outer join and right outer join, in which left outer join retrieves all records from the left table and as all records of right table in right outer join along with null values for the columns having no matching records in any tuple. To retain all the records of left as well as right table along with null values for non matching rows displaying the combination of results of left outer and right outer join, FULL OUTER JOIN is the best solution.
SQL FULL outer join example:
SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAMEFROM AUTHORS A FULL OUTER JOIN PUBLISHERS PON A.CITY = P.CITYORDER BY A.AU_LNAME, A.AU_FNAME
Cross Join: Cross join works as a Cartesian product of rows for both left and right table. It combined each row of left table with all the rows of right table. SQL Cross join returns the output result as a Cartesian product of both database tables. Let left table has 10 rows and right table has 8 rows then SQL CROSS Join will return 180 rows combining each record of left table with all records of right side table.
Consider the following example of CROSS Join:
USE PUBSSELECT AU_FNAME, AU_LNAME, PUB_NAMEFROM AUTHORS CROSS JOIN PUBLISHERS ORDER BY AU_FNAME
Above cross join will return 23 * 8 = 184 results by multiplying each row of authors table with publishers table. SQL CROSS Join with WHERE clause By just adding the where clause with Cross join sql query it turns the output result into inner join.
Aug 22, 2009
Aug 5, 2009
Call-by-value Vs Call-by-reference in Java
The terms "Call-by-value" semantics and "Call-by-reference" semantics have very precise definitions, and they're often horribly abused when folks talk about Java. I want to correct that... The following is how I'd describe these
Call-by-value
The actual parameter (or argument expression) is fully evaluated and the resulting value is copied into a location being used to hold the formal parameter's value during method/function execution. That location is typically a chunk of memory on the runtime stack for the application (which is how Java handles it), but other languages could choose parameter storage differently.
Call-by-reference
The formal parameter merely acts as an alias for the actual parameter. Anytime the method/function uses the formal parameter (for reading or writing), it is actually using the actual parameter.
When you call a method by reference, the callee sees the caller’s original variables passed as parameters, not copies. References to the callee’s objects are treated the same way. Thus any changes the callee makes to the caller’s variables affect the caller’s original variables. Java never uses call by reference. Java always uses call by value.
How do you fake call by reference in Java, or more precisely, how can a callee influence the values of it’s caller’s variables?
* Use a holder/wrapper object passed to the callee as a parameter. The callee can change the object’s fields. That object may be as simple as an Object[]. In Java, a callee may change the fields of a caller’s object passed as a parameter, but not the caller’s reference to that object. It can’t make the caller’s variable passed as a parameter point to a different object. It can only make its local copy point to a different object. A holder class is just a class with fields to hold your values. It has no methods other than accessors for the fields.
* Have the callee return the new values, perhaps wrapped in an holder class or Object[], and have the caller store them away somewhere.
* Communicate via a static or instance variable visible to both caller and callee.
* Use a delegate object. The callee calls its methods to save the results for the caller.
Dale King points out that attempts to fake call by reference are usually a sign of poor object-oriented design. A function should not be trying to return more than one thing. He uses the term thing because it is proper to return more than one value (e.g. returning a Point that contains two values). If you are trying to return two values, the test he like to apply is whether you can come up with a logical name for the values as a group. If you can’t, you had better look to see if maybe you what you have is really two functions lumped together.
Java is strictly Call-by-value
Call-by-value
The actual parameter (or argument expression) is fully evaluated and the resulting value is copied into a location being used to hold the formal parameter's value during method/function execution. That location is typically a chunk of memory on the runtime stack for the application (which is how Java handles it), but other languages could choose parameter storage differently.
Call-by-reference
The formal parameter merely acts as an alias for the actual parameter. Anytime the method/function uses the formal parameter (for reading or writing), it is actually using the actual parameter.
When you call a method by reference, the callee sees the caller’s original variables passed as parameters, not copies. References to the callee’s objects are treated the same way. Thus any changes the callee makes to the caller’s variables affect the caller’s original variables. Java never uses call by reference. Java always uses call by value.
How do you fake call by reference in Java, or more precisely, how can a callee influence the values of it’s caller’s variables?
* Use a holder/wrapper object passed to the callee as a parameter. The callee can change the object’s fields. That object may be as simple as an Object[]. In Java, a callee may change the fields of a caller’s object passed as a parameter, but not the caller’s reference to that object. It can’t make the caller’s variable passed as a parameter point to a different object. It can only make its local copy point to a different object. A holder class is just a class with fields to hold your values. It has no methods other than accessors for the fields.
* Have the callee return the new values, perhaps wrapped in an holder class or Object[], and have the caller store them away somewhere.
* Communicate via a static or instance variable visible to both caller and callee.
* Use a delegate object. The callee calls its methods to save the results for the caller.
Dale King points out that attempts to fake call by reference are usually a sign of poor object-oriented design. A function should not be trying to return more than one thing. He uses the term thing because it is proper to return more than one value (e.g. returning a Point that contains two values). If you are trying to return two values, the test he like to apply is whether you can come up with a logical name for the values as a group. If you can’t, you had better look to see if maybe you what you have is really two functions lumped together.
Java is strictly Call-by-value
Aug 1, 2009
Serializing and Deserializing Objects
Serializing and Deserializing Objects
The serialization mechanism in Java provides the means for persisting objects beyond a single run of a Java program. To serialize an object, make sure that the declaring class implements the java.io.Serializable interface. Then obtain an ObjectOutputStream to write the object to and call the writeObject() method on the ObjectOutputStream. To deserialize an object, obtain an ObjectInputStream to read the object from and call the readObject() method on the ObjectInputStream. The following code excerpts illustrate how an object is stored in the file by Serialize and getting object from file by Deserialize.
private static String fileName = "./objectholder.ser";
public static void serializeObject(Object obj) {
try {
ObjectOutput out = new ObjectOutputStream(new FileOutputStream(fileName));
out.writeObject(obj);
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public static Object deSerializeObject(){
try {
Object obj = null;
File file = new File(fileName);
if(file.exists()) {
ObjectInputStream in = new ObjectInputStream(new FileInputStream(file));
// Deserialize the object
obj = in.readObject();
in.close();
}
return obj;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
how to call these methods?
//serialize the object
MyObject fileObjs = new MyObject();
serializeObject(fileObjs);
// Deserialize the object
MyObject fileObjs = (MyObject)deSerializeObject();
Where MyObject is class with implements of Serializable interface.
Serialize the object myObject of type MyObject. The file output stream is created for the file named myObject.ser. The object is actually persisted in this file on ,and read the object back from the file objectholder.ser. If you list the files in the directory where this code's .class file is stored, you will see a new file called objectholder.ser added to the listing.
The serialization mechanism in Java provides the means for persisting objects beyond a single run of a Java program. To serialize an object, make sure that the declaring class implements the java.io.Serializable interface. Then obtain an ObjectOutputStream to write the object to and call the writeObject() method on the ObjectOutputStream. To deserialize an object, obtain an ObjectInputStream to read the object from and call the readObject() method on the ObjectInputStream. The following code excerpts illustrate how an object is stored in the file by Serialize and getting object from file by Deserialize.
private static String fileName = "./objectholder.ser";
public static void serializeObject(Object obj) {
try {
ObjectOutput out = new ObjectOutputStream(new FileOutputStream(fileName));
out.writeObject(obj);
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public static Object deSerializeObject(){
try {
Object obj = null;
File file = new File(fileName);
if(file.exists()) {
ObjectInputStream in = new ObjectInputStream(new FileInputStream(file));
// Deserialize the object
obj = in.readObject();
in.close();
}
return obj;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
how to call these methods?
//serialize the object
MyObject fileObjs = new MyObject();
serializeObject(fileObjs);
// Deserialize the object
MyObject fileObjs = (MyObject)deSerializeObject();
Where MyObject is class with implements of Serializable interface.
Serialize the object myObject of type MyObject. The file output stream is created for the file named myObject.ser. The object is actually persisted in this file on ,and read the object back from the file objectholder.ser. If you list the files in the directory where this code's .class file is stored, you will see a new file called objectholder.ser added to the listing.
Jul 31, 2009
Basic Architecture of Hibernate
Introduction to Hibernate:
Hibernate is an Object-Relational Mapping (ORM) solution for JAVA. It is a powerful, highperformance object/relational persistence and query service. It allows us to develop persistent classesfollowing object-oriented idiom – including association, inheritance and polymorphism.
Hibernate Architecture
1) itself opens connection to database,
2) converts HQL (Hibernate Query Language) statements to database specific statement,
3) receives result set,
4) then performs mapping of these database specific data to Java objects which are directly used by Java application.
Hibernate uses the database specification from Hibernate Properties file. Automatic mapping is performed on the basis of the properties defined in hbm XML file defined for particular Java object.
JDBC Vs Hibernate
Why is Hibernate better than JDBC
1) Relational Persistence for JAVAWorking with both Object-Oriented software and Relational Database is complicated task withJDBC because there is mismatch between how data is represented in objects versus relationaldatabase. So with JDBC, developer has to write code to map an object model's data representationto a relational data model and its corresponding database schema. Hibernate is flexible andpowerful ORM solution to map Java classes to database tables. Hibernate itself takes care of thismapping using XML files so developer does not need to write code for this.
2) Transparent PersistenceThe automatic mapping of Java objects with database tables and vice versa is called TransparentPersistence. Hibernate provides transparent persistence and developer does not need to write codeexplicitly to map database tables tuples to application objects during interaction with RDBMS.With JDBC this conversion is to be taken care of by the developer manually with lines of code.
3) Support for Query LanguageJDBC supports only native Structured Query Language (SQL). Developer has to find out theefficient way to access database, i.e to select effective query from a number of queries to performsame task. Hibernate provides a powerful query language Hibernate Query Language(independent from type of database) that is expressed in a familiar SQL like syntax and includesfull support for polymorphic queries. Hibernate also supports native SQL statements. It also selectsan effective way to perform a database manipulation task for an application.
4) Database Dependent CodeApplication using JDBC to handle persistent data (database tables) having database specific codein large amount. The code written to map table data to application objects and vice versa isactually to map table fields to object properties. As table changed or database changed then it’sessential to change object structure as well as to change code written to map table-to-object/objectto-table. Hibernate provides this mapping itself. The actual mapping between tables andapplication objects is done in XML files. If there is change in Database or in any table then theonly need to change XML file properties.
5) Maintenance CostWith JDBC, it is developer’s responsibility to handle JDBC result set and convert it to Java objectsthrough code to use this persistent data in application. So with JDBC, mapping between Javaobjects and database tables is done manually. Hibernate reduces lines of code by maintainingobject-table mapping itself and returns result to application in form of Java objects. It relievesprogrammer from manual handling of persistent data, hence reducing the development time andmaintenance cost.
6) Optimize PerformanceCaching is retention of data, usually in application to reduce disk access. Hibernate, withTransparent Persistence, cache is set to application work space. Relational tuples are moved to thiscache as a result of query. It improves performance if client application reads same data manytimes for same write. Automatic Transparent Persistence allows the developer to concentrate moreon business logic rather than this application code. With JDBC, caching is maintained by handcoding.
7) Automatic Versioning and Time StampingBy database versioning one can be assured that the changes done by one person is not being rollbacked by another one unintentionally. Hibernate enables developer to define version type field toapplication, due to this defined field Hibernate updates version field of database table every timerelational tuple is updated in form of Java class object to that table. So if two users retrieve sametuple and then modify it and one user save this modified tuple to database, version is automaticallyupdated for this tuple by Hibernate. When other user tries to save updated tuple to database then itdoes not allow to save it because this user does not has updated data. In JDBC there is no checkthat always every user has updated data. This check has to be added by the developer.
8) Open-Source, Zero-Cost Product LicenseHibernate is an open source and free to use for both development and production deployments.
9) Enterprise-Class Reliability and ScalabilityHibernate scales well in any environment, no matter if use it in-house Intranet that serves hundredsof users or for mission-critical applications that serve hundreds of thousands. JDBC can not bescaled easily.Hibernate Made Easy: Simplified Data Persistence with Hibernate and JPA (Java Persistence API) Annotations
Disadvantages of Hibernate
1) Steep learning curve.
2) Use of Hibernate is an overhead for the applications which are :• simple and use one database that never change• need to put data to database tables, no further SQL queries• there are no objects which are mapped to two different tablesHibernate increases extra layers and complexity. So for these types of applications JDBC is thebest choice.
3) Support for Hibernate on Internet is not sufficient.
4) Anybody wanting to maintain application using Hibernate will need to know Hibernate.
5) For complex data, mapping from Object-to-tables and vise versa reduces performance andincreases time of conversion.
6) Hibernate does not allow some type of queries which are supported by JDBC. For example It doesnot allow to insert multiple objects (persistent data) to same table using single query. Developerhas to write separate query to insert each object.
1) Relational Persistence for JAVAWorking with both Object-Oriented software and Relational Database is complicated task withJDBC because there is mismatch between how data is represented in objects versus relationaldatabase. So with JDBC, developer has to write code to map an object model's data representationto a relational data model and its corresponding database schema. Hibernate is flexible andpowerful ORM solution to map Java classes to database tables. Hibernate itself takes care of thismapping using XML files so developer does not need to write code for this.
2) Transparent PersistenceThe automatic mapping of Java objects with database tables and vice versa is called TransparentPersistence. Hibernate provides transparent persistence and developer does not need to write codeexplicitly to map database tables tuples to application objects during interaction with RDBMS.With JDBC this conversion is to be taken care of by the developer manually with lines of code.
3) Support for Query LanguageJDBC supports only native Structured Query Language (SQL). Developer has to find out theefficient way to access database, i.e to select effective query from a number of queries to performsame task. Hibernate provides a powerful query language Hibernate Query Language(independent from type of database) that is expressed in a familiar SQL like syntax and includesfull support for polymorphic queries. Hibernate also supports native SQL statements. It also selectsan effective way to perform a database manipulation task for an application.
4) Database Dependent CodeApplication using JDBC to handle persistent data (database tables) having database specific codein large amount. The code written to map table data to application objects and vice versa isactually to map table fields to object properties. As table changed or database changed then it’sessential to change object structure as well as to change code written to map table-to-object/objectto-table. Hibernate provides this mapping itself. The actual mapping between tables andapplication objects is done in XML files. If there is change in Database or in any table then theonly need to change XML file properties.
5) Maintenance CostWith JDBC, it is developer’s responsibility to handle JDBC result set and convert it to Java objectsthrough code to use this persistent data in application. So with JDBC, mapping between Javaobjects and database tables is done manually. Hibernate reduces lines of code by maintainingobject-table mapping itself and returns result to application in form of Java objects. It relievesprogrammer from manual handling of persistent data, hence reducing the development time andmaintenance cost.
6) Optimize PerformanceCaching is retention of data, usually in application to reduce disk access. Hibernate, withTransparent Persistence, cache is set to application work space. Relational tuples are moved to thiscache as a result of query. It improves performance if client application reads same data manytimes for same write. Automatic Transparent Persistence allows the developer to concentrate moreon business logic rather than this application code. With JDBC, caching is maintained by handcoding.
7) Automatic Versioning and Time StampingBy database versioning one can be assured that the changes done by one person is not being rollbacked by another one unintentionally. Hibernate enables developer to define version type field toapplication, due to this defined field Hibernate updates version field of database table every timerelational tuple is updated in form of Java class object to that table. So if two users retrieve sametuple and then modify it and one user save this modified tuple to database, version is automaticallyupdated for this tuple by Hibernate. When other user tries to save updated tuple to database then itdoes not allow to save it because this user does not has updated data. In JDBC there is no checkthat always every user has updated data. This check has to be added by the developer.
8) Open-Source, Zero-Cost Product LicenseHibernate is an open source and free to use for both development and production deployments.
9) Enterprise-Class Reliability and ScalabilityHibernate scales well in any environment, no matter if use it in-house Intranet that serves hundredsof users or for mission-critical applications that serve hundreds of thousands. JDBC can not bescaled easily.Hibernate Made Easy: Simplified Data Persistence with Hibernate and JPA (Java Persistence API) Annotations
Disadvantages of Hibernate
1) Steep learning curve.
2) Use of Hibernate is an overhead for the applications which are :• simple and use one database that never change• need to put data to database tables, no further SQL queries• there are no objects which are mapped to two different tablesHibernate increases extra layers and complexity. So for these types of applications JDBC is thebest choice.
3) Support for Hibernate on Internet is not sufficient.
4) Anybody wanting to maintain application using Hibernate will need to know Hibernate.
5) For complex data, mapping from Object-to-tables and vise versa reduces performance andincreases time of conversion.
6) Hibernate does not allow some type of queries which are supported by JDBC. For example It doesnot allow to insert multiple objects (persistent data) to same table using single query. Developerhas to write separate query to insert each object.
Subscribe to:
Posts (Atom)