JDBC的使用

概念

• Java Database Connectivity，简称JDBC
• JDBC：sun公司定义的一套操作数据库的规范，就是接口

四个核心的对象

• DriverManager：依据数据库的不同，管理JDBC驱动
• Connection： 负责连接数据库并担任传送数据的任务
• Statement： 由 Connection 产生、负责执行SQL语句
• ResultSet：负责保存Statement执行后所产生的查询结果

DriverManager

DriverManager（驱动管理类）：

​ 1、 注册驱动

​	Class.forName("com.mysql.jdbc.Driver")注册驱动//可以不用写了

​ 上面代码将类导入到内存运行，类导入后内存后会自动运行内部静态代码 块其中有一行代码DriverManager.registerDriver这一句才是真正注册驱动 的方法

​ 2、 连接数据库

​	Connection conn = DriverManager.getConnection(url（MySQL表的地址），mysql用户名，密	码)连接数据库

- 语法: jdbc:mysql://ip地址(域名):端口号/数据库名称?参数键值对1&参数键值对2...

示例: jdbc:mysql://127.0.0.1:3306/db1

• 细节:如果连接的是本机mysal服务器，并且mysql服务默认端口是3306

Connection

connection（数据库连接对象）作用：

• 获取执行sql语句的对象
  • conn.createStatement()普通执行对象
  • PreparedStatement PreparedStatement()预编译SQL的执行SQL对象: 防止SQL注入

Statement

• Statement作用：
  • 执行sql语句
  • int executeUpdate(sql): 执行DML、DDL语句
  • 返回值: (1) DML语句影响的行数 (2) DDL语句执行后，执行成功也可能返回 0
  • ResultSet executeQuery(sql): 执行DQL 语句
  • 返回值: ResultSet 结果集对象

ResultSet

• ResultSet（结果集对象）作用：

  • 封装了DQL查询语句的结果

  • ResultSetstmt.executeQuery(sql): 执行DQL 语句，返回 ResultSet 对象

  • 获取查询结果

  • boolean next():(1)将光标从当前位置向前移动一行 (2)判断当前行是否为有效行

  • 效返回值:
    true: 有效行，当前行有数据
    false: 无效行，当前行没有数据

  • getXxx(参数): 获取数据XXx

  • xxx: 数据类型;如: int getlnt(参数);String getString(参数)

  参数:
  int: 列的编号，从1开始
  string: 列的名称

PreparedStatement

• PreparedStatement作用:
  • 预编译SQL语句并执行: 预防SQL注入问题
  • SQL注入：SQL注入是通过操作输入来修改事先定义好的SQL语句，用以达到执行代码对服务器进行攻击的方法
  • 在获取PreparedStatement对象是就需要传入sql语句是需要用？作为占位符代替字符串拼接位置
  • 会对传入的属性中的敏感字符进行转义，从而达到内容一样但是不会与SQL拼接的效果

案例

• 创建JDBC工具类

package com.xinzhi.utils;

import java.sql.*;

public class JdbcUtils {
    private static String url="jdbc:mysql://127.0.0.1:3306/test?userSSL=false&useServerPrepStmts=true";
    private static String username = "root";
    private static String password="123456";
//    private static ;

    public static Connection getConnection(){
        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            return connection;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public  static void release(ResultSet resultSet, Statement statement,Connection connection){
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

    }
    private JdbcUtils() {
    }
}

• 加密工具

package com.xinzhi.utils;

import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;

public class MD5Utils {

    public static String encrypt(String password){
        MessageDigest md = null;
        String s = null;
        try {
            md = MessageDigest.getInstance("MD5");
            md.update(password.getBytes());
            s = new BigInteger(1, md.digest()).toString(16);
        } catch (NoSuchAlgorithmException e) {
            e.printStackTrace();
        }
        return s;
    }
}

• 实体类

package com.xinzhi.model;

public class User {
   private int id;
   private String name;
   private String username;
   private String password;
   private String phone;
   private String email;

   public User() {
   }
   public User( String name, String username, String password, String phone, String email) {
       this.name = name;
       this.username = username;
       this.password = password;
       this.phone = phone;
       this.email = email;
   }
   public User(int id, String name, String username, String password, String phone, String email) {
       this.id = id;
       this.name = name;
       this.username = username;
       this.password = password;
       this.phone = phone;
       this.email = email;
   }

   public int getId() {
       return id;
   }

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

   public String getName() {
       return name;
   }

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

   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 getPhone() {
       return phone;
   }

   public void setPhone(String phone) {
       this.phone = phone;
   }

   public String getEmail() {
       return email;
   }

   public void setEmail(String email) {
       this.email = email;
   }
}

• dao层代码

package com.xinzhi.dao;

import com.xinzhi.model.User;

public interface IUserdao {
   String registered(User user);
   String login(String username,String password);
   String revise(User user);
   String delete(String username);
}

package com.xinzhi.dao;

import com.xinzhi.model.User;
import com.xinzhi.utils.JdbcUtils;
import com.xinzhi.utils.MD5Utils;

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

public class UserDaoImpl implements IUserdao {
    @Override
    public String registered(User user) {
        Connection connection = JdbcUtils.getConnection();
        String sql="insert into user(name,username,password,phone,email) VALUES (?,?,?,?,?)";
        PreparedStatement preparedStatement=null;
        try {
            if (user.getUsername()==null){
                return "用户名不能为空值";
            }if(user.getUsername().trim().length()<6){
                return "用户名长度需要大于6";
            }if (repeat(user.getUsername())){
                return "用户名重复";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,user.getName());
            preparedStatement.setString(2,user.getUsername());
            String encrypt = MD5Utils.encrypt(user.getPassword());
            preparedStatement.setString(3, encrypt);
            preparedStatement.setString(4, user.getPhone());
            preparedStatement.setString(5, user.getEmail());
            int i = preparedStatement.executeUpdate();
            return i>0?"注册成功":"注册失败";
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtils.release(null,preparedStatement,connection);
        }
    }

    @Override
    public String login(String username, String password) {
        Connection connection = JdbcUtils.getConnection();
        String sql = "select username,password from user where username=? and password=?";
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                return "登陆成功";
            }
            return "登录失败";
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtils.release(null,preparedStatement,connection);
        }
    }

    @Override
    public String revise(User user) {
        Connection connection = JdbcUtils.getConnection();
        String sql="update user set name=?,username=?,password=?,phone=?,email=? where id =?";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,user.getName());
            preparedStatement.setString(2, user.getUsername());
            preparedStatement.setString(3, user.getPassword());
            preparedStatement.setString(4, user.getPhone());
            preparedStatement.setString(5, user.getEmail());
            preparedStatement.setInt(6,user.getId());
            int i = preparedStatement.executeUpdate();
            return i>0?"修改成功":"修改失败";
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtils.release(null,preparedStatement,connection);
        }
    }

    @Override
    public String delete(String username) {
        Connection connection = JdbcUtils.getConnection();
        String sql ="delete from user where username=?";
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            int i = preparedStatement.executeUpdate();
            return i>0?"删除成功":"删除失败";
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtils.release(null,preparedStatement,connection);
        }
    }

    public Boolean repeat(String username ){
        Connection connection = JdbcUtils.getConnection();
        String sql ="select username from user where username = ?";
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                return true;
            }
            return false;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtils.release(resultSet,preparedStatement,connection);
        }
    }
}