JDBC连接数据库

上弦月QAQ

已于 2023-04-10 09:45:32 修改

阅读量845

点赞数 1

分类专栏： Java Web框架程序设计文章标签：数据库 java

于 2022-11-26 11:54:52 首次发布

本文链接：https://blog.csdn.net/qq_46323758/article/details/128047846

版权

Java Web框架程序设计专栏收录该内容

6 篇文章 0 订阅

订阅专栏

JDBC

一、操作数据库流程

1、客户端发起请求 2、mysql -h localhost -u root -p localhost:ip地址
-u：用户名
-p：密码端口号：3306
3、编写sql语句，并且执行
4、数据库返回结果

如何使用Java操作数据库？JDBC

二、JDBC

2.1 JDBC 概述

JDBC Java Database Connectivity
连接数据库类型：mysql oracle sqlServer

2.2 JDBC连接数据库步骤

1、导入依赖：将jdbc的工具包，导入项目中
2、加载驱动：Driver DriverManger type:class
3、获取数据库连接：url username password Connection type：interface
4、获取发送SQL语句的Statement对象 Statement type：interface
5、获取并解析结果集 ResultSet
type: interface 6、释放资源：close() 原则：先开后闭

2.3 JDBC 具体使用

2.3.1 导入jar

1、项目名右键新建一个文件夹，命名为lib
2、将jar包导入文件夹中
3、文件夹名称上右键Add as Library

2.3.2 详细操作

package com.qf.edu;

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

public class DemoJdbcInsert {
    public static void main(String[] args) throws ClassNotFoundException,SQLException {
        // 1、加载驱动 反射 抛出ClassNotFoundException
        Class.forName("com.mysql.jdbc.Driver");
        // 2、获取数据库连接 抛出SQLException
        String url = "jdbc:mysql://localhost:3306/hello?useUnicode = true & characterEncoding = utf8";
        /*
        jdbc:mysql : 协议 指明jdbc的驱动 操作的是mysql数据库
        localhost: 指代本地 127.0.0.1 ip地址 windows：ipconfig
        3306: 端口号
        hello: 数据库名称
        */
        String username = "root";
        String password = "0000";
        Connection connection = DriverManager.getConnection(url, username,password);
        // 3、获取发送Sql语句的Statement对象
        Statement statement = connection.createStatement();
        // 4、SQL语句
        String sql = "insert into user(username,userpswd)values('admin', 'admin') ";
        // 5、使用statement发送sql语句，并获取返回结果
        // statement.executeUpdate(sql) 增删改的操作方法
        // statement.executeQuery(sql) 查询的操作方法
        int update = statement.executeUpdate(sql);
        // 6、解析结果集
        if (update == 1) {
            System.out.println("success");
        } else {
            System.out.println("failed");
        }
        // 7、释放资源
        statement.close();
        connection.close();
    }
}

package com.qf.edu;
import java.sql.*;

public class DemoJDBCQuery {
    public static void main(String[] args) throws ClassNotFoundException,SQLException {
        // 1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2、获取数据库连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/hellouserUnicode=true&characterEncoding=utf8","root","0000");
        // 3、获取statement对象
        Statement statement = connection.createStatement();
        // 4、获取结果集 存储的数据为从数据中查询到的数据
        ResultSet resultSet = statement.executeQuery("select * from user");
        // 5、结果集解析
        // 判断有无下一条数据
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String userpswd = resultSet.getString("userpswd");
            System.out.println("id : " + id + " username : " + username + "userpswd : " + userpswd);
        }
        // 6、释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

三、SQL注入问题

用户输入的条件含有sql中的关键字，并且参与了sql语句的执行，从而将sql语句的结果更改的情况，叫sql 注入。
解决：
预编译：将sql语句事先处理，之后用户只输入条件，这个条件不会影响sql语句的执行。
好处：
1、预编译sql语句，可以有效的防止sql注入
2、安全，效率高
3、可以动态的填充数据，

四、PreparedStatement

PreparedStatement 继承了Statement类，可以防止sql注入问题。

package com.qf.edu;
import java.sql.*;
import java.util.ArrayList;

public class DemoPrepareStatement {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = new User();
        ArrayList<User> list = new ArrayList<>();
        try {
            // 1、加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 2、获取数据库连接
            connection =
                    DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/hello?useUnicode=true&characterEncoding=utf8","root","0000");
            // 3、准备sql语句 ? 参数标记符
            String sql = "select * from user where id = ? and username = ? and userpswd = ?";
            // 4、获取preparedStatement对象
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,116);
            preparedStatement.setString(2,"老王");
            preparedStatement.setString(3,"123456");
            // 5、获取结果集对象
            resultSet = preparedStatement.executeQuery();
            // 6、解析结果集 用集合封装结果 ArrayList<User>
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String userpswd = resultSet.getString("userpswd");
                user.setId(id);
                user.setUsername(username);
                user.setUserpswd(userpswd);
                list.add(user);
            }
            System.out.println(list);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            // 7、释放资源
            try {
                resultSet.close();
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

五、综合案例

登录：用户名密码成功,输出登录成功；失败，重新跳转到登录
注册：用户信息成功，跳转到登录，失败，重新注册
用户表：id username password addr desc phone sex

entity: 实体类
dao: 数据库操作 CRUD
service: 逻辑判断
servlet: 信息展示，前后端交互

注册：用户名是否存在存在，则重新注册不存在继续注册成功跳转登录失败再次注册
登录：一个查询语句用户名密码都匹配失败重新登录
表：user

package com.qf.entity;

public class User{
    private int id;
    private String username;
    private String password;
    private String addr;
    private String desc;
    private String phone;
    private String sex;

    public User() {
    }
    public User(int id, String username, String password, String addr, String
            desc, String phone, String sex) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.addr = addr;
        this.desc = desc;
        this.phone = phone;
        this.sex = sex;
    }
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getAddr() {
        return addr;
    }
    public void setAddr(String addr) {
        this.addr = addr;
    }
    public String getDesc() {
        return desc;
    }
    public void setDesc(String desc) {
        this.desc = desc;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    @Override
    public String toString() {
        return "User03{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", addr='" + addr + '\'' +
                ", desc='" + desc + '\'' +
                ", phone='" + phone + '\'' +
                ", sex='" + sex + '\'' +
                '}';
    }
}

package com.qf.dao;
import com.qf.entity.User;

public interface UserDao {
    public String selectByUsername(String username);
    
    public int insertUser(User user);
}

package com.qf.dao.impl;

import com.qf.dao.UserDao;
import com.qf.entity.User;

import java.sql.*;

public class UserDaoImpl implements UserDao {
    @Override
    public String selectByUsername(String username) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/text_03?userUnicode=true&characterEncoding=utf8","root","root");
            preparedStatement = connection.prepareStatement("select username from user where username = ?");
            preparedStatement.setString(1,username);
            resultSet = preparedStatement.executeQuery();
            String name = null;
            while (resultSet.next()) {
                name = resultSet.getString("username");
            }
            return name;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
    @Override
    public int insertUser(User user) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/text_03?userUnicode=true&characterEncoding=utf8","root","root");
            preparedStatement = connection.prepareStatement("insert into user(username,password,addr,desc,phone,sex) values(?,?,?,?,?,?)");
            preparedStatement.setString(1,user.getUsername());
            preparedStatement.setString(2,user.getPassword());
            preparedStatement.setString(3,user.getAddr());
            preparedStatement.setString(4,user.getDesc());
            preparedStatement.setString(5,user.getPhone());
            preparedStatement.setString(6,user.getSex());
            int update = preparedStatement.executeUpdate();
            return update;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }
}

package com.qf.service;
import com.qf.entity.User;

public interface UserService {

    public int register(User user);
}

package com.qf.service.impl;
import com.qf.dao.UserDao;
import com.qf.dao.impl.UserDaoImpl;
import com.qf.entity.User;
import com.qf.service.UserService;

public class UserServiceImpl implements UserService {
    UserDaoImpl userDao = new UserDaoImpl();
    @Override
    public int register(User user) {
    // 判断用户名是否存在
        String s = userDao.selectByUsername(user.getUsername());
        if (s != null) {
            return -1;
        }
        int i = userDao.insertUser(user);
        return i;
    }
}

package com.qf.controller;
import com.qf.entity.User;
import com.qf.service.impl.UserServiceImpl;
import java.util.Scanner;

public class Menu {
    public static void main(String[] args) {
        UserServiceImpl userService = new UserServiceImpl();
        int num = -1;
		// 从键盘输入
        while (true) {
            System.out.println("*********************");
            System.out.println("**** 老王洗浴中心 ****");
            System.out.println("**** 1、注册");
            System.out.println("**** 2、登录");
            System.out.println("**** 3、退出");
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入您的选择：");
            int choice = -1;
            if (num != -1 ) {
                choice = num;
            } else {
                choice = scanner.nextInt();
            }
            if (choice == 3) {
                System.out.println("欢迎下次光临老王洗浴中心！");
                break;
            }
            switch (choice) {
                case 1:
                    System.out.println("注册");
                    System.out.println("请输入用户名：");
                    String username = scanner.next();
                    System.out.println("请输入密码：");
                    String password = scanner.next();
                    System.out.println("请输入地址：");
                    String addr = scanner.next();
                    System.out.println("请输入描述：");
                    String desc = scanner.next();
                    System.out.println("请输入电话：");
                    String phone = scanner.next();
                    System.out.println("请输入性别：");
                    String sex = scanner.next();
                    User user = new User(-1, username, password, addr,
                            desc, phone, sex);
                    int register = userService.register(user);
                    if (register == -1) {
                        System.out.println("用户名存在，请重新注册！");
                    } else if (register == 1) {
                        System.out.println("注册成功");
                        num = 2;
                    } else {
                        System.out.println("注册失败");
                    }
                    break;
                case 2:
                    num = -1;
                    System.out.println("登录！！！");
                    break;
                default:
                    System.out.println("输入错误，请重新输入！！！");
            }
        }
    }
}

六、DBUtils工具类封装

package com.qf.utils;
import java.sql.*;

public class DBUtils {
    // 加载驱动
    static {
    // 静态代码块 程序启动时加载，只加载一次
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection connection = null;
        String url = "jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf8";
        /*
        jdbc:mysql : 协议 指明jdbc的驱动 操作的是mysql数据库
        localhost: 指代本地 127.0.0.1 ip地址 windows：ipconfig
        3306: 端口号
        hello: 数据库名称
        */
        String username = "root";
        String password = "0000";
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
 
    public static void closeAll(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) {
            e.printStackTrace();
        }
    }
}

七、数据库连接池

druid
解析：在程序启动时，预先创建指定数量的数据库连接，放入到池中，需要数据库连接时，直接从池中获取，使用结束之后，再冲新放回。
好处：实现复用，节省资源，提升效率。

7.1 druid的使用

1、将jar包复制到lib文件中
2、项目名右键新建一个文件，命名database.properties,配置文件

连接设置 driverClassname=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/hello?useUnicode=true&characterEncoding=utf8
username=root password=root 连接池初始化设置初始化连接数量 initialSize=10 最大连接数量
maxActive=50 最小连接数量 minIdle=5 最大超时时间单位毫秒 maxWait=5000

package com.qf.utils;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.pool.DruidPooledConnection;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class DBUtils {
    // 数据库连接池对象
    private static DruidDataSource ds;
    // 加载驱动
    static {
    /*
    // 静态代码块 程序启动时加载，只加载一次
        try {
        Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }*/
        // 配置文件对象
        Properties properties = new Properties();
        try {
            // 将配置文件加载到配置文件对象中 可能不生效
            //properties.load(new FileInputStream("database.properties"));
            properties.load(DBUtils.class.getClassLoader().getResourceAsStream("database.properties"));
            // 通过连接池工厂获取连接池对象 工厂模式
            ds = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
    /* Connection connection = null;
    String url = "jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf8";
    */
    /*
    jdbc:mysql : 协议 指明jdbc的驱动 操作的是mysql数据库
    localhost: 指代本地 127.0.0.1 ip地址 windows：ipconfig
    3306: 端口号
    hello: 数据库名称
    */
    /*
    String username = "root";
    String password = "0000";
    try {
    connection = DriverManager.getConnection(url, username, password);
    } catch (SQLException e) {
    e.printStackTrace();
    }*/
        Connection connection = null;
        try {
            connection = ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    
    public static void closeAll(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) {
            e.printStackTrace();
        }
    }
}