Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

java code to insert dynamic data to oracle database

Posted on 2007-04-09
16
Medium Priority
?
665 Views
Last Modified: 2013-12-18
I am writing a dynamic java code that if it find certine data in my xml ducument it must compare the coulmn name in the data base with the data I found. if they match insert its data to the database

My code:

 public void characters(char ch[], int start, int length)
    {
        String data = String.valueOf(ch, start, length).trim();

        if(this.insideTag && this.prevData != null)
        {
              System.out.println("Data in next tag after founding '"+prevData+"': "+data);
//here I want to compare the coulmn names of my database with "prevData" if they match insert the "data" as data for this coulmn

            this.prevData = null;
           
        }

        for(int i=0;i<searchData.length;i++)
        {
            if(data.equalsIgnoreCase(searchData[i]))
            {
                this.prevData = data;
                break;
            }
        }
    }
0
Comment
Question by:fm134
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
16 Comments
 
LVL 10

Expert Comment

by:Jaax
ID: 18874965
Modify insertIntoDB as per your needs and have it called
private insertIntoDB(String val)
    // Database Connection object
    Connection conn = null;
 
    // Set database URL details.
    String url = "jdbc:oracle:thin:@localhost:1521:orcl";
    try {
      // Instantiate the driver class.
      Class.forName("oracle.jdbc.OracleDriver");
      // Get the database connection.
      conn = DriverManager.getConnection(url,"system","pass");
     
     
            PreparedStatement prep = conn.prepareStatement("insert into TBL("+val+")");
            prep.executeQuery();
     
    }catch (Exception e) {
          e.printStackTrace();
    }      
0
 

Author Comment

by:fm134
ID: 18875014
Hi jaxx,
thanks but I know how to connect to database now.
but the eproblem is  how to  

 System.out.println("Data in next tag after founding '"+prevData+"': "+data);
//here I want to compare the coulmn names of my database with "prevData" if they match insert the "data" as data for this coulmn

and u gave me:
  PreparedStatement prep = conn.prepareStatement("insert into TBL("+val+")");
            prep.executeQuery();
which did not compare the coulmn name with "prevData"

0
 
LVL 10

Expert Comment

by:Jaax
ID: 18875035
Hope this helps:

private insertIntoDB(String val)
    // Database Connection object
    Connection conn = null;
 
    // Set database URL details.
    String url = "jdbc:oracle:thin:@localhost:1521:orcl";
    try {
      // Instantiate the driver class.
      Class.forName("oracle.jdbc.OracleDriver");
      // Get the database connection.
      conn = DriverManager.getConnection(url,"system","pass");
     
      PreparedStatement prep = conn.prepareStatement("select yourcol from yourtable");
      ResultSet rs = prep.executeQuery();
      while(rs.next()){
            String dbCol = rs.getString("yourcol");
            if(!val.equals(dbCol)){
                    PreparedStatement prep = conn.prepareStatement("insert into yourtable(yourcol) values('"+val+"')");
                    prep.executeQuery();
                    break;
            }
      }
     
     
    }catch (Exception e) {
          e.printStackTrace();
    }      
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 10

Expert Comment

by:Jaax
ID: 18875044
Sorry,
PreparedStatement prep = conn.prepareStatement("insert into yourtable(yourcol) values('"+val+"')");
shud just read

prep = conn.prepareStatement("insert into yourtable(yourcol) values('"+val+"')");


0
 

Author Comment

by:fm134
ID: 18888200
thanks jaxx,
but i am facing some problems:
C:\classes>javac -classpath C:\oracle\product\10.1.0\db_1\jdbc\lib\classes12.jar
 com\psol\xbe2\MySAXApp2.java
com\psol\xbe2\MySAXApp2.java:63: cannot resolve symbol
symbol  : class PreparedStatement
location: class com.psol.xbe2.MySAXApp2
      PreparedStatement prep = conn.prepareStatement("select p from pro");
      ^
1 error


the code:
 private void insertIntoDB(String val)
    {
    // Database Connection object
    Connection conn = null;
 
    // Set database URL details.
    String url = "jdbc:oracle:thin:@localhost:1521:orcl";
    try {
      // Instantiate the driver class.
      Class.forName("oracle.jdbc.OracleDriver");
      // Get the database connection.
      conn = DriverManager.getConnection(url,"system","pass");
     
      PreparedStatement prep = conn.prepareStatement("select p from pro");
      ResultSet rs = prep.executeQuery();
      while(rs.next()){
            String dbCol = rs.getString("p");
            if(!val.equals(dbCol)){
                    prep = conn.prepareStatement("insert into yourtable(p) values('"+val+"')");
                    prep.executeQuery();
                    break;
            }
      }
         
    }catch (Exception e) {
          e.printStackTrace();
    }    
    }
0
 
LVL 10

Expert Comment

by:Jaax
ID: 18888317
Did u add the import statement for the PreparedStatement  ?
- import java.sql.PreparedStatement;
0
 

Author Comment

by:fm134
ID: 18888443
thanks jaax u solved it.
but I have two more questions:
1- C:\classes>javac -classpath . com\psol\xbe2\MySAXApp2.java
com\psol\xbe2\MySAXApp2.java:11: package oracle.jdbc does not exist
import oracle.jdbc.OraclePreparedStatement;
                   ^
1 error

I have this erroe even if put the C:\oracle\product\10.1.0\db_1\jdbc\lib\classes12.jar in my classpath???

2- how I call the code  private void insertIntoDB(String val)
from another function??
0
 
LVL 10

Expert Comment

by:Jaax
ID: 18888462
1. You need  not specify the vendor specific PreparedStatement. java.sql.PreparedStatement will do.
In this way, you would make ur code independent of DB Drivers. Class.forName will load the appropriate DB Driver. classes12.jar shud in the classpath as you have done.

2.Since the calling method is in the same class, you can. 'private' has been specified to disallow access of this method from other class
0
 

Author Comment

by:fm134
ID: 18888582
thanks jaax,
but can u please change the the coulmn name can be dynamic
I have to compare every coulmn name with the data I found  if they match or simematch then insert the data to that coulm.
      PreparedStatement prep = conn.prepareStatement("select(dynamic name) from pro");

0
 

Author Comment

by:fm134
ID: 18888840
hi agian
I tried the code and nothing inserted in the database!!
  private void insertIntoDB(String val)
    {
    // Database Connection object
    Connection conn = null;
 
    // Set database URL details.
    String url = "jdbc:oracle:thin:@localhost:1521:orcl";
    try {
      // Instantiate the driver class.
      Class.forName("oracle.jdbc.OracleDriver");
      // Get the database connection.
      conn = DriverManager.getConnection(url,"system","pass");
     
      PreparedStatement prep = conn.prepareStatement("select p from pro1");
      ResultSet rs = prep.executeQuery();
      while(rs.next()){
            String dbCol = rs.getString("p");
            if(!val.equals(dbCol)){
                    prep = conn.prepareStatement("insert into pro1(p) values('"+val+"')");
                    prep.executeQuery();
                    break;
            }
      }
         
    }catch (Exception e) {
          e.printStackTrace();
    }    
    }

 public void characters(char ch[], int start, int length)
    {
        String data = String.valueOf(ch, start, length).trim();
        insertIntoDB("xxx");
}
0
 

Author Comment

by:fm134
ID: 18888885
thanks jax there is no problem now:)
0
 
LVL 10

Expert Comment

by:Jaax
ID: 18889036
Cool Man. Please award me some points ;)
0
 

Author Comment

by:fm134
ID: 18896438
I just have one problem
can u please change the the coulmn name can be dynamic
I have to compare every coulmn name with the data I found  if they match or simematch then insert the data to that coulm.
      PreparedStatement prep = conn.prepareStatement("select(dynamic name) from pro");
0
 
LVL 10

Accepted Solution

by:
Jaax earned 2000 total points
ID: 18896762
>>PreparedStatement prep = conn.prepareStatement("select(dynamic name) from pro");
Change it to
PreparedStatement prep = conn.prepareStatement("select  * from pro");
ResultSet rs = prep.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
 int numberOfColumns = rsmd.getColumnCount();
while(rs.next()){
  String colValue = "";
  for(int i=0; i < numberOfColumns ; i++){
    colValue  = rs.getString(i);
    //Your logic to Compare
  }
}
0
 

Author Comment

by:fm134
ID: 18897196
thanks alooot Jaax
0
 
LVL 10

Expert Comment

by:Jaax
ID: 18904175
You're welcome !
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video teaches viewers about errors in exception handling.
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question