JDBC的增删查改

jar包的导入

    1.下载mysql-connector-java-5.0.8-bin.jar包
         http://www.java2s.com/Code/Jar/m/Downloadmysqlconnectorjava508bingjar.htm
    2.在项目下创建名为lib的目录
    3.将下载的jar包加入目录文件下
    4.设置jar包的add aslibrary

1.数据库表格创建

    create table_user(
       id int primary key,
       username varchar(255),
       password varchar(255)
       )

2.增加记录

import java.sql.*;
public class Test2 {
        public static void main(String[] args)  {
            Connection conn=null;
            PreparedStatement pstmt=null;
            ResultSet rs=null;
            try {
                //1.加载驱动
                Class.forName("com.mysql.jdbc.Driver");
                //2.创建连接
                conn = DriverManager.getConnection("jdbc:mysql://localhost/ningda?", "root", "root");
                System.out.println("创建连接成功");
                //3.sql语句
                String sql="insert into tb_user value(2,'asd',456) ";
                //4.得到statement对象
                pstmt = conn.prepareStatement(sql);
                //5.执行sql
                int count = pstmt.executeUpdate();
                //6.处理结果集
                //7.关闭资源
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            finally {
               if (conn!=null)
               {
                   try {
                       conn.close();
                   } catch (SQLException e) {
                       e.printStackTrace();
                   }
               }
                if (rs!=null)
                {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (pstmt!=null)
                {
                    try {
                        pstmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

3.查找所有记录

import java.sql.*;
public class Test {
    public static void main(String[] args) throws Exception {
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost/ningda?", "root", "root");
            System.out.println("创建连接成功");
            //3.sql语句
            String sql="select * from tb_user";
            //4.得到statement对象
            pstmt = conn.prepareStatement(sql);
            //5.执行sql
            rs = pstmt.executeQuery();
            //6.处理结果集
            while(rs.next())
            {
                System.out.println( rs.getInt(1));
                System.out.println(rs.getString("username"));
                System.out.println(rs.getString("password"));
            }
            //7.关闭资源
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
          finally {
            conn.close();
            pstmt.close();
            rs.close();
        }
    }
}

4. 修改记录

import java.sql.*;
public class Test3 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost/ningda?", "root", "root");
            System.out.println("创建连接成功");
            //3.sql语句
            String sql = "update tb_user set password='789' where id=2";
            //4.得到statement对象
            pstmt = conn.prepareStatement(sql);
            //5.执行sql
            int count = pstmt.executeUpdate();
            //6.处理结果集
            //7.关闭资源
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

5. 删除记录

import java.sql.*;

/**
 * Created by 13781 on 2019/8/8.
 */
public class Test4 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost/ningda?", "root", "root");
            System.out.println("创建连接成功");
            //3.sql语句
            String sql = "delete from tb_user where id=2";
            //4.得到statement对象
            pstmt = conn.prepareStatement(sql);
            //5.执行sql
            int count = pstmt.executeUpdate();
            //6.处理结果集
            //7.关闭资源
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}