詳解spring開發_JDBC操作MySQL數據庫

本文介紹spring開發_JDBC操作MySQL數據庫，具體如下：

項目結構：

數據庫表：

/spring_1100_spring+jdbc/src/com/b510/bean/Person.java

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

/spring_1100_spring+jdbc/src/com/b510/service/PersonService.java

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

/spring_1100_spring+jdbc/src/com/b510/service/impl/PersonServiceBean.java

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

/spring_1100_spring+jdbc/src/com/b510/service/impl/PersonRowMapper.java

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

/spring_1100_spring+jdbc/src/com/b510/test/SpringJDBCTest.java

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

/spring_1100_spring+jdbc/src/bean.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" 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" 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" > <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"> <!-- 向屬性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 女

當然我們可以用配置文件來存放我們的數據源信息：

/spring_1100_spring+jdbc/src/jdbc.properties

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

相應要修改：

/spring_1100_spring+jdbc/src/bean.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" 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" 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" > <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"> <!-- 向屬性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 女

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持武林網。