JavaWeb学习——MySQL与JDBC

1. 学习内容

对MySQL数据库进行学习,掌握MySQL语句,可实现创建表,增删查改表,了解如何使用事务。对JDBC学习，实现了通过Java实现对数据库进行操作,初步实现连接数据库,并实现了创建工具类对注册驱动和关闭资源进行封装,同时实现了JDBC中使用事务.

2. 学习成果

JDBC工具类

package Utils;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
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 {
        //创建Properties集合类
        Properties pro = new Properties();
        try {
            //调用ClassLoader类获取文件路径
            //文件夹存在空格无法识别 该方法无法使用 转用相对路径
//            ClassLoader cl = JDBCUtils.class.getClassLoader();
//            URL res = cl.getResource("jdbc.properties");
//            System.out.println(res);
//            String path = res.getPath();
//            System.out.println(path);

            //加载文件
            pro.load(new FileReader("src\\jdbc.properties"));
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");

            //注册驱动
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    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 e) {
                e.printStackTrace();
            }
            if (conn != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

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

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

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

使用该类实现用户登录

package JDBC;

import Utils.JDBCUtils;

import java.sql.*;
import java.util.Scanner;

public class jdbc_login {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String username = sc.next();
        System.out.println("请输入用户密码:");
        String password = sc.next();
        boolean flag = new jdbc_login().login(username, password);
            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;
        PreparedStatement pstm = null;//使用Statement子类PreparedStatement可防止sql注入,并且效率更高
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from user where username = ? and password = ?";
            //stmt = conn.createStatement();
            pstm = conn.prepareStatement(sql);
            //将占位符?赋值
            pstm.setString(1,username);
            pstm.setString(2, password);
            //不需要传递sql 执行查询
            rs = pstm.executeQuery();
            return rs.next();//如果可以在下一行查询到,则返回true
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,pstm,conn);
        }
        return false;
    }
}

在JDBC中使用事务

package JDBC;

import Utils.JDBCUtils;

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

public class jdbc_事务 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstm1 = null;
        PreparedStatement pstm2 = null;
        try {
            //连接数据库
            conn = JDBCUtils.getConnection();
            //开启事务
            conn.setAutoCommit(false);
            //定义转账sql语句
            String sql1 = "update 数据211名单 set 账户 = 账户 - ? where 学号 = ?";
            String sql2 = "update 数据211名单 set 账户 = 账户 + ? where 学号 = ?";
            //获取执行sql对象
            pstm1 = conn.prepareStatement(sql1);
            pstm2 = conn.prepareStatement(sql2);
            //设置参数
            pstm1.setInt(1,500);
            pstm1.setInt(2,1);

            pstm2.setInt(1,500);
            pstm2.setInt(2,2);
            //执行sql
            pstm1.executeUpdate();
            pstm2.executeUpdate();
            //如果到最后还没有问题 提交事务
            conn.commit();
        } catch (Exception e) {
            try {
                if (conn != null) {
                    conn.rollback();//事务回滚 回到为开始事务的状态
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JDBCUtils.close(pstm1,conn);
            JDBCUtils.close(pstm2,null);
        }
    }
}