Working with Lambda and JDBC

public <T> List<T> getAll(String sql, Function<ResultSet,T> function) {

        final List<T> list = new ArrayList<>();

        try (Connection con = getConeccion()) {

            try (Statement stmt = con.createStatement()) {

                try (ResultSet rs = stmt.executeQuery(sql)) {

                    while (rs.next()) {

                        list.add(function.apply(rs));

                    }

                }

            }

        } catch (SQLException ex) {

             Logger.getLogger(getClass().getName()).log(Level.SEVERE, null, ex);

        }

        return list;

    }

This is a  generic simple solution for querying to database using JDBC.

And we can use the above snippet in this way:

public List<Country> getAllCountries() {

        return getAll("select * from petcare.country", new Function<ResultSet, Country>() {

            @Override

            public Country apply(ResultSet rs) {

                Country c = new Country();

              

                try {

                  

                    c.setId(rs.getInt("id"));

                    c.setName(rs.getString("name"));

                  

                } catch (SQLException ex) {

                    Logger.getLogger(getClass().class.getName()).log(Level.SEVERE, null, ex);

                }

                return c;

            }

        });

    }

How to insert an image into database table? or Write an example for inserting BLOB into table.

package com.java2novice.jdbc;   import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException;   public class MyBlobInsert {       public static void main(String a[]){                   Connection con = null;         PreparedStatement ps = null;         InputStream is = null;         try {             Class.forName("oracle.jdbc.driver.OracleDriver");             con = DriverManager.                     getConnection("jdbc:oracle:thin:@<hostname>:<port num>:<DB name>"                         ,"user","password");             ps = con.prepareCall("insert into student_profile values (?,?)");             ps.setInt(1, 101);             is = new FileInputStream(new File("Student_img.jpg"));             ps.setBinaryStream(2, is);             int count = ps.executeUpdate();             System.out.println("Count: "+count);         } catch (ClassNotFoundException e) {             // TODO Auto-generated catch block             e.printStackTrace();         } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         } catch (FileNotFoundException e) {             // TODO Auto-generated catch block             e.printStackTrace();         } finally{             try{                 if(is != null) is.close();                 if(ps != null) ps.close();                 if(con != null) con.close();             } catch(Exception ex){}         }     } }

- See more at: http://www.java2novice.com/jdbc/insert-image/#sthash.hpJ21hin.dpuf

JDBC- Execute INSERT query using Statement's executeUpdate method in java

java.sql.Statement's executeUpdate method can be used for executing INSERT queries and

executeUpdate method returns number of rows inserted

--Before executing java program execute these database scripts  >

create table EMPLOYEE(ID number(4), NAME varchar2(22));

commit;

--If table already exists then execute the DROP command >

drop table EMPLOYEE;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class StatementInsertTest {

   public static void main(String... arg) {

          Connection con = null;

          Statement stmt = null;

          try {

                 // registering Oracle driver class

                 Class.forName("oracle.jdbc.driver.OracleDriver");

                 // getting connection

                 con = DriverManager.getConnection(

                              "jdbc:oracle:thin:@localhost:1521:orcl",

                              "ankit", "Oracle123");

                 System.out.println("Connection established successfully!");

                

                 stmt = con.createStatement();

                 //execute insert query

                 int numberOfRowsInserted=stmt.executeUpdate("INSERT into EMPLOYEE(ID,NAME)"

                                                                                                                                        + "values (1, 'ankit') ");

                 System.out.println("numberOfRowsInserted=" + numberOfRowsInserted);

          } catch (ClassNotFoundException e) {

                 e.printStackTrace();

          } catch (SQLException e) {

                 e.printStackTrace();

          }

          finally{

                 try {

                       if(stmt!=null) stmt.close(); //close Statement

                       if(con!=null) con.close(); // close connection

                 } catch (SQLException e) {

                       e.printStackTrace();

                 }

          }

   }

}

/*OUTPUT

Connection established successfully!

numberOfRowsInserted=1

*/

JDBC- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in java

The java.sql.CallableStatement is an interface which can be used to execute SQL stored procedures in java.

How to deal with SQL stored procedures OUT parameter in java-

1) OUT parameters must be registered in java before executing

the stored procedure,

2) Execute database stored procedure,

3) Then retrieve values of OUT parameters using using get methods.

--Before executing java program execute these database scripts  >

create table EMPLOYEE(ID number(4), NAME varchar2(22), SALARY number(8), CREATION_DATE date);

insert into EMPLOYEE values(11, 'ankit', 12000, sysdate);

commit;

--Procedure accepts id as IN parameter, selects data from EMPLOYEE table on basis of id and

--populate following OUT parameters > name, salary and creation_date

create or replace PROCEDURE MYPROC_EMPLOYEE_SELECT_OUT(   p_id IN EMPLOYEE.ID%TYPE,   p_name OUT EMPLOYEE.NAME%TYPE,   p_salary OUT EMPLOYEE.SALARY%TYPE,   p_creation_date OUT EMPLOYEE.CREATION_DATE%TYPE) IS BEGIN  SELECT NAME, SALARY, CREATION_DATE  INTO p_name, p_salary, p_creation_date  FROM EMPLOYEE where ID = p_id; END;

--If table already exists then execute the DROP command >

drop table EMPLOYEE;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */

public class CallableStatementTestProcedureOUT {

public static void main(String... arg) {

     Connection con = null;

     CallableStatement callableStmt = null;

     try {

          // registering Oracle driver class

          Class.forName("oracle.jdbc.driver.OracleDriver");

          // getting connection

          con = DriverManager.getConnection(

                   "jdbc:oracle:thin:@localhost:1521:orcl",

                   "ankit", "Oracle123");

          System.out.println("Connection established successfully!");

         

          callableStmt = con.prepareCall("{call MYPROC_EMPLOYEE_SELECT_OUT(?,?,?,?)}");

          //IN parameter -

          //   1) set methods are used for setting IN parameter values of Stored procedure

          callableStmt.setInt(1, 11);

         

          //OUT parameter -

          // 1) OUT parameters must be registered in java before executing the stored procedure,

          callableStmt.registerOutParameter(2, java.sql.Types.VARCHAR);

          callableStmt.registerOutParameter(3, java.sql.Types.NUMERIC);

          callableStmt.registerOutParameter(4, java.sql.Types.DATE);

         

          //OUT parameter -

          //   2) Execute database stored procedure,

          callableStmt.executeUpdate();

          //OUT parameter -

          //   3) Then retrieve values of OUT parameters using using get methods.

          System.out.println("name = " + callableStmt.getString(2));

          System.out.println("salary = " + callableStmt.getInt(3));

          System.out.println("creationDate = " +callableStmt.getDate(4));//returns java.Sql.Date

         

          System.out.println("Stored procedure executed successfully, "

              + "data has been fetched from Employee table");

     } catch (ClassNotFoundException e) {

          e.printStackTrace();

     } catch (SQLException e) {

          e.printStackTrace();

     }

     finally{

          try {

              if(callableStmt!=null) callableStmt.close(); //close CallableStatement

              if(con!=null) con.close(); // close connection

          } catch (SQLException e) {

              e.printStackTrace();

          }

     }

}

}

/*OUTPUT

Connection established successfully!

name = ankit

salary = 12000

creationDate = 2015-07-27

Stored procedure executed successfully, data has been fetched from Employee table

*/