JDBC Template and AngularJS with RESTful web application

Question

I am trying to populate an HTML table with data from an MS SQL Database. I have set my app.properties for the sql server and declared my jdbcTemplate in my .java file. I am entirely new to AngularJS and jdbcTemplate. Can someone please offer some example code or point me in the right direction?

Thank you in advance!!

table.html

<h2>Industry Maintenance SIC Table</h2>

<div ng-controller="tableController">

    <p>Click <a ng-click="loadObjects()">here</a> to load data.</p>

    <!-- table -->
     <table class="table table-bordered table-hover table-condensed">
        <tr style="font-weight: bold">
            <td style="width:40%">SIC Code</td>
            <td style="width:50%">SIC Code Description</td>
        </tr>
        <tr ng-repeat="object in objects">
            <td>{{object.code}}</td>
            <td>{{object.description}}</td>
        </tr>
    </table>

</div>

table.controller.js

(function () {
      'use strict';

      angular.module('support-dash.pi.table').controller('tableController', tableController);

      function tableController($scope, $http){

          $scope.objects = [];

          $scope.loadObjects = function(){

              ***DONT KNOW WHAT TO PUT HERE
          }


      };
})();

HelloResource.java

    package supportdash.api;

import javax.ws.rs.GET;
import javax.ws.rs.Path;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

@Path("hello")
public class HelloResource {

    @GET
    public String retrieve() {
        return "Hello";
    }

    @Autowired
    JdbcTemplate jdbcTemplate;

}

As for what you put there... You make a JDBC connection, and you populate the array with your data. Which, it's hard to really provide an accurate answer without knowing what you database schema looks like. — OneCricketeer
– OneCricketeer, Commented Aug 2, 2016 at 18:42
@cricket_007 I added my .java but I haven't really done anything there other than declare the template — user6387287
– user6387287, Commented Aug 2, 2016 at 18:43
I see now. So, step 1 -- You need to actually write a SQL query to get some data in the Java code. — OneCricketeer
– OneCricketeer, Commented Aug 2, 2016 at 18:44
And step 2 would be preferably formatting a JSON string output from those SQL results. Step 3 is making sure your web server works correctly when you hit /hello... And then you start building the Angular app using AJAX requests. — OneCricketeer
– OneCricketeer, Commented Aug 2, 2016 at 18:47

Lazar Lazarov · Accepted Answer · 2016-08-03 07:20:30Z

Lets strat from Sprin. You want a RESTful api so there is how a example controller should look like :

@Controller
   @RequestMapping("/hello")
   public class ShowController {

   @Autowired
   private YourRepository yr;

   @RequestMapping(value = "/getObjects", method = RequestMethod.GET)
       public List<Object> home(Model model) {
       List<Object> objects = yr.getObjects();
       return objects;
       }

}

Now it is better if you use services and in your controller @Autowired a service like YourService in which you call the repository and do every kind of logic over your data from the data base. But lets keep it as simple as we can.

Now YourRepository should look like this :

public interface YourRepository{
  public List<Object> getObjects();
}

and of course the implementation of our interface :

@Repository
public class YourRepositoryJdbc implements YourRepository{

   @Autowired
   private NamedParameterJdbcTemplate jdbcTmpl;

   public List<Object> getObjects(){

      StringBuilder sql = new StringBuilder();
      sql.append("SELECT ob.id, ob.name ");
      sql.append("FROM objects ob ");
      sql.append("WHERE ob.del_flag = 0 ");

      List<Object> allObjects = jdbcTmpl.query(sql.toString(), new ResultSetExtractor<List<Object>>(){ 
         @Override
         public List<Object> extractData(ResultSet rs) throws SQLException, DataAccessException {

            while(rs.next()) {
               Object local = new Object();
               local.setId(rs.getIng("id"));
               local.setName(rs.getString("name"));
               allObjects.add(local);
            }
            return allObjects;

         }
    }
}

Now this will work if you have a table named OBJECTS in your SQL. Also when you go to your browser and you write http://localhost:8080/hello/getObjects you should get a JSON object with your Objects.

Now from the client you can use Ajax requests to call the REST service :

    var getAllObjects = function() {
        var request = $http({
            url: 'http://localhost:8080/hello/getObjects',
            method: 'GET',
            params: {

            }
        });
        return request.then(function (data) {
            return data;
        }, function (error) {
            return error;
        });
    }
    $scope.loadObjects = getAllObjects();

It should look something like this.

Collectives™ on Stack Overflow

JDBC Template and AngularJS with RESTful web application

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related