8.Spring-Boot之SpringJdbcTemplate整合Freemarker

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

<modelVersion>4.0.0</modelVersion>


<groupId>springboot</groupId>

<artifactId>testSpringBoot</artifactId>

<version>0.0.1-SNAPSHOT</version>

<packaging>war</packaging>



<name>testSpringBoot</name>

<url>http://maven.apache.org</url>



<properties>

<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

</properties>

<!-- 继承父包 -->

<parent>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-parent</artifactId>

<version>1.5.2.RELEASE</version>

<relativePath></relativePath>

</parent>

<dependencies>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-web</artifactId>

</dependency>

<!-- 配置解析jsp -->

<!-- <dependency> <groupId>org.apache.tomcat.embed</groupId> <artifactId>tomcat-embed-jasper</artifactId>

<scope>provided</scope> </dependency> <dependency> <groupId>javax.servlet</groupId>

<artifactId>jstl</artifactId> </dependency> -->



<!--jdbc -->

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-jdbc</artifactId>

</dependency>

<!--jpa的jar包 ，操作数据库的，类似hibernate -->

<!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId>

</dependency> -->

<!-- Spring Boot Mybatis 依赖 -->

<!-- <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId>

<version>1.2.0</version> </dependency> -->

<!--mysql驱动 -->

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>



<dependency>

<groupId>com.alibaba</groupId>

<artifactId>druid</artifactId>

<version>1.0.25</version>

</dependency>

<dependency>

<groupId>org.apache.commons</groupId>

<artifactId>commons-lang3</artifactId>

<version>3.2</version>

</dependency>

<!--thymeleaf模板jar，是很不错的html数据传递取值，类似jsp的jstl -->

<!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId>

</dependency> -->

<!-- log4j -->

<!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-log4j</artifactId>

</dependency> -->

<!--restful风格 -->

<!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-rest</artifactId>

</dependency> -->

<!--aop -->

<!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId>

</dependency> -->

<!-- freemarker -->

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-freemarker</artifactId>

</dependency>

<!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-amqp</artifactId>

</dependency> -->

<!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-redis</artifactId>

</dependency> -->

<!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-logging</artifactId>

</dependency> -->

<!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-security</artifactId>

</dependency> -->

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-test</artifactId>

</dependency>



<!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-rest</artifactId>

</dependency> -->



<dependency>

<groupId>junit</groupId>

<artifactId>junit</artifactId>

<scope>test</scope>

</dependency>

</dependencies>

<!--maven的插件 -->

<build>

<plugins>

<plugin>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-maven-plugin</artifactId>

</plugin>

</plugins>

<pluginManagement>

<plugins>

<plugin>

<!-- 配置java版本 不配置的话默认父类配置的是1.6 -->

<artifactId>maven-compiler-plugin</artifactId>

<configuration>

<source>1.7</source>

<target>1.7</target>

</configuration>

</plugin>

<!-- 配置Tomcat插件 -->

<plugin>

<groupId>org.apache.tomcat.maven</groupId>

<artifactId>tomcat7-maven-plugin</artifactId>

<version>2.2</version>

</plugin>


</plugins>

</pluginManagement>

</build>

</project>

package com.niugang;



import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import org.springframework.boot.web.support.SpringBootServletInitializer;

//解决包tomcat冲突

//@EnableAutoConfiguration(exclude={WebMvcAutoConfiguration.class})

//组件扫描

//@ComponentScan("com.niugang.controller")

//springboot注解

//springboot1.2+之后用@SpringBootApplication替代了三个注解

//@SpringBootApplication // same as @Configuration @EnableAutoConfiguration @ComponentScan //ComponentScan 也会自动扫描@configuration注解

@SpringBootApplication



public class Application extends SpringBootServletInitializer{

public static void main(String[] args) {

SpringApplication.run(Application.class,args);

}

}

#web项目名称

server.contextPath=/myweb

#配置freemaker

spring.freemarker.template-loader-path=/WEB-INF/view

spring.freemarker.cache=false

spring.freemarker.charset=UTF-8

spring.freemarker.check-template-location=true

spring.freemarker.content-type=text/html

spring.freemarker.expose-request-attributes=true

spring.freemarker.expose-session-attributes=true

spring.freemarker.request-context-attribute=request

spring.freemarker.suffix=.html

#引入jdbc相关配置

spring.profiles.active=jdbc

#修改默认HTTP编码，之前是在web.xml中过滤的，两个必须同时使用

spring.http.enconding.charset=UTF-8

spring.http.encoding.force=true

# 驱动配置信息

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

spring.datasource.url = jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8

spring.datasource.username = root

spring.datasource.password = 123456

spring.datasource.driverClassName = com.mysql.jdbc.Driver

#连接池的配置信息

## 初始化大小，最小，最大

spring.druid.initialSize=5

spring.druid.minIdle=5

spring.druid.maxActive=20

## 配置获取连接等待超时的时间

spring.druid.maxWait=60000

# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒

spring.druid.timeBetweenEvictionRunsMillis=60000

# 配置一个连接在池中最小生存的时间，单位是毫秒

spring.druid.minEvictableIdleTimeMillis=300000

spring.druid.validationQuery=SELECT 1 FROM DUAL

spring.druid.testWhileIdle=true

spring.druid.testOnBorrow=false

spring.druid.testOnReturn=false

spring.druid.poolPreparedStatements=true

spring.druid.maxPoolPreparedStatementPerConnectionSize=20

# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，‘wall‘用于防火墙

spring.druid.filters=stat,wall,log4j

# 通过connectProperties属性来打开mergeSql功能；慢SQL记录

spring.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

package com.niugang.entity;

public class User {

private int id;

private String name;

private Integer age;

private String phone;

private String password;

public String getPassword() {
return password;
}

public void setPassword(String password) {

this.password = password;

}

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {

this.name = name;

}



public Integer getAge() {
return age;
}

public void setAge(Integer age) {

this.age = age;

}

public String getPhone() {

return phone;

}

public void setPhone(String phone) {

this.phone = phone;

}

}

package com.niugang.entity;

import java.sql.ResultSet;

import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class UserMapper implements RowMapper<User>{

public User mapRow(ResultSet rs, int rowNum) throws SQLException {

User user = new User();

user.setId(rs.getInt("id"));

user.setName(rs.getString("name"));

user.setAge(rs.getInt("age"));

user.setPhone(rs.getString("phone"));

user.setPassword(rs.getString("password"));

return user;

}

}

package com.niugang.dao;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Repository;

import com.niugang.entity.User;

import com.niugang.entity.UserMapper;



@Repository

public class UserDao {



@Resource

private JdbcTemplate jdbcTemplate;

public List<User> queryList() {

return jdbcTemplate.query("select * from user",new UserMapper());

}

public void save(User user){

jdbcTemplate.update("insert into user (name,password,age,phone) values(?,?,?,?)",new Object[]{user.getName(),user.getPassword(),user.getAge(),user.getPhone()});

}

public User get(int id) {

List<User> list=jdbcTemplate.query("select * from user where id=?",new Object[]{id},new UserMapper());

if(list!=null&&list.size()>0){

return list.get(0);

}

return null;

}

public void delete(int id) {

jdbcTemplate.execute("delete from user where id ="+id);

}



}

package com.niugang.service;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.niugang.dao.UserDao;

import com.niugang.entity.User;

@Service

public class UserService {

@Resource

private UserDao userDao;

public List<User> queryList(){

return userDao.queryList();

}

public void save(User user){

userDao.save(user);

}

public User get(int id){

return userDao.get(id);

}

public void delete(int id){

userDao.delete(id);;

}

}

package com.niugang.controller;

import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;

import org.springframework.stereotype.Controller;

import org.springframework.ui.ModelMap;

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

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

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

import com.niugang.entity.User;

import com.niugang.service.UserService;

@Controller

public class IndexController {

@Resource

private UserService userService;

/**

* 跳转到登录页面

*

* @param map

* @return

*/

@RequestMapping(value = "/login", method = RequestMethod.GET)

public String toLogin(ModelMap map) {

return "login";

}



/**

* 登录信息校验

*

* @param map

* @return

*/

@RequestMapping(value = "/login", method = RequestMethod.POST)

public String login(ModelMap map, String name,String password) {



if (StringUtils.isNotBlank(name)&& !name.equals("admin") && StringUtils.isNotBlank(password)

&& !password.equals("123456")) {

map.put("errorMessage", "用户名或密码错误");

return "login";

}



return "redirect:index";

}

/**

* 跳转到index页面

*

* @return

*/

@RequestMapping(value = "/index")

public String index(ModelMap map) {

List<User> list = userService.queryList();

map.put("users", list);

return "index";

}

/**

* 详情页面

* @param id

* @param map

* @return

*/

@RequestMapping(value = "/detail/{id}")

public String detail(@PathVariable(value="id") Integer id,ModelMap map){

User user = userService.get(id);

map.put("user", user);

return "detail";

}

/**

* 删除

* @param id

* @return

*/

@RequestMapping(value = "/delete/{id}")

public String delete(@PathVariable(value="id") Integer id){

userService.delete(id);

return "redirect:/index";

}

/**

* 跳转到添加页面

* @param map

* @return

*/

@RequestMapping(value = "/save",method = RequestMethod.GET)

public String toSave(ModelMap map) {

return "add";

}

/**

* 保存添加信息

* @param user

* @return

*/

@RequestMapping(value = "/save",method = RequestMethod.POST)

public String save(User user,ModelMap map){

if(StringUtils.isBlank(user.getName())){

map.put("error", "用户名不能为空");

return "add";

}

if(user.getAge()==null){

map.put("error", "非法年龄");

return "add";

}

if(StringUtils.isBlank(user.getPhone())){

map.put("error", "手机号不能为空");

return "add";

}

user.setPassword("123456");

userService.save(user);

return "redirect:/index";

}

}

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
       <h2>spring boot</h2>
      <#if errorMessage??> 
         ${errorMessage}
       </#if>
        <form action="login" method=‘post‘>
           用户名:<input type="text" name="name"><p>
            密码:<input type="password" name="password"><p>
            <input type="submit" value="提交">
        </form>
        <h6>用户名admin密码123456</h6>
</body>
</html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style type="text/css">
table {
border-collapse: collapse;
margin: 0 auto;
}
table, td, th {
border: 1px solid black;
padding: 15px;
}
</style>

</head>

<body>
     <input type="button" value="添加" onclick="add();">
<table>
<thead>
<tr>
<th>用户名</th>
<th>年龄</th>
<th>电话</th>
<th>操作</th>
</tr>


</thead>
<tbody>
<#if users??> <#list users as user>
<tr>
<td>${user.name}</td>
<td>${user.age}</td>
<td>${user.phone}</td>
<td><a href="javascript:void(0)" onclick="edit(‘${user.id}‘)" >编辑</a>
<a href="javascript:void(0)" onclick="del(‘${user.id}‘)">删除</a>
<a href="javascript:void(0)" onclick="detail(‘${user.id}‘)">详情</a>
</td>
</tr>
</#list> </#if>
</tbody>
</table>
<script type="text/javascript">
function edit(id){
alert(id);
}
function del(id){
          var flag=confirm("你确定要删除此选项吗");
          if(flag==true){
                  window.location.href="delete/"+id;
          }
}
function detail(id){
window.location.href="detail/"+id;
}
function add(){
window.location.href="save";
}


 </script>
</body>
</html>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Document</title>
</head>
<body>
  <h2>详情页面</h2>
  用户名：<input type="text" readonly value="${user.name!‘‘}">
  年龄：<input type="text" readonly value="${user.age!‘‘}">
  电话：<input type="text" readonly value="${user.phone!‘‘}">
</body>
</html>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Document</title>
</head>
<body>
  <h2>添加</h2>
      <#if error??> 
         ${error}
       </#if>
   <form action="save" method=‘post‘>
           用户名:<input type="text" name="name"><p>
            年龄:<input type="text" name="age"><p>
            手机号:<input type="text" name="phone"><p>
            <input type="submit" value="提交">
        </form>
</body>

</html>