运用代码对数据库进行增删改查

佚名 6年前 (2019-04-10) 随笔 1162人围观抢沙发百度已收录

package com.bdqn;

import java.sql.*;

public class Test {
    Connection con = null;
    PreparedStatement ps = null;
    int result = 0;
    ResultSet rs = null;

//    查询数据
    public void select(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1/studentbd22","root","dmgyzchlry");
            String sql = "select * from student";
            ps = con.prepareStatement(sql);
            rs =ps.executeQuery();
            while (rs.next()){
                int id = rs.getInt(1);
                String name = rs.getString(2);
                System.out.println(id+"  "+name);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(rs!=null)
                    rs.close();
                if(ps!=null)
                    ps.close();
                if(con!=null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //    增加数据
    public void add(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1/studentbd22", "root", "dmgyzchlry");
            String sql = "insert into student values (?,?,?,?)";
            ps = con.prepareStatement(sql);
            ps.setInt(1, 104);
            ps.setString(2, "李四");
            ps.setString(3, "男");
            ps.setInt(4, 95033);
            result = ps.executeUpdate();
            System.out.println(result > 0 ? "添加成功" : "添加失败");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null)
                    ps.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //    修改数据
    public void update(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1/studentbd22", "root", "dmgyzchlry");
            String sql = "update student set  sno = ? where sno = ?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, 106);
            ps.setInt(2, 110);
            result = ps.executeUpdate();
            System.out.println(result > 0 ? "修改成功" : "修改失败");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null)
                    ps.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //    修改数据
    public void del(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1/studentbd22", "root", "dmgyzchlry");
            String sql = "delete from student where sno = ?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, 106);
            result = ps.executeUpdate();
            System.out.println(result > 0 ? "删除成功" : "删除失败");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null)
                    ps.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        Test t = new Test();
        t.select();
        t.add();
        t.update();
        t.del();
    }

}