8.Spring-Boot之SpringJdbcTemplate整合Freemarker
1.创建maven工程
2.包结构如下
3.pom.xml配置
<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>
4.创建启动类Application.java一般放在主包下，因为会有组件扫描
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);
}
}
5.创建application.properties
#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
6创建application-jdbc.properties和主的配置分开
# 驱动配置信息
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
7.实体类
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;
}
}
8.JdbcTemplate查询转换类
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;
}
}
9.dao层
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);
}
}
10.service层
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);;
}
}
11.controller层
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";
}
}
12.login.html
<!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>
13.index.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>
14.detail.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>
15.add.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>
16.启动项目
输入如下地址：
http://localhost:8080/myweb/login
成功后跳转页面：
详情页面：