JDBC进行表的curd操作

朴灿烈づ我的快乐病毒、 2023-06-30 13:58 23阅读 0赞

## JDBC连接数据库进行数据库的增删改操作 ##

添加数据

//  添加操作
        @Test
        public void test5()
        {
            InputStream is = null;
            PreparedStatement ps = null;
            Connection connection = null;
            try
            {
                //  1. 获取properties文件中的4个基本信息
                is = JdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
                Properties properties = new Properties();
                properties.load(is);
    
                String user = properties.getProperty("user");
                String password = properties.getProperty("password");
                String url = properties.getProperty("url");
                String driverClass = properties.getProperty("driverClass");
    
                //  2.加载驱动
                Class.forName(driverClass);
                //  3.获取链接
                connection = DriverManager.getConnection(url, user, password);
    
                //  4.获取预编译sql  ?代表预编译sql
                String sql = "INSERT INTO dome1(NAME,PASSWORD) VALUES(?,?)";
                ps = connection.prepareStatement(sql);
    
                //  5.填充占位符
                ps.setString(1, "李四");
                ps.setString(2, "123456");
    
                //  6.执行操作
                ps.execute();
    
                System.out.println("添加成功");
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
    
            finally
            {
                //  7.关闭资源
    
                try
                {
                    if (is != null)
                        is.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
    
                try
                {
                    if (ps != null)
                        ps.close();
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
    
                try
                {
                    if (connection != null)
                        connection.close();
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
    
            }
    
        }

提取相同代码

public static Connection getConnection() throws Exception{
    
                //  1. 获取properties文件中的4个基本信息
                InputStream is = JdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
                Properties properties=new Properties();
                properties.load(is);
    
                String user = properties.getProperty("user");
                String password = properties.getProperty("password");
                String url = properties.getProperty("url");
                String driverClass = properties.getProperty("driverClass");
    
                //  2.加载驱动
                Class.forName(driverClass);
                //  3.获取链接
                Connection  connection = DriverManager.getConnection(url, user, password);
    
    
            return connection;
        }
    
        //  修改关闭资源
        public static void closes(Connection connection, PreparedStatement ps){
    
            try
            {
                if( ps != null)
                    ps.close();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
    
            try
            {
                if(connection!= null)
                    connection.close();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
    
        }

更新

//  更新操作
        @Test
        public void test2()
        {
            Connection connection = null;
            PreparedStatement ps = null;
            try
            {
                //  1.获取数据库连接
                connection = JdbcUtil.getConnection();
                //  2.获取preparestatement
                String sql = "update dome1 set name =? where name='zs' ";
                ps = connection.prepareStatement(sql);
    
                ps.setString(1, "张大三");
                //  3.执行
                ps.execute();
    
                System.out.println("更新成功");
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                //  4.关闭
                JdbcUtil.closes(connection, ps);
            }
    
        }

删除

//  删除操作
        @Test
        public void test3()
        {
            Connection connection = null;
            PreparedStatement ps = null;
            try
            {
                //  1.获取数据库连接
                connection = JdbcUtil.getConnection();
                //  2.获取preparestatement
                String sql = "delete  from dome1 where name = ? ";
                ps = connection.prepareStatement(sql);
    
                ps.setString(1, "李四");
                //  3.执行
                ps.execute();
    
                System.out.println("删除成功");
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                //  4.关闭
                JdbcUtil.closes(connection, ps);
            }
    
        }

通用的增删改操作

//  通用增删改
        public static  void update(String sql, Object ... args) throws  Exception{
            Connection connection = null ;
            PreparedStatement ps = null;
            try
            {
                //  1.获取数据库连接
                 connection = getConnection();
                //  2.获取PreparedStatement
                 ps = connection.prepareStatement(sql);
                //  3.填充占位符,
                for (int i = 0; i <args.length ; i++)
                {
                    ps.setObject((i+1), args[i]);
                }
                //  4.执行
                ps.execute();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                //  5.关闭
                closes(connection , ps);
            }
    
    
        }

案例代码

//  测试通用增删改
        @Test
        public void test4()
        {
            String sql = "  INSERT INTO dome1(NAME,PASSWORD) VALUES(?,?);";
            try
            {
                JdbcUtil.update(sql, "王五", "123457");
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }

## JDBC连接数据库进行数据库的查询操作 ##

//  查询
        @Test
        public void test6()
        {
    
            Connection connection = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try
            {
                //  1.获取数据库连接
                connection = JdbcUtil.getConnection();
    
                //  2.获取preparestatement
                String sql = "select name ,password  from dome1 where name=?";
                ps = connection.prepareStatement(sql);
    
                // 设置占位符
                ps.setObject(1, "张大三");
    
                //  3.执行并返回结果集
                rs = ps.executeQuery();
    
                //  处理结果集
                if (rs.next())
                { //   next方法，查询是否有数据如果有就为true 如果没有为false
                    //  获取当前查询的数据字段
                    String name = rs.getString(1);
                    String password = rs.getString(2);
    
                    Dome1 dome1 = new Dome1(name, password);
                    System.out.println(dome1);
                }
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                //  4.关闭资源
                JdbcUtil.closes(connection, ps, rs);
    
            }
    
    
        }

单表通用查询

//  通用的查询
        public static Dome1 query(String sql, Object ... args)throws  Exception{
            Connection connection = null ;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try
            {
                //  1.获取数据库连接
                connection = getConnection();
                //  2.获取PreparedStatement
                ps = connection.prepareStatement(sql);
                //  3.填充占位符,
                for (int i = 0; i <args.length ; i++)
                {
                    ps.setObject((i+1), args[i]);
                }
                //  4.得到查询的结果集
                 rs =ps.executeQuery();
                //  5.得到结果集的元数据
                ResultSetMetaData metaData = rs.getMetaData();
                //  通过元数据获取其中的列数
                int columnCount = metaData.getColumnCount();
                if(rs.next()){ //   判断是否还有下一条数据
                    //  创建对象
                    Dome1 dome1=new Dome1();
    
                    for (int i = 0; i < columnCount ; i++)
                    {
                        Object columnValue  = rs.getObject(i + 1);
    
                        //  获取每个数据的列明
                        String columnName = metaData.getColumnName(i+1);
    
                        //  通过反射获取javabean中的字段名
                        Field declaredField = Dome1.class.getDeclaredField(columnName);
                        declaredField.setAccessible(true); //   设置属性如果是private修饰的也能够赋值
                        declaredField.set(dome1,columnValue);    //  给属性设置值
    
                        return dome1;
                    }
                }
    
    
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                //  5.关闭
                closes(connection , ps,rs);
            }
    
            return null;
        }

案例代码

//  测试通用查询
        @Test
        public void test7() throws Exception
        {
            String sql = "select name,password from dome1 where name= ?";
            Dome1 dome1 = JdbcUtil.query(sql, "王五");
            System.out.println(dome1);
    
        }