JDBC Clob datatype, store and read data with java

by wecancode-founder | Aug 4, 2021 | Java, MySql

What is JDBC Clob data type? how to store and read data from it?

CLOB stands for Character Large Object in general.

SQL Clob is a built-in datatype and is used to store huge amount of characters data. Using this datatype, you can store data up to 2,147,483,647 characters.

The java.sql.Clob interface of the JDBC API represents the CLOB datatype. Since the Clob object in JDBC is implemented using an SQL locator, it holds a logical pointer to the SQL CLOB (not the data).

MYSQL database provides support for this datatype using four variables.

TINYTEXT: A CLOB type with a maximum of 28-1 (255) characters.
TEXT: A CLOB type with a maximum of 216-1 (65535) characters.
MEDIUMTEXT: A CLOB type with a maximum of 224-1 (16777215) characters.
LONGTEXT: A CLOB type with a maximum of 232-1 (4294967295 ) characters.

Storing Clob datatype in to table in a database

To store Clob datatype to database, using JDBC program follow the steps given below

Step 1: Connect to the database

You can connect to a database using the getConnection() method of the DriverManager class.

Connect to the MySQL database by passing the MySQL URL which is jdbc:mysql://localhost/sampleDB (where sampleDB is the database name), username and password as parameters to the getConnection() method.

String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");

Step 2: Create a Prepared statement

Create a PreparedStatement object using the prepareStatement() method of the Connection interface. To this method pass the insert query (with place holders) as a parameter.

PreparedStatement pstmt = con.prepareStatement("INSERT INTO Technologies(Name,
Type, Article ) VALUES (?, ?, ?)");

Step 3: Set values to the place holders

Set the values to the place holders using the setter methods of the PreparedStatement interface. Chose the methods according to the datatype of the column. For Example if the column is of VARCHAR type use setString() method and if it is of INT type you can use setInt() method.

And if it is of Clob type you can set value to it using the setCharacterStream() or setClob() methods. To these methods pass an integer variable representing the parameter index and an object of the Reader class as parameters.

pstmt.setString(1, "JavaFX");
pstmt.setString(2, "Java Library");
FileReader reader = new FileReader("E:\\images\\javafx.txt");
pstmt.setClob(3, reader);
pstmt.execute();

Step 4: Execute the statement

Execute the above created PreparedStatement object using the execute() method of the PreparedStatement interface.

Retrieving blob from database

The getClob() method of the ResultSet interface accepts an integer representing the index of the column (or, a String value representing the name of the column) and retrieves the value at the specified column and returns it in the form of a Clob object.

while(rs.next()) {
   System.out.println(rs.getString("Name"));
   System.out.println(rs.getString("Type"));
   Clob clob = rs.getClob("Article");
}

The getCharacterStream() method of the Clob Interface retrieves the contents of the current Clob object and returns as a Reader object.

Using the getClob() method you can get the contents of the Clob as a Reader object and create text file with the retrieved contents, using the write() method of the FileOutputStream object.

Reader r = clob.getCharacterStream();
char cbuf[] = new char[r.read()];
r.read(cbuf);
FileOutputStream outPutStream = new
FileOutputStream("E:\\images\\clob_output"+i+".txt");
outPutStream.write(cbuf.toString().getBytes());

Example : Creates table with Clob field | Inserts large text data from a file to it. Retrieves the text back and stores it in another text file.

import java.io.FileReader;
import java.io.FileWriter;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class ClobExample {
   public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a table
      Statement stmt = con.createStatement();
      stmt.execute("CREATE TABLE Technologies( Name VARCHAR(255), Type VARCHAR(255), Article LONGTEXT)");
      System.out.println("Table Created......");

      //Inserting values
      String query = "INSERT INTO Technologies(Name, Type, Article ) VALUES (?, ?, ?)";
      PreparedStatement pstmt = con.prepareStatement(query);
      pstmt.setString(1, "JavaFX");
      pstmt.setString(2, "Java Library");
      FileReader reader = new FileReader("E:\\images\\javafx.txt");
      pstmt.setClob(3, reader);
      pstmt.execute();
      pstmt.setString(1, "CoffeeScript");
      pstmt.setString(2, "Scripting Language");
      reader = new FileReader("E:\\images\\coffeescript.txt");
      pstmt.setClob(3, reader);
      pstmt.execute();
      pstmt.setString(1, "Cassandra");
      pstmt.setString(2, "NoSQL Database");
      reader = new FileReader("E:\\images\\cassandra.txt");
      pstmt.setClob(3, reader);
      pstmt.execute();
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from Technologies");
      int j = 0;
      System.out.println("Contents of the table are: ");
      while(rs.next()) {
         System.out.println(rs.getString("Name"));
         Clob clob = rs.getClob("Article");
         Reader r = clob.getCharacterStream();
         String filePath = "E:\\Data\\clob_output"+j+".txt";
         FileWriter writer = new FileWriter(filePath);
         int i;
         while ((i=r.read())!=-1) {
            writer.write(i);
         }
         writer.close();
         System.out.println(filePath);
         j++;
      }
   }
}

wecancode-founder

August 04, 2021

Java & C#.NET Developer with 10++ years of IT experience.

Planning to learn ReactJS or Angular or Flutter.!

Java 8: Streams – Part 1

by WeCanCode-Author | Aug 23, 2020 | Java, Program

What is Streams?

Stream is a new abstract layer introduced in Java 8, It represents a sequence of objects from a source, which supports aggregate operations.

To understand better, lets take basic sql function distinct.

SELECT DISTINCT(USER_TYPE_ FROM [MYSCHEMA].[USER];

In the above SQL script, we perform Distinct operation using the DISTINCT method. which actually performs operation on the retrived data/rows.

Similarly Java8:Streams also performs operation or action on collective data.

Follow are the characteristic of Java8:Streams

Sequence of elements − Stream provides a set of elements of specific type in a sequential manner. A stream gets/computes/manipulated the elements/data. It never stores the manipulated elements/data.
Source − Stream accepts Collections, Arrays, or I/O resources as input source.
Aggregate operations − Stream supports aggregate operations such as filter, map, limit, reduce, find, match, & so on.
Pipelining − Most of the stream operations return stream itself so that their result can be pipelined. These operations are called intermediate operations and their function is to take input, process them, and return output to the target. collect() method is a terminal operation which is normally present at the end of the pipelining operation to mark the end of the stream.
Automatic iterations − Stream operations do the iterations internally over the source elements provided, in contrast to Collections where explicit iteration is required.

Lets continure in the next part..

Java Programming: Why do we have main method ? can this method name be changed ?

by WeCanCode-Author | Oct 27, 2019 | How-To, Interview, Java

I was wondering always, why it is always main method is the starting point for the Java program.
Well When i was matured enough to read the Java Specification i learnt why and whether can it be changed ? Okay Lets See !!

Why main method ?

Main method is the starting point for the java program, JVM initialize the class and invokes main method, having below listed specifications (reference 12.1.4).

Should be named as main
Should be public
Should be static
Should accept String array as argument.
Return type is void

As based on the above specification, we can conclude that we cannot rename the main method.

Some Additional Info.!

public static void main(String[] args)  // this is valid

public static void main(String… args)  //String…  this is also valid where it is available from the java 5

public static void main(String args[]) //This is valid but not recommended because the array brackets in the variable)

String Java Program: Palindrome

by WeCanCode-Author | Oct 24, 2019 | Code, Interview, Java, Program

Interview Questions
Refer this for preparing yourself on how to write java program to check given string is a Palindrome or not.

Palindrome

public class PalindromeApp {
	public static void main(String[] args) {
		String input = "dad";
		if (PalindromeApp.isPalindrome(input)) {
			System.out.println(String.format("%s is palindrome", input));
		} else {
			System.out.println(String.format("%s is NOT palindrome", input));
		}
	}

	private static Boolean isPalindrome(String input) {
		char[] inputArr = input.toLowerCase().toCharArray();
		char[] reverse = new char[inputArr.length];

		for (int i = 0; i &lt; inputArr.length; i++) {
			reverse[i] = inputArr[(inputArr.length - 1) - i];
		}
		String r = new String(reverse); // String.copyValueOf(reverse);
		return (input.equals(r));
	}
}

JAVA PROGRAM TO PRINT PATTERNS

by WeCanCode-Author | Oct 10, 2019 | Code, Interview, Java, Program

Interview Questions
Refer this for preparing yourself on how to write java program to print patterns (Note: NOT Design Patterns)

Start Pattern
Number Pattern

Star Pattern

Print pattern like below, By giving the max length of the print. (max. length is 5) — *(max. length is 5)*

public class StarPatternApp {
	public static void main(String[] args) {
		StarPatternApp.printPattern("*", 5);
	}
	private static void printPattern(String pattern, int size) {
		StringBuilder patternBuilder = new StringBuilder();
		for (int i = 1; i &lt;= size; i++) {
			patternBuilder.append(pattern);
			System.out.println(patternBuilder.toString());
		}
		for (int i = size; i > 1; i--) {
			System.out.println(patternBuilder.deleteCharAt(i - 1).toString());
		}
	}

	private static String addSpaceAndPrint(int spaceCount, String printStr) {
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i &lt; spaceCount; i++) {
			sb.append("" + i);
		}
		sb.append(printStr);
        	return sb.toString();
	}
}

Number Pattern

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class NumberReducingPatternApp {

    public static void main(String[] args) {
       int n = 5;
       int length = 1;
       List&lt;Integer> numberPatternItems = new ArrayList&lt;>();
       for(int i=n; i>0;i--){
           numberPatternItems.add(listRangeInReverse(n,length));
           length++;
       }

       //This line actually reverst the entire list, so that the expected number pattern is printed
       Collections.reverse(numberPatternItems);
        for(Integer x: numberPatternItems){
            System.out.println(x);
        }
    }

    private static Integer listRangeInReverse(int k, int count) {
        StringBuilder sb = new StringBuilder();
        for (int c = k; c >=count;c--){
            sb.append(c);
        }
        return Integer.parseInt(sb.toString());
    }
}

Just a Note
There are N-ways of writing the above codes, please feel free to explore by playing around it.

Java My Way of learning

by WeCanCode-Author | Dec 9, 2018 | hello-world, How-To, Java

OOPs

Object oriented Programming Concepts

Abstraction
Encapsulation
Polymorphism
Inheritance
Association
Composition
Aggregation

Abstraction

Hiding the internal details and describing things in simple terms, there are many ways to achieve abstraction in OOPs, such as encapsulation and inheritance.

For Example: a method that adds two integers, the method internal process is hidden from outer world.

Encapsulation

It is a technique to implement abstraction in OOPs, used for access restriction to class method and members.

Access modifier keywords are used for encapsulation in OOPs.

For Example: encapsulation is achieved using the keywords private, public, and protected.

Polymorphism

It is the concept where an object behaves differently in different situation.

Two type: runtime polymorphism and compile time polymorphism.

Compile time polymorphism: achieved by method overloading, (meaning same method name, but different arguments).

Runtime polymorphism: This is implemented when we have IS-A relationship between objects. Called method overriding.

Example:Same method being used by another class which implements the super class which has this method declaration.

public interface shape{
            public void draw();
}
public class circle implements shape{
            @Override
            Public void draw(){
                //Method overridden from shape interface
             }
}

Inheritance

It is concept where object is based on another object. Inheritance is the mechanism of code reuse, Super class and sub class.

We use extends keyword to implement the inheritance.

Association

It the concept to define the relationship between objects, One to many relationship in Teachers and students object similarly students can have one to many relation with teacher class,

Aggregation

Object has its own life cycle, but there is an ownership. Whenever we have HAS-A relationship between objects and ownership then it’s Aggregation.

Composition

Composition is more restrictive form of Aggregation, when the contained object in HAS-A relationship cant exist in it own.

Example: house has-a room, well room cannot exist without house.

Java Concurrency (Multi-threading)

It is the ability to run several programs or several parts of a program in parallel, an time consuming task can be performed asynchronously or in parallel.

Process Vs Thread

Process runs independently and isolated of other processes, it cannot directly access shared data in other processes. OS provides the resources such as memory, CPU time.

Thread is so called lightweight process, it has its own call stack, but it can access shared data of other threads in the same process. Thread has its own memory cache, the thread shared data is stored in the memory cache. Thread can re-read the shared data.

Java application runs by default in once process, within the java application multiple threads to achieve parallel processing or asynchronous behavior.

Issues with Concurrency

Since thread has its own call stack and also can access the shared data, because of this we have two problem, Visibility and access problem.

Visibility problem: when thread A reads shared data and which is later modified by the thread B and thread A does not know about the change.

Access problem: when many threads try to change the shared data at the same time

How to avoid multiple thread call same method?

By using the synchronized keyword, which allows only one thread to enter into the method at the same time.

public synchronized void someMethod(){ }

Collections

Collections in java is a framework that provides an architecture to store and manipulate the group of objects.

We can perform the following operations on the collection of data, Searching,sorting, insertion, deletion and manipulation.

In Java collections we have few interfaces (Set, List, Queue, Deque) and Classes(ArrayList, Vector, LinkedList, PriorityQueue, HashSet, TreeSet)

All these interface and classes are presented in the java.util package in java

Methods of Collection Interface

public boolean add(E e) – Used to add element in the collection
public boolean addAll(Collection<?Extends E> c) – used to add one collection to another.
public boolean remove(Object element) – used to remove the object from the collection.
public boolean removeAll(Collection<?>c) – used to remove specific collection in a collection
default boolean removeIf(Predicate<? Super E> filter) – Used to remove the element based on the predicate.
public boolean retainAll(Collection<?> c) – used to remove all other element apart from the collection passes as argument.
public int size() – gives the size of the collection
public void clear() – it removed the total number of elements in the collection
public boolean contains(Object element) – used to check whether the passed element argument is available in the collection.
public boolean containsAll(Collection<?>c) – used to check whether the passed collection element are available in the collection.
public Iterator iterator() – used to provide the Iterator for the collection.
public Object[] toArray() – used to convert the collection to an Array
public <T> T[] toArray(T[] a)– it converts collection into array, it has runtime type of returned array.
public boolean isEmpty() – used to check whether the collection is empty.
public boolean equals(Object element) – used to check whether the element matches with the collection.
public int hashCode() – it returns the hashcode of the collection.

Iterator Interface

It provides facility to iterate over the collection in forward direction only.

Method of iterator interface:

public boolean hashNext() – checks whether it has any more element.
- public Object next() – it gives the item from the collection and moved the cursor to next element.
- public void remove() – it removed the last element returned by the iterator, it is used less.

How to iterate over the collection

List sample = new ArrayList();
sample.add(“Hello”);
sample.add(“world”);
sample.add(“test”);

Iterator itr = sample.iterator();
while(itr.hasNext()){
System.out.println(itr.next());
}

Iterable Interface

It is the root interface for all the collection classes, The Collection interface extends the Iterable interface.

Hence all the classes and sub class of collection also implements the Iterable interface.

Iterable interface contains only one abstract method (Iterator<T> iterator())

Collection Interface

This is the interface which is implemented by all the classes in Collection framework.

List Interface

It is the child interface of Collection interface, It inhibits a list data type structure in which we can store the ordered collection of objects. It can have duplicate values.

List interface is implemented by the following classes,

ArrayList – List<T> list1 = newArrayList();
Stack –List<T> list1 = new Stack();
Vector – List<T> list1 = new Vector();
LinkesList – List<T> list1 = newLinkedList();

Set Interface

It represents the unordered set of elements which does not allow us to store duplicate elements. We can store at most one NULL value in Set.

Set interface is implemented by the following classes,

HashSet – Set<T>set1 = new HashSet(); – this uses the HashTable for storage, it contains uniqueelements.
LinkedHashSet – Set<T> set2 = new LinkedHashSet();- LinkedList implementation of Set Interface and extends HashSet, So It maintainsthe insertion order , permits null element and contains unique elements.
TreeSet – SortedSet<T> set3 = new TreeSet(); – Itis SortedSet, it is arranged in ascending order, TreeSet is fastwhen accessing compared to the HashSet.

Map Interface

Map is based on Key, Value pair collection. Each key and value is know as entity. Map contains unique key. And it does not contains duplicate keys.

There are two interface implementing Map and SortedMap and three classes

HashMap – It is implemented from Map and does not maintain any order
TreeMap – It is implemented from Mapand SortedMap, it maintains ascending order.
LinkedHashMap – It is implemented fromMap and it inherits HashMap class, it maintains the insertion order.

Method	Description
V put(Object key, Object value)	It is used to insert an entry in the map.
void putAll(Map map)	It is used to insert the specified map in the map.
V putIfAbsent(K key, V value)	It inserts the specified value with the specified key in the map only if it is not already specified.
V remove(Object key)	It is used to delete an entry for the specified key.
boolean remove(Object key, Object value)	It removes the specified values with the associated specified keys from the map.
Set keySet()	It returns the Set view containing all the keys.
Set<Map.Entry<K,V>> entrySet()	It returns the Set view containing all the keys and values.
void clear()	It is used to reset the map.
V compute(K key, BiFunction<? super K,? super V,? extends V> remappingFunction)	It is used to compute a mapping for the specified key and its current mapped value (or null if there is no current mapping).
V computeIfAbsent(K key, Function<? super K,? extends V> mappingFunction)	It is used to compute its value using the given mapping function, if the specified key is not already associated with a value (or is mapped to null), and enters it into this map unless null.
V computeIfPresent(K key, BiFunction<? super K,? super V,? extends V> remappingFunction)	It is used to compute a new mapping given the key and its current mapped value if the value for the specified key is present and non-null.
boolean containsValue(Object value)	This method returns true if some value equal to the value exists within the map, else return false.
boolean containsKey(Object key)	This method returns true if some key equal to the key exists within the map, else return false.
boolean equals(Object o)	It is used to compare the specified Object with the Map.
void forEach(BiConsumer<? super K,? super V> action)	It performs the given action for each entry in the map until all entries have been processed or the action throws an exception.
V get(Object key)	This method returns the object that contains the value associated with the key.
V getOrDefault(Object key, V defaultValue)	It returns the value to which the specified key is mapped, or defaultValue if the map contains no mapping for the key.
int hashCode()	It returns the hash code value for the Map
boolean isEmpty()	This method returns true if the map is empty; returns false if it contains at least one key.
V merge(K key, V value, BiFunction<? super V,? super V,? extends V> remappingFunction)	If the specified key is not already associated with a value or is associated with null, associates it with the given non-null value.
V replace(K key, V value)	It replaces the specified value for a specified key.
boolean replace(K key, V oldValue, V newValue)	It replaces the old value with the new value for a specified key.
void replaceAll(BiFunction<? super K,? super V,? extends V> function)	It replaces each entry’s value with the result of invoking the given function on that entry until all entries have been processed or the function throws an exception.
Collection values()	It returns a collection view of the values contained in the map.
int size()	This method returns the number of entries in the map.