JDBC

JDBC：java database connection java数据库连接技术
1.添加操作

public class Add1Test {
    public static void main(String[] args) {
       try{
           //第一步：加载驱动
           Class.forName("com.mysql.jdbc.Driver");//mysql8+ : Class.forName("com.mysql.cj.jdbc.Driver");
           String url ="jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useSSL=false";      //jdbc:mysql://本机：端口号/数据库名？  mysql5 后面不需要这句话?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
           String user = "root";//用户名
           String password = "root";//密码
           //第二步：建立连接
           Connection conn = DriverManager.getConnection(url,user,password);
           String sql = "INSERT INTO userinfo VALUES(83,'java','女',21,'北京',MD5('123'),1)";//java只负责将此语句送出去
           //第三步：产生传递SQL命令的邮差对象
           PreparedStatement ps = conn.prepareStatement(sql);
           //第四步：将SQL命令交由MySQL执行
           int result = ps.executeUpdate();//执行更新操作，返回受影响的行数
           System.out.println("有"+result+"行受到影响");
           //第五步：关闭数据库相应对象
           ps.close();
           conn.close();
       }catch(ClassNotFoundException e){//驱动包出错
           System.out.println("类没有找到！");
       }catch(SQLException e){//SQL方面出错
           System.out.println(e.getMessage());
       }
    }
}

public class ADD2Test {
    public static void add(String name,String sex,int age,String address){
        try{
            //第一步：加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            String url ="jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useSSL=false";      //jdbc:mysql://本机：端口号/数据库名？  mysql5 后面不需要这句话?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
            String user = "root";//用户名
            String password = "root";//密码
            //第二步：建立连接
            Connection conn = DriverManager.getConnection(url,user,password);
            String sql = "INSERT INTO userinfo VALUES(85,'"+name+"','"+sex+"','"+age+"','"+address+"',MD5('123'),1)";
            //第三步：产生传递SQL命令的邮差对象
            PreparedStatement ps = conn.prepareStatement(sql);
            //第四步：将SQL命令交由MySQL执行
            int result = ps.executeUpdate();
            System.out.println("共"+result+"行受到影响");
            //第五步：关闭数据库相应对象
            ps.close();
            conn.close();
        }catch(ClassNotFoundException e){
            System.out.println("类没有找到!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
    public static void main(String[] args) {
       add("java","男",23,"北京");
    }
}

public class Add3Test {
    public static void add(String name,String sex,int age,String address){
        try{
            //第一步：加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            String url ="jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useSSL=false";      //jdbc:mysql://本机：端口号/数据库名？  mysql5 后面不需要这句话?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
            String user = "root";//用户名
            String password = "root";//密码
            //第二步：建立连接
            Connection conn = DriverManager.getConnection(url,user,password);
            String sql = "INSERT INTO userinfo VALUES(86,?,?,?,?,MD5('123'),1)";
            //第三步：产生传递SQL命令的邮差对象
            PreparedStatement ps = conn.prepareStatement(sql);
            //给SQL中的问号赋值
            ps.setString(1,name);
            ps.setString(2,sex);
            ps.setInt(3,age);
            ps.setString(4,address);
            //第四步：将SQL命令交由MySQL执行
            int result = ps.executeUpdate();
            System.out.println("共"+result+"行受到影响");
            //第五步：关闭数据库相应对象
            ps.close();
            conn.close();
        }catch(ClassNotFoundException e){
            System.out.println("类没有找到!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
    public static void main(String[] args) {
        add("aaa","男",23,"北京");
    }
}

2.删除操作

public class Delete1Test {
    public static void main(String[] args) {
        try{
            //第一步：加载驱动
            Class.forName("com.mysql.jdbc.Driver");//mysql8+ : Class.forName("com.mysql.cj.jdbc.Driver");
            String url ="jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useSSL=false";      //jdbc:mysql://本机：端口号/数据库名？  mysql5 后面不需要这句话?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
            String user = "root";//用户名
            String password = "root";//密码
            //第二步：建立连接
            Connection conn = DriverManager.getConnection(url,user,password);
            String sql = "delete  from userinfo where userid=86";//java只负责将此语句送出去
            //第三步：产生传递SQL命令的邮差对象
            PreparedStatement ps = conn.prepareStatement(sql);
            //第四步：将SQL命令交由MySQL执行
            int result = ps.executeUpdate();//执行更新操作，返回受影响的行数
            System.out.println("有"+result+"行受到影响");
            //第五步：关闭数据库相应对象
            ps.close();
            conn.close();
        }catch(ClassNotFoundException e){//驱动包出错
            System.out.println("类没有找到！");
        }catch(SQLException e){//SQL方面出错
            System.out.println(e.getMessage());
        }
    }
}

public class Delete2Test {
    public static void delete(String name,String sex,int age,String address){
        try{
            //第一步：加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            String url ="jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useSSL=false";      //jdbc:mysql://本机：端口号/数据库名？  mysql5 后面不需要这句话?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
            String user = "root";//用户名
            String password = "root";//密码
            //第二步：建立连接
            Connection conn = DriverManager.getConnection(url,user,password);
            String sql = "delete from userinfo where username='"+name+"'";
            //第三步：产生传递SQL命令的邮差对象
            PreparedStatement ps = conn.prepareStatement(sql);
            //第四步：将SQL命令交由MySQL执行
            int result = ps.executeUpdate();
            System.out.println("共"+result+"行受到影响");
            //第五步：关闭数据库相应对象
            ps.close();
            conn.close();
        }catch(ClassNotFoundException e){
            System.out.println("类没有找到!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
    public static void main(String[] args) {
        delete("java","男",23,"北京");
    }
}

3.查询操作

public class Select1Test {
    public static void main(String[] args) {
        try{
            Class.forName("com.mysql.jdbc.Driver");
            String url ="jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useSSL=false";      //jdbc:mysql://本机：端口号/数据库名？  mysql5 后面不需要这句话?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
            String user = "root";//用户名
            String password = "root";//密码
            Connection conn = DriverManager.getConnection(url,user,password);
            String sql = "select * from userinfo where userid=1";
            PreparedStatement ps = conn.prepareStatement(sql);
            //获取到查询结果
            ResultSet rs = ps.executeQuery();
            if (rs.next()){//是否存在下一行
                //在if中rs就是下一行
                int id = rs.getInt(1);//获取到int类型的第1列
                String name = rs.getString("username");
                String sex = rs.getString(3);
                int age = rs.getInt("userage");
                String address = rs.getString(5);
                System.out.println(id);
                System.out.println(name);
                System.out.println(sex);
            }
            rs.close();
            ps.close();
            conn.close();
        }catch(ClassNotFoundException e){
            System.out.println("类没有找到!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
}

public class Select2Test {
    public static void select(String name,String pass){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            String url ="jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useSSL=false";      //jdbc:mysql://本机：端口号/数据库名？  mysql5 后面不需要这句话?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
            String user = "root";//用户名
            String password = "root";//密码
            Connection conn = DriverManager.getConnection(url,user,password);
            String sql = "select * from userinfo where username=? and userpass=md5(?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,pass);
            //获取到查询结果
            ResultSet rs = ps.executeQuery();
            if(rs.next()){//是否存在下一行
                System.out.println("欢迎你！");
            }else{
                System.out.println("没有查询到此人！");
            }
            rs.close();
            ps.close();
            conn.close();
        }catch(ClassNotFoundException e){
            System.out.println("类没有找到!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
    public static void main(String[] args) {
        System.out.println("请输入姓名和密码：");
        Scanner in =new Scanner(System.in);
        String name = in.next();
        String pass = in.next();
        select(name,pass);
    }
}

public class Select3Test {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useSSL=false";      //jdbc:mysql://本机：端口号/数据库名？  mysql5 后面不需要这句话?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
            String user = "root";//用户名
            String password = "root";//密码
            Connection conn = DriverManager.getConnection(url, user, password);
            String sql = "select * from userinfo";
            PreparedStatement ps = conn.prepareStatement(sql);
            //获取到查询结果
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {//是否存在下一行
                //在if中rs就是下一行
                int id = rs.getInt(1);//获取到int类型的第1列
                String name = rs.getString("username");
                String sex = rs.getString(3);
                int age = rs.getInt("userage");
                String address = rs.getString(5);
                System.out.print(id + "\t" + name + "\t" + sex + "\t" + age + "\t" + address);
                System.out.println();
            }
            rs.close();
            ps.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            System.out.println("类没有找到!");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

修改操作同理