Beginning Amazon DynamoDB

What Is Amazon DynamoDB?

Amazon DynamoDB is a fully managed NoSQL database service that provides fast and predictable performance with seamless scalability. 

NoSQL is a term used to describe nonrelational database systems that are highly available, scalable, and optimized for high performance. Instead of the relational model, NoSQL databases (like DynamoDB) use alternate models for data management, such as key-value pairs or document storage. For more information, see http://aws.amazon.com/nosql.

Read More

MyBatis introduction

 MyBatis is a Java persistence framework that couples objects with stored procedures or SQL statements using an XML descriptor or annotations.

MyBatis is free software that is distributed under the Apache License 2.0.

MyBatis is a fork of iBATIS 3.0 and is maintained by a team that includes the original creators of iBATIS. 

MyBatis is a first class persistence framework with support for custom SQL, stored procedures and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and retrieval of results. MyBatis can use simple XML or Annotations for configuration and map primitives, Map interfaces and Java POJOs (Plain Old Java Objects) to database records

Read More

Oracle SQL căn bản P2

1> Execute a function that is defined in a package

Question: How can I execute a function that is defined in a package using Oracle/PLSQL?

Answer: To execute a function that is defined in a package, you'll have to prefix the function name with the package name.

package_name.function_name (parameter1, parameter2, ... parameter_n)

You can execute a function a few different ways.

Read More

Beginning elasticsearch

1.Elasticsearch concepts

wikipedia talks about elasticsearch:
Elasticsearch is a search engine based on Lucene. It provides a distributed, multitenant-capable full-text searchengine with an HTTP web interface and schema-free JSON documents. Elasticsearch is developed in Java and is released as open source under the terms of the Apache License. Official clients are available in Java, .NET(C#), PHP, Python, Apache Groovy, Ruby and many other languages.^[1] According to the DB-Engines ranking, Elasticsearch is the most popular enterprise search engine followed by Apache Solr, also based on Lucene.^[2]

elasticsearch definition from elastic site:

Elasticsearch is a real-time distributed search and analytics engine. It allows you to explore your data at a speed and at a scale never before possible. It is used for full-text search, structured search, analytics, and all three in combination:

• Wikipedia uses Elasticsearch to provide full-text search with highlighted search snippets, and search-as-you-type and did-you-mean suggestions.
• The Guardian uses Elasticsearch to combine visitor logs with social -network data to provide real-time feedback to its editors about the public’s response to new articles.
• Stack Overflow combines full-text search with geolocation queries and uses more-like-this to find related questions and answers.
• GitHub uses Elasticsearch to query 130 billion lines of code.

Read More

Database normalization Introduction

Definition from wikipedia

Database normalization is the process of restructuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancyand improve data integrity. It was first proposed by Edgar F. Codd as an integral part of his relational model.

Normalization entails organizing the columns (attributes) and tables (relations) of a database to ensure that their dependencies are properly enforced by database integrity constraints. It is accomplished by applying some formal rules either by a process of synthesis (creating a new database design) or decomposition (improving an existing database design).

=> quá trình chuẩn hóa DB sẽ áp dụng 1 số luật (các dạng chuẩn) để thiết kế các bảng cơ sở dữ liệu sao cho có thể giảm thiếu tối đa dư thừa dữ liệu, sự chính xác của truy vấn dữ liệu, tăng performance truy xuất DB,..

Read More

LEFT JOIN vs. LEFT OUTER JOIN in SQL

As per the documentation: FROM (Transact-SQL):

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

The keyword OUTER is marked as optional (enclosed in square brackets), and what this means in this case is that whether you specify it or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will of course make a difference.
For instance, the entire type-part of the JOIN clause is optional, in which case the default is INNER if you just specify JOIN. In other words, this is legal:

SELECT *
FROM A JOIN B ON A.X = B.Y

Here's a list of equivalent syntaxes:

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

Read More

JPA Criteria API

Using the Criteria API to Create Queries

The Criteria API is used to define queries for entities and their persistent state by creating query-defining objects. Criteria queries are written using Java programming language APIs, are typesafe, and are portable. Such queries work regardless of the underlying data store.

The following topics are addressed here:

• Overview of the Criteria and Metamodel APIs
• Using the Metamodel API to Model Entity Classes
• Using the Criteria API and Metamodel API to Create Basic Typesafe Queries

0.1 Overview of the Criteria and Metamodel APIs

Similar to JPQL, the Criteria API is based on the abstract schema of persistent entities, their relationships, and embedded objects. The Criteria API operates on this abstract schema to allow developers to find, modify, and delete persistent entities by invoking Java Persistence API entity operations. The Metamodel API works in concert with the Criteria API to model persistent entity classes for Criteria queries.

The Criteria API and JPQL are closely related and are designed to allow similar operations in their queries. Developers familiar with JPQL syntax will find equivalent object-level operations in the Criteria API.

The following simple Criteria query returns all instances of the Pet entity in the data source:

EntityManager em = ...;
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
Root<Pet> pet = cq.from(Pet.class);
cq.select(pet);
TypedQuery<Pet> q = em.createQuery(cq);
List<Pet> allPets = q.getResultList();

The equivalent JPQL query is

SELECT p
FROM Pet p

Read More

JPA Select Statements and NamedQuery

Select Statements

A select query has six clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. The SELECT and FROM clauses are required, but the WHERE, GROUP BY, HAVING, and ORDER BY clauses are optional. Here is the high-level BNF syntax of a query language select query:

     Note: some of the terms referred to in this chapter.

• Abstract schema: The persistent schema abstraction (persistent entities, their state, and their relationships) over which queries operate. The query language translates queries over this persistent schema abstraction into queries that are executed over the database schema to which entities are mapped.
• Abstract schema type: The type to which the persistent property of an entity evaluates in the abstract schema. That is, each persistent field or property in an entity has a corresponding state field of the same type in the abstract schema. The abstract schema type of an entity is derived from the entity class and the metadata information provided by Java language annotations.
• Backus-Naur Form (BNF): A notation that describes the syntax of high-level languages. The syntax diagrams in this chapter are in BNF notation.
• Navigation: The traversal of relationships in a query language expression. The navigation operator is a period.
• Path expression: An expression that navigates to an entity's state or relationship field.
• State field: A persistent field of an entity.
• Relationship field: A persistent field of an entity whose type is the abstract schema type of the related entity.

QL_statement ::= select_clause from_clause 
  [where_clause][groupby_clause][having_clause][orderby_clause]

Read More

Ordinary JPA Query API

Queries are represented in JPA 2 by two interfaces - the old Query interface, which was the only interface available for representing queries in JPA 1, and the new TypedQuery interface that was introduced in JPA 2. The TypedQuery interface extends the Query interface.

In JPA 2 the Query interface should be used mainly when the query result type is unknown or when a query returns polymorphic results and the lowest known common denominator of all the result objects is Object. When a more specific result type is expected queries should usually use the TypedQuery interface. It is easier to run queries and process the query results in a type safe manner when using the TypedQuery interface.
Ngoài query thông thường dùng Query hoặc TypedQuery thì còn có JPA Criteria API  và named queries 

Building Queries with createQuery

As with most other operations in JPA, using queries starts with an EntityManager (represented by em in the following code snippets), which serves as a factory for both Query and TypedQuery:

  Query q1 = em.createQuery("SELECT c FROM Country c");
 
  TypedQuery<Country> q2 =
      em.createQuery("SELECT c FROM Country c", Country.class);

Read More

JavaDB Connection pool

From Wikipedia, the free encyclopedia

In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required.^{[citation needed]} Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.

Applications

Web-based and enterprise applications use an application server to handle connection pooling. Dynamic web pages without connection pooling open connections to database services as required and close them when the page is done servicing a particular request. Pages that use connection pooling, on the other hand, maintain open connections in a pool. When the page requires access to the database, it simply uses an existing connection from the pool, and establishes a new connection only if no pooled connections are available. This reduces the overhead associated with connecting to the database to service individual requests.

Local applications that need frequent access to databases can also benefit from connection pooling. Open connections can be maintained in local applications that don't need to service separate remote requests like application servers, but implementations of connection pooling can become complicated. A number of available libraries implement connection pooling and related SQL query pooling, simplifying the implementation of connection pools in database-intensive applications.

Administrators can configure connection pools with restrictions on the numbers of minimum connections, maximum connections and idle connections to optimize the performance of pooling in specific problem contexts and in specific environments.

Database support

Connection pooling is supported by IBM DB2,^[1] Microsoft SQL Server,^[2] Oracle,^[3] MySQL,^[4] and PostgreSQL.^[5]

How to configure the C3P0 connection pool in Hibernate

Connection Pool
Connection pool is good for performance, as it prevents Java application create a connection each time when interact with database and minimizes the cost of opening and closing connections.

Hibernate comes with internal connection pool, but not suitable for production use. In this tutorial, we show you how to integrate third party connection pool – C3P0, with Hibernate.

1. Get hibernate-c3p0.jar

To integrate c3p0 with Hibernate, you need hibernate-c3p0.jar, get it from JBoss repository.

File : pom.xml

<project ...>

 <repositories>
  <repository>
   <id>JBoss repository</id>
   <url>http://repository.jboss.org/nexus/content/groups/public/</url>
  </repository>
 </repositories>

 <dependencies>

  <dependency>
   <groupId>org.hibernate</groupId>
   <artifactId>hibernate-core</artifactId>
   <version>3.6.3.Final</version>
  </dependency>

  <!-- Hibernate c3p0 connection pool -->
  <dependency>
   <groupId>org.hibernate</groupId>
   <artifactId>hibernate-c3p0</artifactId>
   <version>3.6.3.Final</version>
  </dependency>

 </dependencies>
</project>

2. Configure c3p0 properties

To configure c3p0, puts the c3p0 configuration details in “hibernate.cfg.xml“, like this :

File : hibernate.cfg.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
 <session-factory>
  <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
  <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:MKYONG</property>
  <property name="hibernate.connection.username">mkyong</property>
  <property name="hibernate.connection.password">password</property>
  <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
  <property name="hibernate.default_schema">MKYONG</property>
  <property name="show_sql">true</property>

  <property name="hibernate.c3p0.min_size">5</property>
  <property name="hibernate.c3p0.max_size">20</property>
  <property name="hibernate.c3p0.timeout">300</property>
  <property name="hibernate.c3p0.max_statements">50</property>
  <property name="hibernate.c3p0.idle_test_period">3000</property>

  <mapping class="com.mkyong.user.DBUser"></mapping>
</session-factory>
</hibernate-configuration>

1. hibernate.c3p0.min_size – Minimum number of JDBC connections in the pool. Hibernate default: 1
2. hibernate.c3p0.max_size – Maximum number of JDBC connections in the pool. Hibernate default: 100
3. hibernate.c3p0.timeout – When an idle connection is removed from the pool (in second). Hibernate default: 0, never expire.
4. hibernate.c3p0.max_statements – Number of prepared statements will be cached. Increase performance. Hibernate default: 0 , caching is disable.
5. hibernate.c3p0.idle_test_period – idle time in seconds before a connection is automatically validated. Hibernate default: 0

Note
For detail about hibernate-c3p0 configuration settings, please read this article.

Understanding Java Database Connection Pooling Properties

Basic properties controlling pooling behaviour.

Property	Explanation
minpool	Minimum number of connections that should be held in the pool.
maxpool	Maximum number of connections that may be held in the pool.
maxsize	Maximum number of connections that can be created for use.
idleTimeout	The idle timeout for connections (seconds).

  
Bean properties supported by snaq.db.DBPoolDataSource (can also be specified via snaq.db.DBPoolDataSourceFactory).

Property	Description
name	Name of the DataSource, which is also used to assign a ConnectionPool name.
description	Description for the DataSource.
driverClassName	Fully-qualified class name of JDBC Driver to use.
url	JDBC URL to connect to the database.
user	Username for database connections.
password	Password for database connections.
passwordDecoderClassName	Fully-qualified class name of snaq.db.PasswordDecoder implementation to use. (It must have a public no-argument constructor).
minPool	Minimum number of pooled connections to maintain.
maxPool	Maximum number of pooled connections to maintain.
maxSize	Maximum number of connection that can be created.
idleTimeout	Idle timeout of pooled connections (seconds).
loginTimeout	Timeout for database connection attempts (seconds).
validatorClassName	Fully-qualified class name of snaq.db.ConnectionValidator implementation to use. (It must have a public no-argument constructor).
validatorQuery*	Query string to use for validation, if validatorClassName not specified. This is passed to a snaq.db.SimpleQueryValidator instance.

Hibernate-Supported Connection Pools

Table 10.1. Hibernate-Supported Connection Pools

c3p0	http://sourceforge.net/projects/c3p0	Distributed with Hibernate
Apache DBCP	http://jakarta.apache.org/commons/dbcp/	Apache Pool
Proxool	http://proxool.sourceforge.net/	JDBC Pooling Wrapper

Read More