Solved

wrong query string getting picked up java spring

Posted on 2014-01-22
6
446 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

911 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