jdbc连接及封装

jdbc连接

   //1.查询所有
     public static void main(String[] args) {
        //1. 导入数据库对应的jar包
        //String url = "jdbc:mysql:///test?userSSL=false";//当前主机简写
        //String url = "jdbc:mysql://127.0.0.1/test?userSSL=false";
        String url = "jdbc:mysql://localhost:3306/test?userSSL=false";";
        String username = "root";
        String password = "1234";
        try {
            //2. 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3. 获取数据库连接对象
            Connection conn = DriverManager.getConnection(url, username, password);
            //4. 定义sql语句
            String sql = "select * from qh_student";
            //5. 获取执行sql的对象 Statement
            Statement stmt = conn.createStatement();
            //6. 执行sq
            ResultSet rs = stmt.executeQuery(sql);
            //7. 对结果进行处理
            while (rs.next()){
                String id = rs.getString("sid");
                String name = rs.getString("username");
                String pwd = rs.getString("password");
                System.out.println(id+"-"+name+"-"+pwd);
            }
            //8. 释放资源
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            System.out.println("数据库连接失败!");
            e.printStackTrace();
        }
    }
//2.添加
    public static void main(String[] args) {
        String url = "jdbc:mysql:///test?userSSL=false";
        String username = "root";
        String password = "1234";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");;
            Connection conn = DriverManager.getConnection(url, username, password);
            String sql = "insert into qh_student(username,password)" +
                    "values ('朱八','zb')";
            Statement stmt = conn.createStatement();
            int count = stmt.executeUpdate(sql);
            System.out.println("影响的行数= " + count);
            stmt.close();
            conn.close();
        } catch (Exception e) {
            System.out.println("数据库连接失败!");
            e.printStackTrace();
        }
    }
//3/删除
    public static void main(String[] args) {
        String url = "jdbc:mysql:///test?userSSL=false";
        String username = "root";
        String password = "1234";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, username, password);
            String sql = "delete from qh_student where sid='6'";
            Statement stmt = conn.createStatement();
            int count = stmt.executeUpdate(sql);
            System.out.println("影响的行数= " + count);
            stmt.close();
            conn.close();
        } catch (Exception e) {
            System.out.println("数据库连接失败!");
            e.printStackTrace();
        }
    }

jdbc连接封装

   //1.JdbcUtils : 
    public class JdbcUtils {
        private static final String DRIVERCLASSNAME = "com.mysql.cj.jdbc.Driver";
        private static final String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        private static final String USERNAME = "root";
        private static final String PASSWORD = "1234";
        private JdbcUtils() {
        }
        //获取连接对象
        public static Connection getConnnection() {
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                Connection connection = DriverManager.getConnection(
                        URL,
                        USERNAME,
                        PASSWORD
                );
                return connection;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return  null ;
        }
        //释放资源
        //如果是针对ddl语句或者dml语句,Statement对象以及Connection对象关闭
        public static void close(Statement stmt, Connection conn) {
            close(null, stmt, conn);
        }
        //如果是针对DQL语句,ResultSet对象以及Statement以及Connection对象关闭
        public static void close(ResultSet rs, Statement stmt, Connection conn) {
            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();
                }
            }
        }
    }
//2.JdbcUtilsProperties
jdbc.properties : 
    driverClassName=com.mysql.cj.jdbc.Driver
    url=jdbc:mysql://localhost:3306/数据库?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
    username=root
    password=123456
JdbcUtilsProperties : 
    public class JdbcUtilsProperties {
        //jdbc.properties路径
        private static final String PROPERTIESPATH="com\\qh\\jdbc\\jdbc.properties";
        private static String DRIVERCLASSNAME = null;
        private static String URL = null;
        private static String USERNAME = null;
        private static String PASSWORD = null;
        //静态代码块
        static {
            try {
                Properties prop = new Properties();
                InputStream inputStream = JdbcUtilsProperties.class.getClassLoader().getResourceAsStream(PROPERTIESPATH);
                prop.load(inputStream);
                DRIVERCLASSNAME = prop.getProperty("driverClassName"); //获取驱动类
                URL = prop.getProperty("url");  //获取url
                USERNAME = prop.getProperty("username"); //用户名username
                PASSWORD = prop.getProperty("password");//密码password
                //注册驱动
                Class.forName(DRIVERCLASSNAME);
            } catch (Exception e) {
                e.printStackTrace();
            } 
        }
        private JdbcUtilsProperties() {
        }
        //获取连接对象
        public static Connection getConnnection() {
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                Connection connection = DriverManager.getConnection(
                        URL,
                        USERNAME,
                        PASSWORD
                );
                return connection;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return  null ;
        }
        //释放资源
        //如果是针对ddl语句或者dml语句,Statement对象以及Connection对象关闭
        public static void close(Statement stmt, Connection conn) {
            close(null, stmt, conn);
        }
        //如果是针对DQL语句,ResultSet对象以及Statement以及Connection对象关闭
        public static void close(ResultSet rs, Statement stmt, Connection conn) {
            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();
                }
            }
        }
    }
//使用 : 
Test :
    public class Test {
        public static void main(String[] args) throws SQLException {
            Connection conn = JdbcUtils.getConnnection();
            String sql="select * from student where username=? and password=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,"张三");
            ps.setString(2,"zs");
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                System.out.println("登陆成功!");
            }else{
                System.out.println("登录失败!");
            }
            JdbcUtils.close(rs,ps,conn);
        }
    }