First ------JDBC

package com.gton;

import java.sql.*;

/**
 * @program: Jdbc-start
 * @description: jdbc  study
 * @author: GuoTong
 * @create: 2020-08-31 11:58
 **/
public class JdbcFirst {
    /**
     * 开发JDBC程序的步骤：
     * 1：导入数据库厂商的JAR到项目中，
     * 2：注册MySQL（数据库）驱动，即将jar加载到项目中。||通过反射将 com.mysql.jdbc.Driver 加载进项目程序。
     * 3：MySQL5.7开始，注册驱动厂商自动注册，可以省略。
     */
    //jdbc:mysql://localhost:3306/study?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false
    //serverTimezone=Asia/Shanghai||Hongkong
    public static final String URL = "jdbc:mysql://localhost:3306/study?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8";
    public static final String USER = "root";
    public static final String PASSWORD = "root";

    public static void main(String[] args) {


        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //2. 获得数据库连接;Connection getConnection(String url,String user, String password)
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            //3.操作数据库，实现增删改查
            stmt = conn.createStatement();
            //DQL使用executeQuery方法：DML的使用executeUpdate（）；其余的或者结果不确定的，都可以使用execute（）。
            rs = stmt.executeQuery("SELECT * FROM student");
            //如果有数据，rs.next()返回true
            //ResultSet 类似于迭代器。。。。可以一行行跌倒：通过列名获取数据。
            while (rs.next()) {
                //可以通过索引或者列名获取。。。
                System.out.println(" 学号：" + rs.getInt("id") + "\t" +
                        "姓名:" + rs.getString("name") + "\t" +
                        "年龄：" + rs.getInt("age") + "\t" +
                        "成绩：" + rs.getDouble("score") + "\t" +
                        "生日：" + rs.getDate("birthday"));

                //从小标是从1开始；

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();//关闭结果集对象
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();//关闭SQL执行对象
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();//关闭MySQL连接对象。
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


    }
}

JDBC  DML测试

package com.gton;

import java.sql.*;

/**
 * @program: Jdbc-start
 * @description: SQL增删改
 * @author: GuoTong
 * @create: 2020-08-31 15:40
 **/
public class DMLTest {
    public static final String URL = "jdbc:mysql://localhost:3306/study?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8";
    public static final String USER = "root";
    public static final String PASSWORD = "root";

    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //2. 获得数据库连接;Connection getConnection(String url,String user, String password)
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
          /*  //添加数据
            stmt = conn.createStatement();
            String sql = "insert into student values(null,'混蛋',20,90,'2020-8-31')";
            //DQL使用executeQuery方法：DML的使用executeUpdate（）；其余的或者结果不确定的，都可以使用execute（）。
            int count = stmt.executeUpdate(sql);
            if (count >= 1) {
                System.out.println("添加成功");

            } else {
                System.out.println("添加失败");
            }*/
            //修改数据
            /*stmt = conn.createStatement();
            String sql2 = "update student set name='狂神' where id=1006";
            //DQL使用executeQuery方法：DML的使用executeUpdate（）；其余的或者结果不确定的，都可以使用execute（）。
            int count2 = stmt.executeUpdate(sql2);
            if (count2>= 1) {
                System.out.println("修改成功");

            } else {
                System.out.println("修改失败");
            }*/
            //删除数据
            stmt = conn.createStatement();
            String sql3 = "delete  from student where id=1007";
            //DQL使用executeQuery方法：DML的使用executeUpdate（）；其余的或者结果不确定的，都可以使用execute（）。
            int count3 = stmt.executeUpdate(sql3);
            if (count3>= 1) {
                System.out.println("修改成功");

            } else {
                System.out.println("修改失败");
            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();//关闭结果集对象
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();//关闭SQL执行对象
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();//关闭MySQL连接对象。
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

JDK8----新写法JDBC

package com.gton;

import java.sql.*;

/**
 * @program: Jdbc-start
 * @description: JDK8优化简写try
 * @author: GuoTong
 * @create: 2020-08-31 15:30
 **/
public class JDK8jdbc {
    /**
     * 开发JDBC程序的步骤：
     * 1：导入数据库厂商的JAR到项目中，
     * 2：注册MySQL（数据库）驱动，即将jar加载到项目中。||通过反射将 com.mysql.jdbc.Driver 加载进项目程序。
     * 3：MySQL5.7开始，注册驱动厂商自动注册，可以省略。
     */
    //jdbc:mysql://localhost:3306/study?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false
    //serverTimezone=Asia/Shanghai||Hongkong
    public static final String URL = "jdbc:mysql://localhost:3306/study?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8";
    public static final String USER = "root";
    public static final String PASSWORD = "root";

    public static void main(String[] args) {

    // MySQL5.7开始，注册驱动厂商自动注册，可以省略。

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM student");) {
            while (rs.next()) {
                //可以通过索引或者列名获取。。。
                System.out.println(" 学号：" + rs.getInt("id") + "\t" +
                        "姓名:" + rs.getString("name") + "\t" +
                        "年龄：" + rs.getInt("age") + "\t" +
                        "成绩：" + rs.getDouble("score") + "\t" +
                        "生日：" + rs.getDate("birthday"));

                //从小标是从1开始；

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

    }
}

JDBC工具类，抽取

• （外部配置文件）  db.properties

URL=jdbc:mysql://localhost:3306/study?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8
USER=root
PASSWORD=root
DRIVER=com.mysql.jdbc.Driver

• java类

package com.gton;


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @program: Jdbc-start
 * @description: 自己封装jdbc工具类
 * @author: GuoTong
 * @create: 2020-08-31 16:11
 **/
public class JdbcUtils {
    public static String URL;
    public static String USER;
    public static String PASSWORD;
    public static String DRIVER;

    static {
        //1.加载驱动程序

        try {
            Properties properties = new Properties();
            //读取配置文件com/gton/transaction/jdbc.properties
            //推荐classLode
            InputStream resourceAsStream = JdbcUtils.class.getClassLoader().getResourceAsStream("com/gton/transaction/jdbc.properties");
            properties.load(resourceAsStream);
            DRIVER = properties.getProperty("DRIVER");
            Class.forName(DRIVER);
            URL = properties.getProperty("URL");
            USER = properties.getProperty("USER");
            PASSWORD = properties.getProperty("PASSWORD");

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

    }

    /*** 得到数据库的连接 */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    //获取数据库操作对象
    public static Statement getStatement(Connection conn) {
        try {
            return conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static PreparedStatement getPreparedStatement(Connection conn, String sql) {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return preparedStatement;
    }


    //关闭资源
    public static void close(Connection conn, Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

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

    public static void close(Connection conn, Statement stmt, ResultSet rs) {

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

    public static void close(Connection conn, PreparedStatement stmt, ResultSet rs) {

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

    public static final String[] stats = {"插入", "删除", "修改"};
    public static int change = 0;

    //添加
    public static int addSql(String sql, Statement statement) {
        String str = stats[0];
        if (change == 1) {
            str = stats[1];
        }
        if (change == 2) {
            str = stats[2];
        }
        int index = 0;
        try {
            index = statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (index >= 1)
            System.out.println(str + "成功");
        else
            System.out.println(str + "失败");
        return index;
    }
    //删除

    /**
     * @param sql
     * @return
     */
    public static int deleteSql(String sql, Statement statement) {
        change = 1;
        return addSql(sql, statement);
    }

    //修改
    public static int updateeSql(String sql, Statement statement) {
        change = 2;
        return addSql(sql, statement);
    }

}

测试JDBCUTIL工具类

package com.gton;

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

/**
 * @program: Jdbc-start
 * @description: 测试jdbc工具类
 * @author: GuoTong
 * @create: 2020-08-31 16:33
 **/
public class JdbcUtilTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = JdbcUtils.getStatement(connection);
            String sql = "select * from student";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                //可以通过索引或者列名获取。。。
                System.out.println(" 学号：" + resultSet.getInt("id") + "\t" +
                        "姓名:" + resultSet.getString("name") + "\t" +
                        "年龄：" + resultSet.getInt("age") + "\t" +
                        "成绩：" + resultSet.getDouble("score") + "\t" +
                        "生日：" + resultSet.getDate("birthday"));

                //从小标是从1开始；

            }
            //添加
            //String sql = "insert into student values(null,'混蛋',20,90,'2020-8-31')";
            // JdbcUtils.addSql(sql,statement);
            //修改
            //String sql2 = "update student set name='狂神' where id=1008";
            //JdbcUtils.updateeSql(sql2,statement);
            //删除
            // String sql3 = "delete from student where id=1008";
            // JdbcUtils.deleteSql(sql3,statement);


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection, statement, resultSet);
        }
    }
}

SQL注入问题

package com.gton;

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

/**
 * @program: Jdbc-start
 * @description: 登录问题
 * @author: GuoTong
 * @create: 2020-08-31 17:15
 **/
public class PwdAndName {
    public static void main(String[] args) {

        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String name = sc.nextLine();
        System.out.println("请输入密码：");
        String password = sc.nextLine();
        login(name, password);

    }

    private static void login(String name, String password) {
        //声明。。。
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            //实例化。。。jdbc相关。。。
            connection = JdbcUtils.getConnection();

           /* //statement   sql注入
            String sql = "select * from student where name='"+name+"'and birthday='"+password+"'";
            statement = JdbcUtils.getStatement(connection);
            resultSet = statement.executeQuery(sql);*/

            //PreparedStatement
            String sql2 = "select * from student where name=? and birthday=?";
            preparedStatement = JdbcUtils.getPreparedStatement(connection, sql2);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();


            if (resultSet.next()) {
                //可以通过索引或者列名获取。。。
                System.out.println("登录成功");
                System.out.println(" 学号：" + resultSet.getInt("id") + "\t" +
                        "姓名:" + resultSet.getString("name") + "\t" +
                        "年龄：" + resultSet.getInt("age") + "\t" +
                        "成绩：" + resultSet.getDouble("score") + "\t" +
                        "生日：" + resultSet.getDate("birthday"));
            } else {
                System.out.println("登录失败");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null)
                JdbcUtils.close(connection, preparedStatement, resultSet);
            if (statement != null)
                JdbcUtils.close(connection, statement, resultSet);
        }
    }
}