MySQL数据库六：Java数据库连接（JDBC）

最新推荐文章于 2022-07-26 15:45:00 发布

嘵奇

最新推荐文章于 2022-07-26 15:45:00 发布

阅读量132

点赞数

分类专栏： MySQL 文章标签：数据库

本文链接：https://blog.csdn.net/qq479850581/article/details/108921476

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

概念

Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。

第一个JDBC程序

import java.sql.*;

public class JdbcTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        /*2.打开连接
         * 服务器地址: localhost（localhost: 3306）
         * 数据库名称: jdbcstudy
         * useSSL=false: 关闭SSL，8.0以上版本必要
         * serverTimezone=UTC: 不然容易因为时间问题报错
         * user：用户名
         * password：密码
         */
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcstudy?useSSL=false&serverTimezone=UTC&user=root&password=123456");
        //3.执行查询
        Statement statement = connection.createStatement();
        //4.执行SQL语句
        String sql = "SELECT * FROM users";
        //5.获得查询的结果
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            System.out.println("编号：" + resultSet.getObject("id"));
            System.out.println("姓名：" + resultSet.getObject("name"));
            System.out.println("密码：" + resultSet.getObject("pwd"));
            System.out.println("邮箱：" + resultSet.getObject("email"));
            System.out.println("生日：" + resultSet.getObject("birthday"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

Statement对象

创建一个工具类

package com.www.qi.utils;

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

public class JdbcUtils {

    private static String driver = null;
    private static String url = null;

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

    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url);
    }

    //释放连接
    public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (resultSet != null){
            resultSet.close();
        }
        if(statement != null){
            statement.close();
        }
        if (connection != null){
            connection.close();
        }
    }
}

实现数据库的插入操作

package com.www.qi;

import com.www.qi.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) throws SQLException {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            //获取数据库连接
            connection = JdbcUtils.getConnection();
            //获得SQL的执行对象
            statement = connection.createStatement();
            String sql = "INSERT INTO users VALUES(5,'刘奇','123654','787856@qq.com','1997-11-18')";
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

实现数据库的修改操作

package com.www.qi;

import com.www.qi.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestUpdate {
    public static void main(String[] args) throws SQLException {

        Connection connection = null;
        Statement statement =null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "UPDATE users SET `name`='嘵奇',email='458254@qq.com' WHERE id=1";
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("修改成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

实现数据库的删除操作

package com.www.qi;

import com.www.qi.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestDelete {
    public static void main(String[] args) throws SQLException {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "DELETE FROM users WHERE id = 5";
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

实现数据库的查询操作

package com.www.qi;

import com.www.qi.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestSelect {
    public static void main(String[] args) throws SQLException {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "SELECT `name`,email,pwd FROM users WHERE id = 1 ";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println("姓名：" + resultSet.getString("name"));
                System.out.println("邮箱：" + resultSet.getString("email"));
                System.out.println("密码：" + resultSet.getString("pwd"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

SQL注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

百度百科

PreparedStatement对象

可以防止SQL注入，效率会更高
PreparedStatement会把传递进来的参数当作字符
假设其中存在转义字符会被直接转义

利用PreparedStatement实现查询操作

package com.www.qi;

import com.www.qi.utils.JdbcUtils;

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

public class TestPre03 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "select * from users where id = ?";
            ps = connection.prepareStatement(sql);
            ps.setInt(1,1);
            //查询结束返回一个结果值
            resultSet = ps.executeQuery();
            if (resultSet.next()){
                System.out.println("序号：" + resultSet.getInt("id"));
                System.out.println("姓名：" + resultSet.getString("name"));
                System.out.println("密码：" + resultSet.getString("pwd"));
                System.out.println("邮箱：" + resultSet.getString("email"));
                System.out.println("生日：" + resultSet.getDate("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,ps,resultSet);
        }
    }
}

利用PreparedStatement实现插入操作

package com.www.qi;

import com.www.qi.utils.JdbcUtils;

import java.sql.*;

public class TestPre01 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            connection = JdbcUtils.getConnection();
            //使用 ？ 占位符代替参数
            String sql = "insert into users(id,`name`,`pwd`,`email`,`birthday`) values(?,?,?,?,?)";
            //预编译SQL，编写SQL但不执行
            ps = connection.prepareStatement(sql);
            //为参数赋值
            ps.setInt(1,7);
            ps.setString(2,"嘵奇");
            ps.setString(3,"852147");
            ps.setString(4,"89561@qq.com");
            ps.setDate(5,new java.sql.Date(new java.util.Date().getTime()));
            int i = ps.executeUpdate();
            if (i > 0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,ps,null);
        }

    }
}

利用PreparedStatement实现更新操作

package com.www.qi;

import com.www.qi.utils.JdbcUtils;

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

public class TestPre02 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            connection = JdbcUtils.getConnection();
            String sql = "update users set `name` = ?,`pwd` = ? where id = ?";
            ps = connection.prepareStatement(sql);
            ps.setString(1,"老詹");
            ps.setString(2,"741258");
            ps.setInt(3,6);
            int i = ps.executeUpdate();
            if (i > 0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

利用PreparedStatement实现删除操作

package com.www.qi;

import com.www.qi.utils.JdbcUtils;

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

public class TestPre04 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            connection = JdbcUtils.getConnection();
            String sql = "delete from users where id = ?";
            ps = connection.prepareStatement(sql);
            ps.setInt(1,2);
            int i = ps.executeUpdate();
            if (i > 0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,ps,null);
        }
    }
}

JDBC操作事务

package com.www.qq;

import com.www.qi.utils.DbcpUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//JDBC操作事务
public class TestDbcp02 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        ResultSet resultSet = null;
        PreparedStatement ps = null;

        try {
            connection = DbcpUtils.getConnection();
            //关闭数据库的自动提交，自动会开启事务
            connection.setAutoCommit(false);
            String sql1 = "update test set money = money - 200 where id = 1";
            ps = connection.prepareStatement(sql1);
            ps.executeUpdate();
            String sql2 = "update test set money = money + 200 - 500 where id = 2";
            ps = connection.prepareStatement(sql2);
            ps.executeUpdate();
            //业务完毕，提交事务
            connection.commit();
            System.out.println("操作成功！");
        } catch (SQLException e) {
            try {
                //如果失败回滚事务
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DbcpUtils.release(connection,ps,resultSet);
        }
    }
}

DBCP和C3P0连接池

DBCP连接池

配置文件

#连接设置
driverClassName=com.mysql.cj.jdbc.Driver
url= jdbc:mysql://localhost:3306/jdbcstudy?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username=root
password=123456

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000


#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

创建工具类

package com.www.qi.utils;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class DbcpUtils {

    private static DataSource dataSource = null;

    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);
            //创建数据源
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        //从数据源中获取连接
        return dataSource.getConnection();
    }
    //释放连接资源
    public static void release(Connection connection, PreparedStatement ps, ResultSet resultSet) throws SQLException {
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null){
            connection.close();
        }
    }
}

测试类：

package com.www.qq;

import com.www.qi.utils.DbcpUtils;

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

public class TestDbcp01 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            connection = DbcpUtils.getConnection();
            String sql = "insert into users(id,`name`,`pwd`,`email`,`birthday`) values(?,?,?,?,?)";
            ps = connection.prepareStatement(sql);
            ps.setInt(1,11);
            ps.setString(2,"浓眉");
            ps.setString(3,"000000");
            ps.setString(4,"11111@qq.com");
            ps.setString(5,"1990-10-10");
            int i = ps.executeUpdate();
            if (i > 0){
                System.out.println("插入成功！");
            }else {
                System.out.println("插入失败！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbcpUtils.release(connection,ps,null);
        }
    }
}

C3P0连接池

配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
    //默认的配置
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?&amp;useSSL=false&amp;serverTimezone=UTC</property>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="acquireIncrement">3</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">2</property>
        <property name="maxPoolSize">10</property>
    </default-config>
</c3p0-config>

创建工具类

package com.www.qi.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
public class C3P0Utils {
    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
    public static DataSource getDataSource(){
        return dataSource;
    }
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException();
        }
    }
    public static void release(Connection connection, PreparedStatement ps, ResultSet resultSet) throws SQLException {
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null){
            connection.close();
        }
    }
}

测试类：

package com.www.qq;

import com.www.qi.utils.C3P0Utils;

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

public class TestC3P01 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = C3P0Utils.getConnection();
            String sql = "insert into users(id,`name`,`pwd`,`email`,`birthday`) values(?,?,?,?,?)";
            ps = connection.prepareStatement(sql);
            ps.setInt(1,10);
            ps.setString(2,"詹姆斯");
            ps.setString(3,"202020");
            ps.setString(4,"202020@qq.com");
            ps.setString(5,"1988-12-12");
            int i = ps.executeUpdate();
            if (i > 0){
                System.out.println("插入成功！");
            }else {
                System.out.println("插入失败！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            C3P0Utils.release(connection,ps,null);
        }
    }
}