Solved

java code to insert dynamic data to oracle database

Posted on 2007-04-09
16
639 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Shredding xml into an oracle 11g Database 2 46
Why my table column Id is not passed to java object? 4 39
hibernate jars 4 32
Oracle DB monitor SW 21 50
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
The viewer will learn how to implement Singleton Design Pattern in Java.

810 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