JDBC：增删改查

心已赠人 2024-03-22 14:52 133阅读 0赞

#### 增 ####

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * account表 添加一条记录 insert 语句
     */
    public class JDBCDemo {
        
        public static void main(String[] args) {
        
            Statement stmt = null;
            Connection conn = null;
            try {
        
                //1. 注册驱动
                Class.forName("com.mysql.jdbc.Driver");
                //2. 定义sql
                String sql = "insert into account values(null,'王五',3000)";
                //3.获取Connection对象
                conn = DriverManager.getConnection("jdbc:mysql:///db", "root", "root");
                //4.获取执行sql的对象 Statement
                stmt = conn.createStatement();
                //5.执行sql
                int count = stmt.executeUpdate(sql);//影响的行数
                //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 {
        
                //stmt.close();
                //7. 释放资源
                //避免空指针异常
                if(stmt != null){
        
                    try {
        
                        stmt.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
    
                if(conn != null){
        
                    try {
        
                        conn.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
            }
        }
    }

#### 改 ####

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * account表 修改记录
     */
    public class JDBCDemo {
        
        public static void main(String[] args) {
        
            Connection conn = null;
            Statement stmt = null;
            try {
        
                //1. 注册驱动
                Class.forName("com.mysql.jdbc.Driver");
                //2.获取连接对象
                conn = DriverManager.getConnection("jdbc:mysql:///db", "root", "root");
                //3.定义sql
                String sql  = "update account set balance = 1500 where id = 3";
                //4.获取执行sql对象
                stmt = conn.createStatement();
                //5.执行sql
                int count = stmt.executeUpdate(sql);
                //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(stmt != null){
        
                    try {
        
                        stmt.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
    
                if(conn != null){
        
                    try {
        
                        conn.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
            }
    
    
        }
    }

#### 删 ####

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * account表 删除一条记录
     */
    public class JDBCDemo {
        
        public static void main(String[] args) {
        
            Connection conn = null;
            Statement stmt = null;
            try {
        
                //1. 注册驱动
                Class.forName("com.mysql.jdbc.Driver");
                //2.获取连接对象
                conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root");
               //conn = JDBCUtils.getConnection("jdbc:mysql:///db3", "root", "root");
                //3.定义sql
                String sql  = "delete from account where id = 3";
                //4.获取执行sql对象
                stmt = conn.createStatement();
                //5.执行sql
                int count = stmt.executeUpdate(sql);
                //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(stmt != null){
        
                    try {
        
                        stmt.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
    
                if(conn != null){
        
                    try {
        
                        conn.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
            }
        }
    }

#### 查 ####

import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * 查询所有emp对象
     */
    public class JDBCDemo {
        
    
        public static void main(String[] args) {
        
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            List<Emp> list = null;
            try {
        
                //1.注册驱动
                Class.forName("com.mysql.jdbc.Driver");
                //2.获取连接
                conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root");
                //3.定义sql
                String sql = "select * from emp";
                //4.获取执行sql的对象
                stmt = conn.createStatement();
                //5.执行sql
                rs = stmt.executeQuery(sql);
                //6.遍历结果集，封装对象，装载集合
                Emp emp = null;
                list = new ArrayList<Emp>();
                while(rs.next()){
        
                    //获取数据
                    int id = rs.getInt("id");
                    String ename = rs.getString("ename");
                    int job_id = rs.getInt("job_id");
                    int mgr = rs.getInt("mgr");
                    Date joindate = rs.getDate("joindate");
                    double salary = rs.getDouble("salary");
                    double bonus = rs.getDouble("bonus");
                    int dept_id = rs.getInt("dept_id");
                    // 创建emp对象,并赋值
                    emp = new Emp();
                    emp.setId(id);
                    emp.setEname(ename);
                    emp.setJob_id(job_id);
                    emp.setMgr(mgr);
                    emp.setJoindate(joindate);
                    emp.setSalary(salary);
                    emp.setBonus(bonus);
                    emp.setDept_id(dept_id);
    
                    //装载集合
                    list.add(emp);
                }
    
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            } catch (SQLException e) {
        
                e.printStackTrace();
            }finally {
        
                if(rs != null){
        
                    try {
        
                        rs.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
    
                if(stmt != null){
        
                    try {
        
                        stmt.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
    
                if(conn != null){
        
                    try {
        
                        conn.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
            }
            return list;
            System.out.println(list);
            System.out.println(list.size());
        }
    }

#### 创建表 ####

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * 执行DDL语句
     */
    public class JDBCDemo {
        
        public static void main(String[] args) {
        
            Connection conn = null;
            Statement stmt = null;
            try {
        
                //1. 注册驱动
                Class.forName("com.mysql.jdbc.Driver");
                //2.获取连接对象
                conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root");
                //3.定义sql
                String sql  = "create table student (id int , name varchar(20))";
                //4.获取执行sql对象
                stmt = conn.createStatement();
                //5.执行sql
                int count = stmt.executeUpdate(sql);
                //6.处理结果
                System.out.println(count);
    
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
                //7.释放资源
    
                if(stmt != null){
        
                    try {
        
                        stmt.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
    
                if(conn != null){
        
                    try {
        
                        conn.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
            }
        }
    
    }