spring+maven+freemarker+servlet项目连接数据库

pom.xml依赖的jar包

<dependencies>
	<!-- spring框架jar包 -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-context</artifactId>
		<version>4.3.12.RELEASE</version>
	</dependency>
	<!-- 用于连接数据库的jar包 -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-jdbc</artifactId>
		<version>4.3.12.RELEASE</version>
	</dependency>
	<!-- 使用web项目的jar包 -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-web</artifactId>
		<version>4.3.12.RELEASE</version>
	</dependency>
	<!-- 数据库的jar包 -->
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>5.1.26</version>
	</dependency>
	<!-- freemarker的jar包 -->
	<dependency>
		<groupId>org.freemarker</groupId>
		<artifactId>freemarker</artifactId>
		<version>2.3.23</version>
	</dependency>
	<!-- jap使用的标签库的jar包 -->
	<dependency>
		<groupId>jstl</groupId>
		<artifactId>jstl</artifactId>
		<version>1.2</version>
	</dependency>
	<dependency>
		<groupId>taglibs</groupId>
		<artifactId>standard</artifactId>
		<version>1.1.2</version>
	</dependency>
</dependencies>

spring.xml文件配置

扫描注解
<context:component-scan base-package="lesson02"></context:component-scan>
读取properties文件
<context:property-placeholder location="classpath:/lesson02/jdbc/jdbcoracle.properties"/>

在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" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" version="2.5">
  <display-name>spring_web</display-name>
  <!--上下文参数
	spring.xml配置文件所在的位置
	<param-name>contextConfigLocation</param-name> 参数名为contextConfigLocation写死
  -->
  <context-param>
    <param-name>contextConfigLocation</param-name>
    <param-value>classpath:spring.xml</param-value>
  </context-param>
  <!-- 
	该监听器是ServletContext（相当于jsp中的application）的生命周期
	Tomcat启动时  contextInitialized 用于创建spring的容器WebApplicationContext 不需要new ClassPathXmlApplicationContext("lesson02/cycle/spring.xml");
	tomcat关闭时  contextDestroyed 调用容器的close的方法 关闭
  -->
  <listener>
    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
  </listener>
  
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  <!-- freemarker映射  -->
  <servlet>
    <servlet-name>freemarker</servlet-name>
    <servlet-class>freemarker.ext.servlet.FreemarkerServlet</servlet-class>
    <init-param>
      <param-name>TemplatePath</param-name>
      <param-value>/</param-value>
    </init-param>
    <init-param>
      <param-name>NoCache</param-name>
      <param-value>true</param-value>
    </init-param>
    <init-param>
      <param-name>ContentType</param-name>
      <param-value>text/html;UTF-8</param-value>
    </init-param>
    <init-param>
      <param-name>template_update_delay</param-name>
      <param-value>0</param-value>
    </init-param>
    <init-param>
      <param-name>default_encoding</param-name>
      <param-value>UTF-8</param-value>
    </init-param>
    <init-param>
      <param-name>number_format</param-name>
      <param-value>0.##</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>freemarker</servlet-name>
    <url-pattern>*.ftl</url-pattern>
  </servlet-mapping>
  
  <servlet>
    <description></description>
    <display-name>NewsController</display-name>
    <servlet-name>NewsController</servlet-name>
    <servlet-class>cn.et.web.controller.NewsController</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>NewsController</servlet-name>
    <url-pattern>/NewsController</url-pattern>
  </servlet-mapping>
  <servlet>
    <description></description>
    <display-name>AddService</display-name>
    <servlet-name>AddService</servlet-name>
    <servlet-class>cn.et.web.controller.AddService</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>AddService</servlet-name>
    <url-pattern>/AddService</url-pattern>
  </servlet-mapping>
</web-app>

JdbcTemplate的实例（使用Java创建）

package cn.et.web.conf;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
public class MyConf {
    @Value("${url}")
    private String url;
    @Value("${driverClass}")
    private String driverClassName;
    @Value("${username1}")
    private String userName;
    @Value("${password}")
    private String password;
    
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
    
    @Bean
    public DataSource dataSouce(){
        DriverManagerDataSource dmds=new DriverManagerDataSource();
        dmds.setUrl(url);
        dmds.setDriverClassName(driverClassName);
        dmds.setUsername(userName);
        dmds.setPassword(password);
        return dmds;
    }
}

实例

package cn.et.web.entity;


public class CC {
    private String c1;
    private String c2;
    public String getC1() {
        return c1;
    }
    public void setC1(String c1) {
        this.c1 = c1;
    }
    public String getC2() {
        return c2;
    }
    public void setC2(String c2) {
        this.c2 = c2;
    }
}

添加的servlet（调用service层）

package cn.et.web.controller;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

import cn.et.web.service.NewsService;

/**
 * Servlet implementation class AddService
 */
public class AddService extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public AddService() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	    WebApplicationContext ac=WebApplicationContextUtils.getWebApplicationContext(this.getServletContext());
        NewsService ns=(NewsService)ac.getBean("newsServiceImpl");
	    request.setCharacterEncoding("UTF-8");
	    response.setContentType("text/html;charset=UTF-8");
	    String c1=request.getParameter("c1");
		String c2=request.getParameter("c2");
		request.setAttribute("c1", c1);
		request.setAttribute("c2", c2);
        ns.add(c1, c2);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}
}

查询的servlet （调用service层）   servlet是由tomcat实例化的 tomcat不认识spring中的注解

package cn.et.web.controller;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

import cn.et.web.entity.CC;
import cn.et.web.service.NewsService;

/**
 * Servlet implementation class NewsController
 */
public class NewsController extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public NewsController() {
        super();
        // TODO Auto-generated constructor stub
    }

	
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		WebApplicationContext ac=WebApplicationContextUtils.getWebApplicationContext(this.getServletContext());
		NewsService ns=(NewsService)ac.getBean("newsServiceImpl");
		String name=request.getParameter("temp");
		if(name==null){
		    name="";
		}
		request.setAttribute("name", name);
		List<CC> queryNewsByContent = ns.queryNewsByContent(name);
		request.setAttribute("newsList", queryNewsByContent);
		request.getRequestDispatcher("/index.jsp").forward(request,response);
	
	}

	
	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

service层（调用到层）

package cn.et.web.service.impl;

import java.util.List;

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

import cn.et.web.dao.NewsDao;
import cn.et.web.entity.CC;
import cn.et.web.service.NewsService;
@Service
public class NewsServiceImpl implements NewsService {
    @Autowired
    private NewsDao dao;
    public List<CC> queryNewsByContent(String content) {
        if(content==null){
            content="";
        }
        return dao.queryNewsByContent(content);
    }
    
    public void add(String c1,String c2){
        if(c1==null){
            c1="";
        }
        if(c2==null){
            c2="";
        }
        
        dao.add(c1,c2);
    }
}

dao层

package cn.et.web.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


import org.omg.CORBA.PUBLIC_MEMBER;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import cn.et.web.dao.NewsDao;
import cn.et.web.entity.CC;
@Repository
public class NewsDaoImpl implements NewsDao {
    @Autowired
    private JdbcTemplate jdbc;
    //Map<String, Object>
    
    //查询
    public List<CC> queryNewsByContent(String content) {
        String sql="select * from cc where c1 like '%"+content+"%'";
        return jdbc.query(sql,new RowMapper<CC>(){

            public CC mapRow(ResultSet rs, int rowNum) throws SQLException {
                CC c=new CC();
               c.setC1(rs.getString("C1"));
               c.setC2(rs.getString("C2"));
               return c;
            }
        });
    }


    //新增
    public void add(String c1, String c2) {
       String sql="insert into cc (c1,c2) values('"+c1+"','"+c2+"')";
        jdbc.execute(sql);
    }
}

freemarker页面

<form action="NewsController" method="get">
	<input type='text' name="temp" value=""/>
	<input type='submit' value='查询'/><br/>
</form>
<a href="AddService.html">新增</a>
<#list newsList as temp>
	${temp.c1}
	${temp.c2}<br/>
</#list>

增加的页面

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<form action="AddService" method="post">
		请输入c1:<input type='text' name="c1"><br/>
		请输入c2:<input type='text' name="c2">
		<input type='submit' value='确定'/>
	</form>
</body>
</html>