乘风原创程序

  • JDBC实现数据库增删改查功能
  • 2021/7/4 12:49:48
  • 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()。