JDBC :: Oracle IN Clause With Collection Of Elements
Jan 26, 2012
I am using a IN clause in Oracle DB to pass a collection of custId to retrieve the customer details. If it was 10 or 50 custId's as a collection in IN clause it works fine. But if the collection grows bigger to 500 or 1000 then it is pretty slow to load the JSP page with the customer details.
Here is the query:-
select CustName, CustAge, CustCity, CustPin from CUSTOMER where custId IN (....)
The list of custId that is passed through Hibernate query.setParameterList()
How to optimize this query to make sure it displays the customer details faster even if the collection of elements which we pass is huge?
I have installed and configured Oracle Business Intelligence Applications on top of one demo eBS instance as a source. Now I would like to change the OLTP to a different eBS instance but am not sure which steps to take. I don't know if I can add new EBS connection pool and a new DataWarehouse Connection Pool in the administration and have old ones preserved or I need to overwrite the old ones? Or (ideally) can I just change the OLTP and overwrite the old DWH?
Any document supporting the process of changing the eBS OLTP database without new installation of the OBIAPPS?
We have a java application in our production enviroment using Spring framework which is configured with DBCP connection pool and the backend is Oracle database( version 11.2.0.3). Recently we encountered an occasionally happened situation(about once every week) and can be described as below:
The application schedules a task that runs every 10 minutes. And during task execution, the application would issue a SQL query of which the result is expected to be got within 10 seconds. At 11:51 a.m Oct 13th, however, the application failed to get the result within expected time, and 2 hours later, at 13:51, the result was finally returned to application. Due to lack of information at that time, we were not able to reproduce the same problem in test environment. At 15:31 p.m, Oct 29th, it happend again and this time we grasped all the information including thread dump and Oracle diagnostic information. We could found that:
1. Through oracle v$sql view, we could find that the SQL query is executed twice, at 15:31 and 17:31 respectively.
2. By analyzing TCP packets provided by network monitoring tools, it can be concluded that the request TCP packet containing SQL statement had been sent to Oracle and get executed, but after that JDBC only fetched first 80000 records out of 90000 records in total and then it stopped, didn't send any more request to Oracle to fetch rows. 2 hours later, Oracle sent a TCP keep alive packet and JDBC driver resume fetching remaining rows using the same connection(which can be confirmed by comparing source port of packets).
3. We dumped the thread at which JDBC hangs at socket read of JDBC driver
By the way, the version of JDBC we use is 11.2.0.1 and JDK version is 1.5.0_22.
I am trying to connect to Sql Server database using Oracle UCP with sqljdbc4-3.0 JDBC driver for Sql Server,with different ports and instances.
– the issue is with the port being ignored in the server string.
For example, using port 1440 connects to the default instance (which is on port 1433) rather than MSSQLINSTANCE1 which is on 1440.
Below are Server hosts used.
sql005.sqlasoftware.com (connects correctly to the default instance) sql005.sqlasoftware.com:1440 (connects incorrectly to the default instance on port 1433) sql005.sqlasoftware.comMSSQLINSTANCE1 (connects correctly to the named instance)
I have small Java code, which execute every day and checks for data in database using Cronj Schedular and everything works fine, but recently I have observed that, it is failing due to
java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@160.110.xx.xxx:1521/test
At the same time, when I run my test code to check Database connectivity that works fine without above exception. I'm unable to figure it out. Although, there was just slight code change, but that was nowhere related to Database or Database connection.
dbconf.java public class dbconf { private Connection connect; private String connstr; public Connection getConnection() throws SQLException { connstr = "jdbc:oracle:thin:@160.110.xx.xxx:1521/test";
[Code] ....
Application Log file
Wed Jul 01 09:25:17 IST 2015:------- Initializing ------------------- Wed Jul 01 09:25:17 IST 2015:------- Scheduling Jobs ---------------- Wed Jul 01 09:25:17 IST 2015:------- Job Started Running ---------------- Thu Jul 02 06:00:00 IST 2015 : Job Executed..!! Bschedularv2.2 java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@160.xxx.67.xxx:1521/test Sat Jul 04 06:00:00 IST 2015 : Job Executed..!! Bschedularv2.2 Sun Jul 05 06:00:00 IST 2015 : Job Executed..!! Bschedularv2.2 java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@160.xxx.67.xxx:1521/test
So, you can see, It failed on 3rd of July and 5th July as well. But, in between it ran fine.
I run Java EE application on Glassfish server v3 together with Oracle 12 DB on the same machine under Windows Server 2012 64bit. I use latest ojdbc7 driver.
After 2 or 3 hours, when there is more than 1 user (3-5) using my application, it stops responding and I get this in glassfish logs
javax.enterprise.resource.resourceadapter.com.sun.enterprise.resource.allocator|_ThreadID=152;_ThreadName=Thread-2;|RAR5038:Unexpected exception while creating resource for pool dbPool. Exception : javax.resource.spi.ResourceAllocationException: Connection could not be allocated because: IO Error: Socket read timed out
[Code] ....
From the database side it looks like this
Fatal NI connect error 12560, connecting to: (LOCAL=NO) VERSION INFORMATION: TNS for 64-bit Windows: Version 12.1.0.1.0 - Production Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 12.1.0.1.0 - Production
[Code] ....
When I just reset db listener everything works ok for next 1-2 hours (depends on application load). So temporary solution is to run bat script from windows scheduler to reset the listener every 1h. I tried everything I could find - applied these parameters:
I need to create an algorithm that finds the common element(s) in all arrays that has a signature of public Comparable[] findCommonElements(Object[] collection) that has an efficiency of at most O(knlogn), uses a query array, and accepts as input a collection of arrays. I am aware my time would be better spent learning how to use array lists and hash sets, but I am supposed to use concepts already covered, and these have not been.
I feel like this code should work, but it is returning null for the array of common elements. Which means it obviously is not working correctly. I am also likely going to need implementing the sort algorithm, but I wanted to get the part of finding the common elements set first.
public class CommonElements2<T extends Comparable<T>> { Comparable[] tempArr; Comparable[] common; Comparable[] queryArray; /* sort algorithm goes here */ public Comparable[] findCommonElements(Object[] collections)
calling a parameterized stored procedure in java jdbc from sql server.The stored procedure goes like this in sql
create proc patientreg @id int as begin select [patient_id],[Psurname], [pFirstname], [pMiddlename], [reg_date], [DOB], [Sex], [Phone_num], [Addr],[Email],[dbo].[fncomputeage](DOB) from [dbo].[Patient_registration] where [patient_id] = @id end please note dbo.fncompute(DOB) is a function
I have a mySql table of PROJECTS, which I am displaying as a list in the index.xhtml. The projectid column contains hyperlinks. When they're clicked I would like the specific projectid row selected to be passed as the query argument into another jsf file (ListProjects.xhtml) which displays all the project values referring to the projectid selected in the index.xhtml. The named query is:
@NamedQuery(name = "Projects.findByProjectid", query = "SELECT p FROM Projects p WHERE p.projectid = :projectid") index.xhtml <?xml version='1.0' encoding='UTF-8' ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"
[Code] ....
I get the following stack trace. How to correctly pass the hyperlink parameters.
org.jboss.weld.exceptions.WeldException: WELD-000049 Unable to invoke public void com.manaar.beans.SelProjectMgtBean.init() on com.manaar.beans.SelProjectMgtBean@681859ae at org.jboss.weld.injection.producer.DefaultLifecycleCallbackInvoker.invokeMethods(DefaultLifecycleCallbackInvoker.java:91)
what is the use of checked exception.I know unchecked exception or Runtime exception are thrown by jvm whenever programmer makes any mistake in logic and current thread is terminated.But checked Exception are checked at compile time so that compiler compels programmer to put risky methods in try catch clause. And this checked Exception are caused due to problem in IO operation or any such operation which the programmer can't control.Programmer can't do anything to avoid this checked exception but can catch this exception.
Now the question is Why compiler compels checked exception to be put in try catch clause but doesn't complain anything in case of Runtime Exception???
import java.util.ArrayList; import java.util.Arrays; import java.util.List; public class array { public static void main(String[] args)
[Code] ...
Is there a way to write this, where, alpha is one array.
Write a program that declares an array "alpha" of 50 elements of type "double". Initialize the array so that the first 25 elements are equal to the square of the index variable and the last 25 elements are equal to three times the index variable. Output the array so that 10 elements per line are printed.
If I have an array of 50 integers, can I break that to read in lines of 10?
I am trying to make a 2d array that keeps track of comparison counts. what I have so far works ok but writes over the previous elements with 0. can't seem to find where I am re-initializing the previous elements.
//this is one of my fill sort arrays
public void fillSelectionArray(int index, long countSum) { //rand = new Random( ); //for ( int i = 0; i < listsize; i++) { selectionList[ index -1] = countSum; // }
I was giving a quick skim to some tutorials on the internet where I have found the exception that is handled is also declared in the throws clause of the method wrapping the try-catch block. For a code representation consider the following:
public void methodName() throws IOException { try { ... } catch (IOException ex) { .... TODO handle exception }
Where do I have to put the ojdbc6.jar file so that Java finally recognizes it?I'm trying to connect to a Oracle XE databse from a Java application, but
Class.forName("oracle.jdbc.OracleDriver");
Will throw a ClassNotFoundException no matter where I put the driver. Stuff like this should be extremely simple but I am about to give up for good now.
I need a java program for getting name and address matching from oracle database. I need get all the customer name and address belonging to same person even if there is slight name and address changes are there.
James Smith 123 Broadway Av Brownville, ME 04415
James S 123 Brodway Avenue Brownville Junction, ME 04451
I am unable to get a best code for this and trying since long time.
I have small project to be implemented in Java, which, expected the management of a parking.
The project has a class abstract Vehicle, whence derive three classes: Car, Motorcycle, Heavy Vehicles; the cost estimated time for the 3 types of vehicles are: 2€ for Cars, 1€ for Motorcycle and 5€ for H.V.
In addition, in class Ticket will be stored the arrival time of the customer and the characteristics of his vehicle.
Finally, in the class Parking(which provides 80 places available), here it should be added the various types of vehicles.
Now, I though of using an Collection, as Set.. So that they can not, two Vheicle with same license plate.
I used the PLPDF software to generate a blob in PDF format in PL/SQL. I can open it in a browser but I would rather send it directly to a printer from PL/SQL.
I know I can use tools like BI and Jasper but I want this particular solution to be 100% database solution.
I was wondering of I can use JAVA in Oracle to send the blob to a printer.
I am using mysql database and I have downloaded the library also I have managed to get the database connection and query working. I need to know if I need to create separate classes to add/remove/edit items and view items? Do I need to put my database connection script in every class that I create or should I create methods for both the connection and the queries that will be called in the additional classes or methods that I have?
Below is what I have written so far and it is working, I will change the database and query soon to reflect the task I need to do because I have followed a tutorial.
Java Code:
/** * cdCollection.java */ package org.com.mm00422_prototype; //Import for the SQL package import java.sql.*;
//Registering the JDBC driver //Class.forName("com.mysql.jdbc.Driver");
I have a method that accepts JSONArray as parameter and returns the values of it as ArrayList Object. My question which of these ways is appropriate in populating the ArrayList object this method populates the arraylist upon creation of object (I don't know what the right term to use, but as netbeans IDE suggest, JSONArray object should be final since it was used in inner class.).
private List<String> getStringList(final JSONArray jsonArr) { return new ArrayList<String>() { { try { for (int i = 0; i < jsonArr.length(); i++) { add(jsonArr.getString(i)); } } catch (JSONException ex) { ex.printStackTrace(); } } }; }
this second method is the usual way of populating collection
private List<String> getStringList(JSONArray jsonArr) { List<String> strList = new ArrayList<String>(); try { for (int i = 0; i < jsonArr.length(); i++) { strList.add(jsonArr.getString(i)); } } catch (JSONException ex) { ex.printStackTrace(); } }
What are the advantages and disadvantages between the two? like which is faster? or which consumed larger memory?
My query retrieves more than 5000 records from Oracle database. I want to display the records 100 per page. I know it is called pagination. Any detailed styp-by-guide or tutorial or example available?
I've Installed oracle JDK version 1.8 on a debian machine that already has openjdk 1.6.i've set the path variable in etc/login.defs and java_home variable In etc/environment. When I echo $JAVA_HOME it points to the oracle version correctly and when I run javac It uses the oracle 1.8 version. The problem is when I run the java command , It runs the openjdk 1.6 version and I'm not sure why.If I type java -version It shows the openjdk version.
