Java Database Application with Apache Velocity

Java Database Application with Apache Velocity

Introduction to Apache Velocity

Please Refer the article Spring with Apache Velocity

also read:

• Java Tutorials
• Java EE Tutorials
• Design Patterns Tutorials
• Java File IO Tutorials

Introduction to JDBC

JDBC stands for Java DataBase Connectivity. The JDBC API helps us to develop Java applications with data base connection. We can use any database like Oracle,MySql,MSAcess etc..

For developing JDBC Applications we have to depend many APIs from the package java.sql. There are certain steps to do this.

The steps are:

1. Loads a DriverClass.forName(“sun.jdbc.odbc.JdbcOdbcDriver”)
2. Connects to the databaseConnection con = DriverManager.getConnection(“jdbc:odbc:dsn”, “username”, “password”)
3. Executes SQLStatement statmt = con.createStatement( );

   ResultSet rs = statmt.executeQuery(“SELECT * FROM ProductTable”);

4. Retrieves the resultswhile(rs.next( )) {

   System.out.println(rs.getString(“pid”))

Example :JDBC Application with Apache Velocity

The application is using a Java file to retrieve the data from the database and the output is rendering with the help of a Velocity page.The java file uses an Arraylist to store the data from the resultset and the data is then add to the Velocity Context.Later the data is retrieving from the VelocityContext in the file Extract .vm page. The Database used in this example is Apache Derby.The below list contains all the related files and library details.

The files are :

JDBCVelocityDemo.java
	Extract.vm(Velocity file)
	Derby database table Product with the fields product_ID,purchase_cost,..
	Libraries:   velocity-tools-view-1.4.jar ,velocity-dep-1.5.jar, velocity-tools-view-1.4.jar,derby.jar
	Editor support :Netbeans 6.5 or above  or any other java editor 

JDBCVelocityDemo.java

/**
	 *
	 * @author Rasmi_G
	*/
	import java.io.StringWriter;
	import java.io.Writer;
	import java.sql.*;
	import java.util.*;
	import org.apache.velocity.*;
	import org.apache.velocity.app.Velocity;
	import org.apache.velocity.app.VelocityEngine;
	public class JDBCVelocityDemo {
		static HashMap map;
		public static void main(String[] args) throws Exception {
			VelocityEngine ve = new VelocityEngine();
			ve.init();
			try {
				//Derby database url
				String url=   "jdbc:derby://localhost:1527/sample";
				//Database connection details with the username and password
				Connection conn = DriverManager.getConnection(url,"app","app");
				Statement stmt = conn.createStatement();
				ResultSet rs;
				//the product is table in the Derby Sample database
				rs = stmt.executeQuery("SELECT * FROM PRODUCT");
				String id ;
				String cost1;
				ArrayList list = new ArrayList();
				//Retrieving the data and storing into a List in the form of map
				while ( rs.next() ) {
					map = new HashMap();
					id = rs.getString("product_ID");
					cost1=rs.getString("PURCHASE_COST");
					map.put("Pid", id);
					map.put("cost",cost1); }
				list.add(map);
				int count=list.size();
				ArrayList l=new ArrayList();
				l.add(list.get(count-1));
				//For Loading the velocity page
				Properties p = new Properties();
				//loading the template engine path
				p.setProperty("file.resource.loader.path", "D:/Learning Materials/2010-2011/AllDemoWorkFolder/Test11/src/java");
				//Storing  the data in the Velocity Context
				VelocityContext context = new VelocityContext();
				context.put("prdList", l);
				//Initialize velocity run time engine through method  init()
				Velocity.init(p);
				Template t = Velocity.getTemplate("Extract.vm");
				StringWriter writer = new StringWriter();
				//merge() is a  method of the Template class.
				//The usage of merge() is  for merging  the VelocityContext class object to produce the output.
				t.merge(context, writer);
				System.out.println(writer.toString());
			}
			conn.close();
			catch (Exception e) {System.err.println("Exception");
				System.err.println(e.getMessage());
			}
		}
	}

Extract.vm

The Details Are:
	#foreach( $prd in $prdList )
		Product id: $prd.Pid
		Purchase Cost: $prd.cost
	#end

After developing this application

1. Start the Derby Database Server
2. Compile and execute the JDBCVelocityDemo.java
3. The contents in the Extrac.vm will display as the output.

The output :

The Details Are:

	Product id: 980001
	Purchase Cost: 1095.00

	The Details Are:

	Product id: 980005
	Purchase Cost: 11500.99

	The Details Are:

	Product id: 980025
	Purchase Cost: 2095.99

Conclusion

The article gives an introduction to JDBC and Apache Velocity .It contains a JDBC example which uses Apache velocity template engine for displaying the output page. Also this article gives an idea about how to develop this example and required libraries needed for this application.