详解spring开发_JDBC操作MySQL数据库

package com.b510.bean;/** * 普通的javaBean类Person *  * @author Hongten *  */public class Person {  /**   * id号*/  private int id;  /**   * 姓名*/  private String name;  /**   * 年龄*/  private int age;  /**   * 性别*/  private String sex;  public Person(int id, String name, int age, String sex) {    this.id = id;    this.name = name;    this.age = age;    this.sex = sex;  }  public Person() {  }  public int getId() {    return id;  }  public void setId(int id) {    this.id = id;  }  public String getName() {    return name;  }  public void setName(String name) {    this.name = name;  }  public int getAge() {    return age;  }  public void setAge(int age) {    this.age = age;  }  public String getSex() {    return sex;  }  public void setSex(String sex) {    this.sex = sex;  }}

package com.b510.service;import java.util.List;import com.b510.bean.Person;public interface PersonService {  /**   * 保存Person   *    * @param person*/  public abstract void save(Person person);  /**   * 更新Person   *    * @param person*/  public abstract void update(Person person);  /**   * 获取Person   *    * @param id   * @return*/  public abstract Person getPerson(Integer id);  /**   * 获取所有Person   *    * @return*/  public abstract List<Person> getPerson();  /**   * 删除指定id的Person   *    * @param id*/  public abstract void delete(Integer id);}

package com.b510.service.impl;import java.util.List;import javax.sql.DataSource;import org.springframework.jdbc.core.JdbcTemplate;import com.b510.bean.Person;import com.b510.service.PersonService;/** * 业务bean *  * @author Hongten *  */public class PersonServiceBean implements PersonService {  /**   * 数据源*/  private DataSource dataSource;  /**   * spring提供的jdbc操作辅助类*/  private JdbcTemplate jdbcTemplate;  // 设置数据源  public void setDataSource(DataSource dataSource) {    this.jdbcTemplate = new JdbcTemplate(dataSource);  }  public void save(Person person) {    jdbcTemplate.update("insert into person(name,age,sex)values(?,?,?)",        new Object[] { person.getName(), person.getAge(),            person.getSex() }, new int[] { java.sql.Types.VARCHAR,            java.sql.Types.INTEGER, java.sql.Types.VARCHAR });  }  public void update(Person person) {    jdbcTemplate.update("update person set name=?,age=?,sex=? where id=?",        new Object[] { person.getName(), person.getAge(),            person.getSex(), person.getId() }, new int[] {            java.sql.Types.VARCHAR, java.sql.Types.INTEGER,            java.sql.Types.VARCHAR, java.sql.Types.INTEGER });  }  public Person getPerson(Integer id) {    Person person = (Person) jdbcTemplate.queryForObject(        "select * from person where id=?", new Object[] { id },        new int[] { java.sql.Types.INTEGER }, new PersonRowMapper());    return person;  }  @SuppressWarnings("unchecked")  public List<Person> getPerson() {    List<Person> list = jdbcTemplate.query("select * from person", new PersonRowMapper());    return list;  }  public void delete(Integer id) {    jdbcTemplate.update("delete from person where id = ?", new Object[] { id },        new int[] { java.sql.Types.INTEGER });  }}

package com.b510.service.impl;import java.sql.ResultSet;import java.sql.SQLException;import org.springframework.jdbc.core.RowMapper;import com.b510.bean.Person;public class PersonRowMapper implements RowMapper {  @Override  public Object mapRow(ResultSet set, int index) throws SQLException {    Person person = new Person(set.getInt("id"), set.getString("name"), set        .getInt("age"), set.getString("sex"));    return person;  }}

package com.b510.test;import java.util.List;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import com.b510.bean.Person;import com.b510.service.PersonService;public class SpringJDBCTest {  public static void main(String[] args) {    ApplicationContext act = new ClassPathXmlApplicationContext("bean.xml");    PersonService personService = (PersonService) act        .getBean("personService");    Person person = new Person();    person.setName("苏东坡");    person.setAge(21);    person.setSex("男");    // 保存一条记录    personService.save(person);    List<Person> person1 = personService.getPerson();    System.out.println("++++++++得到所有Person");    for (Person person2 : person1) {      System.out.println(person2.getId() + " " + person2.getName()          + "  " + person2.getAge() + " " + person2.getSex());    }    Person updatePerson = new Person();    updatePerson.setName("Divide");    updatePerson.setAge(20);    updatePerson.setSex("男");    updatePerson.setId(5);    // 更新一条记录    personService.update(updatePerson);    System.out.println("******************");    // 获取一条记录    Person onePerson = personService.getPerson(2);    System.out.println(onePerson.getId() + " " + onePerson.getName()        + " " + onePerson.getAge() + " " + onePerson.getSex());    // 删除一条记录    personService.delete(1);  }}

<?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:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"  xsi:schemaLocation="http://www.springframework.org/schema/beans      http://www.springframework.org/schema/beans/spring-beans-2.5.xsd      http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd      http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd      http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">  <!--配置数据源 -->  <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"    destroy-method="close">    <property name="driverClassName" value="org.gjt.mm.mysql.Driver" />    <property name="url"      value="jdbc:mysql://localhost:3307/spring?useUnicode=true&characterEncoding=UTF-8" />    <property name="username" value="root" />    <property name="password" value="root" />    <!-- 连接池启动时的初始值 -->    <property name="initialSize" value="1" />    <!-- 连接池的最大值 -->    <property name="maxActive" value="300" />    <!-- 最大空闲值.当经过一个高峰时间后，连接池可以慢慢将已经用不到的连接慢慢释放一部分，一直减少到maxIdle为止 -->    <property name="maxIdle" value="2" />    <!-- 最小空闲值.当空闲的连接数少于阀值时，连接池就会预申请去一些连接，以免洪峰来时来不及申请 -->    <property name="minIdle" value="1" />  </bean>  <!--    采用注解方式来配置事务。针对数据源的事务管理器    ，把我们定义的数据源注入到DataSourceTransactionManager类的属性dataSource中  -->  <bean id="txManager"    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">    <property name="dataSource" ref="dataSource" />  </bean>  <!--    引入命名空间： 1.xmlns:tx="http://www.springframework.org/schema/tx    2.http://www.springframework.org/schema/tx    http://www.springframework.org/schema/tx/spring-tx-2.5.xsd    采用@Transaction注解方式使用事务管理器  -->  <tx:annotation-driven transaction-manager="txManager" />  <!-- 配置业务bean：PersonServiceBean -->  <bean id="personService" class="com.b510.service.impl.PersonServiceBean">    <!-- 向属性dataSource注入数据源 -->    <property name="dataSource" ref="dataSource"></property>  </bean></beans>

2012-3-9 23:30:57 org.springframework.context.support.AbstractApplicationContext prepareRefresh信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1a05308: display name [org.springframework.context.support.ClassPathXmlApplicationContext@1a05308]; startup date [Fri Mar 09 23:30:57 CST 2012]; root of context hierarchy2012-3-9 23:30:57 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions信息: Loading XML bean definitions from class path resource [bean.xml]2012-3-9 23:30:58 org.springframework.context.support.AbstractApplicationContext obtainFreshBeanFactory信息: Bean factory for application context [org.springframework.context.support.ClassPathXmlApplicationContext@1a05308]: org.springframework.beans.factory.support.DefaultListableBeanFactory@2bb5142012-3-9 23:30:58 org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons信息: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@2bb514: defining beans [dataSource,txManager,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,personService]; root of factory hierarchy++++++++得到所有Person2 TomCat  12 女3 hongten  21 男4 liufang  21 女5 Divide  20 男6 Jone  20 女7 苏东坡  21 男******************2 TomCat 12 女

driverClassName=org.gjt.mm.mysql.Driverurl=jdbc/:mysql/://localhost/:3307/spring?useUnicode/=true&characterEncoding/=UTF-8username=rootpassword=rootinitialSize=1maxActive=300maxIdle=2minIdle=1

<?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:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"  xsi:schemaLocation="http://www.springframework.org/schema/beans      http://www.springframework.org/schema/beans/spring-beans-2.5.xsd      http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd      http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd      http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">  <!-- 读取jdbc.properties配置文件 -->  <context:property-placeholder location="classpath:jdbc.properties" />  <!--配置数据源 -->  <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"    destroy-method="close">    <property name="driverClassName" value="${driverClassName}" />    <property name="url" value="${url}" />    <property name="username" value="${username}" />    <property name="password" value="${password}" />    <!-- 连接池启动时的初始值 -->    <property name="initialSize" value="${initialSize}" />    <!-- 连接池的最大值 -->    <property name="maxActive" value="${maxActive}" />    <!-- 最大空闲值.当经过一个高峰时间后，连接池可以慢慢将已经用不到的连接慢慢释放一部分，一直减少到maxIdle为止 -->    <property name="maxIdle" value="${maxIdle}" />    <!-- 最小空闲值.当空闲的连接数少于阀值时，连接池就会预申请去一些连接，以免洪峰来时来不及申请 -->    <property name="minIdle" value="${minIdle}" />  </bean>  <!--    采用注解方式来配置事务。针对数据源的事务管理器    ，把我们定义的数据源注入到DataSourceTransactionManager类的属性dataSource中  -->  <bean id="txManager"    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">    <property name="dataSource" ref="dataSource" />  </bean>  <!--    引入命名空间： 1.xmlns:tx="http://www.springframework.org/schema/tx    2.http://www.springframework.org/schema/tx    http://www.springframework.org/schema/tx/spring-tx-2.5.xsd    采用@Transaction注解方式使用事务管理器  -->  <tx:annotation-driven transaction-manager="txManager" />  <!-- 配置业务bean：PersonServiceBean -->  <bean id="personService" class="com.b510.service.impl.PersonServiceBean">    <!-- 向属性dataSource注入数据源 -->    <property name="dataSource" ref="dataSource"></property>  </bean></beans>

2012-3-10 0:23:59 org.springframework.context.support.AbstractApplicationContext prepareRefresh信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@c1b531: display name [org.springframework.context.support.ClassPathXmlApplicationContext@c1b531]; startup date [Sat Mar 10 00:23:59 CST 2012]; root of context hierarchy2012-3-10 0:23:59 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions信息: Loading XML bean definitions from class path resource [bean.xml]2012-3-10 0:23:59 org.springframework.context.support.AbstractApplicationContext obtainFreshBeanFactory信息: Bean factory for application context [org.springframework.context.support.ClassPathXmlApplicationContext@c1b531]: org.springframework.beans.factory.support.DefaultListableBeanFactory@1aa57fb2012-3-10 0:23:59 org.springframework.core.io.support.PropertiesLoaderSupport loadProperties信息: Loading properties file from class path resource [jdbc.properties]2012-3-10 0:23:59 org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons信息: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@1aa57fb: defining beans [org.springframework.beans.factory.config.PropertyPlaceholderConfigurer#0,dataSource,txManager,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,personService]; root of factory hierarchy++++++++得到所有Person2 TomCat  12 女3 hongten  21 男4 liufang  21 女5 Divide  20 男6 Jone  20 女7 苏东坡  21 男8 苏东坡  21 男******************2 TomCat 12 女