Java/Database SQL JDBC/Stream Data

Demo Prepared Statement Set Ascii Stream

   <source lang="java">

import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class DemoPreparedStatementSetAsciiStream {

 public static Connection getConnection() throws Exception {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
   String username = "name";
   String password = "password";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static void main(String[] args) {
   Connection conn = null;
   PreparedStatement pstmt = null;
   String query = null;
   try {
     conn = getConnection();
     String fileName = "fileName.txt";
     File file = new File(fileName);
     int fileLength = (int) file.length();
     InputStream stream = (InputStream) new FileInputStream(file);
     query = "insert into  LONG_VARCHAR_TABLE(id, stream) values(?, ?)";
     pstmt = conn.prepareStatement(query);
     pstmt.setString(1, fileName);
     pstmt.setAsciiStream(2, stream, fileLength);
     int rowCount = pstmt.executeUpdate();
     System.out.println("rowCount=" + rowCount);
   } catch (Exception e) {
     e.printStackTrace();
   } finally {
     try {
       pstmt.close();
       conn.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
 }

}

      </source>
   
  
 
  

Demo Prepared Statement Set BinaryStream

   <source lang="java">

import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetBinaryStream {

 public static Connection getConnection() throws Exception {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
   String username = "name";
   String password = "password";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static void main(String[] args)throws Exception {
   String smallFileName = "smallFileName.dat";
   String largeFileName = "largeFileName.dat";
   Connection conn = null;
   PreparedStatement pstmt = null;
   try {
     conn = getConnection();
     File smallFile = new File(smallFileName);
     int smallFileLength = (int) smallFile.length();
     InputStream smallStream = (InputStream) new FileInputStream(smallFile);
     File largeFile = new File(largeFileName);
     int largeFileLength = (int) largeFile.length();
     InputStream largeStream = (InputStream) new FileInputStream(largeFile);
     String query = "insert into binary_table(id, raw_column, long_raw_column) values(?, ?, ?)";
     conn.setAutoCommit(false);
     pstmt = conn.prepareStatement(query);
     pstmt.setString(1, "0001");
     pstmt.setBinaryStream(2, smallStream, smallFileLength);
     pstmt.setBinaryStream(3, largeStream, largeFileLength);
     // execute query, and return number of rows created
     int rowCount = pstmt.executeUpdate();
     System.out.println("rowCount=" + rowCount);
     conn.rumit();
   } finally {
     pstmt.close();
     conn.close();
   }
 }

}

      </source>
   
  
 
  

Demo PreparedStatement Set Character Stream

   <source lang="java">

import java.io.BufferedReader; import java.io.File; import java.io.FileReader; import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DemoPreparedStatementSetCharacterStream {

 public static Connection getConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/databaseName";
   String username = "root";
   String password = "root";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static void main(String[] args)throws Exception {
   String fileName = "charDataFile.txt";
   Reader fileReader = null;
   long fileLength = 0;
   Connection conn = null;
   PreparedStatement pstmt = null;
   try {
     File file = new File(fileName);
     fileLength = file.length();
     fileReader = (Reader) new BufferedReader(new FileReader(file));
     System.out.println("fileName=" + fileName);
     System.out.println("fileLength=" + fileLength);
     conn = getConnection();
     // begin transaction
     conn.setAutoCommit(false);
     // prepare SQL query for inserting a new row using SetCharacterStream()
     String query = "insert into char_stream_table (id, char_stream_column) values(?, ?)";
     // create PrepareStatement object
     pstmt = conn.prepareStatement(query);
     pstmt.setString(1, fileName);
     pstmt.setCharacterStream(2, fileReader, (int) fileLength);
     // execute query, and return number of rows created
     int rowCount = pstmt.executeUpdate();
     System.out.println("rowCount=" + rowCount);
     // end transaction
     conn.rumit();
   } finally {
     pstmt.close();
     conn.close();
   }
 }

}

      </source>
   
  
 
  

Insert Text File To Oracle

   <source lang="java">

import java.io.File; import java.io.FileInputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class InsertTextFileToOracle {

 public static Connection getConnection() throws Exception {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@localhost:1521:oracle";
   String username = "username";
   String password = "password";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static void main(String[] args)throws Exception {
   String id = "001";
   String fileName = "fileName.txt";
   
   FileInputStream fis = null;
   PreparedStatement pstmt = null;
   Connection conn = null;
   try {
     conn = getConnection();
     conn.setAutoCommit(false);
     File file = new File(fileName);
     fis = new FileInputStream(file);
     pstmt = conn.prepareStatement("insert into DataFiles(id, fileName, fileBody) values (?, ?, ?)");
     pstmt.setString(1, id);
     pstmt.setString(2, fileName);
     pstmt.setAsciiStream(3, fis, (int) file.length());
     pstmt.executeUpdate();
     conn.rumit();
   } catch (Exception e) {
     System.err.println("Error: " + e.getMessage());
     e.printStackTrace();
   } finally {
     pstmt.close();
     fis.close();
     conn.close();
   }
 }

}

      </source>