JDBC框架

JDBC 框架

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

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;

   }

}

----

   <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 id="studentDaoImpl"

      class="com.tutorialspoint.StudentDaoImpl">

      <property name="dataSource"  ref="dataSource" />   

   </bean>

----

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;

   }

}

调用存储过程

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;

import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import org.springframework.jdbc.core.simple.SimpleJdbcCall;

public class StudentDaoImpl implements StudentDAO {

   private DataSource dataSource;

   private SimpleJdbcCall jdbcCall;

   public void setDataSource(DataSource dataSource) {

      this.dataSource = dataSource;

      this.jdbcCall =  new SimpleJdbcCall(dataSource).

                       withProcedureName("getRecord");

   }

   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 class StudentDaoImpl implements StudentDAO {

   private DataSource dataSource;

   private JdbcTemplate jdbcTemplateObject;

   private PlatformTransactionManager transactionManager;

   public void setDataSource(DataSource dataSource) {

      this.dataSource = dataSource;

      this.jdbcTemplateObject = new JdbcTemplate(dataSource);

   }

   public void setTransactionManager(

      PlatformTransactionManager transactionManager) {

      this.transactionManager = transactionManager;

   }

   public void create(String name, Integer age, Integer marks, Integer year){

      TransactionDefinition def = new DefaultTransactionDefinition();

      TransactionStatus status = transactionManager.getTransaction(def);

      try {

         String SQL1 = "insert into Student (name, age) values (?, ?)";

         jdbcTemplateObject.update( SQL1, name, age);

         // Get the latest student id to be used in Marks table

         String SQL2 = "select max(id) from Student";

         int sid = jdbcTemplateObject.queryForInt( SQL2 );

         String SQL3 = "insert into Marks(sid, marks, year) " +

                       "values (?, ?, ?)";

         jdbcTemplateObject.update( SQL3, sid, marks, year);

         System.out.println("Created Name = " + name + ", Age = " + age);

         transactionManager.commit(status);

      } catch (DataAccessException e) {

         System.out.println("Error in creating record, rolling back");

         transactionManager.rollback(status);

         throw e;

      }

      return;

   }

----

   <bean id="transactionManager"

      class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

      <property name="dataSource"  ref="dataSource" />   

   </bean>

 

   <!-- Definition for studentJDBCTemplate bean -->

   <bean id="studentDaoImpl"

      class="com.tutorialspoint.StudentDaoImpl">

      <property name="dataSource"  ref="dataSource" />

      <property name="transactionManager"  ref="transactionManager" />   

   </bean>

声明式事务管理

   <tx:advice id="txAdvice"  transaction-manager="transactionManager">

      <tx:attributes>

      <tx:method name="create"/>

      </tx:attributes>

   </tx:advice>

 

   <aop:config>

      <aop:pointcut id="createOperation"

      expression="execution(* com.tutorialspoint.StudentJDBCTemplate.create(..))"/>

      <aop:advisor advice-ref="txAdvice" pointcut-ref="createOperation"/>

   </aop:config>

 

   <!-- Initialization for TransactionManager -->

   <bean id="transactionManager"

   class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

      <property name="dataSource"  ref="dataSource" />   

   </bean>