Web学习历程记录(三)——JDBC

Web学习历程记录(三)

JDBC

什么是JDBC

jdbc由两个包组成，分别是java.sql和javax.sql，目前已经被集成到javase规范中

开发JDBC程序

开发步骤
注册驱动（引入驱动jar包）
获得连接
创建执行sql语句的对象
执行sql语句，处理结果
关闭资源

public class JDBC01 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        String ur1 = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(ur1,user,password);
        Statement statement = connection.createStatement();
        String sql = "SELECT * FROM accounts ";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            System.out.println(resultSet.getObject(1));
            System.out.println(resultSet.getObject(2));
            System.out.println(resultSet.getObject(3));
        }
        if (resultSet != null){
            resultSet.close();
        }
        if (statement != null){
            statement.close();
        }
        if (connection != null){
            connection.close();
        }
    }
}

API详解

java.sql.DriverManager

注册驱动

java.sql.DriverManager.registerDriver(new Driver)

这种方法要new两次Driver，所以使用下一种写法

Class.forName(com.mysql.jdbc.Driver)

java.sql.Connection 接口

接口的实现在数据库驱动中。所有与数据交互都是基于连接对象的

createStatement(): 创建执行sql语句对象
prepareStatement(String sql): 创建预编译执行sql语句的对象

java.sql.Statement 接口

接口的实现在数据库驱动中，用来操作sql语句，并返回相应结果对象

Statement：执行sql语句对象
ResultSet execute(String sql)： 根据查询语句返回结果集。只能执行select语句
Int executeUpdate(String sql)： 根据执行的DML(insert , update , delete )语句，返回受影响的行数
boolean execute(String sql)： 此方法可以执行任意sql语句。返回boolean值，表示是否返回的是ResultSet结果集。仅当执行select语句。且有返回结果时返回true。其他语句都返回false

java.sql.ResultSet接口

封装结果集，查询结果表的对象
提供一个游标，默认游标指向结果结果集第一行之前
调用一次next()，游标向下移动一行
提供一些get方法

操作练习

//封装数据库
package priticw;

public class User {
    private int uid;
    private String user;
    private String password;

    public User() {
    }

    public User(int uid, String user, String password) {
        this.uid = uid;
        this.user = user;
        this.password = password;
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", user='" + user + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

//properties
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/account
user = root
password = root

//工具类
package priticw;

import jdk.internal.util.xml.impl.Input;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;

public class Junit {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    static {
        try {
            InputStream is = Junit.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(is);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConection() throws SQLException {
        Connection connection = DriverManager.getConnection(url,user,password);
        return connection;
    }
    public static void release(ResultSet resultSet, Statement statement , Connection connection) throws SQLException {
        if (resultSet != null){
            resultSet.close();
        }
        if (statement != null){
            statement.close();
        }
        if (connection != null){
            statement.close();
        }
    }
    public static ArrayList arrayList(Object obj, ResultSet resultSet) throws SQLException {
        ArrayList<Object> arrayList = new ArrayList<>();
        while (resultSet.next()) {
            obj = new Object();
            arrayList.add(obj);
        }
        return arrayList;
    }

}

//登录代码实现
package priticw;

import java.sql.*;
import java.util.ArrayList;
import java.util.Scanner;

public class Pri2 {
    public static void main(String[] args) throws SQLException {
        Scanner sc = new Scanner(System.in);
        Connection connection = Junit.getConection();
        PreparedStatement statement;
        System.out.println("注册:输入账号");
        String newuser = sc.next();
        System.out.println("注册:输入密码");
        String newPassword = sc.next();

        String nstr ="insert into user values(null,?,?)";
        statement = connection.prepareStatement(nstr);
        statement.setString(1,newuser);
        statement.setString(2,newPassword);
        statement.executeUpdate();

        System.out.println("输入账号");
        String user = sc.next();
        System.out.println("输入密码");
        String password = sc.next();

        String str = "select * from user where user = ? and password = ?";
        statement =  connection.prepareStatement(str);
        statement.setString(1,user);
        statement.setString(2,password);
        ResultSet resultSet = statement.executeQuery();

        User user1 = null;
        while (resultSet.next()){
            user1 = new User(resultSet.getInt("uid"),resultSet.getString("user"),resultSet.getString("password"));

        }
        if (user1 == null){
            System.out.println("登陆失败");
        }
        else {
            System.out.println("登录成功");
            System.out.println(user1.toString());
        }

    }
}

自定义连接池

Connection对象在JDBC使用的时候会去创建对象，使用结束以后就会将这个对象销毁，每次创建和销毁对象都i是耗时操作，需要使用连接池对其进行优化

原理

程序一开始就创建一定数量的连接池，放在一个连接池中
使用的时候直接从连接池取一个已经创建好的对象
关闭的时候不是真正关闭连接，而是将连接对象再次放回到连接池中

初级

public class Pri3 {
    //自定义连接池的类
    //创建一个类，定义linkedlist集合作为连接池，在静态代码块中，向集合里面添加5个对象
    //添加addBack（）方法，用作归还连接
    private static LinkedList<Connection> pool;

    static {
        try {
            pool = new LinkedList<Connection>();
            for (int i = 0; i < 5; i++) {
                Connection connection = Junit.getConection();
                pool.add(connection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() {
        Connection connection = pool.removeFirst();
        return connection; 
    }
    public void addBack(Connection connection){
        pool.addLast(connection);
    }

}

datasource接口

java为数据库连接池提供了公共的接口：javax.sql.DataSource
javax.sql.DataSource 接口。叫数据源也叫连接池。从数据源中得到Connection 连接对象。实现由第三方实现。

但是dataSource中不可以调用addBack()方法

装饰者模式

改写已存在的类的某个方法或某些方法，增强方法的逻辑

使用装饰这模式需要满足的条件
增强类和被增强类实现的是同一个接口
增强类里面要拿到被增强类的引用

public class MyConnection implements Connection{
    private Connection connection;
    private LinkedList<Connection> pool;

    public MyConnection(Connection connection,LinkedList linkedList) {
        this.connection = connection;
        this.pool = linkedList;
    }

    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }
    ···}

这里的装饰模式就是新建一个类实现Connection的全部方法，并且重写close()方法，再写一个构造方法，传入connection和链表。
之后在连接池中调用新建的类，在这个类中将connection放进去。
总之就是通过调用重写的类来实现想要实现的方法

第三方连接池

C3P0

硬编写

ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass("com.mysql.jdbc.Driver");
cpds.setJdbcUrl("jdbc:mysql://localhost :3306/account");
cpds.setUser("root");
cpds.setPassword("root");
Connection connection = cpds.getConnection();

通过配置文件来编写

xml文件放在src目录下

改写工具类

public class C3p0utils {
    private static DataSource dataSource = new ComboPooledDataSource();

    public static Connection getConnection() throws SQLException {
        Connection connection = dataSource.getConnection();
        return connection;
    }
    public static void release(ResultSet resultSet, Statement statement,Connection connection) throws SQLException {
        if (resultSet != null){
            resultSet.close();
        }
        if (statement != null){
            statement.close();
        }
        if (connection != null){
            connection.close();
        }
    }
}

DRUID

硬编写

DruidDataSource dds = new DruidDataSource();
dds.setDriverClassName("com.mysql.jdbc.Driver");
dds.setUrl("jdbc:mysql:///account");
dds.setUsername("root");
dds.setPassword("root");

dds.setInitialSize(5);//设置初始化连接的数量
Connection connection = dds.getConnection();

通过配置文件来编写

xml文件放在src目录下

改写工具类

package pritice;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DruidUtils {
    public static DataSource dataSource = null;

    static {
        try {
            Properties properties = new Properties();
            InputStream is = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            properties.load(is);
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        Connection connection = dataSource.getConnection();
        return connection;
    }

    public static void release(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

JDBC事务的处理

开启事务

connection.setAutoCommit(false);
//如果设置为false，表示关闭自动提交，相当于开启事务

提交

connection。commit();

回滚

connection.rollback();

练习

package pritice;

import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Pri4 {
    @Test
    public void fun01() throws SQLException {

        Connection connection = null;
        PreparedStatement p1 = null;
        PreparedStatement p2 = null;
        try {
            connection = C3p0utils.getConnection();
            //开启事务

            connection.setAutoCommit(false);
            String sql = "update account set money = money - ? where name = ?";

            p1 = connection.prepareStatement(sql);

            p1.setDouble(1, 100.0);
            p1.setString(2, "zs");
            p1.executeUpdate();

            int i = 1 / 0; //模拟错误

            String sql2 = "update account set money = money + ? where name = ?";
            p2 = connection.prepareStatement(sql2);
            p2.setDouble(1, 100.0);
            p2.setString(2, "ls");
            p2.executeUpdate();

            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            connection.rollback();
        } finally {
            p1.close();
            p2.close();
            connection.close();
        }
    }
}