玩转spring boot——结合AngularJs和JDBC

参考官方例子：http://spring.io/guides/gs/relational-data-access/

一、项目准备

在建立mysql数据库后新建表“t_order”

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for `t_order`

-- ----------------------------

DROP TABLE IF EXISTS `t_order`;

CREATE TABLE `t_order` (

  `order_id` varchar(36) NOT NULL,

  `order_no` varchar(50) DEFAULT NULL,

  `order_date` datetime DEFAULT NULL,

  `quantity;` int(11) DEFAULT NULL,

  PRIMARY KEY (`order_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of t_order

-- ----------------------------

修改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>com.github.carter659</groupId>

    <artifactId>spring04</artifactId>

    <version>0.0.1-SNAPSHOT</version>

    <packaging>jar</packaging>

    <name>spring04</name>

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

    <parent>

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

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

        <version>1.4.2.RELEASE</version>

    </parent>

    <properties>

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

        <java.version>1.8</java.version>

    </properties>

    <dependencies>

        <dependency>

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

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

        </dependency>

        <dependency>

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

            <artifactId>spring-boot-devtools</artifactId>

            <optional>true</optional>

        </dependency>

        <dependency>

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

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

        </dependency>

        <dependency>

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

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

        </dependency>

        <dependency>

            <groupId>mysql</groupId>

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

        </dependency>

        <dependency>

            <groupId>commons-dbcp</groupId>

            <artifactId>commons-dbcp</artifactId>

        </dependency>

    </dependencies>

    <build>

        <plugins>

            <plugin>

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

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

            </plugin>

        </plugins>

    </build>

</project>

pom.xml

二、编写类文件：

修改App.java

package com.github.carter659.spring04;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

/**

 * 博客出处：http://www.cnblogs.com/GoodHelper/

 *

 * @author 刘冬

 *

 */

@SpringBootApplication

public class App {

    public static void main(String[] args) {

        SpringApplication.run(App.class, args);

    }

}

新建数据载体类文件“Order.java”

package com.github.carter659.spring04;

import java.util.Date;

/**

 * 博客出处：http://www.cnblogs.com/GoodHelper/

 * @author 刘冬

 *

 */

public class Order {

    public String id;

    public String no;

    public Date date;

    public int quantity;

    /**

     * 省略 get set

     */

}

新建数据持久层类“OrderDao.java”

package com.github.carter659.spring04;

import java.util.ArrayList;

import java.util.List;

import java.util.UUID;

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

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.support.rowset.SqlRowSet;

import org.springframework.stereotype.Repository;

/**

 * 博客出处：http://www.cnblogs.com/GoodHelper/

 * @author 刘冬

 *

 */

@Repository

public class OrderDao {

    @Autowired

    private JdbcTemplate jdbcTemplate;

    public List<Order> findAll() {

        List<Order> list = new ArrayList<>();

        String sql = " select * from t_order ";

        SqlRowSet rows = jdbcTemplate.queryForRowSet(sql, new Object[] {});

        while (rows.next()) {

            Order order = new Order();

            list.add(order);

            order.id = rows.getString("order_id");

            order.no = rows.getString("order_no");

            order.date = rows.getDate("order_date");

            order.quantity = rows.getInt("quantity");

        }

        return list;

    }

    public Order get(String id) {

        Order order = null;

        String sql = " select * from t_order where order_id = ? ";

        SqlRowSet rows = jdbcTemplate.queryForRowSet(sql, new Object[] { id });

        while (rows.next()) {

            order = new Order();

            order.id = rows.getString("order_id");

            order.no = rows.getString("order_no");

            order.date = rows.getDate("order_date");

            order.quantity = rows.getInt("quantity");

        }

        return order;

    }

    public String insert(Order order) {

        String id = UUID.randomUUID().toString();

        String sql = " insert into t_order ( order_id , order_no , order_date , quantity ) values (?,?,?,?) ";

        jdbcTemplate.update(sql,

                new Object[] { id, order.no, new java.sql.Date(order.date.getTime()), order.quantity });

        return id;

    }

    public void update(Order order) {

        String sql = " update t_order set order_no = ? , order_date = ? , quantity = ? where order_id = ? ";

        jdbcTemplate.update(sql,

                new Object[] { order.no, new java.sql.Date(order.date.getTime()), order.quantity, order.id });

    }

    public void delete(String id) {

        String sql = " delete from t_order where order_id = ? ";

        jdbcTemplate.update(sql, new Object[] { id });

    }

}

其中对数据库的操作，顾名思义：

findAll-->查询所有数据

get-->通过id获取数据

insert-->插入数据

update-->修改数据

delete-->删除数据

新建控制器“MainController.java”

package com.github.carter659.spring04;

import java.util.HashMap;

import java.util.Map;

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

import org.springframework.stereotype.Controller;

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

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

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

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

import com.mysql.jdbc.StringUtils;

@Controller

public class MainController {

    @Autowired

    private OrderDao orderDao;

    @GetMapping("/")

    public String index() {

        return "index";

    }

    @PostMapping("/save")

    public @ResponseBody Map<String, Object> save(@RequestBody Order order) {

        Map<String, Object> result = new HashMap<>();

        if (StringUtils.isNullOrEmpty(order.id))

            order.id = orderDao.insert(order);

        else {

            orderDao.update(order);

        }

        result.put("id", order.id);

        return result;

    }

    @PostMapping("/get")

    public @ResponseBody Object get(String id) {

        return orderDao.get(id);

    }

    @PostMapping("/findAll")

    public @ResponseBody Object findAll() {

        return orderDao.findAll();

    }

    @PostMapping("/delete")

    public @ResponseBody Map<String, Object> delete(String id) {

        Map<String, Object> result = new HashMap<>();

        orderDao.delete(id);

        result.put("id", id);

        return result;

    }

}

三、新建thymeleaf模板

新建文件“src/main/resources/templates/index.html”

<!DOCTYPE html>

<html xmlns:th="http://www.thymeleaf.org">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

<title>玩转spring boot——结合JDBC</title>

<script src="//cdn.bootcss.com/angular.js/1.5.6/angular.min.js"></script>

<script type="text/javascript">

    /*<![CDATA[*/

    var app = angular.module('app', []);

    app.controller('MainController', function($rootScope, $scope, $http) {

        $scope.data = {};

        $scope.rows = [];

        //添加

        $scope.add = function() {

            $scope.data = {

                no : 'No.1234567890',

                quantity : 100,

                'date' : '2016-12-30'

            };

        }

        //编辑

        $scope.edit = function(id) {

            for ( var i in $scope.rows) {

                var row = $scope.rows[i];

                if (id == row.id) {

                    $scope.data = row;

                    return;

                }

            }

        }

        //移除

        $scope.remove = function(id) {

            for ( var i in $scope.rows) {

                var row = $scope.rows[i];

                if (id == row.id) {

                    $scope.rows.splice(i, 1);

                    return;

                }

            }

        }

        //保存

        $scope.save = function() {

            $http({

                url : '/save',

                method : 'POST',

                data : $scope.data

            }).success(function(r) {

                //保存成功后更新数据

                $scope.get(r.id);

            });

        }

        //删除

        $scope.del = function(id) {

            $http({

                url : '/delete?id=' + id,

                method : 'POST',

            }).success(function(r) {

                //删除成功后移除数据

                $scope.remove(r.id);

            });

        }

        //获取数据

        $scope.get = function(id) {

            $http({

                url : '/get?id=' + id,

                method : 'POST',

            }).success(function(data) {

                for ( var i in $scope.rows) {

                    var row = $scope.rows[i];

                    if (data.id == row.id) {

                        row.no = data.no;

                        row.date = data.date;

                        row.quantity = data.quantity;

                        return;

                    }

                }

                $scope.rows.push(data);

            });

        }

        //初始化载入数据

        $http({

            url : '/findAll',

            method : 'POST'

        }).success(function(rows) {

            for ( var i in rows) {

                var row = rows[i];

                $scope.rows.push(row);

            }

        });

    });

    /*]]>*/

</script>

</head>

<body ng-app="app" ng-controller="MainController">

    <h1>玩转spring boot——结合JDBC</h1>

    <h4>

        <a href="http://www.cnblogs.com/GoodHelper/">from 刘冬的博客</a>

    </h4>

    <input type="button" value="添加" ng-click="add()" />

    <input type="button" value="保存" ng-click="save()" />

    <br />

    <br />

    <h3>清单信息：</h3>

    <input id="id" type="hidden" ng-model="data.id" />

    <table cellspacing="1" style="background-color: #a0c6e5">

        <tr>

            <td>编号：</td>

            <td><input id="no" ng-model="data.no" /></td>

            <td>日期：</td>

            <td><input id="date" ng-model="data.date" /></td>

            <td>数量：</td>

            <td><input id="quantity" ng-model="data.quantity" /></td>

        </tr>

    </table>

    <br />

    <h3>清单列表：</h3>

    <table cellspacing="1" style="background-color: #a0c6e5">

        <tr

            style="text-align: center; COLOR: #0076C8; BACKGROUND-COLOR: #F4FAFF; font-weight: bold">

            <td>操作</td>

            <td>编号</td>

            <td>日期</td>

            <td>数量</td>

        </tr>

        <tr ng-repeat="row in rows" bgcolor='#F4FAFF'>

            <td><input ng-click="edit(row.id)" value="编辑" type="button" /><input

                ng-click="del(row.id)" value="删除" type="button" /></td>

            <td>{{row.no}}</td>

            <td>{{row.date}}</td>

            <td>{{row.quantity}}</td>

        </tr>

    </table>

    <br />

    <a href="http://www.cnblogs.com/GoodHelper/">点击访问原版博客</a>

</body>

</html>

index.html

使用angularjs的ajax调用spring boot mv的后台方法。

四、数据库连接

新建“src/main/resources/application.properties”文件

spring.datasource.initialize=false

spring.datasource.url=jdbc:mysql://localhost:3306/demo

spring.datasource.username=root

spring.datasource.password=

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

完整的结构为：