Frequently, I find myself needing to lookup and update values from a sql server table we use at work to store client specific coded values and their descriptions.
Mostly, I do the look-ups trying to standardize the descriptions across different clients by assigning a business specific code and description to logically similar client values.
Tired of looking up and updating these values straight from the database, I decided to create a simple app to cut down on time spent on editing sql statments.
The Angular data grid UIGrid is no longer in beta having been recently released. It looked like a good grid to test out. I also decided to use a spring boot mvc app for the back end.
The end product will look something like this, allowing filtering an multiple columns, and eventually updating values in place
Getting the Back end up and running:
I created a new spring starter project in eclipse.
I selected the options web and jpa.
To get the spring jpa repository to work, I had to do the following:
- Enter the database credentials in the application.properties
spring.datasource.url=jdbc:sqlserver://dbserver;database=dbname;integrated security=false
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.password=passwd
spring.datasource.username=username
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.DefaultNamingStrategy
- Generate the JPA class from the database table – I used the jpa support in eclipse provided by eclipselink
After opening the JPA view in eclipse, I could create a connection to my db. Then by doing a create new -> jpa entiy class from table, I was able to get my class with all the annotations
A few pointers here. If any columns in the table brought in is nullable and has null values, I had to make sure the datatype I used was the autoboxed type (example Integer and not the primitive int) since nulls cant apply to the primitive types.
Also , the defaultNamingStrategy property above was necessary to get Hibernate to stop applying its conventions (camelCase to under_scores) when figuring out my table name, otherwise it wouldn’t find my table and colulmn names.
- Create the JPA repository interface with the signature below – assuing my entity was called Code, mapped from the table Code that holds my coded values
@Repository
public interface CodeRepository extends CrudRepository <Code, Integer> {
List<Code>; findAll();
}
- Create my controller
@RestController
@RequestMapping("/getCodes")
public class CodeController {
@Autowired
private CodeRepository codeRepo;
@RequestMapping(method=RequestMethod.GET)
public List<Code> getCodes(){
List<Code> codes = codeRepo.findAll();
return codes ;
}
}
- Add the @EnableJpaRepositories annotation to the class that runs the main spring boot method
On running the main spring boot method, my endpoint was available on port 8080 served by the embedded tomcat, and a list of my codes were available to a browser at the ‘getCodes’ path.
Now I had to get UIGrid to request the list of codes and display them.
The html page ended up looking as follows:
<!doctype html>
<html ng-app="app">
<head>
<link rel="stylesheet"
href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" >
<link rel="stylesheet"
href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css" >
< link rel="stylesheet" href=" http://ui-grid.info/release/ui-grid.css"
type="text/css">
<link rel="stylesheet" href=" /resources/css/app.css " type=" text/css" >
</head>
<body>
<div class="container"ng-controller="DecodeCtrl">
< div id=" gridCode" ui-grid=" CodeGridOptions" class="grid" ui-grid-resize-columns ui-grid-pagination ui-grid-auto-resize ui-grid-selection> </div >
</div>
<script src= " http://ajax.googleapis.com/ajax/libs/angularjs/1.4.3/angular.js " > < /script>
<script src= " http://ajax.googleapis.com/ajax/libs/angularjs/1.4.3/angular-touch.js " > < /script>
<script src= "http://ajax.googleapis.com/ajax/libs/angularjs/1.4.3/angular-animate.js"> </script>
<script src= "http://ui-grid.info/docs/grunt-scripts/csv.js"> < /script>
<script src= "http://ui-grid.info/docs/grunt-scripts/pdfmake.js"> < /script>
<script src= "http://ui-grid.info/docs/grunt-scripts/vfs_fonts.js"> < /script>
<script src= "http://ui-grid.info/release/ui-grid.js"> < /script>
<script src= "/resources/js/app.js " > < /script >
</body>
</html>
And the angularjs javascript to provide the module, controller and directives
var app = angular.module('app', ['ngTouch', 'ui.grid','ui.grid.resizeColumns','ui.grid.pagination',
'ui.grid.autoResize','ui.grid.selection']);
app.controller('CodeCtrl', ['$scope', '$http','$log',function ($scope,$http,$log) {
$http.get('/getCodes').success(function(data){
$scope.CodeGridOptions.data = data;
});
$scope.CodeGridOptions = {
enableFiltering: true,
paginationPageSizes: [25, 50, 75],
paginationPageSize: 25,
enableRowHeaderSelection:false,
multiSelect:false,
columnDefs: [{field:'ID',width:30},
{field:'Client',width:30},
{field:'Group',width:30},
{field:'Category',width:200},
{field:'Code',width:200},
{field:'Description',width:200},
{field:'MappedCode',width:200},
{field:'MappedDescription',width:200},
{field:'VendorID',width:30} ]
}
}]);
kodesnippetsnstuff 