Sql query in jsp page

Get Started
1

hello all

Use case:
sql table contains column with name longText
How can i write a qeury that select a row that contains longText=“asdf”
longText might have a page of text but as long as string “asdf” appears
in that page the row should be selected.

Thanks.

2

Sounds more like a db question to me! :wink:

Anyway, what db are you using? In MySQL it would be something like

SELECT * from my_db WHERE longText LIKE ‘%asdf%’

you can also do a case insensitive version (again depending on the db):

SELECT * from my_db WHERE longText ILIKE ‘%asdf%’

In Postgres you could do a more pattern match version like this:

SELECT * from my_db WHERE longText *~ ‘asdf’ (think thats right - Ill have to look)

3

cool thanks

4

here is continuation… :slight_smile:

i have to write this query in jsp page using jstl

if I say: LIKE ‘%?%’
<sql:param value=“${param.d}”/> - parameter that is passed from html <form> tag page, i get compilation error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%‘asdf’%

I typed “asdf” string into input.

what is wrong this the syntax?

by the way what is Postgres?

thanks

5

Look at your error message … your tags are putting the ’ inside the % '%‘asdf’%, that’s wrong and why the db is complaining

Postgres is an open source object oriented db, as powerful as Oracle

6 

<sql:query dataSource="jdbc/yourDataSource" var="rs">
SELECT * FROM table WHERE textfield LIKE '%${param.d}%';
</sql:query>
7

Postgres > Mysql but as features go it is not as powerful as Oracle, not by a longshot. Postgres has four nice things going for it that give it an Oracle feel (1) sequences which are better than auto_increment (2) MVCC means writers don’t block readers when a writer does a SELECT FOR UPDATE (3) No messy table types that only support a specific feature like in MySQL where if you want transaction and referential integrity you have to use INNODB which doesn’t support full-text searching like MyISAM does. (4) proven long-time support of stored procs, triggers and views. This is only recent in MySQL5.

If you want DBMS_JOBS, data partitioning, materialized views, analytics, PLSQL packages(I really miss this in Postgres, it’s a great way to organize your stored procs and functions), BULK_COLLECT(miss this a lot too), rich and easy built-in full-text searching(Postgres requires tsearch or another 3rd party separate app which are not nearly as easy to use), built-in and very easy XML support(3rd party again in Postgres) then you need Oracle.

You can spend a lifetime learning all the features of Oracle.

8

You forgot #5 & #6… Postgres doesn’t cost freaking $10,000 AND Postgres isn’t slower than DEATH!

9

Thanks for the input :slight_smile:

There is a tiny problem with this query

<sql:query dataSource="jdbc/yourDataSource" var="rs">
SELECT * FROM table WHERE textfield LIKE '%${param.d}%';
</sql:query>

here is actually what I have


		<sql:query var="rs" dataSource="jdbc/TestDB">
			SELECT *
			FROM myTable
			WHERE
				OR id=?
				OR description LIKE '%${param.description}%'
			ORDER BY id DESC;
			<sql:param value="${param.postId}"/>
		</sql:query>

Here if the user does not specify description but only specifies id than it selects ALL rows instead of only one with specified id.

How can I restrict this? So if the user specifies id but does not specify description it selects only one row with specified id?

10

any help?

11

Get rid of the “OR” before your id=?. You have no clause there (therefore no selection criteria) so it’s choosing every record.

12

Oh, that was my typo.
without OR it still select everything if I do not specify description

13

so, can you suggest something?

I have done some changes to the query:


<sql:query var="rs" dataSource="jdbc/TestDB">
			SELECT id, title, d, cost, description, currentDate, isActive
			FROM myTable
			WHERE d=?
				OR id=?
				OR cost BETWEEN ? AND ?
				OR description LIKE '%${param.description}%'
			ORDER BY id DESC;
			<sql:param value="${param.d}"/>
			<sql:param value="${param.postId}"/>
			<sql:param value="${param.from}"/>
			<sql:param value="${param.to}"/>
		</sql:query>

so if I want to select ONLY by id and do no specify description it gets me all rows instead of the one with id that I specify.

Hmmmm?

14

try


SELECT id, title, d, cost, description, currentDate, isActive
			FROM myTable
			WHERE d=?
				OR id=?
				AND (cost BETWEEN ? AND ?
				OR description LIKE '%${param.description}%')
			ORDER BY id DESC;
15