1.JDBC基础
JDBC(Java Database Connectivity)是一个独立于特定数据库管理系统、通用的SQL数据库存取和操作的公共接口,定义了用来访问数据库的标准Java类库,(java.sql.javax.sql)使用这个类库可以以一种标准的方法、方便地访问数据库资源。
JDBC为访问不同的数据库提供了一种统一的途径,为开发者屏蔽了一些细节问题。
JDBC的目标是使JAVA程序员使用JDBC可以连接任何提供了JDBC驱动数据库系统,这样就使得程序员无需对特定的数据库的特点有过多的了解,从而大大简化和加快了开发过程。
2.建立连接
方式一:使用Driver类
方式二:使用反射的方式
方式三:使用DriverManager的方式
方式四:使用DriverManager实现,在方式三的基础上迭代。
方式五:在方式四的基础上迭代,使用配置文件的方式,提供了数据连接的四个基本信息。
package com;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import org.junit.Test;
public class ConnectionTest {
/**
* 方式五:在方式四的基础上迭代,使用配置文件的方式,提供了数据连接的四个基本信息。
*
* 好处:方便修改,同时在代码上体会面向接口编程思想。
* 因为代码没有进行过修改,如果调整配置文件,不用重新编译代码。
*/
@Test
public void test5(){
//1.加载配置文件,读取四个基本信息
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
try {
pros.load(is);
String driverClass = pros.getProperty("driverClass");
String url = pros.getProperty("url");
String user = pros.getProperty("user");
String password = pros.getProperty("password");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 方式四:使用DriverManager实现,在方式三的基础上迭代。
*/
@Test
public void test4(){
//1.提供必要的4个基本信息
String driverClass = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
try {
//2.加载驱动
Class.forName(driverClass);
/**
* 注册驱动的操作在mysql的Driver的源码中已经实现了,如下:
* static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
*/
//3.获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 方式三:使用DriverManager的方式
*/
@Test
public void test3(){
//1.提供必要的4个基本信息
String driverClass = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
try {
//2.1 通过反射创建Driver接口实现类的对象
Class clazz = Class.forName(driverClass);
Driver driver = (Driver) clazz.newInstance();
//2.2注册驱动
DriverManager.registerDriver(driver);
//3.获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 方式二:使用反射的方式
*/
@Test
public void test2() throws SQLException{
//1.提供具体Driver的实现类对象(使用反射)
String driverClass = "com.mysql.jdbc.Driver";
try {
Class clazz = Class.forName(driverClass);
Driver driver = (Driver) clazz.newInstance();
//2.提供了url、用户名、密码
String url = "jdbc:mysql://localhost:3306/test";
Properties pros = new Properties();
pros.setProperty("user", "root");
pros.setProperty("password","123456");
Connection conn = driver.connect(url, pros);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 方式一:使用Driver类
*/
@Test
public void test1() throws SQLException{
//1.提供具体Driver的实现类对象
Driver driver = new com.mysql.jdbc.Driver();
//2.提供了url、用户名、密码
String url = "jdbc:mysql://localhost:3306/test";
Properties pros = new Properties();
pros.setProperty("user", "root");
pros.setProperty("password","123456");
Connection conn = driver.connect(url, pros);
}
}
jdbc.properties文件
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useServePrepStmts=false
user=root
password=123456
3.封装通用的查询操作
/**
* 通用的查询操作(一条数据)
* @param clazz
* @param sql
* @param objs
* @return
* @throws Exception
*/
public <T> T getInstance(Connection conn,Class<T> clazz, String sql ,Object ...objs) throws Exception{
//1.预编译sql语句
PreparedStatement ps = conn.prepareStatement(sql);
//2.填充占位符
for(int i = 0;i<objs.length;i++){
ps.setObject(i+1,objs[i]);
}
//3.执行查询操作,返回结果集:ResultSet
ResultSet rs = ps.executeQuery();
//4.获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
T t = clazz.newInstance();
//5.处理结果集
if(rs.next()){
//判断结果集对应的下一个位置是否有数据。如果返回true,则指针下移一位。
//遍历一行数据的每一个列,并将列值装配到对象上。
for(int i = 0;i<columnCount;i++){
//获取每一列的列值
Object columnValue;
columnValue = rs.getObject(i+1);
//获取列名
//String columnLabel = rsmd.getColumnLabel(i+1);
//获取 order_id,order_name ,会出错
String columnLabel = rsmd.getColumnName(i+1);
//需求:将cust中属性名为columnLabel 的属性值赋值为columnValue --->反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
}
//6.关闭操作
rs.close();
return t;
}
/**
* 通用的多条查询
* @param conn
* @param clazz
* @param sql
* @param objs
* @return
* @throws Exception
*/
public <T> List<T> getForList(Connection conn,Class<T> clazz, String sql ,Object ...objs) throws Exception{
//1.预编译sql语句
PreparedStatement ps = conn.prepareStatement(sql);
//2.填充占位符
for(int i = 0;i<objs.length;i++){
ps.setObject(i+1,objs[i]);
}
//3.执行查询操作,返回结果集:ResultSet
ResultSet rs = ps.executeQuery();
//4.获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//获取结果集的列数
int columnCount = rsmd.getColumnCount();
//创建装配所有查询到的数据构成的集合
ArrayList<T> list = new ArrayList<>();
//5.处理结果集
while(rs.next()){
T t = clazz.newInstance();
//遍历一行数据的每一个列,并将列值装配到对象上。
for(int i = 0;i<columnCount;i++){
//获取每一列的列值
Object columnValue = rs.getObject(i+1);
//获取列名
String columnLabel = rsmd.getColumnLabel(i+1);
//需求:将cust中属性名为columnLabel 的属性值赋值为columnValue --->反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
//6.关闭操作
rs.close();
return list;
}
4.封装通用的更新、删除、修改操作
/**
* 通用的增删改操作
* @author DengDan E-mail: 1173922872@qq.com
* @version CreateTime:2018年11月15日下午4:34:53
* @param conn
* @param sql
* @param objs
* @return 修改的数量
*/
public int update(Connection conn ,String sql,Object ...objs){//可变形参的个数=sql中占位符的个数
PreparedStatement ps = null;
try {
//1.通过连接,获取PreparedStatement的实例(预编译sql语句)
ps = conn.prepareStatement(sql);
//2.填充占位符
for(int i = 0 ;i<objs.length;i++){
ps.setObject(i+1, objs[i]);
}
//3.执行
int count = ps.executeUpdate();
return count;
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return 0;
}
5.数据库连接池
C3p0数据库连接池
package dataConnectionPool;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
*数据库连接池的好处:
*可以实现资源的重用
*提高连接操作的效率
*提供更好针对于数据库连接的管理。
*在数据库连接池技术方面,我们使用DataSource替换原来的DriverManager,实现数据库的连接
*具体DataSource的体现,:
*/
public class C3p0Test {
/**
* 方式二:使用配置文件的方式。推荐!
*/
@Test
public void testConnetion2(){
ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
try {
Connection connection = cpds.getConnection();
System.out.println(connection);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 方式一
*/
@Test
public void testConenction1(){
try {
ComboPooledDataSource cpds = new ComboPooledDataSource();//
cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
cpds.setUser("root");
cpds.setPassword("123456");
Connection conn = cpds.getConnection();
System.out.println(conn);
} catch (PropertyVetoException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
c3p0-config.xml文件:
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config name="helloc3p0">
<!-- 提供数据库连接的四个基本信息 -->
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">123456</property>
<!-- 设置其他的数据库连接信息 -->
<!-- 当c3p0数据库连接池中数理不够时,一次性向数据库服务器申请的连接数 -->
<property name="acquireIncrement">5</property>
<!-- 初始化时,数据库连接池中的连接数 -->
<property name="initialPoolSize">10</property>
<!-- 数据库连接池中的最少连接数 -->
<property name="minPoolSize">10</property>
<!-- 数据库连接池中最多的连接数 -->
<property name="maxPoolSize">100</property>
<!-- 数据库连接池中最多可以维护的Statement的个数 -->
<property name="maxStatements">15</property>
<!-- 每一个数据库连接最多可以使用的Statement的个数 -->
<property name="maxStatementsPerConnection">2</property>
</named-config>
</c3p0-config>
dcbp数据库连接池
package dataConnectionPool;
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.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;
/**
* 使用DBCP的方式要导入两个包
* 一个是dbcp.jar
* 另一个是pool.jar
*/
/**
* 测试dbcp的实现
*/
public class DBCPTest {
@Test
public void testConection2(){
Properties pros = new Properties();
InputStream is = DBCPTest.class.getClassLoader().getResourceAsStream("dbcp.properties");
try {
pros.load(is);
//根据提供的BasicDataSourceFactory创建对应的DataSource对象
DataSource source = BasicDataSourceFactory.createDataSource(pros);
Connection conn = source.getConnection();
System.out.println(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 写死了的方式
*/
@Test
public void testConnection1(){
BasicDataSource source = new BasicDataSource();
source.setDriverClassName("com.mysql.jdbc.Driver");
// source.setUrl("jdbc:mysql://localhost:3306/test");
source.setUrl("jdbc:mysql:///test");//数据库在本地时,可以省略IP地址和端口号
source.setUsername("root");
source.setPassword("123456");
source.setInitialSize(10);
Connection conn;
try {
conn = source.getConnection();
System.out.println(conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
dbcp.properties文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useServePrepStmts=false
username=root
password=123456
initialSize=10
6.数据库工具类的封装
package utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;
import com.ConnectionTest;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import dataConnectionPool.DBCPTest;
public class JDBCUtils {
/**
* 关闭资源操作
* @param conn
* @param ps
*/
public static void closeResource(Connection conn,PreparedStatement ps){
try {
if(conn != null)
conn.close();
if(ps != null)
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 测试连接
*/
@Test
public void test6()throws Exception{
Connection conn = JDBCUtils.getConnection();
System.out.println(conn);
}
/**
* 获取连接
* @return
*/
public static Connection getConnection(){
Connection conn = null;
//1.加载配置文件,读取四个基本信息
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
try {
pros.load(is);
String driverClass = pros.getProperty("driverClass");
String url = pros.getProperty("url");
String user = pros.getProperty("user");
String password = pros.getProperty("password");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
conn = DriverManager.getConnection(url,user,password);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 使用c3p0数据库连接池的配置文件方式,获取数据库连接:推荐
*
*/
private static DataSource cpds = new ComboPooledDataSource("helloc3p0");
public static Connection getConnection2(){
Connection conn=null;
try {
conn = cpds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
private static DataSource source = null;
static{
Properties pros = new Properties();
InputStream is = DBCPTest.class.getClassLoader().getResourceAsStream("dbcp.properties");
Connection conn = null;
try {
pros.load(is);
//根据提供的BasicDataSourceFactory创建对应的DataSource对象
source = BasicDataSourceFactory.createDataSource(pros);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 使用dbcp数据库连接池的配置方式,获取数据库的连接:推荐
*/
public static Connection getConnection3(){
Connection conn=null;
try {
conn = source.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println(conn);
return conn;
}
}
7.使用第三方包:dbutils.jar实现CRUD操作。
package dbutils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;
import jdbc2.com.Customer;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import utils.JDBCUtils;
/**
* 使用dbutils包提供现成的QueryRunner实现数据表的CRUD操作。
*/
public class QueryRunnerTest {
/**
* 如何查询类似于最大的,最小的,平均的,总和,个数相关的数据。
* 使用ScalarHandler
*/
@Test
public void testQueryValue(){
QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection2();
//测试一:
// String sql = "select count(*) from customers where id < ?";
// ScalarHandler handler = new ScalarHandler();
// long count = 0;
// try {
// count = (long) runner.query(conn, sql,handler,20);
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
// System.out.println(count);
//测试二:
String sql = "select max(birth) from customers";
ScalarHandler handler = new ScalarHandler();
try {
Date birth = (Date) runner.query(conn, sql,handler);
System.out.println(birth);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
JDBCUtils.closeResource(conn, null);
}
/**
* 提供ResultSetHandler的实现类,自己实现结果集处理部分
* @author DengDan E-mail: 1173922872@qq.com
* @version CreateTime:2018年11月18日下午1:21:37
*/
@Test
public void testQueryInstance1(){
QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection2();
String sql = "select id,name,email,birth from customers where id = ?";
//自己实现结果集处理
ResultSetHandler <Customer> handler = new ResultSetHandler<Customer>(){
@Override
public Customer handle(ResultSet rs) throws SQLException {
// System.out.println("handle");
// return new Customer(1,"Tom","tom@qq.com",new Date(564165));
if(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth =rs.getDate("birth");
return new Customer(id,name,email,birth);
}
return null;
}
};
try {
Customer customer = runner.query(conn,sql,handler,23);
System.out.println(customer);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
JDBCUtils.closeResource(conn, null);
}
/**
* 测试查询一条记录,此记录使用Map存储
*/
@Test
public void testQueryMap(){
QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection2();
String sql = "select id,name,email,birth from customers where id < ?";
MapHandler handler = new MapHandler();
try {
Map<String,Object> map = runner.query(conn,sql,handler,23);
System.out.println(map);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
JDBCUtils.closeResource(conn, null);
}
/**
* 测试查询多条记录
* 使用ResultSetHandler的实现类:BeanListHandler
*/
@Test
public void testQueryList(){
QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection2();
String sql = "select id,name,email,birth from customers where id < ?";
BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
try {
List<Customer> list = runner.query(conn,sql,handler,23);
list.forEach(System.out::println);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
JDBCUtils.closeResource(conn, null);
}
/**
* 测试查询一条记录
* 使用ResultSetHandler的实现类:BeanHandler
*/
@Test
public void testQueryInstance(){
QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection2();
String sql = "select id,name,email,birth from customers where id = ?";
BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
try {
Customer customer = runner.query(conn,sql,handler,23);
System.out.println(customer);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
JDBCUtils.closeResource(conn, null);
}
/**
* 测试删除
*/
@Test
public void testDelete(){
QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection3();
String sql = "delete from customers where id < ?";
int count=0;
try {
count = runner.update(conn, sql, 3);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("删除了"+count+"条记录");
}
/**
* 添加操作
*/
@Test
public void testInsert(){
QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection3();
String sql = "insert into customers(name,email,birth)values(?,?,?)";
int count=0;
try {
count = runner.update(conn, sql, "张文","zhangwen@qq.com","1996-12-05");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("添加了"+count+"条记录");
}
}