You are here
Home >

Important Database Logics & Queries

Apart from Java technology we need to have a good hold on database concepts as a Java Techie. No real time project is complete without a database. Many a time we come across recurring database logics and queries in small or big project. So in this section we will learn about solutions of various recurring problems as part of Database Logics & Queries.

 How to get rows starting from 10 to 30 from database ?

       If you are using Hibernate to get records

int rowFrom= 10;
int numberOfRecords = 20; 
List listOfRecords = getHibernateTemplate().findByCriteria(DetachedCriteria.forClass(Entity.class),rowFrom,numberOfRecords);

OR

org.hibernate.Criteria criteria=this.getSession().createCriteria(Entity.class); 
criteria.setFirstResult(rowFrom));
criteria.setMaxResults(numberOfRecords);
List listOfRecords = criteria.list();

       If you are using mySQL

select * from employee LIMIT 10,20 ;

How to update Column datatype in SQL Server?

ALTER TABLE [dbo].[Employee] ALTER COLUMN [salary] numeric(19,0);
ALTER TABLE [dbo].[Employee] ALTER COLUMN [salary] BIGINT;

How to add additional Column manually in SQL Server database?

ALTER TABLE [dbo].[Employee] ADD joining_date DATETIME NULL ;
ALTER TABLE [dbo].[Employee] ADD noOfDays_worked INT NOT NULL DEFAULT(0);

How to convert Date formats of SQL Server as per our need?

Suppose we want Date format other than provided by SQL Server by default, we can use the following query :

Converting DATETIME to VARCHAR :

SELECT CONVERT(VARCHAR(20),GETDATE(),101);

Converting VARCHAR to DATETIME :

SELECT CONVERT(DATETIME, '05/04/2013', 101);

​Converting VARCHAR to DATETIME :

SELECT CAST('2014-01-10 17:07:09' AS DATETIME);

here 101 is the style of DATE in SQL Server. It represents date in mm/dd/yyyy(’05/04/2013′) format.

Similarly there are many date styles in SQL Server. Useful when we need different format to show in UI. Also useful when there is an exception in your java code because of the different date formats.

 What Database Logics & Queries will you apply to convert Text to Integer in SQL Server?

​If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.

​SELECT CAST(YourVarcharCol  AS  INT) FROM Table;

​                              OR

SELECT CONVERT(INT, YourVarcharCol) FROM Table;

What is the difference between CAST & CONVERT ?

CONVERT is SQL Server specific, CAST is ANSI.

Convert provides more flexibility than Cast. So if you are trying to know which one to use, I would suggest Cast unless you have a specific reason to use Convert. As a suggestion;

​Dates, times : CONVERT
Decimals, numerics : CAST

What are commonly used DATE & TIME functions in SQL Server ?

  • GETDATE()

  • returns exact date time from the system. It does not accept any parameter.

  • DATEADD()

  • -used to add or subtract datetime. Its return a new datetime based on the added or subtracted interval.

​          DATEADD(datepart, number, date) :

SELECT DATEADD(day, 5, Date ) AS NewTime;

  • DATEPART()

  • used when we need a part of date or time from a datetime variable. We can use DATEPART()only with select command.
    SELECT DATEPART(month, GETDATE()) AS 'Month';

  • DATEDIFF()

  • is very common function to find out the difference between two DateTime elements.
    SELECT DATEDIFF(datepart, startdate, enddate) :

    SELECT DATEDIFF(day, Date1, Date2) AS DifferenceOfDay

  • DATENAME()

  • most useful function to find out the date name from the datetime value.
 SELECT DATENAME(month, getdate()) AS 'Month';

  • DAY()

  • to get the day from any date time object.
      SELECT DAY(getdate()) AS 'DAY'​;

  • MONTH()

  • to get the month from any date time object.
    SELECT MONTH(getdate()) AS 'MONTH';

  • YEAR()

  • to get the year from any date time object.
      SELECT YEAR(getdate()) AS 'YEAR';

​How to apply search query logic in Hibernate using ‘like’ irrespective of case-sensitiveness ?

​​String query = "Select* from model_project p where ((p.name like '"
+ name.toLowerCase() + "%') or (p.name like '"
+ name.toUpperCase() + "%')) and ((p.title like '"
+ title.toLowerCase() + "%') or (p.title like '"
+ title.toUpperCase() + "%')) and ((p.vertical like '"
+ vertical.toLowerCase() + "%') or (p.vertical like '"
+ vertical.toUpperCase() + "%')) " ;
projects = getHibernateSession().createSQLQuery(query).addEntity(Project.class).list();

  OR

Criteria searchCriteria = getHibernateSession().createCriteria(Project.class);
searchCriteria
.add(Restrictions.or(
Restrictions.like("name", name.toLowerCase()+"%"),
Restrictions.like("name", name.toUpperCase()+"%"))
)
.add(Restrictions.or(
Restrictions.like("title", title.toLowerCase()+"%"),
Restrictions.like("title", title.toUpperCase()+"%"))
)
.add(Restrictions.or(
Restrictions.like("vertical",vertical.toLowerCase()+"%"),
Restrictions.like("vertical",vertical.toUpperCase()+"%"))
);
projects = searchCriteria.list();

​Suppose a date is given. What Database Logics & Queries will you apply to find the day of week for the given date ?

​Suppose a date 2014-01-15 is given. We are interested in getting date of Friday in this week. we can do this by :

SELECT DATEADD(wk, DATEDIFF(wk,0,convert(datetime,'2014-01-15', 121)), 4);

​above query will result in 2014-01-17 00:00:00.000

Here in DATEADD function’s last parameter 4 represents day of week.
values starts from 0 to 6 for Monday to Sunday respectively.

 

How to install Mongo DB in your System ?


Top