jdbc连接及封装

爱被打了一巴掌 2024-03-30 14:34 60阅读 0赞

### 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);
            }
        }