using sql like in java

I make a procedure that get a nvarchar and it return a table by using like and it works well. but when I want to use this procedure in java it doesn't work. here is the java code.

String query = "exec Predict ?"; pst = conn.prepareStatement(query); pst.setString(1, "%"+"ee"+"%"); rs = pst.executeQuery(); int i = 0; while (rs.next()) { System.out.println(rs.getString(1)); i++; }

--------------Solutions-------------

Hey you actually can't just call it as a query. You got to make it a callable statement and execute it. This is the code I have been using. It takes in parameters too, but if you dont need that, just remove it. This is sort of customized for my project setup, but this will help you out:

/**
* This method takes in a stored procedure along with its parameters and parameter type and creates a result set from them.
* @param storedProcedure
* @param parameters
* @param parameterType
* @return
* @throws SQLException
*/
public static ResultSet createRSFromStoredProc(String storedProcedure, ArrayList<String> parameters, ArrayList<String> parameterType) throws SQLException {

// Initializes variables that are used in the method.
Connection dbConnection = null;
CallableStatement callableStatement = null;
ResultSet rs = null;

// Builds the stored procedure calling
String insertStoreProc = "{call " + storedProcedure + "(";
for(int i=0;i<parameters.size(); i++)
{
if(i==parameters.size()-1)
insertStoreProc+="?";
else
insertStoreProc+="?,";
}
insertStoreProc+=")}";

try {
// establishes the connection
dbConnection = getDBConnection();

// creates the callable statement
callableStatement = dbConnection.prepareCall(insertStoreProc);

// Parses the string into the correct parameter type and sets the parameter to the value.
for(int i=0;i<parameters.size(); i++)
{
if(parameterType.get(i).equals("string"))
callableStatement.setString(i+1, parameters.get(i));
else if(parameterType.get(i).equals("int"))
callableStatement.setInt(i+1, Integer.parseInt(parameters.get(i)));
if(parameterType.get(i).equals("dateTime"))
{
// if now is entered for the time gets the current timestamp
if(parameters.get(i).equals("now"))
callableStatement.setTimestamp(i+1, Timestamp.valueOf(getCurrentDate()));
else
{
callableStatement.setString(i+1, parameters.get(i));
}

}
}

// execute insertDBUSER store procedure
Boolean results = callableStatement.execute();
int rowsAffected=0;
while(results||rowsAffected!=-1)
{
if(results)
{
rs=callableStatement.getResultSet();
break;
}
else
{
rowsAffected=callableStatement.getUpdateCount();
}
results= callableStatement.getMoreResults();
}

} catch (SQLException e) {

System.out.println(e.getMessage());

}
return rs;

}

Category:java Time:2018-11-05 Views:2
Tags: sql java

Related post

  • How use sql "like" in PyMongo? 2012-04-04

    How use sql "like" in PyMongo? >>> db.houses.find().count() 11616 >>> db.houses.find({"hid":u"16999"}).count() 1 >>> db.houses.find({"hid":u"/9/"}).count() 0 The documentation says that sql "like" (SELECT * FROM users WHERE

  • How to use SQL 'LIKE' with LINQ to Entities? 2010-06-22

    I have a textbox that allows a user to specify a search string, including wild cards, for example: Joh* *Johnson *mit* *ack*on Before using LINQ to Entities, I had a stored procedure which took that string as parameter and did: SELECT * FROM Table WH

  • Using SQL LIKE and IN together 2010-02-23

    Is there a way to use LIKE and IN together? I want to achieve something like this. SELECT * FROM tablename WHERE column IN ('M510%', 'M615%', 'M515%', 'M612%'); So basically I want to be able to match the column with a bunch of different strings. Is

  • Objective-C. Can you use protocol like a Java interface? 2009-06-18

    Are these basically the same thing? For example if I have an interface in Java public interface CoolObject{ ... } I can use any object that implements the CoolObject interface in functions that take a CoolObject as a parameter: public void foo(CoolOb

  • delete rows using sql 'like' command using data from another table 2011-01-05

    I am trying to delete rows from a table ("lovalarm") where a field ("pointid") is like any one of a number of strings. Currently I am entering them all manually however I need to be able to have a list of over 100,000 options. My thoughts are to have

  • Autocomplete uses sql "Like" query, but returns every result 2012-03-12

    My code : var db = Database.Open("dbase"); var term = Request.Form["username"] + "%"; var sql = "SELECT * from Users where Username LIKE @0"; var result = db.Query(sql, term); var data = result.Select(p => new{label = p.username}); Json.Write(data

  • Can you use sql like() functions for table joins? 2011-02-28

    Assume I have two tables, each with a single string column (name). I'd like to do a left outer join from t1 to t2 where t1.name like %t2.name% or t2.name like %t1.name% is something like that possible? --------------Solutions------------- Pretty much

  • Searching for newlines in a Oracle DB using SQL 'like' clause 2010-10-05

    I'm trying to see if a particular column in a table in my Oracle database has any strings which contain a newline character, so a wildcard, a newline, and another wildcard. I've tried the like command and a combination with CHR(10) and tried escaping

  • How to a csv file in oracle using sql loader in java 2014-09-09

    I want to load data from a csv file to oracle database. Here is my code- void importData(Connection conn) { Statement stmt; String query; String filename = "C:/CSVData/Student.csv"; try { stmt = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE,

  • How do you query object collections in Java (Criteria/SQL-like)? 2008-09-18

    Suppose you have a collection of a few hundred in-memory objects and you need to query this List to return objects matching some SQL or Criteria like query. For example, you might have a List of Car objects and you want to return all cars made during

  • implement SQL like select in Java 2011-11-23

    In general I want to know how SQL select is implemented in the lower level, it looks like the algorithm they using is close to O(1)..... in Java you can only achieve this by using hashmaps, I wander how they did it EXAMPLE: If I have a group of stude

  • Display cell value as column name using SQL 2011-12-17

    I have got table as given below in database: Name Grade Subject Ami HD Java Ami D C++ Bec D Java Bec P C++ Is it possible to display it in format given below only using SQL: Name Java C++ Ami HD D Bec D P I have tried hard, but couldn't find a soluti

  • Using NoSQL on a web application, and not using SQL 2012-04-18

    What are the pros and cons of using a NoSQL database (like MongoDb, etc) entirely for a web application which is intended to be a social networking site. I mean, for the User accounts and other credentials to be stored in a NoSQL database, instead of

  • Can I connect to SQL Server using Windows Authentication from Java EE webapp? 2008-10-03

    I am currently investigating how to make a connection to a SQL Server database from my Java EE web application using Windows Authentication instead of SQL Server authentication. I am running this app off of Tomcat 6.0, and am utilizing the Microsoft

  • How to implement a SQL like 'LIKE' operator in java? 2009-05-22

    I need a comparator in java which has the same semantics as the sql 'like' operator. For example: myComparator.like("digital","%ital%"); myComparator.like("digital","%gi?a%"); myComparator.like("digital","digi%"); should evaluate to true, and myCompa

  • How can we run .sql file from java without using JDBC? 2010-04-23

    I want to run a .sql file from java without using jdbc on windows 7. I am trying with java runtime class which executes the system commands. But my java is running with only user privileges and the command needs Administrator privileges. The command

  • What is the Java RegEx equivalent to the SQL LIKE clause of "%A%B%"? 2010-11-19

    What is the Java RegEx equivalent to the SQL LIKE clause of "%A%B%"? Pretty basic question, I'm just learning the Java Regex flavor. --------------Solutions------------- I think this is it pattern wise: .*A.*B.* I'll edit and add more for specific ja

  • Use variable declared in Java within SQL query 2010-11-26

    k so I have created a database called dictionaryDatabase using Apache Derby in Java. The database has a table called dictionaryTable and contains two columns, wrongWord and rightWord. Here's what I have so far: String keyWord = "test"; getWords = con

  • Why use JPA instead of directly writing SQL query on Java File (i.e. directly to JDBC)? 2010-12-10

    I've been reading up on several articles what is JPA (Java Persistent API) and which vendor supporting it (DataNucleus, JBoss Hibernate etc) I don't have experience with ORM (object relational mapping). What I have done so far is to write my own Data

  • Why cant I execute the bulk of .sql file without using exit at the end of sql file through java 2012-02-24

    I am trying to execute .sql file from java. It runs successfully when I put exit at the end of the .sql file. Is there any possibility to run without giving exit in .sql? Java code import java.io.BufferedReader; import java.io.File; import java.io.Fi

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.950 (s). 14 q(s)