JDBC的链接，及数据库增、删、改、查

一、JDBC链接8步骤（1+7）

1、jar包的添加：

1创建lib文件夹 (右键JDBC_test)

粘贴jar包到lib文件夹

2、右键jar包 Add

2、JDBC链接的七步骤：

注：JDBC连接Mysql6以上时，加载驱动要改成com.mysql.cj.jdbc.Driver

 PreparedStatement statement=null;

    ResultSet rst=null;

    Connection con=null;

//1.加载驱动

   Class.forName("com.mysql.jdbc.Driver");

 //2.创建连接

con=DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/UserTable?&useSSL=false&serverTimezone=UTC","root","123456");

System.out.println("创建成功");

 //3.写sql

 String sql="select * from usertable";

 //4.得到statement对象

statement=con.prepareStatement(sql);

 //5.执行sql  得到结果集

 rst=statement.executeQuery();

 //6.处理结果集

 while(rst.next()){
     System.out.println("id 
"+rst.getInt(1)+"  name 
"+rst.getString(2)+"  password 
"+rst.getString(3));
 }
 
//7.关闭链接

statement.close();
rst.close();
con.close();

二、数据库增、删、改、查

数据库操作包含了三个类：Util类，Service类和Main类

**

1、Util类：

**用来封装数据库的链接和关闭

package whut;
import java.sql.*;

public class Util {
    public static Connection connect(){
        Connection conn=null;
        try {
            //1、装载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2、创建连接
            conn= DriverManager.getConnection( "jdbc:mysql://127.0.0.1:3306/test?&useSSL=false&serverTimezone=UTC","root","123456");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
public static void close(Statement statement, ResultSet rst,Connection con){
    if (statement!=null) {
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (rst!=null) {
        try {
            rst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (con!=null) {
        try {
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
}
return;
}
}

2、Service类

用来封装对数据库的操作

package whut;
import java.sql.*;

public class Service {
    public static void insert(int id,String name,String password){
        PreparedStatement statement=null;
        //1、2连接
        Connection conn=Util.connect();
        //3.写sql
        String sql="INSERT INTO  Userinfo(id,name,password)  VALUES (?,?,?)";
        try {
            //4.得到statement对象
            statement=conn.prepareStatement(sql);
            statement.setInt(1,id);
            statement.setString(2,name);

            statement.setString(3,password);
            //5.执行sql  得到结果集
            statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
      finally {
            Util.close(statement,null,conn);
            return;
        }
    }
    public static void delete(String name){
        //1、2连接
        Connection conn=Util.connect();
        PreparedStatement statement=null;
        //3.写sql
        String sql="DELETE FROM userinfo WHERE name=?";
        try {
            //4.得到statement对象
            statement=conn.prepareStatement(sql);
            statement.setString(1,name);
            //5.执行sql  得到结果集
            statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();

        }
        finally {
            Util.close(statement,null,conn);
            return;
        }


    };
    public static void update(String name,String newpass){
        //1、2连接
        Connection conn=Util.connect();
        PreparedStatement statement=null;
        //3.写sql
        String sql="UPDATE userinfo SET password = ? WHERE name = ?";
        try {
            //4.得到statement对象
            statement=conn.prepareStatement(sql);
            statement.setString(1,newpass);
            statement.setString(2,name);
            //5.执行sql  得到结果集
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            Util.close(statement,null,conn);
            return;
        }
    };
    public static ResultSet select(String name){
            ResultSet r=null;
        Connection conn=Util.connect();
        PreparedStatement statement=null;
        //3.写sql
        String sql="select * from userinfo WHERE name = ?";
        try {
            //4.得到statement对象
            statement=conn.prepareStatement(sql);

            statement.setString(1,name);
            //5.执行sql  得到结果集
          r=statement.executeQuery();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
}}

2、Main类

主函数，程序入口

package whut;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class Main {
    public static void main(String[] args) throws SQLException {
        Scanner sc=new Scanner(System.in);
        System.out.println("请输入选择：1、增\t2、删\t3、改\t4、查\t0、退出");
       int choose=sc.nextInt();
       while(choose!=0){
           if(choose==1)
           {
               System.out.println("请输入编号、姓名和密码：");
               Service.insert(sc.nextInt(),sc.next(),sc.next());
           }
           if(choose==2)
           {
               System.out.println("请输入姓名");
               Service.delete(sc.next());
           }
           if(choose==3)
           {
               System.out.println("请输入姓名和新密码：");
               Service.update(sc.next(),sc.next());
           }
           if(choose==4)
           {
               System.out.println("请输入姓名");
               ResultSet r=Service.select(sc.next());
               boolean b=r.next();
               if(!b)
               {
                   System.out.println("查无此人");

               }
               while (b){
                   System.out.println("id: "+r.getInt(1)+" name:  "+r.getString(2)+"  password:  "+r.getString(3));
                   b=r.next();
               }
           }
           System.out.println("请输入选择：1、增 \t 2、删 \t 3、改\t  4、查 \t 0、退出");
           choose=sc.nextInt();
       }
       return;
           }
}