Spring Boot: The #1 question my students are asking right now

This is a problem students run into with Spring Data & Spring Boot.

Today’s Question & Answer came in from a student in my Spring Boot Introduction course and it is one that I get a lot. This tells me that I probably need to address this in the course but it’s also an opportunity for me to share this with you. 

Spring Boot Questions

The question is: 

“I created a new application from scratch using the Web, Spring Data JPA & H2 dependencies. If I create an entity and then start the application up I don’t see the tables created in the H2 database for me. What is going wrong?”

SQL Server Exception – The Statement Did Not Return A Result Set

We are working on moving a fairly large Dynamic Java Web Project over to a Grails application. In this application we have a ton of stored procedures that we need to continue to use in our Grails app. Luckily it couldn’t be easier to call a stored proc in Groovy. If you look at the groovy.sql.Sql class there are a ton of methods for this. There are numerous calls methods but one of the easiest ways to get back a list is to use the rows method and pass in a gstring. In this example we are calling a stored procedure called getFooStoredPorc that takes 1 parameter and because this is a gstring we can include our variable in our call.

So I had this working for a ton of stored procedure calls already when I came across a very strange error yesterday.

This looks almost identical to the call I made before but this time the code was throwing an exception.

SqlServerException: The Statement Did Not Return A Result Set

SQL Server: Execute SQL Statement Shortcut

This is going to be pretty obvious to anyone who uses SQL Server on a regular basis but I am not one of those people. In almost every other SQL editor that I use you can place a semi colon after your statement and hit cntrl+enter to execute the query. In SQL Server I always end up trying that only to add a new line to my statement. Now you could hit the execute button but I really dislike the mouse.

Anyways, this whole post was to remind myself that there is a keyboard shortcut for this and to be precise there are 3 shortcuts for this. You can use cntrl+e, alt+x or F5. This will execute the selected query or if nothing is selected it will run the entire query window.

Create SQL Insert statements from a spreadsheet

I know this is probably old news to most but I was helping a friend out yesterday who didn’t know this little trick so I thought I would share it. While some of you may have access to production databases its pretty common that these servers are guarded by a DBA. If I get a huge spread sheet of data I can’t really import the data I need to send the sql statements to the dba and the script is run against the production database.

In this example I just got a spread sheet of 5 users that need to be imported into our users table.

Now for 5 users this is not a big deal but what about 100 or even a 1000 users. There is actually an easy way to create your insert statements using excel. First we will mark our D column as SQL. Next place click on the cell D1. What we are going to do is write a sql statement that will grab data from the columns a,b and c. First we write our normal insert statement but for the values we can evaluate the data in a cell using the following formula. The & is just used for concatenating.

Then we can use that same formula for every row in our sheet. Simply copy and then past that formula all the way down for as many records as you have and you will end up with something like this.

While this is great you should see the issue. The user id field is fine but we have no single quotes around our strings, but we can fix that using the concatenate function.

Now our insert statements look a lot better. Again, not the coolest thing in the world but it really helps out nicely in this spot.

Oracle default Date Format

Today I was trying to run this very basic query.

When I ran the query I got the following error.

>[Error] Script lines: 1-4 ————————–
ORA-01843: not a valid month 

For whatever reason it did not like the month. A little hunting around and I found some information about date formats in Oracle. Unless you set some type of client environment variable it will run off of the database default. If you need to find out what that is you can run the following sql.

Looking at the session parameters I was able to identify that the default NLS_DATE_FORMAT was set to ‘DD-MON-RR’. As I said before, I think you can change this in your environment but I just updated my query.

If anyone has something to add to this please feel free, I am a complete newb when it comes to Oracle.

Oracle on the fly

When I started my job the one thing that was new to me was Oracle. As a developer I am hear to tell you that you don’t need to be scared to learn it. While there are some major differences (especially in 8i) there is nothing overwhelming to learn. I am speaking from a developers standpoint, I am sure that a DBA would tell me its a different world but I am no DBA and I don’t even play one on TV.

The first thing I had to get used to was some terminology. If there was a physical database machine and I created different databases I would refer to each instance as a database. I guess its more common in this world to refer to each as a Schema. Again, maybe someone could fill me in on this but its just what I am noticing here. The first thing you need to do if find yourself a tool to manage your schema’s in. I am coming from the world of MSSQL so I am really use to enterprise manager. Here are some alternatives that can get you up and going quickly.

  • Oracle Developer – This is a free tool from oracle and it has a nice familiar feel to it.
  • Orace SQL Plus – Another free tool from Oracle, its a command line only tool
  • TOAD – A commercial tool that is quite popular. I installed it and it just seemed a little over the top for me.
  • Aqua Data Studio This is easily my favorite new tool in my tool belt and I plan on devoting a whole writeup on it so stay tuned.

I have a ton of things that you might run into as a developer so stay tuned for some tips.

ORDER BY NULL Dates First

I came across a little problem today that I would like to share with you. I have an application that keeps track of emails sent to customers. My list method will return a query of emails sent. At first I was ordering them by DateSent DESC so that I would be able to view the emails from the last 1 sent. I quickly realized that I have emails that are drafts that have not yet been sent. I would like these emails to show up before any of the sent emails, so how do you do it.



SELECT subject, datesent
FROM notifications
ORDER BY ISNULL(dateSent,GETDATE()) DESC

The ISNULL replaces NULL with the specified replacement value. I can use todays date because no emails will be sent from the future.

Comments In SQL

It seems that alot of people dont realize that you can place comments in SQL queries just like you can in html and cfml. Here is a quick example of single and multi line comments. I would like to see more developers using comments in their sql code just as you use them in your day to day coding.


/*
Query: getUsers()
Description: Just a quick list of students that are active or pending
Author: Daniel Vega
*/
SELECT fname,lname,email
FROM users
/*active clients */
WHERE status = 'active'
/* or pending */
OR status = 'pending'

SQL What do I do here?

So I ran into a problem last night. I had a bunch of old data that I needed to add a quick search to. One of the fields was a due date. We wanted to be able to search by due date. The main problem here is that all of the dates in the database where datetime types with a timestamp, probably from a Now() function.So the following code would not work and rightfully so.


select duedate
from table
where duedate = 07/25/2006

The code above was comparing 2006-07-25 17:08:41.147 to 07/25/2006. So even though the customer was supplying a date that existed it would never return the record. I finally came up with a quick solution but I was wondering If I could get some feedback from everyone as to what the best approach to this would be. I ended up using the sql convert function. I may write more about this later but here is my code. The convert function will allow us for comparison to temporarily convert a field to the type and expression of our choice.


Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )


select duedate
from table
where CONVERT(varchar,duedate,101) = '07/25/2006'

SQL Back To Basics

In our last back to basics post we learned about string concatenation. Today we are going to learn about the EXISTS condition. The EXISTS condition is considered “to be met” if the sub query returns at least one row. The EXISTS condition can be used in any valid SQL statement – select, insert, update, or delete. The syntax for the EXISTS condition is


SELECT columns
FROM tables
WHERE EXISTS ( subquery );

Let’s take a look at a simple example. The example below will return all records from the customers table where there is at least one record in the orders table with the same customer id.


Select * from customers WHERE EXISTS
(select * from orders where customers.customerID = orders.customerId)