Solved

java code to insert dynamic data to oracle database

Posted on 2007-04-09
16
630 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
  • 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 500 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video teaches viewers about errors in exception handling.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now