Spring JDBC 入门笔记

[size=medium][color=blue]  最近学了下Spring JDBC 这块，发现还是蛮有趣的。现在展示一个入门基本的实例吧！
第一步： create 一个table

引用

   CREATE TABLE Student(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   PRIMARY KEY (ID)
);

第二步：引入spring的包。
第三步：编写一个实体类。Student 实体类

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

第四步。编写一个操作该实体的接口，StudentDao

public interface StudentDAO {

   public void setDataSource(DataSource ds);

   public void create(String name, Integer age);

   public Student getStudent(Integer id);

   public List<Student> listStudents();
  
   public void delete(Integer id);

   public void update(Integer id, Integer age);
}

第五步。编写一个Mapper类

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

第六步。编写一个实现StudentDao的类，这里就要用到SpringJDBC了。

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

}

第七步。编写测试类

public class TestMainApp {
   public static void main(String[] args) {
      ApplicationContext context = 
             new ClassPathXmlApplicationContext("applicationContext.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());
	  
   }
}

最后一步配置applicationContext文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">
	
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
	<property name="driverClass" value="com.mysql.jdbc.Driver" />
	<!-- 测试数据库-->
	<property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/test" />
	
	<property name="maxPoolSize" value="30" />
	<property name="minPoolSize" value="5" />
	<property name="idleConnectionTestPeriod" value="60" />
	<property name="acquireIncrement" value="5" />
	<property name="maxStatements" value="300" />
	<property name="properties">
		<props>
			<prop key="user">root</prop>
			<prop key="password">password</prop>
		</props>
	</property>
</bean>	

<!-- Definition for studentJDBCTemplate bean -->
<bean id="studentJdbcTempalte" class="com.spring.jdbc.example.lession1.JdbcTemplate.StudentJDBCtemplate">
	<property name="dataSource" ref="dataSource"/>
</bean>

</beans>

现在就可以简单的使用Spring jdbc 进行CRUD操作了。
注意这样用DateSource，其实可以直接通过配置Spring jdbcTemplate来实现。这样通过加载配置文件就可以使用到jdbcTemplate。我上面是使用DataSource。
如果直接使用jdbcTemplate的话，配置及可以写成

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	<constructor-arg ref="dataSource" />
</bean>
<bean id="studentJdbcTempalte" com.spring.jdbc.example.lession1.JdbcTemplate.StudentJDBCtemplate">
	<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>

两种不同的引人其实都一样的效果。[/color][/size]