Solved

wrong query string getting picked up java spring

Posted on 2014-01-22
6
444 Views
Last Modified: 2014-02-06
HI,
I have a following file :

package com.yatra.extremesearch.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import com.yatra.extremesearch.dao.ParamDao;
import com.yatra.extremesearch.model.ExtremeSearchParam;
import com.yatra.extremesearch.DBTemplate.DBTemplate;

@Repository
public class ParamDaoImpl implements ParamDao {
	
	@Autowired @Qualifier("yatraDB") DBTemplate yatraDB;
	
	@Override
	public List<ExtremeSearchParam> getReqParams() throws SQLException
	{
		List<ExtremeSearchParam> reqParams = new ArrayList<ExtremeSearchParam>();
		ResultSet rs = yatraDB.executeQuery("select * from extreme_search_param limit 0,5");
		while (rs.next()) {
			ExtremeSearchParam param = new ExtremeSearchParam();
			param.setOrigin(rs.getString("origin"));
			param.setDest(rs.getString("destination"));
			param.setMinDays(rs.getInt("min_dep_days"));
			param.setMaxDays(rs.getInt("max_dep_days"));
			param.setMinStay(rs.getInt("min_stay"));
			param.setMaxStay(rs.getInt("max_stay"));
			param.setSearchType(rs.getString("search_type"));
			param.setIsValid(rs.getString("is_valid"));
			param.setSplit(rs.getInt("split"));
		
			reqParams.add(param);
		}
				
		return reqParams;	
	}

}

Open in new window


The DBTemplate.java file is as follows :

package com.yatra.extremesearch.DBTemplate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

public class DBTemplate{
	private static final Logger logger = Logger.getLogger(DBTemplate.class);

	DataSource dataSource;
	
	public DBTemplate(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public ResultSet executeQuery (String sql) {

		ResultSet rs = null;
		Connection conn =null;
		try {
			conn = dataSource.getConnection();
			rs = conn.createStatement().executeQuery(sql);	
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
		
		return rs;
	
	}


	public void executeUpdate(String sql) {
		Connection conn =null;
		try {
			conn = dataSource.getConnection();
			conn.createStatement().executeUpdate(sql);
			
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
		finally{
			if(conn!=null)
				try {
					conn.close();
				} catch (SQLException e) {
					logger.error(e.getMessage());
				}
			}
		
	}
	
	
	public void loadInfile(String dumpFile, String table){
		Connection conn =null;
		PreparedStatement ptmt = null;
		String sql = "LOAD DATA LOCAL INFILE '"+ dumpFile.replace("\\", "\\\\") +
				"' REPLACE INTO TABLE "+ table
				+" FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n'"+
				" (origin, destination, price, dep_date, arrival_date, supplier, search_type)";
		try {
			conn = dataSource.getConnection();
			ptmt = conn.prepareStatement(sql);
			ptmt.executeQuery();			
			
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
		finally{
			if(conn!=null)
				try {
					ptmt.close();
					conn.close();
				} catch (SQLException e) {
					logger.error(e.getMessage());
				}
			}
	}
	
}

Open in new window



Even thought the query written in executeQuery is :
select * from extreme_search_param limit 0,5"

When i start the spring java application in debug mode and debug the
public ResultSet executeQuery (String sql) function

I see sql = select * from extreme_search_param
in debugger eclipse mode

What could be the reason for this ?

Thanks
0
Comment
Question by:Rohit Bajaj
  • 3
  • 2
6 Comments
 
LVL 6

Expert Comment

by:Mahesh Bhutkar
ID: 39799380
You are getting result as 5 records or even result is also more.

This should not be possible. But if you are getting correct result then there might be issue with eclipse.

Please check & update.
0
 

Author Comment

by:Rohit Bajaj
ID: 39799414
result is also more.
0
 

Author Comment

by:Rohit Bajaj
ID: 39799417
Does eclipse maintains an internal cache of some kind ?
Similar thing happened to me before also but i couldn't get the reason for it.
Restarting eclipse also didnt resolve the isse. But when i restarted my system things worked sanely...
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 6

Expert Comment

by:Mahesh Bhutkar
ID: 39799427
PFA image file. Tested on local machine. It works as expected. Myql Limit clause sql in Debug mode
Please restart your eclipse or system.
0
 
LVL 1

Accepted Solution

by:
Smart_Kid earned 350 total points
ID: 39802065
Make your application as a executable jar file, deploy and start. Check you are running into same erroneous situation. If not, we can confirm that the issue is with Eclipse. If yes, then there exist some issue with your code.

Kindly try.
0
 
LVL 6

Assisted Solution

by:Mahesh Bhutkar
Mahesh Bhutkar earned 150 total points
ID: 39802176
Is this problem resolved?

If not, then can you decompile DBTemplate.class file & check the sql query string,
"select * from extreme_search_param limit 0,5"
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now