Posts Tagged ‘SQL’

The Greek lowercase omega (ω) character is use...

Here’s a guide to show you how to integrate JSF 2.0 with database via JDBC. In this example, we are using MySQL database and Tomcat web container.

Directory structure of this example

1. Table Structure

Create a “customer” table and insert five dummy records. Later, display it via JSF h:dataTable.

SQL commands

DROP TABLE IF EXISTS `mkyongdb`.`customer`;
CREATE TABLE  `mkyongdb`.`customer` (
  `CUSTOMER_ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(45) NOT NULL,
  `ADDRESS` VARCHAR(255) NOT NULL,
  `CREATED_DATE` datetime NOT NULL,
  PRIMARY KEY (`CUSTOMER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

INSERT INTO mkyongdb.customer(customer_id, name, address, created_date) 
VALUES(1, 'mkyong1', 'address1', now());
INSERT INTO mkyongdb.customer(customer_id, name, address, created_date) 
VALUES(2, 'mkyong2', 'address2', now());
INSERT INTO mkyongdb.customer(customer_id, name, address, created_date) 
VALUES(3, 'mkyong3', 'address3', now());
INSERT INTO mkyongdb.customer(customer_id, name, address, created_date) 
VALUES(4, 'mkyong4', 'address4', now());
INSERT INTO mkyongdb.customer(customer_id, name, address, created_date) 
VALUES(5, 'mkyong5', 'address5', now());

2. MySQL DataSource

Configure a MySQL datasource named “jdbc/mkyongdb“, follow this article – How to configure MySQL DataSource in Tomcat 6

3. Model Class

Create a “Customer” model class to store the table records.

File : Customer.java

package com.mkyong.customer.model;

import java.util.Date;

public class Customer{

	public long customerID;
	public String name;
	public String address;
	public Date created_date;

	//getter and setter methods 
}

4. JDBC Example

A JSF 2.0 managed bean, inject datasource “jdbc/mkyongdb” via @Resource, and uses normal JDBC API to retrieve all the customer records from database and store it into a List.

File : CustomerBean.java

package com.mkyong;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.mkyong.customer.model.Customer;

@ManagedBean(name="customer")
@SessionScoped
public class CustomerBean implements Serializable{

	//resource injection
	@Resource(name="jdbc/mkyongdb")
	private DataSource ds;

	//if resource injection is not support, you still can get it manually.
	/*public CustomerBean(){
		try {
			Context ctx = new InitialContext();
			ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mkyongdb");
		} catch (NamingException e) {
			e.printStackTrace();
		}

	}*/

	//connect to DB and get customer list
	public List getCustomerList() throws SQLException{

		if(ds==null)
			throw new SQLException("Can't get data source");

		//get database connection
		Connection con = ds.getConnection();

		if(con==null)
			throw new SQLException("Can't get database connection");

		PreparedStatement ps 
			= con.prepareStatement(
			   "select customer_id, name, address, created_date from customer"); 

		//get customer data from database
		ResultSet result =  ps.executeQuery();

		List list = new ArrayList();

		while(result.next()){
			Customer cust = new Customer();

			cust.setCustomerID(result.getLong("customer_id"));
			cust.setName(result.getString("name"));
			cust.setAddress(result.getString("address"));
			cust.setCreated_date(result.getDate("created_date"));

			//store all data into a List
			list.add(cust);
		}

		return list;
	}
}

5. JSF Page dataTable

A JSF 2.0 xhtml page, uses h:dataTable to display all the customer records in table layout format.

 

 <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"   
      xmlns:h="http://java.sun.com/jsf/html"
      xmlns:f="http://java.sun.com/jsf/core"
      >
    <h:head>
    	<h:outputStylesheet library="css" name="table-style.css"  />
    </h:head>
 
    <h:body>
 
    	<h1>JSF 2.0 + JDBC Example</h1>
 
 		<h:dataTable value="#{customer.getCustomerList()}" var="c"
    			styleClass="order-table"
    			headerClass="order-table-header"
    			rowClasses="order-table-odd-row,order-table-even-row"
    		>
 
    		<h:column>
    			<f:facet name="header">
    				Customer ID
    			</f:facet>
    				#{c.customerID}
    		</h:column>
 
    		<h:column>
    			<f:facet name="header">
    				Name
				</f:facet>
    				#{c.name}
    		</h:column>
 
 			<h:column>
    			<f:facet name="header">
    				Address
				</f:facet>
    				#{c.address}
    		</h:column>
 
    		<h:column>
    			<f:facet name="header">
    				Created Date
				</f:facet>
    				#{c.created_date}
    		</h:column>
 
    	</h:dataTable>
 
    </h:body>
 
</html>
 

6. Demo

Run it, see output

Download Source Code

Download It – JSF-2-JDBC-Integration-Example.zip (12KB)

International Bibliography of Periodical Liter...

executeQuery()—for getting the data from database
executeUpdate()—for insert,update,delete
execute()—any kind of operations

executeQuery() This is used generally for reading the content of the database. The output will be in the form of ResultSet. Generally SELECT statement is used.

executeUpdate() This is generally used for altering the databases. Generally DROP TABLE or DATABASE, INSERT into TABLE, UPDATE TABLE, DELETE from TABLE statements will be used in this. The output will be in the form of int. This int value denotes the number of rows affected by the query.

execute() If you dont know which method to be used for executing SQL statements, this method can be used. This will return a boolean. TRUE indicates the result is a ResultSet and FALSE indicates it has the int value which denotes number of rows affected by the query.

Deutsch: Logo der Christlich Sozialen Volkspartei

Loading CSV file into Database can be cumbersome task if your Database provider does not offer an out of box feature for this. Most of the time you’ll spend up in creating valid insert statements and putting up values escaping all special characters. Importing CSV files gets a bit complicated when you start doing things like importing files with description fields that can contain punctuation (such as commas or single-double quotation marks).

So here’s a simple Java Utility class that can be used to load CSV file into Database. Note how we used some of the best practices for loading data. The CSV file is parsed line by line and SQL insert query is created. The values in query are binded and query is added to SQL batch. Each batch is executed when a limit is reached (in this case 1000 queries per batch).

Import CSV into Database example

Let’s us check an example. Below is the sample CSV file that I want to upload in database table Customer.

employee.csv – Sample CSV file:

EMPLOYEE_ID,FIRSTNAME,LASTNAME,BIRTHDATE,SALARY
1,Dean,Winchester,27.03.1975,60000
2,John,Winchester,01.05.1960,120000
3,Sam,Winchester,04.01.1980,56000

The Table customer contains few fields. We added fields of different types like VARCHAR, DATE, NUMBER to check our load method works properly.

Table: Customer – Database table

CREATE TABLE Customer (
  EMPLOYEE_ID  NUMBER,
  FIRSTNAME    VARCHAR2(50 BYTE),
  LASTNAME     VARCHAR2(50 BYTE),
  BIRTHDATE    DATE,
  SALARY       NUMBER
)

Following is a sample Java class that will use CSVLoader utility class (we will come to this shortly).

Main.java – Load sample.csv to database

package net.viralpatel.java;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Main {
    private static String JDBC_CONNECTION_URL =
            "jdbc:oracle:thin:SCOTT/TIGER@localhost:1500:MyDB";
    
    public static void main(String[] args) {
        try {
            CSVLoader loader = new CSVLoader(getCon());
            
            loader.loadCSV("C:\\employee.sql", "CUSTOMER", true);
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private static Connection getCon() {
        Connection connection = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            connection = DriverManager.getConnection(JDBC_CONNECTION_URL);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

In above Main class, we created an object of class CSVLoader using parameterized constructor and passed java.sql.Connection object.

Then we called the loadCSV method with three arguments. First the path of CSV file, second the table name where data needs to be loaded and third boolean parameter which decides whether table has to be truncated before inserting new records.

Execute this Java class and you’ll see the records getting inserted in table.

csv-load-java-database-example

The CSV is successfully loaded in database.

Let’s check the Utility class now. I strongly recommend you to go through below tutorials as the Utility class combines the idea from these tutorials.

  1. Batch Insert In Java – JDBC
  2. Read / Write CSV file in Java
  3. Check if String is valid Date in Java

The utility class uses OpenCSV library to load and parse CSV file. Then it uses the idea of Batching in JDBC to batch insert queries and execute them. Each CSV value is checked if it is valid date before inserting.

CSVLoader.java – Utility class to load CSV into Database

package net.viralpatel.java;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;
import org.apache.commons.lang.StringUtils;
import au.com.bytecode.opencsv.CSVReader;
/**
 *
 * @author viralpatel.net
 *
 */
public class CSVLoader {
    private static final
        String SQL_INSERT = "INSERT INTO ${table}(${keys}) VALUES(${values})";
    private static final String TABLE_REGEX = "\\$\\{table\\}";
    private static final String KEYS_REGEX = "\\$\\{keys\\}";
    private static final String VALUES_REGEX = "\\$\\{values\\}";
    private Connection connection;
    private char seprator;
    /**
     * Public constructor to build CSVLoader object with
     * Connection details. The connection is closed on success
     * or failure.
     * @param connection
     */
    public CSVLoader(Connection connection) {
        this.connection = connection;
        //Set default separator
        this.seprator = ',';
    }
    
    /**
     * Parse CSV file using OpenCSV library and load in
     * given database table.
     * @param csvFile Input CSV file
     * @param tableName Database table name to import data
     * @param truncateBeforeLoad Truncate the table before inserting
     *          new records.
     * @throws Exception
     */
    public void loadCSV(String csvFile, String tableName,
            boolean truncateBeforeLoad) throws Exception {
        CSVReader csvReader = null;
        if(null == this.connection) {
            throw new Exception("Not a valid connection.");
        }
        try {
            
            csvReader = new CSVReader(new FileReader(csvFile), this.seprator);
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("Error occured while executing file. "
                    + e.getMessage());
        }
        String[] headerRow = csvReader.readNext();
        if (null == headerRow) {
            throw new FileNotFoundException(
                    "No columns defined in given CSV file." +
                    "Please check the CSV file format.");
        }
        String questionmarks = StringUtils.repeat("?,", headerRow.length);
        questionmarks = (String) questionmarks.subSequence(0, questionmarks
                .length() - 1);
        String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
        query = query
                .replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ","));
        query = query.replaceFirst(VALUES_REGEX, questionmarks);
        System.out.println("Query: " + query);
        String[] nextLine;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = this.connection;
            con.setAutoCommit(false);
            ps = con.prepareStatement(query);
            if(truncateBeforeLoad) {
                //delete data from table before loading csv
                con.createStatement().execute("DELETE FROM " + tableName);
            }
            final int batchSize = 1000;
            int count = 0;
            Date date = null;
            while ((nextLine = csvReader.readNext()) != null) {
                if (null != nextLine) {
                    int index = 1;
                    for (String string : nextLine) {
                        date = DateUtil.convertToDate(string);
                        if (null != date) {
                            ps.setDate(index++, new java.sql.Date(date
                                    .getTime()));
                        } else {
                            ps.setString(index++, string);
                        }
                    }
                    ps.addBatch();
                }
                if (++count % batchSize == 0) {
                    ps.executeBatch();
                }
            }
            ps.executeBatch(); // insert remaining records
            con.commit();
        } catch (Exception e) {
            con.rollback();
            e.printStackTrace();
            throw new Exception(
                    "Error occured while loading data from file to database."
                            + e.getMessage());
        } finally {
            if (null != ps)
                ps.close();
            if (null != con)
                con.close();
            csvReader.close();
        }
    }
    public char getSeprator() {
        return seprator;
    }
    public void setSeprator(char seprator) {
        this.seprator = seprator;
    }
}

The class looks complicated but it is simple :)

The loadCSV methods combines the idea from above three tutorials and create insert queries.

Following is the usage of this class if you want to use it in your project:

Usage

CSVLoader loader = new CSVLoader(connection);
loader.loadCSV("C:\\employee.csv", "TABLE_NAME", true);

Load file with semicolon as delimeter:

CSVLoader loader = new CSVLoader(connection);
loader.setSeparator(';');
loader.loadCSV("C:\\employee.csv", "TABLE_NAME", true);

Load file without truncating the table:

CSVLoader loader = new CSVLoader(connection);
loader.loadCSV("C:\\employee.csv", "TABLE_NAME", false);

Hope this helps.

Download Source Code

Load_CSV_Database_Java_example.zip (2.05 MB)

This tutorial guides us on how to pass Array objects from Java to stored procedures in Oracle and also, how to retrieve an array object in Java.

All PLSQL arrays can not be called from java. An array needs to be created as TYPE, at SCHEMA level in the database and then it can be used with ArrayDescriptor in Java, as oracle.sql.ArrayDescriptor class in Java can not access at package level.

Database Code

First, Create an array, at SCHEMA level. An example is shown below:

CREATE TYPE array_table AS TABLE OF VARCHAR2 (50); -- Array of String
CREATE TYPE array_int AS TABLE OF NUMBER;          -- Array of integers

Next, Create a procedure which takes an array as an input parameter and returns an array as its OUT parameter.

An example of one such procedure is shown below, which has 2 parameters –

  1. an array of String as its IN parameter – p_array
  2. an array of Integers as OUT parameter – p_arr_int
CREATE OR REPLACE PROCEDURE SchemaName.proc1 (p_array     IN     array_table,
                                              len            OUT NUMBER,
                                              p_arr_int      OUT array_int)
AS
   v_count   NUMBER;
BEGIN
   p_arr_int := NEW array_int ();
   p_arr_int.EXTEND (10);
   len := p_array.COUNT;
   v_count := 0;
   FOR i IN 1 .. p_array.COUNT
   LOOP
      DBMS_OUTPUT.put_line (p_array (i));
      p_arr_int (i) := v_count;
      v_count := v_count + 1;
   END LOOP;
END;
/

After this, Execution permission would be required to execute the procedure created by you:

GRANT EXECUTE ON SchemaNAme.proc1 TO UserName;

Java Code

Create a java class which makes a call to the procedure proc1, created before.

Below is an example which contains the whole flow from creating a connection with the database, to making a call to the stored procedure, passing an array to Oracle procedure, retrieving an array from an Oracle procedure and displaying the result.

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.internal.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class TestDatabase {
    
    public static void passArray()
    {
        try{
        
            Class.forName("oracle.jdbc.OracleDriver");         
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:url ","UserName","Password");;
            
            String array[] = {"one", "two", "three","four"};
            
            ArrayDescriptor des = ArrayDescriptor.createDescriptor("SchemaName.ARRAY_TABLE", con);
            ARRAY array_to_pass = new ARRAY(des,con,array);
            
            CallableStatement st = con.prepareCall("call SchemaName.proc1(?,?,?)");
            // Passing an array to the procedure -
            st.setArray(1, array_to_pass);
            st.registerOutParameter(2, Types.INTEGER);
            st.registerOutParameter(3,OracleTypes.ARRAY,"SchemaName.ARRAY_INT");
            st.execute();
            
            System.out.println("size : "+st.getInt(2));
            // Retrieving array from the resultset of the procedure after execution -
            ARRAY arr = ((OracleCallableStatement)st).getARRAY(3);
             BigDecimal[] recievedArray = (BigDecimal[])(arr.getArray());
            for(int i=0;i<recievedArray.length;i++)
                System.out.println("element" + i + ":" + recievedArray[i] + "\n");
            
        } catch(Exception e) {
            System.out.println(e);
        }
    }
    public static void main(String args[]){
        passArray();
    }
}

Brief Explanations:

  1. Class.forName() – Returns the Class object associated with the class or interface with the given string name.
  2. DriverManager.getConnection() – Attempts to establish a connection to the given database URL.
  3. oracle.sql.ArrayDescriptor – Describes an array class
  4. ArrayDescriptor.createDescriptor() – Descriptor factory. Lookup the name in the database, and determine the characteristics of this array.
  5. oracle.sql.ARRAY – An Oracle implementation for generic JDBC Array interface.
  6. CallableStatement – The interface used to execute SQL stored procedures.

References

International Bibliography of Periodical Liter...

JDBC

Let’s see how we can perform batch insert in Java using JDBC APIs. Although you might already knew this, I will try to explain the basic to a bit complex scenarios.

In this note, we will see how we can use JDBC APIs like Statement and PreparedStatement to insert data in any database in batches. Also we will try to explore scenarios where we can run out of memory and how to optimize the batch operation.

So first, the basic API to Insert data in database in batches using Java JDBC.

Simple Batch

I am calling this a simple batch. The requirement is simple. Execute a list of inserts in batch. Instead of hitting database once for each insert statement, we will using JDBC batch operation and optimize the performance.

Consider the following code:

Bad Code

String [] queries = {
    "insert into employee (name, city, phone) values ('A', 'X', '123')",
    "insert into employee (name, city, phone) values ('B', 'Y', '234')",
    "insert into employee (name, city, phone) values ('C', 'Z', '345')",
};
            
Connection connection = new getConnection();
Statement statemenet = connection.createStatement();
            
for (String query : queries) {
    statemenet.execute(query);
}
statemenet.close();
connection.close();

This is the BAD code. You are executing each query separately. This hits the database for each insert statement. Consider if you want to insert 1000 records. This is not a good idea.

We’ll below is the basic code to perform batch insert. Check it out:

Good Code

Connection connection = new getConnection();
Statement statemenet = connection.createStatement();
for (String query : queries) {
    statemenet.addBatch(query);
}
statemenet.executeBatch();
statemenet.close();
connection.close();

Note how we used addBatch() method of Statement, instead of directly executing the query. And after adding all the queries we executed them in one go using statement.executeBatch() method. Nothing fancy, just a simple batch insert.

Note that we have taken the queries from a String array. Instead you may want to make it dynamically. For example:

import java.sql.Connection;
import java.sql.Statement;
//...
Connection connection = new getConnection();
Statement statemenet = connection.createStatement();
for (Employee employee: employees) {
    String query = "insert into employee (name, city) values('"
            + employee.getName() + "','" + employee.getCity + "')";
    statemenet.addBatch(query);
}
statemenet.executeBatch();
statemenet.close();
connection.close();

Note how we are creating query dynamically using data from Employee object and adding it in batch to insert in one go. Perfect! isn’t it?

wait.. You must be thinking what about SQL Injection? Creating queries like this dynamically is very prone to SQL injection. And also the insert query has to be compiled each time.

Why not to use PreparedStatement instead of simple Statement. Yes, that can be the solution. Check out the below SQL Injection Safe Batch.

SQL Injection Safe Batch

Consider the following code:

import java.sql.Connection;
import java.sql.PreparedStatement;
//...
String sql = "insert into employee (name, city, phone) values (?, ?, ?)";
Connection connection = new getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
for (Employee employee: employees) {
    ps.setString(1, employee.getName());
    ps.setString(2, employee.getCity());
    ps.setString(3, employee.getPhone());
    ps.addBatch();
}
ps.executeBatch();
ps.close();
connection.close();        

Checkout the above code. Beautiful. We used java.sql.PreparedStatement and added insert query in the batch. This is the solution you must implement in your batch insert logic, instead of above Statement one.

Still there is one problem with this solution. Consider a scenario where you want to insert half million records into database using batch. Well, that may generate OutOfMemoryError:

java.lang.OutOfMemoryError: Java heap space
    com.mysql.jdbc.ServerPreparedStatement$BatchedBindValues.<init>(ServerPreparedStatement.java:72)
    com.mysql.jdbc.ServerPreparedStatement.addBatch(ServerPreparedStatement.java:330)
    org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:171)

This is because you are trying to add everything in one batch and inserting once. Best idea would be to execute batch itself in batch. Check out the below solution.

Smart Insert: Batch within Batch

This is a simplest solution. Consider a batch size like 1000 and insert queries in the batches of 1000 queries at a time.

String sql = "insert into employee (name, city, phone) values (?, ?, ?)";
Connection connection = new getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
final int batchSize = 1000;
int count = 0;
for (Employee employee: employees) {
    ps.setString(1, employee.getName());
    ps.setString(2, employee.getCity());
    ps.setString(3, employee.getPhone());
    ps.addBatch();
    
    if(++count % batchSize == 0) {
        ps.executeBatch();
    }
}
ps.executeBatch(); // insert remaining records
ps.close();
connection.close();

This would be the idea solution. This avoids SQL Injection and also takes care of out of memory issue. Check how we have incremented a counter count and once it reaches batchSize which is 1000, we call executeBatch().

Hope this helps.

International Bibliography of Periodical Liter...

There are major differences between iBatis and Hibernate but both the solutions work well, given their specific domain. Personally I would suggest you should use iBATIS if:

  • You want to create your own SQL‘s and are willing to maintain them.
  • your environment is driven by relational data model.
  • you have to work existing and complex schema’s.

And simply use Hibernate if:

  • Your environment is driven by object model and wants generates SQL automatically.

To count there are few differences:

  • iBATIS is:
    • Simpler
    • Faster development time
    • Flixable
    • Much smaller in package size
  • Hibernate:
    • Generates SQL for you which means you don’t spend time on SQL
    • Provides much more advance cache
    • Highly scalable

Other difference is that iBATIS makes use of SQL which could be database dependent where as Hibernate makes use of HQL which is relatively independent of databases and it is easier to change db in Hibernate.

Hibernate maps your Java POJO objects to the Database tables where as iBatis maps the ResultSet from JDBC API to your POJO Objets.

If you are using stored procedures, well you can do it in Hibernate but it is little difficult in comparision of iBATIS. As an alternative solution iBATIS maps results sets to objects, so no need to care about table structures. This works very well for stored procedures, works very well for reporting applications, etc

Finally, Hibernate and iBATIS both are open source Object Relational Mapping(ORM) tools available in the industry. Use of each of these tools depends on the context you are using them. Hibernate and iBatis both also have good support from SPRING framework so it should not be a problem to chose one of them.

Gyrotwister orange

Last nite i am struck in putting date  in the DB and because the date is in string and in DB its Sql date type then now how i convert the string into java.sql.date formate look below……………..

 

 

Date dateVar;
SimpleDateFormat sdf = new SimpleDateFormat(“yyyy/MM/dd”);

String DOB=”2012/07/18″;

java.util.Date dt = sdf.parse(DOB);
dateVar = new java.sql.Date(dt.getTime());

 

 

May - Oct 2006 Calendar

 

 

Finding the difference between two dates isn’t as straightforward as subtracting the two dates and dividing the result by /*(24 * 60 * 60 * 1000). Infact, its erroneous!

Going the ‘milliseconds way’ will lead to rounding off errors and they become most evident once you have a little thing like “Daylight Savings Time” come into the picture.

The Correct Way:

 

/** Using Calendar – THE CORRECT WAY**/
//assert: startDate must be before endDate
public static long daysBetween(Calendar startDate, Calendar endDate) {
Calendar date = (Calendar) startDate.clone();
long daysBetween = 0;
while (date.before(endDate)) {
date.add(Calendar.DAY_OF_MONTH, 1);
daysBetween++;
}
return daysBetween;
}
}

 

 

 

 

 

or more efficiently,   if you’re using the Gregorian Calendar:

 

/** Using Calendar – THE CORRECT (& Faster) WAY**/
/****Needs testing …… Anyone?****/
//assert: startDate must be before endDate
public static long daysBetween(final Calendar startDate, final Calendar endDate) {
int MILLIS_IN_DAY = 1000 * 60 * 60 * 24;
long endInstant = endDate.getTimeInMillis();
int presumedDays = (int) ((endInstant – startDate.getTimeInMillis()) / MILLIS_IN_DAY);
Calendar cursor = (Calendar) startDate.clone();
cursor.add(Calendar.DAY_OF_YEAR, presumedDays);
long instant = cursor.getTimeInMillis();
if (instant == endInstant)
return presumedDays;
final int step = instant < endInstant ? 1 : -1;
do {
cursor.add(Calendar.DAY_OF_MONTH, step);
presumedDays += step;
} while (cursor.getTimeInMillis() != endInstant);
return presumedDays;
}

 
Lets take, for example, the difference between the two dates 03/24/2007 and 03/25/2007 should be 1 day;
However, using the millisecond route, you’ll get 0 days,

 

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;

public class DateTest {

public class DateTest {

static SimpleDateFormat sdf = new SimpleDateFormat(“dd-MMM-yyyy”);

public static void main(String[] args) {

TimeZone.setDefault(TimeZone.getTimeZone(“Europe/London”));

//diff between these 2 dates should be 1
Date d1 = new Date(“01/01/2007 12:00:00”);
Date d2 = new Date(“01/02/2007 12:00:00”);

//diff between these 2 dates should be 1
Date d3 = new Date(“03/24/2007 12:00:00”);
Date d4 = new Date(“03/25/2007 12:00:00”);

Calendar cal1 = Calendar.getInstance();cal1.setTime(d1);
Calendar cal2 = Calendar.getInstance();cal2.setTime(d2);
Calendar cal3 = Calendar.getInstance();cal3.setTime(d3);
Calendar cal4 = Calendar.getInstance();cal4.setTime(d4);

printOutput(“Manual   “, d1, d2, calculateDays(d1, d2));
printOutput(“Calendar “, d1, d2, daysBetween(cal1, cal2));
System.out.println(“—“);
printOutput(“Manual   “, d3, d4, calculateDays(d3, d4));
printOutput(“Calendar “, d3, d4, daysBetween(cal3, cal4));
}

private static void printOutput(String type, Date d1, Date d2, long result) {
System.out.println(type+ “- Days between: ” + sdf.format(d1)
+ ” and ” + sdf.format(d2) + ” is: ” + result);
}

/** Manual Method – YIELDS INCORRECT RESULTS – DO NOT USE**/
/* This method is used to find the no of days between the given dates */
public static long calculateDays(Date dateEarly, Date dateLater) {
return (dateLater.getTime() – dateEarly.getTime()) / (24 * 60 * 60 * 1000);
}

/** Using Calendar – THE CORRECT WAY**/
public static long daysBetween(Calendar startDate, Calendar endDate) {
Calendar date = (Calendar) startDate.clone();
long daysBetween = 0;
while (date.before(endDate)) {
date.add(Calendar.DAY_OF_MONTH, 1);
daysBetween++;
}
return daysBetween;
}
}

 

 

 

OUTPUT:
Manual – Days between: 01-Jan-2007 and 02-Jan-2007 is: 1
Calendar – Days between: 01-Jan-2007 and 02-Jan-2007 is: 1

Manual – Days between: 24-Mar-2007 and 25-Mar-2007 is: 0
Calendar – Days between: 24-Mar-2007 and 25-Mar-2007 is: 1

 

 

 

The Microsoft Visual Studio .NET logo.

Let us see how can we create and call Stored procedures, in a .NET Environment, i.e Visual Studio.We use .NET 2.0 and Visual Studio 2005 for all the examples.fs

Writing stored procedures has never been easy as Microsoft has almost integrated SQL Server with Visual Studio 2005. In the past most of the developers has wondered can’t we have a good editor for creating stored procedures. One of the main advantage of creating procedures in Visual Studio is it creates the basic stub for you and further more, it has inbuilt syntax checking which makes the job easier for us.

In order to create a stored procedure from Visual Studio, first you need to create a data connection from the Server Explorer and follow the below steps.

Step 1: Open Visual Studio 2005.

Step 2: Create a VB.NET / C# Windows / Web Application  Project.

Step 3: Open the Server Explorer by Selecting View -> Server Explorer.

stored_procedures_VB.NET

Step 4: Create a Data Connection to your server you can do this by Right Clicking on the Data Connection Tree and Selecting “Add New Connection”.

Step 5: It will Prompt for the Provider Type you can select .NET SQL Server Provider as it gives more performance.

Step 6: After giving all the credentials once the connection is active expand the database that you are having.

Step 7: Expand the Stored Procedure Tree.

Step 8: To Create a New Procedure Right Click and Select “Add New Procedure”.

Step 9: The IDE will give you a Stub where you can replace the Name of the Procedure and Arguments.

Those who are familiar with Visual Studio IDE would love to create procedures here rather then doing it in Query Analyzer or in SQL Enterprise Manager, though it doesn’t provide any fancy auto complete drop downs its still the best I believe to create stored procedures.

TIP: The Maximum number of parameters in a stored procedure is 2100.

Calling Stored Procedure

Hope everyone have used SQLCommand / OLEDB Command objects in .NET. Here we can call stored procedures in two different forms, one without using parameter objects which is not recommended for conventional development environments, the other one is the familiar model of using Parameters.

In the first method you can call the procedure using Exec command followed by the procedure name and the list of parameters, which doesn’t need any parameters.

Example:

Dim SQLCon As New SqlClient.SqlConnection
SQLCon.ConnectionString = “Data Source=Server;User ID=User;Password=Password;”
SQLCon.Open()

Calling Stored Procedures with Exec command

SQLCmd.CommandText = “Exec SelectRecords ‘Test’, ‘Test’, ‘Test'”
SQLCmd.Connection = SQLCon ‘Active Connection

The second most conventional method of calling stored procedures is to use the parameter objects and get the return values using them. In this method we need to set the “SQLCommandType” to “StoredProcedure” remember you need to set this explicitly as the the default type for SQLCommand is SQLQuery”.

Here is an example to call a simple stored procedure.

Example – I (A Stored Procedure Returns Single Value)

In order to get XML Results from the Stored Procedure you need to first ensure that your stored procedure is returning a valid XML. This can be achieved using FOR XML [AUTO | RAW | EXPLICIT] clause in the select statements. You can format XML using EXPLICIT Keyword, you need to alter your Query accordingly

‘Set up Connection object and Connection String for a SQL Client
Dim SQLCon As New SqlClient.SqlConnection
SQLCon.ConnectionString = “Data Source=Server;User ID=User;Password=Password;”
SQLCon.Open()SQLCmd.CommandText = “SelectRecords” ‘ Stored Procedure to Call
SQLCmd.CommandType = CommandType.StoredProcedure ‘Setup Command Type
SQLCmd.Connection = SQLCon ‘Active Connection

The procedure can be called by adding Parameters in at least two different methods, the simplest way to add parameters and respective values is using

SQLCmd.Parameters.AddWithValue(“S_Mobile”, “Test”)
SQLCmd.Parameters.AddWithValue(“S_Mesg”, “Test”)
SQLCmd.Parameters.AddWithValue(“LastMsgID”, “”)

In this above method, you doesn’t necessarily know the actually data type that you had in your procedure and all parameters are validated according to the type declared in your procedure but only thing is all the validations will occur in SQL and not in your client code.

We still need to declare the last parameter as Output and we need to do that explicitly as the default type is Input. So here we are going to declare the last parameter as Output by

SQLCmd.Parameters(“LastMsgID”).Direction = ParameterDirection.Outputfs

If you want to declare parameters properly then you need to use the below method to add all the parameters with its data type, direction. Also if you are using stored procedures to update all the rows in a dataset then you need to declare parameters in the below fashion and give SouceColumn value as the Column name in the DataTable.

SQLCmd.Parameters.Add(New SqlClient.SqlParameter(“S_Mobile”, SqlDbType.VarChar, 10, ParameterDirection.Input, False, 30, 0, “”, DataRowVersion.Current, “91000000000”))SQLCmd.Parameters.Add(New SqlClient.SqlParameter(“S_Mesg”, SqlDbType.VarChar, 160, ParameterDirection.Input, False, 30, 0, “”, DataRowVersion.Current, “Calling Stored Procedures from VB.NET”))

SQLCmd.Parameters.Add(New SqlClient.SqlParameter(“LastMsgID”, SqlDbType.BigInt, 5, ParameterDirection.Output, False, 5, 0, “”, DataRowVersion.Current, 0))

‘ The Above Procedure has two input parameters and one output parameter you can notice the same in the “Parameter Direction”
SQLCmd.ExecuteNonQuery() ‘We are executing the procedure here by calling Execute Non Query.

MsgBox(SQLCmd.Parameters(“LastMsgID”).Value) ‘You can have the returned value from the stored procedure from this statement. Its all similar to ASP / VB as the only difference is the program structure.

Example – II (Stored Procedure to get Table Result Set)

In order to get the result sets from the stored procedure, the best way is to use a DataReader to get the results. In this example we are getting the results from the Stored Procedure and filling the same in a DataTable.

Here we need to additionally declare a SQLDataReader and DataTable

Dim SQLDBDataReader As SqlClient.SqlDataReader
Dim SQLDataTable As New DataTableSQLCmd.CommandText = “GetAuthors”
SQLCmd.CommandType = CommandType.StoredProcedure
SQLCmd.Connection = SQLCon
SQLCmd.Parameters.Add(New SqlClient.SqlParameter(“AuthorName”, SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, “”, DataRowVersion.Current, “Y%”)) SQLDBDataReader = SQLCmd.ExecuteReader() SQLDataTable.Columns.Add(“AuthorName”, GetType(Int32), “”) SQLDataTable.Columns.Add(“AuthorLocation”, GetType(String), “”)

Dim FieldValues(1) As Object ‘A Temporary Variable to retrieve all columns in a row and fill them in Object array

While (SQLDBDataReader.Read)
SQLDBDataReader.GetValues(FieldValues)
SQLDataTable.Rows.Add(FieldValues)

End While

Example – III (Calling Simple Stored Procedure to get XML Result Set)

In order to get XML Results from the Stored Procedure you need to first ensure that your stored procedure is returning a valid XML. This can be achieved using FOR XML [AUTO | RAW | EXPLICIT] clause in the select statements. You can format XML using EXPLICIT Keyword, you need to alter your Query accordingly.

CREATE PROCEDURE GetRecordsXML (@AuthorName varchar(100))
ASSelect Author_ID, Author_Name, Author_Location Where Author_Name LIKE  @AuthorName from Authors FOR XML AUTO

RETURN

When you use the above procedure you can get XML Results with TableName as Element and Fields as Attributes

Dim SQLXMLReader As Xml.XmlReader

SQLCmd.CommandText = “GetAuthorsXML”
SQLCmd.CommandType = CommandType.StoredProcedure
SQLCmd.Connection = SQLCon
SQLCmd.Parameters.Add(New SqlClient.SqlParameter(“AuthorName”, SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, “”, DataRowVersion.Current, “Y%”))
SQLDBDataReader = SQLCmd.ExecuteReader()

SQLXMLReader = SQLCmd.ExecuteXmlReader()
While (SQLXMLReader.Read)
MsgBox(SQLXMLReader.ReadOuterXml)
End While

You can further process this XML or write XSL to display results in a formatted manner. But in order to get formatted XML Results, we need to use EXPLICIT case which we can see in our next article on SQL Queries & XML.

Building a datasource for a web of data

JDBC Template Batch update example, In the tutorial we have discussed about  batchUpdate()  method of class JdbcTemplate in Spring framework. The Batch update methods are used to executes multiple SQL updates query on a single JDBC Statement. The example given below consists of the code to delete and update data of the table Simultaneously.

context.xml


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:lang="http://www.springframework.org/schema/lang"
       xsi:schemaLocation="http://www.springframework.org/schema/beans 
       http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
       http://www.springframework.org/schema/util 
       http://www.springframework.org/schema/util/spring-util-2.0.xsd
       http://www.springframework.org/schema/tx 
       http://www.springframework.org/schema/tx/spring-tx-2.0.xsd
       http://www.springframework.org/schema/aop 
       http://www.springframework.org/schema/aop/spring-aop-2.0.xsd
       http://www.springframework.org/schema/lang 
       http://www.springframework.org/schema/lang/spring-lang-2.0.xsd">
          <bean id="dataSource" 
                                 destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://192.168.10.75:3306/komal"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>
       <bean id="lobHandler">
        <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/>
    </bean>
        <bean id="nativeJdbcExtractor" 
   />
       <bean id="jdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

Main.java

ApplicationContext ac = new ClassPathXmlApplicationContext(“context.xml”, Main.class):-ApplicationContext is the interface that is used to  provide Bean factory methods for accessing application components. Here we Creates an instance of this interface to access context.xml and Main.java.

DataSource source = (DataSource) ac.getBean(“dataSource”):-Data source is an Interface which provides a way for connecting to the physical data source. Here we created datasource for making connection to our xml document in which we have declared the bean.

jt.batchUpdate(new String[]{“update employee set departement = ‘Finance#'”, “delete from employee where EmployeeId =31” }):-With the use of this method we are executing two SQLquery simultaneously. This method executes multiple Sql updates on a single JDBC statement.

import javax.sql.DataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
class Main {
    public static void main(String args[]) {
        try {
            ApplicationContext ac = new
                    ClassPathXmlApplicationContext("context.xml", Main.class);
            DataSource source = (DataSource) ac.getBean("dataSource");
            JdbcTemplate jt = new JdbcTemplate(source);
            jt.batchUpdate(new String[]{"update employee set departement = 'Finance#'",
                        "delete from employee where EmployeeId =31"
                    });
            System.out.println("Data updated successfully");
        } catch (Exception e) {
            e.printStackTrace();
                    }
    }
}

Output of the program

Data updated successfully
BUILD SUCCESSFUL (total time: 2 seconds)
  Table before updation    Table after updation

Download source code