1.什么是JDBC
JDBC是一个独立于特定数据库管理系统、通用的SQL数据库存取和操作的公共接口,面向接口编程,为访问不同的数据库提供了一种统一的途径,为开发者屏蔽了一些细节问题,各个数据库厂商根据JDBC的规范制作的 JDBC 实现类的类库。主要面向两个层次,面向应用的api:供应应用程序开发人员使用和面向数据库的api:供开发商开发数据库驱动程序用,
2.Driver接口:所有的jdbc驱动程序需要实现的接口
#config.properties
user=root
password=密码
url=jdbc:mysql://localhost:3306/table?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false&verifyServerCertificate=false
driverClass=com.mysql.cj.jdbc.Driver
<!--父pom.xml-->
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>cn.jdbc.test</groupId>
<artifactId>jdbctest</artifactId>
<version>0.0.1-SNAPSHOT</version>
<!-- 必须为pom -->
<packaging>pom</packaging>
<name>jdbctest</name>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.7.0</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
</plugins>
</build>
<!-- 依赖的自定义实现 -->
<dependencyManagement>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
</dependencies>
</dependencyManagement>
</project>
<!--子pom.xml-->
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>cn.jdbc.test2</groupId>
<artifactId>jdbctest2</artifactId>
<name>jdbctest2</name>
<description>jdbctest2</description>
<parent>
<groupId>cn.jdbc.test</groupId>
<artifactId>jdbctest</artifactId>
<version>0.0.1-SNAPSHOT</version>
<!-- 相对该pom.xml的父pom.xml的位置 -->
<relativePath>../jdbctestparent/pom.xml</relativePath>
</parent>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
</project>
public class DriverTest1 {
public static void main(String[] args) throws Exception {
Connection connection = DriverTest1.getConnection("config.properties");
System.out.println(connection);
connection.close();
}
// 适用于mysql的jdbc连接
//@Test // test兼容性不好不能使用
public static Connection getMysqlConnection() throws SQLException {
// mysql5用的驱动url是com.mysql.jdbc.Driver,mysql6以后用的是com.mysql.cj.jdbc.Driver。
// 版本不匹配便会报驱动类已过时的错误。 JDBC连接Mysql6
// com.mysql.cj.jdbc.Driver需要指定时区serverTimezone,
// 否则会报错如下
/*
* Exception in thread "main" java.sql.SQLException: The server time
* zone value '̨±±±ê׼ʱ¼ä' is unrecognized or represents more than one
* time zone. You must configure either the server or JDBC driver (via
* the serverTimezone configuration property) to use a more specifc time
* zone value if you want to utilize time zone support. 时区不统一
*/
Driver driver = new Driver();
/*
* mysql需要提供的一些信息的源码 public DriverPropertyInfo[] getPropertyInfo(String
* url, Properties info) throws SQLException { String host = "";//主机ip
* localhost (url中写了) String port = "";//端口3306(url中写了) String database
* = "";数据库 table (url中写了) String user = "";数据库账号 (properties需要提供)
* String password = "";数据库密码 (properties需要提供) url
* 格式:jdbc:mysql://localhost:3306/table 说明:jdbc:固定格式 mysql:协议
* //localhost:3306主机名和端口/table数据库名,后续还可连接如下
* mysql.url=jdbc:mysql://127.0.0.1:3306/table?serverTimezone=UTC&
* useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&
* useSSL=false&verifyServerCertificate=false&autoReconnct=true&
* autoReconnectForPools=true&allowMultiQueries=true
* serverTimezone=UTC:时区utc(世界标准时间),我过时间在其上加8个小时即可 有两种方法:方法1:在mysql中使用
* set global time_zone='+8:00'; 方法2:&serverTimezone=GMT%2B8 来设置为中国时区
* useUnicode=true&characterEncoding=utf8&characterSetResults=utf8
* 处理与数据库字符集不统一导致的乱码问题, 这3个一般一起书写,含义如下
* 本质:useUnicode=true&characterEncoding=utf-
* 8的作用是指定character_set_client和character_set_connection的值,
* 在jdbc链接中使用characterSetResults=UTF-8,即可设置character_set_results的值
* 系统变量character_set_client:用来指定解析客户端传递数据的编码
* 系统变量character_set_connection:用来指定数据库内部处理时使用的编码
* 系统变量character_set_results:用来指定数据返回给客户端的编码方式 useSSL=false
* 不需要使用SSL连接,你需要通过设置useSSL=false来显式禁用SSL连接。 SSL:Secure Sockets
* Layer(安全套接层) verifyServerCertificate=false 服务证书
* 设置useSSL=false与verifyServerCertificate=false可以解决如下异常警告 Mon Aug 06
* 23:00:16 CST 2018 WARN: Establishing SSL connection without server's
* identity verification is not recommended. According to MySQL 5.5.45+,
* 5.6.26+ and 5.7.6+ requirements SSL connection must be established by
* default if explicit option isn't set. For compliance with existing
* applications not using SSL the verifyServerCertificate property is
* set to 'false'. You need either to explicitly disable SSL by setting
* useSSL=false, or set useSSL=true and provide truststore for server
* certificate verification.
*
* autoReconnct=true 超时自动重连 autoReconnectForPools=true 针对数据库重连策略
* allowMultiQueries=true 允许批量更新
*
* (后续mysql中说)
*/
Properties properties = new Properties();
properties.put("user", "root");
properties.put("password", "cgz12345678");
Connection connect = driver.connect(
"jdbc:mysql://localhost:3306/table?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8",
properties);
return connect;
}
public static Connection getConnection(String path) throws Exception {
Properties properties = new Properties();
FileInputStream fileInputStream = new FileInputStream(path);
properties.load(fileInputStream);
fileInputStream.close();
String driverClass=properties.getProperty("driverClass");
// 为什么使用sql呢,因为这里使用的是jdbc规范,也就是说都要实现jdbc的Driver接口
// 所以这里采用的是多态的方式
java.sql.Driver driver = (java.sql.Driver) Class.forName(driverClass).newInstance();
String url = properties.getProperty("url");
Connection connect = driver.connect(url, properties);
return connect;
}
}
public class DriverManagerTest {
//文件的运行时建立在编译上
//建立在maven上
public static void main(String[] args) throws Exception {
//DriverManagerTest 找不到类文件在maven的java的构建路径与maven的构建路径中有说明
Connection connection = DriverManagerTest.getConnection("config.properties");
System.out.println(connection);
connection.close();
}
public static Connection getConnection(String path) throws IOException, ClassNotFoundException, SQLException {
Properties properties = new Properties();
//所以getResourceAsStream读取的目录是对编译后的文件位置而言的
// InputStream ras=DriverManagerTest.class.getClassLoader().getResourceAsStream(path);
//可能会出现null指针异常 ras为空找不到文件所致,这里的原因请见maven的java的构建路径与maven的构建路径中有说明
InputStream ras = DriverManagerTest.class.getResourceAsStream("/" + path);
if (ras == null) {
System.out.println("Connection==null");
return null;
}
properties.load(ras);
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
ras.close();
// 不用获取对象,会自动的注册
//这里会自动的根据DriverManager.getConnection(url的url自动的选择我们需要的Class.forName(driverClass);所以写多个Class.forName(driverClass)并不会导致错误
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, properties);
return connection;
}
}
3.statement
创建表teachers
create table teachers(
id int primary key auto_increment,
name varchar(10) ,
sex char(1),
birth date,
salary int
)engine=InnoDB default charset=utf8
statement的增删改查
public class StatementTest { public static void main(String[] args) throws Exception { Connection con = StatementTest.getConnection("config.properties"); // 插入 // String insert="insert into teachers(name,sex,birth,salary) values('王五','男','2018-1-1',666)"; // StatementTest.insert(con, insert); // 修改 // String update="update teachers set name='丽丽',sex='女' where id=1"; // StatementTest.insert(con, update); // 删除 // String delete="delete from teachers where id =2" ; // StatementTest.insert(con, delete); //查询 String select="select name,sex,birth,salary from teachers where id=1"; // StatementTest.select(con, select); //查询Column名 StatementTest.getColumn(con, select); } public static Connection getConnection(String path) throws IOException, ClassNotFoundException, SQLException { Properties properties = new Properties(); InputStream ras = StatementTest.class.getResourceAsStream("/" + path); if (ras == null) { System.out.println("Connection==null"); return null; } properties.load(ras); String url = properties.getProperty("url"); String driverClass = properties.getProperty("driverClass"); ras.close(); Class.forName(driverClass); Connection connection = DriverManager.getConnection(url, properties); return connection; } private static void executeUpdate(Connection con, String sql) throws SQLException{
try {
con.setAutoCommit(false);
Statement statement = con.createStatement();
statement.executeUpdate(sql);
con.commit();
close(con,statement);
} catch (Exception e) {
con.rollback();
e.printStackTrace();
}
} public static void update(Connection con, String sql) throws SQLException{ executeUpdate(con,sql); } public static void delete(Connection con, String sql) throws SQLException{ executeUpdate(con,sql); } public static void insert(Connection con, String sql) throws SQLException{ executeUpdate(con,sql); } public static void select(Connection con, String sql) throws SQLException{ Statement statement = con.createStatement(); ResultSet executeQuery = statement.executeQuery(sql); while(executeQuery.next()){ System.out.println(executeQuery.getObject(1)+"-"+executeQuery.getObject(2)+ "-"+ executeQuery.getObject(3)+"-"+ executeQuery.getObject(4)); ; } if(executeQuery!=null){ executeQuery.close(); } close(con, statement); } public static void getColumn(Connection con, String sql) throws SQLException{ Statement statement = con.createStatement(); ResultSet executeQuery = statement.executeQuery(sql); ResultSetMetaData metaData = executeQuery.getMetaData(); //metaData.getCatalogName从1开始的 for (int i = 1; i <= metaData.getColumnCount(); i++) { if(i!=metaData.getColumnCount()) System.out.print(metaData.getColumnName(i)+"-");else System.out.print(metaData.getColumnName(i)); } if(executeQuery!=null){ executeQuery.close(); } close(con, statement); } public static void close(Connection con, Statement statement) throws SQLException{ if(statement!=null){ statement.close(); } if(con!=null){ con.close(); } } }
statement 的sql注入问题
产生的原因:
statement中的sql语句是硬连接,例如String select="select name,sex,birth,salary from teachers where name="+name; 其中的name是varchar修饰) 我们的String name="'name'",必须加''这么去写,如果我们写成String name="name",那么会报错,倘若我们将写成String name="'name' or 1=1",那么sql语句会变为 String select="select name,sex,birth,salary from teachers where name='name' or 1=1; 那么就永远为true,符合每一条记录。这是statement的sql注入的缺陷
4.prepareStatement
public class PrepareStatementTest {
public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException {
Connection con = PrepareStatementTest.getConnection("config.properties");
String sql = "select name,sex,birth,salary from teachers where name=?";
PrepareStatementTest.select(con, sql, "丽丽");
}
public static Connection getConnection(String path) throws IOException, ClassNotFoundException, SQLException {
Properties properties = new Properties();
InputStream ras = StatementTest.class.getResourceAsStream("/" + path);
if (ras == null) {
System.out.println("Connection==null");
return null;
}
properties.load(ras);
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
ras.close();
Class.forName(driverClass);
return DriverManager.getConnection(url, properties);
}
public static void select(Connection con, String sql, Object... objs) throws SQLException {
PreparedStatement statement = con.prepareStatement(sql);
// 解决sql注入
statement.setString(1, (String) objs[0]);
ResultSet executeQuery = statement.executeQuery();
//ResultSetMetaData可以获取表明,栏名,别名,并且可以判定是否只读,是否自增,做大宽度(字符为单位),指示指定列中的值是否可以为 null等表中的相关信息
ResultSetMetaData metaData = executeQuery.getMetaData();
// 获取column数
int columnCount = metaData.getColumnCount();
while (executeQuery.next()) {
for (int i = 1; i <= columnCount; i++) {
System.out.println(executeQuery.getObject(i));
// 获取column名 与getColumnName的区别,getColumnName返回的是sql语句中field的原始名字。getColumnLabel是field的SQL AS的值(Alias--别名)。
System.out.println(metaData.getColumnName(i));
}
}
if (executeQuery != null) {
executeQuery.close();
}
close(con, statement);
}
。。。
}
备注:解决sql注入的原因
上述的statement.setString(1, (String) objs[0]);会作为一个整体传入,就如上述的name直接用这个整体传入的进行查询
5.DAO data access object
DAO:数据访问对象,对数据进行CRUD create read update delete,而不包含业务相关的信息
6.获取数据库的元数据
public class DatabaseTest2 {
public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException {
Connection con = DatabaseTest2.getConnection("config.properties");
DatabaseTest2.databaseMetaData(con);
}
。。。
public static void databaseMetaData(Connection con) throws SQLException {
ResultSet catalogs=null;
try {
//获取数据库本身的一些信息
DatabaseMetaData metaData = con.getMetaData();
//获取版本信息
int databaseMajorVersion = metaData.getDatabaseMajorVersion();
System.out.println(databaseMajorVersion);
//获取用户名
String userName = metaData.getUserName();
System.out.println(userName);
//获取数据库名
catalogs = metaData.getCatalogs();
while(catalogs.next()){
//只有一条信息
System.out.println(catalogs.getObject(1));
}
} finally {
if(catalogs!=null){
catalogs.close();
}
if(con!=null){
con.close();
}
}
}
}
7.获取生成的主键值
public class GeneratedKeys { public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException { Connection con = GeneratedKeys.getConnection("config.properties"); String sql="insert into teachers(name,sex,birth,salary) values('王五','男','2018-1-1',666)"; GeneratedKeys.insert(con, sql); } 。。。 public static void insert(Connection con, String sql, Object... objs) throws SQLException { try { con.setAutoCommit(false); PreparedStatement statement = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); statement.executeUpdate(); ResultSet generatedKeys = statement.getGeneratedKeys(); //获取主键 while (generatedKeys.next()) { //获取生成的主键值 System.out.println(generatedKeys.getObject(1)); } ResultSetMetaData metaData = generatedKeys.getMetaData(); for (int i = 0; i < metaData.getColumnCount(); i++) { System.out.println(metaData.getColumnName(i+1)); } con.commit(); close(con, statement,generatedKeys); } catch (Exception e) { con.rollback(); e.printStackTrace(); } } 。。。 }
8.插入lob(比较大的数据例如图片)
使用alter table teachers add picture mediumblob 在末尾插入一列二进制保存文件,最大为16M
public class GeneratedKeys {
public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException {
Connection con = GeneratedKeys.getConnection("config.properties");
// 写入
// String sql="insert into teachers(picture) values(?)";
// GeneratedKeys.insert(con, sql);
// 获取
String sql="select picturer from teachers where id=11";
GeneratedKeys.select(con, sql);
}
。。。。
public static void insert(Connection con, String sql, Object... objs) throws SQLException, FileNotFoundException {
try {
con.setAutoCommit(false);
PreparedStatement statement = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
statement.setBlob(1, new FileInputStream("timg.jpg"));;
statement.executeUpdate();
con.commit();
close(con, statement,null);
} catch (Exception e) {
con.rollback();
e.printStackTrace();
}
}
public static void select(Connection con, String sql, Object... objs) throws SQLException, IOException {
PreparedStatement statement = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
ResultSet executeQuery = statement.executeQuery();
while(executeQuery.next()){
Blob blob = executeQuery.getBlob(1);
InputStream binaryStream = blob.getBinaryStream();
//写入方式与io流一致
binaryStream .close();
}
close(con, statement,executeQuery);
}
。。。。
}
9.事务的ACID 属性
A原子性atomicity
C 一致性consistency
I 隔离性isolation
D 持久性 durability
关于事务的原子回滚操作上述已有,一个失败全部失败,con设置手动提交后,一次性可以提交多条sql语句;假如不去小默认提交,那么在executeupdate时会自动提交
10. 事务的隔离级别
并发问题(针对事务)
脏读:读去了已经被更新还没有提交的事物,当事物回滚后,那么读取的数据是临时的和无效的
不可重复读: 第一次读取后,更新了该数据(提交了),第二次读取时数据不同
幻读:第一次去读时,读取了一些数据,然后提交了一些数据,再去读的时候多出几行数据
隔离级别:隔离级别越高那么一致性越好,并发性越差
READ_UNCOMMITTED 读未提交的数据 允许脏读,不可重复读 幻读
READ_COMMITTED 读已提交的数据 可以防止脏读 但是不可以防止不可重复读幻读 con. SetTransactionIsolation(con.TRANSACTION_READ_COMMITTED)
REPEATABLE_READ可重读 可避免脏读和不可重读 但是幻读依然存在TRANSACTION_REPEATABLE_READ
SERIALIZABLE 串行化 可避免上述所有问题 但是效率低 TRANSACTION_SERIALIZABLE ,mysql默认的隔离级别
public static void select(Connection con, String sql, Object... objs) throws SQLException, IOException {
//读已提交,需要永久的改隔离级别可以去mysql中改
con.setTransactionIsolation(con.TRANSACTION_READ_COMMITTED);
PreparedStatement statement = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
ResultSet executeQuery = statement.executeQuery();
while(executeQuery.next()){
Blob blob = executeQuery.getBlob(1);
InputStream binaryStream = blob.getBinaryStream();
//写入方式与io流一致
binaryStream .close();
}
close(con, statement,executeQuery);
}
备注:已经提交表示已经提交到数据库里面了,未提交表示为提交数据库里面,但存在于缓存之中。
Mysql支持4种
Oracle只支持Read commited Serializable两种
11.批量处理提高处理速度
public class BacthTest {
public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException {
Connection con = BacthTest.getConnection("config.properties");
String sql="insert into teachers(name,salary) values(?,?)";
long start = Instant.now().toEpochMilli();
// BacthTest.insertPreparedStatement(con, sql);//PreparedStatement :8148
// BacthTest.insertStatment(con);23012
BacthTest.insertBatch(con, sql);//1656
long end = Instant.now().toEpochMilli();
System.out.println(end-start);
}
public static void insertPreparedStatement(Connection con, String sql, Object... objs) throws SQLException, FileNotFoundException {
try {
//这里采用的mysql,mysql的效率在增删没有oracle效率高,这是因为一个底层基于数组,一个基于链表的原因
con.setAutoCommit(false);
PreparedStatement statement = con.prepareStatement(sql);
for (int i = 0; i < 100000; i++) {
statement.setObject(1, "丽丽");
statement.setObject(2, i);
statement.executeUpdate();
}
con.commit();
close(con, statement,null);
} catch (Exception e) {
con.rollback();
e.printStackTrace();
}
}
public static void insertBatch(Connection con, String sql, Object... objs) throws SQLException, FileNotFoundException {
try {
//这里采用的mysql,mysql的效率在增删没有oracle效率高,这是因为一个底层基于数组,一个基于链表的原因
con.setAutoCommit(false);
PreparedStatement statement = con.prepareStatement(sql);
for (int i = 0; i < 10000; i++) {
statement.setObject(1, "丽丽");
statement.setObject(2, i);
statement.executeUpdate();
statement.addBatch();
if(i%300==0){
//类似io流的思想
statement.executeBatch();
statement.clearBatch();
}
}
if(100000%300!=0){
statement.executeBatch();
statement.clearBatch();
}
con.commit();
close(con, statement,null);
} catch (Exception e) {
con.rollback();
e.printStackTrace();
}
}
public static void insertStatment(Connection con) throws SQLException, FileNotFoundException {
try {
//这里采用的mysql,mysql的效率在增删没有oracle效率高,这是因为一个底层基于数组,一个基于链表的原因
con.setAutoCommit(false);
Statement statement = con.createStatement();
for (int i = 0; i < 100000; i++) {
String sql="insert into teachers(name,salary) values('丽丽',"+i+")";
statement.executeUpdate(sql);
}
con.commit();
close(con, statement,null);
} catch (Exception e) {
con.rollback();
e.printStackTrace();
}
}
12.连接池
类似于线程池,倘若每次都需要去获取连接和关闭开销比较大,而且会产生大量的临时对象,增加gc的操作,所以有了连接池的产生,连接太多也会导致很多问题,甚至崩溃,连接池统一的对连接进行管理,避免数据库连接泄露。连接池基本的思想是在系统初始化的时候,将数据库连接作为对象存储在内存中,当用户需要访问数据库时,并非建立一个新的连接,而是从连接池中取出一个已建立的空闲连接对象。使用完毕后,用户也并非将连接关闭,而是将连接放回连接池中,以供下一个请求访问使用。而连接的建立、断开都由连接池自身来管理。同时,还可以通过设置连接池的参数来控制连接池中的初始连接数、连接的上下限数以及每个连接的最大使用次数、最大空闲时间等等。也可以通过其自身的管理机制来监视数据库连接的数量、使用情况等。
连接池主要有两种dbcp,c3p0,其他类似,java api使用javax.sql DataSource接口对连接池进行统一的管理。也是一种面向接口的编程。
13.Connection与Statement的关系
执行的语句是查询,查询是读锁,100个查询都可以读取相同的数据,因此100个查询之间相互之间是没有影响的。但是对于更新就不是这样了,尤其是涉及到事务的时候。事务是以连接Connection为单位的,在JDBC中,默认autocommit是true,所以每一个SQL语句都是一个事务。当你在同一个连接上,创建不同的Statement的时候,是没法保证事务的ACID特性的,数据不一致就会发生,程序就是错误的。创建Connection是十分耗时的操作,一般情况下,都是使用连接池,比如c3p0,需要Connection的时候就去连接池取。Spring的事务管理是把Connection与当前线程关联起来实现事务。
理论上(也就是根据jdbc规范),connection对象是线程安全的,但实际当中不敢保证所有jdbc驱动都会遵守该规范。所以最好还是不要这么做,而是使用连接池,每个线程单独从连接池中获得connection对象。