进行这个练习的一些先决条件。
创建一张student数据库表：
CREATE TABLE Student( ID INT NOT NULL AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY (ID));
创建一个存储过程：
DELIMITER $$DROP PROCEDURE IF EXISTS `TEST`.`getRecord` $$CREATE PROCEDURE `TEST`.`getRecord` (IN in_id INTEGER,OUT out_name VARCHAR(20),OUT out_age INTEGER)BEGIN SELECT name, age INTO out_name, out_age FROM Student where id = in_id;END $$DELIMITER ;
创建一个数据访问对象接口文件 StudentDAO.java：
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
创建一个POJO Student.java:
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; }}
StudentMapper.java，负责将mySQL的数据映射成Java POJO Student对象实例：
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; }}
StudentJDBCTemplate.java，实现了DAO 接口 StudentDAO：
import java.util.Map;import javax.sql.DataSource;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;import org.springframework.jdbc.core.namedparam.SqlParameterSource;import org.springframework.jdbc.core.simple.SimpleJdbcCall;public class StudentJDBCTemplate implements StudentDAO { private DataSource dataSource; private SimpleJdbcCall jdbcCall; public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; this.jdbcCall = new SimpleJdbcCall(dataSource). withProcedureName("getRecord"); } public void create(String name, Integer age) { JdbcTemplate jdbcTemplateObject = new JdbcTemplate(dataSource); 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) { SqlParameterSource in = new MapSqlParameterSource(). addValue("in_id", id); Map<String, Object> out = jdbcCall.execute(in); Student student = new Student(); student.setId(id); student.setName((String) out.get("out_name")); student.setAge((Integer) out.get("out_age")); return student; } public List
MainApp.java:
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