• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 675
  • Last Modified:

java code to insert dynamic data to oracle database

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
fm134
Asked:
fm134
  • 8
  • 8
1 Solution
 
JaaxCommented:
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
 
fm134Author Commented:
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
 
JaaxCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

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


0
 
fm134Author Commented:
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
 
JaaxCommented:
Did u add the import statement for the PreparedStatement  ?
- import java.sql.PreparedStatement;
0
 
fm134Author Commented:
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
 
JaaxCommented:
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
 
fm134Author Commented:
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
 
fm134Author Commented:
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
 
fm134Author Commented:
thanks jax there is no problem now:)
0
 
JaaxCommented:
Cool Man. Please award me some points ;)
0
 
fm134Author Commented:
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
 
JaaxCommented:
>>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
 
fm134Author Commented:
thanks alooot Jaax
0
 
JaaxCommented:
You're welcome !
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now