spring开发_JDBC操作MySQL数据库
项目结构：
http://www.cnblogs.com/hongten/gallery/image/112450.html
数据库表：
http://www.cnblogs.com/hongten/gallery/image/112452.html
/spring_1100_spring+jdbc/src/com/b510/bean/Person.java
1 package com.b510.bean;
2
3 /**
4 * 普通的javaBean类Person
5 *
6 * @author Hongten
7 *
8 */
9 public class Person {
10
11 /**
12 * id号
13 */
14 private int id;
15 /**
16 * 姓名
17 */
18 private String name;
19 /**
20 * 年龄
21 */
22 private int age;
23 /**
24 * 性别
25 */
26 private String sex;
27
28
29 public Person(int id, String name, int age, String sex) {
30 this.id = id;
31 this.name = name;
32 this.age = age;
33 this.sex = sex;
34 }
35
36 public Person() {
37 }
38
39 public int getId() {
40 return id;
41 }
42
43 public void setId(int id) {
44 this.id = id;
45 }
46
47 public String getName() {
48 return name;
49 }
50
51 public void setName(String name) {
52 this.name = name;
53 }
54
55 public int getAge() {
56 return age;
57 }
58
59 public void setAge(int age) {
60 this.age = age;
61 }
62
63 public String getSex() {
64 return sex;
65 }
66
67 public void setSex(String sex) {
68 this.sex = sex;
69 }
70
71 }
/spring_1100_spring+jdbc/src/com/b510/service/PersonService.java
1 package com.b510.service;
2
3 import java.util.List;
4
5 import com.b510.bean.Person;
6
7 public interface PersonService {
8
9 /**
10 * 保存Person
11 *
12 * @param person
13 */
14 public abstract void save(Person person);
15
16 /**
17 * 更新Person
18 *
19 * @param person
20 */
21 public abstract void update(Person person);
22
23 /**
24 * 获取Person
25 *
26 * @param id
27 * @return
28 */
29 public abstract Person getPerson(Integer id);
30
31 /**
32 * 获取所有Person
33 *
34 * @return
35 */
36 public abstract List<Person> getPerson();
37
38 /**
39 * 删除指定id的Person
40 *
41 * @param id
42 */
43 public abstract void delete(Integer id);
44
45 }
/spring_1100_spring+jdbc/src/com/b510/service/impl/PersonServiceBean.java
1 package com.b510.service.impl;
2
3 import java.util.List;
4
5 import javax.sql.DataSource;
6
7 import org.springframework.jdbc.core.JdbcTemplate;
8
9 import com.b510.bean.Person;
10 import com.b510.service.PersonService;
11
12 /**
13 * 业务bean
14 *
15 * @author Hongten
16 *
17 */
18 public class PersonServiceBean implements PersonService {
19
20 /**
21 * 数据源
22 */
23 private DataSource dataSource;
24 /**
25 * spring提供的jdbc操作辅助类
26 */
27 private JdbcTemplate jdbcTemplate;
28
29 // 设置数据源
30 public void setDataSource(DataSource dataSource) {
31 this.jdbcTemplate = new JdbcTemplate(dataSource);
32 }
33
34 public void save(Person person) {
35 jdbcTemplate.update("insert into person(name,age,sex)values(?,?,?)",
36 new Object[] { person.getName(), person.getAge(),
37 person.getSex() }, new int[] { java.sql.Types.VARCHAR,
38 java.sql.Types.INTEGER, java.sql.Types.VARCHAR });
39 }
40
41 public void update(Person person) {
42 jdbcTemplate.update("update person set name=?,age=?,sex=? where id=?",
43 new Object[] { person.getName(), person.getAge(),
44 person.getSex(), person.getId() }, new int[] {
45 java.sql.Types.VARCHAR, java.sql.Types.INTEGER,
46 java.sql.Types.VARCHAR, java.sql.Types.INTEGER });
47
48 }
49
50 public Person getPerson(Integer id) {
51 Person person = (Person) jdbcTemplate.queryForObject(
52 "select * from person where id=?", new Object[] { id },
53 new int[] { java.sql.Types.INTEGER }, new PersonRowMapper());
54 return person;
55
56 }
57
58 @SuppressWarnings("unchecked")
59 public List<Person> getPerson() {
60 List<Person> list = jdbcTemplate.query("select * from person", new PersonRowMapper());
61 return list;
62
63 }
64
65 public void delete(Integer id) {
66 jdbcTemplate.update("delete from person where id = ?", new Object[] { id },
67 new int[] { java.sql.Types.INTEGER });
68
69 }
70 }
/spring_1100_spring+jdbc/src/com/b510/service/impl/PersonRowMapper.java
1 package com.b510.service.impl;
2
3 import java.sql.ResultSet;
4 import java.sql.SQLException;
5
6 import org.springframework.jdbc.core.RowMapper;
7
8 import com.b510.bean.Person;
9
10 public class PersonRowMapper implements RowMapper {
11
12 @Override
13 public Object mapRow(ResultSet set, int index) throws SQLException {
14 Person person = new Person(set.getInt("id"), set.getString("name"), set
15 .getInt("age"), set.getString("sex"));
16 return person;
17 }
18
19 }
/spring_1100_spring+jdbc/src/com/b510/test/SpringJDBCTest.java
1 package com.b510.test;
2
3 import java.util.List;
4
5 import org.springframework.context.ApplicationContext;
6 import org.springframework.context.support.ClassPathXmlApplicationContext;
7
8 import com.b510.bean.Person;
9 import com.b510.service.PersonService;
10
11 public class SpringJDBCTest {
12
13 public static void main(String[] args) {
14 ApplicationContext act = new ClassPathXmlApplicationContext("bean.xml");
15
16 PersonService personService = (PersonService) act
17 .getBean("personService");
18
19 Person person = new Person();
20 person.setName("苏东坡");
21 person.setAge(21);
22 person.setSex("男");
23
24 // 保存一条记录
25 personService.save(person);
26
27 List<Person> person1 = personService.getPerson();
28 System.out.println("++++++++得到所有Person");
29 for (Person person2 : person1) {
30 System.out.println(person2.getId() + " " + person2.getName()
31 + " " + person2.getAge() + " " + person2.getSex());
32 }
33 Person updatePerson = new Person();
34 updatePerson.setName("Divide");
35 updatePerson.setAge(20);
36 updatePerson.setSex("男");
37 updatePerson.setId(5);
38 // 更新一条记录
39 personService.update(updatePerson);
40 System.out.println("******************");
41
42 // 获取一条记录
43 Person onePerson = personService.getPerson(2);
44 System.out.println(onePerson.getId() + " " + onePerson.getName()
45 + " " + onePerson.getAge() + " " + onePerson.getSex());
46 // 删除一条记录
47 personService.delete(1);
48 }
49 }
/spring_1100_spring+jdbc/src/bean.xml
1 <?xml version="1.0" encoding="UTF-8"?>
2 <beans xmlns="http://www.springframework.org/schema/beans"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
4 xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
5 xsi:schemaLocation="http://www.springframework.org/schema/beans
6 http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
7 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
8 http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
9 http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
10 <!--配置数据源 -->
11 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
12 destroy-method="close">
13 <property name="driverClassName" value="org.gjt.mm.mysql.Driver" />
14 <property name="url"
15 value="jdbc:mysql://localhost:3307/spring?useUnicode=true&characterEncoding=UTF-8" />
16 <property name="username" value="root" />
17 <property name="password" value="root" />
18 <!-- 连接池启动时的初始值 -->
19 <property name="initialSize" value="1" />
20 <!-- 连接池的最大值 -->
21 <property name="maxActive" value="300" />
22 <!-- 最大空闲值.当经过一个高峰时间后，连接池可以慢慢将已经用不到的连接慢慢释放一部分，一直减少到maxIdle为止 -->
23 <property name="maxIdle" value="2" />
24 <!-- 最小空闲值.当空闲的连接数少于阀值时，连接池就会预申请去一些连接，以免洪峰来时来不及申请 -->
25 <property name="minIdle" value="1" />
26 </bean>
27 <!--
28 采用注解方式来配置事务。针对数据源的事务管理器
29 ，把我们定义的数据源注入到DataSourceTransactionManager类的属性dataSource中
30 -->
31 <bean id="txManager"
32 class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
33 <property name="dataSource" ref="dataSource" />
34 </bean>
35 <!--
36 引入命名空间： 1.xmlns:tx="http://www.springframework.org/schema/tx
37 2.http://www.springframework.org/schema/tx
38 http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
39 采用@Transaction注解方式使用事务管理器
40 -->
41 <tx:annotation-driven transaction-manager="txManager" />
42
43 <!-- 配置业务bean：PersonServiceBean -->
44 <bean id="personService" class="com.b510.service.impl.PersonServiceBean">
45 <!-- 向属性dataSource注入数据源 -->
46 <property name="dataSource" ref="dataSource"></property>
47 </bean>
48 </beans>
运行结果；
1 2012-3-9 23:30:57 org.springframework.context.support.AbstractApplicationContext prepareRefresh
2 信息: 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 hierarchy
3 2012-3-9 23:30:57 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
4 信息: Loading XML bean definitions from class path resource [bean.xml]
5 2012-3-9 23:30:58 org.springframework.context.support.AbstractApplicationContext obtainFreshBeanFactory
6 信息: Bean factory for application context [org.springframework.context.support.ClassPathXmlApplicationContext@1a05308]: org.springframework.beans.factory.support.DefaultListableBeanFactory@2bb514
7 2012-3-9 23:30:58 org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
8 信息: 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
9 ++++++++得到所有Person
10 2 TomCat 12 女
11 3 hongten 21 男
12 4 liufang 21 女
13 5 Divide 20 男
14 6 Jone 20 女
15 7 苏东坡 21 男
16 ******************
17 2 TomCat 12 女
当然我们可以用配置文件来存放我们的数据源信息：
/spring_1100_spring+jdbc/src/jdbc.properties
1 driverClassName=org.gjt.mm.mysql.Driver
2 url=jdbc\:mysql\://localhost\:3307/spring?useUnicode\=true&characterEncoding\=UTF-8
3 username=root
4 password=root
5 initialSize=1
6 maxActive=300
7 maxIdle=2
8 minIdle=1
相应要修改：
/spring_1100_spring+jdbc/src/bean.xml
1 <?xml version="1.0" encoding="UTF-8"?>
2 <beans xmlns="http://www.springframework.org/schema/beans"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
4 xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
5 xsi:schemaLocation="http://www.springframework.org/schema/beans
6 http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
7 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
8 http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
9 http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
10
11 <!-- 读取jdbc.properties配置文件 -->
12 <context:property-placeholder location="classpath:jdbc.properties" />
13 <!--配置数据源 -->
14 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
15 destroy-method="close">
16 <property name="driverClassName" value="${driverClassName}" />
17 <property name="url" value="${url}" />
18 <property name="username" value="${username}" />
19 <property name="password" value="${password}" />
20 <!-- 连接池启动时的初始值 -->
21 <property name="initialSize" value="${initialSize}" />
22 <!-- 连接池的最大值 -->
23 <property name="maxActive" value="${maxActive}" />
24 <!-- 最大空闲值.当经过一个高峰时间后，连接池可以慢慢将已经用不到的连接慢慢释放一部分，一直减少到maxIdle为止 -->
25 <property name="maxIdle" value="${maxIdle}" />
26 <!-- 最小空闲值.当空闲的连接数少于阀值时，连接池就会预申请去一些连接，以免洪峰来时来不及申请 -->
27 <property name="minIdle" value="${minIdle}" />
28 </bean>
29 <!--
30 采用注解方式来配置事务。针对数据源的事务管理器
31 ，把我们定义的数据源注入到DataSourceTransactionManager类的属性dataSource中
32 -->
33 <bean id="txManager"
34 class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
35 <property name="dataSource" ref="dataSource" />
36 </bean>
37 <!--
38 引入命名空间： 1.xmlns:tx="http://www.springframework.org/schema/tx
39 2.http://www.springframework.org/schema/tx
40 http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
41 采用@Transaction注解方式使用事务管理器
42 -->
43 <tx:annotation-driven transaction-manager="txManager" />
44
45 <!-- 配置业务bean：PersonServiceBean -->
46 <bean id="personService" class="com.b510.service.impl.PersonServiceBean">
47 <!-- 向属性dataSource注入数据源 -->
48 <property name="dataSource" ref="dataSource"></property>
49 </bean>
50 </beans>
运行结果是相同的：
1 2012-3-10 0:23:59 org.springframework.context.support.AbstractApplicationContext prepareRefresh
2 信息: 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 hierarchy
3 2012-3-10 0:23:59 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
4 信息: Loading XML bean definitions from class path resource [bean.xml]
5 2012-3-10 0:23:59 org.springframework.context.support.AbstractApplicationContext obtainFreshBeanFactory
6 信息: Bean factory for application context [org.springframework.context.support.ClassPathXmlApplicationContext@c1b531]: org.springframework.beans.factory.support.DefaultListableBeanFactory@1aa57fb
7 2012-3-10 0:23:59 org.springframework.core.io.support.PropertiesLoaderSupport loadProperties
8 信息: Loading properties file from class path resource [jdbc.properties]
9 2012-3-10 0:23:59 org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
10 信息: 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
11 ++++++++得到所有Person
12 2 TomCat 12 女
13 3 hongten 21 男
14 4 liufang 21 女
15 5 Divide 20 男
16 6 Jone 20 女
17 7 苏东坡 21 男
18 8 苏东坡 21 男
19 ******************
20 2 TomCat 12 女
