JavaWeb复习知识一：JDBC基础

一、 Jdbc快速入门（七个基本步骤）

package cn.itcast.jdbc;
import java.util.*;

import java.sql.*;

public class JdbcDemo1 {
    public static void main(String[] args) throws Exception{
        //1.抛出类找不到的异常,注册数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取数据库连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "root");
       //3.定义sql语句
        String sql = "update account set balance = 500 where id = 1";
        //4.获取执行sql的对象 statement
        Statement stmt = connection.createStatement();
        //5.执行Sql
        int count = stmt.executeUpdate(sql);
        //6.处理结果
        System.out.println(count);
        //7.释放资源
        stmt.close();
        connection.close();




    }
}

二、JDBCutils用户登录案例

1.在jdbc同级目录下创建一个新的目录，再在新的目录下创建JDBCUtuils.java文件,JDBCutil工具类代码

package cn.itcast.utils;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils
{
    private static String url;
    private static String user;
    private static String password;
    private static String driver;
    /*
    * 文件的读取，只需要读取一次即可拿到这些值。故使用静态代码块
    * */

    static
            //读取文件资源
    {
        try {
            //1.创建Properties集合类
            Properties pro = new Properties();
            //2.加载配置文件
            pro.load(new FileReader("src\\jdbc.properties"));
            //3.获取数据，赋值
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            //4.注册驱动
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    /*
    * 获取连接
    * @return连接对象
    * */
    public static Connection getConnection()throws SQLException{

            return DriverManager.getConnection(url, user, password);
        }
    /*
    * 释放资源
    * 
    * */
    public static void close(Statement stmt, Connection conn)
    {
        if(stmt != null) {

            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        
        if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
    }
    
    /*
    * 释放资源
    * */
    public static void close(ResultSet rs,Statement stmt, Connection conn)
    {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

2.使用JDBCUtils完成用户登录验证
*（在IDEA中创建一个包，再在包下创建一个JdbcDemo2.java文件）

package cn.itcast.jdbc;
import cn.itcast.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/*
* 需求：；1.通过键盘录入用户名和密码
*       2.判断用户是否登录成功
* */
public class JdbcDemo2 {
    public static void main(String[] args) {
        //1.键盘录入,接收用户名和密码
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = sc.nextLine();
        System.out.println("请输入密码");
        String password = sc.nextLine();
        //2.调用方法
        boolean flag = new JdbcDemo2().login(username, password);
        //3.判断结果，输出不同语句
        if (flag) {
            System.out.println("登录成功");
        } else {
            System.out.println("登陆失败");
        }
    }


    /*
    * 登录方法
    * */
    public boolean login(String username, String password) {
        if (username == null || password == null) {
            return false;
        }
        //连接数据库判断是否登录成功
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        //1.获取连接
        try {
             conn = JDBCUtils.getConnection();
            //2.定义SQL
            String sql = "select * from user where username = '" + username + "' and password = '" + password + "'";
            //3.获取执行SQL的对象
            stmt = conn.createStatement();
            //4.执行查询
            rs = stmt.executeQuery(sql);
            //5.判断
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, stmt, conn);
        }
        return false;

    }
}

3.在src目录下创建配置文件jdbc.properties
（IDEA中点文件–new–resource bundle）

url=jdbc:mysql:///db1
user=root
password=root
driver=com.mysql.jdbc.Driver

4.利用preparestatemen解决SQL注入问题

    /*
     * preparestatemen登录方法
     * */

    public boolean login(String username, String password) {
        if (username == null || password == null) {
            return false;
        }
        //连接数据库判断是否登录成功
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        //1.获取连接
        try {
            conn = JDBCUtils.getConnection();
            
      **//特别需要注意preparestatement**
 **//2.定义SQL
            String sql = "select * from user where username = ?  and password = ?;
            //3.获取执行SQL的对象
            pstmt = conn.prepareStatement(sql);
            //4.执行查询,不需要传递sql
            rs = pstmt.executeQuery();**

            //5.判断
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, pstmt, conn);
        }
        return false;

    }
}