JAVA操作数据库的标准，称为JDBC
引入包：java.sql Javax.sql
数据库驱动包：mysql-connector-java-5.1.47.jar
下载地址：https://mvnrepository.com/art...
Mac OS10.12对应Docker下载地址(2.2.1.0)：https://docs.docker.com/deskt...
Docker装置Mysql5
vim docker-compose.yml
docker-compose up -d
version: '3.5'services: dbm: image: mysql:5.7 command: --default-authentication-plugin=mysql_native_password restart: always environment: MYSQL_ROOT_PASSWORD: 123456 volumes: - './storage/mysql:/var/lib/mysql' ports: - 3306:3306注：mysql5.7.11对应的JDBC驱动是5.1版本。
第一个JDBC程序
- 创立一个一般我的项目
- 导入数据库驱动：驱动包放入lib文件夹（和src同级），Add as Library
- 编写测试代码
import java.sql.*;public class Application { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); //2.配置连贯信息 String url = "jdbc:mysql://localhost:3306/testDB?useUnicode=true&characterEncoding=utf8&useSSL=false"; String username = "root"; String password = "root"; //3.连贯胜利，获取connection对象 Connection connection = DriverManager.getConnection(url, username, password); //4.获取执行Sql的对象statement Statement statement = connection.createStatement(); //5.执行Sql String sql = "SELECT * FROM users"; ResultSet resultSet = statement.executeQuery(sql); while (resultSet.next()) { System.out.println("id=" + resultSet.getObject("id")); } //6.开释资源 resultSet.close(); statement.close(); connection.close(); }}JDBC对象解释
statement
statement.executeQuery(sql);//查问操作 返回resultSetstatement.execute(sql);//执行任何Sqlstatement.executeUpdate(sql);//更新、插入、删除操作，返回受影响的行数遍历，指针
resultSet.beforeFirst();//挪动到最后面resultSet.afterLast();//挪动到最初面resultSet.next();//下一行数据resultSet.previous();//前一行resultSet.absolute(row);//挪动到指定行statement对象
JDBC中的statement对象用于向数据库发送Sql语句。
配置文件 db.properties
driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=falseusername=rootpassword=root工具类 JdbcUtils
package com.oop.demo;import java.io.InputStream;import java.sql.*;import java.util.Properties;public class JdbcUtils { private static String driver = null; private static String url = null; private static String username = null; private static String password = null; static { try { //getClassLoader 类加载器 InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(in); driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); Class.forName(driver);//驱动只须要加载一次 } catch (Exception e) { e.printStackTrace(); } } //获取连贯 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, username, password); } //开释连贯 public static void release(Connection conn, Statement st, ResultSet rs) { try { if (conn != null) { conn.close(); } if (st != null) { st.close(); } if (rs != null) { rs.close(); } } catch (Exception e) { e.printStackTrace(); } }}应用工具类
package com.oop.demo;import java.sql.*;public class Application { public static void main(String[] args) throws SQLException { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); String sql = "INSERT INTO users(id,name) VALUES(3,'xxx')"; int i = st.executeUpdate(sql); if (i > 0) { System.out.println("插入胜利"); } } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtils.release(conn, st, rs); } }}prepareStatement对象
效率更高，能够避免Sql注入
package com.oop.demo;import java.sql.*;public class Application { public static void main(String[] args) throws SQLException { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "INSERT INTO users(id,name) VALUES(?,?)";//?占位符 st = conn.prepareStatement(sql);//预编译sql st.setInt(1,4); st.setString(2,"xxx"); //st.setDate(3,new java.sql.Date(new Date().getTime())); int i = st.executeUpdate();//间接执行 if (i > 0) { System.out.println("插入胜利"); } } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtils.release(conn, st, rs); } }}JDBC操作事务
ACID准则：原子性，一致性，隔离性，持久性
原子性：要么全副实现，要么全不实现
一致性：总数不变
隔离性：多个过程互不烦扰
持久性：提交后不可逆，长久化到数据库了
隔离性常见问题：
脏读：一个事务读取了另一个没有提交的事务
不可反复读：同一事务内，反复读取表数据，表数据产生扭转（不晓得用哪个数据了）
虚读(幻读)：事务内读取到他人插入的数据，导致前后后果不统一（读着读着数据变多了）
package com.oop.demo;import java.sql.*;public class Application { public static void main(String[] args) throws SQLException { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //敞开数据库的主动提交性能，会主动开启事务 conn.setAutoCommit(false); String sql1 = "UPDATE users SET name='xxx' WHERE id=1"; st = conn.prepareStatement(sql1); st.executeUpdate(); String sql2 = "UPDATE users SET name='yyy' WHERE id=2"; st = conn.prepareStatement(sql2); st.executeUpdate(); //业务结束，提交事务 conn.commit(); } catch (Exception e) { try { //失败回滚 conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } finally { JdbcUtils.release(conn, st, rs); } }}数据库连接池
池化技术：事后筹备一些资源，申请过去连贯事后筹备好的资源
罕用概念：最小连接数，最大连接数，期待超时
原理：编写连接池，实现DataSource接口
开源数据实现：DBCP，C3P0，Druid
DBCP
须要用到的jar包：commons-dbsp-1.4.jar commons-pool-1.6.jar
//加载配置文件InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");Properties properties = new Properties();properties.load(in);//创立数据源dataSOurce = BasicDataSOurceFactory.createDataSource(properties);//获取连贯public static Connection getConnection() throws SQLException { return dataSource.getConnection();//应用数据源获取连贯}C3P0
须要用到的jar包：c3p0-0.9.5.5.jar mchange-commons-java-0.2.19.jar
/src/c3p0-config.xml <named-config name="MYSQL">...</named-config>
//代码版dataSource = new ComboPooledDataSource();dataSource.setDriverClass();dataSource.setUser();dataSource.setPassword();dataSource.setJdbcUrl();//配置文件dataSource = new ComboPooledDataSource("MYSQL");//获取连贯public static Connection getConnection() throws SQLException { return dataSource.getConnection();}论断：无论是哪种连接池，DataSource接口不变，办法就不会变