JDBC编程实例（实现增删改查）

JDBC编程实例

实例

数据库中初始的表格

1.把getConnection写成一个方法，完成加载驱动和获取数据库连接，后续直接调用

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

//把驱动加载和数据库连接直接写成一个方法，后续一直调用
public class GetConnection {
    public static final String URL = "jdbc:mysql://localhost:3306/jdbc_test?characterEncoding=UTF-8";
    public static final String USER = "root";
    public static final String PWD = "root";

    public static Connection getConnection(){
        Connection connection = null;
        try{
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");

            //获取连接
            connection = DriverManager.getConnection(URL,USER,PWD);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

}

2.实例

import com.sun.deploy.xml.GeneralEntity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class JDBCDemo {
    public static Connection connection = null;
    public static PreparedStatement preparedStatement = null;
    public static ResultSet resultSet = null;

    public static void main(String[] args) {
        //可执行所有sql语句
        execute();
        //执行增删改语句
        //executeUpdate();
        //执行查询语句，并且显示结果
        //executeQuery();

    }

    //增删改数据
    public static void executeUpdate() {
        //在sql中，后面要用preparedStatement.set  ()设置的直接用?号占位
        String sql = "INSERT INTO student (id,`name`,password,grade) VALUES (?,?,?,?)";
        try {
            connection = GetConnection.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 2);
            preparedStatement.setString(2, "Rose");
            preparedStatement.setString(3, "12345678");
            preparedStatement.setString(4, "2019");
            int count = preparedStatement.executeUpdate();
            System.out.println("插入" + count + "条数据");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
//            关闭释放资源（从里到外，从后到前）
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //查询数据
    public static void executeQuery(){
            String sql = "select id,`name`,password,grade from student where grade=?";
            try {
                connection = GetConnection.getConnection();
                preparedStatement = connection.prepareStatement(sql);
                //注意：注意数据类型，string要加引号
                preparedStatement.setString(1, "2019");
                resultSet = preparedStatement.executeQuery();
                List<Student> studentList = new ArrayList<>();
                while (resultSet.next()) {
                    Student student = new Student();
                    student.setId(resultSet.getInt("id"));
                    student.setName(resultSet.getString("name"));
                    student.setPassword(resultSet.getString("password"));
                    student.setGrade(resultSet.getString("grade"));
                    System.out.println(student.getName());
                    studentList.add(student);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (preparedStatement != null) {
                    try {
                        preparedStatement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
    }

    //使用execute()执行增删改查
    public static void execute() {
        //查询
        String sql = "SELECT id,`name`,grade FROM student WHERE id=?";

        //更新
        //String sql = "UPDATE student SET grade=? WHERE name=?";
        try {
            connection = GetConnection.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //查询
            preparedStatement.setInt(1,2);

            //更新
            //preparedStatement.setString(1, "2020");
            //preparedStatement.setString(2, "Rose");
            boolean resultSet1 = preparedStatement.execute();
            if (resultSet1) {
                resultSet = preparedStatement.getResultSet();
                List<Student> studentList = new ArrayList<>();
                while (resultSet.next()) {
                    Student student = new Student();
                    student.setId(resultSet.getInt("id"));
                    student.setName(resultSet.getString("name"));
//                    student.setPassword(resultSet.getString("password"));
                    student.setGrade(resultSet.getString("grade"));
                    System.out.println(student.getName());
                    studentList.add(student);
                }
            } else {
                int count = preparedStatement.executeUpdate();
                System.out.println("插入" + count + "条数据");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

错误总结

错误：没有配置database

配置database请移步到我的另一篇博客：
idea配置database及过程中出现的错误解决