在Java的Spring框架的程序中使用JDBC API操作数据库
同时与数据库使用普通的旧JDBC的工作,它变得繁琐写不必要的代码来处理异常,打开和关闭数据库连接等,但Spring的JDBC框架需要的所有低层次细节从打开连接,准备和执行SQL语句,过程异常,处理事务,最后关闭连接。 所以,你所要做的只是定义连接参数,并指定要执行的SQL语句,并做必要的工作,在每次迭代时从数据库中获取数据。 Spring JDBC提供了一些方法和相应不同的类与数据库进行交互。我要采取经典和最流行的做法,利用JdbcTemplateclass框架。这是管理的所有数据库的通信和异常处理中心框架类。 JdbcTemplate 类 JdbcTemplate类的实例是一次配置的线程。所以,你可以配置一个JdbcTemplate的一个实例,然后安全地注入这种共享引用到多个DAO。 使用JdbcTemplate类时,通常的做法是配置一个DataSource在Spring配置文件,然后依赖关系注入该共享数据源豆到DAO类,JdbcTemplate或者是在setter数据源创建。 配置数据源 CREATE TABLE Student( ID INT NOT NULL AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL,AGE INT NOT NULL,PRIMARY KEY (ID) ); 现在,我们需要提供一个数据源给JdbcTemplate类,因此它可以自行配置,以获得数据库访问。您可以配置数据源的XML文件中有一段代码,如下图所示: <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="password"/> </bean> 数据访问对象 (DAO) 在Spring的数据访问对象(DAO)的支持使得它很容易与如JDBC,Hibernate,JPA和JDO以一致的方式进行数据访问技术。 执行SQL语句 查询一个整数: String SQL = "select count(*) from Student"; int rowCount = jdbcTemplateObject.queryForInt( SQL ); 查询长整数: String SQL = "select count(*) from Student"; long rowCount = jdbcTemplateObject.queryForLong( SQL ); 使用绑定变量的简单查询: String SQL = "select age from Student where id = ?"; int age = jdbcTemplateObject.queryForInt(SQL,new Object[]{10}); 在查询字符串: String SQL = "select name from Student where id = ?"; String name = jdbcTemplateObject.queryForObject(SQL,new Object[]{10},String.class); 查询并返回一个对象: String SQL = "select * from Student where id = ?"; Student student = jdbcTemplateObject.queryForObject(SQL,new StudentMapper()); 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; } } 查询并返回多个对象: String SQL = "select * from Student"; List<Student> students = jdbcTemplateObject.query(SQL,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; } } 插入一行到表: String SQL = "insert into Student (name,age) values (?,?)"; jdbcTemplateObject.update( SQL,new Object[]{"Zara",11} ); 更新一行到表:
String SQL = "update Student set name = ? where id = ?"; jdbcTemplateObject.update( SQL,10} ); 从表中删除行: String SQL = "delete Student where id = ?"; jdbcTemplateObject.update( SQL,new Object[]{20} ); 执行DDL语句 String SQL = "CREATE TABLE Student( " + "ID INT NOT NULL AUTO_INCREMENT," + "NAME VARCHAR(20) NOT NULL," + "AGE INT NOT NULL," + "PRIMARY KEY (ID));" jdbcTemplateObject.execute( SQL ); SQL存储过程 其次,考虑以下的MySQL存储过程这需要学生证和用OUT参数对应的学生的姓名和年龄的回报。因此,让我们使用MySQL命令提示符下在测试数据库中创建该存储过程: 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 ; 现在让我们写了Spring JDBC应用程序,将执行我们的学生桌简单的创建和读取操作。 package com.yiibai; 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(); } 以下是Student.java文件的内容: package com.yiibai; 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文件的内容: package com.yiibai; 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: package com.yiibai; 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,?)"; 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<Student> listStudents() { String SQL = "select * from Student"; List <Student> students = jdbcTemplateObject.query(SQL,new StudentMapper()); return students; } } 关于上面的程序几句话:你写的调用的执行代码时,需要创建包含IN参数的一个SqlParameterSource。重要的是要配合提供与存储过程中声明的参数名的输入值的名称。 execute方法接收传入的参数,并返回包含任何列在存储过程中指定的名称键入参数的映射。现在让我们修改主应用程序文件MainApp.java,这是如下: package com.yiibai; import java.util.List; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.yiibai.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("----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()); } } 以下是配置文件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="password"/> </bean> <!-- Definition for studentJDBCTemplate bean --> <bean id="studentJDBCTemplate" class="com.yiibai.StudentJDBCTemplate"> <property name="dataSource" ref="dataSource" /> </bean> </beans> 创建源代码和bean配置文件完成后,让我们运行应用程序。如果一切顺利,这将打印以下信息: ------Records Creation-------- Created Record Name = Zara Age = 11 Created Record Name = Nuha Age = 2 Created Record Name = Ayan Age = 15 ------Listing Multiple Records-------- ID : 1,Name : Zara,Age : 11 ID : 2,Name : Nuha,Age : 2 ID : 3,Name : Ayan,Age : 15 ----Listing Record with ID = 2 ----- ID : 2,Age : 2 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |