jdbc连接数据库

最新推荐文章于 2022-12-29 10:55:12 发布

qq_47721411

最新推荐文章于 2022-12-29 10:55:12 发布

阅读量249

点赞数

文章标签：数据库 java mysql

本文链接：https://blog.csdn.net/qq_47721411/article/details/127991344

版权

JDBC概述

JDBC API是一系列的接口，它统一和规范了应用程序与数据库的连接，执行SQL语句，并得到返回结果等各类操作，相关类和接口在java.sql与javax.sql包中

JDBC程序编写步骤

1.注册驱动-----加载Driver类
2.获取连接-----得到Connection
3.执行增删改查--------发送sql给mysql执行
4.释放资源---------关闭相关连接

数据库连接方式

方式一：

		Driver driver = new Driver();
        String url = "jdbc:mysql://bigdata12:3306/bigdata";
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","123456");
        Connection connect = driver.connect(url, properties);
        String sql = "insert into b(id,name) values(3,'ss')";
        Statement statement = connect.createStatement();
        int rows = statement.executeUpdate(sql);
        System.out.println(rows > 0 ? "成功" : "失败");
        statement.close();
        connect.close();
        
 		Driver driver = new Driver();
        String url = "jdbc:mysql://bigdata12:3306/bigdata";
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","123456");
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);

方式二：

		Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");// 使用反射加载Driver类
        Driver driver = (Driver)aClass.newInstance();
        String url = "jdbc:mysql://bigdata12:3306/bigdata";
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","123456");
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);

方式三：

		Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver)aClass.newInstance();
        String url = "jdbc:mysql://bigdata12:3306/bigdata";
        String user = "root";
        String password = "123456";
        DriverManager.registerDriver(driver);// 注册驱动
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);

方式四：

//        static {
//            try {
//                DriverManager.registerDriver(new Driver());
//            } catch (SQLException var1) {
//                throw new RuntimeException("Can't register driver!");
//            }
//        }
		// 静态代码块在类加载时会执行一次
		// 使用反射加载Driver类，在加载时会自动完成注册驱动
 		Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://bigdata12:3306/bigdata";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);

方式五：
1.mysql驱动5.1.6可以无需Class.forName(“com.mysql.jdbc.Driver”);
2.从jdk1.5以后使用了jdbc4,不再需要显示调用class.forName()注册驱动而是自动调用驱动jar包下META-INF\services\java.sql.Driver文本中的类名称去注册
3.建议还是写上Class.forName(“com.mysql.jdbc.Driver”)更加明确

 		Properties properties = new Properties();
        properties.load(new FileInputStream("src/main/resources/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
		
		user=root
		password=123456
		url=jdbc:mysql://bigdata12:3306/bigdata
		driver=com.mysql.jdbc.Driver

ResultSet结果集

1.表示数据库结果集的数据表，通常通过执行查询数据库的语句生成
2.ResultSet对象保持一个光标指向其当前数据行。最初，光标位于第一行之前
3.next方法将光标移动到下一行，并且由于在ResultSet对象中没有更多行时返回false，因此可以在while循环中使用循环来遍历结果集。

		Properties properties = new Properties();
        properties.load(new FileInputStream("src/main/resources/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();
        String sql = "select id,name from b";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            System.out.println(id + "\t" + name);
        }
        resultSet.close();
        statement.close();
        connection.close();

Statement

1.Statement对象用于执行静态SQL语句并返回其生成的结果的对象
2.在连接建立后，需要对数据库进行访问，执行命名或是SQL语句，可以通过
Statement(存在SQL注入)
PreparedStatement(预处理)
CallableStatement(存储过程)
3.Statement对象执行SQL语句，存在SQL注入风险
4.SQL注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的SQL语句段或命令，恶意攻击数据库
5.要防范SQL注入，只要用PreparedStatement(从Statement扩展而来)取代Statement就可以了
输入用户名：1’ or
输入密码：or ‘1’ = '1
scanner.next()当接收到空格或者单引号表示结束
scanner.nextLine();当接收到换行才表示结束

PreparedStatement

1.PreparedStatement执行的SQL语句中的参数用问号(?)来表示，调用PreparedStatement对象的setXxx()方法来设置这些参数，setXxx()方法有两个参数，第一个参数是要设置的SQL语句中的参数的索引(从1开始)，第二个是设置的SQL语句中的参数的值
2.调用executeQuery()，返回ResultSet对象
3.调用executeUpdate(),执行更新包括增删改
预处理好处：
1.不再使用+拼接sql语句，减少语法错误
2.有效的解决了sql注入问题
3.大大减少了编译次数，效率较高

preparedStatement.executeQuery() 查询操作

 		Scanner scanner = new Scanner(System.in);
        System.out.print("请输入管理员的id：");
        // next()当接收到空格或者单引号表示结束
        String admin_name = scanner.nextLine();
        System.out.print("请输入管理员的名字：");
        String admin_pwd = scanner.nextLine();
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/main/resources/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql = "select name,pwd from admin where name =? and pwd =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,admin_name);
        preparedStatement.setString(2,admin_pwd);
        // select语句执行executeQuery()
        // update delete insert执行的executeUpdate()
        // 这里执行的executeQuery()不要再写sql
        // 如果sql里面没有?，executeQuery(sql)可以写sql
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            System.out.println("登陆成功");
        } else {
            System.out.println("登陆失败");
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();

preparedStatement.executeUpdate() dml语句增删改

		Scanner scanner = new Scanner(System.in);
        System.out.println("请输入管理员姓名：");
        String admin_name = scanner.nextLine();
        System.out.println("请输入管理员密码：");
        String admin_pwd = scanner.nextLine();
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/main/resources/mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql = "insert into admin values(?,?)";
        String sql01 = "update admin set pwd = ? where name = ?";
        String sql02 = "delete from admin where name = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,admin_name);
        preparedStatement.setString(2,admin_pwd);
        int rows = preparedStatement.executeUpdate();
        System.out.println(rows > 0 ? "执行成功" : "执行失败");
        preparedStatement.close();
        connection.close();

JDBC相关API小结

DriverManager驱动管理类
1.getConnection(url,user,pwd)获取连接
Connection接口
1.createStatement():生成命令对象
2.prepareStatement(sql):生成预编译对象
resultSet.getXxx(“”):通过列名来获取值
resultSet.getXxx(1):获取该行的第一列
在这里插入图片描述

封装JDBCUtils

在jdbc操作中，获取连接和释放资源是经常使用到的，可以将其封装JDBC连接的工具类JDBCUtils

 	private static String user;
    private static String password;
    private static String url;
    private static String driver;
    static {
        
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src/main/resources/mysql.properties"));
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");
        } catch (IOException e) {
            // 将编译异常转成运行异常，调用者就可以选择捕获该异常或者选择默认处理该异常
            throw new RuntimeException(e);
        }
    }
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public static void close(ResultSet set, Statement statement,Connection connection){
        
            try {
                if (set != null){
                    set.close();
                }
                if (statement != null){
                    statement.close();
                }
                if (connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

JDBCUtilsDML

 		Connection connection = null;
       String sql = "Update actor set name = ? where id = ?";
       PreparedStatement preparedStatement = null;
       try {
           connection = JDBCUtils.getConnection();
           preparedStatement = connection.prepareStatement(sql);
           preparedStatement.setString(1,"周星驰");
           preparedStatement.setInt(2,4);
           preparedStatement.executeUpdate();
       } catch (SQLException e) {
           e.printStackTrace();
       } finally {
           JDBCUtils.close(null,preparedStatement,connection);
       }

JDBCUtils查询

 		Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from actor where id = ?";
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,5);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                String borndate = resultSet.getString("borndate");
                System.out.println(id + "\t" +name +"\t" + sex +"\t" +borndate);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }