JDBC實現數據庫增刪改查功能

Posted on by

JDBC，簡單點來說，就是用Java操作數據庫，下面簡單介紹怎麼實現數據庫的增刪改查功能。

1、添加數據

package cn.itcast.jdbc;

import java.sql.*;

public class JdbcDemo2 {

    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            //1、註冊驅動
            Class.forName("com.mysql.jdbc.Driver");
            //2、定義sql
            String sql = "insert into course values(?,?,?)";
            //3、獲取Connection對象
            //student表示你要操作的數據庫
            //如果是locakhost：3306，也可以簡寫為"jdbc:mysql:///student"
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");
            //4、獲取執行sql的對象
            preparedStatement = connection.prepareStatement(sql);
            //傳入參數
            preparedStatement.setInt(1,5);
            preparedStatement.setString(2,"JavaWeb");
            preparedStatement.setInt(3,88);
            //5、執行sql
            int count = preparedStatement.executeUpdate();
            //6、處理結果
            System.out.println(count);
            if (count > 0) {
                System.out.println("添加成功");
            } else {
                System.out.println("添加失敗");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7、釋放資源
            //避免空指針異常
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

2、刪除數據

package cn.itcast.jdbc;

import java.sql.*;

public class JdbcDemo4 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1、註冊驅動
            Class.forName("com.mysql.jdbc.Driver");

            //2、獲取連接對象
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");

            //3、定義sql
            String sql = "delete from course where cno = ?";

            //4、獲取執行sql對象
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,5);

            //5、執行sql
            int count = preparedStatement.executeUpdate();

            //6、處理結果
            System.out.println(count);
            if (count > 0) {
                System.out.println("刪除成功");
            } else {
                System.out.println("刪除失敗");
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7、釋放資源
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

3、修改數據

package cn.itcast.jdbc;

import java.sql.*;

public class JdbcDemo3 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1、註冊驅動
            Class.forName("com.mysql.jdbc.Driver");

            //2、獲取連接對象
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student", "root", "root");

            //3、定義sql
            String sql = "update course set period = ? where cno = ?";

            //4、獲取執行sql對象
            preparedStatement = connection.prepareStatement(sql);
            //設置參數
            preparedStatement.setInt(1,90);
            preparedStatement.setInt(2,1);


            //5、執行sql
            int count = preparedStatement.executeUpdate();

            //6、處理結果
            System.out.println(count);
            if (count > 0) {
                System.out.println("修改成功！");
            } else {
                System.out.println("修改失敗！");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7、釋放資源
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

4、查詢數據

package cn.itcast.jdbc;

import cn.itcast.domain.Course;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCDemo5 {

    /**
     * 查詢所有Course對象
     * @return
     */
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Course> list = null;
        try {
            //1、註冊驅動
            Class.forName("com.mysql.jdbc.Driver");
            //2、獲取連接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student", "root", "root");

            //3、定義sql
            String sql = "select * from course";
            //4、獲取執行sql的對象
            preparedStatement = connection.prepareStatement(sql);
            //5、執行sql
            resultSet = preparedStatement.executeQuery();
            //6、遍歷結果集，封裝對象，裝載集合
            Course course = null;
            list = new ArrayList<Course>();
            while (resultSet.next()) {
                //獲取數據
                int cno = resultSet.getInt("cno");
                String cname = resultSet.getString("cname");
                int period = resultSet.getInt("period");
                //創建Course對象並賦值
                course = new Course();
                course.setCno(cno);
                course.setCname(cname);
                course.setPeriod(period);
                //裝載集合
                list.add(course);

            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } 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();
                }
            }
        }
        System.out.println(list);
    }

}

我們可以發現，增刪改的操作基本都是差不多的語句，且執行sql的語句都是一樣的，都是preparedStatement.executeUpdate()。但查詢操作就有所不同瞭，返回的是一個結果集，且執行sql的語句就是preparedStatement.executeQuery()。

以上就是本文的全部內容，希望對大傢的學習有所幫助，也希望大傢多多支持WalkonNet。

推薦閱讀：