JDBC学习笔记(二) 对数据库最基本的操作

JDBC学习笔记(二) 对数据库最基本的操作

JDBC学习笔记(一) 创建数据库连接

3. JDBC对操作库最基本的操作

有了连接操作之后，我们就可以对数据库做基本的操作，为了方便，将连接操作封装到一个工具类的静态方法之中，其静态方法如下:

// 通用建立连接的静态方法
    public static Connection getConnection(String properties_name) throws Exception{

        String driverClass = null;
        String url = null;
        String user = null;
        String password = null;

        // 读取路径下的properties文件
        InputStream in = JDBCTools.class.getClassLoader().getResourceAsStream(properties_name);

        // 将属性文件的值赋值给变量
        Properties properties = new Properties();
        properties.load(in);
        driverClass = properties.getProperty("driver");
        url = properties.getProperty("url");
        user = properties.getProperty("user");
        password = properties.getProperty("password");

        // 构建数据库属性
        Properties info = new Properties();
        info.put("user", user);
        info.put("password", password);
        info.put("useSSL", "true");

        Class.forName(driverClass);

        Connection connection = DriverManager.getConnection(url,info);

        return  connection;
    }

• 更新数据库操作(插入， 删除， 更新)
  通过Statement对象的executeUpdate方法执行更新操作语句

    // 更新数据库统一操作(插入，更新， 删除)
    public static  void update(String sql, String properties_name) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConnection(properties_name); //建立连接
            statement = connection.createStatement();
            statement.executeUpdate(sql); // sql为SQL语句
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            release(statement, connection);
        }
    }
	
	// 统一资源释放静态方法
    public static void release(Statement statement, Connection connection){
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

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

• 查询数据库操作:
  通过Statement对象的executeQuery方法执行查询操作语句

        System.out.println("创建Statement...");
        Statement stmt = null;
        ResultSet result = null;

        // MySQL 查询语句
        String command = "SELECT * FROM student";
        
        try {
            stmt = connection.createStatement();
            result = stmt.executeQuery(command);

            // 5. 从ResultSet中提取结果
            System.out.println("no\t" + "name\t"+"sex\t"+"birthday\t"+"class");
            while (result.next()){
                int id = result.getInt("no");
                String name = result.getString("name");
                String sex = result.getString("sex");
                Date birthday = result.getDate("birthday");
                String classes = result.getString("class");

                // 显示结果
                System.out.print(id+"\t");
                System.out.print(name+"\t");
                System.out.print(sex+"\t");
                System.out.print(birthday+"\t");
                System.out.println(classes);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
			// 6.清理环境资源
            if (result != null){
                try {
                    result.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

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

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