Can both 'LIKE' and '=' be used together in a single SQL query?

I am currently in the process of developing a Filter function that is responsible for sorting a list. Utilizing ajax, I aim to retrieve and exhibit the outcomes.

https://i.sstatic.net/Zf62m.png

However, my struggle lies in finding a solution to return results when both the search input and an option have been designated.

Every time I attempt to formulate my query as follows:

@Query(value = "SELECT * FROM colaboradores where name LIKE :search% AND department = ? AND category = ? ORDER BY id DESC", nativeQuery = true)
List <Colaboradores> filterColaboradorBySearch(@Param("search")String search, int department, int category);

ERROR:

Caused by: org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List com.dexa.hr.repository.ColaboradorRepository.filterColaboradoresAll(java.lang.String,int,int)! Reason: Mixing of ? parameters and other forms like ?1 is not supported!; nested exception is java.lang.IllegalArgumentException: Mixing of ? parameters and other forms like ?1 is not supported!
...

Seeking clarification on why this occurs.

javascriptmysqlsqlajax

Answer №1

The error you are experiencing is not caused by mixing the use of = and LIKE.

This issue arises because one value (search) is being passed using @Param, while the other 2 attributes are being used with question marks as the placeholders.

To resolve this, you can modify the query to:

@Query(value = "SELECT * FROM colaboradores where name LIKE :search% AND department = :department AND category = :category ORDER BY id DESC", nativeQuery = true)
List<Colaboradores> filterColaboradorBySearch(@Param("search") String search, @Param("department") int department, @Param("category") int category);

Alternatively, you can also adjust it to:

@Query(value = "SELECT * FROM colaboradores where name LIKE ?1% AND department = ?2 AND category = ?3 ORDER BY id DESC", nativeQuery = true)
List<Colaboradores> filterColaboradorBySearch(String search, int department, int category);

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Encountered a problem in node js when retrieving data from a mysql database

Authenticating Routes: exports.authenticate = function(req, res) { //connection.connect(); var sql="SELECT * from users where username='"+req.body.user+"' and password='"+req.body.pass+"' LIMIT 1"; connection.query(sql, fu ...

mysqlnode.jsexpresspug

Every initial test with Protractor ends in failure

Here are the versions I am currently using: protractor v5.1.2 chromedriver v2.33 node v6.11.4 npm 3.10.10 selenium-webdriver v3.0.1 I am a beginner with protractor and I am attempting to run the provided test natively included in protractor. The test scr ...

javascriptselenium-webdriverprotractor

Oops! There seems to be a problem with inserting an image using

I am trying to insert multiple images with a loop when clicked. The images are named "0.jpg","1.jpg","2.jpg" etc. To achieve this, I am using an array with all the elements: {name: '1', result: 1, prefecture: "city", photo1: "0.jpg"}, and my l ...

javascriptjqueryloopsnan

Stretching the Mantine Accordion Section

The Mantine accordion requires that its content be of type Accordion.Item, as indicated in the documentation for the children props. This means that even functions returning AccordionItem will not be recognized. Therefore, only AccordionItem(s) created in ...

javascriptreactjstypescriptmantine

Angular 8 combined with Mmenu light JS

Looking for guidance on integrating the Mmenu light JS plugin into an Angular 8 project. Wondering where to incorporate the 'mmenu-light.js' code. Any insights or advice would be greatly appreciated. Thank you! ...

javascriptangulartypescript

Leverage variables from different Vue components and link them together

I'm currently working on two different sections of the website First: Is it acceptable to use one instance within another, particularly with v-model? Are there any drawbacks to this approach? Second: Is there a way to reference each other, such as p ...

javascriptvue.jsvuejs2

SQL disaster caused by excessive semicolons and apostrophe abuses

It's a common occurrence for me to have numerous tabs open simultaneously. Often I find myself with SQL panes open after directly accessing tables. I frequently experience crashes in my SQL instance due to my fast typing causing me to mistakenly use a ...

sqlsql-servercrashssms

The hidden absolute positioned div disappears once the sticky navbar becomes fixed on the page

Whenever my navbar reaches the top of the screen, the links in the dropdown menu disappear. I followed tutorials and examples from w3schools to build my webpage. Specifically: howto: js_navbar_sticky howto: css_dropdown_navbar This code snippet exempli ...

javascripthtmlcssmenusticky

Using jQuery to Populate a Table with JSON Data

Despite my extensive search and analysis of similar questions on this forum, I have reached a roadblock. My script is failing to load data from a Json file into the table I am trying to create, even after closely following the jquery API guidelines. Any as ...

jqueryjsonajax

Changing Float Attributes to String in Google Earth Engine

I am trying to export data from Google Earth Engine to my Google Drive. To name the file, I am using information from its data properties which results in 2019.0_1.0. However, I would like the file name to be in a different format - '2019_1'. Bel ...

javascriptgoogle-earth-engine

Removing Embedded Json Element from a Collection in AngularJS HTML

In my JSON Collection, I want to display the Email ID that is marked as IsPreffered = TRUE using AngularJS HTML without using JavaScript. This is my JSON Collection: { "user" : [ { "Name" : "B. Balamanigandan", "Email": [ ...

javascripthtmlangularjsjsonangular-filters

I want to hide jqvmap when viewing on mobile devices

I'm currently working on my website at . I have a template that I'm using as a guide, but I want to make the map disappear on mobile view and replace it with a dropdown list. Can anyone suggest what code I should use for this? Appreciate any hel ...

javascripthtmlcssresponsive-designjqvmap

Having trouble serving static files with express.Router?

With my file system becoming more complex, I decided to switch from using app.use(express.static()) to utilizing express.Router(). Initially, I thought I could just replace app.use(express.static()) with router.use(express.static()), but unfortunately, thi ...

javascriptnode.jsexpress

What is the best way to implement toggleable divs using JavaScript's innerHTML method?

I am attempting to fetch data from an XML file and generate a name that, upon being clicked, will reveal more information. The additional details will be contained within a div element that remains hidden until the header is clicked. This is the concept. ...

javascriptjqueryhtmlxmlgetelementbyid

Ways to retrieve a specific value in an array of objects

When working with the p5 javascript library, there is a convenient built-in function called {key} that captures the key you press. For instance, using text(${key},200,200) will display the key pressed at position 200, 200 on the canvas. If I were to stor ...

javascriptarraysobjectp5.js

Removing data from MySQL using a left join with another table

online order table +------+-----------+----------+----------+ | id | item | quantity | status | +------+-----------+----------+----------+ | 1 | pizza | 2 | pending | | 2 | burger | 1 | shipped | | 3 | salad | ...

mysql

Utilizing a particular Google font site-wide in a Next.js project, restricted to only the default route

My goal is to implement the 'Roboto' font globally in my Next.js project. Below is my main layout file where I attempted to do so following the documentation provided. import type { Metadata } from "next"; import { Roboto } from "n ...

javascriptreactjsnext.jsfonts

Which criteria should be used to display the most recent results: ORDER BY id or date_created

I have multiple tables from which I want to retrieve results, prioritizing the most recent entries first. I am considering my options for the ORDER BY clause: date_created (type: INT, never changes value) id (type: INT, AUTO_INCREMENT) Both columns coul ...

mysqlsqldatabasesql-order-by

Retrieve a maximum of 5 entries for each 'post_type' within a database table

I need help with optimizing a query that retrieves posts from a DB table based on certain criteria. Currently, the query fetches all matching posts, but I want to limit it to only return up to 5 posts from each specific 'post_type'. At the moment ...

mysql

What is the best way to stop a web page from automatically scrolling to the top of the browser?

I am experiencing an issue on my webpage where, after scrolling down and selecting an item to view, pressing the browser back button causes the page to scroll back to the top instead of the previous location. The webpage is using JavaScript to render a l ...

javascriptphp