Spring JDBC using application.properties file

Question

I'm training myself on Spring JDBC and I'm following this tutorial: Spring JDBC Example.

The tutorial is very good and executing the code I have no problem.

Fof simplicity and convenience, I quote here the code.

First class, Student (the entity to store in db):

package com.tutorialspoint;

public class Student 
{
       private Integer age;
       private String name;
       private Integer id;

       public void setAge(Integer age) 
       {
          this.age = age;
       }
       
       public Integer getAge() 
       {
          return age;
       }
       
       public void setName(String name) 
       {
          this.name = name;
       }
       
       public String getName() 
       {
          return name;
       }
       
       public void setId(Integer id) 
       {
          this.id = id;
       }
       
       public Integer getId() 
       {
          return id;
       }
    }

Interface, StudentDAO (for DAO logic):

package com.tutorialspoint;

import java.util.List;
import javax.sql.DataSource;

public interface StudentDAO 
{
   /** 
      * This is the method to be used to initialize
      * database resources ie. connection.
   */
   public void setDataSource(DataSource ds);
   
   /** 
      * This is the method to be used to create
      * a record in the Student table.
   */
   public void create(String name, Integer age);
   
   /** 
      * This is the method to be used to list down
      * a record from the Student table corresponding
      * to a passed student id.
   */
   public Student getStudent(Integer id);
   
   /** 
      * This is the method to be used to list down
      * all the records from the Student table.
   */
   public List<Student> listStudents();
   
   /** 
      * This is the method to be used to delete
      * a record from the Student table corresponding
      * to a passed student id.
   */
   public void delete(Integer id);
   
   /** 
      * This is the method to be used to update
      * a record into the Student table.
   */
   public void update(Integer id, Integer age);
}

Then, the class StudentJDBCTemplate (to implements StudentDAO):

package com.tutorialspoint;

import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

public class StudentJDBCTemplate implements StudentDAO 
{
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;
   
   public void setDataSource(DataSource dataSource) 
   {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
   }
   
   public void create(String name, Integer age) 
   {
      String SQL = "insert into Student (name, age) values (?, ?)";
      jdbcTemplateObject.update( SQL, name, age);
      System.out.println("Created Record Name = " + name + " Age = " + age);
      return;
   }
   
   public Student getStudent(Integer id) 
   {
      String SQL = "select * from Student where id = ?";
      Student student = jdbcTemplateObject.queryForObject(SQL, 
         new Object[]{id}, new StudentMapper());
      
      return student;
   }
   
   public List<Student> listStudents() 
   {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
      return students;
   }
   
   public void delete(Integer id) 
   {
      String SQL = "delete from Student where id = ?";
      jdbcTemplateObject.update(SQL, id);
      System.out.println("Deleted Record with ID = " + id );
      return;
   }
   
   public void update(Integer id, Integer age)
   {
      String SQL = "update Student set age = ? where id = ?";
      jdbcTemplateObject.update(SQL, age, id);
      System.out.println("Updated Record with ID = " + id );
      return;
   }
}

Third class: StudentMapper (to mapping to MySQL db Student):

package com.tutorialspoint;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

public class StudentMapper implements RowMapper<Student> 
{
   public Student mapRow(ResultSet rs, int rowNum) throws SQLException 
   {
      Student student = new Student();
      student.setId(rs.getInt("id"));
      student.setName(rs.getString("name"));
      student.setAge(rs.getInt("age"));
      
      return student;
   }
}

Last class, MainApp (run the application):

  package com.tutorialspoint;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.tutorialspoint.StudentJDBCTemplate;

public class MainApp {
   public static void main(String[] args) {
      ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");

      StudentJDBCTemplate studentJDBCTemplate = 
         (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
      
      System.out.println("------Records Creation--------" );
      studentJDBCTemplate.create("Zara", 11);
      studentJDBCTemplate.create("Nuha", 2);
      studentJDBCTemplate.create("Ayan", 15);

      System.out.println("------Listing Multiple Records--------" );
      List<Student> students = studentJDBCTemplate.listStudents();
      
      for (Student record : students) {
         System.out.print("ID : " + record.getId() );
         System.out.print(", Name : " + record.getName() );
         System.out.println(", Age : " + record.getAge());
      }

      System.out.println("----Updating Record with ID = 2 -----" );
      studentJDBCTemplate.update(2, 20);

      System.out.println("----Listing Record with ID = 2 -----" );
      Student student = studentJDBCTemplate.getStudent(2);
      System.out.print("ID : " + student.getId() );
      System.out.print(", Name : " + student.getName() );
      System.out.println(", Age : " + student.getAge());
   }
}

Finally, the Beans file to configure database connection:

<?xml version = "1.0" encoding = "UTF-8"?>
<beans xmlns = "http://www.springframework.org/schema/beans"
   xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" 
   xsi:schemaLocation = "http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">

   <!-- Initialization for data source -->
   <bean id="dataSource" 
      class = "org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name = "driverClassName" value = "com.mysql.jdbc.Driver"/>
      <property name = "url" value = "jdbc:mysql://localhost:3306/springtraining"/>
      <property name = "username" value = "***"/>
      <property name = "password" value = "***"/>
   </bean>

   <!-- Definition for studentJDBCTemplate bean -->
   <bean id = "studentJDBCTemplate" 
      class = "com.tutorialspoint.StudentJDBCTemplate">
      <property name = "dataSource" ref = "dataSource" />    
   </bean>
      
</beans>

The tutorial is very clear and if i try it, there is a perfect execution without error and the MySQL Db is correctly updated.

My questions are about a possible variation of this project. More specifically:

1. If I want to use the application.properties file (as that given by SpringBoot), is it this the correct syntax?

   spring.datasource.driver-class-name=com.mysql.jdbc.Driver
   spring.datasource.url=jdbc:mysql://localhost:3306/springtraining
   spring.datasource.username=***
   spring.datasource.password=***
   

2. If I don't want use the RowMapper class, is it correct to use the one given by Spring, the BeanPropertyRowMapper?

3. Without the Beans file (if I use, for this example, the application.properties, I have no need of it), how can I replace in the MainApp this rows:

   ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");

      StudentJDBCTemplate studentJDBCTemplate = 
         (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
   

and making so the applications executable?

Answer 1

Firstly, I'd advise you to learn how to do it without application.properties file. We live in 21rst century, where Spring-boot allows us to declare jdbc dataSource as @Bean with database credentials in MySpringBootApplication class. See how to do it here

Secondly, i'd advise not to use jdbcTemplate unless you don't have time. Mark my words, if happens to debug - it would be nightmare. So try to use pure Jdbc with addition of spring configuration.

Sample example how to do it:

StudentDAO interface

    public interface StundentDAO {

    void addStudent(String name, String surname);

    List<Student> findStudents();
}

JdbcStudentDAO implementation

    @Repository
    public class JdbcStudentDAO implements StudentDAO {

    //[IMPORTANT] import javax.sql.datasource package (?)
    private Datasource datasource;

    @Autowire
    public JdbcStudentDAO(Datasource datasource) {
        this.datasource = datasource;
    }

    @Override
    public void addStudent(String name, String surname) {
        String query = "INSERT INTO Students VALUES (?,?)";
        try(Connection connection = datasource.getConnection()) {
            try(PreparedStatement statement = connection.preparedStatement(query)) {
                statement.setString(1, name);
                statement.setString(2, surname);
                statement.executeUpdate();
            }
        } catch(SQLException e) {
            e.printStacktrace();
        }
    }

    @Override
    public List<Student> findStudents() {
        String query = "SELECT * FROM Students";
        Student student = null; //will be used soon as DTO
        List<Student> listOfStudents = null;
        try(Connection connection = datasource.getConnection()) {
            try(PreparedStatement statement = connection.preparedStatement(query)) {
                try(ResultSet rs = statement.executeQuery()) {
                    listOfStudents = new ArrayList<>();
                    while(rs.next()) {
                        student = new Student(
                            rs.getString("name");
                            rs.getString("surname");
                        );
                    }
                    listOfStudents.add(student);
                }
            }
        } catch(SQLException e) {
            e.printStacktrace();
        }
        return listOfStudents;
    }
} 

Please note, that dataSource does only Database connectivity.(see the link)

Good luck!