I am trying to make an application using Spring Boot and JDBC.
However, the creation of the Table is not getting initiated.
I have created a schema-mysql.sql file to have the table created.
The schema-mysql.sql file is as follows:
DROP TABLE IF EXISTS Pirate;
CREATE TABLE Pirate (
id INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) NOT NULL
);
Application properties:
server.port=8081
spring.datasource.url=jdbc:h2:mem:testdb
spring.data.jpa.repositories.bootstrap-mode=default
spring.h2.console.enabled=true
Repository used to interact with the Database:
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
@Repository
public class PirateRepository extends JdbcDaoSupport {
@Autowired
DataSource dataSource;
@PostConstruct
private void initialize() {
setDataSource(dataSource);
}
public void insertPirate(Pirate pirate)
{
String sql = "INSERT INTO Pirate "+"(Name,Email) VALUES (?,?)";
getJdbcTemplate().update(sql,new Object[] {pirate.getName(),pirate.getEmail()});
}
public List<Pirate> getAllPirates(){
String sql = "SELECT * FROM Pirate";
List<Map<String,Object>> rows = getJdbcTemplate().queryForList(sql);
List<Pirate> result = new ArrayList<>();
for(Map<String, Object> row:rows) {
Pirate pirate = new Pirate();
pirate.setId((Integer)row.get("id"));
pirate.setName((String)row.get("Name"));
pirate.setEmail((String)row.get("Email"));
result.add(pirate);
}
return result;
}
}
Can you please tell me what I am missing, due to the table not being initiated? According to the program question, no Jpa or Crud Repository can be used.
Try to define spring.datasource.platform=mysql onto your properties file.