Spring的JDBC支援

1. 建立專案

2. pom.xml新增jar依賴

<properties>
	<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	<spring.version>5.0.9.RELEASE</spring.version>
</properties>

<dependencies>
	<!-- spring需要的jar包依賴 -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-core</artifactId>
		<version>${spring.version}</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-context</artifactId>
		<version>${spring.version}</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-test</artifactId>
		<version>${spring.version}</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-tx</artifactId>
		<version>${spring.version}</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-beans</artifactId>
		<version>${spring.version}</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-jdbc</artifactId>
		<version>${spring.version}</version>
	</dependency>
	
	<dependency>
		<groupId>commons-dbcp</groupId>
		<artifactId>commons-dbcp</artifactId>
		<version>1.4</version>
	</dependency>
	<dependency>
		<groupId>commons-pool</groupId>
		<artifactId>commons-pool</artifactId>
		<version>1.6</version>
	</dependency>

	<!-- mysql的jar包依賴 -->
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>8.0.12</version>
	</dependency>
	<dependency>
		<groupId>junit</groupId>
		<artifactId>junit</artifactId>
		<version>4.11</version>
		<scope>test</scope>
	</dependency>
</dependencies>

3. 建立表結構和準備資料

CREATE TABLE `article` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL DEFAULT '',
  `content` text,
  `created` datetime NOT NULL DEFAULT '2018-09-10 00:00:00',
  PRIMARY KEY (`id`)
);
INSERT INTO `article` VALUES ('1', 'Spring的Jdbc', 'Spring的Jdbc詳解。。。', '2018-09-12 20:11:23');

4. 建立實體類

public class Article {
	private int id;
	private String title;
	private String content;
	
	public Article(int id, String title, String content) {
		super();
		this.id = id;
		this.title = title;
		this.content = content;
	}
	
	// getters and setter
}

5. 建立資料訪問物件 (Dao) 模式

public interface ArticleDao {
	public List<Article> getArticles();

	public Article getArticleById(int id);
	
	public void insert(Article article);
}

public class JdbcArticleDao implements ArticleDao {

	private DataSource dataSource;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List<Article> getArticles() {
		String sql = "SELECT * FROM ARTICLE";
		Connection conn = null;

		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			List<Article> articles = new ArrayList<Article>();

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Article article = new Article(rs.getInt("ID"), rs.getString("TITLE"), rs.getString("CONTENT"));
				articles.add(article);
			}
			rs.close();
			ps.close();
			return articles;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public Article getArticleById(int id) {
		String sql = "SELECT * FROM ARTICLE WHERE ID = ?";
		Connection conn = null;

		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			Article article = null;
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				article = new Article(rs.getInt("ID"), rs.getString("TITLE"), rs.getString("CONTENT"));
			}
			rs.close();
			ps.close();
			return article;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public void insert(Article article) {
		String sql = "INSERT INTO ARTICLE (ID, TITLE, CONTENT) VALUES (?, ?, ?)";
		Connection conn = null;

		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, article.getId());
			ps.setString(2, article.getTitle());
			ps.setString(3, article.getContent());
			ps.executeUpdate();
			ps.close();

		} catch (SQLException e) {
			throw new RuntimeException(e);

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}
}

6. Spring bean配置檔案Spring-Bean.xml，Spring-Datasource.xml和Spring-Module.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"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

	<bean id="articleDao" class="com.angelia.spring.dao.JdbcArticleDao">
		<property name="dataSource" ref="dataSource" />
	</bean>

</beans>

<?xml version="1.0" encoding="utf-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/myspring?useSSL=false&amp;serverTimezone=UTC" />
		<property name="username" value="root" />
		<property name="password" value="root" />
	</bean>

</beans>

<?xml version="1.0" encoding="utf-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

	<import resource="Spring-Datasource.xml" />
	<import resource="Spring-Bean.xml" />

</beans>

7. 測試類

public class ArticleTest {
	private static ApplicationContext ctx;
	static {
		ctx = new ClassPathXmlApplicationContext("Spring-Module.xml");
	}

	@Test
	public void shouldAnswerWithTrue() {

		ArticleDao articleDao = (ArticleDao) ctx.getBean("articleDao");
		Article article = new Article(2, "Spring的Jdbc", "Spring的Jdbc內容");
		articleDao.insert(article);

		Article article1 = articleDao.getArticleById(14);
		System.out.println(article1);

		List<Article> articles = articleDao.getArticles();
		System.out.println(articles);
	}
}