Java: How to insert CLOB into oracle database

问题:

I need to write an XML file content into oracle database where the column is of CLOB datatype. How will I do that?

回答1:

With about 100 lines of code ;-) Here is an example.

The main point: Unlike with other JDBC drivers, the one from Oracle doesn't support using Reader and InputStream as parameters of an INSERT. Instead, you must SELECT the CLOB column FOR UPDATE and then write into the ResultSet

I suggest that you move this code into a helper method/class. Otherwise, it will pollute the rest of your code.

回答2:

The easiest way is to simply use the

stmt.setString(position, xml);

methods (for "small" strings which can be easily kept in Java memory), or

try {
  java.sql.Clob clob = 
    oracle.sql.CLOB.createTemporary(
      connection, false, oracle.sql.CLOB.DURATION_SESSION);

  clob.setString(1, xml);
  stmt.setClob(position, clob);
  stmt.execute();
}

// Important!
finally {
  clob.free();
}

回答3:

passing the xml content as string.

table1 

ID   int
XML  CLOB



import oracle.jdbc.OraclePreparedStatement;
/*
Your Code
*/
 void insert(int id, String xml){
    try {
        String sql = "INSERT INTO table1(ID,XML) VALUES ("
                + id
                + "', ? )";
        PreparedStatement ps = conn.prepareStatement(sql);
        ((OraclePreparedStatement) ps).setStringForClob(1, xml);
        ps.execute();
        result = true;
        } catch (Exception e) {
        e.printStackTrace();
    }
  }

回答4:

This code worked for me. I use ojdbc6-11.2.0.2.jar.

java.sql.Connection con;
javax.xml.bind.Marshaller marshaller;

Clob xmlClob = con.createClob();
try {
  try (Writer xmlClobWriter = xmlClob.setCharacterStream(1)) {
    m.marshal(jaxbObject, xmlClobWriter);
  } // xmlClobWriter.close();
  try (PreparedStatement stmt = con.prepareStatement("INSERT INTO table (xml) values(?)")) {
    stmt.setClob(1, xmlClob);
    stmt.executeUpdate();
  }
} finally {
  xmlClob.free();
}

回答5:

For this purpose you need to make the connection result set

ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE

Connection con=null;
//initialize connection variable to connect to your database...
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String query="Select MYCLOB from TABLE_NAME for update";
con.setAutoCommit(false);
ResultSet resultset=stmt.executeQuery(query);


if(resultset.next()){
oracle.sql.CLOB    clobnew = ((OracleResultSet) rss).getCLOB("MYCLOB");
PrintWriter pw = new PrintWriter(clobnew.getCharacterOutputStream() );
BufferedReader br = new BufferedReader( new FileReader( new File("filename.xml") ) );
String  lineIn = null;
while( ( lineIn = br.readLine() ) != null )
      pw.println( lineIn );
      pw.close();
      br.close();
}

con.setAutoCommit(true);
con.commit();
}

Note: its important that you add the phrase for update at the end of the query that is written to select the row...

Follow the above code to insert the XML file

回答6:

You can very well do it with below code, i am giving you just the code to insert xml hope u are done with rest of other things..

import oracle.xdb.XMLType;

//now inside the class......
// this will be to convert xml into string

File file = new File(your file path);
FileReader fileR = new FileReader(file);
fileR.read(data);
String str = new String(data);

// now to enter it into db

conn = DriverManager.getConnection(serverName, userId, password);

XMLType objXml = XMLType.createXML(conn, str);

// inside the query statement put this code

objPreparedstatmnt.setObject(your value index, objXml);

I have done like this and it is working fine.

回答7:

Converting clob to string:

Clob clob=rs.getClob(2);      
String str=(String)clob.getSubString(1,(int)clob.length());      
System.out.println("Clob Data is : "+str);

回答8:

Try this , there is no need to set its a CLOB

  public static void main(String[] args)
        {
        try{    

                    System.out.println("Opening db");

                    Class.forName("oracle.jdbc.driver.OracleDriver"); 
                    if(con==null)
                     con=DriverManager.getConnection("jdbc:oracle:thin:@192.9.200.103:1521: orcl","sas","sas");  
                    if(stmt==null)
                    stmt=con.createStatement();  


                    int res=9;

                    String usersSql = "{call Esme_Insertsmscdata(?,?,?,?,?)}";
                    CallableStatement stmt = con.prepareCall(usersSql);
            // THIS THE CLOB DATA  
            stmt.setString(1,"SS¶5268771¶00058711¶04192018¶SS¶5268771¶00058712¶04192018¶SS¶5268772¶00058713¶04192018¶SS¶5268772¶00058714¶04192018¶SS¶5268773¶00058715¶04192018¶SS¶5268773¶00058716¶04192018¶SS¶5268774¶00058717¶04192018¶SS¶5268774¶00058718¶04192018¶SS¶5268775¶00058719¶04192018¶SS¶5268775¶00058720¶04192018¶");     
                    stmt.setString(2, "bcvbcvb");
                    stmt.setString(3, String.valueOf("4522"));
                    stmt.setString(4, "42.25.632.25");
                    stmt.registerOutParameter(5,OracleTypes.NUMBER);    
                    stmt.execute();
                    res=stmt.getInt(5);
                    stmt.close();

                    System.out.println(res);




                    }
                    catch(Exception e)
                    { 

                         try 
                         {
                            con.close();
                        } catch (SQLException e1) {


                        }
                    }  
                }
    }

回答9:

I had similar issue. Changed one of my table column from varchar2 to CLOB. I didn't needed to change any java code. I kept it as setString(..) only so no need to change set method as setClob() etch if you are using following versions ATLEAST of Oracle and jdbc driver.

I tried in In Oracle 11g and driver ojdbc6-11.2.0.4.jar

回答10:

Take a look at the LobBasicSample for an example to use CLOB, BLOB, NLOB datatypes.