Servlet与数据库交互--JDBC&DBCP

一、JDBC概述
为什么要使用ＪＤＢＣ？

JDBC:java database connectivity SUN公司提供的一套操作数据库的标准规范。
JDBC与数据库驱动的关系：接口与实现的关系。

JDBC规范（掌握四个核心对象）：
DriverManager:用于注册驱动
Connection: 表示与数据库创建的连接
Statement: 操作数据库sql语句的对象
ResultSet: 结果集或一张虚拟表

开发一个JDBC程序的准备工作：
> JDBC规范在哪里：
 JDK中：
java.sql.*;
javax.sql.*;
> 数据库厂商提供的驱动：jar文件
  *.jar

二、开发一个JDBC程序（重要）
实现查询数据库中的数据显示在java的控制台中

1、创建数据库表，并向表中添加测试数据

create database day06;
use day06;

create table users(
    id int primary key auto_increment,
    name varchar(40),
    password varchar(40),
    email varchar(60),
    birthday date
)character set utf8 collate utf8_general_ci;

insert into users(name,password,email,birthday) values('zs','123456','zs@sina.com','1980-12-04');
insert into users(name,password,email,birthday) values('lisi','123456','lisi@sina.com','1981-12-04');
insert into users(name,password,email,birthday) values('wangwu','123456','wangwu@sina.com','1979-12-04');

2、创建java project项目，添加数据库驱动（*.jar）

3、实现JDBC操作
        //1、注册驱动
        //2、创建连接
        //3、得到执行sql语句的Statement对象
        //4、执行sql语句，并返回结果
        //5、处理结果
        //6、关闭资源

三、JDBC常用的类和接口详解
1、java.sql.Drivermanager类 : 创建连接

a、注册驱动
DriverManager.registerDriver(new com.mysql.jdbc.Driver());不建议使用
原因有2个：
> 导致驱动被注册2次。
> 强烈依赖数据库的驱动jar
解决办法：
Class.forName("com.mysql.jdbc.Driver");
b、与数据库建立连接
static Connection getConnection(String url, String user, String password) 
          试图建立到给定数据库 URL 的连接。

getConnection("jdbc:mysql://localhost:3306/day06", "root", "root");

URL:SUN公司与数据库厂商之间的一种协议。
jdbc:mysql://localhost:3306/day06
协议 子协议  IP :端口号 数据库
mysql: jdbc:mysql://localhost:3306/day14 或者 jdbc:mysql:///day14（默认本机连接）
oracle: jdbc:oracle:thin:@localhost:1521:sid

2、java.sql.Connection接口：一个连接
接口的实现在数据库驱动中。所有与数据库交互都是基于连接对象的。

Statement  createStatement(); //创建操作sql语句的对象

3、java.sql.Statement接口: 操作sql语句，并返回相应结果的对象（小货车）
接口的实现在数据库驱动中。用于执行静态 SQL 语句并返回它所生成结果的对象。 
ResultSet  executeQuery(String sql) 根据查询语句返回结果集。只能执行select语句。
 int executeUpdate(String sql) 根据执行的DML（insert update delete）语句，返回受影响的行数。
boolean execute(String sql)  此方法可以执行任意sql语句。返回boolean值，表示是否返回ResultSet结果集。仅当执行select语句，且有返回结果时返回true, 其它语句都返回false;
4、java.sql.ResultSet接口: 结果集（客户端存表数据的对象）
a、封装结果集的。
提供一个游标，默认游标指向结果集第一行之前。
调用一次next()，游标向下移动一行。
提供一些get方法。

封装数据的方法
Object getObject(int columnIndex); 根据序号取值，索引从1开始
Object getObject(String ColomnName); 根据列名取值。

将结果集中的数据封装到javaBean中
java的数据类型与数据库中的类型的关系
byte          tityint
short         smallint
int           int
long          bigint
float         float
double        double
String        char varchar 
Date         date

boolean next()    将光标从当前位置向下移动一行
int getInt(int colIndex)    以int形式获取ResultSet结果集当前行指定列号值
int getInt(String colLabel)    以int形式获取ResultSet结果集当前行指定列名值
float getFloat(int colIndex)    以float形式获取ResultSet结果集当前行指定列号值
float getFloat(String colLabel)    以float形式获取ResultSet结果集当前行指定列名值
String getString(int colIndex)    以String 形式获取ResultSet结果集当前行指定列号值
String getString(String colLabel)    以String形式获取ResultSet结果集当前行指定列名值
Date getDate(int columnIndex);  
Date getDate(String columnName);
void close()    关闭ResultSet 对象

b、可移动游标的方法
 boolean next()  将光标从当前位置向前移一行。 
     boolean previous() 
             将光标移动到此 ResultSet 对象的上一行。 
     boolean absolute(int row) 参数是当前行的索引，从1开始
             根据行的索引定位移动的指定索引行。
 void afterLast() 
          将光标移动到末尾，正好位于最后一行之后。 
 void beforeFirst() 
          将光标移动到开头，正好位于第一行之前。 

5、释放资源 
资源有限，要正确关闭。

四、使用JDBC实现CRUD操作

五、读取properties配置文件
- 用于读取*.properties配置文件里面的数据
- 使用方式：

//创建属性对象
Properties properties = new Properties();
//得到文件输入流
InputStream inputStream =         
                     Demo04.class.getClassLoader().getResourceAsStream("jdbc.properties");
//把文件加载到属性对象中
properties.load(inputStream);
//读取数据
String name = properties.getProperty("driver");
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
System.out.println(name + " " + url + " " + username + " " + password + ".");

六、SQL注入

' or '1'='1
SELECT COUNT(*) FROM jdbc_user WHERE username = 'fasdgas' AND password = '' or '1'='1'

七、Statement&PreparedStatement
- PreparedStatement预编译的SQL执行对象
    1. 好处是可以避免SQL注入,因为在编译的时候已经把SQL的逻辑固定,不会因为替换进去的内容改变逻辑
    2. 如果SQL中涉及变量,相比Statement的字符串拼接的方式,代码的可读性提高,并且不容易出错
    3. 如果涉及批量执行多条SQL时,效率较高,PreparedStatement不用每一次去编译SQL语句
- 如果SQL中没有变量用statement,如果有变量,则用PreparedStatement


八、批量操作
- statement批量操作

connection = DBUtils.getConnection();
statement = connection.createStatement();
String sql1 = "INSERT INTO jdbc_user VALUES('八戒','a')";
String sql2 = "INSERT INTO jdbc_user VALUES('悟空','b')";
String sql3 = "INSERT INTO jdbc_user VALUES('沙僧','c')";
//把三条sql合并到一次批量操作
statement.addBatch(sql1);
statement.addBatch(sql2);
statement.addBatch(sql3);
//执行批量操作
statement.executeBatch();

- perparedStatement批量操作

String sql = "INSERT INTO jdbc_user VALUES(?,?)";
statement = connection.prepareStatement(sql);
for(int i = 0 ; i < 1000 ;i++){
	statement.setString(1, "name"+i);
	statement.setString(2, "admin"+i);
	//添加到批量操作
	statement.addBatch();
	if(i%20==0){
		//执行批量操作
		statement.executeBatch();
		//每20条执行一次批量操作,结束清空执行过的SQL
		statement.clearBatch();
	}
}
//执行批量操作
statement.executeBatch();
System.out.println("结束");

九、JDBC的事务操作
1. 开启或关闭自动提交
    connection.setAutoCommit(true/false);
2. 提交事务
    connection.commit();
3. 回滚
    connection.rollback();
十、获取自增主键的值

创建球队表team,球员表player,一对多关系

create table team(id int primary key auto_increment,name varchar(10));
create table player(id int primary key auto_increment,name varchar(10),tid int);

connection = DBUtils.getConnection();
//先查询球队是否存在,如果存在则需要得到球队的id
String s = "SELECT id from team WHERE name = ?";
statement = connection.prepareStatement(s);
statement.setString(1, teamName);
resultSet = statement.executeQuery();
int teamId = -1;
while(resultSet.next()){
	teamId = resultSet.getInt("id");//得到teamId,保存过球队
}
if(teamId == -1){//未保存过球队
	//插入球队
	String sql = "INSERT INTO team VALUES(NULL,?)";
	//返回生成的主键
	statement.close();
	statement = connection.prepareStatement(sql,statement.RETURN_GENERATED_KEYS);
	statement.setString(1, teamName);
	statement.executeUpdate();
	//获取自增主键的值
	resultSet.close();
	resultSet = statement.getGeneratedKeys();
	while(resultSet.next()){
		teamId = resultSet.getInt(1);
	}
}
//插入球员
String sql1 = "INSERT INTO player VALUES(NULL,?,?)";
statement = connection.prepareStatement(sql1);
statement.setString(1, playerName);
statement.setInt(2, teamId);
statement.executeUpdate();

十一、元数据
- 元数据指 数据库和表的相关信息
 

//获取数据库的元数据对象
DatabaseMetaData databaseMetaData = connection.getMetaData();
System.out.println("驱动版本:" + databaseMetaData.getDriverVersion());
System.out.println("用户名:" + databaseMetaData.getUserName());
System.out.println("连接地址:" + databaseMetaData.getURL());
System.out.println("数据库名称:" + databaseMetaData.getDatabaseProductName());

//获取表的元数据
resultSet = statement.executeQuery("SELECT * FROM emp");
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
//得到表的字段数量
	int colunCount = resultSetMetaData.getColumnCount();
System.out.println(colunCount);
//得到字段的名称
for(int i = 1 ; i <= colunCount ;i++){
	String name = resultSetMetaData.getColumnName(i);
	String type = resultSetMetaData.getColumnTypeName(i);
	System.out.println(name + ":" + type);
}

附:完整的DBUtils

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

/**
 * 在该类中主要封装两个方法 1. 获取链接 2. 关闭资源
 * 
 * @author Mr.Huang
 *
 */
public class DBUtils {
	private static BasicDataSource dataSource;
	
	static {
		// 创建properties对象
		Properties properties = new Properties();
		// 获取文件输入流
		InputStream inputStream = DBUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
		// 将文件加载进properties中
		try {
			properties.load(inputStream);
			// 读取数据
			String driver = properties.getProperty("driver");
			String url = properties.getProperty("url");
			String username = properties.getProperty("username");
			String password = properties.getProperty("password");
			/*
			 * // 注册驱动 Class.forName(driver); // 获取链接 Connection connection =
			 * DriverManager.getConnection(url, username, password);
			 */
			/* ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓更改为使用dbcp数据库连接池获取连接↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ */
			// 创建数据源对象
			dataSource = new BasicDataSource();
			dataSource.setDriverClassName(driver);
			dataSource.setUrl(url);
			dataSource.setUsername(username);
			dataSource.setPassword(password);
			dataSource.setInitialSize(3);
			dataSource.setMaxActive(5);
			// 获取连接
			// Connection connection = dataSource.getConnection();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			// 关闭文件输入流
			try {
				inputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 获取连接的方法
	 * 
	 * @return Connection
	 * @throws Exception
	 */
	public static Connection getConnection() throws Exception {
		return dataSource.getConnection();
	}

	/**
	 * 关闭数据库资源
	 * 
	 * @param connection
	 * @param statement
	 * @param resultSet
	 */
	public static void close(Connection connection, Statement statement, ResultSet resultSet) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}