Spring MVC with Sqlite sample

Include all the spring application jar for MVC
and for sqlite sqlite-jdbc-3.7.2.jar

Note : helloPage.jsp need some amendments to list and iterate the results from controller.

/**
* SqlLite_CURD.java
*/
package sample;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
* @author PraveenKumar
*
*/
public class SqlLite_CURD {

/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub

//getConnection();
// create();
//populateData();
listAllValues();
}

public static Connection getConnection(){
Connection connection=null;
try{
Class.forName("org.sqlite.JDBC");
connection=DriverManager.getConnection("jdbc:sqlite:D:/Praveen/sw/SQL_LITE/test.db");

//String sql="SELECT name FROM sqlite_master WHERE type='table' ;";
// String sql="SELECT * from test ;";
// PreparedStatement stmt=conn.prepareStatement(sql);
// System.out.println("Test");
// ResultSet rs=stmt.executeQuery();
// while(rs.next()){
// System.out.println(""+rs.getInt(1));
//// System.out.println(""+rs.getString(2));
//// System.out.println(""+rs.getInt(3));
// }
}catch(Exception e){
e.printStackTrace();
}
return connection;
}
public static void create(){
String createSQL="CREATE TABLE mytable(id INT,name text, phonenumber INT) ; ";

Connection con=getConnection();
PreparedStatement pstmt=null;
try{
if(con!=null){
pstmt=con.prepareStatement(createSQL);
if(pstmt.execute()) {
System.out.println("Table created ...!!!");
}
}
closeConnection(pstmt, con);
}catch(Exception e){
e.printStackTrace();
}
}

public static void populateData(){
int maxid=0;
try
{
Connection conn=getConnection();
PreparedStatement pstmt=null;
if(conn!=null){
String insertSQL="INSERT INTO mytable values(?,?,?)";
pstmt=conn.prepareStatement(insertSQL);
maxid=getMaxId();
if(maxid!=0){

for(int i=maxid+1;i pstmt.setInt(1,i);
pstmt.setString(2,i+"th data");
pstmt.setInt(3,(8527400+i));
pstmt.execute();
}
}
}
closeConnection(pstmt, conn);
} catch (Exception e)
{
e.printStackTrace();
}
}

public static void listAllValues(){
try
{
Connection conn=getConnection();
PreparedStatement pstmt=null;
if(conn!=null){
String selectSQL="select * from mytable ;";
pstmt=conn.prepareStatement(selectSQL);

ResultSet rs=pstmt.executeQuery();
while(rs.next()){
System.out.println(""+rs.getInt(1) +"\t"+rs.getString(2)+"\t"+rs.getInt(3));
// System.out.println(""+rs.getString(2));
// System.out.println(""+rs.getInt(3));
}
}
closeConnection(pstmt, conn);

} catch (Exception e)
{
e.printStackTrace();
}
}

public static void closeConnection(PreparedStatement pstmt, Connection conn){
try
{
pstmt.close();
conn.close();
} catch (Exception e)
{
e.printStackTrace();
}

}

public static int getMaxId(){
int maxid=0;
try
{
Connection conn=getConnection();
PreparedStatement pstmt=null;
if(conn!=null){
String selectSQL="select max(id) as maxid from mytable ;";
pstmt=conn.prepareStatement(selectSQL);

ResultSet rs=pstmt.executeQuery();
while(rs.next()){
//System.out.println(""+rs.getInt(1) +"\t"+rs.getString(2)+"\t"+rs.getInt(3));
// System.out.println(""+rs.getString(2));
// System.out.println(""+rs.getInt(3));
maxid=rs.getInt(1);
}
}
closeConnection(pstmt, conn);

} catch (Exception e)
{
e.printStackTrace();
}
return maxid;
}

}

/**

package springapp.web;

/**

* @author PraveenKumar

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.context.ApplicationContext;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.servlet.ModelAndView;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

import springapp.web.dao.mytableDao;

import java.io.IOException;

import java.util.List;

//public class HelloController implements Controller {

// protected final Log logger = LogFactory.getLog(getClass());

// public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response)

// throws ServletException, IOException {

// logger.info("Returning hello view");

// return new ModelAndView("hello.jsp");

// }

@Controller

public class HelloWorldController {

private mytableDao edao;

/**

* @param edao the edao to set

@Autowired

public void setEdao(mytableDao edao) {

this.edao = edao;

}

@RequestMapping("/hello")

public ModelAndView helloWorld(){

String message="Welcome to MVC Sample!!!";

// ApplicationContext ctx=

List resultSet= edao.listAllValues();

ModelAndView modelView=new ModelAndView("helloPage","message",message);

modelView.addObject("results", resultSet);

return modelView;

}

/**

package springapp.web.dao;

/**

* @author PraveenKumar

public class MyTable {

int id;

String name;

String mobile;

/**

* @return the id

public int getId() {

return id;

}

/**

* @param id the id to set

public void setId(int id) {

this.id = id;

}

/**

* @return the name

public String getName() {

return name;

}

/**

* @param name the name to set

public void setName(String name) {

this.name = name;

}

/**

* @return the mobile

public String getMobile() {

return mobile;

}

/**

* @param mobile the mobile to set

public void setMobile(String mobile) {

this.mobile = mobile;

}

/**

package springapp.web.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import java.util.List;

import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.orm.hibernate3.SessionFactoryUtils;

/**

* @author PraveenKumar

public class mytableDao {

private JdbcTemplate jdbcTemplate;

/**

* @param sessionFactory the sessionFactory to set

public void setSessionFactory(JdbcTemplate sessionFactory) {

this.jdbcTemplate = sessionFactory;

}

/**

* @param jdbcTemplate the jdbcTemplate to set

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

this.jdbcTemplate = jdbcTemplate;

}

public List listAllValues(){ List<MyTable> mytble = new ArrayList(); try { String selectSQL="select * from mytable ;"; // pstmt=conn.prepareStatement(selectSQL); //List<Map<String, Object>> rows = jdbcTemplate.queryForList(selectSQL, ); List<MyTable> rows = jdbcTemplate.query(selectSQL,new ResultSetRowMapper()); Iterator itrList=rows.iterator(); // for (Map row : rows) { // MyTable mytab=new MyTable(); // System.out.println(" "+row.get("ID") + "\t"+row.get("NAME")+"\t"+row.get("PHONENUMBER")); // // Object id= row.get("ID"); // mytab.setId(Integer.valueOf((String) row.get("ID")).intValue()); // mytab.setName((String)row.get("NAME")); // mytab.setMobile(row.get("PHONENUMBER")+" "); // mytble.add(mytab); // // } while(itrList.hasNext()){ MyTable mytab=(MyTable) itrList.next(); System.out.println(" "+mytab.getId()+"\t "+ mytab.getName()+"\t "+mytab.getMobile()); // mytab.setId(Integer.valueOf((String) row.get("ID")).intValue()); // mytab.setName((String)row.get("NAME")); // mytab.setMobile(row.get("PHONENUMBER")+" "); mytble.add(mytab); } } catch (Exception e) { e.printStackTrace(); } return mytble; }

}

/**

package springapp.web.dao;

import java.sql.ResultSet;

import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

/**

* @author PraveenKumar

public class ResultSetRowMapper implements RowMapper {

@Override

public Object mapRow(ResultSet rs, int rownum) throws SQLException {

MyTable mytable=new MyTable();

mytable.setId(rs.getInt(1));

mytable.setName(rs.getString(2));

mytable.setMobile(rs.getString(3));

return mytable;

}

</textarea>
helloPage.jsp

<%@page import="java.util.ArrayList"%>
<%@page import="java.util.List"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

Hello : ${message}
<br><br>
<ul>
<c:forEach items="${results}" var="mytable">
${mytable.id} ${mytable.name}: ${mytable.mobile}
<br />
</c:forEach>
</ul>

build.properties

# Ant properties for building the springapp

appserver.home=D:/Praveen/sw/Tomcat_8_24

# for Tomcat 5 use $appserver.home}/server/lib

# for Tomcat 6 use $appserver.home}/lib

appserver.lib=${appserver.home}/lib

deploy.path=${appserver.home}/webapps

tomcat.manager.url=http://localhost:8080/manager

tomcat.manager.username=tomcat

tomcat.manager.password=password

build. xml

<?xml version="1.0"?>

<project name="SPMVC" basedir="." default="usage">
<property file="build.properties"/>

<property name="src.dir" value="src"/>
<property name="web.dir" value="war"/>
<property name="build.dir" value="${web.dir}/WEB-INF/classes"/>
<property name="name" value="springapp"/>

<path id="master-classpath">
<fileset dir="${web.dir}/WEB-INF/lib">
<include name="*.jar"/>
</fileset>



<fileset dir="${appserver.lib}">
<include name="servlet*.jar"/>
</fileset>
<pathelement path="${build.dir}"/>
</path>

<target name="usage">
<echo message=""/>
<echo message="${name} build file"/>
<echo message="-----------------------------------"/>
<echo message=""/>
<echo message="Available targets are:"/>
<echo message=""/>
<echo message="build --> Build the application"/>
<echo message="deploy --> Deploy application as directory"/>
<echo message="deploywar --> Deploy application as a WAR file"/>
<echo message="install --> Install application in Tomcat"/>
<echo message="reload --> Reload application in Tomcat"/>
<echo message="start --> Start Tomcat application"/>
<echo message="stop --> Stop Tomcat application"/>
<echo message="list --> List Tomcat applications"/>
<echo message=""/>
</target>

<target name="build" description="Compile main source tree java files">
<mkdir dir="${build.dir}"/>
<javac destdir="${build.dir}" source="1.5" target="1.5" debug="true"
deprecation="false" optimize="false" failonerror="true">
<src path="${src.dir}"/>
<classpath refid="master-classpath"/>
</javac>
</target>

<target name="deploy" depends="build" description="Deploy application">
<copy todir="${deploy.path}/${name}" preservelastmodified="true">
<fileset dir="${web.dir}">
<include name="**/*.*"/>
</fileset>
</copy>
</target>

<target name="deploywar" depends="build" description="Deploy application as a WAR file">
<war destfile="${name}.war"
webxml="${web.dir}/WEB-INF/web.xml">
<fileset dir="${web.dir}">
<include name="**/*.*"/>
</fileset>
</war>
<copy todir="${deploy.path}" preservelastmodified="true">
<fileset dir=".">
<include name="*.war"/>
</fileset>
</copy>
</target>





<path id="catalina-ant-classpath">


<fileset dir="D:/Praveen/sw/ant">
<include name="*ant*.jar"/>
</fileset>
</path>

<taskdef name="install" classname="org.apache.catalina.ant.InstallTask">
<classpath refid="catalina-ant-classpath"/>
</taskdef>
<taskdef name="reload" classname="org.apache.catalina.ant.ReloadTask">
<classpath refid="catalina-ant-classpath"/>
</taskdef>
<taskdef name="list" classname="org.apache.catalina.ant.ListTask">
<classpath refid="catalina-ant-classpath"/>
</taskdef>
<taskdef name="start" classname="org.apache.catalina.ant.StartTask">
<classpath refid="catalina-ant-classpath"/>
</taskdef>
<taskdef name="stop" classname="org.apache.catalina.ant.StopTask">
<classpath refid="catalina-ant-classpath"/>
</taskdef>

<target name="install" description="Install application in Tomcat">
<install url="${tomcat.manager.url}"
username="${tomcat.manager.username}"
password="${tomcat.manager.password}"
path="/${name}"
war="${name}"/>
</target>

<target name="reload" description="Reload application in Tomcat">
<reload url="${tomcat.manager.url}/html"
username="${tomcat.manager.username}"
password="${tomcat.manager.password}"
path="/${name}"/>
</target>

<target name="start" description="Start Tomcat application">
<start url="${tomcat.manager.url}"
username="${tomcat.manager.username}"
password="${tomcat.manager.password}"
path="/${name}"/>
</target>

<target name="stop" description="Stop Tomcat application">
<stop url="${tomcat.manager.url}"
username="${tomcat.manager.username}"
password="${tomcat.manager.password}"
path="/${name}"/>
</target>

<target name="list" description="List Tomcat applications">
<list url="${tomcat.manager.url}"
username="${tomcat.manager.username}"
password="${tomcat.manager.password}"/>
</target>



</project>

springapp-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:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">


<context:component-scan base-package="springapp.web"></context:component-scan>
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver" >
<property name="prefix" value="/WEB-INF/jsp/"></property>
<property name="suffix" value=".jsp"></property>

</bean>
<bean id="ds" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="org.sqlite.JDBC"></property>
<property name="url" value="jdbc:sqlite:D:/Praveen/sw/SQL_LITE/test.db"></property>
<property name="initialSize" value="2" />
<property name="maxActive" value="20" />
<property name="maxIdle" value="5" />
<property name="poolPreparedStatements" value="true" />
</bean>





<bean id="jdbctemp" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="ds"></property>
</bean>
<bean id="edao" class="springapp.web.dao.mytableDao">
<property name="jdbcTemplate" ref="jdbctemp"></property>
</bean>

</beans>

web.xml

<?xml version="1.0" encoding="UTF-8"?>

<web-app version="2.4"
xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd" >

<servlet>
<servlet-name>springapp</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>

<servlet-mapping>
<servlet-name>springapp</servlet-name>
<url-pattern>*.html</url-pattern>
</servlet-mapping>

<welcome-file-list>
<welcome-file>
index.jsp
</welcome-file>
</welcome-file-list>

</web-app>

Search This Blog

Java

Spring MVC with Sqlite sample

Comments

Popular posts from this blog

mysqldump

Struts Tutorial Page