Spring(十三)之SQL存储过程
发布时间:2020-12-15 07:12:48 所属栏目:Java 来源:网络整理
导读:SimpleJdbcCall ?类可以被用于调用一个包含 IN 和 OUT 参数的存储过程。你可以在处理任何一个 RDBMS 时使用这个方法,就像 Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle,和 Sybase。 ? 还是基于Spring之JDBC框架这个例子继续 ? (1)基于Test
SimpleJdbcCall?类可以被用于调用一个包含 IN 和 OUT 参数的存储过程。你可以在处理任何一个 RDBMS 时使用这个方法,就像 Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle,和 Sybase。 ? 还是基于Spring之JDBC框架这个例子继续 ? (1)基于Test库创建对应的存储过程 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 ; ? (2)创建实体 package com.tutorialspoint; public class Student { private Integer age; String name; Integer id; void setAge(Integer age) { this.age = age; } public Integer getAge() { return setName(String name) { this.name = name; } String getName() { setId(Integer id) { this.id = id; } Integer getId() { id; } } ? (3)编写StudentDAO import java.util.List; javax.sql.DataSource; interface StudentDAO { /** * This is the method to be used to initialize * database resources ie. connection. */ setDataSource(DataSource ds); * This is the method to be used to create * a record in the Student table. 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. 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(); } ? (4)编写StudentMapper.java java.sql.ResultSet; java.sql.SQLException; org.springframework.jdbc.core.RowMapper; 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")); student; } } ? (5)编写StudentJDBCTemplate .java java.util.Map; org.springframework.jdbc.core.JdbcTemplate; org.springframework.jdbc.core.namedparam.MapSqlParameterSource; org.springframework.jdbc.core.namedparam.SqlParameterSource; org.springframework.jdbc.core.simple.SimpleJdbcCall; class StudentJDBCTemplate implements DataSource dataSource; SimpleJdbcCall jdbcCall; setDataSource(DataSource dataSource) { this.dataSource = dataSource; this.jdbcCall = SimpleJdbcCall(dataSource). withProcedureName("getRecord"); } JdbcTemplate(dataSource); String SQL = "insert into Student (name,age) values (?,?)"; jdbcTemplateObject.update( SQL,name,age); System.out.println("Created Record Name = " + name + " Age = " + age); ; } Student getStudent(Integer id) { SqlParameterSource in = MapSqlParameterSource(). addValue("in_id",id); Map<String,Object> out = jdbcCall.execute(in); Student student = Student(); student.setId(id); student.setName((String) out.get("out_name")); student.setAge((Integer) out.get("out_age" student; } listStudents() { JdbcTemplate jdbcTemplateObject = JdbcTemplate(dataSource); String SQL = "select * from Student"; List <Student> students = jdbcTemplateObject.query(SQL, StudentMapper()); students; } } ? (6)编写对应的MainApp.java org.springframework.context.ApplicationContext; org.springframework.context.support.ClassPathXmlApplicationContext; com.tutorialspoint.StudentJDBCTemplate; MainApp { static 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("----Listing Record with ID = 2 -----" ); Student student = studentJDBCTemplate.getStudent(2); System.out.print("ID : " + student.getId() ); System.out.print(",1)"> student.getName() ); System.out.println(",1)"> student.getAge()); } } ? (7)编写Beans.xml <?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/TEST"/> <property name="username" value="root"/> <property name="password" value="1234"/> </bean> <!-- Definition for studentJDBCTemplate bean --> <bean id="studentJDBCTemplate" class="com.tutorialspoint.StudentJDBCTemplate"> <property name="dataSource" ref="dataSource" /> </bean> </beans> ? (8)运行MainApp.java中的main方法 结果如图: ? (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |