The 10 Most Popular DB Engines (SQL and NoSQL) in 2015

Source: The 10 Most Popular DB Engines (SQL and NoSQL) in 2015

Advertisements

Using SpringBoot , SpringData and MongoDB

Hello everybody, its a long gap that i have not written a post in my blog. This post could be my last post in 2015. today we are going to see how to integrate Spring’s one of the best feature Spring Boot with Spring data and MongoDB.

Spring Boot:(courtesy Spring.io) Spring Boot makes it easy to create stand-alone, production-grade Spring based Applications that you can “just run”. Most Spring Boot applications need very little Spring configuration.


Features:

  • Create stand-alone Spring applications
  • Embed Tomcat, Jetty
  • Automatically configure Spring whenever possible
  • Absolutely no code generation and no requirement for XML configuration

Now let us create a project that can integrate the above mentioned technology stack.
spring-boot-proj

Step 1: create the mongo-db.properties file in src/main/resources folder

MONGO_DB_HOST=127.0.0.1
MONGO_DB_PORT=27017
DB=school

Step 2: create the application configuration java class which is responsible for creating the MongoTemplate object. below is AppConfig.java

/**
 * 
 */
package com.spark.spring.practice.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.data.mongodb.MongoDbFactory;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.SimpleMongoDbFactory;
import org.springframework.data.mongodb.core.convert.DefaultMongoTypeMapper;
import org.springframework.data.mongodb.core.convert.MappingMongoConverter;
import org.springframework.data.mongodb.core.mapping.MongoMappingContext;

import com.mongodb.Mongo;
import com.mongodb.MongoClient;

/**
 * @author Sony
 *
 */
@Configuration
@PropertySource(value = "classpath:mongo-config.properties")
public class AppConfig {

	@Value("${MONGO_DB_HOST}")
	private String MONGO_DB_HOST;

	@Value("${MONGO_DB_PORT}")
	private int MONGO_DB_PORT;

	@Value("${DB}")
	private String DB;

	protected String getDatabaseName() {
		return DB;
	}

	@Bean
	public Mongo getMongo() throws Exception {
		return new MongoClient(MONGO_DB_HOST, MONGO_DB_PORT);
	}

	@Bean
	public MongoDbFactory mongoDbFactory() throws Exception {
		return new SimpleMongoDbFactory(getMongo(), getDatabaseName());
	}

	@Bean
	public MongoTemplate mongoTemplate() throws Exception {

		MongoTemplate mongoTemplate = new MongoTemplate(mongoDbFactory());

		return mongoTemplate;

	}
}

Step 3: create the pojo classes which are mapped as documents in mongodb. below are the Student.java and Score.java respectively

/**
 * 
 */
package com.spark.spring.practice.beans;

import java.util.List;

import org.springframework.data.annotation.Id;
import org.springframework.data.mongodb.core.mapping.Document;
import org.springframework.data.mongodb.core.mapping.Field;

/**
 * @author Sony
 *
 */
@Document(collection = "students")
public class Student {

	@Id
	private int _id;
	@Field(value="name")
	private String name;
	@Field(value="scores")
	List<Score> scores;

	public int get_id() {
		return _id;
	}

	public void set_id(int _id) {
		this._id = _id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public List<Score> getScores() {
		return scores;
	}

	public void setScores(List<Score> scores) {
		this.scores = scores;
	}

}

/**
 * 
 */
package com.spark.spring.practice.beans;

/**
 * @author Sony
 *
 */
public class Score {

	private String type;
	private double score;

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public double getScore() {
		return score;
	}

	public void setScore(double score) {
		this.score = score;
	}

}

Step 4: create the controller class which can handle the request coming from client. in this example the controller is called GlobalController.java. The controller class is autowired with MongoTemplate Object and StudentRepository, so let us create StudentRepository so that it will get injected into the controller, when the class loads to spring container. MongoTemplate is readily configured in the AppConfig.java file.

/**
 * 
 */
package com.spark.spring.practice.controller;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.spark.spring.practice.beans.Student;
import com.spark.spring.practice.repositories.StudentRepository;

/**
 * @author Sony
 *
 */
@RestController
@RequestMapping(value = "/rest/v1/students")
public class GlobalController {
	
	Logger logger = LoggerFactory.getLogger(getClass());
	
	@Autowired
	private MongoTemplate mongoTemplate;
	
	@Autowired
	private StudentRepository studentRepository;

	@RequestMapping(value = "/findall", method = RequestMethod.GET)
	public List<Student> getAllStudents() {
		List<Student> students = studentRepository.findAll();
		return students;
	}
}

Step 5: create the repository interface which will extend the MongRepository<T,ID> interface from spring library.

/**
 * 
 */
package com.spark.spring.practice.repositories;

import org.springframework.data.mongodb.repository.MongoRepository;

import com.spark.spring.practice.beans.Student;

/**
 * @author Sony
 *
 */
public interface StudentRepository extends MongoRepository<Student, Integer> {

}

Step 6: create the main class which will act as booting mechanism and will start the project with the help of @SpringBootApplication & @EnableMongoRepositories annotations

/**
 * 
 */
package com.spark.spring.practice.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.data.mongodb.repository.config.EnableMongoRepositories;

/**
 * @author Sony
 *
 */
@SpringBootApplication
@ComponentScan(basePackages={"com.spark.spring.practice"})
@EnableMongoRepositories(basePackages={"com.spark.spring.practice"})
public class SpringBootMain {

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		SpringApplication.run(SpringBootMain.class, args);
	}

}

that’s all guys we are done with coding part. Its time for us to run the project. once you ran the project with out errors then below is the kind of output that can be seen on console.
Spring-boot-console

now open the REST-client add-on either in chrome or firefox and test your services.
Happy Spring-booting 🙂

2014 in review..Thanks to every one for making my blog successful.

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 28,000 times in 2014. If it were a concert at Sydney Opera House, it would take about 10 sold-out performances for that many people to see it.

Click here to see the complete report.

Pushing Data to Apache Solr Using SpringData

From past few days i was working on Apache Solr Technologies to index data in one of my projects. Today i wanted to elaborate on this concept, this post assumes that you already have Apache Solr installed on your system, if not please install the latest( > 4.1) version from here. Read the instruction from solr site make the Solr server up and running. Once this Solr is up and running we can go to Admin dashboard by hitting the following url http://localhost:8983/solr.

Now its time for us to dive into Spring coding inorder to push data to solr server. this project is written on top of Spring4, SpringData, Solr4, Maven and Eclipse Luna.

first lets create a maven project and put the below code in your pom.xml file and the project structure looks as below.

SolrProjectStructure

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.spark.solr</groupId>
	<artifactId>SolrSpringIntegration</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>
	<name>SolrSpringIntegration</name>
	<description>SolrSpringIntegration</description>
	<properties>
		<jdk.version>1.6</jdk.version>
		<spring.version>4.0.4.RELEASE</spring.version>
		<spring.security.version>3.2.3.RELEASE</spring.security.version>
		<jstl.version>1.2</jstl.version>
		<spring-data-solr.verion>1.3.0.RELEASE</spring-data-solr.verion>
	</properties>

	<dependencies>

		<!-- Spring dependencies -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-oxm</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<!-- SOLR -->
		<dependency>
			<groupId>org.springframework.data</groupId>
			<artifactId>spring-data-solr</artifactId>
			<version>${spring-data-solr.verion}</version>
		</dependency>

		<!-- jstl and servlet for jsp page -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
			<version>3.0.1</version>
		</dependency>
		<dependency>
			<groupId>jstl</groupId>
			<artifactId>jstl</artifactId>
			<version>${jstl.version}</version>
		</dependency>

		<!-- json lib -->
		<dependency>
			<groupId>net.sf.json-lib</groupId>
			<artifactId>json-lib</artifactId>
			<version>2.4</version>
			<classifier>jdk15</classifier>
		</dependency>

		<!-- mysql dependency -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.9</version>
		</dependency>

		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>1.7.7</version>
		</dependency>


	</dependencies>
	<build>
		<finalName>spark-solr</finalName>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.1</version>
				<configuration>
					<source>1.7</source>
					<target>1.7</target>
					<encoding>UTF-8</encoding>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

Now its time for to start with the configurations and let me remind since we are using Spring for as the core framework i will make use of Java configuration capability of Spring. so all my configurations are just java codes with out xml files. let us start with database config first
DBConfig.java

/**
 * 
 */
package com.spark.solr.web.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

/**
 * @author Sony
 *
 */
@Configuration
public class DBConfig {

	@Value("${db.driverClass}")
	String driverClassName;
	@Value("${db.url}")
	String url;
	@Value("${db.username}")
	String username;
	@Value("${db.password}")
	String password;
	
	@Bean
	public DataSource getDataSource(){
		DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
		driverManagerDataSource.setDriverClassName(driverClassName);
		driverManagerDataSource.setUrl(url);
		driverManagerDataSource.setUsername(username);
		driverManagerDataSource.setPassword(password);
		
		return driverManagerDataSource;
	}
	
	@Bean
	public JdbcTemplate getJdbcTemplate(DataSource dataSource){
		return new JdbcTemplate(dataSource);
	}
}

The above code is responsible for injecting the database properties and creating the datasource and finally injecting datasource into jdbc template class.
Now let us write the solr configuration integrated to spring.
SolrConfiguration.java

/**
 * 
 */
package com.spark.solr.web.config;

import org.apache.solr.client.solrj.SolrServer;
import org.apache.solr.client.solrj.impl.HttpSolrServer;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.solr.core.SolrTemplate;
import org.springframework.web.client.RestTemplate;

/**
 * @author Sony
 *
 */
@Configuration
public class SolrConfiguration {

	@Value("${solr.server.host}")
	private String solrUrl;
	
	@Bean
	public SolrTemplate getSolrTemplate(SolrServer server){
		SolrTemplate solrTemplate = new SolrTemplate(server);
		return solrTemplate;
	}
	
	@Bean
	public SolrServer getSolrServer(){
		System.out.println("--------->"+solrUrl);
		SolrServer solrServer = new HttpSolrServer(solrUrl);
		return solrServer;
	}
	
}

Above class is responsible for creating the Solr Server object and injecting that to SolrTemplate class, using which we can perform operation over solr server. now to make our project web compatible lets code WebApplication initializer as below which acts similar to web.xml, this works only with servlet 3.0 container (tomcat7 and above)

/**
 * 
 */
package com.spark.solr.web.config;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletRegistration.Dynamic;

import org.springframework.web.WebApplicationInitializer;
import org.springframework.web.context.support.AnnotationConfigWebApplicationContext;
import org.springframework.web.servlet.DispatcherServlet;

/**
 * @author Sony
 *
 */
public class WebAppInitializer implements WebApplicationInitializer{

	public void onStartup(ServletContext servletContext)
			throws ServletException {
		
		AnnotationConfigWebApplicationContext annotationConfigWebApplicationContext = new AnnotationConfigWebApplicationContext();
		annotationConfigWebApplicationContext.register(WebApplicationConfig.class);
		annotationConfigWebApplicationContext.setServletContext(servletContext);
		
		Dynamic dynamic = servletContext.addServlet("dispatcher", new DispatcherServlet(annotationConfigWebApplicationContext));
		dynamic.addMapping("/solr/*");
		dynamic.setLoadOnStartup(1);
		
		
	}

}

Below is the global config file to read the property files and to load other config files also the below code is responsible for detecting different annotations.

/** 
 * 
 */
package com.spark.solr.web.config;

import org.springframework.beans.factory.config.PropertyPlaceholderConfigurer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;

/**
 * @author Sony
 *
 */
@Configuration
@EnableWebMvc
@ComponentScan(basePackages = "com.spark.solr")
@Import({ SolrConfiguration.class, DBConfig.class })
public class WebApplicationConfig extends WebMvcConfigurerAdapter {

	@Bean
	public PropertyPlaceholderConfigurer getPropertyPlaceholderConfigurer() {
		PropertyPlaceholderConfigurer placeholderConfigurer = new PropertyPlaceholderConfigurer();
		placeholderConfigurer.setLocation(new ClassPathResource(
				"application.properties"));
		placeholderConfigurer.setIgnoreUnresolvablePlaceholders(true);
		return placeholderConfigurer;
	}

}

Now the code concept of spring solr come here, Spring Data Solr, part of the larger Spring Data family, provides easy configuration and access to Apache Solr Search Server from Spring applications. It offers both low-level and high-level abstractions for interacting with the store.

Derived queries and annotations for Solr specific functionallity allow easy integration into existing applications. So spring data has provided Repositories to deal with different operations of Spring.

/**
 * 
 */
package com.spark.solr.web.repository;

import org.springframework.data.solr.repository.SolrCrudRepository;

import com.spark.solr.web.model.Entity;

/**
 * @author Sony
 *
 */
public interface EntityRepository extends SolrCrudRepository<Entity, Integer>{

}

Now let us start with our service layer and its implementation followed by DAO and its Implementation classes.
EntityService.java

/**
 * 
 */
package com.spark.solr.web.service;

import java.util.List;

/**
 * @author Sony
 *
 */
public interface EntityService {
	public void saveDocument();
	public void saveDocument(Object object);
	public void saveDocument(List<Object> objects);
}

EntityServiceImpl.java

/**
 * 
 */
package com.spark.solr.web.service.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.solr.core.SolrTemplate;
import org.springframework.data.solr.repository.support.SolrRepositoryFactory;
import org.springframework.stereotype.Service;

import com.spark.solr.web.dao.EntityDAO;
import com.spark.solr.web.model.Entity;
import com.spark.solr.web.repository.EntityRepository;
import com.spark.solr.web.service.EntityService;

/**
 * @author Sony
 *
 */
@Service(value = "entityServiceImpl")
public class EntityServiceImpl implements EntityService {

	@Autowired
	SolrTemplate solrTemplate;

	@Autowired
	EntityDAO entityDao;

	EntityRepository entityRepository;
	
	/* (non-Javadoc)
	 * @see com.spark.solr.web.service.EntityService#saveDocument(java.lang.Object)
	 */
	@Override
	public void saveDocument(Object object) {
		// TODO Auto-generated method stub

	}

	/* (non-Javadoc)
	 * @see com.spark.solr.web.service.EntityService#saveDocument(java.util.List)
	 */
	public void saveDocument(List<Object> objects) {
		// TODO Auto-generated method stub
	}
	
	/* (non-Javadoc)
	 * @see com.spark.solr.web.service.EntityService#saveDocument()
	 */
	@Override
	public void saveDocument() {
		getLogger().info("------------- Before fetching the details from database-----------");
		List<Map<String, Object>> values = entityDao.getDataFromDB();
		List<Entity> entities = new ArrayList<Entity>();
		
		for (Map<String, Object> map : values) {
			
			Entity entity = new Entity();
			
			entity.setEmployeeId(Integer.valueOf(map.get("EmployeeID").toString()));
			entity.setLastName(map.get("LastName").toString());
			entity.setFirstName(map.get("FirstName").toString());
			entity.setTitle(map.get("Title").toString());
			entity.setTitleOfCourtesy(map.get("TitleOfCourtesy").toString());
			entities.add(entity);
		}
		getLogger().info("------------- Before pushing documents to Solr server -----------");
		for (Entity entity : entities) {
			getEntityRepository().save(entity);
		}
		getLogger().info("------------- documents push to solr server done ! -----------");
	}
	
	/**
	 * @return
	 */
	public EntityRepository getEntityRepository(){
		return new SolrRepositoryFactory(solrTemplate.getSolrServer()).getRepository(EntityRepository.class);
	}

	public Logger getLogger(){
		return LoggerFactory.getLogger(EntityServiceImpl.class);
	}

}

EntityDAO.java

package com.spark.solr.web.dao;

import java.util.List;
import java.util.Map;


public interface EntityDAO {

	public List<Map<String, Object>> getDataFromDB();
}

EntityDAOImpl.java

/**
 * 
 */
package com.spark.solr.web.dao.impl;

import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.spark.solr.web.dao.EntityDAO;
import com.spark.solr.web.db.DBQueries;
import com.spark.solr.web.service.impl.EntityServiceImpl;

/**
 * @author Sony
 *
 */
@Repository(value="entityDao")
public class EntityDAOImpl implements EntityDAO {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	/* (non-Javadoc)
	 * @see com.spark.solr.web.dao.EntityDAO#getDataFromDB()
	 */
	@Override
	public List<Map<String, Object>> getDataFromDB() {
		getLogger().info("----------- Querying database for the records -------------");
		getLogger().info("----------- sql query : "+DBQueries.GET_ALL_EMPLOYEES);
		List<Map<String, Object>> values = jdbcTemplate.queryForList(DBQueries.GET_ALL_EMPLOYEES);
		return values;
	}
	
	public Logger getLogger(){
		return LoggerFactory.getLogger(EntityDAOImpl.class);
	}

}

Now for making a better abstraction between the dao layer and the quires that are used in that layer i implemented a separate utility class as below.

/**
 * 
 */
package com.spark.solr.web.db;

/**
 * @author Sony
 *
 */
public class DBQueries {

	public static final String GET_ALL_EMPLOYEES = "select * from employee";
	
}

Finally our entity model looks as below.

/**
 * 
 */
package com.spark.solr.web.model;

import org.apache.solr.client.solrj.beans.Field;
import org.springframework.data.annotation.Id;

/**
 * @author Sony
 *
 */
public class Entity {

	@Id
	@Field(value="id")
	private int employeeId;
	@Field(value="lastName")
	private String lastName;
	@Field(value="firstName")
	private String firstName;
	@Field(value="title")
	private String title;
	@Field(value="titleOfCourtesy")
	private String titleOfCourtesy;

	/**
	 * @return the employeeId
	 */
	public int getEmployeeId() {
		return employeeId;
	}

	/**
	 * @param employeeId
	 *            the employeeId to set
	 */
	public void setEmployeeId(int employeeId) {
		this.employeeId = employeeId;
	}

	/**
	 * @return the lastName
	 */
	public String getLastName() {
		return lastName;
	}

	/**
	 * @param lastName
	 *            the lastName to set
	 */
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	/**
	 * @return the firstName
	 */
	public String getFirstName() {
		return firstName;
	}

	/**
	 * @param firstName
	 *            the firstName to set
	 */
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	/**
	 * @return the title
	 */
	public String getTitle() {
		return title;
	}

	/**
	 * @param title
	 *            the title to set
	 */
	public void setTitle(String title) {
		this.title = title;
	}

	/**
	 * @return the titleOfCourtesy
	 */
	public String getTitleOfCourtesy() {
		return titleOfCourtesy;
	}

	/**
	 * @param titleOfCourtesy
	 *            the titleOfCourtesy to set
	 */
	public void setTitleOfCourtesy(String titleOfCourtesy) {
		this.titleOfCourtesy = titleOfCourtesy;
	}

}

Now let us look at the resource files that i used in this project 1.application.properties and 2.log4j.properties
please put the below files in “scr/main/resources” folder of your maven project.
application.properties

solr.server.host=http://localhost:8983/solr

db.driverClass=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/test
db.username=root
db.password=root

log4j.properties

log4j.rootLogger = INFO, rollingFile

log4j.appender.rollingFile=org.apache.log4j.RollingFileAppender
log4j.appender.rollingFile.File=D:/spring-solr-dev.log
log4j.appender.rollingFile.MaxFileSize=2MB
log4j.appender.rollingFile.MaxBackupIndex=2
log4j.appender.rollingFile.layout = org.apache.log4j.PatternLayout
log4j.appender.rollingFile.layout.ConversionPattern=%p %t %c - %m%n

Note : please find the log file in d drive as i have given in log4j.properties file. please chage the drive location if you need.

Now we have done with the coding part, it is time for deployment, from cmd please navigate to location of project and issue the command as mvn clean install package this should build the project with all the required dependencies and final artifact would be spark-solr.war file.
now deploy this war file in apache tomcat under webapps directory and put the below url in your browser.
http://localhost:8080/spark-solr/solr/controller/load

Once we hit the url the controller calls the service layer and from service layer invoke dao layer to ftch the data from database abd returned back to service layer now in service layer we have the object od Solr repository and using that we push the data to solr.

After tha data is pushed we cah check tha data in solr using the solr admin page. as below
SolrAdmin

using my previous post we can pull the data from solr and show on custom UI.
Happy coding 🙂 happy Solr 🙂

LINQ to SQL in c# .Net

Hello folks, for a change in the week end i started reading .Net, as a programmer i should appreciate both the legendary languages(Java, .Net) some have i was attracted to the beauty of one of the .Net’s feature called LINQ.
so here is a small post where i will demonstrate how to link up our front end with database using LINQ(Language Integrated Query). This is my simple attempt to write a .Net post(apologies if anything is wrong and please give your precious feedback)

prior to create our application let us create the database table as follows

USE [TestDatabase]
GO
/****** Object:  Table [dbo].[Details]    Script Date: 04/27/2014 20:28:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Details](
	[id] [int] NOT NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[Age] [int] NULL,
 CONSTRAINT [PK_Details] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

after creating the database from the visual designer of the VisualStudio please create the following screen.
FrontEnd

inside the “CSharp” class behind this UI double click the submit button it will create the following method

private void SaveDetailsButton_Click(object sender, EventArgs e){
    // code to save the details and retrieve the details using LINQ
}

now its time to create the LINQ class inside the project create a file called “LINQ to SQL” and drag and drop the table in this file and it looks as below.
dbml_file

Once this is done the VisualStudio automatically create the following code


//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:2.0.50727.4984
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace SampleApplication
{
	using System.Data.Linq;
	using System.Data.Linq.Mapping;
	using System.Data;
	using System.Collections.Generic;
	using System.Reflection;
	using System.Linq;
	using System.Linq.Expressions;
	using System.ComponentModel;
	using System;
	
	
	[System.Data.Linq.Mapping.DatabaseAttribute(Name="TestDatabase")]
	public partial class DetailsClassDataContext : System.Data.Linq.DataContext
	{
		
		private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
		
    #region Extensibility Method Definitions
    partial void OnCreated();
    partial void InsertDetail(Detail instance);
    partial void UpdateDetail(Detail instance);
    partial void DeleteDetail(Detail instance);
    #endregion
		
		public DetailsClassDataContext() : 
				base(global::SampleApplication.Properties.Settings.Default.TestDatabaseConnectionString, mappingSource)
		{
			OnCreated();
		}
		
		public DetailsClassDataContext(string connection) : 
				base(connection, mappingSource)
		{
			OnCreated();
		}
		
		public DetailsClassDataContext(System.Data.IDbConnection connection) : 
				base(connection, mappingSource)
		{
			OnCreated();
		}
		
		public DetailsClassDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
				base(connection, mappingSource)
		{
			OnCreated();
		}
		
		public DetailsClassDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
				base(connection, mappingSource)
		{
			OnCreated();
		}
		
		public System.Data.Linq.Table<Detail> Details
		{
			get
			{
				return this.GetTable<Detail>();
			}
		}
	}
	
	[Table(Name="dbo.Details")]
	public partial class Detail : INotifyPropertyChanging, INotifyPropertyChanged
	{
		
		private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
		
		private int _id;
		
		private string _FirstName;
		
		private string _LastName;
		
		private System.Nullable<int> _Age;
		
    #region Extensibility Method Definitions
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void OnidChanging(int value);
    partial void OnidChanged();
    partial void OnFirstNameChanging(string value);
    partial void OnFirstNameChanged();
    partial void OnLastNameChanging(string value);
    partial void OnLastNameChanged();
    partial void OnAgeChanging(System.Nullable<int> value);
    partial void OnAgeChanged();
    #endregion
		
		public Detail()
		{
			OnCreated();
		}
		
		[Column(Storage="_id", DbType="Int NOT NULL", IsPrimaryKey=true)]
		public int id
		{
			get
			{
				return this._id;
			}
			set
			{
				if ((this._id != value))
				{
					this.OnidChanging(value);
					this.SendPropertyChanging();
					this._id = value;
					this.SendPropertyChanged("id");
					this.OnidChanged();
				}
			}
		}
		
		[Column(Storage="_FirstName", DbType="VarChar(50)")]
		public string FirstName
		{
			get
			{
				return this._FirstName;
			}
			set
			{
				if ((this._FirstName != value))
				{
					this.OnFirstNameChanging(value);
					this.SendPropertyChanging();
					this._FirstName = value;
					this.SendPropertyChanged("FirstName");
					this.OnFirstNameChanged();
				}
			}
		}
		
		[Column(Storage="_LastName", DbType="VarChar(50)")]
		public string LastName
		{
			get
			{
				return this._LastName;
			}
			set
			{
				if ((this._LastName != value))
				{
					this.OnLastNameChanging(value);
					this.SendPropertyChanging();
					this._LastName = value;
					this.SendPropertyChanged("LastName");
					this.OnLastNameChanged();
				}
			}
		}
		
		[Column(Storage="_Age", DbType="Int")]
		public System.Nullable<int> Age
		{
			get
			{
				return this._Age;
			}
			set
			{
				if ((this._Age != value))
				{
					this.OnAgeChanging(value);
					this.SendPropertyChanging();
					this._Age = value;
					this.SendPropertyChanged("Age");
					this.OnAgeChanged();
				}
			}
		}
		
		public event PropertyChangingEventHandler PropertyChanging;
		
		public event PropertyChangedEventHandler PropertyChanged;
		
		protected virtual void SendPropertyChanging()
		{
			if ((this.PropertyChanging != null))
			{
				this.PropertyChanging(this, emptyChangingEventArgs);
			}
		}
		
		protected virtual void SendPropertyChanged(String propertyName)
		{
			if ((this.PropertyChanged != null))
			{
				this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
			}
		}
	}
}

now copy and paste the following code snippet into the button handling method as shown below.

 DetailsClassDataContext detailsClassDataContext = new DetailsClassDataContext();

            Detail detail = new Detail();
            detail.id = Int32.Parse(IdtextBox.Text.ToString());
            detail.FirstName = firstNametextBox.Text.ToString();
            detail.LastName = lastNametextBox.Text.ToString();
            detail.Age = Int32.Parse(agetextBox.Text.ToString());
            try
            {
                detailsClassDataContext.Details.InsertOnSubmit(detail);
                detailsClassDataContext.SubmitChanges();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

           
            var data = from a in detailsClassDataContext.Details select a;
            dataGridView1.DataSource = data;

Also in the Form_load method please write the following code as below. this helps to query the database at the application load time and populate the DataGrid.

     private void Form1_Load(object sender, EventArgs e)
        {
            DetailsClassDataContext detailsClassDataContext = new DetailsClassDataContext();
            var data = from a in detailsClassDataContext.Details select a;
            dataGridView1.DataSource = data;
        }

Finally aour application is ready to use and this looks as below.
FinalScreen

Happy Coding in LINQ to SQL with C# 🙂

Querying Entities using JPQL and the Criteria API

When i started using EJB2.0 the only way to query the database is to use EJB QL(EJB Query Language) later on with the introduction of JavaEE 5.0 the EJB QL is Replaced by JPQL (Java Persistence Query Language). Now in today’s world, There are two primary Java EE technologies for querying a database: Java Persistence Query Language (JPQL) and the Criteria API. JPQL is similar in appearance and usage to SQL while the Criteria API provides a more type-safe and object-oriented entity querying capability.JPQL is based on the Hibernate Query Language (HQL) and is a subset of HQL. It is portable and allows queries to be executed against different data stores. This is in contrast to the use of a native query where the query is expressed in the native SQL of the data store.Another older, yet still viable technique is the Java database classes and interfaces found in the java.sql package.

Now i want to share some comparison of JPQL and CriteriaAPI:

JPQL:
String param = "Pav%"; Query query = em.createQuery( "select e from Employee e where e.firstName like :param"); query.setParameter("param", param); List<Employee> employees = query.getResultList(); for (Employee emp : employees){ System.out.println(emp.getFirstName()); }

Equivalent with Criteria API:
String param = "Pav%";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Employee> query = cb.createQuery(Employee.class);

        Root<Employee> emp = query.from(Employee.class);
        query.where(cb.like(emp.<String>get("firstName"),
                            cb.parameter(String.class, "param")));

        TypedQuery<Employee> tq = em.createQuery(query);
        tq.setParameter("param", param);
        List<Employee> employees = tq.getResultList();

        for (Employee employee : employees){
            System.out.println(employee.getFirstName());
        }

Example of criteria API using count:
In JPQL

String param = "2000";

        Query query = em.createQuery(
             "select count(e) from Employee e where e.sal = :empSal");
        query.setParameter("empSal", param);
        Long count1 = (Long)query.getSingleResult();

        System.out.println("count : "+count1);

Equivalent with Criteria API

String param = "2000";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Long> query = cb.createQuery(Long.class);

        Root<Employee> emp = query.from(Employee.class);
        query.select(cb.count(emp));
        query.where(cb.equal(emp.<String>get("sal"),
                             cb.parameter(String.class,"empSal")));

        TypedQuery<Long> tq = em.createQuery(query);
        tq.setParameter("empSal", param);

        Long count = tq.getSingleResult();

        System.out.println("count : "+count);

Example of criteria using distinct
In JPQL

String param = "dev%";

        Query query = em.createQuery(
             "select distinct e.disignation from Employee e where e.designation like :param");
        query.setParameter("param", param);
        List<String> desg= query.getResultList();

        for (String desgs : desg){
            System.out.println(desgs);
        }

Equivalent with Criteria API

String param = "dev%";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<String> query = cb.createQuery(String.class);

        Root<Employee> emp = query.from(Employee.class);
        query.select(emp.<String>get("designation"));
        query.distinct(true);

        query.where(cb.like(music.<String>get("artisteName"),
                            cb.parameter(String.class, "param")));

        TypedQuery<String> tq = em.createQuery(query);
        tq.setParameter("param", param);

        List<String> designations = tq.getResultList();


        for (String desgs : designations){
            System.out.println(desgs);
        }

Happy Coding :)