Factory Method Pattern

Define an interface or abstract class and let the subclass  decide which class to instantiate. This is a principle behind factory method. This also know as Virtual Constructor.

 

 

 

Advertisements

Data Access Object Design Pattern Java

DAO design pattern works with Data transfer object also known as value object.

DTO is a java class with properties, getter and setter methods.

Now let us create a DTO class.

package com;

import java.io.Serializable;

public class User implements Serializable {

private static final long serialVersionUID = 1L;

private int userId;
private String name;
private String designation;
private int age;

public int getUserId() {
return userId;
}

public void setUserId(int userId) {
this.userId = userId;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getDesignation() {
return designation;
}

public void setDesignation(String designation) {
this.designation = designation;
}

public int getAge() {
return age;
}

public void setAge(int age) {
this.age = age;
}

}

If you See, class definition ,the user class has private field declared as private with getter/setter methods.  The class is serializable because we want to pass DTO between different JVM’s using RMI or any other protocol, it should implement Serializable interface.

TO create DAO Class, We Need to implement following steps.

1.An interface which defines methods for various operations related to DTO

2. Concreate classes which implement DAO interface

3. Factory/Abstract Factory class to get a reference to DAO object.

Now implementing Interface.

package com;

public interface UserDAO {

public void insert(User user);

public void update(User user);

public void delete(int userId);

public User[] findAll();

public User findByKey(int userId);

}

and Implementation class

package com;

public class UserDAOImpl implements UserDAO{

@Override
public void insert(User user) {
// TODO Auto-generated method stub

}

@Override
public void update(User user) {
// TODO Auto-generated method stub

}

@Override
public void delete(int userId) {
// TODO Auto-generated method stub

}

@Override
public User[] findAll() {
// TODO Auto-generated method stub
return null;
}

@Override
public User findByKey(int userId) {
// TODO Auto-generated method stub
return null;
}

}

Query to Get Column name and DataType

SELECT column_name,data_type
FROM information_schema.columns
WHERE table_schema = ‘public’
AND table_name = ‘Table Name’

 

output :

“id”;”bigint”
“unitid”;”bigint”
“itemcode”;”character varying”
“brandname”;”character varying”
“strength”;”character varying”
“itemname”;”character varying”
“moleculename”;”bigint”

Postgres Join Delete Query

SQL-99 does not allow to delete data in a table when using JOINS. The reasons for this are quite obscure. Maybe it was considered too complicated to implement. Therefore for safety reasons it was not part of the standard.

As a result, MySQL implemented this feature and PostgreSQL rejected it.

The following query works under MySQL but fails under PostgreSQL:

 

DELETE h
FROM history AS h 
INNER JOIN term_node AS tn ON (h.nid = tn.nid) 
INNER JOIN term_data AS td ON (td.tid = tn.tid) 
WHERE h.uid = 2067 AND td.vid = 2

Sql Inner Join Update Query

create table 1 and table 2

 

CREATE TABLE Table1
(
Id INT,
FruitName Varchar( 100)
);

CREATE TABLE Table2
(
Id INT,
FruitName Varchar( 100)
);

Insert into Table1 Values ( 1,’Mango’ ), ( 2, ‘Graps’), (3, ‘Banana’);
Insert into Table2 Values ( 1,’Mango’ ), ( 2, ‘vitthal’), (4, ‘Orange’);
SELECT * FROM Table1 ;
SELECT * FROM Table2 ;
SELECT *
FROM Table1 T1 Inner Join Table2 T2
ON T1. Id = T2 .Id;

update table2 set FruitName=null where id>1

UPDATE Table2
SET FruitName = t1.FruitName
FROM Table1 as t1 INNER JOIN Table2 T2
ON T1. Id = t2.Id;

Sql Data Type and Java Data Type Mapping

Sql Type                                                       Simply mappable                        Object Mappable

CHARACTER String
VARCHAR String
LONGVARCHAR String
NUMERIC java.math.BigDecimal
DECIMAL java.math.BigDecimal
BIT boolean Boolean
TINYINT byte Integer
SMALLINT short Integer
INTEGER int Integer
BIGINT long Long
REAL float Float
FLOAT double Double
DOUBLE PRECISION double Double
BINARY byte[]
VARBINARY byte[]
LONGVARBINARY byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp

SELECT DISTINCT clause

The syntax of the DISTINCT clause is as follows:

The DISTINCT clause is used in the SELECT statement to remove duplicate rows from a result set. The DISTINCT clause keeps one row for each group of duplicates. You can use the DISTINCT clause on one or more columns of a table.

SELECT DISTINCT
column_1
FROM table_name
SELECT DISTINCT
column_1,
column_2
FROM
tbl_name;
Example :
Distinct
SELECT DISTINCT  bcolor  FROM t1 ORDER BY bcolor;
Distinct_OP.PNG

PostgreSQL DISTINCT on multiple columns

SELECT DISTINCT  bcolor, fcolor FROM t1 ORDER BY bcolor,      fcolor;
distinct2.PNG
Because we specified both bcolor and fcolor columns in the SELECT DISTINCT clause, PostgreSQL combined the values in both bcolor and fcolor columns to evaluate the uniqueness of rows.

PostgreSQL DISTINCT ON ORDER BY example

The following statement sorts the result set by the  bcolor and  fcolor, and then for each group of duplicates, it keeps the first row in the returned result set.

SELECT DISTINCT ON (bcolor) bcolor, fcolor FROM t1 ORDER BY bcolor, fcolor;
distinct3.PNG