servlets struts hibernate spring maven java
 

JDBC - Streaming ASCII and Binary Data



A PreparedStatement object can utilize information and yield streams to supply parameter information. This empowers you to put whole records into database segments that can hold extensive qualities, for example, CLOB and BLOB information types.

There are following strategies, which can be utilized to stream information −

  • setAsciiStream(): This strategy is utilized to supply substantial ASCII values.

  • setCharacterStream(): This strategy is utilized to supply substantial UNICODE values.

  • setBinaryStream(): This strategy is utilized to supply substantial twofold values.

The setXXXStream() strategy requires an additional parameter, the document estimate, other than the parameter placeholder. This parameter advises the driver how much information ought to be sent to the database utilizing the stream.

Example

Consider we need to transfer a XML record XML_Data.xml into a database table. Here is the substance of this XML document −

<?xml version="1.0"?> 

<Employee> 

<id>100</id> 

<first>Zara</first> 

<last>Ali</last> 

<Salary>10000</Salary> 

<Dob>18-08-1978</Dob> 

<Employee>

Keep this XML document in a similar catalog where you are going to run this example.

This precedent would make a database table XML_Data and afterward document XML_Data.xml would be transferred into this table.

Copy and past the accompanying precedent in JDBCExample.java, aggregate and keep running as pursues −

//Import required packages 

import java.sql.*; 

import java.io.*; 

import java.util.*;
    
    
   public class JDBCExample { 

//JDBC driver name and database URL 

static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 

static final String DB_URL = "jdbc:mysql://localhost/EMP"; 

//Database credentials 

static final String USER = "username"; 

static final String PASS = "password"; 

public static void main(String[] args) { 

Connection conn = null; 

PreparedStatement pstmt = null; 

Statement stmt = null; 

ResultSet rs = null; 

try{ 

//Register JDBC driver 

Class.forName("com.mysql.jdbc.Driver"); 

//Open a connection 

System.out.println("Connecting to database..."); 

conn = DriverManager.getConnection(DB_URL,USER,PASS); 

//Create a Statement item and manufacture table 

stmt = conn.createStatement(); 

createXMLTable(stmt); 

//Open a FileInputStream 

File f = new File("XML_Data.xml"); 

long fileLength = f.length(); 
FileInputStream fis = new FileInputStream(f); 

//Create PreparedStatement and stream data 

String SQL = "INSERT INTO XML_Data VALUES (?,?)"; 

pstmt = conn.prepareStatement(SQL); 

pstmt.setInt(1,100); 

pstmt.setAsciiStream(2,fis,(int)fileLength); 

pstmt.execute(); 

//Close information stream 

fis.close(); 

//Do an inquiry to get the row 

SQL = "SELECT Data FROM XML_Data WHERE id=100"; 

rs = stmt.executeQuery (SQL); 

//Get the first row 

if (rs.next ()){ 

//Retrieve information from info stream 

InputStream xmlInputStream = rs.getAsciiStream (1); 

int c; 

ByteArrayOutputStream bos = new ByteArrayOutputStream(); 

while (( c = xmlInputStream.read ()) != -1) 

bos.write(c); 

//Print results 

System.out.println(bos.toString()); 

} 

//Clean-up environment 

rs.close(); 

stmt.close(); 

pstmt.close(); 

conn.close(); 

}catch(SQLException se){ 

//Handle blunders for JDBC 

se.printStackTrace(); 

}catch(Exception e){ 

//Handle blunders for Class.forName 

e.printStackTrace(); 

}finally{ 

//at last square used to close resources 

try{ 

if(stmt!=null) 

stmt.close(); 

}catch(SQLException se2){ 

}//nothing we can do 

try{ 

if(pstmt!=null) 

pstmt.close(); 

}catch(SQLException se2){ 

}//nothing we can do 

try{ 

if(conn!=null) 

conn.close(); 

}catch(SQLException se){ 

se.printStackTrace(); 

}//end at last try 

}//end try 

System.out.println("Goodbye!"); 

}//end mainpublic static void createXMLTable(Statement stmt) 

throws SQLException{ 

System.out.println("Creating XML_Data table..." ); 

//Create SQL Statement 

String streamingDataSql = "CREATE TABLE XML_Data " + 

"(id INTEGER, Data LONG)"; 

//Drop table first on the off chance that it exists. 

try{ 

stmt.executeUpdate("DROP TABLE XML_Data"); 

}catch(SQLException se){ 

}//do nothing 

//Build table. 

stmt.executeUpdate(streamingDataSql); 

}//end createXMLTable 

}//end JDBCExample

Now let us accumulate the above model as pursues −

C:\>javac JDBCExample.java 

C:\>

When you run JDBCExample, it creates the accompanying outcome −

C:\>java JDBCExample 

Connecting to database... 

Creating XML_Data table... 

<?xml version="1.0"?> 

<Employee> 

<id>100</id> 

<first>Zara</first> 

<last>Ali</last> 

<Salary>10000</Salary> 

<Dob>18-08-1978</Dob> 

<Employee> 

Goodbye! 

C:\>







© Javacodegeeks 2019 - 2019
All Right Reserved and you agree to have read and accepted our term and condition.

Python 3