Posts Tagged ‘Database’

MySQL

Stop all services in wamp.

Then make changes to php.ini

post_max_size = 750M
upload_max_filesize = 750M
max_execution_time = 5000
max_input_time = 5000
memory_limit = 1000M
max_allowed_packet = 200M (in mysql  my.ini  file)

Restart all services and it should be okay,

Now  XXXMb file upload very quickly.

 

I received lot of requests from my readers that asked to me how to implement Pagination with jQuery, PHP and MySQL. so I had developed a simple tutorial. It’s looks big but very simple script. Take a look at this live demo
Pagination with jQuery, MySQL and PHP.


The tutorial contains three PHP files and two js files includes jQuery plugin.

-config.php (Database Configuration)
-pagination.php
-pagination_data.php
-jquery.js
-jquery_pagination.js

Download Script     Live Preview

Database Table

CREATE TABLE messages
(
msg_id INT PRIMARY KEY AUTO_INCREMENT,
message TEXT
);
 

jquery_pagination.js
Contains javascript this script works like a data controller.

$(document).ready(function()
{

//Display Loading Image

function Display_Load()
{
$(“#loading”).fadeIn(900,0);
$(“#loading”).html(“<img src=”bigLoader.gif” />”);
}

//Hide Loading Image

function Hide_Load()
{
$(“#loading”).fadeOut(‘slow’);
};

//Default Starting Page Results

$(“#pagination li:first”)
.css({‘color’ : ‘#FF0084’}).css({‘border’ : ‘none’});
Display_Load();
$(“#content”).load(“pagination_data.php?page=1”, Hide_Load());

//Pagination Click

$(“#pagination li”).click(function(){
Display_Load();

//CSS Styles

$(“#pagination li”)
.css({‘border’ : ‘solid #dddddd 1px’})
.css({‘color’ : ‘#0063DC’});

$(this)
.css({‘color’ : ‘#FF0084’})
.css({‘border’ : ‘none’});

//Loading Data

var pageNum = this.id;
$(“#content”).load(“pagination_data.php?page=” + pageNum, Hide_Load());
});

});

config.php
You have to change hostname, username, password and databasename.

<!–?php

$mysql_hostname = “localhost”;
$mysql_user = “username”;
$mysql_password = “password”;
$mysql_database = “database”;
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password)
or die(“Opps some thing went wrong”);
mysql_select_db($mysql_database, $bd)
or die(“Opps some thing went wrong”);

?>

pagination.php
User interface page.

<?php

include(‘config.php’);
$per_page = 9;

//Calculating no of pages
$sql = “select * from messages”;
$result = mysql_query($sql);
$count = mysql_num_rows($result);
$pages = ceil($count/$per_page)

?>

// <![CDATA[
javascript” src=”http://ajax.googleapis.com/ajax/
// ]]>
libs/jquery/1.3.0/jquery.min.js”>
// <![CDATA[
javascript” src=”jquery_pagination.js”>
// ]]>

<div id=”loading” ></div>
<div id=”content” ></div>
<ul id=”pagination”>
<?php
//Pagination Numbers

for($i=1; $i<=$pages; $i++)
{
echo ‘<li id=”‘.$i.‘”>’.$i.‘</li>’;
}

?>
</ul>

pagination_data.php
Simple php script display data from the messages table.

<?php

include(‘config.php’);
$per_page = 9;
if($_GET)
{
$page=$_GET[‘page’];
}

$start = ($page-1)*$per_page;
$sql = “select * from messages order by msg_id limit $start,$per_page”;
$result = mysql_query($sql);

?>

<table width=”800px”>

<?php

while($row = mysql_fetch_array($result))
{
$msg_id=$row[‘msg_id’];
$message=$row[‘message’];

?>

<tr>
<td><?php echo $msg_id; ?></td>
<td><?php echo $message; ?></td>
</tr>

<?php

}

?>

</table>

CSS Code
CSS code for page numbers.

#loading

{
width: 100%;
position: absolute;
}

li

{
list-style: none;
float: left;
margin-right: 16px;
padding:5px;
border:solid 1px #dddddd;
color:#0063DC;
}

li:hover

{
color:#FF0084;
cursor: pointer;
}

Reference: 9lessons.info

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.

Working with BLOB / CLOB data types in database is sometime a trivial taspring-hibernate-blob-mysql-tutorialsk. I found particularly when working with Hibernate 3 to store and retrieve BLOB objects we need certain things to be taken care of. Let us see a tutorial where we will using Spring 3 MVC and Hibernate 3 to store and retrieve blob objects in database.

Our Goal

Our goal is to create a Document Manager application in Spring 3 MVC and Hibernate. Following is the functionality.

  1. A form is displayed on main page with fields such as Document name, description and browse button to select document from file system.
  2. User can select any document from local drive and upload the same using Save document functionality.
  3. All the documents saved are added in a database table.
  4. List of all the documents present in database is displayed on the main page.
  5. Each document in the list have two buttons: Delete and Download.
  6. Any document can be downloaded by clicking on download button.
  7. Any document can be deleted by clicking on delete button.

Here is the final screen shot of Document manager application.
document-manager-hibernate-spring-blob

Step 1: Create Database Table

For Document Manager application, we will use MySQL database. Create a table documents in MySQL database docdb. This is very preliminary example and thus we have minimum columns to represent a document. Feel free to extend this example and create a more complex application.

CREATE DATABASE `docdb`;

USE `docdb`;

CREATE TABLE `documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` text NOT NULL,
  `filename` varchar(200) NOT NULL,
  `content` mediumblob NOT NULL, /* for ORACLE enter BLOB*/
  `content_type` varchar(255) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

Step 2: Create Maven Project in Eclipse

The document manager application will use Maven for build and dependency management. For this we will use the Maven Dynamic Web Project in Eclipse as the base architecture of our application.

Or directly download the below source code:
Maven Dynamic Web Project (6.7 KB)

Once you have imported / created the Maven web project in Eclipse. Copy following content into Maven’s pom.xml file. These are the dependencies we will use in our Document manager application.
File: /pom.xml

<!--?xml version="1.0" encoding="UTF-8"?>
  4.0.0
  MavenWeb
  MavenWeb
  war
  0.0.1-SNAPSHOT

        maven-compiler-plugin

          1.5
          1.5

        maven-war-plugin
        2.0

      javax.servlet
      servlet-api
      2.5

      org.springframework
      spring-beans
      ${org.springframework.version}

      org.springframework
      spring-jdbc
      ${org.springframework.version}

      org.springframework
      spring-web
      ${org.springframework.version}

      org.springframework
      spring-webmvc
      ${org.springframework.version}

      org.springframework
      spring-orm
      ${org.springframework.version}

      taglibs
      standard
      1.1.2

      javax.servlet
      jstl
      1.1.2

      org.hibernate
      hibernate-entitymanager
      3.3.2.ga

      mysql
      mysql-connector-java
      5.1.10

      commons-dbcp
      commons-dbcp
      20030825.184428

      commons-pool
      commons-pool
      1.5.4

      commons-fileupload
      commons-fileupload
      1.2.1

      commons-io
      commons-io
      1.3

    3.0.2.RELEASE
    UTF-8

document-manager-project-structure
Unzip the source code to your hard drive and import the project in Eclipse. Once the project is imported in Eclipse, we will create package structure for Java source. First rename the project to DocumentManager and create following packages under src/main/java folder.

  1. net.viralpatel.docmanager.controller – This package will contain Spring Controller classes for Document Manager application.
  2. net.viralpatel.docmanager.model – This package will contain form object for Document manager application. Document model will be a simple POJO class with different attributes such as document name, description, filename etc.
  3. net.viralpatel.docmanager.dao – This is the DAO layer of Document manager application. It consists of DocumentDao class which will use Hibernate API to interact with database.
  4. The src/main/resources folder will have hibernate configuration file: hibernate.cfg.xml.
  5. The WEB-INF folder will have jsp/documents.jsp file to render document list and add form and jdbc.properties file containing database connection configuration. Also it contains spring-servlet.xml which will define all the Controller class and web.xml which contain spring configuration.

Entity class – The Hibernate model class

Let us start with the coding of Document manager application. First we will create a model object or hibernate POJO class to store document information. Also this class will be an Entity class and will be linked with DOCUMENTS table in database.

Create a java class Document.java under net.viralpatel.docmanager.model package and copy following code into it.
File: /src/main/java/net/viralpatel/docmanager/model/Document.java

package net.viralpatel.docmanager.model;

import java.sql.Blob;
import java.sql.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.Table;

@Entity
@Table(name="documents")
public class Document {

	@Id
	@GeneratedValue
	@Column(name="id")
	private Integer id;

	@Column(name="name")
	private String name;

	@Column(name="description")
	private String description;

	@Column(name="filename")
	private String filename;

	@Column(name="content")
	@Lob
	private Blob content;

	@Column(name="content_type")
	private String contentType;

	@Column(name="created")
	private Date created;

	//Getter and Setter methods
}

The first thing you’ll notice is that the import statements import from javax.persistence rather than a Hibernate or Spring package. Using Hibernate with Spring, the standard JPA annotations work just as well and that’s what I’m using here.

  • First we’ve annotated the class with @Entity which tells Hibernate that this class represents an object that we can persist.
  • The @Table(name = "documents") annotation tells Hibernate which table to map properties in this class to documents table. The first property in this class on line 20 is our object ID which will be unique for all events persisted. This is why we’ve annotated it with @Id.
  • The @GeneratedValue annotation says that this value will be determined by the datasource, not by the code.
  • The @Column(name = "filename") annotation is used to map this property to the FILENAME column in the DOCUMENTS table.

The Data Access (DAO) Layer

The DAO layer of Document Manager application consist of a class DocumentDAO. Ideal solution will be to create an interface (DocumentDAO) and its corresponding implementation class DocumentDAOImpl. But for sake of simplicity we will create just normal DAO class DocumentDAO.java.

File: src/main/java/net/viralpatel/docmanager/dao/DocumentDAO.java

package net.viralpatel.docmanager.dao;

import java.util.List;

import net.viralpatel.docmanager.model.Document;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class DocumentDAO {

	@Autowired
	private SessionFactory sessionFactory;

	@Transactional
	public void save(Document document) {
		Session session = sessionFactory.getCurrentSession();
		session.save(document);
	}

	@Transactional
	public List<Document> list() {
		Session session = sessionFactory.getCurrentSession();
		List<Document> documents = null;
		try {
			documents = (List<Document>)session.createQuery("from Document").list();

		} catch (HibernateException e) {
			e.printStackTrace();
		}
		return documents;
	}

	@Transactional
	public Document get(Integer id) {
		Session session = sessionFactory.getCurrentSession();
		return (Document)session.get(Document.class, id);
	}

	@Transactional
	public void remove(Integer id) {
		Session session = sessionFactory.getCurrentSession();

		Document document = (Document)session.get(Document.class, id);

		session.delete(document);
	}
}

Methods:

  • list() Method – This method gets the list of all documents stored in documents table and return a List of Document objects.
  • save() Method – This method is used to store a new document (including BLOB) into database.
  • get() Method – This method returns Document entry for a given ID from database. Used in download functionality to download a stored document from database.
  • remove() Method – This method is used to delete a document with specific ID from database.

Note that we have used two Spring annotations @Repository and @Autowired. Classes marked with annotations are candidates for auto-detection by Spring when using annotation-based configuration and classpath scanning. The @Component annotation is the main stereotype that indicates that an annotated class is a “component”.

The @Repository annotation is yet another stereotype that was introduced in Spring 2.0. This annotation is used to indicate that a class functions as a repository and needs to have exception translation applied transparently on it. The benefit of exception translation is that the service layer only has to deal with exceptions from Spring’s DataAccessException hierarchy, even when using plain JPA in the DAO classes.

Another annotation used in DocumentDAO is @Autowired. This is used to autowire the dependency of the DocumentDAO on the SessionFactory.

Also note that we have used @Transactional annotation on each method. Ideally the DAO layer is access from a Service layer and transaction management needs to be specified at Service layer. But again for sake of simplicity we will not include service layer in our example and directly call the DAO layer from Spring Controller. Again, feel free to change this implementation and add your own service layer in between.

For more information about A layered application with Services in Spring MVC and Hibernate read this tutorial.
Spring MVC Hibernate Maven example

Adding Spring MVC Support to Webapplication

Let us add Spring MVC support to our web application.
Update the web.xml file and add servlet mapping for org.springframework.web.servlet.DispatcherServlet. Also note that we have mapped url / with springServlet so all the request are handled by spring.

File: /src/webapp/WEB-INF/web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://java.sun.com/xml/ns/javaee"
	xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
	id="WebApp_ID" version="2.5">
	<display-name>DocumentManager</display-name>
	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
	</welcome-file-list>
	<servlet>
		<servlet-name>spring</servlet-name>
		<servlet-class>
			org.springframework.web.servlet.DispatcherServlet
		</servlet-class>
		<load-on-startup>1</load-on-startup>
	</servlet>
	<servlet-mapping>
		<servlet-name>spring</servlet-name>
		<url-pattern>*.html</url-pattern>
	</servlet-mapping>
</web-app>

Once the web.xml is configured, let us add spring-servlet.xml and jdbc.properties files in /src/main/webapp/WEB-INF folder.

File: /src/main/webapp/WEB-INF/jdbc.properties

jdbc.driverClassName= com.mysql.jdbc.Driver
jdbc.dialect=org.hibernate.dialect.MySQLDialect
jdbc.databaseurl=jdbc:mysql://localhost:3306/docdb
jdbc.username=root
jdbc.password=password

The jdbc.properties file contains database connection information such as database url, username, password, driver class. You may want to edit the driverclass and dialect to other DB if you are not using MySQL.

File: /src/main/webapp/WEB-INF/spring-servlet.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:aop="http://www.springframework.org/schema/aop"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jee="http://www.springframework.org/schema/jee"
	xmlns:lang="http://www.springframework.org/schema/lang"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
		http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd
		http://www.springframework.org/schema/lang http://www.springframework.org/schema/lang/spring-lang.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">

	<context:annotation-config />
	<context:component-scan base-package="net.viralpatel.docmanager" />

	<bean id="jspViewResolver"
		class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="viewClass"
			value="org.springframework.web.servlet.view.JstlView" />
		<property name="prefix" value="/WEB-INF/jsp/" />
		<property name="suffix" value=".jsp" />
	</bean>

	<bean id="propertyConfigurer"
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
		p:location="/WEB-INF/jdbc.properties" />

	<bean id="dataSource"
		class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
		p:driverClassName="${jdbc.driverClassName}"
		p:url="${jdbc.databaseurl}" p:username="${jdbc.username}"
		p:password="${jdbc.password}" />

	<bean id="sessionFactory"
		class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="configLocation">
			<value>classpath:hibernate.cfg.xml</value>
		</property>
		<property name="configurationClass">
			<value>org.hibernate.cfg.AnnotationConfiguration</value>
		</property>
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">${jdbc.dialect}</prop>
				<prop key="hibernate.show_sql">true</prop>
				<prop key="hibernate.connection.SetBigStringTryClob">true</prop>
				<prop key="hibernate.jdbc.batch_size">0</prop>
			</props>
		</property>
	</bean>
	<bean id="multipartResolver"
		class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

		<!-- one of the properties available; the maximum file size in bytes -->
		<property name="maxUploadSize" value="10000000" />
	</bean>
	<tx:annotation-driven />

	<bean id="transactionManager"
		class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>
</beans>

The spring-servlet.xml file contains different spring mappings such as transaction manager, hibernate session factory bean, data source etc.

  • jspViewResolver bean – This bean defined view resolver for spring mvc. For this bean we also set prefix as “/WEB-INF/jsp/” and suffix as “.jsp”. Thus spring automatically resolves the JSP from WEB-INF/jsp folder and assigned suffix .jsp to it.
  • propertyConfigurer bean – This bean is used to load database property file jdbc.properties. The database connection details are stored in this file which is used in hibernate connection settings.
  • dataSource bean – This is the java datasource used to connect to document manager database. We provide jdbc driver class, username, password etc in configuration.
  • sessionFactory bean – This is Hibernate configuration where we define different hibernate settings. hibernate.cfg.xml is set a config file which contains entity class mappings. Also note that in sessionFactory we have specified few hibernate properties such as hibernate.connection.SetBigStringTryClob and hibernate.jdbc.batch_size. These are used to configure BLOB / CLOB settings in hibernate.
  • multipartResolver bean – We use Spring MVCs CommonsMultipartResolver. This resolver will resolve multipart form data such as file uploads from the request and make available File object to spring controller. Note that we have specified property maxUploadSize with value 10000000. This is the maximum limit of filesize which can be uploaded in our example.
  • transactionManager bean – We use hibernate transaction manager to manage the transactions of our document manager application.

File: /src/main/resources/hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <mapping />
    </session-factory>

</hibernate-configuration>

The Controller – Spring MVC controller class

We are almost done with our application. Just add following Spring controller class DocumentController.java to net.viralpatel.docmanager.controller package.

File: /src/main/java/net/viralpatel/docmanager/controller/DocumentController.java

package net.viralpatel.docmanager.controller;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.SQLException;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import net.viralpatel.docmanager.dao.DocumentDAO;
import net.viralpatel.docmanager.model.Document;

import org.apache.commons.io.IOUtils;
import org.hibernate.Hibernate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

@Controller
public class DocumentController {

	@Autowired
	private DocumentDAO documentDao;

	@RequestMapping("/index")
	public String index(Map<String, Object> map) {
		try {
			map.put("document", new Document());
			map.put("documentList", documentDao.list());
		}catch(Exception e) {
			e.printStackTrace();
		}

		return "documents";
	}

	@RequestMapping(value = "/save", method = RequestMethod.POST)
	public String save(
			@ModelAttribute("document") Document document,
			@RequestParam("file") MultipartFile file) {

		System.out.println("Name:" + document.getName());
		System.out.println("Desc:" + document.getDescription());
		System.out.println("File:" + file.getName());
		System.out.println("ContentType:" + file.getContentType());

		try {
			Blob blob = Hibernate.createBlob(file.getInputStream());

			document.setFilename(file.getOriginalFilename());
			document.setContent(blob);
			document.setContentType(file.getContentType());
		} catch (IOException e) {
			e.printStackTrace();
		}

		try {
			documentDao.save(document);
		} catch(Exception e) {
			e.printStackTrace();
		}

		return "redirect:/index.html";
	}

	@RequestMapping("/download/{documentId}")
	public String download(@PathVariable("documentId")
			Integer documentId, HttpServletResponse response) {

		Document doc = documentDao.get(documentId);
		try {
			response.setHeader("Content-Disposition", "inline;filename="" +doc.getFilename()+ """);
			OutputStream out = response.getOutputStream();
			response.setContentType(doc.getContentType());
			IOUtils.copy(doc.getContent().getBinaryStream(), out);
			out.flush();
			out.close();

		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	@RequestMapping("/remove/{documentId}")
	public String remove(@PathVariable("documentId")
			Integer documentId) {

		documentDao.remove(documentId);

		return "redirect:/index.html";
	}
}

The spring controller defines four methods to manipulate document manager application.

  • index method – This method uses list() method of DocumentDAO to fetch the list of all documents from database. Note that we have mapped request “/index” to this method. Thus Spring will automatically calls this method whenever it encounters this url in request.
  • save method – This method adds a new document to document list. The document details are fetched in Document object using @ModelAttribute annotation. Also note that the request “/save” is mapped with this method. The request method should also be POST. Once the document is added in document list, we redirect to /index.html page which in turn calls index() method to display document list to user. One more thing to note here is @RequestParam. We are mapping MultipartFile object using @RequestParam(“file”) annotation. Spring automatically detects “file” data from request and map it with MultipartFile object. This object is later converted to BLOB object and set in the Document content.

    Related: Forms in Spring MVC

  • download method – This method is used to download a selected testcase. Note that we are fetching the document content from database using DAO class and thn set the Data stream in Response. Also note that we are using response.setHeader() method to set "Content-Disposition". This will raise a Save As dialog box in browser whenever user tries to download a document.
  • remove method – This methods removes a document from the document list. Similar to save() this method also redirects user to /index.html page once the document is removed. One thing to note in this method is the way we have mapped request url using @RequestMapping annotation. The url “/remove/{documentId}” is mapped thus whenever user send a request /remove/12.html, the remove method will try to delete document with ID:12.

Finally add following JSP file to WEB-INF/jsp folder.
File: /src/main/webapp/WEB-INF/jsp/documents.jsp

<%@taglib uri="http://www.springframework.org/tags" prefix="spring"%>
<%@taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
	<title>Document Manager - viralpatel.net</title>
</head>
<body>

<h2>Document Manager</h2>

<h3>Add new document</h3>
<form:form method="post" action="save.html" commandName="document" enctype="multipart/form-data">
	<form:errors path="*" cssClass="error"/>
	<table>
	<tr>
		<td><form:label path="name">Name</form:label></td>
		<td><form:input path="name" /></td> 
	</tr>
	<tr>
		<td><form:label path="description">Description</form:label></td>
		<td><form:textarea path="description" /></td>
	</tr>
	<tr>
		<td><form:label path="content">Document</form:label></td>
		<td><input type="file" name="file" id="file"></input></td>
	</tr>
	<tr>
		<td colspan="2">
			<input type="submit" value="Add Document"/>
		</td>
	</tr>
</table>	
</form:form>

<br/>
<h3>Document List</h3>
<c:if  test="${!empty documentList}">
<table>
<tr>
	<th>Name</th>
	<th>Description</th>
	<th>&nbsp;</th>
</tr>
<c:forEach items="${documentList}" var="document">
	<tr>
		<td width="100px">${document.name}</td>
		<td width="250px">${document.description}</td>
		<td width="20px">
			<a href="${pageContext.request.contextPath}/download/${document.id}.html"><img 
				src="${pageContext.request.contextPath}/img/save_icon.gif" border="0" 
				title="Download this document"/></a> 

			<a href="${pageContext.request.contextPath}/remove/${document.id}.html"
				onclick="return confirm('Are you sure you want to delete this document?')"><img 
				src="${pageContext.request.contextPath}/img/delete_icon.gif" border="0" 
				title="Delete this document"/></a> 
		</td>
	</tr>
</c:forEach>
</table>
</c:if>
</body>
</html>

Download Source code

Click here to download full source code of Document manager application (16 KB)

Enhanced by Zemanta

 

 

Since its release in 1995, MySQL has became one of the most commonly used database in Internet world. A lot of small and medium businesses uses MySQL as their backend db.  Its popularity for use with web applications is closely tied to the popularity of PHP, which is often combined with MySQL. Wikipedia runs on MediaWiki software, which is written in PHP and uses a MySQL database. Several high-traffic web sites use MySQL for its data storage and logging of user data, including Flickr, Facebook, Wikipedia, Google, Nokia and YouTube.

MySQL provide a great command line utility to take backup of your MySQL database and restore it. mysqldump command line utility is available with MySQL installation (bin directory) that can be used to achieve this.

1. Getting backup of a MySQL database using mysqldump.

Use following command line for taking backup of your MySQL database using mysqldump utility.

mysqldump –-user [user name] –-password=[password] [database name] > [dump file]

or

mysqldump –u[user name] –p[password] [database name] > [dump file]

Example:

mysqldump –-user root –-password=myrootpassword db_test > db_test.sql

or

mysqldump –uroot –pmyrootpassword db_test > db_test.sql

2. Backup multiple databases in MySQL.

mysqldump –u[user name] –p[password] [database name 1] [database name 2] .. > [dump file]

Example:

mysqldump –-user root –-password=myrootpassword db_test db_second db_third > db_test.sql

3. Backup all databases in MySQL.

shell> mysqldump –u[user name] –p[password] –all-databases > [dump file]

4. Backup a specific table in MySQL.

shell> mysqldump --user [username] --password=[password] [database name] [table name] 
> /tmp/sugarcrm_accounts_contacts.sql

Example:

shell> mysqldump --user root --password=myrootpassword db_test customers 
> db_test_customers.sql

5. Restoring MySQL database.

The mysqldump utility is used only to take the MySQL dump. To restore the database from the dump file that you created in previous step, use mysql command.

shell> mysql --u [username] --password=[password] [database name] < [dump file]

Example:

shell> mysql --user root --password=myrootpassword new_db < db_test.sql
Enhanced by Zemanta

Empire Avenue Tag Cloud

The basic idea this time is to present a way to form a tag cloud from user input text and text entered in the past. A tag cloud is a visual depiction of user-generated tags, or simply the word content of a site, used typically to describe the content of web sites.

for this we will create an HTML form that will accept user text & also allow user to see tag cloud generated from mysql database which contains the text entered in the past.

<!--?php
    echo 'php” name=”gen_tag_db”>’;
    echo 'Input your text here:
<textarea name=”tag_input” rows=”20″ cols=”80″>

';

    echo '<input type="submit" name="submit">';
    echo '</form>';
?>
<br />
<h3>OR</h3>
<br />
<p>see the current tag cloud here</p>
<!--?php
    echo 'php”>’;
    echo '<input type="submit" value="show current tag cloud" >';
    echo '</form>';
?>

The entered text will be tokenized into single words with php function strtok(), each of which will have its frequency counted and the pair will go into an array. This array will then be stored into a mysql database, we can optionally keep a coloumn in the mysql database table to store links if any for future expansion of this project.

1) tag_id —- int,primary key,auto increament

2) keyword — varchar(20),unique

3) weight — int

4) link — varchar(256).

Next make a php file and name it tag_cloud_gen.php . The php code written in following lines just make an array ‘$words’ which has keyword in lower case & its frequency association from input text. The pairs from the array are then feed into the mysql database ‘tagcloud_db’ which has a table callet ‘tags’ whose columns are listed above. On encountering error the mysql _errrno() returns error number. While feeding the word array to the tags table duplication may occour, because of the previous entries, so we check whether this is the case by comparing the number returned to ’1062′ which indicates duplicate field present in the table (the keyword coloumn of table has unique constraint). On encountering this we simply update the mysql database to include the count of this input word/tag too.

<!--?php
///////////////////////////////////////////////////////////////////////////////////////////////////////
/**
* this function will update the mysql database table to reflect the new count of the keyword
* i.e. the sum of current count in the mysql database &amp;amp; current count in the input.
*/
function update_database_entry($connection,$table,$keyword,$weight){
    
    $string=$_POST['tag_input'];
    $connection = mysql_connect("localhost", "root", "");
    /**
    * now comes the main part of generating the tag cloud
    * we would use a css styling for deciding the size of the tag according to its weight,
    * both of which would be fetched from mysql database.
    */
    $query="select * from `tagcloud_db`.`tags` where keyword like '%$keyword%'";
    $resultset=mysql_query($query,$connection);
    if(!$resultset){
        die('Invalid query: ' . mysql_error());
    } else {
        while($row=mysql_fetch_array($resultset)){
        $query="UPDATE `tagcloud_db`.`tags` SET weight=".($row[2]+$weight)." where tag_id=".$row[0].";";
        mysql_query($query,$connection);
    }
}
}
?>
<?php
/*
* get the input string from the post and then tokenize it to get each word, save the words in an array
* in case the word is repeated add '1' to the existing words counter
*/
    $count=0;
    $tok = strtok($string, " \t,;.\'\"!&-`\n\r");//considering line-return,line-feed,white space,comma,ampersand,tab,etc... as word separator
    if(strlen($tok)>0) $tok=strtolower($tok);
    $words=array();
    $words[$tok]=1;
    while ($tok !== false) {
        echo "Word=$tok<br />";
        $tok = strtok(" \t,;.\'\"!&-`\n\r");
        if(strlen($tok)>0) {
        $tok=strtolower($tok);
        if($words[$tok]>=1){
            $words[$tok]=$words[$tok] + 1;
        } else {
            $words[$tok]=1;
        }
    }
}
print_r($words);
echo '<br /><br />';
/**
* now enter the above array of word and corresponding count values into the database table
* in case the keyword already exist in the table then update the database table using the function 'update_database_entry(...)'
*/
$table="tagcloud_db";
mysql_select_db($table,$connection);
foreach($words as $keyword=>$weight){
    $query="INSERT INTO `tagcloud_db`.`tags` (keyword,weight,link) values ('".$keyword."',".$weight.",'NA')";
    if(!mysql_query($query,$connection)){
        if(mysql_errno($connection)==1062){
            update_database_entry($connection,$table,$keyword,$weight);
        }
    }
}
mysql_close($connection);
?>

Make another file and name it style.css . Put the following code in it.

HTML, BODY
{
padding: 0;
border: 0px none;
font-family: Verdana;
font-weight: none;
}
.tags_div
{
padding: 3px;
border: 1px solid #A8A8C3;
background-color: white;
width: 500px;
-moz-border-radius: 5px;
}
H1
{
font-size: 16px;
font-weight: none;
}
A:link
{
color: #676F9D;
text-decoration: none;
}
A:hover
{
text-decoration: none;
background-color: #4F5AA1;
color: white;
}

This will make our tag cloud look pretty, save it as style.css .
again make a new php file and name it show_tag_cloud.php .
In the php code that follows we connect to mysql database, fetch back all the tags, its weight and link.

Then it calculates the size for each tag using its weight & minimum assumed size for tags, it also associates each tag the link retrieved from the database or with a google link if no link was there i.e. ‘NA’

<?php
    $connection = mysql_connect("localhost", "root", "");
    $table="tagcloud_db";
    $words=array();
    $words_link=array();
    mysql_select_db($table,$connection);
    $query="SELECT keyword,weight,link FROM `tagcloud_db`.`tags`;";
    
    if($resultset=mysql_query($query,$connection)){
        while($row=mysql_fetch_row($resultset)){
            $words[$row[0]]=$row[1];
            $words_link[$row[0]]=$row[2];
        }
    }
// Incresing this number will make the words bigger; Decreasing will do reverse
$factor = 0.5;
// Smallest font size possible
$starting_font_size = 12;
// Tag Separator
$tag_separator = '&nbsp;    ';
$max_count = array_sum($words);
?>
<!--DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
    <HEAD>
        <TITLE> Tag Cloud Generator </TITLE>
        <META NAME="Keywords" CONTENT="tag, cloud, php, mysql">
        <META NAME="Description" CONTENT="A Tag Cloud using php and mysql">
        <LINK REL="stylesheet" HREF="style.css" TYPE="text/css">
    </HEAD>
<BODY>

Tag Cloud using php

and mysql </h1><div align='center' class='tags_div'>

<?php
foreach($words as $tag => $weight )
{
    $x = round(($weight * 100) / $max_count) * $factor;
    $font_size = $starting_font_size + $x.'px';
    if($words_link[$tag]=='NA') echo "<span style='font-size: ".$font_size."; color: #676F9D;'>.$tag."&meta='>".$tag."</a></span>".$tag_separator;
    else echo "<span style='font-size: ".$font_size."; color: #676F9D;'>.$words_link[$tag]."/'>".$tag."</a></span>".$tag_separator;
}
?>
</div></center>
</BODY>
</HTML>

now put them all in your webserver’s root directory and watch the results. Each query will give you new results over time as the database grows.

a sample output from my tag cloug looks like this:

a sample tag cloud on my computer

This tag cloud was generated using the following data:

tag_id;keyword;weight;link
“1″;”vimal”;”7″;”www.zeeshanakhter.com”
“2″;”scet”;”5″;”NA”
“3″;”engg”;”2″;”NA”
“4″;”0″;”1″;”NA”
“7″;”google”;”5″;”NA”
“8″;”cool”;”2″;”NA”
“9″;”orkut”;”3″;”NA”

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.

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());

 

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

Reference Rose India.

This is detailed simple java code that how save image into mysql database. Before running this java code you need to create data base and table to save image in same database. In the example given below we have used database ‘mahendra’ and table ‘save_image’.

Structure of table ‘save_image’

First create database named ‘mahendra’ by query given below:

mysql> create database mahendra;

and create table by query given below:

CREATE TABLE save_image (             
              id int(5) NOT NULL auto_increment,  
              name varchar(25) default NULL,      
              city varchar(20) default NULL,      
              image blob,                         
              Phone varchar(15) default NULL,     
              PRIMARY KEY  (`id`)                   
 );

Save table ‘save_image‘ in database ‘mahendra‘. Before running this java code you need mysql connector jar in the jdk1.6.0_01./lib.

SaveImageToDatabase java file provides full code for uploading image in database.

SaveImageToDatabase.java

import java.sql.*;
import java.io.*;
class SaveImageToDatabase {
public static void main(String[] args) throws SQLException {
// declare a connection by using Connection interface 
Connection connection = null;
/* Create string of connection url within specified format with machine 
name, port number and database name. Here machine name id localhost 
and database name is mahendra. */
String connectionURL = "jdbc:mysql://localhost:3306/mahendra";
/*declare a resultSet that works as a table resulted by execute a specified 
sql query. */
ResultSet rs = null;
// Declare prepare statement.
PreparedStatement psmnt = null;
// declare FileInputStream object to store binary stream of given image.
FileInputStream fis;
try {
// Load JDBC driver "com.mysql.jdbc.Driver"
Class.forName("com.mysql.jdbc.Driver").newInstance();
/* Create a connection by using getConnection() method that takes 
parameters of string type connection url, user name and password to 
connect to database. */
connection = DriverManager.getConnection(connectionURL, "root", "root");
// create a file object for image by specifying full path of image as parameter.
File image = new File("C:/image.jpg");
/* prepareStatement() is used for create statement object that is 
used for sending sql statements to the specified database. */
psmnt = connection.prepareStatement
("insert into save_image(name, city, image, Phone) "+ "values(?,?,?,?)");
psmnt.setString(1,"mahendra");
psmnt.setString(2,"Delhi");
psmnt.setString(4,"123456");
fis = new FileInputStream(image);
psmnt.setBinaryStream(3, (InputStream)fis, (int)(image.length()));
/* executeUpdate() method execute specified sql query. Here this query 
insert data and image from specified address. */ 
int s = psmnt.executeUpdate();
if(s>0) {
System.out.println("Uploaded successfully !");
}
else {
System.out.println("unsucessfull to upload image.");
}
}
// catch if found any exception during rum time.
catch (Exception ex) {
System.out.println("Found some error : "+ex);
}
finally {
// close all the connections.
connection.close();
psmnt.close();
}
}
}

Output of the program:

Download Source Code