spring JDBC

spring JDBC

 

spring JDBC 帮我们做了什么？

定义数据库连接参数，打开数据库连接，处理异常，关闭数据库连接

 

 

我们仅需要关注：

声明SQL语句，处理每一次得到的结果

 

 

代码

 

applicationContext.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 ">

   <!-- 连接数据库 -->
   <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>

   <!-- 执行数据库操作的bean -->
   <bean id="studentJDBCTemplate" 
      class="com.yiibai.StudentJDBCTemplate">
      <property name="dataSource"  ref="dataSource" />    
   </bean>
      
</beans>

 

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;
   }
}

 

Dao实现类

public class StudentDaoImpl 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;
   }

}

 

 StudentMapper，将ResultSet转成Object

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;
   }
}

 

测试类

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());
      
   }
}

 

 

 

总结

 

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不是数据的定义，而是操作的定义。 

 

 

 

一些细节：

1. ResultSet就是执行SQL语句返回的结果集

2. ResultSetMetaData 用于返回记录集的自身信息

3. DataSource 这个的介绍有点复杂，总体来看还是为了安全性与封装

 

 

 

连接数据库通常需要实现以下几个步骤：

 

1. 注册数据库驱动程序（driver）。可以通过调用java.sql.DriverManager类的registerDriver方法显式注册驱动程序，也可以通过加载数据库驱动程序类隐式注册驱动程序。

2． 建立连接。调用java.sql.DriverManager类的getConnection（）方法可以建立与数据库的连接。