JDBC和JDBC_Template

2021-12-09 07:45:11

 

JDBC

数据库驱动

JDBC和JDBC_Template

JDBC

概念：SUN 公司为了简化开发人员的（对数据库的统一）操作，提供了一个（java操作数据库的）规范。

# 掌握 JDBC 的接口即可！

JDBC和JDBC_Template

包：

java.sql

javax.sql

还需要导入一个数据库驱动包（maven）

mysql-connector-java-5.1.47.jar

第一个JDBC程序

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
?
USE jdbcStudy;
?
CREATE TABLE `users`(
    id INT PRIMARY KEY,
    NAME VARCHAR(40),
    PASSWORD VARCHAR(40),
    email VARCHAR(60),
    birthday DATE
);
?
INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,‘zhansan‘,‘123456‘,‘zs@sina.com‘,‘1980-12-04‘),
(2,‘lisi‘,‘123456‘,‘lisi@sina.com‘,‘1981-12-04‘),
(3,‘wangwu‘,‘123456‘,‘wangwu@sina.com‘,‘1979-12-04‘)

导入数据库驱动

JDBC和JDBC_Template

注：记得右键——>Add as Library（添加到项目库中）

编写测试代码

  /*
  1、加载驱动
  2、用户信息和url
  3、连接成功，返回数据库对象
  4、执行sql的对象
  5、执行sql的对象 去 执行sql，可能存在结果，查看所有结果
  6、释放连接
   */
package com.xujun.lesson01;
?
import java.sql.*;
?
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
?
        //1、加载驱动,固定写法
        Class.forName("com.mysql.jdbc.Driver");
?
        //2、用户信息和url,固定写法
        /*
        useUnicode=true：支持中文编码
        characterEncoding=UTF8：设定字符集为UTF-8
        useSSL=true：使用安全的连接，防止异常
        用 ？ 去连接参数
         */
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF8&useSSL=true";
        String username = "root";
        String password = "123456";
?
        //3、连接成功，返回数据库对象，Connection 代表对象
        // DriverManager 驱动连接
        Connection connection = DriverManager.getConnection(url, username, password);
?
        //4、执行sql的对象——Statement
        Statement statement = connection.createStatement();
?
        //5、执行sql的对象 去 执行sql，可能存在结果，查看所有结果
        String sql = "SELECT * FROM users";
?
        //所有的删除（delete）和插入（insert）都叫更新 executeQuery()
        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集
        //结果集 中封装了我们全部的查询出来的结果
?
        while(resultSet.next()){
            System.out.println("id="+ resultSet.getObject("id"));
            //不知道类型可以统一 getObject（列名）
            System.out.println("name="+ resultSet.getObject("NAME"));
            System.out.println("password="+ resultSet.getObject("PASSWORD"));
            System.out.println("email="+ resultSet.getObject("email"));
            System.out.println("birthday="+ resultSet.getObject("birthday"));
        }
?
        //6、释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

总结：

  • 加载驱动

  • 连接数据库 DriverManager

  • 获得执行sql的对象 Statement（不安全）

  • 获得返回的结果集

  • 释放连接

JDBC中对象的解释

DriverManager

Connection connection = DriverManager.getConnection(url, username, password);
?
//Connection 代表数据库，数据库能做的事儿，它都可以。譬如：设置自动提交；事务提交；事务回滚
?
connection.setAutoCommit();
connection.commit();
connection.rollback();

 

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF8&useSSL=true";
?
//mysql的端口号默认：3306
// 协议://主机地址：端口号/数据库名?参数1&参数2&参数3
?
//oralce的端口号默认：1521
//jdbc:oracle:thin:@localhost:1521:sid

Statement——执行sql的对象（不安全）

String sql = "SELECT * FROM users";//编写sql
?
statement.executeQuery(); //查询操作返回 ResultSet（结果集）
?
statement.execute();//执行任何sql，所以存在一个判断的过程，效率相对较低
?
statement.executeUpdate();//更新、插入、删除、都是用这个，返回一个受影响的行数

Statement对象

jdbc中的statement对象用于向数据库发送SQL语句

  • executeUpdate()

Statement st = connection.createStatement();
String sql = "增加/删除/改变的操作sql语句";
int  num = st.executeUpdate(sql);//返回一个受影响的行数
if(num>0)
{
    System.out.println("操作成功！！！");
}

  • executeQuery()

Statement st = connection.createStatement();
String sql = "查询操作的sql语句";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
    //根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

 

ResultSet（查询的结果集）：封装了所有的查询结果

获得指定的数据类型：

//在不知道列类型的情况下使用
resultSet.getObject();
//如果知道列类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getDouble();
......

通过指针遍历

resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行

resultSet.next();//移动到下一个数据（常用）

释放连接

//sql操作是十分耗资源的，用完就关掉
resultSet.close();
statement.close();
connection.close();

通过工具类/实用类和配置文件，简化第一个JDBC程序

工具类/实用类

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{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            //getClassLoader():获得内加载器
            //getResourceAsStream():获取具体的资源
            //此时的配置文件必须是在src根目录下
            //返回的结果是一个输入流
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1.驱动只加载一次
            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){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}

 

配置文件properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

 

测试1——executeUpdate()

import com.xujun.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();//获取数据量连接
            //Connection connection = JdbcUtils.getConnection();
            //在try...catch中的变量是无法被释放的

            st = conn.createStatement();//获得SQL的执行对象

            String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
                    "VALUES(4,‘xujun‘,‘123456‘,‘2979150036@qq.com‘,‘2021-3-10‘)";

            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功！");
                // sql中主键不能重复，如果重复插入，会报异常
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            JdbcUtils.release(conn,st,rs);

        }

    }

 

测试2——executeQuery()

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

public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            String sql = "select * from users where id = 1";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

SQL注入的问题

sql存在漏掉，会被攻击导致数据泄露

SQL会被拼接 —— or

import com.xujun.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public static void main(String[] args) {

        //正常登录：
        // login("xujun","123456");

    //异常登录，or，盗取所有信息
    login("‘or ‘1=1","‘or ‘1=1");

    }

    //登录业务
    public static void login(String username,String password){

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            //SELECT * FROM users WHERE `NAME` = ‘xujun‘ AND `password` = ‘123456‘
            //SELECT * FROM users WHERE `NAME` = ‘‘ or ‘1=1‘ AND `password` = ‘‘ or ‘1=1‘
            String sql = "SELECT * FROM users WHERE `NAME` = ‘"+username+"‘ AND `password` = ‘"+password+"‘";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
                System.out.println("=======================");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

运行结果：异常登录，or，盗取所有信息

JDBC和JDBC_Template

解决方法：Statement换成PreparedStatement

import com.xujun.lesson02.utils.JdbcUtils;
import java.sql.*;

public static void main(String[] args) {

        //正常登录：
        //login("lisi","123456");
        login("‘or ‘1=1","‘or ‘1=1");

    }

    //登录业务
    public static void login(String username,String password){

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符
            //假设其中存在转义字符，比如说 ‘ 会被直接转义
            String sql = "SELECT * FROM users WHERE `NAME` = ? AND `password` = ?";
            st = conn.prepareStatement(sql);

            st.setString(1,username);
            st.setString(2,password);

            rs = st.executeQuery();

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
                System.out.println("=======================");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

使用IDEA连接数据库

1、

JDBC和JDBC_Template

2、

JDBC和JDBC_Template

3、

JDBC和JDBC_Template

遇到的问题

  • 驱动问题：

    Driver class ‘com.mysql.cj.jdbc.Driver‘ not found.

    JDBC和JDBC_Template

  • 时区问题：

    JDBC和JDBC_Template

    解决方法一：在Advanced中更改（暂时性）

    JDBC和JDBC_Template

    解决方法二：sql语句中加 &severTime=UTC

    JDBC和JDBC_Template

JDBC操作事务

ACID原则

原子性（Atomicity）：

  • 要么都成功

  • 要么都失败

     

一致性（Consistency）：

  • 无论怎么样，最后的总值不变

  • 最终一致性：事务前后的数据完成性要保证一致

     

持久性（Durability）：

  • 事务没有提交：恢复原状

  • 事务已经提交：持久化到数据库（事务一旦提交就不可逆）

     

隔离性（Isolation）：多个进程互不干扰

隔离出现的问题

  1. 脏读：指一个事物读取了另一个事务未提交的数据

  2. 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同（不一定是错误，只是某些场合不对）

  3. 虚读（幻读）：在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

代码实现

  1. 开启事务

    conn.setAutoCommit(false);

  2. 一组业务执行完毕，提交事务

  3. 可以在catch中 显示的定义回滚语句，但默认失败就会回滚

数据库连接池

数据库连接——执行完毕——释放（其中连接，释放等操作 是 十分浪费系统资源的）

池化技术：准备一些预先的资源，过来就连接预先准备好的

连接数：类似于银行的服务员理解

最小连接数：（看常用连接数的个数）

最大连接数：业务最高承载上限

等待超时：100ms

编写连接池

只需要实现一个接口DataSource

开源数据源实现

DBCP

C3P0

Druid：阿里巴巴

使用了这些数据库连接池之后，我们在项目中就不需要编写连接数据库的代码了！

DBCP

需要用到 jar 包：

  • commons-dbcp-1.4.jar

  • commons-pool-1.6.jar

配置文件：

#连接设置   这里面的名字是DBCP数据源中定义好的
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=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 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

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

 

C3P0

需要用到 jar 包：

  • c3p0-0.9.5.5.jar

  • mchange-commons-java-0.2.19,jar

Druid

需要用到 jar 包：

  • druid-1.1.10.jar

定义配置文件：

  • 是properties形式的

  • 可以叫任意名称，可以放在任意目录下（不会自动加载，需要手动的去指定文件的名称和路径）

获取数据库连接池对象：通过工厂类（DruidDataSourceFactory）来获取对象，不是new

获取连接：getConnection

 

JDBC_Template

Spring框架对JDBC的简单封装——提供了一个JDBCTemplate对象简化JDBC的开发

操作步骤

  1. 导入jar包

    JDBC和JDBC_Template

  2. 创建JdbcTemplate对象（依赖于数据源DataSource）

    public static DataSource getDataSource(){
    return ds;
}
JdbcTemplate template = new JdbcTemplate(ds);

  3. 调用JdbcTemplate的方法来完成CRUD（增删改查）的操作

    update():执行DML语句（增、删、改语句）
queryForMap():查询结果将结果集封装为Map集合
queryForList():查询结果将结果集封装为List集合
query():查询结果，将结果封装为JavaBean对象
queryForObject:查询结果，将结果封装为对象

代码演示

        //创建JdbcTemplate对象
        JdbcTemplate template = new JdbcTemplate();
        //调用方法
        String sql = "update account set balance 5000 where id = ?";
        template.update(sql,3);
        //? 表示参数，有几个问号就传入几个参数

JDBC和JDBC_Template

上一篇：SparkSQL读写JDBC


下一篇：查询数据时报Syntax error or access violation: 1055 Expression #1 of SELECT list is not 错误解决