JDBC
1. JDBC概述
1.1JDBC简单介绍
JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API。JDBC是Java访问数据库的标准规范，可以为不同的关系型数据库提供统一访问，它由一组用Java语言编写的接口和类组成。
JDBC需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定，设备提供商为设备提供驱动软件，通过软件可以与该设备进行通信。
JDBC API是一个Java API，可以访问任何类型表列数据，特别是存储在关系数据库中的数据。JDBC代表Java数据库连接。
JDBC库中所包含的API通常与数据库使用于：
- 连接到数据库
- 创建SQL或MySQL语句
- 在数据库中执行SQL或MySQL查询
- 查看和修改数据库中的数据记录
1.2 JDBC原理
Java提供访问数据库规范称为JDBC，而生产厂商提供规范的实现类称为驱动。
JDBC是接口，驱动是接口的实现，没有驱动将无法完成数据库连接，从而不能操作数据库！每个数据库厂商都需要提供自己的驱动，用来连接自己公司的数据库，也就是说驱动一般都由数据库生成厂商提供。
1.3JDBC入门准备
我们使用Maven构建项目，导入相应依赖：
<dependencies>
<!-- 单元测试依赖-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>compile</scope>
</dependency>
<!-- mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
<!-- C3P0数据库连接池依赖-->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
<!-- Druid数据库连接池依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<!-- DBUtils依赖-->
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>
</dependencies>
2. 获取数据库连接Connection
下面我们通过迭代来实现多个不同版本获得数据库连接方式。
//版本1
Driver driver = new com.mysql.jdbc.Driver();//直接硬编码加载MySQL驱动
//协议
String url = "jdbc:mysql://localhost:3306/test?useSSL=false";
//使用集合类Properties封装部分属性
Properties properties = new Properties();
properties.setProperty("user",用户名);
properties.setProperty("password",密码);
Connection connect = driver.connect(url, properties);
System.out.println(connect);
//版本2
String url = "jdbc:mysql://localhost:3306/test?useSSL=false";
Properties properties = new Properties();
properties.setProperty("user",用户名);
properties.setProperty("password"密码);
//通过反射来构建驱动
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver)clazz.newInstance();
Connection connect = driver.connect(url, properties);
System.out.println(connect);
//版本3
String url = "jdbc:mysql://localhost:3306/test?useSSL=false";
String user = 用户名;
String password = 密码;
//加载Driver
Class.forName("com.mysql.jdbc.Driver");//只需要加载MySQL会自己注册驱动
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
//版本4，最终版，使用配置文件
//获取配置文件输入流
InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
//Properties集合读取数据
Properties properties = new Properties();
properties.load(inputStream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
//加载驱动
Class.forName(driverClass);
//获取连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
user=用户名
password=密码
url=jdbc:mysql://localhost:3306/test?useSSL=false
driverClass=com.mysql.jdbc.Driver
3.使用JDBC完成CURD操作
//增
Connection conn = null;
PreparedStatement pre = null;
try{
//获取连接
conn = JDBCUtils.getConnection();
//编写SQL
String sql = "INSERT INTO emp(ename,salary,dname,birth) VALUES(?,?,?,?)";
pre = conn.prepareStatement(sql);
//填充占位符
pre.setObject(1,"无涯子");
pre.setObject(2,3000);
pre.setObject(3,"研发部");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = simpleDateFormat.parse("2017-08-17");
pre.setObject(4,new Date(date.getTime()));
pre.execute();//执行SQL语句
}catch(Exception e){
System.out.println(e.getMessage());
}finally {
try {
JDBCUtils.closeAll(conn,pre,null);
} catch (Exception e) {
e.printStackTrace();
}
}
//更新
//更新一条记录
Connection conn = null;
PreparedStatement pre = null;
try{
conn = JDBCUtils.getConnection();
String sql = "UPDATE emp SET dname = ? WHERE eno = ?";
pre = conn.prepareStatement(sql);
pre.setObject(1,"销售部");
pre.setObject(2,3671);
pre.execute();
System.out.println("执行成功");
}catch(Exception e){
e.printStackTrace();
}finally {
//关闭资源
try {
JDBCUtils.closeAll(conn,pre,null);
} catch (Exception e) {
e.printStackTrace();
}
}
//删除
Connection conn = null;
PreparedStatement pre = null;
try{
conn = JDBCUtils.getConnection();
String sql = "DELETE FROM emp WHERE eno = ?";
pre = conn.prepareStatement(sql);
pre.setObject(1,3672);
pre.execute();
System.out.println("执行成功");
}catch(Exception e){
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(conn,pre,null);
} catch (Exception e) {
e.printStackTrace();
}
}
//通用的增删改
public void update(String sql,Object... args){
Connection conn = null;
PreparedStatement ps = null;
try{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 1; i <= args.length;i++){
ps.setObject(i,args[i-1]);
}
ps.execute();
System.out.println("执行成功");
}catch (Exception e){
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(conn,ps,null);
} catch (Exception e) {
e.printStackTrace();
}
}
}
//查询
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
conn = JDBCUtils.getConnection();
String sql = "SELECT ename,salary,dname,birth FROM emp WHERE eno = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1,3673);
rs = ps.executeQuery();
if(rs.next()){
String ename = rs.getString(1);
int salary = rs.getInt(2);
String dname = rs.getString(3);
Date birth = rs.getDate(4);
Emp emp = new Emp(dname,ename,salary,birth);
System.out.println(emp);
}
}catch(Exception e){
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(conn,ps,rs);
} catch (Exception e) {
e.printStackTrace();
}
}
//通用查询操作
public <T> T getInstance(Class<T> clazz, String sql, Object... args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0; i < args.length; i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columns = rsmd.getColumnCount();
if(rs.next()){
T t = clazz.newInstance();
for(int i = 0; i < columns; i++){
Object columnValue = rs.getObject(i+1);
String columnName = rsmd.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
}catch (Exception e){
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(conn,ps,rs);
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
//通用查询集合
public <T> List<T> getList(Class<T> clazz,String sql,Object... args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0; i < args.length; i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columns = rsmd.getColumnCount();
List<T> list = new ArrayList<>();
while(rs.next()){
T t = clazz.newInstance();
for(int i = 0; i < columns; i++){
Object columnValue = rs.getObject(i+1);
String columnName = rsmd.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t,columnValue);
}
list.add(t);
}
return list;
}catch (Exception e){
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(conn,ps,rs);
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
4.使用JDBC操作BLOB数据
//向数据库中传输BLOB数据
Connection conn = null;
PreparedStatement ps = null;
try{
conn = JDBCUtils.getConnection();
String sql = "INSERT INTO emp(ename,salary,dname,birth,photo) VALUES(?,?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setObject(1,"舒淇");
ps.setObject(2,4000);
ps.setObject(3,"秘书部");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date parse = sdf.parse("2001-10-27");
ps.setObject(4,new java.sql.Date(parse.getTime()));
FileInputStream is = new FileInputStream("src/1.jpg");
ps.setBlob(5,is);
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(conn,ps,null);
} catch (Exception e) {
e.printStackTrace();
}
}
//从数据库中读取BLOB数据
Connection conn = null;
PreparedStatement ps = null;
InputStream is = null;
FileOutputStream os = null;
ResultSet rs = null;
try{
conn = JDBCUtils.getConnection();
String sql = "SELECT photo FROM emp WHERE eno = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1,3674);
rs = ps.executeQuery();
if(rs.next()){
Blob photo = rs.getBlob("photo");
is = photo.getBinaryStream();
os = new FileOutputStream("e:\\2.jpg");
byte[] buf = new byte[1024];
int len;
while((len = is.read(buf)) != -1) {
os.write(buf, 0, len);
}
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(conn,ps,rs);
if(is != null){
is.close();
}
if(os != null){
os.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
5.JDBC中的批处理
Connection conn = null;
PreparedStatement ps = null;
try{
conn = JDBCUtils.getConnection();
conn.setAutoCommit(false);//自动提交改为false
long l = System.currentTimeMillis();
String sql = "INSERT INTO goods(goods_title) VALUES(?)";
ps = conn.prepareStatement(sql);
for(int i = 1; i <= 20000; i++){
ps.setObject(1,"name_" + i);
ps.addBatch();
if(i % 500 == 0){
ps.executeBatch();//批量执行
ps.clearBatch();//清空
}
}
conn.commit();//提交执行
long e = System.currentTimeMillis();
System.out.println("time: " + (e - l));
} catch (Exception e) {
e.printStackTrace();
}
6.JDBC中的事务
Connection conn = null;
try{
conn = JDBCUtils.getConnection();
conn.setAutoCommit(false);//修改自动提交
//conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
//System.out.println(conn.getTransactionIsolation());
String sql = "UPDATE test SET blance = blance - 100 WHERE user = ? AND password = ?";
update(conn,sql,"aa","AA");
System.out.println(10/0);
String sql1 = "UPDATE test SET blance = blance + 100 WHERE user = ? AND password = ?";
update(conn,sql1,"bb","BB");
conn.commit();//提交事务
System.out.println("转账成功");
}catch (Exception e){
e.printStackTrace();
try {
if(conn != null){
conn.rollback();//回滚事务
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}finally {
try {
if(conn != null){
conn.setAutoCommit(true);//重新设置自动提交为true
JDBCUtils.closeAll(conn,null,null);
}
} catch (Exception e) {
e.printStackTrace();
}
}
7.JDBC封装成DAO
针对不同表编写一个通用的DAO抽象类，提供增删改查基础操作
public abstract class BaseDao<T> {
private Class<T> clazz;
//普通代码块执行clazz的初始化，在子类初始化的执行
{
Type superclass = this.getClass().getGenericSuperclass();//获得带泛型的父类
ParameterizedType paramType = (ParameterizedType)superclass;//ParameterizedType参数化类型，即泛型
//获取父类的泛型参数
Type[] actualTypeArguments = paramType.getActualTypeArguments();//获得所有泛型，可能有多个，所以是数组
clazz = (Class<T>) actualTypeArguments[0];
}
/**
*考虑事务通用的DML操作
* @param conn 数据库连接
* @param sql SQL语句
* @param args 传入SQL中的参数
* @return DML操作更新了几条数据，如果没有更新数据就返回0
*/
public int update(Connection conn, String sql, Object... args){
PreparedStatement ps = null;
try{
ps = conn.prepareStatement(sql);
for(int i = 0; i < args.length ; i++){
ps.setObject(i+1,args[i]);
}
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(null,ps,null);
} catch (Exception e) {
e.printStackTrace();
}
}
return 0;
}
/**
* 考虑事务通用的查询操作
* @param conn 数据库连接
* @param sql SQL语句
* @param args SQL参数
* @return 返回查询到的数据，使用对象填充，如果没有查询到返回null
*/
public T query(Connection conn,String sql,Object... args){
PreparedStatement ps = null;
ResultSet rs = null;
try{
ps = conn.prepareStatement(sql);
for(int i = 0; i < args.length; i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if(rs.next()){
ResultSetMetaData rsmd = rs.getMetaData();//获得结果集元数据
int columns = rsmd.getColumnCount();
T t = clazz.newInstance();
for(int i = 0; i < columns; i++){
Object columnValue = rs.getObject(i+1);//获取值
String columnName = rsmd.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
}catch (Exception e){
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(null,ps,rs);
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
/**
* 通用的查询多条记录
* @param conn 数据库连接
* @param sql SQL语句
* @param args SQL参数
* @return 查询的多条记录，如果没有则返回null
*/
public List<T> queryList(Connection conn,String sql,Object... args){
PreparedStatement ps = null;
ResultSet rs = null;
try{
ps = conn.prepareStatement(sql);
for(int i = 0; i < args.length; i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columns = rsmd.getColumnCount();//获取列数
List<T> list = new ArrayList<>();
while(rs.next()){
T t = clazz.newInstance();
for(int i = 0; i < columns; i++){
Object columnValue = rs.getObject(i+1);
String columnName = rsmd.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t,columnValue);
}
list.add(t);
}
return list;
}catch (Exception e){
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(null,ps,rs);
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
/**
* 查询一些特殊操作
* @param conn 数据库连接
* @param sql SQL语句
* @param args 参数
* @return 查询结果
*/
public <E>E getValue(Connection conn,String sql,Object... args){
PreparedStatement ps = null;
ResultSet rs = null;
try{
ps = conn.prepareStatement(sql);
for(int i = 0; i < args.length; i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if(rs.next()){
return (E)rs.getObject(1);
}
}catch (Exception e){
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(null,ps,rs);
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
}
针对数据库中每张表，我们提供一个接口和实现类来完成其操作。
public interface EmpDao {
//增
void insert(Connection conn, Emp emp);
//删
void deleteOfNo(Connection conn,int no);
//改
void update(Connection conn,Emp emp);
//查
Emp query(Connection conn,int no);
//查询所有记录
List<Emp> queryList(Connection conn);
long getSize(Connection conn);
}
public class EmpDaoImpl extends BaseDao<Emp> implements EmpDao {
@Override
public void insert(Connection conn, Emp emp) {
String sql = "INSERT INTO emp(ename,salary,dname,birth) VALUES(?,?,?,?)";
int insert = update(conn,sql,emp.getEname(),emp.getSalary(),emp.getDname(),emp.getBirth());
if(insert > 0){
System.out.println("插入成功");
}
}
@Override
public void deleteOfNo(Connection conn, int no) {
String sql = "DELETE FROM emp WHERE eno = ?";
int delete = update(conn,sql,no);
if(delete > 0){
System.out.println("删除成功");
}
}
@Override
public void update(Connection conn,Emp emp) {
String sql = "UPDATE emp SET ename = ?,salary = ?,dname = ?,birth = ? WHERE eno = ?";
int update = update(conn,sql,emp.getEname(),emp.getSalary(),emp.getDname(),emp.getBirth(),emp.getEno());
if(update > 0){
System.out.println("更新成功");
}
}
@Override
public Emp query(Connection conn, int no) {
String sql = "SELECT eno,ename,salary,dname,birth FROM emp WHERE eno = ?";
Emp emp = query(conn, sql, no);
return emp;
}
@Override
public List<Emp> queryList(Connection conn) {
String sql = "SELECT eno,ename,salary,dname,birth FROM emp";
List<Emp> emps = queryList(conn, sql);
return emps;
}
@Override
public long getSize(Connection conn) {
String sql = "SELECT COUNT(*) FROM emp";
return getValue(conn,sql);
}
}
8.数据库连接池
8.1 C3P0数据库连接池
//方式1
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass("com.mysql.jdbc.Driver");
cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test?useSSL=false" );
cpds.setUser("用户名");
cpds.setPassword("密码");
cpds.setInitialPoolSize(10);//初始连接池数量
Connection connection = cpds.getConnection();
System.out.println(connection);
//方式2使用配置文件
ComboPooledDataSource cpds = new ComboPooledDataSource("c3p0App");
Connection connection = cpds.getConnection();
System.out.println(connection);
8.2 Druid数据库连接池
Properties properties = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
properties.load(is);
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
System.out.println(connection);
9. DBUtils工具类
//更新操作
Connection conn = null;
try{
conn = JDBCUtils.getConnection2();
QueryRunner queryRunner = new QueryRunner();
String sql = "INSERT INTO emp(ename,salary,dname) VALUES(?,?,?)";
int update = queryRunner.update(conn, sql,"蔡徐坤",3000,"销售部");
System.out.println("插入了" + update + "条记录");
}catch (Exception e){
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(conn,null,null);
} catch (Exception e) {
e.printStackTrace();
}
}
//查询
Connection conn = null;
try{
conn = JDBCUtils.getConnection2();
QueryRunner queryRunner = new QueryRunner();
String sql = "SELECT eno,ename,salary,dname,birth FROM emp WHERE eno = ?";
BeanHandler<Emp> handler = new BeanHandler<>(Emp.class);
Emp query = queryRunner.query(conn, sql, handler, 3420);
System.out.println(query);
}catch (Exception e){
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(conn,null,null);
} catch (Exception e) {
e.printStackTrace();
}
}
//查询多条记录
Connection conn = null;
try{
conn = JDBCUtils.getConnection2();
QueryRunner queryRunner = new QueryRunner();
String sql = "SELECT eno,ename,salary,dname,birth FROM emp";
BeanListHandler<Emp> handler = new BeanListHandler<>(Emp.class);
List<Emp> query = queryRunner.query(conn, sql, handler);
query.forEach(System.out::println);
}catch (Exception e){
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(conn,null,null);
} catch (Exception e) {
e.printStackTrace();
}
}
//
Connection conn = null;
try{
conn = JDBCUtils.getConnection2();
QueryRunner queryRunner = new QueryRunner();
String sql = "SELECT eno,ename,salary,dname,birth FROM emp WHERE eno = ?";
MapHandler handler = new MapHandler();
Map<String, Object> query = queryRunner.query(conn, sql, handler, 3420);
System.out.println(query);
}catch (Exception e){
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(conn,null,null);
} catch (Exception e) {
e.printStackTrace();
}
}
//其它查询操作
Connection conn = null;
try{
conn = JDBCUtils.getConnection2();
QueryRunner queryRunner = new QueryRunner();
String sql = "SELECT COUNT(*) FROM emp";
ScalarHandler<Long> handler = new ScalarHandler();
Long count = queryRunner.query(conn, sql, handler);
System.out.println(count);
}catch (Exception e){
e.printStackTrace();
}finally {
try {
JDBCUtils.closeAll(conn,null,null);
} catch (Exception e) {
e.printStackTrace();
}
}
浙公网安备 33010602011771号