2021-07-30JDBC练习

• 练习：JDBC执行 insert delete update

package com.bipowernode;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Test01 {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;

        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123");
            //3.获取数据库操作对象
            stmt = conn.createStatement();
            //4.执行sql语句
            /*int count  = stmt.executeUpdate("update dept set dname = '销售部',loc = '西安' where deptno = 20;");
            System.out.println(count==1?"修改成功":"修改失败");*/
           /* int count = stmt.executeUpdate("insert into dept(deptno,dname,loc) values (50,'人事部','深圳')");
            System.out.println(count == 1 ? "增加成功":"增加失败");*/
           int count = stmt.executeUpdate("delete from dept where deptno = 50 ");
            System.out.println(count == 1 ? "删除成功":"删除失败");
            //5.查询结果集

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //6.释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

    }
}

• 练习： 用户登录系统

package com.bipowernode;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;

public class Test02 {
    public static void main(String[] args) {
        Map<String,String> userLoginInfo  =initUI();
        boolean loginSuccess = login(userLoginInfo);
        System.out.println(loginSuccess ? "登陆成功":"登录失败");
    }

    /**
     * 用户登录
     * @param userLoginInfo 用户登录信息
     * @return  false表示失败  true表示成功
     */
    private static boolean login(Map<String, String> userLoginInfo) {
        //打标记
        boolean loginSuccess = false;
        //单独定义变量
        String loginNeme = userLoginInfo.get("loginName");
        String loginPwd = userLoginInfo.get("loginNPwd");

        //JDBC代码
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123");
            //3.获取数据库操作对象
            stmt = conn.createStatement();
            //4.执行sql语句
            String sql = "select * from t_user where loginName = '"+loginNeme+ "' and loginPwd = '" + loginPwd+ "'";
           rs = stmt.executeQuery(sql);
            //5.查询结果集
            if (rs.next()){
                loginSuccess = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //6.释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return loginSuccess;
    }


    /**
     * 初始化用户界面
     * @return 用户输入的用户名和密码等信息
     */
    private static Map<String, String> initUI() {
        Scanner s = new Scanner(System.in);

        System.out.print("请输入用户名：");
        String loginName = s.nextLine();

        System.out.print("请输入密码：");
        String loginPwd = s.nextLine();

        Map<String,String> userLoginInfo = new HashMap<>();
        userLoginInfo.put("loginName",loginName);
        userLoginInfo.put("loginNPwd",loginPwd);

        return userLoginInfo;
    }

}

• 练习： 处理查询结果集

package com.bipowernode;

import java.sql.*;

public class Test04 {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.建立连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123");
            //3.获取数据库操作对象
            stmt = conn.createStatement();
            //4.执行sql语句
            rs = stmt.executeQuery("select ename,empno,sal from emp ");
            //5.获取查询结果集
            while (rs.next()){
               /* String empno = rs.getString(1);
                String ename = rs.getString(2);
                String sal = rs.getString(3);
                System.out.println(empno+","+ename+","+sal);*/

                /*String empno = rs.getString("empno");
                String ename = rs.getString("ename");
                String sal = rs.getString("sal");
                System.out.println(empno+","+ename+","+sal);*/

                /*int empno = rs.getInt(2);
                String ename = rs.getString(1);
                double sal = rs.getDouble(3);
                System.out.println(empno+","+ename+","+sal);*/

                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                double sal = rs.getDouble("sal");
                System.out.println(empno+","+ename+","+sal);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {        //6.资源释放
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        
    }
}

• 练习： 使用prepareStatement

package com.bipowernode;
import java.sql.*;
public class Test05 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            //1.创建驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123");
            //3.获取数据库操作对象
            String sql = "select empno,ename,sal from emp where deptno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"10");
            //4.执行sql语句
            rs = ps.executeQuery();
            //5.处理查询结果集
            while (rs.next()){
                int empno = rs.getInt(1);
                String ename = rs.getString(2);
                double sal = rs.getDouble(3);
                System.out.println(empno+","+ename+","+sal);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //6.释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

            }
}

• 练习：降序或者升序

package com.bipowernode;

import java.sql.*;
import java.util.Scanner;

public class Test06 {
    public static void main(String[] args) {
        Scanner s = new Scanner(System.in);
        System.out.print("请输入desc或acs:");
        String KeyWords = s.nextLine();


        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            //1.创建驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123");
            //3.获取数据库操作对象
            String sql = "select ename from emp order by ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,KeyWords);
            //4.执行sql
             rs = ps.executeQuery();

            //5.处理查询结果集
            while (rs.next()){
                System.out.println(rs.getString(1));
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //6.释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


    }
}

• 使用PreparedStatement完成insert、update、delete

package com.bipowernode;

import java.sql.*;

//使用PreparedStatement完成insert、update、delete
public class Test07 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            //1.创建驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123");
            //3.获取预编译数据库对象
//            String sql = "insert into dept(deptno,dname,loc)values(40,'renshibu','xian')";
//            String sql = "update dept set dname = 'caiwubu',loc = 'beijing' where deptno = 40";
            String sql = "delete from dept where deptno = 40";

            ps = conn.prepareStatement(sql);
            //4.执行sql
            int count = ps.executeUpdate();
//            System.out.println(count == 1 ? "添加成功":"添加失败");
//            System.out.println(count == 1 ? "修改成功":"修改失败");
            System.out.println(count == 1 ? "删除成功":"删除失败");
            //5.处理查询结果集

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //6.释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}