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对象。