SpringMVC+C3P0+dbUtils实现增删改查
【web.xml】
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<display-name></display-name>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<!-- 注册spring核心中央调度器 -->
<servlet>
<servlet-name>springmvc</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:springmvc.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>springmvc</servlet-name>
<url-pattern>*.action</url-pattern>
</servlet-mapping>
<!-- 注册spring核心编码过滤器 -->
<filter>
<filter-name>CharacterEncodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>CharacterEncodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>
【springmvc.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:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
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
">
<context:component-scan base-package="com.athl" />
</beans>
【c3p0-config.xml】
<c3p0-config>
<default-config>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/athl_ajax</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">3</property>
<!-- 连接的最大空闲时间 单位秒 默认是0-代表永远不会断开连接 超过设定时间的空闲连接将会断开 -->
<property name="maxIdleTime">30</property>
<!-- 连接池中拥有的最大连接数 默认值为15个 -->
<property name="maxPoolSize">20</property>
<!-- 连接池中保持的最小连接数 默认值为3个-->
<property name="minPoolSize">3</property>
<!-- 将连接池的连接数保持在minpoolsize 必须小于maxIdleTime设置 默认值为0代表不处理 单位秒 -->
<property name="maxIdleTimeExcessConnections">15</property>
</default-config>
</c3p0-config>
【ConnectionUtil】
package com.athl.utils;
import org.apache.commons.dbutils.QueryRunner;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class ConnectionUtil {
private static ComboPooledDataSource cpds = new ComboPooledDataSource();
public static QueryRunner getQueryRunner(){
return new QueryRunner(cpds);
}
public static void main(String[] args) {
System.out.println(ConnectionUtil.getQueryRunner());
}
}
【dao】
package com.athl.dao;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.stereotype.Component;
import com.athl.entity.Person;
import com.athl.utils.ConnectionUtil;
@Component(value="dao")
public class PersonDao {
private QueryRunner qr= ConnectionUtil.getQueryRunner();
/**
* 添加
* @param p
* @throws SQLException
*/
public void add(Person p) throws SQLException{
String sql="insert into person(name,age) values(?,?)";
qr.update(sql, p.getName(),p.getAge());
}
/**
* 修改
* @param p
* @throws SQLException
*/
public void update(Person p) throws SQLException{
String sql="update person set name=?,age=? where id=?";
qr.update(sql, p.getName(),p.getAge(),p.getId());
}
/**
* 删除
* @param id
* @throws SQLException
*/
public void delete(int id) throws SQLException{
String sql="delete from person where id=?";
qr.update(sql,id);
}
/**
* 查询所有
* @return
* @throws SQLException
*/
public List<Person> query() throws SQLException{
String sql="select * from person";
return qr.query(sql, new BeanListHandler<Person>(Person.class));
}
/**
* 根据id查询
* @param id
* @return
* @throws SQLException
*/
public Person queryById(int id) throws SQLException{
String sql="select * from person where id=?";
return qr.query(sql, new BeanHandler<Person>(Person.class),id);
}
/**
* 模糊查询
* @param search
* @return
* @throws SQLException
*/
public List<Person> queryLike(String search) throws SQLException{
String sql="select * from person where name like ? or age like ?";
return qr.query(sql, new BeanListHandler<Person>(Person.class),"%"+search+"%","%"+search+"%");
}
}
【PersonController】
package com.athl.controller;
import javax.annotation.Resource;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import com.athl.dao.PersonDao;
import com.athl.entity.Person;
@Controller
public class PersonController {
@Resource(name="dao")
private PersonDao dao;
@RequestMapping(value="/getAll.action")
public String getAll(Model m) throws Exception{
m.addAttribute("list", dao.query());
return "/WEB-INF/jsp/list.jsp";
}
@RequestMapping(value="/add.action")
public String add(Person p,Model m) throws Exception{
dao.add(p);
return "redirect:/getAll.action";
}
@RequestMapping(value="/update.action")
public String update(Person p,Model m) throws Exception{
dao.update(p);
return "redirect:/getAll.action";
}
@RequestMapping(value="/delete.action")
public String delete(Person p,Model m) throws Exception{
dao.delete(p.getId());
return "redirect:/getAll.action";
}
@RequestMapping(value="/queryLike.action")
public String getAll(Model m,String search) throws Exception{
m.addAttribute("list", dao.queryLike(search));
return "/WEB-INF/jsp/list.jsp";
}
@RequestMapping(value="/queryById.action")
public String queryById(Model m,int id) throws Exception{
m.addAttribute("p", dao.queryById(id));
return "/WEB-INF/jsp/update.jsp";
}
}