java的jdbc
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对象。
