JDBC数据库连接一 Li.060

JDBC 的概念

JDBC的概念
- JDBC(Java DataBase Connectivity Java数据库连接)是一种用于执行SQL语句的Java API, 可以为多种关系型数据库提供统一访问,它是由一组用Java语文编写的类和接口组成的
JDBC的本质
- 其实就是Java官方提供的一套规范(接口). 用于帮助开发人员快速实现不同关系型数据库的连接.
JDBC 的快速入门程序
- 导入Jar包
- 注册驱动
- 获取数据库连接
- 获取执行者对象
- 执行SQL语句并返回结果
- 处理结果
- 释放资源

package com.lizicai.jdbc;

import java.sql.*;

public class JDBCDemo1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 导入Jar包, 已导入
        // 注册驱动, 也可以省略掉
        Class.forName("org.mariadb.jdbc.Driver");

        // 获取数据库连接
        Connection connection = DriverManager.getConnection
                ("jdbc:mariadb://192.168.0.100:3306/db10", "root", "rootPassword");

        // 获取执行者对象
        Statement statement = connection.createStatement();

        // 执行SQL语句并返回结果
        String sql = "SELECT * FROM city";
        ResultSet rs = statement.executeQuery(sql);

        // 处理结果
        while (rs.next()){
            System.out.println(rs.getInt("id")+","+rs.getString("NAME")+","+rs.getInt("VERSION"));
        }

        // 释放资源
        connection.close();
        statement.close();
    }
}

DriverManager

DriverManger驱动管理对象
- 注册驱动
  - 注册给定的驱动程序: static void registerDriver(Driver driver)
  - 写代码使用: Class.forName(“org.mariadb.jdbc.Driver”)
  - 在org.mariadb.jdbc.Driver类中存在静态代码块

static {
    try {
        DriverManager.registerDriver(new Driver(), new DeRegister());
    } catch (SQLException var1) {
        throw new RuntimeException("Could not register driver", var1);
    }
}

不需要通过DriverManager调用静态方法registerDriver(),因为只要Driver类被使用,则会执行其静态代码块完成注册驱动

MySQL5.0(MariaDB同样适用)之后可以省略注册驱动的步骤.在Jar包中,存在一个java.sql.Driver配置文件,文件中指定了com.mysql.jdbc.Driver

获取数据库连接
- 获取数据库连接对象static Connection getConnection(String url, String user, String password)
- 参数:
  - url数据库连接路径jdbc:mariadb://ip:端口/数据库,(mysql是jdbc:mysql://192.168.0.100:3306/db10)
  - user用户名
  - password

Connection

Connection 数据库连接对象
- 获取执行者对象
  - 获取普通执行都对象: Statement createStatement();
  - 获取预编译执行者对象: PreparedStatement prepareStatement(String sql)
- 管理事务
  - 开启事务: setAutoCommit(boolean autoCommit); 参数为false,则开启事务
  - 提交事务: commit();
  - 回滚事务: rollback();
- 释放资源
  - 立即将数据库连接对象释放: void close();

Statement

Statement执行SQL语句的对象
- 执行DML语句: int executeUpdate(String sql);
  - 返回值int: 返回影响的行数
  - 参数sql: 可能执行insert,update,delete语句
- 执行DQL语句: ResultSet executeQuery(String sql);
  - 返回值ResultSet: 封装查询的结果
  - 参数sql: 可能执行select语句
- 释放资源
  - 立即将执行者对象释放: void close();

ResultSet

ResultSet结果集对象
- 判断结果集中是否还有数据:boolean next();
  - 有数据返回true,并将索引向移动一行
  - 没有数据返回false
- 获取结果集中的数据: XXX getXxx(“列名”);
  - XXX代表数据类型(要获取某列数据,这一列的数据类型).
  - String getString(“name”) int getInt(“age”)
- 释放资源
  - 立即将结果集对象释放: void close();

JDBC案例需要

使用JDBC完成对student表的CRUD操作
数据准备

# 数据准备
CREATE DATABASE db14;
USE db14;

CREATE TABLE student(
    id INT PRIMARY KEY auto_increment,
    NAME VARCHAR(50),
    age INT,
    birthday DATE
);
INSERT INTO student VALUES (NULL,'张三',23,'1999-09-23');
INSERT INTO student VALUES (NULL,'李四',24,'1998-08-10');
INSERT INTO student VALUES (NULL,'五五',25,'1996-06-06');
INSERT INTO student VALUES (NULL,'赵六',26,'1994-10-20');

创建Student类

自定义类的功能是为了封装表中的系列数据,成员变量和列保持一致

所有基本数据类型需要使用对应包装类,以免表中NULL值无法赋值

public class Student {
    private Integer sid;
    private String name;
    private Integer age;
    private Date birthday;
}

练习

查询所有学生信息
根据id查询学生信息
新增学生信息
修改学生信息
删除学生信息

package com.lizicai.domain;

import java.util.Date;

public class Student {
    private Integer sid;
    private String name;
    private Integer age;
    private Date birthday;
    public Student(){}

    public Student(Integer id, String name, Integer age, Date birthday) {
        this.sid = id;
        this.name = name;
        this.age = age;
        this.birthday = birthday;
    }

    public Integer getSid() {
        return sid;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", birthday=" + birthday +
                '}';
    }
}

package com.lizicai.dao;

import com.lizicai.domain.Student;

import java.util.ArrayList;

public interface StudentDao {
    // 查询所有学生信息
    public abstract ArrayList<Student> findAll();

    // 条件查询, 根据id获取学生信息
    public abstract Student findById(Integer id);

    //新增学生信息
    public abstract int insert(Student stu);

    //修改学生信息
    public abstract int update(Student stu);

    //删除学生信息
    public abstract int delete(Integer id);
}

import com.lizicai.domain.Student;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class StudentDaoImpl implements StudentDao{
    /**
     * 查询所有学生
     * @return
     */
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection connection = null;
        Statement statement = null;
        ResultSet rs  = null;
        try {
            Class.forName("org.mariadb.jdbc.Driver");

            connection = DriverManager.getConnection
                    ("jdbc:mariadb://192.168.0.100:3306/db14", "root", "rootPassword");

            statement = connection.createStatement();

            String sql = "SELECT * FROM student";

            rs = statement.executeQuery(sql);

            while (rs.next()){
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                list.add(new Student(sid,name,age,birthday));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
}

package com.lizicai.service;

import com.lizicai.domain.Student;

import java.util.ArrayList;

public interface StudentService {
    // 查询所有学生信息
    public abstract ArrayList<Student> findAll();

    // 条件查询, 根据id获取学生信息
    public abstract Student findById(Integer id);

    //新增学生信息
    public abstract int insert(Student stu);

    //修改学生信息
    public abstract int update(Student stu);

    //删除学生信息
    public abstract int delete(Integer id);
}

package com.lizicai.service;

import com.lizicai.dao.StudentDao;
import com.lizicai.dao.StudentDaoImpl;
import com.lizicai.domain.Student;

import java.util.ArrayList;

public class StudentServiceImpl implements StudentService{

    private StudentDao dao = new StudentDaoImpl();

    @Override
    public ArrayList<Student> findAll() {
        return dao.findAll();
    }

    @Override
    public Student findById(Integer id) {
        return dao.findById(id);
    }

    @Override
    public int insert(Student stu) {
        return dao.insert(stu);
    }

    @Override
    public int update(Student stu) {
        return dao.update(stu);
    }

    @Override
    public int delete(Integer id) {
        return dao.delete(id);
    }
}

package com.lizicai.controller;

import com.lizicai.domain.Student;
import com.lizicai.service.StudentService;
import com.lizicai.service.StudentServiceImpl;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Date;

public class StudentController {

    private StudentService service = new StudentServiceImpl();

    @Test
    public void findAll(){
        ArrayList<Student> list = service.findAll();
        for(Student stu:list){
            System.out.println(stu);
        }
    }
}

JDBC 工具类

编写配置文件
- 在src目录下创建config.properties配置文件

driverClass=org.mariadb.jdbc.Driver
url=jdbc:mariadb://192.168.0.100:3306/db14
username=root
password=rootPassword

编写JDBC 工具类

package com.lizicai.utils;

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

/**
 * JDBC 工具类
 */
public class JDBCUtils {


    // 1. 私有构造方法
    private JDBCUtils(){}

    //    2. 声明所需要的配置变量
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;
    private static Connection con;

    //    3. 提供静态代码块. 读取配置文件信息为变量赋值,注册驱动
    static {

        try {
            // 读取配置文件的信息为变量赋值
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("config.properties");
            Properties properties = new Properties();
            properties.load(is);

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

            // 注册驱动
            Class.forName(driverClass);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

//    4. 提供获取数据库连接方法
    public static Connection getConnection(){
        try {
            con = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }
//    5. 提供释放资源的方法

    public static void close(Connection con, Statement stat, ResultSet rs){
        if(con != null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stat != null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(Connection con, Statement stat){
        if(con != null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stat != null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

package com.lizicai.dao;

import com.lizicai.domain.Student;
import com.lizicai.utils.JDBCUtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class StudentDaoImpl2 implements StudentDao{
    /**
     * 查询所有学生
     * @return
     */
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        Statement stat = null;
        ResultSet rs  = null;
        try {

            con = JDBCUtils.getConnection();

            stat = con.createStatement();

            String sql = "SELECT * FROM student";

            rs = stat.executeQuery(sql);

            while (rs.next()){
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                list.add(new Student(sid,name,age,birthday));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(con,stat,rs);
        }

        return list;
    }
}

Student表的CRUD操作整合页面

# 数据准备
use db14;
CREATE TABLE USER(
    uid VARCHAR(50) PRIMARY KEY ,
    ucode VARCHAR(50),
    loginname VARCHAR(100),
    PASSWORD VARCHAR(100),
    username VARCHAR(100),
    gender VARCHAR(10),
    birthday DATE,
    dutydate DATE
);
INSERT INTO USER VALUES
('11111111','zhangsan001','zhangsan','1234','张三','男','2008-10-28','2018-10-28');
select * from USER;

SQL 注入攻击

什么是SQL注入攻击
- 就是利用SQL语句的漏洞来对系统进行攻击
SQL注入解决
- PreparedStatement预编译执行者对象
  - 在执行SQL语句前, 将SQL语句

PreparedStatement stat = null;
String sql = "SELECT * FROM USER WHERE loginname=? AND PASSWORD=?";
stat = con.prepareStatement(sql);
stat.setString(1,user.getLoginname());
stat.setString(2,user.getPASSWORD());
rs = stat.executeQuery();

JDBC 事务

JDBC 管理事务
- 管理事务的功能类:Connection
  - 开启事务: setAutoCommit(boolean autoCommit); 参数为false, 则开启事务
  - 提交事务: commit();
  - 关闭事务: close();

事务的控制放在service层做, dao层只存放, 下为dao层

@Override
public void save(Connection con, User user) {
    PreparedStatement stat = null;
    ResultSet rs = null;

    String sql = "insert into USER values (?,?,?,?,?,?,?,?)";
    java.sql.Date birthday = new java.sql.Date(user.getBirthday().getTime());
    java.sql.Date dutyday = new java.sql.Date(user.getDutyday().getTime());
    try {
        stat = con.prepareStatement(sql);
        stat.setString(1, user.getUid());
        stat.setString(2, user.getUcode());
        stat.setString(3, user.getLoginname());
        stat.setString(4, user.getPASSWORD());
        stat.setString(5, user.getUsername());
        stat.setString(6, user.getGender());
        stat.setDate(7, birthday);
        stat.setDate(8, dutyday);
        stat.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

int a = 1/0;模拟异常,去掉则可以正常存进来数据,service层

@Override
public void batchAdd(List<User> userList) {
    Connection con = JDBCUtils.getConnection();
    try{
        //开户事务
        con.setAutoCommit(false);
        for(User user : userList){
            //创建id, 并把uuid
            String uid = UUID.randomUUID().toString().replace("-","").toUpperCase(Locale.ROOT);
            user.setUid(uid);
            user.setUcode(uid);
            int a = 1/0;
            userDao.save(con, user);
        }
        // 提交事务
        con.commit();
    } catch (Exception e){
        // 回滚
        try {
            con.rollback();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        e.printStackTrace();
    } finally {
        JDBCUtils.close(con,null);
    }
}

@Test
public void batchAddTest()  {
    ArrayList<User> userList = new ArrayList<>();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    try {
        userList.add(new User(null,null,"lisi",
                    "123456","李四","男",sdf.parse("1992-02-04"),sdf.parse("2000-10-09")));
        userList.add(new User(null,null,"wangwu",
                    "123456","王五","男",sdf.parse("1992-02-05"),sdf.parse("2000-10-09")));
        userList.add(new User(null,null,"zhaoliu",
                    "123456","赵六","男",sdf.parse("1992-02-06"),sdf.parse("2000-10-09")));
        userService.batchAdd(userList);
    } catch (ParseException e) {
        e.printStackTrace();
    }
}

JDBC 的概念#

DriverManager#

Connection#

Statement#

ResultSet#

JDBC案例需要#

练习#

JDBC 工具类#

Student表的CRUD操作整合页面#

SQL 注入攻击#

JDBC 事务#