rest api study notes 1

rest JRA hibernate spring

---

this article is my study note of this tutorial - rest tutorial.
the github link is here : prjoect source

prepare database

• install mysql

yum install mysql-server mysql
chkconfig mysqld
/etc/init.d/mysqld status
/etc/init.d/mysql start
mysqladmin -u root password 123456

login mysql as root : mysql -u root -p , then it would prompt u to input password.

• create a user with user/pwd - rest_demo/rest_demo

create user 'rest_demo'@'localhost' identified by 'rest_demo'

notice, I also tried

create user 'rest_demo'@'localhost' identified password by 'rest_demo'

it prompts me to input a 41 hex hash value. that's not what I want.
see this link mysql creat user

• need to dump the database
  go to the sql script folder, then login mysql as root, then run the command source ./DumpRESTDB.sql . but I got some error like this:
  

  ERROR 1067 (42000): Invalid default value for 'insertion_date'
  Query OK, 0 rows affected (0.00 sec)
  ERROR 1146 (42S02): Table 'rest_demo.podcasts' doesn't exist
  ERROR 1146 (42S02): Table 'rest_demo.podcasts' doesn't exist
  ERROR 1146 (42S02): Table 'rest_demo.podcasts' doesn't exist
  ERROR 1146 (42S02): Table 'rest_demo.podcasts' doesn't exist
  Query OK, 0 rows affected (0.00 sec)

the issue is here :

CREATE TABLE `podcasts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(145) NOT NULL,
  `feed` varchar(145) NOT NULL,
  `insertion_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `description` varchar(500) DEFAULT NULL,
  `link_on_podcastpedia` varchar(145) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `feed_UNIQUE` (`feed`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

"invalid default value for 'insertion_date'. There are some explaination : [invalid default value][4] finally find the issue :

`insertion_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

after change it to:

`insertion_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

then it works great.

• start jetty

mvn jetty:run -Djetty.port=8888 -DskipTests=true

after open the /podcasts, got something like this :

53169 [qtp901914516-16] DEBUG o.c.d.r.f.LoggingResponseFilter - Response {
"status" : 500,
"code" : 5001,
"message" : "org.hibernate.exception.SQLGrammarException: Could not open connection",
"link" : "here will be the blo post url",

after dig into code, I also found :

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'rest_demo'@'localhost' to database 'rest_demo'\n\tat

after some investigation, it seems this is a privilege issue, I login as 'rest_demo', then found this :

mysql> use rest_demo;
ERROR 1044 (42000): Access denied for user 'rest_demo'@'localhost' to database 'rest_demo'

this happens becaues I ran the 'DumpRESTdemoDB.sql' by using the root account, so the user 'rest_demo' doesn't have enough privilege to access the 'rest_demo' database.

need to grant all privileges to the user 'rest_demo'.

mysql> grant all privileges on rest_demo.* to 'rest_demo'@'localhost' identified by 'rest_demo' with grant option;
Query OK, 0 rows affected (0.00 sec)

after that, finally I saw the result. so happy.

• how to connect to mysql from my mac. by using sequel pro, I could do that, here is the screenshot
  remember to choose 'ssh'. by this way, view table a lot easier.