Hibernate, Java, JDBC, JPA

[solved] org.h2.jdbc.JdbcSQLSyntaxErrorException: Column count does not match

Leave a comment

JdbcSQLSyntaxErrorException occurred when the executing query has violated SQL syntax rules.

JdbcSQLSyntaxErrorException Example

In this example throwing JdbcSQLSyntaxException with message “Column count does not match” because table having two columns while inserting row with only one value without column name. Here DBMS will confuse like passing value is for which column and throw exception as below “Column count does not match”.

Entity Class

@Entity
@Table(name = "author")
public class Author {

	@Id
	@GeneratedValue
	private Long id;

	@NotNull
    @Size(max = 100)
    @Column(unique = true)
	private String name;

	//getter and setter
	}

Query to insert

#wrong way
insert into author  values('Saurabh Gupta');
insert into author  values ('Gaurav Gupta');

Exception Stacktrace

 
    Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column count does not match; SQL statement:
     insert into author values('Saurabh Gupta') [21002-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:451) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.get(DbException.java:205) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.get(DbException.java:181) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.get(DbException.java:170) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.dml.Insert.prepare(Insert.java:314) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.prepareCommand(Parser.java:689) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Session.prepareLocal(Session.java:627) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Session.prepareCommand(Session.java:565) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1292) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:217) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205) ~[h2-1.4.199.jar:1.4.199]

Solutions

Thumb rule for database insert query is always mention the column name as below while inserting records so that if new column add on table or position change of columns in table then no code and query change required.

For above example the solution would be as below.

insert into author values(10001,'Saurabh Gupta');
insert into author values(10002,'Gaurav Gupta');

Recommend Solution

Here inserting records with column name.

insert into author (id, name) values(10001,'Saurabh Gupta');
insert into author (id, name) values(10002,'Gaurav Gupta');

You would like to see

Follow below link to see more JDBC, Hibernate and JPA issues solutions.