JDBC基础

前路漫漫亦灿灿呀

已于 2024-03-28 22:17:53 修改

阅读量247

点赞数 2

文章标签： java 数据库开发语言

于 2024-03-27 17:33:41 首次发布

本文链接：https://blog.youkuaiyun.com/qq_63855497/article/details/137078761

版权

模拟实现JDBC原理

//JDBC接口
package JDBC;

interface JdbcInterface {
    //连接
    public Object getConnection();
    //
    public void crud();
    //关闭连接
    public void close();
}

//Mysql实现JDBC接口
package JDBC;

public class MysqlJABC implements JdbcInterface{

    @Override
    public Object getConnection() {
        System.out.println("MySQL连接成功");
        return null;
    }

    @Override
    public void crud() {
        System.out.println("mysql实现增删改查");
    }

    @Override
    public void close() {
        System.out.println("关闭mysql");
    }
}

//Oracle实现JDBC接口
package JDBC;

public class OracleJDBC implements JdbcInterface{
    @Override
    public Object getConnection() {
        System.out.println("Oracle连接成功");
        return null;
    }

    @Override
    public void crud() {
        System.out.println("oracle实现增删改查");
    }

    @Override
    public void close() {
        System.out.println("关闭Oracle");
    }
}

//测试类
package JDBC;

public class TestJDBC {
    public static void main(String[] args) {
        //mysql
        JdbcInterface jdbcInterface = new MysqlJABC();
        jdbcInterface.getConnection();
        jdbcInterface.crud();
        jdbcInterface.close();

        //oracle
        jdbcInterface = new OracleJDBC();
        jdbcInterface.getConnection();
        jdbcInterface.crud();
        jdbcInterface.close();
    }
}

JDBC编写程序步骤

1、注册驱动 -- 加载Driver类

2、获取连接 -- 得到Connection

3、执行增删改查 -- 发送SQL给Mysql

4、释放资源 -- 关闭相关连接

//JDBC连接数据库的五种方式
public class Jdbc1 {
    public static void main(String[] args) throws Exception {
        //注册驱动，静态加载，灵活性差，依赖强
        //Driver driver = new Driver();
        //使用反射加载,动态加载
        Class aClass = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver)aClass.newInstance();
        //获取连接，底层是网络连接
        //jdbc:mysql://规定好的协议，通过jdbc的方式连接数据库
        //localhost 主机可以是IP地址
        //3306 MySQL监听端口
        String url = "jdbc:mysql://localhost:3306/university";
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","xiang200212");
        Connection connect = driver.connect(url, properties);
        //执行sql
        String sql = "insert into actor value(null,'xiang','男','2000-1-1')";
        //statement用于执行静态的SQL语句并返回其生成的结果对象
        Statement statement = connect.createStatement();
        int row = statement.executeUpdate(sql);
        System.out.println(row > 0 ? "成功":"失败");
        //关闭连接
        statement.close();
        connect.close();
    }
    @Test
    public void connect3() throws Exception {
        //使用DriverManager代替driver统一管理
        Class aClass = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver)aClass.newInstance();
        String url = "jdbc:mysql://localhost:3306/university";
        String user = "root";
        String password = "xiang200212";
        DriverManager.registerDriver(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
    }
    @Test
    public void connect4() throws Exception {
        //使用Class.forName()自动完成注册驱动，简化代码
        Class aClass = Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/university";
        String user = "root";
        String password = "xiang200212";
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }
    @Test
    public void connect5() throws Exception {
       //使用Properties配置文件
    }
}


ResultSet resultSet = statement.executeQuery(sql1);
while(resultSet.next()){
    int id = resultSet.getInt(1);
    String name = resultSet.getString(2);
    String sex = resultSet.getString(3);
    Date birth = resultSet.getDate(4);                   
    System.out.println(id+"\t"+name+"\t"+sex+"\t"+birth);
}

#sql注入
create table admin(
	name varchar(32),
	pwd varchar(32)
);
insert into admin value("tom",'123');
insert into admin value("jack",'12345');
#输入用户名1' or	输入密码 or '1' = '1
select * from admin 
where name = '1' or' and pwd = 'or '1' = '1'

select * from admin
where name ='1' or' and pwd = 'or '1' = '1'

preparedStatement

        //1、?相当于占位符
        String sql1 = "select name,pwd from admin where name= ? and pwd = ?";
        //2、prepareStatement对象实现了PreparedStatement接口的实现类的对象
        PreparedStatement preparedStatement = connect.prepareStatement(sql1);
        //3、给？赋值
        preparedStatement.setString(1,admin_name);
        preparedStatement.setString(2,admin_pwd);
        //preparedStatement.executeUpdate()
        ResultSet resultSet = preparedStatement.executeQuery();

JDBC API

封装JDBCUtils

在JDBC操作中，获取连接和释放资源是经常使用到，可以将其封装JDBC连接的工具类JDBCUtils

package JDBC;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

//工具类，完成Mysql数据库的连接和关闭资源
public class JDBCUtils {
    //定义相关属性（4个），因为只需要一份，因此使用static
    private static String user;
    private static String password;
    private static String url;
    private static String driver;
    //static代码块去初始化
    static{
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\JDBC\\mysql.properties"));
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");
        } catch (IOException e) {
            //在实际开发中，将编译异常转为运行异常
            //这是调用者可以选择捕获该异常，也可以选择默认处理该异常
            throw new RuntimeException(e);
        }
    }
    //连接数据库,返回connect
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //关闭相关资源
    public static void close(ResultSet set, Statement statement,Connection connection){
        try {
            if(set != null){
                set.close();
            }
            if(statement != null){
                statement.close();
            }
            if(connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

package JDBC;

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

public class UtilsUse {
    public static void main(String[] args) {

        String sql = "update admin set name = ? where pwd = ?";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection =JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"jack");
            preparedStatement.setString(2,"123");
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }

    }
}

public class Transaction_ {
    public static void main(String[] args) throws SQLException {
        String sql = "update account set balance = balance - 100 where id = 1";
        String sql1 = "update account set balance = balance + 100 where id = 2";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection =JDBCUtils.getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
            int i = 1/0;
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();
            connection.commit();
        } catch (Exception e) {
            System.out.println("发生异常，进行回滚");
            connection.rollback();
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }
}

public class Batch_ {
    @Test
    public void noBatch(){
        Connection connection= JDBCUtils.getConnection();
        String sql = "insert into admin value(?,?)";
        PreparedStatement preparedStatement = null;
        long begin = System.currentTimeMillis();
        try {
            preparedStatement = connection.prepareStatement(sql);
            long l = System.currentTimeMillis();
//            for(int i = 1;i <=5000;i++){
//                preparedStatement.setString(1,"tom"+i);
//                preparedStatement.setString(2,"12345");
//                preparedStatement.executeUpdate();
//            }
            for(int i = 1;i <=5000;i++){
                preparedStatement.setString(1,"tom"+i);
                preparedStatement.setString(2,"12345");
                preparedStatement.addBatch();
                if(i%1000 == 0) {//1000个数据时发送执行
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();//清除
                    //url=jdbc:mysql://localhost:3306/university？rewriteBatchedStatements=true
                }
            }
            long end = System.currentTimeMillis();
            System.out.println(end-begin);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }

    }
}

public class C3P0_ {
    @Test
    //相关参数，在程序中指定user，url,password
    public void testC3P0() throws Exception {
        //创建一个数据源对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        //通过mysql.properties获取相关连接信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\JDBC\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        //给数据源设置相关参数
        //连接管理是由comboPooledDataSource来管理的
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);

        //设置初始化连接数
        comboPooledDataSource.setMaxPoolSize(10);
        //设置最大连接数
        comboPooledDataSource.setMaxPoolSize(50);

        Connection connection = comboPooledDataSource.getConnection();
        System.out.println("连接OK");
        connection.close();
    }
    @Test
    //使用配置文件模板来完成
    //将c3p0提供的c3p0.config.xml拷贝至src下，指定了连接数据库和连接池的信息
    public void C3P0__() throws SQLException {
        //构造数据源
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hello");
        Connection connection = comboPooledDataSource.getConnection();
        System.out.println("连接成功");
    }
}

<c3p0-config>
    <named-config name="hello">
        <!--  驱动类  -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!--  url -->
        <property name="jdbcUrl">jdbc:mysql://localhost/university</property>
        <!--  用户名  -->
        <property name="user">root</property>
        <!--  密码  -->
        <property name="password">xiang</property>
        <!--  每次增长的连接数 -->
        <property name="acquireIncrement">5</property>
        <!--  初始的连接数  -->
        <property name="initialPoolSize">10</property>
        <!--  最小连接数  -->
        <property name="minPoolSize">5</property>
        <!--  最大连接数  -->
        <property name="maxPoolSize">10</property>
        <!--  可连接的最多的命令对象数  -->
        <property name="maxStatements">5</property>
        <!--  每个连接对象可连接的最多的命令对象数  -->
        <property name="maxStatementsPerConnection">2</property>
    </named-config>
</c3p0-config>

public class Druid_ {
    @Test
    public void testDruid() throws Exception {
        //加入Druid.jar包
        //加入配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\druid.properties"));
        //创建一个指定参数的数据库连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        System.out.println("连接成功");
        connection.close();
    }
}

public class JDBCUtilsDruid {
    private static DataSource ds;
    //在静态代码块完成ds的初始化
    static{
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
    //关闭连接，close不是真正的断开连接
    public static void close(ResultSet resultSet, Statement statement, Connection connection){
        try{
            if(resultSet != null){
                resultSet.close();
            }
            if(statement != null){
                statement.close();
            }
            if(connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/university?rewriteBatchedStatements=true
username=root
password=xiang200212
initialSize=10
minIdle=5
maxActive=20
maxWait=5000

Apache-DBUtils

问题：1、关闭connection后，resultSet结果集无法使用

2、resultSet不利于数据的管理

//将resultSet封装到ArrayList中,土方法
public class ResultSet_ {
    @Test
    public void resultSet_(){
        System.out.println("使用druid完成");
        String sql = "select * from actor where id>=?";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        ArrayList<Actor> actors = new ArrayList<>();
        try {
            connection = JDBCUtilsDruid.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            System.out.println(preparedStatement.getClass());
            preparedStatement.setString(1,"1");
            set = preparedStatement.executeQuery();
            while(set.next()){
                int id = set.getInt(1);
                String name = set.getString(2);
                String sex = set.getString(3);
                Date birth = set.getDate(4);
                //将resultSet的记录封装到Actor
                actors.add(new Actor(id,name,sex,birth));
            }
            System.out.println(actors);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsDruid.close(set,preparedStatement,connection);
        }
    }
}

public class Actor {    //JavaBean,POJO,Domain
    private Integer id;
    private String name;
    private String sex;
    private Date birth;
    public Actor(){}    //一定要给一个无参构造器【反射需要】

    public Actor(Integer id, String name, String sex, Date birth) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.birth = birth;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "Actor{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", birth=" + birth +
                '}';
    }
}

    public void testQueryMany() throws SQLException {
        //1、得到连接
        Connection connection = JDBCUtilsDruid.getConnection();
        //2、使用DBUtils的类和接口，引入jar包
        //3、创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4、就可以执行相关的方法，返回ArrayList结果集
        String sql ="select * from actor where id >= ?";
        //query方法就是执行sql语句，得到resultSet--封装到-->ArrayList集合中
        //new BeanListHandler<>(Actor.class) 底层使用反射机制获取Actor类属性，然后进行封装
        //？可以有多个值，因为是可变参数Object... params
        //会关闭ResultSet,prepareStatement
        List<Actor> list = queryRunner.query(connection,sql,new BeanListHandler<>(Actor.class),1);
        System.out.println(list);
        JDBCUtilsDruid.close(null,null,connection);
    }
}

Statement stmt = null;
ResultSet resultSet = null;
T result = null;

try {
    if (params != null && params.length > 0) {
        PreparedStatement ps = this.prepareStatement(conn, sql);//创建prepareStatement
        stmt = ps;
        this.fillStatement(ps, params);//对sql进行？赋值
        resultSet = this.wrap(ps.executeQuery());//执行SQL，返回resultSet
    } else {
        stmt = conn.createStatement();
        resultSet = this.wrap(((Statement)stmt).executeQuery(sql));
    }

    result = rsh.handle(resultSet); //使用反射，封装到ArrayList

public void testQuerySingle() throws SQLException {//单行记录
    //1、得到连接
    Connection connection = JDBCUtilsDruid.getConnection();
    //2、使用DBUtils的类和接口，引入jar包
    //3、创建QueryRunner
    QueryRunner queryRunner = new QueryRunner();
    //4、就可以执行相关的方法，返回ArrayList结果集
    String sql ="select * from actor where id = ?";
    //query方法就是执行sql语句，得到resultSet--封装到-->ArrayList集合中
    //new BeanListHandler<>(Actor.class) 底层使用反射机制获取Actor类属性，然后进行封装
    //？可以有多个值，因为是可变参数Object... params
    //会关闭ResultSet,prepareStatement
//没有返回空
    Actor actor = queryRunner.query(connection,sql,new BeanHandler<>(Actor.class),2);
    System.out.println(actor);
    JDBCUtilsDruid.close(null,null,connection);
}

//开发BasicDao,是其他类的父类
public class BasicDao<T> {  //泛型指定类型
    private QueryRunner qr = new QueryRunner();
    Connection connection = null;
    //开发通用的dml方法
    public int update(String sql,Object... params){
        try {
            connection = JDBCUtilsByDruid.getConnection();
            int update = qr.update(connection,sql,params);
            return update;
        } catch (SQLException e) {
            throw new RuntimeException(e);  //将编译异常转为运行异常，抛出
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
    //返回对各对象，针对任意表
    //clazz传入一个类的CLass对象，
    public List<T> queryMany(String sql,Class<T> clazz,Object... params) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new BeanListHandler<T>(clazz), params);
        } catch (SQLException e) {
            throw new RuntimeException(e);  //将编译异常转为运行异常，抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }
    //查询单行
    public T querySingle(String sql,Class<T> clazz,Object... params) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new BeanHandler<T>(clazz), params);
        } catch (SQLException e) {
            throw new RuntimeException(e);  //将编译异常转为运行异常，抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }
}

public class TestActorDao {
    @Test
    public void testDao(){
        ActorDao actorDao = new ActorDao();
        List<Actor> actors = actorDao.queryMany("select * from actor where id >= ?", Actor.class, 1);
        System.out.println(actors);
    }
}