spring中使用spring mvc jdbc操作数据库

初次接触Java Spring MVC, 正准备选个适合自己的orm用, Hibernate我感觉还是有点复杂, Mybatis一样如此.

这是我最后确定的orm, spring自带的jdbc, 蛮适合我!

先看下我的配置

web.xml

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

<web-app id="WebApp_ID" version="3.0" 
	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" 
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">

  <display-name>JavaWebDemo</display-name>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
  <context-param>
    <param-name>contextConfigLocation</param-name>
    <param-value>/WEB-INF/springmvc-servlet.xml</param-value>
  </context-param>
  <listener>
    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
  </listener>
  <servlet>
    <servlet-name>springmvc</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    <init-param>
      <param-name>contextConfigLocation</param-name>
      <param-value>/WEB-INF/springmvc-servlet.xml</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>springmvc</servlet-name>
    <url-pattern>/</url-pattern>
  </servlet-mapping>
</web-app>

springmvc-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:mvc="http://www.springframework.org/schema/mvc" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:util="http://www.springframework.org/schema/util"
	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  
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd  
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd">

	<!-- 启动注解驱动的Spring MVC功能，注册请求url和注解POJO类方法的映射 -->
	<mvc:annotation-driven />
	<mvc:default-servlet-handler />
	<mvc:resources mapping="/static/**" location="/static/" />

	<!-- 启动包扫描功能，以便注册带有@Controller、@Service、@repository、@Component等注解的类成为spring的bean -->
	<context:component-scan base-package="com.controllers" />

	<!-- 完成请求和注解POJO的映射 -->
	<bean
		class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter" />

	<!-- 对模型视图名称的解析，在请求时模型视图名称添加前后缀 -->
	<bean
		class="org.springframework.web.servlet.view.InternalResourceViewResolver"
		p:prefix="/views/" p:suffix=".jsp" />

	<!-- <bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver"> 
		<property name="prefix" value="/views/"></property> <property name="suffix" 
		value=".jsp"></property> </bean> -->

	<!-- <bean name="/hello" class="com.controllers.HomeController"></bean> -->

	<!--配置拦截器, 多个拦截器,顺序执行 -->
	<mvc:interceptors>
		<mvc:interceptor>
			<!-- 匹配的是url路径， 如果不配置或/**,将拦截所有的Controller -->
			<mvc:mapping path="/home/sqlite" />
			<bean class="com.filter.LoginInterceptor"></bean>
		</mvc:interceptor>
		<!-- 当设置多个拦截器时，先按顺序调用preHandle方法，然后逆序调用每个拦截器的postHandle和afterCompletion方法 -->
	</mvc:interceptors>


	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="org.sqlite.JDBC" />
		<property name="url" value="jdbc:sqlite:/data.db" />
	</bean>
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource">
			<ref local="dataSource" />
		</property>
	</bean>
</beans>

依赖的jar包

TestController.java

package com.controllers;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import com.common.JsonHelper;
import com.entity.UsersEntity;

@Controller
@RequestMapping(value = "test")
public class TestController {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@ResponseBody
	@RequestMapping(value = "/index")
	public String Index() {
		final UsersEntity users = new UsersEntity();
		jdbcTemplate.query("select * from Users", new Object[] {}, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				users.setID(rs.getInt("id"));
				users.setUsername(rs.getString("username"));
				users.setPassword(rs.getString("password"));
				users.setStatus(rs.getInt(rs.getInt("status")));
				users.setIsDelete(rs.getBoolean("isdelete"));
			}
		});
		
		return JsonHelper.toJson(users);
	}

	@ResponseBody
	@RequestMapping(value = "list")
	public String List() {
		String sql = "select id,username,password,status,isdelete from Users";
		List<UsersEntity> list = jdbcTemplate.query(sql, new Object[] { }, new RowMapper<UsersEntity>() {
			@Override
			public UsersEntity mapRow(ResultSet rs, int arg1) throws SQLException {
				UsersEntity users = new UsersEntity();
				users.setID(rs.getInt("id"));
				users.setUsername(rs.getString("username"));
				users.setPassword(rs.getString("password"));
				users.setStatus(rs.getInt(rs.getInt("status")));
				users.setIsDelete(rs.getBoolean("isdelete"));
				return users;
			}
		});
		return JsonHelper.toJson(list);
	}
	
	@ResponseBody
	@RequestMapping(value = "/update")
	public String Update() {
		String sqlStr = "insert into users(username,password,status,isdelete) values(?,?,?,?)";
		Object[] params = new Object[] { "test", "test", 1, 0};
		return jdbcTemplate.update(sqlStr, params) + "";
	}
}

src下的主要架构

WebContent的主要目录