With that update in the question, first thing that anybody is going to say is to remove the concatenation and use parameters in the query—it will protect you from SQL Injection.
Secondly, there are so many LIKE operators, why are you using searches on the table, and trying to match every column with the input. A quick tip would be, use a separate search for each column and then try to aggregate the overall results. This would have a little amount of WHERE clause, and the query would end quickly, yours is having multiple OR clauses, which is not letting SQL Server short-circuit the query either and is making it run on each of the records.
So, you can start by:
1. Add indexers to the columns as needed. You will know where to add them.
2. Create separate stored procedures to find the query results. Then try to aggregate the result of all queries.
3. Use caching to store the results of the most recent queries.
4. Also, try using full text search capabilities of SQL Server.
What else could cause this error beside the sequence not being created?
I have recreated the sequence using SQL code, also recreated it using Oracle SQL Developer "New Sequence" menu but still nothing. I only have a single class and and a single table, being a learning project, I don't want to complicate things. Project made using Maven.
<?xml version = "1.0" encoding = "utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN""http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<class name = "com.testing.classes.Person" table = "PERSON">
<id name = "id" type = "int" column = "ID">
<property name = "firstName" column = "FIRST_NAME" type = "string"/>
<property name = "lastName" column = "LAST_NAME" type = "string"/>
<property name = "age" column = "AGE" type = "int"/>
CREATE SEQUENCE CREATE_PERSON_ID INCREMENT BY 1 START WITH 1 MAXVALUE 9999 MINVALUE 1 CACHE 10;
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
Session session = sessionFactory.openSession();
Person person = new Person("Name", "Last name", 48);
session.save(person); //Eclipse says the error is here
Well, as you can see in my hibernate.cfg.xml, I'm using SYSTEM, so that isn't the problem unfortunately.
After some more research I found the problem. All the sites with tutorials google suggested were using either <generator class="native"> or <param name="sequence" />SEQUENCE_NAME
The problem is that hibernate devs changed <param name="sequence" /> to <param name="sequence_name" />. It is weird that you get sequence does not exist error, instead of something like invalid value for param or something to suggest that is the problem.
After you install Oracle 11g XE, it will automatically create some tables (from what I've read, some are used for "Application Express", and you shouldn't drop them), but having them listed in Tables view is really annoying.
Is there a way to filter them out? And why do you use $ in table name for?
For some reason, last time I searched on google it didn't showed me anything useful (on any of the first 4 pages), but this time, I got the answer I needed.
1. Use a different user then SYSTEM. I didn't though at this, after all I didn't wanted to complicate things on a learning project.
2. Filter each group using %. When I tried it before it didn't worked, but now after I started the DB again, the filter worked as it should have. Maybe there was an error connecting to the DB, don't know, it's good that now it is working.
When i execute the procedure what i get the output is like -
ALTER PROC [dbo].[PROC_EMPLOYEE_PAY_SLIP]
@MonthName varchar(50), @Year Float
SELECT ISNULL(HR_EmployeeMaster.em_EmplopyeeCode,'') As EmplopyeeCode,
ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'A' THEN fa_ComponentMaster.Cm_Name ELSE '' END,'') As Additions,
ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'D' THEN fa_ComponentMaster.Cm_Name ELSE '' END,'') As Deductions,
ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'A' THEN Fa_MonthSalary.Ms_Amount ELSE 0 END,0) As AdditionsAmount,
ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'D' THEN Fa_MonthSalary.Ms_Amount ELSE 0 END,0) As DeductionsAmount
LEFT JOIN Fa_MonthSalary ON Fa_MonthSalary.ms_EmpCode = @EmplopyeeCode
LEFT JOIN Fa_MonthDetails ON Fa_MonthDetails.mo_id = Fa_MonthSalary.Ms_MonCode
LEFT JOIN fa_ComponentMaster ON fa_ComponentMaster.cm_code = Fa_MonthSalary.Ms_CompCode
WHERE HR_EmployeeMaster.em_EmplopyeeCode = @EmplopyeeCode AND
(SELECT CONVERT(CHAR(3), Fa_MonthDetails.mo_name)) = @MonthName AND
Fa_MonthDetails.mon_year = @Year