玩轉spring boot 結合AngularJs和JDBC（4）

2019-11-26 13:19:43

字體：大中小

來源：轉載

供稿：網友

參考官方例子：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>

二、編寫類文件：

修改App.java

package com.github.carter659.spring04;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;/** * 博客出處：http://www.cnblogs.com/GoodHelper/ *  * @author 劉冬 * */@SpringBootApplicationpublic 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 劉冬 * */@Repositorypublic 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;@Controllerpublic 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="http://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 >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 >點擊訪問原版博客</a></body></html>

使用angularjs的ajax調用spring boot mv的后臺方法。

四、數據庫連接

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

spring.datasource.initialize=falsespring.datasource.url=jdbc:mysql://localhost:3306/demospring.datasource.username=rootspring.datasource.password=spring.datasource.driver-class-name=com.mysql.jdbc.Driver

完整的結構為：