利用JDBC进行简单的增删改查（Java）

利用JDBC进行简单的增删改查（Java）

先写一个用来连接和关闭的工具类DbUtil

package work2;

import java.sql.*;

public class DbUtil {

    public static final String URL = "jdbc:mysql://localhost:3306/work";
    public static final String USER = "root";
    public static final String PASSWORD = "123456";

    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获得数据库连接
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        return conn;
    }
    public static void closeall(ResultSet resultSet,PreparedStatement preparedStatement,Connection connection) throws SQLException {
        if (resultSet!=null){
            resultSet.close();
        }
        if (preparedStatement!=null){
            preparedStatement.close();
        }
        if (connection!=null){
            connection.close();
        }
    }
}

在建一个Dao类里面写一些简单的增删改查的方法，delete()、select()、insert()、update()。在最后实例化一个该类并调用里面的方法进行简单的使用。

package work2;

import java.sql.*;

public class Dao {
    Connection connection= null;
    PreparedStatement pstmt=null;
    ResultSet rs=null;
    public void delete() {
        try {
            connection = DbUtil.getConnection();
            String sql="DELETE FROM User where username='ykm'";
            pstmt = connection.prepareStatement(sql);
            pstmt.executeUpdate(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DbUtil.closeall(null,pstmt,connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public void select() {
        try {
            connection = DbUtil.getConnection();
            String sql="SELECT * FROM User";
            pstmt = connection.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()){
                System.out.println( "用户名："+rs.getNString("username")+" 密码："+rs.getNString("password"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DbUtil.closeall(rs,null,connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    public void insert(){
        try {
            connection = DbUtil.getConnection();
            String name= String.valueOf(System.currentTimeMillis());
            String sql="INSERT INTO User (username,password) VALUES ("+name+",'123456')";
            pstmt = connection.prepareStatement(sql);
            pstmt.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DbUtil.closeall(null,pstmt,connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public void update(){
        try {
            connection = DbUtil.getConnection();
            String password= String.valueOf(System.currentTimeMillis());
            String sql="UPDATE User SET password ="+password+" WHERE password = '123456'";
            pstmt = connection.prepareStatement(sql);
            pstmt.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DbUtil.closeall(null,pstmt,connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        Dao dao=new Dao();
        System.out.println("原本的：");
        dao.select();
        System.out.println("插入一行：");
        dao.insert();
        dao.select();
        System.out.println("修改密码为123456的一行：");
        dao.update();
        dao.select();
        System.out.println("删除名字为ykm的一行：");
        dao.delete();
        dao.select();

    }

}

最后是调用方法之后的结果和原来的对比输出。