Spring架構之JDBC操作（3.0）

使用C3P0資料庫連接配接池

1，建立資料庫腳本；

-- 删除資料庫
DROP DATABASE IF EXISTS mldn ;
-- 建立資料庫
CREATE DATABASE mldn CHARACTER SET UTF8 ;
-- 使用資料庫
USE mldn ;
-- 删除資料表
DROP TABLE IF EXISTS member ;
-- 建立member表
CREATE TABLE member(
	mid			VARCHAR(50) ,
	name		VARCHAR(50) ,
	age			INT ,
	sex			VARCHAR(10) ,
	birthday 	DATE ,
	salary		DOUBLE ,
	note		TEXT ,
	CONSTRAINT pk_mid PRIMARY KEY(mid) 
) type=innodb ;
-- 增加測試資料
INSERT INTO member(mid,name,age,birthday,salary,note,sex) VALUES ('mldn-a','張三',10,'1991-11-11',11.1,'不錯','男') ;
INSERT INTO member(mid,name,age,birthday,salary,note,sex) VALUES ('mldn-b','李四',10,'1991-11-12',21.1,'很好','男') ;
INSERT INTO member(mid,name,age,birthday,salary,note,sex) VALUES ('mldn-c','王五',10,'1991-11-13',31.1,'湊合','女') ;
INSERT INTO member(mid,name,age,birthday,salary,note,sex) VALUES ('mldn-d','趙六',10,'1991-11-14',41.1,'邋遢','女') ;
INSERT INTO member(mid,name,age,birthday,salary,note,sex) VALUES ('mldn-e','孫七',10,'1991-11-15',51.1,'肮髒','女') ;
INSERT INTO member(mid,name,age,birthday,salary,note,sex) VALUES ('mldn-f','王八',10,'1991-11-16',61.1,'龌龊','男') ;
           

2，建立memeber.java類；

package cn.mldn.vo;
import java.io.Serializable;
import java.util.Date;
@SuppressWarnings("serial")
public class Member implements Serializable {
	private String mid ;
	private String name ;
	private Integer age ;
	private String sex ;
	private Date birthday ;
	private Double salary ;
	private String note ;
	public String getMid() {
		return mid;
	}
	public void setMid(String mid) {
		this.mid = mid;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public Double getSalary() {
		return salary;
	}
	public void setSalary(Double salary) {
		this.salary = salary;
	}
	public String getNote() {
		return note;
	}
	public void setNote(String note) {
		this.note = note;
	}
	@Override
	public String toString() {
		return "Member [mid=" + mid + ", name=" + name + ", age=" + age
				+ ", sex=" + sex + ", birthday=" + birthday + ", salary="
				+ salary + ", note=" + note + "]\n";
	} 
}
           

3，書寫一個database.properties檔案：

#
db.driver=org.gjt.mm.mysql.Driver
#
db.url=jdbc:mysql://localhost:3306/mldn
#
db.user=root
#
db.password=mysqladmin
#
pool.max=100
#
pool.min=20
#
pool.init=10
#
pool.idle=100
           

4，修改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:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd">
	<context:annotation-config/>
	<context:component-scan base-package="cn.mldn"/>
	<context:property-placeholder location="classpath:database.properties"/>
	<bean id="dataSource" 
		class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="${db.driver}"/>
		<property name="jdbcUrl" value="${db.url}"/>
		<property name="user" value="${db.user}"/>
		<property name="password" value="${db.password}"/>
		<property name="maxPoolSize" value="${pool.max}"/>
		<property name="minPoolSize" value="${pool.min}"/>
		<property name="initialPoolSize" value="${pool.init}"/>
		<property name="maxIdleTime" value="${pool.idle}"/>
	</bean>
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"/>
	</bean> 
</beans>
           

5，編寫測試類TestC3P0.java:

package cn.mldn.demo;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import cn.mldn.vo.Member;
public class TestC3P0 {
	public static void main(String[] args) throws Exception {
		ApplicationContext ctx = new ClassPathXmlApplicationContext(
				"applicationContext.xml");
		JdbcTemplate jt = ctx.getBean("jdbcTemplate",JdbcTemplate.class) ;
		String sql = "SELECT mid,name,age,sex,birthday,salary,note FROM member";
		List<Member> all = jt.query(sql, new RowMapper<Member>() {
			@Override 
			public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
				System.out.println("目前處理行：" + rowNum);
				Member vo = new Member() ;
				vo.setMid(rs.getString(1));
				vo.setName(rs.getString(2));
				vo.setAge(rs.getInt(3));
				vo.setSex(rs.getString(4));
				vo.setBirthday(rs.getDate(5));
				vo.setSalary(rs.getDouble(6));
				vo.setNote(rs.getString(7));
				return vo;
			}}) ;
		System.out.println(all);
	}
}
           

5,運作結果：

記得修改裡面的c3p0-0.9.1.2.jar包：