一、概述

为了持久化&方便管理数据，出现了mysql、sqlserver等多种数据库，我们可以直接这些数据库中使用sql语言增删改查管理数据。但是对于业务人员来说不懂sql，没法通过sql直接在数据库操作，所以我们要通过程序提供对数据库的操作；

这些数据库厂商为了让编程语言操作自身数据库，提供了支持不同语言的驱动包、比如mysql驱动包、sqlserver包等

而编程语言也提供了对接驱动包的接口，java中就是java.sql接口包，几个关系如下

二、基本使用

1、连接

public void con(){
		//把几个用于连接数据库的字符串定义成常量，不必每次去创建,这些字符串也可以通过配置文件方式读取
		String USER = "root";//数据库用户名
		String UPWD = "root";//数据库密码
		//本地数据库shop
		String URL = "jdbc:mysql://localhost:3306/db_book";
		//驱动
		String DRIVER = "com.mysql.jdbc.Driver";
		Statement st = null;
		Connection con = null;
		try {
			//加载驱动
			Class.forName(DRIVER);
			//获取连接对象
			con = DriverManager.getConnection(URL, USER, UPWD);
			//获取执行对象
			st = con.createStatement();
			String sqlString = "create table stu(id int,name varchar(25),age int)";
			//执行sql
			int row = st.executeUpdate(sqlString);
			System.err.println(row);
			
		} catch (Exception e) {
			try {
				if(st != null) {
					st.close();
				}
			} catch (SQLException e2) {
				e2.printStackTrace();
			}
			try {
				if(con != null) {
					con.close();
				}
			} catch (SQLException e3) {
				e3.printStackTrace();
			}
		}
	}

2、api

int executeUpdate(String sql) throws SQLException;//执行insert update delete
ResultSet executeQuery(String sql) throws SQLException;用于执行sql语句
boolean execute(String sql) throws SQLException;//执行create drop 等ddl语句

boolean  next():判断是否有下一行数据,若有,则向下移动一行指针
getXxx(int columnIndex):获取当前行中,第几列.(从1开始):不推荐，
getXxx(String columnName):获取当前行中的,指定列名的列的值.columnName是列名/列的别名，若列的类型是VARCHAR/CHAR/TEXT,都使用getString来获取列的值.若列的类型是int/integer/-->getInt来获取列的值.
对于上面的getint  getstring（）都可以使用getObject  或者都使用getString（）

3、查询

• 查询一个数

• 查询一行数据

• 查询多行数据

4、sql注入原理

select * from user where name = '123' and pass = '234' or 1=1
上面模拟登录 不管用户名密码对不对都能查到结果

5、Statement和Preparement（获取自动生成的主键）

statement

String sqlString = "insert into stu(name,age)value('123',20)";
			//执行sql
			st.executeUpdate(sqlString,st.RETURN_GENERATED_KEYS);
			
			ResultSet resultSet = st.getGeneratedKeys();
			if(resultSet.next()) {
				int id = resultSet.getInt(1);
				System.out.println(id);
			}

Preparement

String sqlString = "insert into stu(name,age)value(?,?)";
			
			PreparedStatement pStatement = con.prepareStatement(sqlString,Statement.RETURN_GENERATED_KEYS);
			pStatement.setString(1, "123");
			pStatement.setInt(2, 20);
			pStatement.executeUpdate();
			ResultSet resultSet = pStatement.getGeneratedKeys();
			if(resultSet.next()) {
				int id = resultSet.getInt(1);
				System.out.println(id);
			}

三、工具类

package pojo;

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

/**
 * 封装的jdbc工具类
 * 
 * @author Administrator
 *
 */
public class MyJDBCUtil {
	// 定义数据库连接对象
	private static Connection con;
	// 定义执行sql对象
	private static PreparedStatement ps;
	// 定义结果集对象
	private static ResultSet rs;
	private static String driver;
	private static String url;
	private static String username;
	private static String password;

	// 工具类的构造器私有了，不让外部调用
	private MyJDBCUtil() {
	}

	// 下面的数据可以通过配置文件读取
	static {
		try {
			readConfig();
			Class.forName(driver);
			con = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 读取配置文件
	private static void readConfig() {
		InputStream in = MyJDBCUtil.class.getClassLoader().getResourceAsStream("database.properties");
		System.out.println(in);
		Properties properties = new Properties();
		try {
			properties.load(in);
			driver = properties.getProperty("driver");
			url = properties.getProperty("url");
			username = properties.getProperty("username");
			password = properties.getProperty("password");
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	// 提供获取连接对象的公共方法
	public static Connection getConnection() {
		return con;
	}

	// 查询操作
	public static ResultSet excuteQuery(String sql, List params) throws SQLException {
		if (con == null || con.isClosed())// 连接对象是空 或者已经打开了就重新开启
		{
			getConnection();
		}
		ps = con.prepareStatement(sql);
		if (params != null && params.size() > 0) {
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
		}
		rs = ps.executeQuery();
		return rs;
	}

	// 更新操作
	public static int excuteUpdate(String sql, List params) throws SQLException {
		if (con == null || con.isClosed())// 连接对象是空 或者已经打开了就重新开启
		{
			getConnection();
		}
		ps = con.prepareStatement(sql);
		if (params != null && params.size() > 0) {
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
		}
		return ps.executeUpdate();
	}

	// 关闭资源
	public static void closeAll() {
		try {
			if (rs != null && !rs.isClosed()) {
				rs.close();
			}
			if (ps != null && !ps.isClosed()) {
				ps.close();
			}
			if (con != null && !con.isClosed()) {
				con.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

测试

public static void main(String[] args){
    String sql = "select * from tb_stu";
    ResultSet rs = MyJDBCUtil.excuteQuery(sql,null);
    while(rs.next()){
        System.out.priint(rs.getInt("id);
    }
    MyJDBCUtil.closeAll();
}
  

四、使用连接池操作数据库

1、不使用连接池缺点

每次CRUD操作都要使用数据库的时候，都要创建一个数据库连接对象；普通的JDBC数据库连接使用 DriverManager 来获取；每次向数据库建立连接的时候都要将 Connection 加载到内存中；然后再验证用户名和密码花费时间0.05s～1s左右；每次CRUD操作就向数据库要要一个连接

执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。

2、连接池原理

数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。我们可以通过设定连接池最大连接数来防止系统无尽的可以通过连接池的管理机制监视数据库的连接的数量﹑使用情况，为系统开发﹑测试及性能调整提供依据。与数据库连接

3、连接池分类

java中使用javax.sql.DataSource接口来表示连接池，DataSource和jdbc一样，也是只提供一个接口，由第三方组织来提供，DataSource数据源和连接池Connection Pool是同一个东西，只是叫法不一样而已

DBCP：Spring推荐，Tomcat的数据源使用的就是DBCP，也就是tomcat内置dbcp，单独使用dbcp需要2个包：commons-dbcp.jar,commons-pool.jar

C3P0：C3P0是一个开放源代码的JDBC连接池，它在lib目录中与Hibernate一起发布；从2007年就没有更新了，性能比较差。

Druid：阿里巴巴提供的连接池-德鲁伊-号称最好的连接池，它里面除了这些， 还有更多的功能。

4、使用连接池

DBCP连接池

导入jar包

• mysql-connector-java-5.0.4-bin.jar
• commons-pool-1.5.6.jar
• commons-dbcp-1.4.jar

src下编写database.properties文件

driverClassName	数据库名称	必填
url	数据库地址	必填
username	用户名	必填
password	密码	必填
maxActive	最大连接数量	选填
minldle	最小空闲连接	选填
maxldle	最大空闲连接	选填
initialSize	初始化连接	选填

工具类

package pojo;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;


public class DBCPUtils {
	private static DataSource dataSource;
	static{
		try {
			//1.加载找properties文件输入流
			InputStream is = DBCPUtils.class.getClassLoader().getResourceAsStream("database.properties");
			//(也可以使用流的方式加载)
			//FileInputStream is=  new FileInputStream("database.properties");
			//2.加载输入流
			Properties props = new Properties();
			props.load(is);
			//3.创建数据源
			dataSource = BasicDataSourceFactory.createDataSource(props);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	public static DataSource getDataSource(){
		return dataSource;
	}
	
	public static Connection getConnection(){
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}

测试
public class Test {
	public static void main(String[] args) {
		System.out.println(DBCPUtils.getDataSource());
		System.out.println(DBCPUtils.getConnection());
	}
}

C3p0连接池

c3p0-0.9.2.1.jar  
mysql-connector-java-5.1.10-bin.jar  
mchange-commons-java-0.2.3.4.jar

src下编写配置文件properties和xml两种，建议xml，文件名称（固定）c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<!-- 默认配置 -->
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
	<property name="jdbcUrl">jdbc:mysql:///db_jdbcdemo</property>
	<property name="user">root</property>
	<property name="password">root</property>
	<property name="initialPoolSize">5</property>
	<property name="maxPoolSize">20</property>
  </default-config>
  <!-- 命名配置 -->
  <named-config name="lph"> 
    <property name="driverClass">com.mysql.jdbc.Driver</property>
	<property name="jdbcUrl">jdbc:mysql:///db_jdbcdemo</property>
	<property name="user">root</property>
	<property name="password">root</property>
  </named-config>
</c3p0-config>

属性	解释
acquireIncrement	当连接池中的连接耗尽的时候c3p0一次同时获取的连接数 默认为：3
acquireRetryAttempts	定义在从数据库获取新连接失败后重复尝试的次数 默认为：30
acquireRetryDelay	两次连接中间隔时间，单位毫秒 默认为：1000
autoCommitOnClose	连接关闭时默认将所有未提交的操作回滚 默认为：false
automaticTestTable	c3p0将建一张名为Test的空表，并使用其自带的查询语句进行测试。如果定义了这个参数那么 属性preferredTestQuery将被忽略。你不能在这张Test表上进行任何操作，它将只供c3p0测试使用 默认为：null
breakAfterAcquireFailure	获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常。但是数据源仍有效 保留，并在下次调用getConnection()的时候继续尝试获取连接。如果设为true，那么在尝试 获取连接失败后该数据源将申明已断开并永久关闭 默认为：false
checkoutTimeout	当连接池用完时客户端调用getConnection()后等待获取新连接的时间，超时后将抛出 SQLException,如设为0则无限期等待。单位毫秒 默认为：0
connectionTesterClassName	通过实现ConnectionTester或QueryConnectionTester的类来测试连接。类名需制定全路径 默认为：com.mchange.v2.c3p0.impl.DefaultConnectionTester
factoryClassLocation	指定c3p0 libraries的路径，如果（通常都是这样）在本地即可获得那么无需设置，默认null即可 默认为：null
idleConnectionTestPeriod	每60秒检查所有连接池中的空闲连接 默认为：0
initialPoolSize	初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间 默认为：3
maxIdleTime	最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃 默认为：0
maxPoolSize	连接池中保留的最大连接数 默认为：15
maxStatements	JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements 属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。 如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭 默认为：0
maxStatementsPerConnection	maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数 默认为：0
numHelperThreads	c3p0是异步操作的，缓慢的JDBC操作通过帮助进程完成。扩展这些操作可以有效的提升性能 通过多线程实现多个操作同时被执行 默认为：3
overrideDefaultUser	当用户调用getConnection()时使root用户成为去获取连接的用户。主要用于连接池连接非c3p0 的数据源时 默认为null
overrideDefaultPassword	与overrideDefaultUser参数对应使用的一个参数 默认为：null
user	用户名 默认为： null
password	密码 默认为：null
preferredTestQuery	定义所有连接测试都执行的测试语句。在使用连接测试的情况下这个一显著提高测试速度。注意： 测试的表必须在初始数据源的时候就存在 默认为：null
propertyCycle	-用户修改系统配置参数执行前最多等待300秒 默认为：300
testConnectionOnCheckout	因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的 时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable 等方法来提升连接测试的性能 默认为：false
testConnectionOnCheckin	如果设为true那么在取得连接的同时将校验连接的有效性 默认为：false

工具类

package c3p0;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {
	//使用默认配置
	//private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
	//使用命名配置
	private static ComboPooledDataSource dataSource = new ComboPooledDataSource("lph");
	//获得连接池（数据源）
	public static DataSource getDataSource() {
		return dataSource;
	}
	//获得连接
	public static Connection getConn() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("连接获取错误");
		}
	}
}

测试
public class Test {
	public static void main(String[] args) {
		//获取连接池
		System.out.println(C3P0Utils.getDataSource());
		//获取连接对象
		System.out.println(C3P0Utils.getConn());
	}
}

Druid连接池

导入druid-1.1.2.jar

五、dbutils操作数据库

dbutils是Apache公司编写的数据库操作实用的工具，小巧，简单，实用，封装了对JDBC的操作，简化了JDBC操作；需要导入驱动包和commons-dbutils-1.6.jar

1、核心功能

QueryRunner中提供对sql语句操作的api

ResultSetHandler接口用于定义select操作后怎么封装结果集

DbUtils类，工具类 定义了关闭资源与事务处理方法

2、QueryRunner

QueryRunner(DataSource ds)或者QueryRunner()；如果使用带参数的是提供数据源连接池，会自动帮你创建连接，没有参数是不用连接池的

2.1 Update(String sql,Object...obj)执行更新数据

添加

删除

修改

2.2 执行查询query(String sql, ResultSetHandler<T> rsh,Object...params)params数组可有可无 、可多可少  、可直接写或者传数组

3、ResultSetHandler查询操作和处理

第一种：new ArrayHandler()

第二种：new ArrayListHandler()

第三种：new BeanHandler<User>(User.class)

第四种new BeanListHandler<User>(User.class)

第五种new ColumnListHandler<>(“列名”)

第六种query(sql, new ScalarHandler())

第七种new MapHandler()

第八种new MapListHandler()

总结

query参数是连接对象、 sql语句、 ResultHandler接口的实现类、参数数组(一个/多个/没有/传递)；query返回值 泛型，根据结果集处理类变化；说明：如果参数中有con就是没有使用连接池的

• ArrayHandler：把结果集中的第一行数据转成对象数组。
• ArrayListHandler：把结果集中的每一行数据都转成一个对象数组，再存放到List中。
• BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
• BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。//重点
• MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。//重点**
• MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List
• ColumnListHandler：将结果集中某一列的数据存放到List中。
• KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里(List)，再把这些map再存到一个map里，其key为指定的列。
• ScalarHandler:将结果集第一行的某一列放到某个对象中。//重点

4、DbUtils

DbUtils.closeQuietly(con);关闭资源

六、dbutils结合连接池操作数据库

导入jar以及配置文件

编写工具类

选择连接池中的工具列（工具类中的getConn方法可以去掉 只留下获取数据源的即可）

举例：不使用配置文件的德鲁伊工具类
import com.alibaba.druid.pool.DruidDataSource;
public class Pool {
	private static DruidDataSource dataSource = null;
	static {
		dataSource = new DruidDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://localhost:3306/1808?characterEncoding=utf-8");// 设置连接的数据库
		dataSource.setUsername("root");// 设置用户名
		dataSource.setPassword("");// 设置密码
	}

	public static DruidDataSource getDataSource() {
		return dataSource;
	}
}

测试

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

public class Test {
	//使用有参数的方法传递数据源
	private  static QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
	//插入操作
	public static void insert() {
		String sql = "insert into user(name,pass)values(?,?)";
		Object[] params  = {"xixi","345"};
		try {
			//此时不必要串连接对象
			int row  =qr.update(sql,params);
			System.out.println(row);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	public static void select() {
		String sql = "select * from user";
		try {
			List<Object[]> list = qr.query(sql, new ArrayListHandler());
			for(Object[] ojbs:list) {
				for(Object obj:ojbs) {
					System.out.println(obj);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {	
		insert();
		select();
	}	
}

总结

QueryRunner里面要传递数据源对象，由工具类获取，操作和不用连接池一样，知识参数不再传递连接对象，操作数据库建议使用这种dbutils和和连接池结合的方式