Fetching and updating data of various databases using JDBC

In this article, I will be showing you how to fetch and update data of database using JDBC. You may operate any database using the required driver of the particular database

For an example I have used connection to H2 database. H2 has both embedded and server mode. I have tried the server mode. In server mode concurrent connections can be possible.

Prerequisites:

1. Eclipse IDE, Luna or any older version
2. JDK 8 or any older
3. Download Windows installer of H2 database and install to any folder in Windows. You will find a jar file like h2-1.3.176.jar. Copy the file to the library folder of your project and import the JAR.

Now you have done the initial configuration. What you need to do now is create a database in H2.

Double click the h2.bat file in the bin folder of the H2 folder in the installed location. It will load in your default browser. Select the H2 Generic H2 (Server) in first drop down. Give a name in second field. Copy the driver class to a notepad. Create a url in JDBC url field like jdbc:h2:tcp://ipaddress/~/test. Save this to the notepad.

Open Eclipse now. Create a Java class to code the JDBC connection part.

JDBC Class

package datpackage;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.sql.Statement;
import org.h2.tools.Server;

/**
 * @author www.javaworkspace.com
 *
 */
public class jdbcclass {
    public static ResultSet resultSet = null;
    public static Connection connection = null;
    public static Statement statement = null;
    public static Server server;
    public static void query(String sql) {       
        try {
            Class.forName("org.h2.Driver");
           
            try{
                connection = DriverManager.getConnection("jdbc:h2:tcp://ipaddres/~/test", "sa", "");
                   
            }
           catch(SQLException e2){
               e2.printStackTrace();
               return;   
               
           }
            statement = connection.createStatement();
           
            try{
                resultSet= statement.executeQuery(sql);
               
            }
        catch(SQLIntegrityConstraintViolationException e1){
            java.awt.Toolkit.getDefaultToolkit().beep();
            return;   
        }
            while (resultSet.next()) {
                System.out.println(resultSet.getString("NAME"));
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
            return;   
            } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if(resultSet!=null){
                resultSet.close();
                statement.close();
                connection.close();
                }
               
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    public static void update(String sql){
        try {
            Class.forName("org.h2.Driver");

            try{
                connection = DriverManager.getConnection("jdbc:h2:tcp://ipaddres/~/test", "sa", "");
            }
            catch(SQLException e2){
                e2.printStackTrace();
                return;   

            }
            try {
                statement = connection.createStatement();
                statement.executeUpdate(sql);
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }  catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                statement.close();
                connection.close();
                server.stop();
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }

    }
}

You can now call statements in a different class to fetch and update data.

 To fetch data:

jdbcclass.query("SELECT * FROM H2DATABASE  NAME WHERE NAME LIKE A% ORDER BY NAME");

To update data:

jdbcclass.update("INSERT INTO H2DATABASE VALUES ('"Abc"', '"bcd"','"efg"');");