中软培训 day04 idea的安装及jdbc的两个写法

最新推荐文章于 2021-11-13 23:42:14 发布

原创最新推荐文章于 2021-11-13 23:42:14 发布 · 165 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#idea #jdbc

本文详细介绍了使用JDBC进行数据库操作的方法，包括加载驱动、创建连接、执行SQL语句及处理结果集等步骤。提供了两种实现方式，一种是通过配置文件和公共类，另一种是直接在代码中进行操作。

中软培训 day4

jdbc:

1、加载驱动

2、创建连接

3、写sql语句

4、得到statement对象

5、执行sql语句

6、处理结果集

7、关闭资源

方法一：

1、写配置文件

#数据库驱动
driver=com.mysql.jdbc.Driver
#连接数据库的URL
url=jdbc:mysql://127.0.0.1:3306/nbufe?useSSL=true&characterEncoding=utf-8&user=root&password=***
#用户名
username=root
#密码
password=***

2、写公共类

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtil {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    static{
        try {
            //读取配置文件
            InputStream in = DBUtil.class.getResourceAsStream("db.properties");
            Properties properties = new Properties();
            //加载配置文件
            properties.load(in);
            //获取配置文件中的数据
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("username");
            password = properties.getProperty("password");
            //加载数据库链接驱动
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取一个数据库链接
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }
}

3、写增删查改

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

public class Test1 {
    public static void main(String[] args) throws SQLException {


    }

    public static void add() throws SQLException {
        String sql = "INSERT INTO tb_user (username,password) VALUES('yjy','123')";
        Connection conn = DBUtil.getConnection();
        conn.setAutoCommit(false);
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.executeUpdate();
        conn.commit();
        conn.close();
    }

    public static void delete() throws SQLException {
        String sql = "DELETE FROM tb_user WHERE tb_user.username = 'yjy'";
        Connection conn = DBUtil.getConnection();
        conn.setAutoCommit(false);
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.executeUpdate();
        conn.commit();
        conn.close();
    }


    public static void updata() throws SQLException {
        String sql = "UPDATE tb_user SET tb_user.username = 'yao'";
        Connection conn = DBUtil.getConnection();
        conn.setAutoCommit(false);
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.executeUpdate();
        conn.commit();
        conn.close();
    }

    public static void query() throws SQLException{
        String sql = "SELECT * FROM tb_user";
        Connection conn = DBUtil.getConnection();
        conn.setAutoCommit(false);
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        //执行查询语句并返回结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            System.out.println(username + " " + password);
        }
        conn.commit();
        conn.close();
    }
}

方法二

import java.sql.*;

import static java.lang.Class.forName;

public class Test {
    public static void main(String[] args) {
    update();
    }

    public static void add(){
        ResultSet resultSet=null;
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/nbufe?useSSL=true&characterEncoding=utf-8&user=root&password=yaojieyu1999");
            System.out.println("创建数据库成功1");
            String sql = "insert into tb_user (username,password) value('yjy','yjy123')";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                connection.close();
                preparedStatement.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    public static void query(){
        ResultSet resultSet=null;
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try{
            //1、加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2、创建连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/nbufe?useSSL=true&characterEncoding=utf-8&user=root&password=yaojieyu1999");
            System.out.println("创建数据库成功2");
            //3、写sql
            String sql="select * from tb_user";
            //4、得到statement对象
            preparedStatement = connection.prepareStatement(sql);
            //5、执行sql
            resultSet=preparedStatement.executeQuery();
            //6、处理结果集
            while (resultSet.next()){
                System.out.println(resultSet.getInt("id"));
                System.out.println(resultSet.getString("username"));
                System.out.println(resultSet.getString("password"));
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //7.关闭资源
            try{
                connection.close();
                preparedStatement.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
    public static void delete(){
        ResultSet resultSet=null;
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try{
            //1、加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2、创建连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/nbufe?useSSL=true&characterEncoding=utf-8&user=root&password=yaojieyu1999");
            System.out.println("创建数据库成功3");
            //3、写sql
            String sql="delete from tb_user where id=1";
            //4、得到statement对象
            preparedStatement = connection.prepareStatement(sql);
            //5、执行sql
            preparedStatement.executeUpdate();
            //6、处理结果集


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //7.关闭资源
            try{
                connection.close();
                preparedStatement.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    public static void update(){
        ResultSet resultSet=null;
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try{
            //1、加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2、创建连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/nbufe?useSSL=true&characterEncoding=utf-8&user=root&password=yaojieyu1999");
            System.out.println("创建数据库成功1");
            //3、写sql
            String sql="update tb_user set username = 'xiaoyao' where id=7";
            //4、得到statement对象
            preparedStatement = connection.prepareStatement(sql);
            //5、执行sql
            preparedStatement.executeUpdate();
            //6、处理结果集


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //7.关闭资源
            try{
                connection.close();
                preparedStatement.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }


}