JDBC
JDBC概述
- JDBC: Java Database Connectivity,是代表一组独立于任何数据库管理系统(DBMS)的API,声明在java.sql与javax.sql包中,是SUN(现在Oracle)提供的一组接口规范。由各个数据库厂商来提供实现类,这些实现类的集合构成了数据库驱动jar。
- JDBC技术包含两个部分
- java.sql包和javax.sql包中的API
因为为了项目代码的可移植性,可维护性,SUN公司从最初就制定了Java程序连接各种数据库的统一接口规范。这样的话,不管是连接哪一种DBMS软件,Java代码可以保持一致性。 - 各个数据库厂商提供的jar
因为各个数据库厂商的DBMS软件各有不同,那么内部如何通过sql实现增、删、改、查等管理数据,只有个数据库厂商自己更清楚,因此把接口规范的实现交给各个数据库厂商自己实现。
- java.sql包和javax.sql包中的API
JDBC的简单使用
右键lib包找到Add as Library…点击创建连接
增
package com.z.jdbctest01;
import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Test01 {
public static void main(String[] args) throws SQLException {
// 0.添加jar包
// 1.注册驱动
DriverManager.registerDriver(new Driver());
//2.建立连接
//我要使用jdbc去连接mysql ,地址是本机 端口号是3306 的test库
String url = "jdbc:mysql://localhost:3306/test";
//用户名
String user = "root";
//密码
String pwd = "1234";
//获取连接
Connection connection = DriverManager.getConnection(url, user, pwd);
//3.创建命令发送器
Statement statement = connection.createStatement();
//4.准备sqL
String sql = "insert into users values(null,'张三','1234')";
//5.执行sqL获取结果
int i = statement.executeUpdate(sql);
//6.输出结果
System.out.println(i);
//7.关闭资源
statement.close();
connection.close();
}
}
改
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//修改一条记录
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");//通过反射触发了Driver类的初始化,里面有静态代码块完成了驱动的注册
//2.获取连接
String url = "jdbc:mysql://localhost:3306/test";
Connection connection = DriverManager.getConnection(url, "root", "1234");
// 3.创建命令发送器
Statement statement = connection.createStatement();
//4.准备sqL
String sql = "update users set sname = 'libai' where id = 1";
//5.发送sqL获取结果
int i = statement.executeUpdate(sql);
//6.输出结果
System.out.println(i>0?"修改成功":"修改失败");
//7.关闭资源
statement.close();
connection.close();
}
删
可以直接使用无序注册驱动
public static void main(String[] args) throws SQLException {
//1.注册驱动 SPI service provider interface
// 2.建立连接
//连接本地数据库jdbc:mysql:///test
Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "1234");
//3.创建命令发送器
Statement statement = connection.createStatement();
//4.准备sqL
String sql = "delete from users where id =1";
//5.发送sql获取结果
int i = statement.executeUpdate(sql);
//6.输出结果
System.out.println(i>0?"删除成功":"删除失败");
//7.关闭资源
statement.close();
connection.close();
}
- 注意
- 如果对应的驱动下有 meta-inf services -对应的驱动报名则可以不用手动注册
- 如果连接的是本地的mysqL则url : jdbc:mysql:///数据库名
- 执行的如果是增删改操作则都是用executeUpdate(sql);
查
package com.z.jdbctest01;
import java.sql.*;
public class Test02 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2. 获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "1234");
//3.创建命令发送器
Statement statement = connection.createStatement();
//4.准备sql
String sql = "select * from users";
//5.发送sql获取结果
//获取结果集 查询的数据 都在结果集内
ResultSet rs = statement.executeQuery(sql);
//返回true则代表有【一条】记录
/*rs.getobject(index);通过下标的方式获取值
rs.get0bject( columnLabeL);通过列名的方式获取值
*/
while (rs.next()){
//6.处理结果
/*int anid = rs.getInt(1);
String sname = rs.getString(2);
Object pwd = rs.getObject(3);*/
/*int anid = rs.getInt("id");
String sname = rs.getString("sname");
Object pwd = rs.getObject("pwd");*/
//不管什么类型
Object anid = rs.getObject("id");
Object sname = rs.getObject("sname");
Object pwd = rs.getObject("pwd");
//7.输出数据
System.out.println(anid+" "+sname+" "+pwd);
}
//8.关闭资源
rs.close();
statement.close();
connection.close();
}
}
使用mysql8
- 使用最新的MySQL驱动jar包
- 把驱动的类名改为:static String driver = “com.mysql.cj.jdbc.Driver”;
- 在访问mysql的url后加入时区设置:static String url = “jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai”;
使用statement不好的地方
- sql拼接问题
package com.z.jdbctest01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class Test03 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入您的名字");
String sname = scanner.next();
System.out.println("请输入您的密码");
String pwd = scanner.next();
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "1234");
//3.创建命令发送器
Statement statement = connection.createStatement();
//4.准备sql 【sql拼接问题:此处需要加多个引号麻烦】
String sql = "insert into users values(null,'"+sname+"','"+pwd+"');";
//5.发送sql 获取结果
int i = statement.executeUpdate(sql);
//6.输出结果
System.out.println(i>0?"添加成功":"添加失败");
//7.关闭资源
statement.close();
connection.close();
}
}
- sql注入危险
package com.z.bean;
public class User {
private int id;
private String sname;
private String pwd;
public User() {
}
public User(int id, String sname, String pwd) {
this.id = id;
this.sname = sname;
this.pwd = pwd;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", sname='" + sname + '\'' +
", pwd='" + pwd + '\'' +
'}';
}
}
package com.z.jdbctest01;
import com.z.bean.User;
import java.sql.*;
import java.util.Scanner;
public class Test04 {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入您的名字");
String sname = scanner.next();
System.out.println("请输入您的密码");
scanner.nextLine();
String pwd = scanner.nextLine();
User user = jodgeUser(sname,pwd);
if(user !=null){
System.out.println(user);
}else System.out.println("查无此人");
}
/**
* 通过传入的用户名和密码,判断此用户存在还是不存在
* @param sname
* @param pwd
* @return 存在返回对象,不存在返回null
*/
private static User jodgeUser(String sname, String pwd) throws Exception {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "1234");
//3.创建命令发送器
Statement statement = connection.createStatement();
//4.准备sql 【12121' or '1'='1 可突破限制 sql注入危险】
String sql = "select * from users where sname = '"+sname+"' and pwd ='"+pwd+"'";
//5.发送sql 获取结果集
ResultSet resultSet = statement.executeQuery(sql);
//6.处理结果
while (resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String password = resultSet.getString("pwd");
User user = new User(id, name, password);
return user;
}
//7.输出结果
//8.结束资源
resultSet.close();
statement.close();
connection.close();
return null;
}
}
使用PreparedStatement处理CRUD(增、删、改、查)
解决sql的拼接以及注入问题
sql的拼接
package com.z.jdbctest02;
import java.sql.*;
import java.util.Scanner;
public class Test01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入您的名字");
String sname = scanner.next();
System.out.println("请输入您的密码");
String pwd = scanner.next();
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立连接
Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "1234");
//3.准备sql
String sql = "insert into users values(null,?,?)";
//4.创建命令发送器
PreparedStatement pst = connection.prepareStatement(sql);
//5.填充数据 pst.setObject(第几个问号?从1开始,sname);
pst.setObject(1,sname);
pst.setObject(2,pwd);
//6.执行sql 获取结果
int i = pst.executeUpdate();
//7.输出结果
System.out.println(i>0?"t":"f");
//8.关闭资源
pst.close();
connection.close();
}
}
sql注入
package com.z.jdbctest02;
import com.z.bean.User;
import java.sql.*;
import java.util.Scanner;
public class Test02 {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入您的名字");
String sname = scanner.next();
System.out.println("请输入您的密码");
scanner.nextLine();
String pwd = scanner.nextLine();
User user = jodgeUser(sname,pwd);
if(user !=null){
System.out.println(user);
}else System.out.println("查无此人");
}
private static User jodgeUser(String sname, String pwd) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "1234");
//3.准备sql
String sql = "select * from users where sname = ? and pwd = ?";
//4.创建命令发送器
PreparedStatement pst = connection.prepareStatement(sql);
//5.填充对象
pst.setObject(1,sname);
pst.setObject(2,pwd);
//6.执行查询 获取结果集
ResultSet resultSet = pst.executeQuery();
while (resultSet.next()){
//7. 处理结果
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String password = resultSet.getString("pwd");
//8.封装对象
User user = new User(id, name, password);
return user;
}
//8.结束资源
resultSet.close();
pst.close();
connection.close();
return null;
}
}
插入图片
在test库中创建photo表
create table photo(
id int primary key auto_increment,
photo blob --只能存下64kb的
);
要想添加大的图片 需要把blob类型(64k)改为longblob(4G)
mysql单次上传的数据最大为4M
超过报以下错误,可以在my.ini中设置max_alLowed_packet
packet for query is too Large (461634783 > 4194304).You can change this value on the server by setting the max_alLowed_packet ’ variable
package com.z.jdbctest02;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestInsertPhoto {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "1234");
//3.准备sql
String sql = "insert into photo values(null,?)";
//4. 创建命令发送器
PreparedStatement pst = connection.prepareStatement(sql);
//5.添加数据
InputStream is = new FileInputStream(new File("D:\\课表.jpg"));//blob只能存下64kb
pst.setBlob(1,is);
//6.执行sql 获取结果
int i = pst.executeUpdate();
//7.输出结果
System.out.println(i>0?"t":"f");
//8.关闭资源
is.close();
pst.close();
connection.close();
}
}
获取自动增长的key值
package com.z.jdbctest02;
import java.sql.*;
public class GetAutoIncrementKey {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//获取自动增长的键值
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "1234");
//3.准备sql
String sql = "insert into users values(null,?,?)";
//4.创建命令发送器
//PreparedStatement pst = connection.prepareStatement(sql);
//获取自动增长的key值 Statement.RETURN_GENERATED_KEYS
PreparedStatement pst = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
//5.添加数据
pst.setObject(1,"dufu");
pst.setObject(2,"222");
//6.执行命令获取结果
int i = pst.executeUpdate();
//7.输出结果
System.out.println(i > 0 ? "t" : "f");
//获取自动增长的key值 存到结果集内
ResultSet keys = pst.getGeneratedKeys();
if (keys.next()) {
Object object = keys.getObject(1);
System.out.println(object);
}
//8.关闭资源
pst.close();
connection.close();
}
}
批处理
package com.z.jdbctest02;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Test03 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
long startTime = System.currentTimeMillis();
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立连接
//Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "1234");
//批处理 ?rewriteBatchedStatements=true 告诉mysql服务器开启批处理支持
Connection connection = DriverManager.getConnection("jdbc:mysql:///test?rewriteBatchedStatements=true", "root", "1234");
//3.准备sql
String sql = "insert into users values(null,?,?)";
//4.创建命令发送器
PreparedStatement pst = connection.prepareStatement(sql);
for (int i = 0; i < 1000; i++) {
//5.填充数据
pst.setObject(1,"ZhangSan"+i);
pst.setObject(2,"pwd"+i);
//6.执行命令 获取结果
//一条一条插入
//pst.executeUpdate();
//批处理 将数据攒到一块
pst.addBatch();
}
pst.executeBatch();
//7.输出时间
long endTime = System.currentTimeMillis();
System.out.println("耗时: " + (endTime - startTime));//开启批处理后会比原来块10多倍
//8.关闭资源
pst.close();
connection.close();
}
}
事务
创建表
create table account(
id int primary key auto_increment,
sname varchar(20),
balance int
);
insert into account values(null,'libai',2000),(null,'dufu',2000);
package com.z.jdbctest02;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Test04 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement pst1 = null;
PreparedStatement pst2 = null;
//1.注册驱动
try {
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "1234");
//开启事务
connection.setAutoCommit(false);
//3.准备sql
String sql1 = "update account set balance = balance - 500 where id = 1";
String sql2 = "update account set balance = balance + 500 where id = 2";
//4.创建命令发送器
pst1 = connection.prepareStatement(sql1);
pst2 = connection.prepareStatement(sql2);
//5.执行sql 获取结果
pst1.executeUpdate();
pst2.executeUpdate();
//提交
connection.commit();
//6.输出结果
System.out.println("转账成功");
} catch (ClassNotFoundException e) {
//程序一旦出现异常,要将数据还原
try {
connection.rollback();
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
//7.关闭资源
if (pst1 != null) {
try {
pst1.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (pst2 != null) {
try {
pst1.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
}
在没有开启事务的时候
如果在sql语句输入错误会导致事务不同步,报以下错误,并且数据已经改动
com.mysqL.jdbc. exceptions.jdbc4.NysQL SyntaxErrorException: You have an error in your SQL syntax;
- //开启事务
connection.setAutoCommit(false); - //提交
connection.commit(); - //回滚
connection.rollback();
jdbcutils关闭资源
package com.z.utils;
import java.sql.*;
public class JDBCUtils {
//1.获取连接
public static Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "1234");
return connection;
}
//2.关闭资源
public static void closeResources(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
}
}
properties
package com.z.properties;
import java.io.*;
import java.util.Properties;
public class PropertiesTest {
public static void main(String[] args) throws IOException {
//获取系统的配置信息
Properties properties = System.getProperties();
//进行输出所有的配置信息
//properties.list(System.out);
//获取编码方式
String value = properties.getProperty("file.encoding");
System.out.println("value= " + value);//value= UTF-8
//自定义
//新建Properties对象
Properties p = new Properties();
p.setProperty("user","root");
p.setProperty("pwd","123");
System.out.println(p);//{user=root, pwd=123}
//写出数据到指定位置 写出到路劲名称 注释
p.store(new FileOutputStream(new File("E:/e.properties")),"this is test");
//读取指定路径的数据
p.load(new FileInputStream(new File("E:/e.properties")));
System.out.println(p);//{user=root, pwd=123}
}
}
在src包下创建File名为jdbc.properties
使用静态代码块优化调用此方法只初始化一次
package com.z.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
static String user;
static String url;
static String password;
static {//静态代码块只会加载一次
Properties properties = new Properties();
try {
//使用反射
//properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"));
//使用绝对地址拿到
properties.load(new FileInputStream(new File("D:\\CodeTest\\JavaTest\\javaproject\\jdbc01\\src\\jdbc.properties")));
user = properties.getProperty("user");
url = properties.getProperty("url");
password = properties.getProperty("password");
String driver = properties.getProperty("driver");
Class.forName(driver);
} catch (IOException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
//1.获取连接
public static Connection getConnection() throws ClassNotFoundException, SQLException, IOException {
/* Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "1234");
return connection;*/
Connection connection = DriverManager.getConnection(url,user,password);
return connection;
}
//2.关闭资源
public static void closeResources(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
}
}
数据库连接池
什么是数据库连接池
连接对象的缓冲区。负责申请,分配管理,释放连接操作。
为什么要使用数据库连接池
Connection对象在每次执行DML和DQL的过程中都要创建一次,DML和DQL执行完毕后,connection对象都会被销毁. connection对象是可以反复使用的,没有必要每次都创建新的.该对象的创建和销毁都是比较消耗系统资源的,如何实现connection对象的反复使用呢?使用连接池技术实现。
连接池的优势
- 预先准备一些链接对象,放入连接池中,当多个线程并发执行时,可以避免短时间内一次性大量创建链接对象,减少计算机单位时间内的运算压力,提高程序的响应速度
- 实现链接对象的反复使用,可以大大减少链接对象的创建次数,减少资源的消耗
市面上有很多现成的数据连接池技术
- JDBC的数据库连接池使用javax.sql.DataSource来表示,DataSource只是一个接口(通常被称为数据源),该接口通常由服务器(Weblogic, WebSphere,Tomcat)提供实现,也有一些开源组织提供实现:
- DBCP是Apache提供的数据库连接池,速度相对c3p0较快,但因自身存在BUG,Hibernate3已不再提供支持
- C3P0是一个开源组织提供的一个数据库连接池,速度相对较慢,稳定性还可以
- Proxool是sourceforge下的一个开源项目数据库连接池,有监控连接池状态的功能,稳定性较c3p0差一点
- BoneCP是一个开源组织提供的数据库连接池,速度快
- Druid是阿里提供的数据库连接池,据说是集DBCP、C3P0、Proxool优点于一身的数据库连接池
阿里的德鲁伊连接池技术
package com.z.druid;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import java.sql.SQLException;
public class DruidTest {
/*德鲁伊数据库连接池*/
public static void main(String[] args) throws SQLException {
//0.导入jar包
//1.创建对象
DruidDataSource ds = new DruidDataSource();
//2.进行配置
//基本配置
ds.setUsername("root");
ds.setPassword("1234");
ds.setUrl("jdbc:mysql:///test");
ds.setDriverClassName("com.mysql.jdbc.Driver");
//连接池配置
ds.setInitialSize(5);//连接池初始化5条连接
ds.setMaxActive(10);//最多可以有几个连接
ds.setMaxWait(2000);//最多等待时间
//3.获取连接
/*DruidPooledConnection connection = ds.getConnection();
System.out.println(connection);*/
for (int i = 0; i < 20; i++) {
DruidPooledConnection connection = ds.getConnection();
System.out.println(connection+"==="+(i+1));
connection.close();
}
}
}
数据库连接池与获取连接池的绑定
driverClassName=com.mysql.jdbc.Driver
url=jdbc :mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=1234
initialSize=5
maxActive=10
maxWait=1000
package com.z.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils1 {
static DataSource dataSource;
static {
Properties properties = new Properties();
try {
properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
//创建druid连接池 通过properties中的数据
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
throw new RuntimeException(e);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
//1.获取连接
public static Connection getConnection() throws ClassNotFoundException, SQLException, IOException {
Connection connection = dataSource.getConnection();
return connection;
}
//2.关闭资源
public static void closeResources(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
}
}
通用的增删改方法
package com.z.basedao;
import com.z.utils.JDBCUtils1;
import java.sql.Connection;
import java.sql.PreparedStatement;
/*
base:基础
dao:data access object
*/
public class BaseDao {
//通用的增删改方法
public int commonUpdate(String sql,Object...objects) throws Exception{
//1.获取连接
Connection connection = JDBCUtils1.getConnection();
//2.准备sql 会采用形参传入
//3.准备命令发送器
PreparedStatement pst = connection.prepareStatement(sql);
if (objects!=null && objects.length>0){
for (int i = 0; i < objects.length; i++) {
//insert into account(null,?,?);
//4.设置值
pst.setObject(i+1,objects[i]);//当i为0的时候在为第一个?赋值
}
}
//5.执行命令获取结果
int i = pst.executeUpdate();
return i;
}
}
测试
package com.z.basedaotest;
import com.z.basedao.BaseDao;
public class Test1 {
public static void main(String[] args) throws Exception {
BaseDao baseDao = new BaseDao();
String sql = "insert into account values(null,?,?)";
Object[] o = {"ZhangSanFeng",500};
int i = baseDao.commonUpdate(sql, o);
System.out.println(i > 0 ? "t" : "f");
}
}
获取表头信息
package com.z.test;
import com.z.utils.JDBCUtils1;
import java.io.IOException;
import java.sql.*;
public class Test1 {
public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
//1.注册驱动
//2.获取连接
Connection connection = JDBCUtils1.getConnection();
//3.准备sql
String sql = "select * from account";
//4.创建命令发送器
PreparedStatement pst = connection.prepareStatement(sql);
//获取表头的数据
ResultSetMetaData metaData = pst.getMetaData();
int columnCount = metaData.getColumnCount();//获取一共有几个字段
for (int i = 0; i < columnCount; i++) {
System.out.println("metaData.getColumnName(i+1) = "
+ metaData.getColumnName(i + 1));//获取字段名
System.out.println("metaData.getColumnLabel(i+1) = "
+ metaData.getColumnLabel(i + 1));//获取字段名(别名)
System.out.println("metaData.getColumnClassName(i+1) = "
+ metaData.getColumnClassName(i + 1));//获取字段类型
System.out.println("metaData.getTableName(i+1) = "
+ metaData.getTableName(i + 1));//获取表名
}
//5.设置值
//6.执行命令获取结果
ResultSet resultSet = pst.executeQuery();
//7.处理结果
while (resultSet.next()){
//获取所有的记录
Object o1 = resultSet.getObject(1);
Object o2 = resultSet.getObject(2);
Object o3 = resultSet.getObject(3);
//8.数据封装
}
//9.关闭资源
JDBCUtils1.closeResources(connection,pst,resultSet);
}
}
通用的获取集合开头、获取单个值、通用的没有实体类对应的方法
package com.z.bean;
public class Account {
private int id;
private String sname;
private int balance;
public Account() {
}
public Account(int id, String sname, int balance) {
this.id = id;
this.sname = sname;
this.balance = balance;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public int getBalance() {
return balance;
}
public void setBalance(int balance) {
this.balance = balance;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", sname='" + sname + '\'' +
", balance=" + balance +
'}';
}
}
package com.z.basedao;
import com.z.utils.JDBCUtils1;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class BaseDao {
/**
* 通用的增删改
* @param sql
* @param args
* @return
* @throws SQLException
* @throws IOException
* @throws ClassNotFoundException
*/
public int update(String sql,Object...args) throws SQLException, IOException, ClassNotFoundException {
//1.获取连接
Connection connection = JDBCUtils1.getConnection();
PreparedStatement pst = connection.prepareStatement(sql);
if (args != null && args.length>0) {
for (int i = 0; i < args.length ; i++) {
pst.setObject(i+1,args[i]);
}
}
int i = pst.executeUpdate();
return i;
}
/**
* 获取 查询的数据封装为集合
* @param clazz 对应类型的class对象
* @param sql 要执行的sql
* @param args 要传递的参数
* @param <T> 具体的类型
* @return
*/
public <T>List<T> getAll(Class<T> clazz,String sql,Object...args) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException, NoSuchFieldException {
//创建存储所有数据的集合
List<T> list = new ArrayList<>();
//获取连接
Connection connection = JDBCUtils1.getConnection();
//准备命令发送器
PreparedStatement pst = connection.prepareStatement(sql);
//获取表头信息
ResultSetMetaData metaData = pst.getMetaData();
//获取表字段数量(获取了一共多少列)
int columnCount = metaData.getColumnCount();
//设置值
if (args !=null && args.length>0){
for (int i = 0; i < args.length; i++) {
pst.setObject(i+1,args[i]);
}
}
//执行命令 获取结果集
ResultSet resultSet = pst.executeQuery();
//遍历结果集获取数据
while (resultSet.next()){
//使用反射创建对象
T instance = clazz.newInstance();
//获取字段名 再去获取对应的值
for (int i = 0; i < columnCount; i++) {//循环一次给一个属性赋值
//获取字段名
String columnLabel = metaData.getColumnLabel(i + 1);
//获取对应的字段值
Object value = resultSet.getObject(columnLabel);
//获取对象对应的属性
Field field = clazz.getDeclaredField(columnLabel);//字段名和属性名一样
field.setAccessible(true);//设置私有属性可见
//将数据库中的值变为对像的属性值
field.set(instance,value);
}
//将对象添加到集合内
list.add(instance);
}
return list;
}
/**
* 获取单个对象
* @param clazz 对应的class信息
* @param sql 要查询的sql
* @param args 参数
* @param <T> 泛型类型
* @return
*/
public <T> T getOne(Class<T> clazz,String sql,Object...args) throws SQLException, IOException, NoSuchFieldException, ClassNotFoundException, InstantiationException, IllegalAccessException {
List<T> all = getAll(clazz,sql,args);
T t = all.get(0);
return t;
}
/**
* 通用的没有实体类对应的方法
* @param sql
* @param args
* @return
*/
public List<Map<String,Object>> mapToList(String sql,Object...args) throws SQLException, IOException, ClassNotFoundException {
//1.创建集合
List<Map<String,Object>> list = new ArrayList<>();
//2.获取连接
Connection connection = JDBCUtils1.getConnection();
//3.创建命令发送器
PreparedStatement pst = connection.prepareStatement(sql);
//4.设置值
if (args!=null&&args.length>0){
for (int i = 0; i < args.length; i++) {
pst.setObject(i+1,args[i]);
}
}
//5.获取原数据
ResultSetMetaData metaData = pst.getMetaData();
int columnCount = metaData.getColumnCount();//获取一共有几个字段
//6.执行命令获取结果
ResultSet resultSet = pst.executeQuery();
//7.遍历结果集
while (resultSet.next()){
//创建Map存储值
Map<String,Object> map = new HashMap<>();
for (int i = 1; i <= columnCount ; i++) {
String columnLabel = metaData.getColumnLabel(i);//获取字段名字
Object value = resultSet.getObject(i);//获取字段值
//8.将每一个值添加到Map内
map.put(columnLabel,value);
}
//9.将Map添加到集合内
list.add(map);
}
//10.关闭资源
JDBCUtils1.closeResources(connection,pst,resultSet);
return list;
}
//获取单个值
public Object getSingleValue(String sql,Object...args) throws SQLException, IOException, ClassNotFoundException {
//select count(*) from account;
//1.获取连接
Connection connection = JDBCUtils1.getConnection();
//2.创建命令发送器
PreparedStatement pst = connection.prepareStatement(sql);
//3.设置值
if (args != null && args.length>0) {
for (int i = 0; i < args.length; i++) {
pst.setObject(i+1,args[i]);
}
}
//4.执行命令获取结果
ResultSet resultSet = pst.executeQuery();
if (resultSet.next()){
Object object = resultSet.getObject(1);
return object;
}
//5.返回结果(如果表中一条记录没有返回空)
return null;
}
}
测试
package com.z.basedaotest;
import com.z.basedao.BaseDao;
import com.z.bean.Account;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
public class BaseDaoTest {
public static void main(String[] args) throws SQLException, IOException, NoSuchFieldException, ClassNotFoundException, InstantiationException, IllegalAccessException {
BaseDao baseDao = new BaseDao();
//当封装类的属性名与数据库表的字段名不一致时 可以用别名方式【metaData.getColumnLabel();】方便使用
String sql = "select id,sname name,balance from account where id < ? and balance > ?";
Object[] arr = {4,2000};
List<Account> all = baseDao.getAll(Account.class, sql, arr);
all.forEach(System.out::println);
}
}
封装JDBCTools
使用ThreadLocal完成事务控制
ThreadLocal保证 如果是同一个线程 那么 获取到的变量 是同一个
package com.z.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtils1 {
static DataSource dataSource;
//创建一个ThreadLocal
static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
static {
Properties properties = new Properties();
try {
properties.load(JDBCUtils1.class.getClassLoader().getResourceAsStream("druid.properties"));
//创建druid连接池 通过properties中的数据
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
throw new RuntimeException(e);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
//1.获取连接
public static Connection getConnection() throws ClassNotFoundException, SQLException, IOException {
Connection connection1 = threadLocal.get();
if (connection1 == null) {
//如果当前线程中没有连接再通过数据库连接池创建连接
connection1 = dataSource.getConnection();
//将创建的新链接添加到ThreadLocal内
threadLocal.set(connection1);
}
return connection1;
}
//2.关闭资源
public static void closeResources(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if (connection != null) {
connection.close();
//关闭连接池 将保存的内容移除
threadLocal.remove();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
}
}
测试
package com.z.test;
import com.z.basedao.BaseDao;
import com.z.utils.JDBCUtils1;
import java.sql.Connection;
import java.sql.SQLException;
public class Test2 {
public static void main(String[] args) {
String sql1 = "update account set balance = balance -500 where id = 1";
String sql2 = "update account set balance = balance +500 where id = 2";
BaseDao baseDao = new BaseDao();
Connection connection = null;
try {
//获取连接 开启事务
connection = JDBCUtils1.getConnection();
System.out.println("test02connection" + connection + "线程名:" + Thread.currentThread().getName());
connection.setAutoCommit(false);//关闭自动提交
baseDao.update(sql1,null);
baseDao.update(sql2,null);
//没有异常进行提交
connection.commit();
} catch (Exception e) {
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
}
}
}
Apache的DBUtils
- commons-dbutils 是Apache组织提供的一个开源JDBC工具类库,它是对DBC的简单封装,学习成本极低,并且使用dbutils能极大简化jdbc编码的工作量,同时也不会影响程序的性能。
- 其中QueryRunner类封装了SQL的执行,是线程安全的。
导入jar包
package com.z.basedao;
import com.z.utils.JDBCUtils1;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.io.IOException;
import java.sql.*;
import java.util.List;
import java.util.Map;
public class BaseDao1 {
QueryRunner queryRunner = new QueryRunner();
//获取单个值
public Object getSingleValue(String sql,Object...args) throws SQLException, IOException, ClassNotFoundException {
//select count(*) from account;
//1.获取连接
Connection connection = JDBCUtils1.getConnection();
return queryRunner.query(connection,sql,new ScalarHandler<>(),args);
}
/**
* 通用的增删改
* @param sql
* @param args
* @return
* @throws SQLException
* @throws IOException
* @throws ClassNotFoundException
*/
public int update(String sql,Object...args) throws SQLException, IOException, ClassNotFoundException {
//1.获取连接
Connection connection = JDBCUtils1.getConnection();
return queryRunner.update(sql,args);
}
/**
* 获取 查询的数据封装为集合
* @param clazz 对应类型的class对象
* @param sql 要执行的sql
* @param args 要传递的参数
* @param <T> 具体的类型
* @return
*/
public <T>List<T> getAll(Class<T> clazz,String sql,Object...args) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException, NoSuchFieldException {
Connection connection = JDBCUtils1.getConnection();
return queryRunner.query(connection,sql,new BeanListHandler<>(clazz),args);
}
/**
* 获取单个对象
* @param clazz 对应的class信息
* @param sql 要查询的sql
* @param args 参数
* @param <T> 泛型类型
* @return
*/
public <T> T getOne(Class<T> clazz,String sql,Object...args) throws SQLException, IOException, NoSuchFieldException, ClassNotFoundException, InstantiationException, IllegalAccessException {
return queryRunner.query(JDBCUtils1.getConnection(),sql,new BeanHandler<>(clazz),args);
}
/**
* 通用的没有实体类对应的方法
* @param sql
* @param args
* @return
*/
public List<Map<String,Object>> mapToList(String sql,Object...args) throws SQLException, IOException, ClassNotFoundException {
return queryRunner.query(JDBCUtils1.getConnection(),sql,new MapListHandler(),args);
}
}