首页 > 代码库 > Spring JDBC入门
Spring JDBC入门
Spring将替我们完成所有使用JDBC API进行开发的单调乏味的、底层细节处理工作。
操作JDBC时Spring可以帮我们做这些事情：
定义数据库连接参数，打开数据库连接，处理异常，关闭数据库连接
我们仅需要关注：
声明SQL语句，处理每一次得到的结果
一个较为简单的例子与讲解
JdbcTemplate类
JdbcTemplate是core包的核心类。它替我们完成了资源的创建以及释放工作，从而简化了我们对JDBC的使用。它还可以帮助我们避免一些常见的错误，比如忘记关闭数据库连接。JdbcTemplate将完成JDBC核心处理流程，比如SQL语句的创建、执行，而把SQL语句的生成以及查询结果的提取工作留给我们的应用代码。它可以完成SQL查询、更新以及调用存储过程，可以对ResultSet进行遍历并加以提取。它还可以捕获JDBC异常并将其转换成org.springframework.dao包中定义的，通用的，信息更丰富的异常。
代码分为三部分
第一部是利用Spring处理业务，第二部分创建Spring相关的XML，第三部分将XML和Context结合到一起。
package com.jiaozg.dao;import java.math.BigDecimal;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import java.util.Map;import org.springframework.jdbc.core.JdbcTemplate;import com.jiaozg.model.Dept;import com.jiaozg.util.SpringUtil;主要类：public class DeptDao { private JdbcTemplate jdbcT; public void setJdbcT(JdbcTemplate jdbcT) { this.jdbcT = jdbcT; } public List findALL() { String sql = "select * from dept"; return jdbcT.queryForList(sql); } public List<Dept> findALLDepts() { List<Dept> depts = new ArrayList<Dept>();; String sql = "select * from Dept"; List list = jdbcT.queryForList(sql); Iterator iterator = list.iterator(); Dept dept = null; while (iterator.hasNext()) { Map map4dept = (Map) iterator.next(); dept = new Dept(); dept.setDeptNo(((BigDecimal) map4dept.get("DEPTNO")).intValue()); dept.setDName((String)map4dept.get("DNAME")); dept.setLoc((String)map4dept.get("LOC")); depts.add(dept); } return depts; } public int delete(int bid){ String sql = "delete from DeptInfo where bid =?"; return jdbcT.update(sql, new Object[]{bid}); } public static void main(String[] args) { DeptDao dao = (DeptDao) SpringUtil.getBean("deptDao"); List<Dept> depts = dao.findALLDepts();; for(Dept dept:depts){ System.out.println(dept.getDeptNo()+","+dept.getDName()+","+dept.getLoc()); } System.out.println("---------------------------------"); List list = dao.findALL(); for(Iterator it = list.iterator(); it.hasNext(); ) { System.out.println(it.next()); } }}
public class SpringUtil { private static ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); public static Object getBean(String beanName){ return ctx.getBean(beanName); } }
<?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-2.5.xsd"> <bean id="springDSN" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"> </property> <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"> </property> <property name="username" value="scott"></property> <property name="password" value="Qwer1234"></property> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" abstract="false" lazy-init="false" autowire="default" dependency-check="default"> <property name="dataSource"> <ref bean="springDSN" /> </property> </bean> <bean id="deptDao" class="com.jiaozg.dao.DeptDao"> <property name="jdbcT"> <ref bean="jdbcTemplate" /> </property> </bean> </beans>
总结：
JAVA逻辑部分还能看懂，但XML就有些迷糊：XML第一部分是配置到数据库的连接信息，第二三部分就不知道了。
还有DAO是什么意思？
应该还缺少数据库中表的定义，这个定义应该对应着DEBT的javabean.
一个更加详细的例子
CREATE TABLE Student( ID INT NOT NULL AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY (ID));
Student表，有ID, NAME, AGE三项
对应的JAVABEAN
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的内容
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; }}
将搜索结果ResultSet转化为Object
StudentJDBCTemplate.java
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; }}
Spring和JAVABEAN的配置文件
<?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>
main.java
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()); }}
还有一个带有用法的main.java，我就不贴了
总结
1. 将JAVA的类与数据库中的表项绑定到一起，这样更加方便操作
2. 表到类的转换过程需要rawMapper,它起作用体现在query或queryForObject
3. SQL语句用到了(name, age) (?, ?)的东西，这个倒是有点像C++11中的bind用法，QueryForObject也用到了类似的写法，但是稍微复杂点
queryForObject(SQL, new Object[]{id}, new StudentMapper()}; 这里的id被填充到一个数组中，数组为SQL提供数据
4. Context读取XML文件，Template依靠Context获得Bean,这个bean不是数据的定义，而是操作的定义。
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。