I am trying to insert data in a MySQL database; all operations working except this insert operation. When I hit this service I am getting below exception:
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank) values (300.0, 'rahul', 20)' at line 1
Controller
@RequestMapping(value="/insertData", method = RequestMethod.POST,produces={"application/json"})
@ResponseBody
public StatusObject insertStudentData(@RequestBody InsertStudentData data){
return userService.insertStudentData(data);
}
service
@Transactional
public StatusObject insertStudentData(InsertStudentData insertStudentData){
Users users = new Users();
users.setName(insertStudentData.getName());
users.setMarks(insertStudentData.getMarks());
users.setRank(insertStudentData.getRank());
int status = usersdao.insertObjectIntoDatabase(users);
StatusObject statusObject = new StatusObject();
if(status==0){
statusObject.setStatus(true);
statusObject.setMessage("Failed to insert data");
return statusObject;
}else{
statusObject.setStatus(false);
statusObject.setMessage("Success to insert data");
return statusObject;
}
}
Dao
public int insertObjectIntoDatabase(Users userOj){
Object obj = hibernateUtil.create(userOj);
if (obj != null) {
Session session = sessionFactory.getCurrentSession();
int userID = (Integer) session.createCriteria(Users.class).setProjection(Projections.max("user_id")).uniqueResult();
session.flush();
session.clear();
return userID;
} else {
return 0;
}
}
hibernateUtil:
@Repository
public class HibernateUtil {
@Autowired
private SessionFactory sessionFactory;
public <T> Serializable create(final T entity) {
return sessionFactory.getCurrentSession().save(entity);
}
public <T> T update(final T entity) {
sessionFactory.getCurrentSession().update(entity);
return entity;
}
public <T> void delete(final T entity) {
sessionFactory.getCurrentSession().delete(entity);
}
public <T> void delete(Serializable id, Class<T> entityClass) {
T entity = fetchById(id, entityClass);
delete(entity);
}
@SuppressWarnings("unchecked")
public <T> List<T> fetchAll(Class<T> entityClass) {
return sessionFactory.getCurrentSession().createQuery(" FROM "+entityClass.getName()).list();
}
@SuppressWarnings("rawtypes")
public <T> List fetchAll(String query) {
return sessionFactory.getCurrentSession().createSQLQuery(query).list();
}
@SuppressWarnings("unchecked")
public <T> T fetchById(Serializable id, Class<T> entityClass) {
return (T)sessionFactory.getCurrentSession().get(entityClass, id);
}
}
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.ensis.bloodgroup</groupId>
<artifactId>bloodgroup</artifactId>
<packaging>war</packaging>
<version>0.0.1-SNAPSHOT</version>
<name>bloodgroup Maven Webapp</name>
<url>http://maven.apache.org</url>
<properties>
<spring.version>4.3.2.RELEASE</spring.version>
<servlet.api.version>3.1.0</servlet.api.version>
<log4j.version>1.2.17</log4j.version>
<fasterxml.jackson.core.version>2.5.3</fasterxml.jackson.core.version>
<codehaus.jackson.version>1.9.13</codehaus.jackson.version>
<hibernate.version>4.3.5.Final</hibernate.version>
<mysql.version>8.0.15</mysql.version>
<quartz.version>2.2.1</quartz.version>
<spring.security.version>3.2.4.RELEASE</spring.security.version>
<spring.security.outh2.version>2.0.12.RELEASE</spring.security.outh2.version>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>javax</groupId>
<artifactId>javaee-web-api</artifactId>
<version>6.0</version>
<scope>provided</scope>
</dependency>
<!-- Spring Farmework -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- Quartz framework -->
<dependency>
<groupId>org.quartz-scheduler</groupId>
<artifactId>quartz</artifactId>
<version>${quartz.version}</version>
</dependency>
<!-- Servlet -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>${servlet.api.version}</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version> <!-- makesure correct version here -->
</dependency>
<dependency>
<groupId>javax.mail</groupId>
<artifactId>mail</artifactId>
<version>1.4.7</version>
</dependency>
<!-- Jackson -->
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>${codehaus.jackson.version}</version>
</dependency>
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-core-asl</artifactId>
<version>${codehaus.jackson.version}</version>
</dependency>
<dependency>
<groupId>javax.xml.bind</groupId>
<artifactId>jaxb-api</artifactId>
<version>2.2.12</version>
</dependency>
<dependency>
<groupId>javax.xml</groupId>
<artifactId>jaxb-impl</artifactId>
<version>2.1</version>
</dependency>
<!-- Jackson -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>${fasterxml.jackson.core.version}</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>${fasterxml.jackson.core.version}</version>
</dependency>
<!-- Loggers -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>${log4j.version}</version>
</dependency>
<!-- @Inject -->
<dependency>
<groupId>javax.inject</groupId>
<artifactId>javax.inject</artifactId>
<version>1</version>
</dependency>
<!-- Hibernate -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>${hibernate.version}</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-entitymanager</artifactId>
<version>${hibernate.version}</version>
</dependency>
<!-- Apache Commons DBCP -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<!--MYSQL Connector -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-io</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>com.sun.jersey</groupId>
<artifactId>jersey-bundle</artifactId>
<version>1.13-b01</version>
</dependency>
<!-- Spring Security -->
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-web</artifactId>
<version>${spring.security.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-config</artifactId>
<version>${spring.security.version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework.security.oauth/spring-security-oauth2 -->
<dependency>
<groupId>org.springframework.security.oauth</groupId>
<artifactId>spring-security-oauth2</artifactId>
<version>${spring.security.outh2.version}</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-c3p0</artifactId>
<version>3.6.3.Final</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.3.7.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.7.RELEASE</version>
</dependency>
</dependencies>
<build>
<finalName>bloodgroup</finalName>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-war-plugin</artifactId>
<version>2.1.1</version>
<configuration>
<packagingExcludes>WEB-INF/web.xml</packagingExcludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
hibernateUtil.create(userOj);i.e create method .rankis a reserved word inmysqlyou cannot use it as yourcolumnname.