Error Help

[Kevin O'Sullivan]

I'm new to spring boot and I'm currently following along with a tutorial

but I'm getting two errors the first is in the schema.sql file

expected one of the following: EDITIONING FORCE FUNCTION NO OR PACKAGE PROCEDURE SEQUENCE TRIGGER TYPE VIEW identifier

the code

CREATE TABLE course(
    course_id integer identity NOT NULL,
    title varchar(80) NOT NULL,         -- Course Title
    description varchar(500) NOT NULL,  -- Course Description
    link varchar(255) NOT NULL,         -- Course landing page link
    CONSTRAINT pk_course_course_id PRIMARY KEY (course_id)

Now for the other error, when I try to insert a course to the database I get this error

Exception in thread "main" org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into course(title,description,link) values(?,?,?)]; NULL not allowed for column "TITLE"; SQL statement:
insert into course(title,description,link) values(?,?,?) [23502-200]; nested exception is org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "TITLE"; SQL statement:
insert into course(title,description,link) values(?,?,?) [23502-200]
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:251)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:960)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1015)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1025)
at com.example.dao.CourseJDBCDao.create(CourseJDBCDao.java:41)
at com.example.dao.CourseJDBCDao.create(CourseJDBCDao.java:13)
at com.example.CoursePlatformApplication.main(CoursePlatformApplication.java:23)
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "TITLE"; SQL statement:
insert into course(title,description,link) values(?,?,?) [23502-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:459)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
at org.h2.message.DbException.get(DbException.java:205)
at org.h2.message.DbException.get(DbException.java:181)
at org.h2.table.Column.validateConvertUpdateSequence(Column.java:374)
at org.h2.table.Table.validateConvertUpdateSequence(Table.java:845)
at org.h2.command.dml.Insert.insertRows(Insert.java:187)
at org.h2.command.dml.Insert.update(Insert.java:151)
at org.h2.command.CommandContainer.update(CommandContainer.java:198)
at org.h2.command.Command.executeUpdate(Command.java:251)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:191)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:152)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.springframework.jdbc.core.JdbcTemplate.lambda$update$2(JdbcTemplate.java:965)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
... 6 more

Now I'll post the rest of the code, I'm about half way through the tutorial so some of the methods aren't filled in yet.

public class Course {
    private int courseId;
    private String title;
    private String description;
    private String link;

    public Course(){

    }

    public Course(String title,String description,String link){

    }

    public void setCourseId(int courseId){
        this.courseId = courseId;
    }

    public int getCourseId(){
        return this.courseId;
    }

    public void setTitle(String title){
        this.title = title;
    }

    public String getTitle(){
        return this.title;
    }

    public void setDescription(String description){
        this.description = description;
    }

    public String getDescription(){
        return this.description;
    }

    public void setLink(String link){
        this.link = link;
    }

    public String getLink(){
        return this.link;
    }

    @Override
    public String toString() {
        return "Course{" +
                "courseId=" + courseId +
                ", title='" + title + '\'' +
                ", description='" + description + '\'' +
                ", link='" + link + '\'' +
                '}';
    }
}

package com.example.dao;
import java.util.List;
import java.util.Optional;
public interface DAO<T> {

    List<T> list();
    void create(T t);
    Optional<T>get(int id);
    void update(T t,int id);
    void delete(int id);
}

import com.example.model.Course;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Optional;

@Component
public class CourseJDBCDao implements  DAO<Course>{

    private static final Logger log = LoggerFactory.getLogger(CourseJDBCDao.class);
    private JdbcTemplate jdbcTemplate;

    RowMapper<Course>rowMapper = (rs,rowNum)-> {
        Course course = new Course();
        course.setCourseId(rs.getInt("course_id"));
        course.setTitle(rs.getString("title"));
        course.setDescription(rs.getString("description"));
        course.setLink(rs.getString("link"));
        return course;
    };

    public CourseJDBCDao(JdbcTemplate jdbcTemplate){
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<Course> list() {
        String sql = "SELECT course_id, title, description, link from course";
        return jdbcTemplate.query(sql,rowMapper);
    }

    @Override
    public void create(Course course) {
        String sql = "insert into course(title,description,link) values(?,?,?)";
        int insert = jdbcTemplate.update(sql,course.getTitle(),course.getDescription(),course.getLink());
        if(insert == 1) {
            log.info("New Course Created: " + course.getTitle());
        }
    }

    @Override
    public Optional<Course> get(int id) {
        return Optional.empty();
    }

    @Override
    public void update(Course course, int id) {

    }

    @Override
    public void delete(int id) {

    }
}

data.sql file

INSERT INTO course(title, description, link) values ('Vue.js for Beginners: Up and Running with Vue','A beginner''s guide to learn the basics of the JavaScript Framework Vue.js.','https://www.udemy.com/course/vue-intro/?referralCode=E9DECFF78CA706D7A68A');
INSERT INTO course(title, description, link) values ('The Complete Apache Groovy Developer Course','Everything you need to know to get started with the Groovy Programming Language','https://courses.danvega.dev/p/the-complete-apache-groovy-developer-course');
INSERT INTO course(title, description, link) values ('Getting Started with Spring Boot 2','Learn how to build a real application using Spring Framework 5 & Spring Boot 2','https://courses.danvega.dev/p/spring-boot-2');
INSERT INTO course(title, description, link) values ('Learn Spring Boot','Spring Boot gives you all the power of the Spring Framework without all of the complexity.','https://courses.danvega.dev/p/spring-boot-intro');
INSERT INTO course(title, description, link) values ('Angular 4 Java Developers','Learn how to build Spring Boot & Angular apps by building a real world application with JHipster.','https://courses.danvega.dev/p/jhipster');

Any ideas? I've gone through the video again and had a look at his github just to see if I made any mistakes and I can't see any, any ideas? thanks.

[Himai Minh]

I found the speaker's github link:
https://github.com/danvega/data-jdbc-test/tree/master/src/main/java/dev/danvega/dao
You may want to compare your code with his to see if you miss anything.

Maybe, try to insert an integer as the id of the course and see.

Here is another similar example. You will see the entity Person has an id in the constructor.
https://www.journaldev.com/17053/spring-jdbctemplate-example