最近學習MyBatis,發現把前幾個月學的資料庫忘得差不多了,當再次練習使用dbcp2資料庫連接配接池時候,全手打又出了一些問題,後來發現錯誤主要是某些小的細節,以為自己會了、沒有問題,但是一不留神就犯錯了,是以,做個學習小結,供大家學習參考。可以先複制粘貼試試,如果不能運作,那很可能是你的開發環境搭建問題;如果運作正常,再手打一遍試試,一個小時足夠了,對比找找自己錯漏的地方。如有大神路過,請幫忙指導下學習路徑,拜謝!!!
一、開發環境(環境搭建可以自行度娘)
jdk-8u144-windows-x64,eclipse-jee-neon-3-win32-x86_64,apache-tomcat-8.5.20
二、建立Maven項目并從pom添加所需的jar包
2.1建立Maven項目後,準備工作:
2.1.1在Deployment Descriptor:(你的工程名),右鍵,Generate Deployment Descriptor Stub;
2.1.2在工程名右鍵,屬性,Targeted Runtimes,勾選你的tomcat版本;
2.1.3從pom檔案導入所需的jar包,也可以自己從pom的Dependencies添加spring-webmvc+dbcp2+junit,或者直接負責替換掉pom的内容。
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.springtest</groupId>
<artifactId>Springwebmvc_login</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.3.10.RELEASE</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.10.RELEASE</version>
</dependency>
</dependencies>
</project>
2.1Maven項目完整目錄
三、spring的配置檔案與資料庫屬性檔案(路徑在src/main/resources下)
3.1配置檔案spring_datasourcetest.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:jdbc="http://www.springframework.org/schema/jdbc"
xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:jpa="http://www.springframework.org/schema/data/jpa"
xmlns:util="http://www.springframework.org/schema/util"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd">
<!-- 配置元件掃描 -->
<context:component-scan base-package="com.springtest" />
<!-- 資料庫配置檔案 -->
<util:properties id="DBConfig" location="classpath:db.properties" />
<!-- 配置資料庫連接配接池 -->
<bean id="ds" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close" >
<property name="driverClassName" value="#{DBConfig.driver}" />
<property name="url" value="#{DBConfig.url}" />
<property name="username" value="#{DBConfig.username}" />
<property name="password" value="#{DBConfig.password}" />
</bean>
</beans>
3.2資料庫配置檔案db.properties,此處要根據你的實際修改(後面涉及到從表裡讀取資料,注意修改為你自己的表名),我的資料庫是mytest,表名為account(有id,username,password三個字段)。注意事項:該檔案對key、value對要求非常苛刻,不能有空格和引号等,漢字注釋都不可以。
# Database Properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mytest
username=root
password=root123
四、建立模型層與DAO層程式(路徑在src/main/java下)
4.1模型層
package com.springtest.entity;
/**
* 與資料庫表各字段相對應的javaBean
*
*/
public class Account {
private Integer id;
private String username;
private String password;
//setter()與getter()方法
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "Account [id=" + id + ", username=" + username + ", password=" + password + "]";
}
}
4.2建立DAO層(路徑在src/main/java下)
4.2.1AccountDao接口
package com.springtest.dao;
/**
* 資料庫操作層接口
*
*/
public interface AccountDao {
//測試dataSource,該方法列印資料庫表的所有資料
public void accountTest();
}
4.2.2AccountDao接口實作類
package com.springtest.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.springframework.stereotype.Repository;
import com.springtest.entity.Account;
/**
* 資料庫操作層接口的實作類
*
*/
@Repository("accountDao")
public class AccountDaoImpl implements AccountDao {
@Resource(name="ds")
private DataSource dataSource;
//測試資料庫是否可以正常連接配接
public void accountTest() {
Connection conn = null;
try {
//擷取資料庫連接配接
conn = dataSource.getConnection();
//建立SQL查詢
StringBuilder sql = new StringBuilder();
sql.append("SELECT id,username,password FROM account");
PreparedStatement ptmt = conn.prepareStatement( sql.toString() );
//擷取結果集
ResultSet rs = ptmt.executeQuery();
Account account = null;
//列印輸出結果集資料
while( rs.next() ){
account = new Account();
account.setId( rs.getInt("id") );
account.setUsername( rs.getString("username") );
account.setPassword( rs.getString("password") );
//列印輸出擷取的行資料
System.out.println( account );
}
//關閉資料源
rs.close();
ptmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally{
if( conn != null ){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
五、建立測試類(路徑在src/test/java下)
測試類TestAccountDaoImpl,在方法test1()右鍵,運作方式JUint 測試,可以看到從資料庫讀取到的結果。
package com.springtest.test;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.springtest.dao.AccountDao;
/**
* 測試類
*/
public class TestAccountDaoImpl {
@Test
public void test1(){
//讀取spring配置檔案
ApplicationContext ac = new ClassPathXmlApplicationContext("spring_datasourcetest.xml");
//擷取bean對象
AccountDao accountDao = ac.getBean("accountDao",AccountDao.class);
//執行測試方法
accountDao.accountTest();
}
}
控制台輸出内容:
Account [id=1, username=Mayun, password=mayun123]
Account [id=2, username=Jingdong, password=jingdong123]
Account [id=3, username=Liubei, password=liubei123]