Spring - JDBC

springmvc

https://www.tutorialspoint.com/spring/spring_jdbc_example.htm
https://spring.io/guides/gs/relational-data-access/
http://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html
https://www.mkyong.com/spring/maven-spring-jdbc-example/
http://www.onlinetutorialspoint.com/spring/spring-jdbctemplate-crud-application.html
http://www.beingjavaguys.com/2013/07/spring-jdbc-template-with-spring-mvc.html
http://www.javatpoint.com/spring-JdbcTemplate-tutorial
http://stackoverflow.com/questions/21949897/how-to-connect-database-using-spring-xml-in-spring

Example of defining a data source in our xml file:

<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">  
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>  
    <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"></property>  
    <property name="username" value="system"></property>  
    <property name="password" value="oracle"></property>  
</bean>  

<bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">  
    <property name="dataSource" ref="ds"></property>  
</bean>  

<bean id="dao" class="com.javatpoint.dao.EmployeeDao">  
    <property name="template" ref="jt"></property>  
</bean>  

Example of creating a DAO and using it inside a controller:

import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.util.List;  
import org.springframework.jdbc.core.BeanPropertyRowMapper;  
import org.springframework.jdbc.core.JdbcTemplate;  
import org.springframework.jdbc.core.RowMapper;  
import com.javatpoint.beans.Emp;  

public class EmployeeDao {  
    JdbcTemplate template;  

    public void setTemplate(JdbcTemplate template) {  
        this.template = template;  
    }  

    public int save(Employee p){  
        String sql="insert into Employee(name,salary,designation)   
              values('"+p.getName()+"',"+p.getSalary()+",'"+p.getDesignation()+"')";  
        return template.update(sql);  
    }  
    public int update(Employee p){  
        String sql="update Employee set name='"+p.getName()+"', salary="+p.getSalary()+",   
              designation='"+p.getDesignation()+"' where id="+p.getId()+"";  
        return template.update(sql);  
    }  
    public int delete(int id){  
        String sql="delete from Employee where id="+id+"";  
        return template.update(sql);  
    }  
    public Employee getEmpById(int id){  
        String sql="select * from Employee where id=?";  
        return template.queryForObject(sql, new Object[]{id},new BeanPropertyRowMapper<Employee>(Employee.class));  
    }  
    public List<Employee> getEmployees(){  
        return template.query("select * from Employee",new RowMapper<Employee>(){  
            public Employee mapRow(ResultSet rs, int row) throws SQLException {  
                Employee e = new Employee();  
                e.setId(rs.getInt(1));  
                e.setName(rs.getString(2));  
                e.setSalary(rs.getFloat(3));  
                e.setDesignation(rs.getString(4));  
                return e;  
            }  
        });  
    }  
    public List<Emp> getEmployeesByPage(int pageid,int total){  
        String sql="select * from Emp limit "+(pageid-1)+","+total;  
        return template.query(sql,new RowMapper<Emp>(){  
            public Emp mapRow(ResultSet rs, int row) throws SQLException {  
                Emp e=new Emp();  
                e.setId(rs.getInt(1));  
                e.setName(rs.getString(2));  
                e.setSalary(rs.getFloat(3));  
                return e;  
            }  
        });  
    }  

}

@Controller  
public class EmployeeController {  
    @Autowired  
    EmployeeDao dao; // will inject dao from xml file  

    /* Displays a form to input data, here "command" is a reserved request attribute 
     * which is used to display object data into form 
     */  
    @RequestMapping("/employeeform")  
    public ModelAndView showform(){  
        return new ModelAndView("employeeform","command",new Employee());  
    }

    /* Saves object into database. The @ModelAttribute puts request data 
     * into model object.
     */  
    @RequestMapping(value="/save",method = RequestMethod.POST)  
    public ModelAndView save(@ModelAttribute("emp") Employee emp){  
        dao.save(emp);  
        return new ModelAndView("redirect:/viewemp");//will redirect to viewemp request mapping  
    }

    /* Provides list of employees in model object */  
    @RequestMapping("/viewemp")  
    public ModelAndView viewemp(){  
        List<Employee> list = dao.getEmployees();  
        return new ModelAndView("viewemp","list",list);  
    }  

    @RequestMapping(value="/viewemp/{pageid}")  
    public ModelAndView viewempByPage(@PathVariable int pageid){  
        int total=5;  
        if(pageid==1){
        } else{  
            pageid=(pageid-1)*total+1;  
        }  
        List<Emp> list=dao.getEmployeesByPage(pageid,total);  
        return new ModelAndView("viewemp","list",list);  
    }  

    /* Displays object data into form for the given id.  
     * The @PathVariable puts URL data into variable.
     */  
    @RequestMapping(value="/editemp/{id}")  
    public ModelAndView edit(@PathVariable int id){  
        Employee emp = dao.getEmpById(id);  
        return new ModelAndView("empeditform","command",emp);  
    }

    /* Updates model object. */  
    @RequestMapping(value="/editsave",method = RequestMethod.POST)  
    public ModelAndView editsave(@ModelAttribute("emp") Employee emp){  
        dao.update(emp);  
        return new ModelAndView("redirect:/viewemp");  
    }

    /* Deletes record for the given id in URL and redirects to /viewemp */  
    @RequestMapping(value="/deleteemp/{id}", method = RequestMethod.GET)  
    public ModelAndView delete(@PathVariable int id){  
        dao.delete(id);  
        return new ModelAndView("redirect:/viewemp");  
    }
}

How can we use Spring JDBC?

jdbcTemplate.update(“Your query”, parameter1)
jdbcTemplate.query(“Your query”, parameter1);

How can we implement a RowMapper?

class TodoMapper implements RowMapper<Todo> {
  public Todo mapRow(ResultSet rs, int rowNum) throws SQLException {
    Todo todo = new Todo();
    todo.setId(rs.getInt(1));
    todo.setDescription(rs.getString(2));
    todo.setDone(rs.getBoolean(3));
    return todo;
  }
}

How can we use Spring JDBC?

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

import com.companyName.jdbc.hsql.HsqlDatabase;
import com.companyName.jdbc.model.Todo;

HsqlDatabase db = new HsqlDatabase();
JdbcTemplate jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(db.conn, false));

jdbcTemplate.update(
  "INSERT INTO TODO(DESCRIPTION,IS_DONE) VALUES(?, ?)", 
  todo.getDescription(),
  todo.isDone()
);

jdbcTemplate.update("DELETE FROM TODO WHERE ID=?", id);

jdbcTemplate.query(“SELECT * FROM TODO”, new BeanPropertyRowMapper<ToDo>(Todo.class));

return jdbcTemplate.query("SELECT * FROM TODO", new TodoMapper());

class TodoMapper implements RowMapper<Todo> {
    public Todo mapRow(ResultSet rs, int rowNum) throws SQLException {
        Todo todo = new Todo();
        todo.setId(rs.getInt(1));
        todo.setDescription(rs.getString(2));
        todo.setDone(rs.getBoolean(3));
        return todo;
    }
}

jdbcTemplate.query(“SELECT * FROM TODO”, new RowMapper<Todo>() {
  return new Todo(rs.getInt(1), rs.getString(2), rs.getBoolean(3);
});
String trainingDataSourceURL = this.getPropValue("TRAINING_DATASOURCE_URL");
String trainingDataSourceUsername = this.getPropValue("TRAINING_DATASOURCE_USERNAME");
String trainingDataSourcePassword = this.getPropValue("TRAINING_DATASOURCE_PASSWORD");
SingleConnectionDataSource trainingDataSource = new SingleConnectionDataSource(
        "oracle.jdbc.driver.OracleDriver", trainingDataSourceURL, trainingDataSourceUsername,
        trainingDataSourcePassword, true);
//trainingDataSource.getConnection().setAutoCommit(true);
return new JdbcTemplate(trainingDataSource);
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License