最近学习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]